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