У меня есть ряд значений и я хочу найти среднее из первых 3, не считая пустых ячеек. Пример:
A B C D E F G 12 blank 8 blank 7 9 4
Среднее значение для первых 3 непустых ячеек должно быть 9, но я не могу найти формулу, которая будет рассчитывать это для меня. Кто-нибудь может помочь?
В А2 введите:
=IF(A1="",0,1)
и в B2 введите:
=IF(OR(B1="",SUM($A$2:A2)=3),0,1)
и скопировать через. Затем в другую ячейку введите:
=SUMPRODUCT((1:1)*(2:2))/3
Грубая сила: среднее значение, которое вы хотите, может быть вычислено с помощью:
=IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1), IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1), IF(COUNT($A1:$E1)=3, AVERAGE($A1:$E1), IF(COUNT($A1:$F1)=3, AVERAGE($A1:$F1), IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1), "oops"))))) (Single-line version:) =IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1), IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1), IF(COUNT($A1:$E1)=3, AVERAGE($A1:$E1), IF(COUNT($A1:$F1)=3, AVERAGE($A1:$F1), IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1),"oops")))))
который ищет самое короткое начальное подмножество строки, которая содержит три числа, и усредняет это.
Это становится не только неудобным для большого количества столбцов, но и может довольно быстро потерпеть неудачу, так как (я полагаю) IF() имеет довольно низкий предел вложенности.
Finesse.
Я называю это «1,9», потому что это еще не сделано. Выражение
SMALL(IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),""), {1,2,3})
(используется в формуле массива) находит номера столбцов первых трех чисел в строке 1. The
IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),"")
часть оценивает
IF( {TRUE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE}, {1,2,3,4,5,6,7}, "" )
который сводится к
{ 1, "", 3, "", 5, 6, 7 }
и SMALL(…, {1,2,3}) из вышеперечисленного производит { 1, 3, 5 } .
Это должно быть просто сказать
=AVERAGE(INDEX($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))
или же
=AVERAGE(OFFSET($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))
но они возвращают либо значение A1 (12), либо ошибку (#DIV/0! или #REF!). Я не могу понять, как это закончить.
Вы ищете функцию СРЕДНЕГО . В соответствии с синтаксисом формулы и использованием для функции AVERAGE в Excel:
Если аргумент ссылки на диапазон или ячейку содержит текст, логические значения или пустые ячейки, эти значения игнорируются; однако, ячейки со значением ноль включены.
Формула =AVERAGE(A1:E1) возвращает 9:
