Как мне вычислить среднее значение из массива условных медиан, используя одну формулу?

#excel #dynamic-arrays

Вопрос:

Мне нужно выполнить двухэтапное вычисление по одной формуле.

Набор данных содержит несколько записей для каждой статьи и выглядит следующим образом:

На данный момент я использую «вспомогательный» столбец, чтобы сначала получить массив условных медиан, а затем вычислить среднее значение.

Шаг 1: Вычислите среднее изменение цены на продукт в процентах.

Шаг 2: Вычислите среднее значение по этим медианам (считая каждую статью один раз).

Я хотел бы объединить эти два шага в одной формуле (т. Е. используя функции динамического массива).

Заранее спасибо.

Ответ №1:

Я думаю, может быть, вспомогательный столбец проще. Однако это можно сделать в O365, который любезно предоставил мой работодатель:

 =AVERAGE((INDEX(SORTBY(B:B,A:A,1,B:B,1),MATCH(UNIQUE(FILTER(A:A,A:A<>"")),A:A,0) FLOOR((COUNTIF(A:A,UNIQUE(FILTER(A:A,A:A<>"")))-1)/2,1)) 
INDEX(SORTBY(B:B,A:A,1,B:B,1),MATCH(UNIQUE(FILTER(A:A,A:A<>"")),A:A,0) CEILING((COUNTIF(A:A,UNIQUE(FILTER(A:A,A:A<>"")))-1)/2,1)))/2)

Таким образом, в основном сопоставление дает вам массив номеров строк, с которых начинается каждая группа, а функции floor / ceiling дают вам смещение оттуда до середины отсортированных чисел в этой группе.

Смотрите Это для описания формы медианной функции без учета регистра.

Предполагается, что данные предварительно отсортированы по букве группы, но не по числам внутри каждой группы.

Итак, индекс теперь дает вам массив без взлома — я не знал этого, прежде чем пытаться это сделать.