ЕСЛИ ЕСЛИ ТО ИНДЕКСИРОВАТЬ ЭТО или ИНДЕКСИРОВАТЬ СООТВЕТСТВИЕ&СООТВЕТСТВИЕ?

Question

Вот один из подходов к созданию динамических списков в Excel.

Предположим, у вас есть массив номеров строк, где находятся ваши критерии TRUE(например, Код покупателя = JDH и Остаток > 0). Затем вы можете передать этот массив в SMALL()функцию, которая будет перечислять номера строк по мере их заполнения. И, наконец, вы можете использовать как SMALL()в row_numsфункции INDEX(), и это будет перечислять соответствующие значения любого столбца, который вы укажете с помощью column_numпараметра.

Итак, начнем с получения массива номеров строк. Для простоты я предположу, что в вашей таблице данных всего 25 строк, и что панель мониторинга находится на том же листе, что и данные. Кроме того, я использую код покупателя ELN, который находится в ячейке B30.

Сначала мы вычислим массив TRUE/FALSEзначений, соответствующих вашим критериям:

($C$1:$C$25=$B$30)*($G$1:$G$25>0)

Первое выражение дает массив TRUE/FALSEзначений, где Код покупателя (столбец C) = ELN, а второе дает массив TRUE/FALSEзначений, где Остаток (столбец G) > 0. В арифметических операциях с логическими значениями TRUEи FALSEпреобразуются в 1 и 0, поэтому это умножение дает массив 1 и 0 с 1 в позициях, где оба ваших критерия равны TRUE.

Теперь нам нужно преобразовать этот массив в список номеров строк, где ваши критерии TRUE. Если мы разделим массив последовательных чисел на этот массив из 1 и 0, мы получим массив номеров строк везде, где есть 1, перемежаемый ошибками везде, где есть #DIV/0!0.

ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0))

Далее, вместо SMALL(), мы будем использовать AGGREGATE(), который делает то же самое, что SMALL()делала бы (function=15), за исключением того, что у него есть возможность игнорировать ошибки (option=6). Также AGGREGATE()может обрабатывать массивы без необходимости вводить их с помощью CTRLShiftEnter.

Поэтому в приведенной ниже формуле ошибки AGGREGATE()игнорируются #DIV/0!и выдается массив, содержащий только номера строк, соответствующие вашим критериям:

AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33)

У меня эта формула в строке 34, поэтому "-33" заставляет функцию начинаться с 1 и перечислять номера строк по мере их заполнения. Измените этот параметр в соответствии с вашей ситуацией.

Теперь мы можем использовать это в INDEX() и обернуть все это в , чтобы IFERROR()вставлять пробелы, когда INDEX()заканчивается row_numsпо мере заполнения вниз. Эта формула, заполненная вниз и вправо от B34:

=IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH(B$32,$A$1:$G$1,0)),"")

дает результаты, показанные ниже.

Для покупателя ELN только строки 5 и 11 имеют значение «Остаток 0», а формула перечисляет все строки, кроме них.

Обратите внимание, что я ссылался на заголовки панели мониторинга в , MATCH()которая дает column_num'sс помощью B$32как lookup_value. Когда формула заполняется по всей длине, это работает для номера заказа и описания, но другие заголовки на панели мониторинга не совпадают с заголовками в вашей таблице данных.

Поэтому для других столбцов я заменил ссылку на ячейку B$32на текст в кавычках: "LINE_NBR" для столбца PO Line, "QUANTITY" для столбца Qty Ordered и "REMAINING" для столбца Qty Received. Этот последний столбец включает в себя расчет, поэтому формула на самом деле такая:

=E34-IFERROR(INDEX($A$1:G$25,AGGREGATE(15,6,ROW($1:$25)/(($C$1:$C$25=$B$30)*($G$1:$G$25>0)),ROW()-33),MATCH("REMAINING",$A$1:$G$1,0)),"")

Последний столбец «Необходимо» рассчитывается следующим образом:

=E34-F34, и заполнил.

Надеюсь, это поможет, и удачи.

Answer 1