Формула Excel: Найти самую раннюю и самую позднюю дату для каждой группы
В этом руководстве приведены формулы для поиска самой ранней и самой поздней даты для каждой группы в Excel.
Если вы хотите повторить действия из этого руководства, пожалуйста, скачайте пример таблицы.
Общая формула:
Найти самую раннюю дату для каждой группы
| {MIN(IF(group_rng=group,startD_rng))} |
Найти самую позднюю дату для каждой группы
| {MAX(IF(group_rng=group,startD_rng))} |
Синтаксис и аргументы
| Group_rng: диапазон ячеек, содержащий все названия групп. |
| Group: группа, для которой требуется определить самую раннюю и самую позднюю дату. |
| StartD_rng: диапазон ячеек, в котором указаны все начальные даты групп. |
Возвращаемое значение
Формулы возвращают дату в виде числового значения. Поскольку дата в Excel хранится как число, чтобы увидеть дату в привычном формате, после применения формулы отформатируйте ячейку в нужный вам формат даты.
Как работает эта формула
В приведённой таблице столбец B3:B8 содержит названия отделов, столбец C3:C8 — различные проекты в отделах, столбец D3:D8 — даты начала каждого проекта, а даты окончания для каждой группы находятся в столбце E3:E8. Чтобы определить самую раннюю дату начала и самую позднюю дату окончания для каждого отдела (A и B), используйте следующую формулу:
Найти самую раннюю дату
| =MIN(IF($B$3:$B$8=B11,$D$3:$D$8)) |
Нажмите Shift + Ctrl + Enter чтобы получить корректный результат массива.
Затем перетащите маркер автозаполнения вниз на нужные ячейки с этой формулой.
Далее, оставив выбранные ячейки с формулой, нажмите Ctrl +1 чтобы открыть Формат ячеек диалоговое окно, затем на вкладке Число выберите Дата в разделе Категория списка, затем выберите нужный формат даты справа в разделе Тип и подтвердите.
Нажмите OK, теперь результаты отображаются как даты.
Найти самую позднюю дату для каждого отдела
| =MAX(IF($B$3:$B$8=B11,$E$3:$E$8)) |
Нажмите Shift + Ctrl + Enter затем перетащите маркер автозаполнения, чтобы применить формулу массива к другим ячейкам, и отформатируйте их как даты.
Пояснение
Функция IF: проверяет выполнение определённого условия.
В формуле массива {IF($B$3:$B$9=B11,$D$3:$D$8)} она возвращает
{43597;FALSE;43952;43802;FALSE;FALSE;FALSE}
Функция MIN: определяет минимальное значение в диапазоне или массиве.
={MIN (IF($B$3:$B$8=B11,$D$3:$D$8))}
={MIN(43597;FALSE;43952;43802;FALSE;FALSE)}
=43597
Функция MAX: определяет наибольшее значение в диапазоне или массиве.
={MAX(IF($B$3:$B$8=B11,$E$3:$E$8))}
={MAX(44158;FALSE;44654;44013;FALSE;FALSE)}
=44684
Примечание
Если вы используете Excel2019 или Excel365, вы можете воспользоваться функциями MINIFS и MAXIFS для быстрого поиска самой ранней и самой поздней даты для каждой группы.
Общая формула
Найти самую раннюю дату для группы
| MINIFS(startD_rng,group_rng,group) |
Найти самую позднюю дату для группы
| MAXIFS(startD_rng,group_rng,group) |
Используйте приведённые ниже формулы для данного примера:
| =MINIFS($D$3:$D$8,$B$3:$B$8,B11) |
| =MAXIFS($E$3:$E$8,$B$3:$B$8,B11) |
Связанные формулы
- Посчитать количество дней в месяце
В этом руководстве приведены формулы для подсчёта общего количества дней в месяце на основе заданной даты. - Посчитать количество дней до даты истечения срока
Чтобы рассчитать количество дней между сегодняшней датой и каждой датой истечения срока в Excel с помощью формулы - Показать текущую дату или время
В этом руководстве приведены формулы для отображения текущей даты или времени в Excel. - Создать недельный диапазон дат
В этом руководстве приведена формула для быстрого получения недельного диапазона дат в Excel.
Связанные функции
- Функция Excel WEEKNUM
Возвращает номер недели для заданной даты в году - Функция Excel WORKDAY
Добавляет рабочие дни к заданной начальной дате и возвращает рабочий день - Функция Excel MONTH
MONTH используется для получения номера месяца (от1 до12) из даты - Функция Excel DAY
Функция DAY возвращает номер дня (от1 до31) из даты - Функция Excel NOW
Получить текущие дату и время
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что то, что вам нужно, находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите сотни кликов мышью каждый день, попрощайтесь с болью в руке от использования мышки.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Привносит эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.