Excel, среднее значение для определенного столбца в таблице

Question

Я пытаюсь рассчитать среднее значение только для одного столбца в таблице значений.

Это так:

Hi   Hello  Hey
1  |  2   | 3
1  |  2   | 3
1  |  2   | 3

И я пытаюсь получить среднее значение для одного из этих столбцов, но который зависит от другой ячейки, которая скажет мне «Привет, Привет» или «Привет». Допустим, такой ячейкой является К5

Поэтому я подумал что-то вроде среднего (А1: С1; "=" & К5; А2: С4). При K5, равном Hi, формула вернула значение 1. Но когда я изменил значение в столбце Привет, имея тогда:

Hi   Hello  Hey
1  |  2   | 3
7  |  2   | 3
1  |  2   | 3

Формула по-прежнему показывает мне 1 (должно быть 3), поэтому она принимает во внимание только второй ряд.

Как я могу рассчитать среднее значение A2:A4, если K5 - Hi, B2:B4, если K5 - Hello, или C2:C4, если K5 - Hey?

Спасибо.

Answer 1

Если вы используете табличную функцию в Excel, чтобы сделать нужные данные в виде таблицы
(Выделите все это и нажмите CTRL+T), затем вы можете получить среднее значение, используя:

=AVERAGE([Hi]) 

Предполагая, что заголовок вашей таблицы называется Hi .

Вы можете использовать это в более крупной функции, такой как

=100 - ([@hi]) / ( MAX([hi]) + AVERAGE([hi]) ) 

или больше, если вы хотите расширить это дальше.

=([@hi]) даст текущее значение hi для этой строки. Остальное довольно понятно.

Answer 2

AverageIf не создан для оценки всей таблицы и возврата только одного столбца. Чтобы достичь описанного вами результата, вы можете использовать что-то вроде

=AVERAGE(IF(A1:C1=K5,A2:C4,""))

Это формула массива, которая должна быть подтверждена с помощью Ctrl+Shift+Enter.

Answer 3

Я дам вам первые два способа, которые пришли на ум.

=average(offset(A2:C2,0,match(K5,A1:C1,0)-1))

смещение смещает ваш диапазон, совпадение найдет K5 в ячейках A1:C1 и вернет, из какой ячейки он (1,2,3) вычтет единицу, чтобы сделать строку правильной.

В качестве альтернативы, если у вас есть данные, хранящиеся в виде таблицы, вы найдете среднее значение для любого столбца с именем заголовка в K5.

=average(INDIRECT("Table1["&K5&"]"))

Косвенный в основном позволяет использовать переменные для определения ссылок на ячейки.