29 сентября 2018 г.

Excel Копирование ячеек с условием без макросов

Всем добра.

Задача:
В некую таблицу скопировать все значения из столбца другой таблицы где значение справа равно некоторому.
Мысли:
Когда у меня первый раз спросили про такое, я ответил- фигушки, без макроса никак, по крайней мере для неопределенного количества найденных строк.
 Спустя время мне задали этот вопрос повторно и тут меня осенило - это не невозможно.



Итак в чем заключается мысль?, а мысль в доп столбце, в котором хранится номер позиции вхождения в некотором диапазоне, где начало диапазона определяется предыдущим.

на деле это выглядит так:
Искать будем значение из ячейки $B$5 (в этой ячейке динамический выпадающий список) в другом листе:
=ПОИСКПОЗ($B$5;ЛИСТ2!B:B;0);"-")
ПоискПоз - ищет вхождения в диапазоне
Результат формулы = 8, т.е. восьмая строчка
Проверяем, и вправду так.

Последующие ячейки при расчете будут учитывать предыдущую:
=ПОИСКПОЗ($B$5;ДВССЫЛ("Карт.сч.!R"&$C16+1&"C2:R[1000]C2";ЛОЖЬ);0)+C16)
Во втором аргументе формулы ПоискПоз ( ДВССЫЛ("Карт.сч.!R"&$C16+1&"C2:R[1000]C2";ЛОЖЬ); ) формируется диапазон $B$0+предыдущее положение+1 т.е. R9C2:R1000C2
Т.о. мы исключаем уже найденную строку из массива для поиска.

В итоге вот такой формулой
ДВССЫЛ("Карт.сч.!R"&C16&"C5";ЛОЖЬ)
Мы получаем значение из пятой колонки строки номер 8, и так далее.

Это можно записать в одну ячейку, но в моем случае получается очень громоздкая конструкция:

=ЕСЛИОШИБКА(ЕСЛИ(ЕПУСТО(ДВССЫЛ("Карт.сч.!R"&ЕСЛИОШИБКА(ПОИСКПОЗ(ДВССЫЛ("$B"&СТРОКА()-B17-10;ИСТИНА);ДВССЫЛ("Карт.сч.!R"&$C16+1&"C2:R[1000]C2";ЛОЖЬ);0)+C16;"-")&"C5";ЛОЖЬ));"АВАНС ";"")&ТЕКСТ(ДВССЫЛ("Карт.сч.!R"&ЕСЛИОШИБКА(ПОИСКПОЗ(ДВССЫЛ("$B"&СТРОКА()-B17-10;ИСТИНА);ДВССЫЛ("Карт.сч.!R"&$C16+1&"C2:R[1000]C2";ЛОЖЬ);0)+C16;"-")&"C3";ЛОЖЬ);"дд.ММ.гггг");"")

Для двойных/тройных условий в поиске предлагаю использовать доп колонку, в таблице поиска, с конкатенацией колонок.

Комментариев нет:

Отправка комментария