☰

Excel ИНДЕКС СОПОСТАВЛЕНИЕ ищет между двумя датами по моему выбору

tag-icon • tag-icon microsoft-excel worksheet-function array
Excel ИНДЕКС СОПОСТАВЛЕНИЕ ищет между двумя датами по моему выбору

Скриншот
Я использую Excel для регистрации списка элементов (названия элементов в столбце D), а затем времени, которое потребовалось на производство этого элемента, в столбце G.

Я провожу ИНДЕКСНОЕ СОПОСТАВЛЕНИЕ, чтобы найти продукт с наихудшими показателями за неделю (или между двумя датами по моему выбору) с помощью

=MAXIFS($G:$G,$E:$E,">="&O67,$E:$E,"<"&O68)

Это возвращает продолжительность самого длительного производства между двумя датами в ячейках O67 и O68. Затем я хочу вернуть название элемента (столбец D), который был изготовлен дольше всего.

я пробовал

=INDEX(D:F,MATCH(O69,G:G,0),1)

Но это просто смотрит на записанное время и возвращает первый случай, когда это время появилось в списке. Это довольно длинный список, поэтому многие времена повторяются, так что это не выход, я просто хочу, чтобы он ссылался на две даты в O67 и O68 и искал между ними СООТВЕТСТВИЕ ИНДЕКСА.

Я думаю, что правильным решением может быть массив, но я раньше этим не занимался, так что если это ваш путь, то не могли бы вы делать это медленно вместе со мной, чтобы я мог понять!

Заранее спасибо!

решение1

Вы можете попробовать этомножествоформула, введенная не с использованием Enter, а Ctrl+ Shift+ Enter:

=INDEX($D$2:$D$1000,MATCH(1,($G$2:$G$1000= (ABSOLUTE REFERENCE TO MAXIFS CELL)
                           *($E$2:$E$1000>=$O$67)
                           *($E$2:$E$1000<$O$68),0))

В формулах массива лучше использовать ссылки на разумно выбранные диапазоны, а не на полные столбцы. Потому что если вы выберете полные столбцы, формула массива будет работать на каждой строке, даже когда она не должна использоваться, что замедлит работу книги.

У меня нет Excel 2019, чтобы проверить это, а в Excel 365 формулы массива можно только просматривать, но не вводить.

решение2

Вашу проблему можно решить, используя несколько вспомогательных ячеек: начальную и конечную дату, а также их количество.

введите описание изображения здесь

  • Введите эту формулу, чтобы получить количество дат начала и окончания в ячейке E193.

    =SUMPRODUCT(($C$179:$C$190>=C193)*($C$179:$C$190<=D193))

  • Введите эту формулу массива (CSE) в ячейку B195, закончите с помощьюCtrl+Shift+Enterи залейте.

{=IF(ROWS(B$195:B195)>$E$193,"",INDEX(B$179:B$190,SMALL(IF(($C$179:$C$190>=$C$193)*($C$179:$C$190<=$D$193),ROW(B$179:B$190)-ROW($B$179)+1),ROWS(B$195:B195))))}

Примечание.

  • Количество дат начала и окончания позволяет избежать использования IFERRORфункции, а также оправдывает список продуктов в качестве ответа, должно быть равно значению количества.

При необходимости измените ссылки на ячейки в формуле.

Связанный контент