KutoolsforOffice — Одно решение — пять мощных инструментов.Меньше усилий — больше результата.

Как найти максимальное или минимальное значение в определённом диапазоне дат (между двумя датами) в Excel?

АвторSiluviaДата изменения

В повседневной работе с анализом данных — особенно при обработке транзакционных записей или временных рядов — часто возникает необходимость определить максимальное или минимальное значение за конкретный период времени. Например, у вас есть таблица, подобная приведённой на скриншоте ниже, и вы хотите найти наибольшее или наименьшее значение между двумя датами, скажем, с 01,07.2016 по 01,12.2016. Такая задача регулярно встречается при подготовке отчётов за заданные периоды, сравнении ежемесячных показателей или анализе пиков и спадов в данных. Эта статья поможет вам освоить несколько практических способов — с использованием формул Excel, кода VBA и встроенных функций — чтобы быстро и точно получить нужный результат.

Снимок экрана, показывающий таблицу Excel с датами и значениями для вычисления максимального или минимального значения в диапазоне дат

Поиск максимума или Минимальное значение в определённом Диапазон дат с помощью формул массива

Один из простых способов — использовать формулы массива в Excel, которые позволяют выполнять расчёты по нескольким критериям, например проверять, попадают ли даты в ваш ограниченный диапазон. Этот метод идеально подходит для работы с данными умерённого объёма и пользователям, уверенно владеющим формулами.

Предположим, что список дат находится в столбце A (A5:A17), а соответствующие значения — в столбце B (B5:B17), при этом начальная и конечная даты указаны в ячейках B1 и D1 соответственно.

1. Выберите пустую ячейку, в которой вы хотите отобразить результат (например, E2).

Поиск Максимальное значение между 01,07.2016 и 01,12.2016:

2. Введите следующую формулу в выбранную ячейку. После редактирования нажмите Ctrl+Shift+Enter (а не просто Enter), чтобы Excel распознал её как формулу массива:

=MAX(IF((A5:A17=$B$1),B5:B17,""))

Эта формула определяет, какие даты попадают в диапазон между начальной и конечной датами, и учитывает только значения из соответствующих строк для вычисления максимального значения.

Снимок экрана, показывающий результат нахождения максимального значения в диапазоне дат с использованием формул массива в Excel

Поиск Минимальное значение между 01,07.2016 и 01,12.2016:

3. Чтобы найти минимум в том же диапазоне дат, используйте аналогичный подход. Введите следующую формулу (и снова подтвердите ввод комбинацией)Ctrl+Shift+Enter):

=MIN(IF((A5:A17=$B$1), B5:B17, ""))

Эта формула работает аналогично, но возвращает минимальное значение, соответствующее вашим критериям по датам.

Снимок экрана, показывающий результат нахождения минимального значения в диапазоне дат с использованием формул массива в Excel

Примечания:

  • В приведённых выше примерах A5:A17 — это диапазон, содержащий ваши даты, $B$1 — дата начала, $D$1 — конечная дата, а B5:B17 — диапазон значений для анализа. Замените эти ссылки на соответствующие вашим данным.
  • Убедитесь, что оба указанных диапазона имеют одинаковую длину — иначе формула может вернуть ошибку.
  • Убедитесь, что записи с датами отформатированы именно как даты, а не как текст — иначе формула может работать некорректно.

Советы:

  • Если вы используете Office 365 или Excel 2021 и более поздние версии, вы можете использовать функции МАКСЕСЛИ и МИНЕСЛИ для ещё более простых расчётов по заданным критериям.
  • Если формула неожиданно возвращает значение 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. В диалоговом окне Создание сводной таблицы выберите место размещения сводной таблицы и нажмите ОК.

3. В области Поля сводной таблицы перетащите поле Дата в область Строки, а поле Значения (то, для которого вы ищете максимум или минимум) — в область Значения. По умолчанию отображается Сумма. Щёлкните по полю в области Значения, выберите Параметры поля → Настройки полей и измените агрегацию на Макс или Мин по необходимости.

4. Чтобы отфильтровать по определённому диапазону дат, щёлкните раскрывающийся список в метках строк для поля Дата, выберите Фильтры по дате > Между…, укажите начальную и конечную даты (например,)01,07.2016 и 01,12.2016) и нажмите ОК.

Сводная таблица теперь отображает максимальное или минимальное значение для каждой даты в пределах вашего ограниченного диапазона. Если вам нужно всего одно наибольшее или наименьшее значение за весь указанный период, вы можете дополнительно отфильтровать данные или визуально проанализировать сводный результат.

Примечания:

  • Убедитесь, что все ячейки в столбце Дата содержат настоящие даты (а не текст). Смешанные форматы могут привести к тому, что фильтры пропустят строки.
  • Если исходные данные изменились, щёлкните правой кнопкой мыши по сводной таблице и выберите команду Обновить, чтобы обновить результаты.
  • В зависимости от макета Excel может группировать даты по месяцам, кварталам или годам. Если нужно изменить группировку, щёлкните правой кнопкой мыши по дате в сводной таблице и выберите Разгруппировать(или)Группировать…, чтобы задать нужный уровень группировки).
  • Для очень больших наборов данных размещение сводной таблицы на отдельном новом листе может повысить читаемость и производительность.

Советы:

  • Добавьте срездля поля «Дата» (Анализ сводной таблицы >)Вставить срез), чтобы интерактивно изменять диапазоны.
  • Нужно найти общий максимум или минимум по всему диапазону фильтрации? После фильтрации отсортируйте столбец со значениями или добавьте второе поле значений и измените его на Макс/Мин.
  • Свяжите её со сводной диаграммой, чтобы получить визуальное резюме, которое автоматически обновляется при изменении фильтров.

Этот метод позволяет избежать ручного ввода формул и обеспечивает динамическое взаимодействие — отлично подходит для презентаций или сценариев совместной работы. Для получения высокоиндивидуальных результатов или автоматизации пакетной обработки множества листов рекомендуется использовать формулы или подходы на основе VBA.

Связанные статьи:

Лучшие инструменты повышения продуктивности в Office

🤖KUTOOLS AI Помощник: Преобразуйте Анализ данных с помощью:Интеллектуального выполнения   |  Генерации кода|  Создания пользовательские формулы  |  Анализа данных и построения диаграмм|  Вызова Расширенные функции…
Популярные функции:Поиск, выделение или Отметить дубликаты   |  Удалить пустые строки   |  Объединить столбцы или ячеек без потери данных   |  Округление без использования формул…
Супер ПОИСК:VLookup по нескольким критериям  |  VLookup по нескольким значениям  |   VLookup по нескольким листам   |   Распознавание нечетких соответствий…
Расширенный раскрывающийся список:Быстрое создание выпадающего списка   |  Зависимый выпадающий список   |  Выпадающий список с множественным выбором…
Управление столбцами:Добавление заданного количества столбцов|Перемещение столбцов|Переключение видимости скрытых столбцов|Сравнение диапазонов и столбцов…
Избранные функции:Сетка фокусировки   |  Просмотр дизайна   |Улучшенная строка формулы   | Управление рабочими книгами и листами   |  Библиотека ресурсов(автотекст)|  Выбор даты   |  Объединить листы  |  Шифрование/Расшифровать ячейки   | Отправка писем по списку   |  Супер фильтр   |   Специальный фильтр(Фильтр ячеек с жирным шрифтом/курсив/зачёркивание…) …
Лучшие наборы инструментов 15:12 Текстовыеинструменты(Добавить текст,Удалить определенные символы, …)|   50+Типыдиаграмм(Диаграмма Ганта, …)|   40+ Практические формулы(Рассчитать возраст на основе даты рождения, …)|   19 Инструментывставки(Вставить QR-код,Вставка изображения по пути, …)|   12 Инструментыпреобразования(Преобразовать в слова,Конвертация валют, …)|   7 Объединить и разделитьИнструменты(Расширенное объединение строк,Разделить ячейки, …)|… и многое другое
Используйте Kutools на предпочитаемом языке — поддержка английского, испанского, немецкого, французского, китайского и ещё 40+ языков!

Раскройте весь потенциал 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.

ExcelWordOutlookTabsPowerPoint
  • Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
  • Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
  • Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
  • 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
  • Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек