Как найти максимальное или минимальное значение в определенном диапазоне дат (между двумя датами) в Excel?
В повседневном анализе данных, особенно при работе с транзакционными записями или временными рядами данных, вам может часто потребоваться выявить наибольшее или наименьшее значение, которое встречается в конкретный временной период. Например, представьте, что у вас есть таблица, как показано на скриншоте ниже, и вы хотите определить максимальное или минимальное значение между двумя датами — например, с 2016/7/1 по 2016/12/1. Это типичное требование при генерации отчетов за конкретные периоды, сравнении месячной производительности или отслеживании пиков и спадов в данных. В этой статье мы проведем вас через несколько практических решений с использованием формул Excel, кода VBA и встроенных функций, помогая быстро и точно извлечь нужные значения.
➤ Найти максимальное или минимальное значение в определенном диапазоне дат с помощью массивных формул
➤ Код VBA: Автоматически найти максимальное или минимальное значение в указанном диапазоне дат
➤ Другие встроенные методы Excel: Использование сводной таблицы для фильтрации и отображения макс/мин по диапазону дат
Найти макс или мин значение в определенном диапазоне дат с помощью массивных формул
Один из простых подходов заключается в использовании массивных формул в Excel, которые позволяют вычислять значения на основе нескольких критериев, таких как проверка, попадают ли даты в указанный диапазон. Этот метод подходит для данных умеренного размера и для пользователей, знакомых с вводом формул.
Предположим, что ваш рабочий лист содержит даты в столбце A (A5:A17) и соответствующие значения в столбце B (B5:B17), а начальная и конечная даты диапазона введены в ячейки B1 и D1 соответственно.
1. Выберите пустую ячейку, где вы хотите отобразить результат (например, E2).
Нахождение максимального значения между 2016/7/1 и 2016/12/1:
2. Введите следующую формулу в выбранную ячейку. После редактирования нажмите Ctrl + Shift + Enter (не просто Enter), чтобы Excel распознал её как массивную формулу:
=MAX(IF((A5:A17<=$D$1)*(A5:A17>=$B$1),B5:B17,""))Эта формула проверяет, какие даты попадают между вашими начальной и конечной датой, и только значения из соответствующих строк учитываются для расчета максимума.
Нахождение минимального значения между 2016/7/1 и 2016/12/1:
3. Чтобы найти минимум в том же диапазоне дат, используйте аналогичный подход. Введите следующую формулу (и снова подтвердите с помощью Ctrl + Shift + Enter):
=MIN(IF((A5:A17<=$D$1)*(A5:A17>=$B$1), B5:B17, ""))Эта формула работает таким же образом, но возвращает минимальное значение, соответствующее вашим критериям дат.
Примечания:
- В приведенных выше примерах A5:A17 — это диапазон, содержащий ваши даты, $B$1 — это начальная дата, $D$1 — это конечная дата, а B5:B17 — это диапазон значений, которые вы хотите оценить. Настройте эти ссылки в соответствии с вашими реальными данными.
- Убедитесь, что два диапазона, на которые вы ссылаетесь, имеют одинаковую длину — иначе формула может вызвать ошибки.
- Удостоверьтесь, что ваши записи дат отформатированы как даты, а не как текст, иначе формула может не работать должным образом.
Советы:
- Если вы работаете с Office 365 или Excel 2021 и более поздними версиями, вы можете использовать функции MAXIFS и MINIFS для более простых расчетов на основе критериев.
- Если формула возвращает 0 или пустое значение неожиданно, проверьте, перекрывается ли ваш диапазон дат с доступными датами данных, и проверьте наличие незамеченных пустых ячеек.
Код VBA: Автоматически найти макс или мин значение в указанном диапазоне дат
Для пользователей, работающих с большими наборами данных, которым необходимо часто повторять эту задачу, или для тех, кто ищет автоматизацию отчетности, решение с помощью макроса VBA может эффективно находить макс или мин значение в выбранном диапазоне дат. С помощью VBA можно запросить у пользователя выбор соответствующих диапазонов и установку дат каждый раз, что делает его идеальным для динамических приложений или интеграции в продвинутые рабочие процессы.
1. Перейдите в Разработчик > Visual Basic. В открывшемся окне редактора VBA нажмите Вставить > Модуль, затем скопируйте и вставьте следующий код в новый модуль:
Sub FindMaxMinInDateRange_Robust()
Dim ws As Worksheet
Dim dateRange As Range, valueRange As Range
Dim startCell As Range, endCell As Range
Dim startDate As Date, endDate As Date
Dim i As Long
Dim d As Date, v As Variant
Dim hasHit As Boolean
Dim maxV As Double, minV As Double
Const TITLE As String = "KutoolsforExcel"
On Error GoTo FailFast
Set ws = ActiveSheet
Set dateRange = Application.InputBox("Select the DATE range:", TITLE, Type:=8)
If dateRange Is Nothing Then Exit Sub
Set valueRange = Application.InputBox("Select the VALUE range (same rows as date range):", TITLE, Type:=8)
If valueRange Is Nothing Then Exit Sub
If dateRange.Rows.Count <> valueRange.Rows.Count Then
MsgBox "Date range and value range must have the SAME number of rows.", vbExclamation, TITLE
Exit Sub
End If
Set startCell = Application.InputBox("Select START date cell:", TITLE, Type:=8)
If startCell Is Nothing Then Exit Sub
Set endCell = Application.InputBox("Select END date cell:", TITLE, Type:=8)
If endCell Is Nothing Then Exit Sub
If Not IsDate(startCell.Value) Or Not IsDate(endCell.Value) Then
MsgBox "Start/End cell must contain valid dates.", vbExclamation, TITLE
Exit Sub
End If
startDate = CDate(startCell.Value)
endDate = CDate(endCell.Value)
If startDate > endDate Then
Dim tmp As Date
tmp = startDate: startDate = endDate: endDate = tmp
End If
For i = 1 To dateRange.Rows.Count
If IsDate(dateRange.Cells(i, 1).Value) Then
d = CDate(dateRange.Cells(i, 1).Value)
If d >= startDate And d <= endDate Then
v = valueRange.Cells(i, 1).Value
If IsNumeric(v) And Not IsEmpty(v) Then
If Not hasHit Then
maxV = CDbl(v): minV = CDbl(v)
hasHit = True
Else
If CDbl(v) > maxV Then maxV = CDbl(v)
If CDbl(v) < minV Then minV = CDbl(v)
End If
End If
End If
End If
Next i
If hasHit Then
MsgBox "Max value in range: " & maxV & vbCrLf & _
"Min value in range: " & minV, vbInformation, TITLE
Else
MsgBox "No rows matched the date range (or values were non-numeric).", vbExclamation, TITLE
End If
Exit Sub
FailFast:
MsgBox "Something went wrong: " & Err.Description, vbExclamation, TITLE
End Sub
2. Чтобы запустить макрос, нажмите 
кнопку в редакторе VBA (или нажмите F5). Следуйте инструкциям, чтобы выбрать диапазоны дат и значений, ввести начальную и конечную даты. Результирующие максимальные и минимальные значения для вашего указанного интервала дат будут отображены в диалоговом окне.
Советы:
- Убедитесь, что выбранные диапазоны дат и значений содержат одинаковое количество строк и соответствуют друг другу напрямую.
- Этот подход особенно полезен для обработки больших списков или автоматизации повторяющихся расчетов максимума/минимума на основе изменяющихся критериев.
- Если выбран пустой или недействительный диапазон, или если вход даты некорректно отформатирован, код может не дать правильного результата — дважды проверьте свои выборы перед запуском.
Другие встроенные методы Excel: Используйте Сводную таблицу для фильтрации и отображения макс/мин по диапазону дат
Если вы предпочитаете не использовать формулы или код, использование функции Сводной таблицы Excel предлагает интерактивный, свободный от формул метод фильтрации данных по дате и отображения сводных значений, таких как максимум или минимум. Это решение подходит для пользователей, которым нужно исследовать данные, генерировать отчеты или легко настраивать критерии с помощью графического интерфейса.
1. Выберите свою таблицу (включая даты и значения), затем перейдите на вкладку Вставка и нажмите Сводная таблица.
2. В диалоговом окне Создание сводной таблицы выберите место, куда вы хотите поместить сводную таблицу, и нажмите OK.
3. В панели Поля сводной таблицы перетащите поле Дата в область Строки, а поле Значения (то, для которого вы хотите найти макс/мин) в область Значений. По умолчанию будет показана сумма; нажмите поле в Значения, выберите Настройки поля значений и измените на Макс или Мин по необходимости.
4. Чтобы отфильтровать по определенному диапазону дат, нажмите выпадающее меню меток строк для поля Дата, выберите Фильтры дат > Между…, затем укажите начальную и конечную даты (например, 2016/7/1 до 2016/12/1) и нажмите OK.
Теперь сводная таблица покажет максимальное или минимальное значение для каждой даты в указанном диапазоне. Если вам нужно только одно наибольшее или наименьшее значение в этом диапазоне, вы можете дополнительно отфильтровать или визуально просмотреть сводный результат.
Примечания:
- Убедитесь, что все ячейки в столбце Дата являются реальными датами (не текстом). Смешанные форматы могут привести к тому, что фильтры пропустят строки.
- Если исходные данные изменились, щелкните правой кнопкой мыши по сводной таблице и выберите Обновить для обновления результатов.
- В зависимости от вашего макета Excel может группировать даты по месяцу/кварталу/году. При необходимости щелкните правой кнопкой мыши по дате в сводной таблице и выберите Разгруппировать (или Группировать… для установки желаемого уровня).
- Для очень больших наборов данных размещение сводной таблицы на новом листе может улучшить читаемость и производительность.
Советы:
- Добавьте Настройщик для поля Дата (Анализ сводной таблицы > Вставить Настройщик), чтобы интерактивно изменять диапазоны.
- Нужно одно значение макс/мин по всему отфильтрованному диапазону? После фильтрации отсортируйте столбец Значений или добавьте второй столбец Значений и переключите его на Макс/Мин.
- Объедините со Сводной диаграммой для визуального сводного отчета, который обновляется вместе с вашими фильтрами.
Этот метод позволяет избежать ручного ввода формул и обеспечивает динамическое взаимодействие — отлично подходит для презентаций или многопользовательских сценариев. Для высококастомизированных выходных данных или массовой автоматизации на многих листах рассмотрите подходы с использованием формул или VBA.
Связанные статьи:
- Как найти позицию первого/последнего числа в текстовой строке в Excel?
- Как найти первый или последний пятницу каждого месяца в Excel?
- Как найти первое, второе или n-е совпадение с помощью vlookup в Excel?
- Как найти значение с самой высокой частотой в диапазоне в Excel?
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек