1

У меня есть данные, сгруппированные по двум критериям, начиная с B2, с заголовками в B1 -> BV1 и A2 -> A101.

Ячейка A1 содержит тег для данных; "МОИ ДАННЫЕ".

Клетки B1 -> BV1 представляют собой факторы 6: 0, 6, 12, 18 ... 354. Это бин запускается так, что 0 представляет данные, где 0 <= данные <6. Клетки A2 -> A101 имеют коэффициенты 0,25: 0,25, 0,50, 0,75 ... 49,75. Это средние значения бина, так что 0,25 представляет данные, где 0,00 <= данные <0,50.

Вот снимок:

MYDATA  0       6       12      18
0.25    0.014   0.013   0.011   0.010
0.75    0.135   0.122   0.120   0.120
1.25    0.451   0.434   0.442   0.442

Я хочу объединить эти данные в меньшее количество бинов, усредняя их, когда я это делаю.

Я хочу перебить строки целым числом как 0 -> 1, 1 -> 2 и т.д. До 30 (данные выше 30 в оригинале исключаются). Начиная с B6 = 0 (затем с B7 = 1 до B36 = 30), переберите столбцы значениями 30, поэтому 0, 30 ... 330. Начиная с C5 = 0, затем D5 = 30 до N5 = 330.

Я пытаюсь сделать это с AVERAGEIFS, используя следующую формулу, введенную в ячейку C6 (столбцы и строки заблокированы с помощью $ ready для перетаскивания для формирования полной таблицы):

AVERAGEIFS('DATA'!$A$1:$BI$101,'DATA'!$B$1:$BI$1,">"&C$5,'DATA'!$B$1:$BI$1,"<="&D$5,'DATA'!$A$2:$A$101,">"&$B6,'DATA'!$A$2:$A$101,"<"&$B7)

Это возвращает ошибку #VALUE. В попытке решить эту проблему я использовал опцию "Оценить формулу" в формулах -> Оценить формулу -> Оценить. Это выдвигает на первый план мой первый критерий,> C5 (записывается как «>» и C5) и после нажатия "Evaluate" меняет это на «>» & 0.

Глядя на мои данные, он вытягивает правильное значение для C5; 0. Однако я не понимаю, почему это возвращает ошибку #VALUE. Насколько я понимаю, это первый критерий, определяющий начало диапазона столбцов, выбранных из MYDATA. Конец диапазона столбцов определяется как D5 (= 30).

В чем ошибка моего понимания или моего подхода?

1 ответ1

1

У вас есть критерии в столбцах и строках. Для этого типа установки вы не можете использовать AVERAGEIFS , но вы можете использовать "формулу массива", например:

=AVERAGE(IF('DATA'!$B$1:$BI$1>C$5,IF('DATA'!$B$1:$BI$1<=D$5,IF('DATA'!$A$2:$A$101>$B6,IF('DATA'!$A$2:$A$101<$B7,'DATA'!$B$2:$BI$101)))))

подтверждено с помощью CTRL+SHIFT+ENTER

Примечание 1: диапазон до среднего находится в конце.
Примечание 2: возможно, вам нужно <=$B7 иначе вы пропустите некоторые значения

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .