Excel: создать массив средних значений двух диапазонов
Мне нужно, чтобы два вычислили среднее значение двух диапазонов Excel. Некоторые значения отсутствуют.
например:
1, 2
2, 2
3, 0
2, нет данных
В результате средние значения:
1,5
2
1,5
2
и общее среднее должно быть 1,75
Есть ли способ сделать это в ОДНОЙ формуле без каких-либо вспомогательных ячеек?
редактировать - важные примечания:
Два диапазона не являются смежными в реальной книге.
это должно работать в Excel 2019. никаких новых функций...
была опечатка. я отредактировал это
Можете ли вы попробовать мой ответ?
Ответы 5
Используйте BYROW() для среднего значения каждой строки. Затем используйте функцию AVERAGE() для окончательного среднего значения. Пытаться-
=AVERAGE(BYROW(A1:B4,LAMBDA(x,AVERAGE(x))))
Для несмежных столбцов вы можете попробовать:
=AVERAGE(MAP(A1:A4,D8:D11,LAMBDA(a,b,AVERAGE(a,b))))
можно ли указать два диапазона по отдельности? в моей реальной книге два диапазона не являются смежными
@BoTz Тогда используйте =AVERAGE(MAP(A1:A4,D8:D11,LAMBDA(a,b,AVERAGE(a,b))))
Возможно, вы могли бы попробовать использовать MAP()
• Формула, используемая в ячейке D7
=LET(x,MAP(A7:A10,B7:B10,LAMBDA(x,y,AVERAGE(x,y))),VSTACK(x,AVERAGE(x)))
это работает с excel 2019?
Нет. Мы предполагаем, что ограничений Excel нет. Следовательно, все опубликованные решения работают исключительно с MS365.
Если это действительно #N/A, вам может понадобиться использовать Aggregate:
=AVERAGE(BYROW(A1:B4,LAMBDA(rw,AGGREGATE(1,6,rw))))
Или, может быть
=AVERAGE(BYROW(A1:B5,LAMBDA(rw,IFERROR(AGGREGATE(1,6,rw),""))))
в том случае, если у вас может быть два NA в одном ряду.
Вариант ответа @user11222393 для Excel 2019:
=AVERAGE(IFERROR((IF(ISNUMBER(A1:A5),A1:A5,0)+IF(ISNUMBER(B1:B5),B1:B5,0))/(ISNUMBER(A1:A5)+ISNUMBER(B1:B5)),""))
Ух ты. Это действительно приятно.
Спасибо! Я думал, что это также будет работать в случае отсутствия целой строки, но это не = ОБЪЕДИНЕНИЕ (1,6, BYROW (A1: B5, LAMBDA (rw, AGGREGATE (1,6, rw)))))
Для версий Excel, отличных от 365:
=AVERAGE(SUBTOTAL(1,OFFSET(A1:B1,ROW(A1:B4)-MIN(ROW(A1:B4)),)))
Если столбцы несмежные (допустим A1:A4 и C1:C4):
=AVERAGE(
MMULT(
CHOOSE(
{1, 2},
IF(ISNUMBER(A1:A4), A1:A4, C1:C4),
IF(ISNUMBER(C1:C4), C1:C4, A1:A4)
),
{1; 1}
) / 2
)
Обратите внимание, что если вы не используете англоязычную версию Excel, разделитель внутри констант
{1, 2}
и
{1; 1}
может потребовать внесения изменений.
обратите внимание, что два двух диапазона не должны быть смежными.
@BoTz Добавлена новая версия.
Довольно некрасиво, но в данном конкретном случае это должно сработать, и я думаю, что это подходит для Excel-2019:
Если в строке возможна только одна ошибка #N/A:
=AVERAGE(IFNA(A1:A4,B1:B4),IFNA(B1:B4,A1:A4))
Если оба значения могут быть #N/A в строке:
=AVERAGE(IFNA(IFNA(A7:A11,B7:B11),""),IFNA(IFNA(B7:B11,A7:A11),""))
Если это не фактическая #N/A ошибка, а текст:
=AVERAGE(IFNA(IF(ISNUMBER(A14:A19),A14:A19,B14:B19),""),IFNA(IF(ISNUMBER(B14:B19),B14:B19,A14:A19),""))
Полученные результаты:
Как результирующее среднее значение равно 3 для последнего?