Резюме
Цей приклад показує, як отримати найбільш ранні та найсвіжіші дати, пов’язані з проектом. У наведеному прикладі формули в 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 повертає найбільше числове значення, яке відповідає наданим критеріям.
Як і COUNTIFS та SUMIFS, ці функції використовують діапазон / критерій "пари" для застосування умов. Для обох формул нам потрібна лише одна умова: назва проекту має відповідати імені у стовпці 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 повертає максимальне значення у стовпці End, де проект дорівнює "Omega". Як і вище, максимальне значення збігається з датою останнього часу.
Альтернатива формули масиву
Якщо у вас немає MINIFS і MAXIFS, ви можете використовувати прості формули масиву, засновані на функціях MIN і MAX, щоб отримати той самий результат. Для самої ранньої дати початку:
(=MIN(IF(data(Project)=G5,data(Start))))
Для останньої дати завершення:
(=MAX(IF(data(Project)=G5,data(End))))
Примітка: обидві формули - це формули масиву, і їх потрібно вводити з керуванням + shift + enter, в Excel 2019 або раніше. У програмі Excel 365 ви можете нормально вводити формули, оскільки формули масивів є власними.
В обох випадках функція IF використовується для "фільтрації" значень дати, як це:
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. Інші значення - FALSE, оскільки проект не є Omega. Оскільки MIN та MAX запрограмовані на ігнорування логічних значень TRUE та FALSE, вони працюють лише з іншими значеннями. MIN повертає найменшу (найранішу) дату, а MAX повертає найбільшу (найпізнішу) дату.
