Быстрый анализ данных в Excel: от максимума до сложных совпадений

Иван Корнев·13.04.2026·⏱4 мин

Чтобы найти максимальное значение в диапазоне, используйте функцию =MAX(диапазон), для среднего арифметического — =AVERAGE(диапазон), а для поиска совпадений по условиям — связку =FILTER или =XLOOKUP. Эти инструменты позволяют мгновенно обрабатывать большие таблицы без ручного перебора. Ниже приведены конкретные формулы и сценарии их применения для решения типовых задач аналитики.

Краткий ответ:

  • Максимум: =MAX(A2:A100)
  • Среднее: =AVERAGE(A2:A100)
  • Совпадение (поиск значения): =XLOOKUP(что_ищем; где_ищем; откуда_вернуть)
  • Совпадение (фильтрация списка): =FILTER(таблица; условие)

Поиск максимального и минимального значений

Функция MAX сканирует указанный диапазон и возвращает наибольшее числовое значение. Она автоматически игнорирует пустые ячейки и текст, но учитывает логические значения (ИСТИНА как 1, ЛОЖЬ как 0), если они введены непосредственно в аргументы функции.

Базовый синтаксис

=MAX(B2:B100)

Эта формула вернет самую высокую цифру из столбца B. Аналогично работает МИН (MIN) для поиска наименьшего значения.

Условный максимум

Часто требуется найти максимум не во всем столбце, а только среди строк, отвечающих определенному критерию (например, максимальная продажа конкретного менеджера).

Вариант для старых версий Excel (массивная формула): Нажмите Ctrl + Shift + Enter после ввода:

=MAX(IF(A2:A100="Иванов"; B2:B100))

Вариант для Excel 365/2021 (динамические массивы): Просто введите формулу, она сама обработает массив:

=MAX(FILTER(B2:B100; A2:A100="Иванов"))

Если подходящих значений нет, функция вернет ошибку. Чтобы избежать этого, добавьте третье числовое значение в MAX: =MAX(0; FILTER(...)).

Расчет среднего арифметического

Функция AVERAGE (СРЗНАЧ) суммирует числа в диапазоне и делит сумму на их количество. Пустые ячейки игнорируются, а ячейки со значением 0 учитываются в расчете, что может занизить результат.

Стандартное использование

=AVERAGE(C2:C500)

Среднее по условию

Для исключение нулей или фильтрации по категориям используйте AVERAGEIF (СРЗНАЧЕСЛИ) или AVERAGEIFS (СРЗНАЧЕСЛИМН).

Пример: Найти средний чек только для клиентов со статусом "Активен":

=AVERAGEIF(D2:D500; "Активен"; E2:E500)

Где D — столбец со статусами, а E — столбец с суммами чеков.

Лайфхак: Если нужно исключить нули из обычного среднего без условий, используйте формулу массива: =AVERAGE(IF(C2:C100<>0; C2:C100)) В новых версиях Excel это работает сразу, в старых требует подтверждения через Ctrl+Shift+Enter.

Поиск совпадений и сравнение данных

Задача «найти совпадения» может означать поиск конкретного значения в списке или выявление общих элементов между двумя таблицами.

Точный поиск одного значения

Забудьте о ВПР (VLOOKUP). Используйте современную функцию XLOOKUP (ПРОСМОТРХ). Она надежнее, быстрее и не ломается при добавлении столбцов.

Синтаксис:

=XLOOKUP(искомое_значение; просматриваемый_массив; возвращаемый_массив)

Пример: Найти цену товара по его артикулу:

=XLOOKUP("A-123"; A2:A100; B2:B100)

Поиск всех совпадений (Фильтрация списка)

Если одному критерию соответствует несколько строк (например, все заказы клиента "ООО Ромашка"), XLOOKUP вернет только первый. Используйте FILTER (ФИЛЬТР).

Пример: Вывести список всех товаров, проданных в регионе "Север":

=FILTER(A2:C100; C2:C100="Север"; "Нет данных")

Эта формула динамически создаст новую таблицу только с нужными строками.

Проверка наличия значения в списке

Чтобы просто понять, есть ли значение из столбца А в столбце В (вернуть ИСТИНА/ЛОЖЬ):

=ISNUMBER(MATCH(A2; B:B; 0))

Или в русской версии:

=ЕЧИСЛО(ПОИСКПОЗ(A2; B:B; 0))

Сравнение подходов: какую функцию выбрать?

ЗадачаРекомендуемая функцияПочему
Найти одно число (макс/мин)MAX / MINБыстро, не требует условий
Среднее с одним условиемAVERAGEIFПроще и быстрее фильтрации
Среднее с несколькими условиямиAVERAGEIFSЕдинственный встроенный вариант
Найти одну запись по ключуXLOOKUPГибче и устойчивее ВПР
Выгрузить список записейFILTERВозвращает массив, а не одно значение
Проверить существованиеMATCH + ISNUMBERЛегковесная проверка без выгрузки данных

Частые ошибки и способы их устранения

  1. #ДЕЛ/0! (#DIV/0!) при расчете среднего.

    • Причина: В диапазоне нет чисел или все они отфильтрованы как не подходящие.
    • Решение: Оберните формулу в IFERROR: =IFERROR(AVERAGE(...); 0).

  2. Неверный результат из-за формата данных.

    • Причина: Числа сохранены как текст (часто бывает при выгрузке из 1С или банковских отчетов). Функции MAX и AVERAGE игнорируют текст.
    • Решение: Выделите столбец → Данные → Текст по столбцам → Готово. Или используйте умножение на 1 в формуле.

  3. Формула массива не работает.

    • Причина: Используется старый синтаксис в новой версии или наоборот.
    • Решение: В Excel 365 формулы с FILTER и UNIQUE вводятся обычным Enter. Если видите ошибку #СПОЛ! (#SPILL!), освободите место ниже формулы для вывода результатов.

FAQ

Как найти среднее значение, игнорируя самые высокие и самые низкие показатели? Используйте функцию TRIMMEAN (УСЕЧСРЕД). Например, =TRIMMEAN(A2:A100; 0,2) удалит по 10% экстремальных значений сверху и снизу перед расчетом среднего.

Можно ли искать совпадения частично (по части слова)? Да. В XLOOKUP и FILTER используйте подстановочные знаки. Пример: =XLOOKUP("*Москва*"; A2:A100; B2:B100) найдет любое значение, содержащее слово "Москва".

Что делать, если FILTER возвращает ошибку #КАЛЬК! (#CALC!)? Это значит, что ни одна строка не удовлетворяет условию. Добавьте третий аргумент в функцию, чтобы вывести сообщение вместо ошибки: =FILTER(...; "..."; "Совпадений не найдено").