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

СРЗНАЧ учитывает числовые нули и пропускает пустые ячейки диапазона. От этого меняется делитель. Измеренный ноль — наблюдение, а отсутствующая запись — неизвестное значение.

Автор Coru · Опубликовано 2026-10-23 · Обновлено 2026-10-23

Шесть часов, четыре записи

Представьте библиотеку, где в течение шести дней считают посещения в один и тот же час. Получились записи: 4, 0, 8, пропуск, 12, пропуск. Это вымышленные учебные данные.

Ноль во вторник означает, что за целый час наблюдения никто не пришёл. Записи за четверг и субботу потеряны: количество посещений неизвестно. Во все шесть выбранных часов библиотека работала. Поэтому отсутствие записи не означает, что библиотека была закрыта или что никто не пришёл.

Посещения библиотеки за одночасовые периоды: Пн 4, Вт 0, Ср 8, Чт пропуск, Пт 12, Сб пропуск. Учтены четыре часа из шести; среднее по ним — 24 ÷ 4 = 6. — Вымышленные учебные данные. За час наблюдения во вторник записали 0 посещений; за четверг и субботу данных нет. За четыре учтённых часа записано 24 посещения: в среднем 6 посещений за учтённый час. Среднее по всем шести часам неизвестно. Иллюстрация: Coru — оригинальная схема. Авторские текст и геометрия SVG Coru. Локальный рендеринг, вымышленные учебные данные.

Перенесите пример в Excel

Скопируйте блок с разделителями-табуляциями в ячейку A1 пустого листа. В столбце B находятся числа посещений, в столбце C — пояснения. Ячейки B5 и B7 должны остаться действительно пустыми. Не вводите в них тире, пробел или слово «пропуск».

Если при вставке всё попало в один столбец, заполните B2:B7 вручную: 4, 0, 8, оставить пустой, 12, оставить пустой. Четыре значения введите как числа.

Учебные данные: вставить в A1

tsv

День	Посещения	Запись
Пн	4	Наблюдение
Вт	0	Наблюдение; посещений не было
Ср	8	Наблюдение
Чт		Запись потеряна
Пт	12	Наблюдение
Сб		Запись потеряна

Два пустых поля в столбце «Посещения» оставлены намеренно. Все шесть периодов имеют одинаковую длительность.

Проверьте делитель, а не только ответ

Введите каждую формулу ниже в отдельную ячейку за пределами A1:C7. СУММ вернёт 24, СЧЁТ — 4, СРЗНАЧ — 6. Расчёт: (4 + 0 + 8 + 12) ÷ 4 = 6 посещений за час с сохранённой записью.

СЧЁТ подсчитывает числовые ячейки диапазона, включая ноль. СРЗНАЧ использует четыре числовых наблюдения; две пустые ячейки не добавляют ни значения, ни наблюдения. Ноль ничего не прибавляет к сумме, но прибавляет единицу к делителю.

Три проверки с русскими именами функций

excel

=СУММ(B2:B7)
=СЧЁТ(B2:B7)
=СРЗНАЧ(B2:B7)

Ожидаемые результаты по порядку: 24, 4, 6. С английскими именами функций: SUM, COUNT и AVERAGE.

Три ответа с разным смыслом

6: сохраняем измеренный ноль, две неизвестные величины оставляем пустыми. Это среднее по четырём часам с записями: 24 ÷ 4.
4: вводим числовой ноль в каждую пустую ячейку. Получаем 24 ÷ 6. Тем самым мы предполагаем, что в оба часа с потерянными записями посещений не было. Исходные данные этого не подтверждают.
8: исключаем настоящий ноль вторника, а неизвестные значения оставляем пустыми. Получаем 24 ÷ 3. Это среднее по трём учтённым часам, когда были посещения, а не по всем четырём часам с записями.

Попробуйте изменения на копии

Перед опытом сделайте копию листа. На копии введите 0 в B5 и B7: СЧЁТ станет равен 6, СРЗНАЧ — 4. Снова очистите содержимое B5 и B7: результаты вернутся к 4 и 6.

Теперь очистите B3, где был измеренный ноль вторника. СЧЁТ вернёт 3, СРЗНАЧ — 8. Верните ноль в B3. На каждом шаге СУММ остаётся равной 24: проверка одной суммы не обнаружила бы изменения смысла.

Укажите, что именно известно

Понятная запись результата: «В среднем 6 посещений за час с сохранённой записью; данные есть за 4 из 6 одночасовых периодов, 2 записи отсутствуют». Число 24 — сумма за четыре учтённых периода. Точные сумма и среднее за все шесть пока неизвестны.

Если потерянные записи найдутся, внесите проверенные значения и пересчитайте результат. Если в источнике ноль служит кодом «не записано», сначала выясните это правило и отделите такие коды от настоящих нулевых значений. Сохраните исходные данные и опишите свою интерпретацию.

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

Если результат не совпал

Проверьте B2:B7. В примере нужны четыре числовые ячейки, включая один ноль, и две действительно пустые. Заголовок и пояснения должны оставаться за пределами диапазона формулы.
Проверьте количество чисел. СРЗНАЧ и СЧЁТ пропускают текст в диапазоне, в том числе число, сохранённое как текст. Видимая «8» ещё не доказывает, что Excel использует её как число.
Изучите содержимое внешне пустой ячейки перед изменением. Ноль может быть скрыт настройками отображения и при этом участвовать в расчёте как число. По одному виду содержимое не определить.
Если числовых наблюдений нет, среднего по наблюдениям тоже нет. Выясните причину отсутствия записей, а не сообщайте вместо среднего ноль.

Какое среднее можно указать?

Отделите измеренный ноль от неизвестного количества. Выберите ответ или откройте пояснения ниже.

В четырёх одночасовых периодах записаны количества посещений: 2, 0, пропуск, 10. Каково среднее по периодам с записями?

Ответы и пояснения

4 посещения в час; записаны 3 из 4 периодов: Да. Сумма известных значений — 12, а ноль входит в три наблюдения: 12 ÷ 3 = 4. Количество за пропущенный период остаётся неизвестным.
3 посещения в час; делим на все 4 периода: 12 ÷ 4 = 3 предполагает, что в период с потерянной записью посещений не было. Данные этого не подтверждают.
6 посещений в час; не учитываем ноль: 12 ÷ 2 = 6 исключает настоящее наблюдение. Для среднего по трём учтённым периодам ноль должен входить в делитель.