Функция СРЗНАЧЕСЛИ в Excel (AVERAGEIF): как работает, пример и формула под вашу таблицу
Синтаксис в русском Excel
В английском Excel, Google Таблицах и внутри файла .xlsx
Аргументы
диапазон | где условие |
условие | чему соответствует |
[диапазон_усреднения] | что усредняем |
Пример
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Дата | Менеджер | Регион | Сумма | ||
| 2 | 01.09.2026 | Петрова | Москва | 184500 | 140350 | |
| 3 | 02.09.2026 | Сидоров | Тверь | 72300 | ||
| 4 | 03.09.2026 | Петрова | Тверь | 96200 | ||
| 5 | 04.09.2026 | Ким | Москва | 15000 |
Результат в ячейке F2 посчитан LibreOffice на сервере Формулника.
Частые ошибки
ни одна строка не подошла — ЕСЛИОШИБКА
Где работает
| Программа | СРЗНАЧЕСЛИ | |
|---|---|---|
| Excel 2010 | есть | |
| Excel 2013 | есть | |
| Excel 2016 | есть | |
| Excel 2019 | есть | |
| Excel 2021 | есть | |
| Excel 2024 | есть | |
| Excel 365 | есть | |
| LibreOffice Calc | есть | |
| Google Таблицы | есть | |
| Яндекс Таблицы | есть | |
| Р7-Офис | есть | |
| МойОфис | есть | |
| MOffice Sheets | есть | проверено |
Похожие функции
СРЗНАЧЕСЛИ: средний чек без нулей, средняя оценка класса, частичное совпадение
СРЗНАЧЕСЛИ усредняет числа только в тех строках, которые прошли условие, и пропускает пустые ячейки и текст в диапазоне усреднения. Ноль при этом считается — средний чек с «нулевыми» отменёнными заказами занизится. Исключить нули можно вторым условием: для одного условия по самим суммам хватит =СРЗНАЧЕСЛИ(D2:D500;">0"), для менеджера и «больше нуля» вместе нужна СРЗНАЧЕСЛИМН (есть с Excel 2007).
По шагам
- Определите диапазон условия (менеджеры, классы, категории) и диапазон чисел — одинаковой длины и с одной первой строкой.
- Условие пишите как текст в кавычках, ссылку на ячейку или склейку оператора со ссылкой:
">"&H1. - Если в данных есть нули, которые не должны влиять на среднее, добавьте условие
">0"через СРЗНАЧЕСЛИМН. - Оберните формулу в ЕСЛИОШИБКА, если в каком-то периоде может не оказаться ни одной строки.
- Сверьте результат с ручным расчётом: сумма по условию, делённая на количество по тому же условию.
Средняя оценка класса и предмета
В журнале на одном листе ученики трёх классов: столбец B — класс («7А», «7Б», «7В»), D — оценка за контрольную, отсутствующим стоит «н». Средний балл 7Б посчитается одной формулой, и «н» её не испортит — текст пропускается. Если оценки стоят у всех, а класс нужно выбрать в ячейке H2, замените "7Б" на ссылку. Результат обычно округляют до сотых: =ОКРУГЛ(СРЗНАЧЕСЛИ(B2:B90;H2;D2:D90);2).
=СРЗНАЧЕСЛИ(B2:B90;"7Б";D2:D90)Средний чек менеджера без отменённых заказов
У Петровой пять заказов: 12 000, 8 000, 0 (отмена), 15 000 и 0 (отмена). СРЗНАЧЕСЛИ по фамилии даст 7 000, а реальный средний чек — 11 667. Второе условие «сумма больше нуля» возможно только в СРЗНАЧЕСЛИМН, где порядок аргументов другой: сначала усредняемый диапазон, потом пары «диапазон — условие».
=СРЗНАЧЕСЛИМН(D2:D500;B2:B500;"Петрова";D2:D500;">0")Частичное совпадение: все товары со словом «кабель»
Звёздочка заменяет любые символы: условие "*кабель*" подберёт «Кабель ВВГнг 3х2,5» и «Удлинитель с кабелем 5 м». Регистр не важен. Если ищете сам символ звёздочки или вопроса — поставьте перед ним тильду. Для числовых кодов подстановочные знаки не работают: «*101*» не найдёт число 21015, только текст.
=СРЗНАЧЕСЛИ(A2:A800;"*кабель*";E2:E800)Как пишется условие
| Нужно | Условие |
|---|---|
| Равно тексту | "Москва" |
| Больше числа из ячейки | ">"&H1 |
| Не равно | "<>Тверь" |
| Содержит слово | "*кабель*" |
| Начиная с даты | ">="&ДАТА(2026;9;1) |
| Только непустые | "<>" |
Частые ошибки
- Среднее посчитано по чужим строкам, а ошибки нет — диапазоны разной длины или сдвинуты:
B2:B90иD3:D91. Excel берёт левую верхнюю ячейку усредняемого диапазона и растягивает его до размера диапазона условия, поэтому сдвиг даёт тихую ошибку. - #ДЕЛ/0! у нового менеджера — ни одной подходящей строки.
=ЕСЛИОШИБКА(СРЗНАЧЕСЛИ(B2:B500;H2;D2:D500);"нет продаж")покажет понятный текст вместо ошибки — у этого менеджера пока нет ни одной строки в выгрузке. - Среднее по средним не совпадает с общим средним — если в классах разное число учеников, средний балл школы нельзя считать как среднее трёх средних. Усредняйте исходные оценки или взвешивайте по количеству.
Вопросы и ответы
Чем СРЗНАЧЕСЛИ отличается от СРЗНАЧЕСЛИМН?
СРЗНАЧЕСЛИ принимает одно условие, СРЗНАЧЕСЛИМН — до 127 пар. Порядок аргументов разный: в СРЗНАЧЕСЛИМН усредняемый диапазон идёт первым.
Как посчитать средневзвешенную цену по условию?
СРЗНАЧЕСЛИ этого не умеет. Делите сумму выручки по условию на сумму количества по тому же условию: =СУММЕСЛИ(B2:B500;"Москва";E2:E500)/СУММЕСЛИ(B2:B500;"Москва";C2:C500).
Можно ли не указывать третий аргумент?
Да, если условие проверяется по тем же числам, которые усредняются: =СРЗНАЧЕСЛИ(D2:D500;">10000") — средняя сумма среди крупных сделок.
Как сделать таблицу «средний чек по каждому менеджеру»?
В Microsoft 365 и Excel 2021 список менеджеров даёт =УНИК(B2:B500), а рядом =СРЗНАЧЕСЛИ(B2:B500;F2#;D2:D500) посчитает всех сразу. В Excel 2016 скопируйте список, удалите дубликаты и протяните формулу.
Можно ли посчитать средние по моему журналу или выгрузке без ручной настройки?
Да, Формулник по описанию «средний балл по каждому классу и предмету» или «средний чек по менеджерам без отмен» сам найдёт столбцы и добавит таблицу с формулами.
- Справка Microsoft: функция СРЗНАЧЕСЛИ
- Справка Microsoft: функция СРЗНАЧЕСЛИМН