Как усреднить несмежные ячейки в Excel, исключив нули?
При работе с данными Excel часто возникает необходимость вычислить среднее значение набора чисел, однако не все соответствующие ячейки расположены рядом, и при этом требуется исключить Нулевые значения (которые могут представлять отсутствующие данные или записи со значением «ноль»). Например, в таблице продаж или учёта запасов определённые товары могут иметь нулевой остаток или отсутствие продаж в конкретные даты; в таких случаях простое вычисление среднего значения может исказить реальную тенденцию данных из-за влияния нулей. Их исключение позволяет анализировать только активные точки данных.
Представьте, что у вас есть таблица фруктов, подобная той, что показана на скриншоте ниже, и вы хотите рассчитать среднее значение всех ячеек в столбце «Количество», исключая нули. Ниже приведены несколько практичных способов решения этой задачи в Excel — независимо от того, находятся ли ваши данные в несмежных ячейках, распределены по нескольким строкам или столбцам, или вы предпочитаете использовать встроенные функции Excel или простые формулы.
- Усреднение несмежных ячеек с исключением нулей с помощью формулы
- Усреднение несмежных ячеек с исключением нулей с помощью макроса VBA
- Усреднение несмежных ячеек с исключением нулей с использованием встроенной функции Excel
Усреднение несмежных ячеек с исключением нулей с помощью формулы
Этот метод использует формулу массива для быстрого расчёта среднего значения несмежных ячеек — например, каждой второй ячейки в строке или столбце — автоматически исключая при этом все нули среди выбранных ячеек. Он идеально подходит, когда целевые ячейки следуют чёткому интервальному шаблону, и особенно эффективен при работе с периодическими данными, такими как значения за каждый второй день, или с данными, расположенными через равные промежутки, но не подряд.
Ограничения: Этот метод идеально подходит, когда несмежные ячейки расположены с равномерным интервалом. Для произвольного ручного выделения см. описанный ниже способ на основе формулы.
1. Выберите пустую ячейку для вывода результата и введите формулу массива:
=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))Затем одновременно нажмите клавиши Ctrl+Shift+Enter(для старых версий Excel; в Excel 365 или 2019 достаточно нажать)Enter, так как эти версии поддерживают динамические массивы).
Примечание: В формуле C2 и G2 обозначают первую и последнюю ячейки в требуемом диапазоне. Число «2» задаёт интервал между столбцами — измените его, если ваши несмежные ячейки разделены другим количеством столбцов. Адаптируйте диапазон (C2:G2) и интервал (2) под структуру ваших данных.
Советы:
- Убедитесь, что выбранный диапазон включает все несмежные ячейки, которые вы хотите усреднить.
- Если выделение данных не основано на регулярных интервалах, воспользуйтесь следующим формульным решением для произвольных ячеек.
- Эта формула автоматически игнорирует пустые ячейки и нулевые значения, гарантируя точное среднее только по значимым данным.
- Чтобы избежать ошибок, убедитесь в корректности ссылок на ячейки и не забудьте нажать нужную комбинацию клавиш для ввода формул массива в более старых версиях Excel.
Усреднение несмежных ячеек с исключением нулей с помощью макроса VBA
Если вам нужно быстро усреднить группу несмежных ячеек, исключая нули, а выделение не следует фиксированному шаблону (например, вы хотите вручную выбрать отдельные ячейки из разных строк и столбцов), макрос VBA станет гибким и мощным решением. Код пройдёт по всем выделенным ячейкам и рассчитает среднее значение, игнорируя те, что содержат ноль.
Этот метод отлично подходит для:
- Быстрая обработка больших и нестандартных наборов данных.
- Сохраняйте пользовательскую логику для многократного использования с аналогичными наборами данных.
- Преодоление ограничений традиционных формул рабочего листа при работе со сложными выделениями.
1. Нажмите Разработчик > Visual Basic; в открывшемся окне выберите Вставка > Модуль и вставьте следующий код в модуль:
Sub AverageNonAdjacentExcludeZero()
Dim rng As Range
Dim cell As Range
Dim SumVal As Double
Dim CountVal As Long
Dim SelectedRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
On Error Resume Next
SumVal = 0
CountVal = 0
For Each cell In SelectedRng
If IsNumeric(cell.Value) And cell.Value <> 0 Then
SumVal = SumVal + cell.Value
CountVal = CountVal + 1
End If
Next
If CountVal > 0 Then
MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
Else
MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
End If
End Sub2. Нажмите кнопку 
«Выполнить» или клавишу F5 в редакторе VBA. Появится диалоговое окно с запросом на выбор ячеек. Используйте мышь и клавишу Ctrl, чтобы щёлкнуть и выделить несмежные ячейки, которые требуется усреднить. Нажмите OK, и макрос отобразит результат с исключением всех нулей.
Усреднение несмежных ячеек с исключением нулей с использованием встроенной функции Excel
Excel также предлагает встроенные инструменты для решения этой задачи с минимальным использованием формул или вовсе без них — идеальный вариант для быстрых разовых расчётов и визуального анализа.
Используйте Строка состояния:
- Удерживайте Ctrl и с помощью мыши вручную выберите каждую несмежную ячейку, которую необходимо усреднить, пропуская ячейки со значениями «нуль».
- После выделения обратите внимание на строку состояния Excel — в правом нижнем углу окна автоматически отобразится значение Среднее, если выделено более одной ячейки.
- Этот метод идеально подходит для быстрой визуальной проверки и работы с небольшими наборами данных, однако его результат не отображается в ячейке рабочего листа.
При выборе подходящего метода учитывайте объём данных, степень разброса выделенных ячеек и то, нужна ли вам повторяемая формула или просто быстрая визуальная проверка. Если возникают ошибки (например,)#ДЕЛ/0!), убедитесь, что среди выделенных ячеек есть хотя бы одно ненулевое значение. Перед запуском новых макросов всегда сохраняйте книгу при работе с VBA — это поможет избежать случайной потери данных.
Быстрое выделение несмежных строк или столбцов через равные интервалы в Ограниченный диапазон в Excel:
Функция Kutools для Excel «Выбрать строки/столбцы с интервалами» позволяет легко выделять строки или столбцы через заданные интервалы в ограниченном диапазоне Excel, как показано на скриншоте ниже. Это особенно полезно перед применением формул или макросов — так вы точно выбираете нужные ячейки для обработки.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек