Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Собрать с разных диапазонов в один, на одном листе, диапазоны на разном "расстоянии"
 
#1
03.01.2016 13:37:42
Всем добра и с Новым годом!!
Честно! Искал по форуму, но ничего не нашел, хотя есть ощущение, что раньше что-то могло быть ..
(искал по фразе: "собрать с разных диапазонов в один" - ничего нужного не нашлось)

В файле там все должно быть понятно, но на всякий случай еще напишу словами
Есть несколько "диапазонов" со списками (причем с ними даже есть рядом слева нумерация). у списков есть заголовки содержащие одинаковые слова
так вот, как собрать из всех диапазонов на этом листе в один диапазон? количество диапазонов может добавляться, количество элементов списка в каждом из них - тоже может расти

Формулой, которая при растягивании вниз будет подтягивать данные, если их стало больше
:-)
 
 
 
#2
03.01.2016 13:56:42
с доп.столбцами
 
 
 
#3
03.01.2016 14:20:26
спасибо большое за это решение с дополнительными столбцами!
только я было подумал, что дополнительных столбцов 1-2, но их тут аж целых 4..
это хорошее решение, если бы не было важно изменение структуры данных, в общем, целых 4 столбца крайне неудобно добавлять на лист

если будут еще какие-то варианты, буду очень благодарен!
 
 
 
#4
03.01.2016 14:27:54
Мне кажется, проще макросом
Код
Sub tt()
    Dim lRow As Long, eRow As Long, I As Long
    Dim Col: Col = Array("B", "H", "M", "Q")
    eRow = 4
    For I = 0 To 3
        lRow = Cells(Rows.Count, Col(I)).End(xlUp).Row
        Range("T" & eRow & ":T" & eRow + lRow - 4).Value = Range(Col(I) & 4 & ":" & Col(I) & lRow).Value
        eRow = eRow + lRow - 3
    Next
    For I = 4 To eRow
        Cells(I, "S").Value = I - 3
    Next
End Sub
 
 
 
#5
03.01.2016 14:46:47
спасибо за решение макросом, а формулами точно никак?
 
 
 
#6
03.01.2016 17:27:53
Цитата
Zhukov_K написал: а формулами точно никак?
Во-первых, вам дали решение формулами выше но с доп. столбцами (я бы там только заменил функцию ДВССЫЛКА на ИНДЕКС, чтобы избежать летучести).
Во-вторых, одной формулой можно (см. вложение), а нужно ли - судите сами.
Добавил вариант с доп. столбцами.
Изменено: KL - 03.01.2016 17:53:16
KL
 
 
 
#7
03.01.2016 17:40:22
массивная :)
=ИНДЕКС(A$4:ИНДЕКС($4:$100;;СТОЛБЕЦ()-2);ОСТАТ(НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСК("№";A$1:ИНДЕКС($1:$1;СТОЛБЕЦ()-2)));ЕСЛИ(A$4:ИНДЕКС($4:$100;;СТОЛБЕЦ()-2)>0;A$4:ИНДЕКС($4:$100;;СТОЛБЕЦ()-2)/1000+СТОЛБЕЦ(ДВССЫЛ("A4:"&АДРЕС(100;СТОЛБЕЦ()-2)))));СТРОКА(T1));1)*1000;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСК("№";A$1:ИНДЕКС($1:$1;СТОЛБЕЦ()-2)));ЕСЛИ(A$4:ИНДЕКС($4:$100;;СТОЛБЕЦ()-2)>0;A$4:ИНДЕКС($4:$100;;СТОЛБЕЦ()-2)/1000+СТОЛБЕЦ(ДВССЫЛ("A4:"&АДРЕС(100;СТОЛБЕЦ()-2)))));СТРОКА(T1))+1)
 
 
 
#8
03.01.2016 18:26:20
Не одной формулой, но постарался оптимизировать.
1. Формулы определения количества для каждого диапазона - по 1.
2. Формула нумерации:
=ЕСЛИ(СТРОКА(A1)>$R$2;"";СТРОКА(A1))
3. Подтягиваем товары:
=ЕСЛИ(S4="";"";ВПР(1+S4-ИНДЕКС($A$2:$P$2;ПОИСКПОЗ(S4;$A$2:$P$2));ИНДЕКС($A$4:$P$4;ПОИСКПОЗ(S4;$A$2:$P$2)):$Q$15;2;))
 
 
 
#9
03.01.2016 19:01:59
Красиво! :)
KL
 
 
 
#10
03.01.2016 21:02:00
спасибо за эти решения! только добрался до компьютера, сейчас буду изучать!! ))
 
 
 
#11
03.01.2016 21:40:43
Цитата
KL написал:
Во-вторых, одной формулой можно (см. вложение), а нужно ли - судите сами.
Да, это, конечно, так, то есть действительно одной формулой, но файл с примером это лишь один из многочисленных вариантов, то есть предполагалось не разово обработать формулой именно этот файл с этими данными, а использовать формулу для самых разных случаев, в которых нынешние диапазоны: $B$4:$B$37 , $H$4:$H$37 , $M$4:$M$37 , $Q$4:$Q$37 совершенно будут в других столбцах и их количество тоже будет от одного до десяти (примерно)

а вот формула Т(ИНДЕКС(($B$4:$B$37;$H$4:$H$37;$M$4:$M$37;$Q$4:$Q$37);$Z4;;$Y4)) очень интересная!! впервые вижу использование второго синтаксиса ИНДЕКС(ссылка;номер_строки;номер_столбца;номер_области) а зачем только в этой формуле Т впереди?
 
 
 
#12
03.01.2016 21:44:21
Цитата
Catboyun написал: массивная
Крутая формула, пока не разобрался как она работает, но попробую разобраться. В ней же можно, я думаю, вместо СТРОКА(T1) сослаться просто на нумерацию слева? (там нумерация подразумевается не формулами, а просто константами и нумерация там должна быть всегда по замыслу файла)
 
 
 
#13
03.01.2016 21:48:55
Zhukov_K, сколько наблюдаю за Вашими хотелками - дайте одной формулой. Да неправильно это! Ни с точки зрения здравого смысла, ни с точки зрения оптимизации вычислений.
Если что-то нужно поменять? Логика "суперформулы" забудется. Сидеть потом, колупаться... Особенно, если
Цитата
использовать формулу для самых разных случаев
Начнет файл подтормаживать. На форум - помогите оптимизировать? Об этом нужно заранее думать.
 
 
 
#14
03.01.2016 21:55:05
Цитата
vikttur написал: Не одной формулой, но постарался оптимизировать.
Очень красивое решение! почти элегантное, но, как уже писал в ответе выше для KL предполагается обработать далеко не один файл, то есть использовать Ваше решение можно, но нужно будет всегда прописывать в нужной строке дополнительные формулы вида =МАКС(G4:G15)+G2 (даже если усовершенствовать их до таких: МАКС(ИНДЕКС(2:2;1;1):ИНДЕКС(2:2;1;СТОЛБЕЦ()-1))+МАКС(G4:G15) - но все равно придется руками определять диапазон G4:G15
 
 
 
#15
03.01.2016 22:02:19
Цитата
предполагается обработать далеко не один файл
Определитесь: или максимально универсальность с ущербностью для вычислений, или максимально оптимально с ручной подгонкой. Я. не о своей формуле, а в общем
Цитата
придется руками определять диапазон G4:G15
Задать  G4:G100
А можно ведь в отдельной ячейке определять максимальную длину диапазона.
 
 
 
#16
03.01.2016 22:04:36
не то чтобы максимальная универсальность, это, наверное, будет уж очень громоздко, но в любом случае, конечно, чтобы формулу можно было использовать не только в рамках одного файла  :cry:
 
 
 
#17
03.01.2016 22:21:43
Цитата
Zhukov_K написал:  а зачем только в этой формуле Т впереди?
Это для отражения текстовой строки (эмуляция пусто) вместо нулей в незаполненных ячейках.
KL
 
 
 
#18
03.01.2016 22:25:52
понял, буду знать! спасибо! )
 
 
 
#19
04.01.2016 04:22:54
Вот тут еще пара решений и надстройка в которой есть функция COL.ARRAY() и еще несколько функций для обработки массивов созданных Charles Williams

https://fastexcel.wordpress.com/2015/01/16/stacking-appendingthere-arraysranges-in-excel-formulas-ve...
http://www.decisionmodels.com/fastexcel.htm
KL
 
 
 
#20
04.01.2016 14:36:02
крутые ссылки! на английском! ) но попробую разобраться! ) Спасибо, Кирилл!!
 
 
 
Страницы: 1
Наверх