Резюме
В этом примере показано, как получить самую раннюю и самую позднюю даты, связанные с проектом. В показанном примере формулы в H5 и I5 следующие:
=MINIFS(data(Start),data(Project),G5) // earliest =MAXIFS(data(End),data(Project),G5) // latest
где «данные» - это таблица Excel, как показано, а имена проектов в столбце G соответствуют этим столбцам B.
Примечание. MINIFS и MAXIFS доступны только в Excel 365 и Excel 2019. В других версиях Excel вы можете использовать простую формулу массива, как описано ниже.
Введение
Задача здесь - найти самые ранние и самые поздние даты, связанные с данным проектом. Самые ранние даты берутся из столбца " Начало" , а самые поздние даты - из столбца " Конец" .
У вас может возникнуть соблазн использовать такие функции поиска, как VLOOKUP, XLOOKUP или INDEX и MATCH. Однако, поскольку каждый проект имеет более одной записи, и записи не всегда могут быть отсортированы по дате, это становится сложной задачей.
Лучше использовать процесс исключения: отбросить даты для других проектов и работать только с оставшимися датами.
Объяснение
Функция MINIFS возвращает наименьшее числовое значение, которое соответствует указанным критериям, а функция MAXIFS возвращает наибольшее числовое значение, которое соответствует указанным критериям.
Как и СЧЁТЕСЛИМН и СУММЕСЛИМН, эти функции используют "пары" диапазон / критерий для применения условий. Для обеих формул нам нужно только одно условие: имя проекта должно совпадать с именем в столбце G:
data(Project),G5 // condition
Чтобы получить самую раннюю дату начала, мы используем:
=MINIFS(data(Start),data(Project),G5) // earliest date
Здесь MINIFS возвращает минимальное значение в столбце Start, где проект равен «Omega» (из ячейки G5). Поскольку даты в Excel - это просто числа, минимальная дата совпадает с самой ранней датой.
Чтобы получить последнюю дату окончания, мы используем:
=MAXIFS(data(End),data(Project),G5) // latest date
Здесь MAXIFS возвращает максимальное значение в столбце « Конец», где проект равен «Омега». Как и выше, максимальное значение совпадает с последней датой.
Альтернатива формулы массива
Если у вас нет MINIFS и MAXIFS, вы можете использовать простые формулы массива, основанные на функциях MIN и MAX, чтобы получить тот же результат. Для самой ранней даты начала:
(=MIN(IF(data(Project)=G5,data(Start))))
На последнюю дату окончания:
(=MAX(IF(data(Project)=G5,data(End))))
Примечание: обе формулы являются формулами массива и должны вводиться с помощью Ctrl + Shift + Enter в Excel 2019 или более ранней версии. В Excel 365 вы можете вводить формулы обычным образом, поскольку формулы массива являются собственными.
В обоих случаях функция ЕСЛИ используется для «фильтрации» значений даты следующим образом:
IF(data(Project)=G5,data(End)) // filter dates by project
Когда G5 - «Омега», IF возвращает дату окончания. В противном случае IF возвращает FALSE. Поскольку мы тестируем все имена проектов в таблице одновременно, результатом является массив значений, подобных этому:
(43936;43983;43990;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Крупные серийные номера - это даты в формате Excel, связанные с проектом Omega. Остальные значения - ЛОЖЬ, поскольку это не Omega. Поскольку MIN и MAX запрограммированы так, чтобы игнорировать логические значения TRUE и FALSE, они работают только с оставшимися значениями. MIN возвращает наименьшую (самую раннюю) дату, а MAX возвращает наибольшую (самую последнюю) дату.
