☰

Решение 1

tag-icon • tag-icon microsoft-excel worksheet-function
Решение 1

У меня есть ряд значений, и я хочу найти среднее значение первых трех, не считая пустых ячеек. Пример:

  ABCDEFG
 12 пусто 8 пусто 7 9 4

Среднее значение первых 3 непустых ячеек должно быть 9, но я не могу найти формулу, которая вычислит это для меня. Может ли кто-нибудь помочь?

решение1

ВА2входить:

=IF(A1="",0,1)

И вБи 2входить:

=IF(OR(B1="",SUM($A$2:A2)=3),0,1)

и скопируйте. Затем в другой ячейке введите:

=SUMPRODUCT((1:1)*(2:2))/3

введите описание изображения здесь

решение2

Вы ищетеСРЕДНИЙФункция. Согласно формулесинтаксис и использованиедля функции СРЗНАЧ в Excel:

Если аргумент ссылки на диапазон или ячейку содержит текст, логические значения,или пустые ячейки, эти значения игнорируются; однако ячейки со значением ноль включаются.

Формула =AVERAGE(A1:E1)возвращает 9:

введите описание изображения здесь

решение3

Решение 1

Грубый метод: необходимое вам среднее значение можно вычислить следующим образом:

=ЕСЛИ(СЧЁТ($A1:$C1)=3, СРЗНАЧ($A1:$C1),
 ЕСЛИ(СЧЁТ($A1:$D1)=3, СРЗНАЧ($A1:$D1),
 ЕСЛИ(СЧЁТ($A1:$E1)=3, СРЗНАЧ($A1:$E1),
 ЕСЛИ(СЧЁТ($A1:$F1)=3, СРЗНАЧ($A1:$F1),
 ЕСЛИ(СЧЁТ($A1:$G1)=3, СРЗНАЧ($A1:$G1), "ой"))))

(Однострочная версия:)
 =ЕСЛИ(СЧЕТ($A1:$C1)=3, СРЗНАЧ($A1:$C1), ЕСЛИ(СЧЕТ($A1:$D1)=3, СРЗНАЧ($A1:$D1), ЕСЛИ(СЧЕТ($A1:$E1)=3, СРЗНАЧ($A1:$E1), ЕСЛИ(СЧЕТ($A1:$F1)=3, СРЗНАЧ($A1:$F1), ЕСЛИ(СЧЕТ($A1:$G1)=3, СРЗНАЧ($A1:$G1),"ой"))))

который ищет самое короткое начальное подмножество строки, содержащей три числа, и усредняет его. Это становится не только неудобным для большого количества столбцов, но и может довольно быстро дать сбой, поскольку (я полагаю) IF()имеет довольно низкий предел вложенности.

Решение 1.9

Изящество.

Я называю это «1.9», потому что это еще не сделано. Выражение

SMALL(IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),""), {1,2,3})

(используется в формуле массива) находит номера столбцов первых трех чисел в строке 1.

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!). Я не могу понять, как это закончить.

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