Вопрос или проблема
Я использую Excel для регистрации списка предметов (названия предметов в столбце D) и времени, затраченного на производство этих предметов, в столбце G.
Я делаю INDEX MATCH, чтобы найти самый плохо работающий продукт недели (или между двумя датами на мой выбор), используя
=MAXIFS($G:$G,$E:$E,">="&O67,$E:$E,"<"&O68)
Это возвращает длительность самого длительного производства между двумя датами в ячейках O67 и O68. Затем я хочу вернуть название предмета (столбец D), который производился дольше всего.
Я пробовал
=INDEX(D:F,MATCH(O69,G:G,0)
Но это просто смотрит на зафиксированное время и возвращает первое появление этого времени в списке. Список довольно длинный, поэтому многие времена повторяются, так что это не самый лучший способ, я просто хочу, чтобы это учитывало две даты в O67 и O68 и искало между ними для INDEX MATCH.
Я думаю, что правильным решением может быть массив, но я никогда этого не делал, поэтому, если это правильный путь, пожалуйста, будьте терпеливы и объясняйте мне медленно, чтобы я мог понять!
Спасибо заранее!
Вы можете попробовать эту массивную формулу, введя её не с помощью 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 массивные формулы могут только просматриваться, но не вводиться.
Вашу проблему можно решить с помощью нескольких вспомогательных ячеек: начальной и конечной даты и их количества.
-
Введите эту формулу для получения количества начальной и
конечной даты в ячейку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))))}
N.B.
- Количество начальной и конечной даты предотвращает использование функции
IFERRORи оправдывает
список продуктов как ответ, который должен быть равен
значению количества.
Отрегулируйте ссылки на ячейки в формуле по мере необходимости.
Ответ или решение
Для решения задачи поиска товара с наименьшей производительностью за выбранный период в Excel с использованием функций INDEX и MATCH, можно воспользоваться формулой массива. В данном случае, вам потребуется выбрать наибочное время производства в определенные даты и вернуться к его названию. Давайте подробно рассмотрим, как это можно сделать.
Шаг 1: Определение диапазонов данных
Предположим, у вас есть следующие данные на листе Excel:
- Колонка D: Название товара
- Колонка E: Дата производства
- Колонка G: Время производства в часах
Шаг 2: Определение дат
В ячейках O67 и O68 укажите начальную и конечную дату периода, для которого вы хотите выполнить анализ.
Шаг 3: Использование функции MAXIFS
Сначала вам уже известна формула, которая позволяет получить максимальное время производства за указанный период:
=MAXIFS($G:$G, $E:$E, ">=" & O67, $E:$E, "<" & O68)Эта формула возвращает максимальное время производства между двумя датами.
Шаг 4: Поиск товаров с помощью массива
Теперь вам нужно определить, какой товар соответствует этому максимальному времени. Здесь вам потребуется формула массива. Вставьте следующую формулу в ячейку, где вы хотите увидеть название товара с наибольшим временем производства:
=INDEX($D$2:$D$1000, MATCH(1, ($G$2:$G$1000 = MAXIFS($G$2:$G$1000, $E$2:$E$1000, ">=" & O67, $E$2:$E$1000, "<" & O68)) * ($E$2:$E$1000 >= O67) * ($E$2:$E$1000 < O68), 0))Шаг 5: Завершение формулы массива
Важно помнить, что для того, чтобы эта формула работала, вам нужно ввести её как формулу массива. Это делается с помощью сочетания клавиш Ctrl + Shift + Enter вместо обычного Enter. После ввода формулы вы должны увидеть фигурные скобки {} вокруг формулы, указывающие на то, что она является формулой массива.
Шаг 6: Улучшение производительности
Для повышения производительности формул рекомендуется использовать ограниченные диапазоны вместо указания полностью колонок (например, $D$2:$D$1000 вместо $D:$D). Это значительно ускорит вашу работу с Excel, особенно при больших объемах данных.
Заключение
Таким образом, с помощью комбинации функций INDEX, MATCH и MAXIFS вы сможете эффективно находить товары с наибольшим временем производства за выбранный период. Убедившись, что вы правильно ввели формулу массива, вы получите точный результат, который позволит вам анализировать производительность товаров. Не забывайте адаптировать диапазоны по мере необходимости в соответствии с вашей таблицей.