Как сделать формулу среднего значения в экселе: СРЗНАЧ
Как сделать формулу среднего значения в экселе: выбери диапазон, различи пропуски и нули, проверь фильтр и веса на журнале длительности встреч кружка.
Д
Денис
Продакт LibraChat
8 мин чтения
Пропуск в длительности встречи легко принять за ноль. Я так и заполнил журнал книжного кружка, а потом удивился: среднее уменьшилось, хотя ни одной новой встречи мы не провели. Когда разбираешься, как сделать формулу среднего значения в экселе, полезнее сначала решить, что считать наблюдением. Сама функция короткая. Решения вокруг неё влияют на результат сильнее.
Мне нужно было понять, сколько времени обычно занимает обсуждение, чтобы выбирать подходящее окно для следующих встреч. В журнале нашлись проведённые собрания, будущая дата и одна длительность, которую ещё требовалось уточнить. Я собрал их на одном листе и перестал делать вид, что все заполненные клетки говорят об одном и том же.
Как сделать формулу среднего значения в экселе для диапазона
Начни с такого журнала в A1:C8. В первой строке находятся заголовки, числа в B2:B6 означают минуты. B7 пока оставь пустой; слово «уточнить» в B8 нужно для следующей проверки.
Для этого набора результат равен 52 минутам: 260 минут суммарно разделены на пять числовых значений. Пустая ячейка и текстовая пометка не добавляют наблюдения в расчёт по диапазону.
Если чисел в диапазоне совсем нет, среднее вычислить не из чего: функция вернёт ошибку деления на ноль. Сначала проверь, есть ли измеренные значения и правильно ли указан диапазон. Заполнение пустот выдуманными нулями лишь изменит исходные данные.
Двоеточие в B2:B8 задаёт непрерывный диапазон от второй до восьмой строки столбца B. Результат я специально поставил за пределами журнала, ниже его строк. Если включить итоговую ячейку в собственный диапазон, получится циклическая ссылка, а не новое среднее.
Можно получить формулу и через меню: выбери ячейку результата, открой список у «Автосуммы» на вкладке «Главная» или «Формулы» и выбери среднее. Перед подтверждением прочитай предложенный диапазон. Программа не знает, какую часть журнала ты хотел исследовать, и автоматическое выделение следует проверить.
Я подписал результат «Среднее по известным длительностям». Это чуть длиннее привычного «Среднее», зато не обещает учесть встречу с неизвестным временем. Шесть проведённых встреч и пять измеренных длительностей — разные количества.
Пустая ячейка и ноль меняют не только внешний вид
Теперь временно поставь в B7 число 0. В указанном диапазоне появится шестое число. Общая сумма останется 260, но делиться она будет уже на шесть. Результат уменьшится примерно до 43,33 минуты.
Именно это произошло с моим журналом: будущая встреча получила «длительность» до того, как началась. Среднее стало выглядеть убедительнее, потому что пустот больше не осталось. Только исходных измерений не прибавилось.
Верни B7 в пустое состояние, очистив её содержимое. Не вводи пробел для вида. СРЗНАЧ снова должна показать 52. Я проверил эту разницу отдельно, прежде чем исправлять остальной журнал: 0 означает числовое значение, а пропуск сообщает, что значения пока нет.
Это не повод удалять все нули из любой таблицы. Если показатель действительно был измерен и равен нулю, исключение изменит вопрос и ответ. Сначала установи, что обозначает поле. В нашем журнале длительность будущей встречи ещё неизвестна, поэтому число за неё я не придумываю.
С отменёнными встречами тоже надо договориться о смысле. Среднее время проведённых обсуждений и среднее использование всех забронированных окон — разные задачи. Для первой отмену нельзя незаметно превратить в состоявшееся обсуждение нулевой длины.
Слова рядом с числом лучше вынести из расчётной колонки
У меня была ещё одна привычка: писать в ячейку «60 минут», чтобы никто не перепутал единицы. В диапазоне для СРЗНАЧ такая запись является текстом и пропускается. При замене числа 60 в B5 на текст «60 минут» среднее известных чисел станет 50, а не 52.
Верни в B5 числовое значение 60. Единицу измерения лучше указать в заголовке или соседнем описании. Тогда человеку понятен смысл, а формула получает число. Для такого журнала достаточно колонки «Минуты» с обычными числовыми значениями.
Пометка «уточнить» в B8 тоже игнорируется при вычислении по ссылке. Это помогает увидеть поведение функции, но для постоянной работы я перенёс такую пометку в отдельное поле. В колонке длительности оставляю число или пропуск, а причину пропуска пишу рядом.
После импорта проверь и числа, которые хранятся как текст. То, что в ячейке видны цифры, ещё не подтверждает их числовой тип. Сравни известный маленький набор с ручным расчётом, прежде чем доверять большому итогу.
Я сделал себе короткую сверку перед вычислением:
все длительности записаны в одной единице;
каждая используемая запись содержит число;
у пропуска есть понятная причина;
нули обозначают измеренные нули;
название результата объясняет, какие встречи вошли в расчёт.
Эта проверка заняла больше внимания, чем ввод СРЗНАЧ. Зато теперь я могу объяснить знаменатель, а не просто показать красивое число с двумя знаками после запятой.
Фильтр скрывает строки, но не переписывает СРЗНАЧ
Предположим, нужны только данные второй недели. Выдели A1:C8 и включи «Данные» → «Фильтр». В колонке «Неделя» оставь значение 2. На экране останутся известные длительности 40, 60 и 80, а также две записи без числовой длительности.
Обычная формула `=СРЗНАЧ(B2:B8)` продолжает обращаться к указанному диапазону. Скрытые фильтром 30 и 50 остаются его частью, поэтому результат в E10 будет 52. Если ты ожидал среднее только показанных встреч, нужна другая формула.
В E11 введи:
```text
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(101;B2:B8)
```
Здесь 101 выбирает среднее с исключением скрытых строк. Отфильтрованные записи не участвуют; строки, скрытые вручную, при таком номере функции тоже пропускаются. В нашем отборе получится 60 минут.
Поставь рядом понятные подписи к двум результатам: весь заданный диапазон и видимые строки. После очистки фильтра они совпадут на 52. Я держу расчёты ниже строк журнала, чтобы фильтрация не скрывала сами итоговые ячейки вместе с данными.
Не меняй функцию только ради ожидаемого числа. Сначала выбери задачу: все известные длительности или текущий отбор. В обоих случаях формула может считать правильно, просто показывать ответы на разные вопросы.
Когда среднее из двух недель требует весов
Сними фильтр. На первой неделе у нас две измеренные встречи: 30 и 50 минут, среднее 40. На второй известны три длительности: 40, 60 и 80, среднее 60. Неизвестная длительность пока не увеличивает число измерений.
Если усреднить недельные средние как два равноправных числа, получится 50. Но среднее по пяти исходным значениям равно 52. Во втором подходе неделя с тремя измерениями должна получить больший вес, чем неделя с двумя.
Для проверки набери такую сводку в F1:H3:
Неделя · Среднее, мин. · Измерений
1 — 40 — 2
2 — 60 — 3
В отдельной ячейке можно восстановить среднее по известным встречам формулой:
```text
=СУММПРОИЗВ(G2:G3;H2:H3)/СУММ(H2:H3)
```
Числитель здесь даёт 40 × 2 плюс 60 × 3, то есть 260. Знаменатель равен пяти измерениям. Получаем прежние 52. Функция СУММПРОИЗВ перемножает соответствующие значения двух диапазонов и складывает произведения.
Для среднего по встречам весом служит количество вошедших измерений. Если считаешь среднее значение именно по неделям, у каждой недели может быть равный вес — но результат надо так и назвать. Я выбираю исходные длительности, когда они под рукой: меньше возможностей потерять количество наблюдений при свёртке.
Не округляй промежуточные групповые средние раньше времени. Формат отображения можно сделать удобным, сохраняя сами расчётные значения. Для нашего примера это не меняет целые 40 и 60, но в другом журнале раннее округление способно повлиять на общий результат.
Как сделать формулу среднего значения в экселе проверяемой
Когда журнал станет больше, возможности работы с прикреплёнными файлами можно уточнить в тарифах LibraChat. Для объяснения текущей формулы достаточно нескольких обезличенных значений и описания пропусков.
Разобрать состав среднего поможет вопрос в LibraChat. Попробуй задать его так:
Объясни мой расчёт средней длительности встреч. В B2:B8 стоят 30, 50, 40, 60, 80, пустая ячейка и слово «уточнить». Использую =СРЗНАЧ(B2:B8). Покажи, какие значения участвуют и на сколько чисел делится сумма. Затем объясни изменение при замене пропуска нулём. Не считай неизвестную длительность известной и не меняй мою книгу: мне нужно понять формулу.
Объяснение из чата сверяй с результатом на листе. LibraChat помогает рассуждать о формуле по описанным данным; открытой книгой управляет сам пользователь. Рядом со средним иногда нужен общий объём минут. Этой смежной задаче посвящён разбор суммы, отбора по условию и фильтра в Excel.
Для одной итоговой проверки замени пустую B7 на ноль и заранее назови направление изменения среднего. Если E10 уменьшилась с 52 примерно до 43,33, ты увидел влияние нового числового наблюдения. Верни пропуск — и оставь в журнале только те значения, смысл которых можешь объяснить.
