Проблемы с SUMPRODUCT в Excel: Попытка подсчитать количество средних вычитаний выше заданного порога
У меня довольно простая проблема, которую я не могу решить. Это может быть знакомо некоторым из вас сейчас.
У меня есть следующая матрица (которую я буду называть двумя массивами):
F G H I J ... R S T U V
1 0 0 1 1
4 4 2 3 5 1 2 3 1 2
2 2 3 1 2 0 1
2 1 0 0 4 0 0 3 0 0
Я хотел бы взять разницу между средним значением каждой строки в массиве 1 (столбцы F:J) и средним значением каждой строки в массиве 2 (столбцы R:V). Например, среднее значение F2:J2 = 3,6, среднее значение R2:V2 = 1,8, а общая разница = 1,8. Затем я хотел бы подсчитать количество общих различий, которые превышают заданный порог (например, 1), но я хочу игнорировать строки, в которых нет записей (см. R1:V1) и / или частично отсутствуют записи (см. 2-ю запись в строка F3:J3 и 4-я и 5-я запись в строке R3: V3).
Мне повезло, что Tom Sharpe познакомил меня с формулами Tom Sharpe, и я попытался адаптировать его код для решения аналогичной проблемы, с которой я столкнулся, например:
=SUMPRODUCT(--((SUBTOTAL(1,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))-SUBTOTAL(1,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))>1)*(SUBTOTAL(2,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))=COLUMNS(F1:J1))*(SUBTOTAL(2,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))=COLUMNS(R1:V1))>0))
Из того, что я понимаю, код пытается подсчитать количество различий между средними значениями каждой строки в каждом массиве, которые превышают 1, при условии, что произведение числа столбцов с полными записями составляет>0 (то есть содержит полные данные). Тем не менее, он продолжает бросать #DIV/0! ошибка, которая, как я полагаю, связана с тем фактом, что он все еще пытается вычесть среднее значение F1:J1 и R1:V1 (например, пустую строку), что привело бы к такой ошибке. Правильный ответ для текущего примера: 1 (например, F2:J2 [3.6] - R2:V2 [1.8] = 1.8 == 1.8 > 1).
Кто-нибудь имеет какие-либо идеи относительно того, как код может быть использован для текущих целей, и, возможно, v. Краткое объяснение того, что происходит в текущем коде неправильно?
2 ответа
Вы правы, SUBTOTAL падает, когда пытается найти среднее значение диапазона, содержащего только пустые ячейки.
Если вы хотите продолжить и попытаться сделать то же самое, что и раньше, с формулой массива, вы должны повернуть ее и поставить условие, чтобы все ячейки в обоих диапазонах были непустыми в операторе if, чтобы не пытайтесь взять среднее, если оба диапазона не имеют пробелов:
=SUM(IF((SUBTOTAL(2,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))=COLUMNS(F1:J1))*(SUBTOTAL(2,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))=COLUMNS(R1:V1)),
--(SUBTOTAL(1,OFFSET(F1,ROW(F1:F4)-ROW(F1),0,1,COLUMNS(F1:J1)))-SUBTOTAL(1,OFFSET(R1,ROW(R1:R4)-ROW(R1),0,1,COLUMNS(R1:V1)))>1)))
На этот раз, к сожалению, я обнаружил, что не могу это сделать SUMPRODUCT - я думаю, что это из-за наличия оператора IF - поэтому вы должны ввести его как формулу массива, используя CtrlShiftEnter
Будет ли это работать для вас?
=IF(NOT(OR(IFERROR(MATCH(TRUE,ISBLANK(F1:J1),0),FALSE),IFERROR(MATCH(TRUE,ISBLANK(R1:V1),0),FALSE))), SUBTOTAL(1,F1:J1)-SUBTOTAL(1,R1:V1), "Missing Value(s)")
Мой подход немного отличался от того, что вы пытались адаптировать из @TomSharp, в том, что я проверяю данные в ячейках (не пустые), а затем выполняю вычисление, иначе возвращаю сообщение об ошибке. Это по-прежнему вызов функции массива, поэтому при вводе формул нажмите ctrl+shft+enter,
Условие часть открытия if() проверяет, что ячейки каждого диапазона не пустые: if a match( true= isblank(cell))означает, что ячейка пуста (плохая), если нет совпадений... т.е. нет пустых ячеек, Match вернет #NA "ошибка" (хорошая). Ложь это хорошо = ошибки найдены? № ((т.е. без пустых ячеек))