Вот один из подходов к созданию динамических списков вещей в 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 где Remaining (столбец 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() может обрабатывать массивы, не вводя их с помощью CTRL Shift Enter.
Таким образом, в приведенной ниже формуле 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 и заполнены.
Надеюсь, это поможет, и удачи.
