=СРЗНАЧ(B2:B7) (по-английски AVERAGE) возвращает среднее чисел в B2:B7: складывает их и делит на их количество. Введите формулу в пустую ячейку или выберите Главная > Автосумма > Среднее, и Excel напишет её за вас. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой: =СРЗНАЧ(B2:B7).
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Ana | 78 |
| 3 | Ben | 92 |
| 4 | Chen | 65 |
| 5 | Dina | 88 |
| 6 | Eli | 71 |
| 7 | Fay | 84 |
| 8 | Average | 79.66666667 |
=СРЗНАЧ(B2:B7)Шесть баллов в сумме дают 478, а 478, делённое на 6, примерно 79,67. Замените балл Chen на 95, и среднее вырастет до 84,67. Чтобы показать меньше знаков после запятой, нажмите Главная > Уменьшить разрядность или округлите сам результат: =ОКРУГЛ(СРЗНАЧ(B2:B7);1) (разницу объясняет страница ОКРУГЛ).
Синтаксис СРЗНАЧ
=AVERAGE(number1, [number2], ...)
Как и СУММ, функция принимает диапазоны, ячейки и числа через точку с запятой: =СРЗНАЧ(B2:B7;D2:D7) усредняет два столбца вместе, а =СРЗНАЧ(B2:D2) усредняет строку. Текст, ИСТИНА/ЛОЖЬ в диапазоне и пустые ячейки пропускаются.
Пустые ячейки и нули
Отсюда берётся большинство неверных средних. СРЗНАЧ пропускает пустую ячейку, но 0 это число, и оно учитывается. Два столбца ниже одинаковы, кроме Ben: в столбце B его ячейка пустая, а в столбце C в ней 0.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Blank | Zero |
| 2 | Ana | 80 | 80 |
| 3 | Ben | 0 | |
| 4 | Chen | 70 | 70 |
| 5 | Dina | 90 | 90 |
| 6 | Average | 80 | 60 |
| 7 | Numbers counted | 3 | 4 |
=СРЗНАЧ(B2:B5)Столбец B даёт 80 (240, делённое на 3), а столбец C даёт 60 (240, делённое на 4). Ни один не ошибается; решите, что означает отсутствующее значение: «не сдавал» или «получил ноль». Формула, которая возвращает "", чтобы выглядеть пустой, пропускается так же, как пустая ячейка.
Среднее без нулей
Когда нули означают «нет данных» и не должны тянуть результат вниз, используйте СРЗНАЧЕСЛИ (AVERAGEIF) с условием "<>0" (не равно нулю):
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Sales | Ignoring zeros | Plain average | |
| 2 | Mon | 420 | 440 | 293.3333333 | |
| 3 | Tue | 0 | |||
| 4 | Wed | 380 | |||
| 5 | Thu | 0 | |||
| 6 | Fri | 510 | |||
| 7 | Sat | 450 |
=СРЗНАЧЕСЛИ(B2:B7;"<>0")D2 даёт 440, среднее четырёх дней с продажами; в русском Excel это =СРЗНАЧЕСЛИ(B2:B7;"<>0"). Обычное среднее в E2 около 293,33, потому что делит на шесть дней. Условие "<>0" заодно исключает пустые ячейки, ведь СРЗНАЧЕСЛИ их никогда не считает. Другие условия описаны на странице СРЗНАЧЕСЛИ.
Среднее трёх наибольших значений
НАИБОЛЬШИЙ (LARGE) возвращает k-е по величине значение. Передайте ему массив {1;2;3}, и он вернёт сразу три наибольших, а СРЗНАЧ их усреднит:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Round | Points | Top 3 average | Bottom 3 average | |
| 2 | 1 | 64 | 85.33333333 | 64.66666667 | |
| 3 | 2 | 81 | |||
| 4 | 3 | 77 | |||
| 5 | 4 | 90 | |||
| 6 | 5 | 58 | |||
| 7 | 6 | 85 | |||
| 8 | 7 | 72 |
Три наибольших это 90, 85 и 81, поэтому D2 даёт 85,33 (с округлением). Три наименьших это 58, 64 и 72, они дают 64,67. В Excel 2021 и Microsoft 365 =СРЗНАЧ(НАИБОЛЬШИЙ(B2:B8;ПОСЛЕД(5))) усредняет пять наибольших, и список набирать не нужно.
AVERAGEA: когда текст должен считаться нулём
AVERAGEA считает текст и ЛОЖЬ равными 0, а ИСТИНА равной 1. С баллами 80, 70 и словом «absent» в B2:B4:
=AVERAGE(B2:B4) returns 75 (80 + 70, divided by 2)
=AVERAGEA(B2:B4) returns 50 (80 + 70 + 0, divided by 3)
Используйте AVERAGEA, только если текстовая запись действительно означает ноль. Пустые ячейки обе функции пропускают.
Практика: среднее без нулей
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Day | Visits | Average (no zeros) | ||
| 2 | Mon | 120 | |||
| 3 | Tue | 0 | |||
| 4 | Wed | 135 | |||
| 5 | Thu | 98 | |||
| 6 | Fri | 0 | |||
| 7 | Sat | 160 | |||
| 8 | Sun | 142 |
Ваша очередь: В E2 посчитайте среднее посещений в B2:B8, не учитывая дни с 0 посещений.
Когда среднее вводит в заблуждение: используйте МЕДИАНА
Одно крайнее значение сильно сдвигает среднее. МЕДИАНА (MEDIAN) возвращает вместо этого среднее по порядку значение, а именно его обычно и имеют в виду под «типичным».
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Employee | Salary | Average | Median | |
| 2 | Ana | $42,000 | $78,800 | $47,000 | |
| 3 | Ben | $45,000 | |||
| 4 | Chen | $47,000 | |||
| 5 | Dina | $50,000 | |||
| 6 | Owner | $210,000 |
=СРЗНАЧ(B2:B6)Средняя зарплата $78,800, больше, чем получают четверо из пяти человек. Медиана $47,000. Когда несколько значений далеки от остальных, приводите медиану или оба показателя. Чтобы одни значения весили больше других, например экзамен, который идёт в зачёт дважды, смотрите средневзвешенное.
Часто задаваемые вопросы
Какая формула среднего значения в Excel?
=СРЗНАЧ(B2:B7). Она складывает числа диапазона и делит на их количество. Пустые ячейки и текст не попадают ни в сумму, ни в количество.
Учитывает ли СРЗНАЧ пустые ячейки?
Нет. Пустая ячейка пропускается, поэтому =СРЗНАЧ(B2:B5) с одной пустой ячейкой делит на 3. Ячейка с 0 учитывается и тянет среднее вниз.
Чем СРЗНАЧ отличается от МЕДИАНА в Excel?
=СРЗНАЧ(B2:B6) складывает значения и делит на их количество; =МЕДИАНА(B2:B6) возвращает среднее по порядку значение после сортировки. Одно крайнее значение сдвигает среднее, но не медиану: для 42000, 45000, 47000, 50000 и 210000 среднее равно 78800, а медиана 47000.
Как посчитать среднее трёх наибольших значений в Excel?
=AVERAGE(LARGE(B2:B8,{1,2,3})) (английская запись) усредняет три наибольших значения. Для трёх наименьших используйте SMALL вместо LARGE. Для пяти наибольших в Excel 2021 или Microsoft 365: =СРЗНАЧ(НАИБОЛЬШИЙ(B2:B8;ПОСЛЕД(5))).
Почему СРЗНАЧ возвращает #ДЕЛ/0!?
В диапазоне вообще нет чисел: он пуст или все значения текстовые, например числа, сохранённые как текст. Тогда СРЗНАЧ делит на ноль.
