Если значение AVERAGE или СУММ относится к ячейкам, содержащим #VALUE! ошибки, формулы приведут к #VALUE! .
Чтобы не учитывать значения ошибок, мы создадим формулу, которая игнорирует ошибки в диапазоне ссылок при вычислении среднего значения с оставшимися "нормальными" значениями.
Чтобы решить эту проблему, мы используем функцию СРЗНАЧ в сочетании с функциями ЕСЛИ и ЕОШИБКА для определения наличия ошибок в указанном диапазоне. В данном конкретном случае требуется формула массива:
=СРЗНАЧ(ЕСЛИ(ЕОШИБКА(B2:D2);"";B2:D2))
Это формула массива. Поэтому необходимо нажать клавишу ВВОД (только если вы используете Microsoft 365) или CTRL+SHIFT+ВВОД в формуле.
Примечание
Если у вас есть текущая версия Microsoft 365, можно просто ввести формулу в ячейку вывода, а затем нажать клавишу ВВОД , чтобы подтвердить формулу как формулу динамического массива. В противном случае формулу необходимо ввести в качестве устаревшей формулы массива, сначала выбрав выходную ячейку, введя формулу в выходную ячейку, а затем нажав клавиши CTRL+SHIFT+ВВОД , чтобы подтвердить ее. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
Примечание
Приведенная выше формула будет работать не только в случае ошибок #ЗНАЧ!, но и в случае ошибок #Н/Д, #ПУСТО!, #ДЕЛ/0! и других.
Таким же образом можно использовать функцию СУММ:
=СУММ(ЕСЛИ(ЕОШИБКА(B2:D2);"";B2:D2))
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.
См. также
Исправление ошибки #ЗНАЧ! ошибка
Полные сведения о формулах в Excel