Чтобы найти среднее значение для диапазона, содержащего значения ошибок, например, # DIV / 0 !, или #NAME?, Используйте вместе функции AVERAGE, IF и ISNUMBER в формуле массива . Иногда такие ошибки генерируются в неполной рабочей таблице, и эти ошибки будут устранены позже, путем добавления новых данных. Если вам нужно найти среднее значение для существующих данных , вы можете использовать функцию AVERAGE вместе с функциями IF и ISNUMBER в формуле массива, чтобы получить среднее значение, игнорируя ошибки.
Найти среднее значение в диапазоне
Примечание: приведенная ниже формула может использоваться только с непрерывным диапазоном.
В приведенном ниже примере используется следующая формула массива, чтобы найти среднее значение для диапазона от D1 до D4.
- = СРЗНАЧ (ЕСЛИ (ЕЧИСЛО (D1: D4), D1: D4))
В этой формуле
CSE Формулы
Обычно ISNUMBER проверяет только одну ячейку за раз. Чтобы обойти это ограничение, используется формула CSE или массива, в результате чего формула оценивает каждую ячейку в диапазоне от D1 до D4 по отдельности, чтобы увидеть, соответствует ли она условию содержания числа. Формулы массива создаются одновременным нажатием клавиш Ctrl, Shift и Enter на клавиатуре после ввода формулы. Из-за клавиш, нажимаемых для создания формулы массива, их иногда называют формулами CSE .
AVERAGE IF Array Formula
- Введите следующие данные в ячейки от D1 до D4: 10, #NAME ?, 30, # DIV / 0!
- Поскольку мы создаем как вложенную формулу, так и формулу массива, нам необходимо ввести всю формулу в одну ячейку рабочего листа .
- После того, как вы ввели формулу, НЕ нажимайте клавишу Enter на клавиатуре или щелкайте мышью по другой ячейке, поскольку нам нужно превратить формулу в формулу массива.
- Нажмите на ячейку E1 — место, где будут отображаться результаты формулы
- Введите следующее: = СРЕДНИЙ (ЕСЛИ (НОМЕР (D1: D4), D1: D4))
Создание формулы массива
- Нажмите и удерживайте клавиши Ctrl и Shift на клавиатуре
- Нажмите клавишу Enter на клавиатуре, чтобы создать формулу массива
- Ответ 20 должен появиться в ячейке E1, так как это среднее для двух чисел в диапазоне 10 и 30
- При нажатии на ячейку E1 полная формула массива {= AVERAGE (IF (ISNUMBER (D1: D4), D1: D4))}
- можно увидеть в строке формул над рабочим листом
Подставляя MAX, MIN или MEDIAN для AVERAGE
Из-за сходства синтаксиса между функцией AVERAGE и другими статистическими функциями, такими как MAX, MIN и MEDIAN, эти функции можно подставить в приведенную выше формулу массива AVERAGE IF для получения различных результатов.
Чтобы найти наибольшее число в диапазоне,
- = МАКС. (ЕСЛИ (НОМЕР (D1: D4), D1: D4))
- Чтобы найти наименьшее число в диапазоне,
- = MIN (ЕСЛИ (НОМЕР (D1: D4), D1: D4))
- Чтобы найти среднее значение в диапазоне,
- = MEDIAN (ЕСЛИ (НОМЕР (D1: D4), D1: D4))
Как и в случае формулы AVERAGE IF, указанные выше три формулы также должны быть введены в виде формул массива.