Microsoft Excel: ввод динамического числа параметров в функцию

Question

У меня есть много таблиц, которые выглядят следующим образом:

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

Вычисление среднего значения довольно просто с помощью =(C2*B2+C3*B3+C4*B4+C5*B5+C6*B6)/SUM(C2:C6)

Я не могу получить медиану правильно. Я обнаружил, что есть функция MEDIAN, но число параметров, которые я должен дать ей, меняется в зависимости от количества ответов. Есть ли что-то вроде «useXTimes (paramter, x)», которое дает параметр x раз функции? Тогда я мог бы использовать:

=MEDIAN(useXTimes(B1,C1);useXTimes(B2,C2);useXTimes(B3,C3);useXTimes(B4,C4);useXTimes(B5;C5);useXTimes(B6,C6))

и он будет разбираться на =MEDIAN(1;2;2;3;3;4;4;4;4); в моем примере, который будет возвращать 3 , что я и хочу.

В целом, я не очень знаком с формулами Excel и, возможно, есть более простое решение, которое я не вижу.

Answer 1

Чтобы найти медиану, мы можем вычислить процентильные сегменты и посмотреть, где находится 50-й процентиль. Это будет наша медиана.

Сначала добавьте общее количество ответов в ячейку C7. Формула =SUM(C2:C6) .

Затем добавьте три столбца к исходному рабочему листу:

• Столбец D будет содержать процент каждого ответа от общего числа. Формула для ячейки D2 имеет вид =C2/$C$7 и вы можете перетащить ее вниз до D6.
• Столбец E будет содержать совокупный процент. Формула для ячейки E2:=SUM($D$2:D2) и вы можете перетащить ее вниз до E6.
• Столбец F будет использоваться для проверки того, попадает ли 50-й процентиль в область процентилей в соседней ячейке в столбце E. Формула для ячейки F2 имеет вид =E2>=0.5 и вы можете перетащить ее вниз до F6.

Ваш рабочий лист будет выглядеть так:

На этом этапе, даже без столбца F, легко увидеть, где находится медиана. Ячейка E4 - это процентиль, где 50%, поэтому медиана в этом случае равна 3.

Мы можем использовать столбец F, чтобы найти первое вхождение значения "ИСТИНА" по следующей формуле: =MATCH(TRUE,F2:F6,0) . Результатом в этом случае будет 3, что является индексом "ИСТИНА" в диапазоне F2: F6.

Мы можем использовать эту последнюю формулу в качестве индекса, чтобы получить фактический медианный ответ (текстовую метку) из столбца A, например: =INDIRECT("A" & (MATCH(TRUE,F2:F6,0)+1)) , Поместите эту последнюю формулу в любое место на рабочем листе, в этом случае результат будет "Нейтральный".

Answer 2

Попробуйте использовать эту формулу:
=MEDIAN(REPT(B1:B5,C1:C5))
затем нажмите Ctrl + Shift + Enter
Потому что это формула массива