Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Автоматическое заполнение элементов диапазона - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматическое заполнение элементов диапазона (Формулы/Formulas)
Автоматическое заполнение элементов диапазона
Vseslav Дата: Воскресенье, 18.10.2015, 23:20 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день, уважаемые форумчане.
Возникла следующая задача:
Дано: в ячейке есть текст с диапазоном чисел, записанным через дефис, например, 1-100
Необходимо: эту ячейку разобрать на массив всех целочисленных значений в указанном диапазоне, например, 1,2,3,...,100

Второй день бьюсь, никак не могу одолеть. В VBA лезть особо не хочется, но если без этого никак, то буду благодарен, если ткнёте носом где именно копать.

Заранее благодарен
К сообщению приложен файл: 3101620.xlsx (10.2 Kb)


Сообщение отредактировал Vseslav - Понедельник, 19.10.2015, 12:08
 
Ответить
СообщениеДобрый день, уважаемые форумчане.
Возникла следующая задача:
Дано: в ячейке есть текст с диапазоном чисел, записанным через дефис, например, 1-100
Необходимо: эту ячейку разобрать на массив всех целочисленных значений в указанном диапазоне, например, 1,2,3,...,100

Второй день бьюсь, никак не могу одолеть. В VBA лезть особо не хочется, но если без этого никак, то буду благодарен, если ткнёте носом где именно копать.

Заранее благодарен

Автор - Vseslav
Дата добавления - 18.10.2015 в 23:20
gling Дата: Воскресенье, 18.10.2015, 23:33 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2519
Репутация: 674 ±
Замечаний: 0% ±

2010
Попробуйте вариант "Текст по столбцам" разделитель дефис. Если в тексте существуют пробелы то после разделения удалить пробелы через Ctrl+H. А лучше конечно файл - пример увидеть чтобы правильные советы дать.


ЯД-41001506838083
 
Ответить
СообщениеПопробуйте вариант "Текст по столбцам" разделитель дефис. Если в тексте существуют пробелы то после разделения удалить пробелы через Ctrl+H. А лучше конечно файл - пример увидеть чтобы правильные советы дать.

Автор - gling
Дата добавления - 18.10.2015 в 23:33
Vseslav Дата: Воскресенье, 18.10.2015, 23:37 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
gling, если использовать "Текст по столбцам" с разделителем дефис, то я получу 2 ячейки: 1 и 100. Т.е. все промежуточные значения не будут учтены (2,3,4,...,99).
 
Ответить
Сообщениеgling, если использовать "Текст по столбцам" с разделителем дефис, то я получу 2 ячейки: 1 и 100. Т.е. все промежуточные значения не будут учтены (2,3,4,...,99).

Автор - Vseslav
Дата добавления - 18.10.2015 в 23:37
gling Дата: Воскресенье, 18.10.2015, 23:40 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2519
Репутация: 674 ±
Замечаний: 0% ±

2010
лучше конечно файл - пример увидеть чтобы правильные советы дать.


ЯД-41001506838083
 
Ответить
Сообщение
лучше конечно файл - пример увидеть чтобы правильные советы дать.

Автор - gling
Дата добавления - 18.10.2015 в 23:40
Vseslav Дата: Понедельник, 19.10.2015, 12:11 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
В первое сообщение темы добавил файл примера
 
Ответить
СообщениеВ первое сообщение темы добавил файл примера

Автор - Vseslav
Дата добавления - 19.10.2015 в 12:11
китин Дата: Понедельник, 19.10.2015, 12:49 | Сообщение № 6
Группа: Модераторы
Ранг: Экселист
Сообщений: 7013
Репутация: 1073 ±
Замечаний: 0% ±

Excel 2007;2010;2016
ну так может проскочит?
Код
=ЕСЛИ(СТРОКА(A1)>--ПРАВБ(Дано!$B$2;ДЛСТР(Дано!$B$2)-ПОИСК("-";Дано!$B$2));"Нет таких";СТРОКА(Дано!A1))
К сообщению приложен файл: Vseslav.xlsx (10.7 Kb)


Не судите очень строго:я пытаюсь научиться
ЯД 41001877306852


Сообщение отредактировал китин - Понедельник, 19.10.2015, 13:57
 
Ответить
Сообщениену так может проскочит?
Код
=ЕСЛИ(СТРОКА(A1)>--ПРАВБ(Дано!$B$2;ДЛСТР(Дано!$B$2)-ПОИСК("-";Дано!$B$2));"Нет таких";СТРОКА(Дано!A1))

Автор - китин
Дата добавления - 19.10.2015 в 12:49
YouGreed Дата: Понедельник, 19.10.2015, 13:06 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 589
Репутация: 123 ±
Замечаний: 0% ±

Excel 2010
Vseslav,
Код
=ЕСЛИ(ИЛИ(ЕНЕТЕКСТ($B1);$B1=ЗНАЧЕН(ПСТР(Дано!$B$2;1;НАЙТИ("-";Дано!$B$2;1)-1)));ЕСЛИ(И($B1<>"";Необходимо!$B1=ЗНАЧЕН(ПРАВСИМВ(Дано!$B$2;ДЛСТР(Дано!$B$2)-НАЙТИ("-";Дано!$B$2;1))));"";Необходимо!$B1+1);ЕСЛИ($B1="";"";ЗНАЧЕН(ПСТР(Дано!$B$2;1;НАЙТИ("-";Дано!$B$2;1)-1))))
К сообщению приложен файл: 4781011.xlsx (11.2 Kb)


Сообщение отредактировал YouGreed - Понедельник, 19.10.2015, 13:23
 
Ответить
СообщениеVseslav,
Код
=ЕСЛИ(ИЛИ(ЕНЕТЕКСТ($B1);$B1=ЗНАЧЕН(ПСТР(Дано!$B$2;1;НАЙТИ("-";Дано!$B$2;1)-1)));ЕСЛИ(И($B1<>"";Необходимо!$B1=ЗНАЧЕН(ПРАВСИМВ(Дано!$B$2;ДЛСТР(Дано!$B$2)-НАЙТИ("-";Дано!$B$2;1))));"";Необходимо!$B1+1);ЕСЛИ($B1="";"";ЗНАЧЕН(ПСТР(Дано!$B$2;1;НАЙТИ("-";Дано!$B$2;1)-1))))

Автор - YouGreed
Дата добавления - 19.10.2015 в 13:06
Vseslav Дата: Понедельник, 19.10.2015, 15:12 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 7
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
YouGreed, спасибо, работает!
И ещё, не сочтите за наглость, спрашиваю в целях повышения образования: Что делает функция ЕНЕТЕКСТ?
 
Ответить
СообщениеYouGreed, спасибо, работает!
И ещё, не сочтите за наглость, спрашиваю в целях повышения образования: Что делает функция ЕНЕТЕКСТ?

Автор - Vseslav
Дата добавления - 19.10.2015 в 15:12
YouGreed Дата: Понедельник, 19.10.2015, 15:27 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 589
Репутация: 123 ±
Замечаний: 0% ±

Excel 2010
Vseslav, [offtop]Проверяет, является ли ячейка не текстом, если в ячейке текст, возвращает ЛОЖЬ.
 
Ответить
СообщениеVseslav, [offtop]Проверяет, является ли ячейка не текстом, если в ячейке текст, возвращает ЛОЖЬ.

Автор - YouGreed
Дата добавления - 19.10.2015 в 15:27
Serge_007 Дата: Понедельник, 19.10.2015, 22:45 | Сообщение № 10
Группа: Админы
Ранг: Местный житель
Сообщений: 16475
Репутация: 2748 ±
Замечаний: ±

Excel 2016
Что делает функция ЕНЕТЕКСТ?
К сообщению приложен файл: 8800197.gif (16.4 Kb)


ЮMoney:41001419691823 | WMR:126292472390
 
Ответить
Сообщение
Что делает функция ЕНЕТЕКСТ?

Автор - Serge_007
Дата добавления - 19.10.2015 в 22:45
Gustav Дата: Вторник, 20.10.2015, 00:18 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2699
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
В VBA лезть особо не хочется, но если без этого никак, то буду благодарен, если ткнёте носом где именно копать.

Позвольте вставить в тему 5 VBA-шных копеек. Увидев подходящий случай, не преминул устроить себе вечернюю разминку с целью очередной демонстрации магии арифметики диапазонов Excel:
[vba]
Код
Sub generateAllNaturalNumbersOfRange()
    Dim strSource   As String
    Dim strAddress  As String
    Dim rng         As Range
    Dim c           As Range
     
    strSource = "1-25,35-30,45-49" 'исходная строка - несколько числовых диапазонов
     
    'превращаем исходную строку в адрес диапазона Excel (в столбце A, чтобы далеко не ходить)
    strAddress = "A" & Replace(Replace(strSource, "-", ":A"), ",", ",A")
     
    'здесь почувствуйте разницу, например, для strSource = "1-25,15-30"
    'Set rng = Range(strAddress)
    Set rng = Union(Range(strAddress), Range(strAddress))
     
    Debug.Print "Количество элементов ряда: ", rng.Cells.Count
     
    'генерация всех элементов (натуральных чисел) сложной комбинации числовых диапазонов
    For Each c In rng.Cells
        Debug.Print c.Row 'номер строки олицетворяет собой очередное число ряда
    Next c

End Sub
[/vba]В общем, понимающий, да обрадуйся! Непонимающий - переспроси, не стесняйся!


МОИ: Ник, Tip box: 41001663842605
 
Ответить
Сообщение
В VBA лезть особо не хочется, но если без этого никак, то буду благодарен, если ткнёте носом где именно копать.

Позвольте вставить в тему 5 VBA-шных копеек. Увидев подходящий случай, не преминул устроить себе вечернюю разминку с целью очередной демонстрации магии арифметики диапазонов Excel:
[vba]
Код
Sub generateAllNaturalNumbersOfRange()
    Dim strSource   As String
    Dim strAddress  As String
    Dim rng         As Range
    Dim c           As Range
     
    strSource = "1-25,35-30,45-49" 'исходная строка - несколько числовых диапазонов
     
    'превращаем исходную строку в адрес диапазона Excel (в столбце A, чтобы далеко не ходить)
    strAddress = "A" & Replace(Replace(strSource, "-", ":A"), ",", ",A")
     
    'здесь почувствуйте разницу, например, для strSource = "1-25,15-30"
    'Set rng = Range(strAddress)
    Set rng = Union(Range(strAddress), Range(strAddress))
     
    Debug.Print "Количество элементов ряда: ", rng.Cells.Count
     
    'генерация всех элементов (натуральных чисел) сложной комбинации числовых диапазонов
    For Each c In rng.Cells
        Debug.Print c.Row 'номер строки олицетворяет собой очередное число ряда
    Next c

End Sub
[/vba]В общем, понимающий, да обрадуйся! Непонимающий - переспроси, не стесняйся!

Автор - Gustav
Дата добавления - 20.10.2015 в 00:18
Gustav Дата: Вторник, 20.10.2015, 13:02 | Сообщение № 12
Группа: Друзья
Ранг: Участник клуба
Сообщений: 2699
Репутация: 1123 ±
Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1
Захотелось на ходу после "вчерашнего" предложить решение и соответствующей обратной задачи. Т.е. имея последовательность натуральных чисел, причем, в общем случае неупорядоченную, свернуть эту последовательность в несколько диапазонов. Для наглядности и преемственности - в диапазоны предыдущего примера:
[vba]
Код
Sub backTask()
    Dim arr As Variant, i As Long, rng As Range
     
    arr = Array(10, 45, 32, 15, 16, 47, 46, 49, 2, 31, 7, 4, 18, 17, 22, 35, 33, 48, 24, 23, 19, 20, 11, 9, 25, 13, 8, 12, 34, 3, 14, 6, 5, 30, 21, 1)
    'arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 30, 31, 32, 33, 34, 35, 45, 46, 47, 48, 49)
     
    Set rng = Cells(arr(LBound(arr)), 1)
    For i = LBound(arr) + 1 To UBound(arr)
        Set rng = Union(rng, Cells(arr(i), 1))
    Next i
     
    Debug.Print Replace(Replace(rng.Address(False, False), "A", ""), ":", "-") '45-49,30-35,1-25
End Sub
[/vba]
Как видно из комментариев, на выходе получилась не очень хорошая строка - не по возрастанию диапазонов слева направо, вероятно, связанная именно с хаотичностью добавления ячеек в операторе Union. Если раскомментировать оператор с упорядоченной последовательностью чисел в Array, то возвращаемая строка станет "хорошей": 1-25,30-35,45-49.

Для универсальности же подхода с целью получения правильной строки следует неявно отсортировать последовательность чисел в процессе добавления очередных ячеек в операторе Union:
[vba]
Код
Sub backTaskWithSort()
    Dim arr As Variant, i As Long, rng As Range, c As Range
     
    arr = Array(10, 45, 32, 15, 16, 47, 46, 49, 2, 31, 7, 4, 18, 17, 22, 35, 33, 48, 24, 23, 19, 20, 11, 9, 25, 13, 8, 12, 34, 3, 14, 6, 5, 30, 21, 1)
     
    Set rng = Cells(WorksheetFunction.Min(arr), 1)
    For i = LBound(arr) + 1 To UBound(arr)
        Set rng = Union(rng, Cells(WorksheetFunction.Small(arr, i + IIf(LBound(arr), 0, 1)), 1))
    Next i

    Debug.Print Replace(Replace(rng.Address(False, False), "A", ""), ":", "-") '1-25,30-35,45-49 'УРА!
     
    'бонус - отсортированный исходный массив - без дополнительных усилий
    For Each c In rng.Cells
        Debug.Print c.Row 'номер строки олицетворяет собой очередное число ряда
    Next c
End Sub
[/vba]


МОИ: Ник, Tip box: 41001663842605
 
Ответить
СообщениеЗахотелось на ходу после "вчерашнего" предложить решение и соответствующей обратной задачи. Т.е. имея последовательность натуральных чисел, причем, в общем случае неупорядоченную, свернуть эту последовательность в несколько диапазонов. Для наглядности и преемственности - в диапазоны предыдущего примера:
[vba]
Код
Sub backTask()
    Dim arr As Variant, i As Long, rng As Range
     
    arr = Array(10, 45, 32, 15, 16, 47, 46, 49, 2, 31, 7, 4, 18, 17, 22, 35, 33, 48, 24, 23, 19, 20, 11, 9, 25, 13, 8, 12, 34, 3, 14, 6, 5, 30, 21, 1)
    'arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 30, 31, 32, 33, 34, 35, 45, 46, 47, 48, 49)
     
    Set rng = Cells(arr(LBound(arr)), 1)
    For i = LBound(arr) + 1 To UBound(arr)
        Set rng = Union(rng, Cells(arr(i), 1))
    Next i
     
    Debug.Print Replace(Replace(rng.Address(False, False), "A", ""), ":", "-") '45-49,30-35,1-25
End Sub
[/vba]
Как видно из комментариев, на выходе получилась не очень хорошая строка - не по возрастанию диапазонов слева направо, вероятно, связанная именно с хаотичностью добавления ячеек в операторе Union. Если раскомментировать оператор с упорядоченной последовательностью чисел в Array, то возвращаемая строка станет "хорошей": 1-25,30-35,45-49.

Для универсальности же подхода с целью получения правильной строки следует неявно отсортировать последовательность чисел в процессе добавления очередных ячеек в операторе Union:
[vba]
Код
Sub backTaskWithSort()
    Dim arr As Variant, i As Long, rng As Range, c As Range
     
    arr = Array(10, 45, 32, 15, 16, 47, 46, 49, 2, 31, 7, 4, 18, 17, 22, 35, 33, 48, 24, 23, 19, 20, 11, 9, 25, 13, 8, 12, 34, 3, 14, 6, 5, 30, 21, 1)
     
    Set rng = Cells(WorksheetFunction.Min(arr), 1)
    For i = LBound(arr) + 1 To UBound(arr)
        Set rng = Union(rng, Cells(WorksheetFunction.Small(arr, i + IIf(LBound(arr), 0, 1)), 1))
    Next i

    Debug.Print Replace(Replace(rng.Address(False, False), "A", ""), ":", "-") '1-25,30-35,45-49 'УРА!
     
    'бонус - отсортированный исходный массив - без дополнительных усилий
    For Each c In rng.Cells
        Debug.Print c.Row 'номер строки олицетворяет собой очередное число ряда
    Next c
End Sub
[/vba]

Автор - Gustav
Дата добавления - 20.10.2015 в 13:02
next777 Дата: Понедельник, 04.04.2016, 23:30 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 20
Репутация: 0 ±
Замечаний: 20% ±

Excel 2013
Захотелось на ходу после "вчерашнего" предложить решение и соответствующей обратной задачи. Т.е. имея последовательность натуральных чисел, причем, в общем случае неупорядоченную, свернуть эту последовательность в несколько диапазонов

Здравствуйте, посоветовали ваш замечательный код, но... увы
очень нужен ваш код почему то не получилось., как раз тот случай нужно
2-35 разбить
и собрать два этих кода ваших, как сделать чтоб заработало, не получается в файле примере
К сообщению приложен файл: 9442409.xlsm (25.8 Kb)


web-программист

Сообщение отредактировал next777 - Понедельник, 04.04.2016, 23:31
 
Ответить
Сообщение
Захотелось на ходу после "вчерашнего" предложить решение и соответствующей обратной задачи. Т.е. имея последовательность натуральных чисел, причем, в общем случае неупорядоченную, свернуть эту последовательность в несколько диапазонов

Здравствуйте, посоветовали ваш замечательный код, но... увы
очень нужен ваш код почему то не получилось., как раз тот случай нужно
2-35 разбить
и собрать два этих кода ваших, как сделать чтоб заработало, не получается в файле примере

Автор - next777
Дата добавления - 04.04.2016 в 23:30
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Автоматическое заполнение элементов диапазона (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

Яндекс.Метрика Яндекс цитирования
© 2010-2024 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!