Как вычислить среднее значение только для положительных или только для отрицательных чисел в Excel?
В Excel часто возникает задача вычисления среднего значения только для положительных или только для отрицательных чисел в диапазоне, содержащем оба типа значений — особенно при анализе прибыли и убытков, доходов и расходов или в других сценариях, где положительные и отрицательные числа имеют разный смысл. Стандартная функция AVERAGE учитывает все числа без исключения, что может исказить результаты. В этом руководстве представлены практические способы расчёта среднего только для положительных или только для отрицательных чисел: пошаговые инструкции по использованию формул, встроенных инструментов и кода VBA — чтобы удовлетворить любые потребности пользователей.
Вычисление среднего значения только положительных или отрицательных чисел с помощью формул
Просмотр среднего значения только положительных или отрицательных чисел с помощью Kutools для Excel 
Вычисление среднего значения только положительных или отрицательных чисел с помощью формул
Чтобы вычислить среднее значение только положительных чисел в диапазоне, Excel предлагает формулы массива, которые выборочно учитывают лишь значения, соответствующие заданному условию. Это особенно удобно, если вы предпочитаете обходиться без надстроек или дополнительных инструментов и хотите быстро получить результат с помощью формул прямо на листе.
1. Введите следующую формулу в пустую ячейку, чтобы отобразить результат:
=AVERAGE(IF(A1:D10>,0,A1:D10,""))В данном примере A1:D10 представляет собой диапазон данных, содержащий как положительные, так и отрицательные числа.
2. После ввода формулы нажмите одновременно Ctrl + Shift + Enter, чтобы подтвердить её как формулу массива. Если всё сделано правильно, вокруг формулы в строке формул появятся фигурные скобки { }, как показано ниже:
Пояснение формулы и её адаптируемость:
- Этот метод подходит как для горизонтальных, так и для вертикальных диапазонов — просто адаптируйте диапазон под свой лист.
- Если в вашем диапазоне отсутствуют положительные числа, а вы применяете эту формулу для положительных значений, результатом будет ошибка #DIV/0!, так как отсутствуют подходящие числа для расчёта среднего. То же самое происходит с отрицательными числами при использовании приведённой ниже формулы для отрицательных значений. Чтобы избежать этого, оберните формулу в функцию
IFERROR— это повысит надёжность:
=IFERROR(AVERAGE(IF(A1:D10>,0,A1:D10,"")), "")Чтобы вычислить среднее значение только отрицательных чисел, используйте следующую формулу:
=AVERAGE(IF(A1:D10<,0,A1:D10,""))- Не забудьте нажать Ctrl + Shift + Enter после ввода формулы, чтобы она работала корректно.
Примечания:
1. A1:D10 — это диапазон, для которого вы хотите рассчитать условное среднее; при необходимости адаптируйте его под свои данные.
2. Если вы хотите, чтобы среднее значение игнорировало нули или определённые значения, просто настройте логическое условие прямо внутри формулы.
3. Этот подход можно использовать в Excel 365 или Excel 2019 и более поздних версиях без нажатия Ctrl + Shift + Enter, так как динамические массивы поддерживаются нативно. В более ранних версиях для формул массива необходимо использовать эту комбинацию клавиш.
4. Если ваши данные содержат ошибки (например,)#DIV/0! или #N/A), формула также может вернуть ошибку. Используйте функцию IFERROR, чтобы корректно обрабатывать такие исключения.
Просмотр среднего значения только положительных или отрицательных чисел с помощью Kutools для Excel
Если вы установили Kutools для Excel, его функция Выбрать определенные ячейки позволяет быстро выделить только положительные или только отрицательные числа в диапазоне. Среднее значение сразу же отображается в строке состояния Excel, что избавляет от необходимости вводить дополнительные формулы. Этот метод идеально подходит пользователям, которые предпочитают визуальный и интерактивный способ сводки данных без сложных формул.
1. Выделите диапазон данных, содержащий как положительные, так и отрицательные числа, которые вы хотите проанализировать.
2. Перейдите в меню Kutools > Выделить > Выбрать определенные ячейки, как показано ниже:
3. В диалоговом окне Выбрать определенные ячейки выполните следующие действия:
- Выберите параметр Ячейка в разделе Выбрать тип.
- Задайте условие для положительных чисел: выберите Больше чем в раскрывающемся списке Указать тип и введите 0 в поле значения.
- Для отрицательных чисел выберите Меньше чем и снова введите 0.
Нажмите ОК, и Kutools автоматически выделит ячейки, соответствующие вашим критериям, а диалоговое окно покажет подтверждение выделенных ячеек.
4. После выделения ячеек просто взгляните на строку состояния Excel в правом нижнем углу окна — там отображается среднее значение выделенных ячеек. Оно обновляется в режиме реального времени, и вводить формулу не нужно.
 |  |
| Результат только для положительных чисел | Результат только для отрицательных чисел |
Преимущества и рекомендации:
- Не нужно писать или запоминать формулы — идеальное решение для мгновенного анализа на лету.
- Метод отлично справляется с небольшими и средними наборами данных, но если ваш выделенный диапазон содержит множество нечисловых ячеек или ошибок, обязательно вручную проверьте сводку в строке состояния, чтобы убедиться в точности результата.
- Вы также можете щелкнуть правой кнопкой мыши по строке состояния, чтобы выбрать нужные вычисления — среднее, сумму, количество и другие.
Демонстрация: сумма/среднее/количество только положительных или отрицательных чисел с помощью Kutools для Excel Kutools для Excel: Более 300 удобных инструментов всегда под рукой! Воспользуйтесь функциями на базе ИИ для более умной и быстрой работы!Скачать сейчас!
Автоматическое вычисление среднего значения только положительных или отрицательных чисел с помощью кода VBA
Для пользователей, которым часто приходится вычислять такие средние значения для разных диапазонов или автоматизировать этот процесс, простой макрос VBA может сэкономить время и повысить точность. Такой подход идеален при наличии повторяющихся задач, сложной структуры данных и уверенном владении редактором Visual Basic for Applications (VBA) в Excel.
1. Нажмите Инструменты разработчика > Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений. В редакторе выберите Вставка > Модуль, затем скопируйте и вставьте один из приведённых ниже кодов в новый модуль.
Чтобы вычислить среднее значение только положительных чисел в Выберите диапазон, используйте следующий макрос:
Sub AveragePositiveNumbers()
Dim rng As Range
Dim cell As Range
Dim sum As Double
Dim count As Long
Dim result As Variant
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set rng = Application.Selection
Set rng = Application.InputBox("Please select the range to average positive numbers", xTitleId, rng.Address, Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
sum = 0
count = 0
For Each cell In rng
If IsNumeric(cell.Value) And cell.Value > 0 Then
sum = sum + cell.Value
count = count + 1
End If
Next cell
If count > 0 Then
result = sum / count
MsgBox "The average of only the positive numbers is " & result, vbInformation, xTitleId
Else
MsgBox "No positive numbers found in the selected range.", vbExclamation, xTitleId
End If
End SubЧтобы вычислить среднее значение только для отрицательных чисел, используйте приведённый ниже код:
Sub AverageNegativeNumbers()
Dim rng As Range
Dim cell As Range
Dim sum As Double
Dim count As Long
Dim result As Variant
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set rng = Application.Selection
Set rng = Application.InputBox("Please select the range to average negative numbers", xTitleId, rng.Address, Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
sum = 0
count = 0
For Each cell In rng
If IsNumeric(cell.Value) And cell.Value < 0 Then
sum = sum + cell.Value
count = count + 1
End If
Next cell
If count > 0 Then
result = sum / count
MsgBox "The average of only the negative numbers is " & result, vbInformation, xTitleId
Else
MsgBox "No negative numbers found in the selected range.", vbExclamation, xTitleId
End If
End Sub2. После ввода макроса вернитесь в Excel. Нажмите F5 или щёлкните Выполнить. В появившемся диалоговом окне можно выбрать диапазон для вычисления — результат отобразится в окне сообщения, а также среднее значение (или предупреждение, если подходящих чисел не найдено).
Советы и устранение неполадок:
- Обязательно сохраните книгу как файл с поддержкой макросов ()
.xlsm), если хотите, чтобы ваши макросы сохранились и были готовы к повторному использованию. - Этот макрос усредняет только числовые ячейки — ячейки с текстом, пустые ячейки и ячейки с ошибками автоматически игнорируются.
- Если ваш набор данных включает очень большие объёмы информации или часто изменяется, автоматизация с помощью VBA поможет избежать ошибок ручного ввода и сэкономит время.
- Если появляется предупреждение о безопасности макросов, измените настройки макросов в разделе «Параметры Excel» > «Центр управления безопасностью», чтобы разрешить их выполнение.
При выборе метода учитывайте особенности своего рабочего процесса и уровень владения Excel:
- Формулы быстры и гибки, но требуют ввода как формулы массива и корректной адресации.
- Kutools отлично справляется с интерактивными задачами и избавляет от необходимости вручную вводить формулы.
- Макросы VBA идеально подходят для регулярной и автоматизированной отчётности.
Если возникают ошибки вычислений или неожиданные результаты, убедитесь, что выбранные диапазоны не содержат нежелательных типов данных или ошибок, проверьте корректность условия (больше или меньше нуля) и при необходимости скорректируйте ссылки. Для обработки более сложных случаев — например, игнорирования ошибок или определённых значений — используйте в формулах или коде VBA соответствующую логику обработки ошибок или фильтрации.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек