☰

Значения AVERAGEIFS, которые не являются NA

tag-icon • tag-icon microsoft-excel spreadsheet google-spreadsheets
Значения AVERAGEIFS, которые не являются NA

У меня есть следующее AVERAGEIFS, которое усредняет столбец I, сгруппированный по значениям в столбце B

= AVERAGEIFS(B:B, A:A, A:A)

Проблема в том, что иногда столбец B содержит NA()ошибку. Как мне отфильтровать их из уравнения?

Я пытался сделать фильтр

= AVERAGEIFS(ARRAYFORMULA(IF(ISNUMBER(B:B),B:B)), A:A,A:A)

но это приводит к ошибке.

Вот некоторые примеры данных:

* A    *  B  *
| Fred |  8  |
| Bill |  9  |
| Jane |  NA |
| Jane |  9  |
| Fred |  NA |
| Bill |  5  |
| Fran |  9  |
| Fred |  4  |
| Jane |  1  |

Мне нужно добавить столбец C, в котором рядом с каждой строкой будет отображаться средний показатель человека, не считая NA.

* A    *  B  * C *
| Fred |  8  | 6 |
| Bill |  9  | 7 |
| Jane |  NA |   |
| Jane |  9  | 5 |
| Fred |  NA |   |
| Bill |  5  | 7 |
| Fran |  9  | 9 |
| Fred |  4  | 6 |
| Jane |  1  | 5 |

Что касается пустых мест, то меня не особо волнует, что там происходит — это может быть ошибка или пробел, главное, чтобы s NAне влияли на среднее значение. Мне все равно.

решение1

Вариант 1 — IFNA() с массивом

Я не совсем понимаю, что вы AVERAGEIFSна самом деле должны делать, но все, что вам нужно сделать, чтобы исправить это, — это использовать IFNA()вместе с формулой массива ( Ctrl + Shift + Enterпри вводе формулы):

= AVERAGEIFS(IFNA(B:B,""), A:A, A:A)

Это превращает NA()значения в пробелы в данных, с которыми вы работаете.

Вариант 2 — Очистите данные

Обычно я бы выбрал вариант 1, но я понимаю, что это не то, что вы просили, поэтому я понизил его до варианта 2...

Очевидно, что все, что делает вышеприведенная формула, это заменяет NA()пустыми значениями. Или, почему бы изначально не сделать так, чтобы ваши ячейки вычислялись как пустые? Это также сделает ваши столбцы более красивыми.

Какие бы вычисления вы ни выполняли в базовых ячейках, либо измените NA()параметр на return "", либо оберните все вычисления вIFNA([insert calculation here],"")

решение2

Не очень умный способ — добавить новый скрытый столбец (например, столбец D) с помощью:

=ЕСЛИ(ЕЧИСЛО(B:B),B:B,0)

Тогда все =AVERAGEIFS(D:D,A:A,A:A)будет работать так, как задумано:

Fred    8   4
Bill    9   7
Jane    NA  3.333333333
Jane    9   3.333333333
Fred    NA  4
Bill    5   7
Fran    9   9
Fred    4   4
Jane    1   3.333333333

Связанный контент