Расчеты для групп данных

Question

У меня есть 3 столбца данных в листе Excel. Первый столбец представляет почтовые индексы, второй представляет значения в этом почтовом индексе, а третий столбец представляет частоту этого значения в почтовом индексе.

В настоящее время, если почтовый индекс имеет несколько значений, он указывается несколько раз в столбце с соответствующим значением и частотой. Поэтому для каждого почтового индекса существует много строк данных.

Я связал картину моих данных.

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

Answer 1

Основываясь на ответе CharlieRB, вы можете получить РЕЖИМ в строке 6, введя формулу массива

=SUMPRODUCT(--($A:$A=G1), --($C:$C=MAX(IF(($A:$A=G1), ($C:$C)))), ($B:$B))

в ячейке G6.

Answer 2

Это решение не является полным, но оно поможет вам начать.

Добавьте столбец SUM справа от ЧАСТОТЫ. Добавьте формулу для расчета БИ по ЧАСТОТЕ; =B2*C2 . Затем скопируйте эту формулу вниз по столбцу. Это даст вам цифры для работы, чтобы определить другие значения, которые вы хотите.

Теперь начните новый столбец для каждого почтового индекса, для которого вы хотите данные. В моем примере ниже я использовал G1 и H1 . Под каждым из них вы вводите формулу массива для расчета желаемых значений. Давайте сначала поработаем над 43015.

В S2 введите =AVERAGE(IF(($A:$A=G1),($D:$D))) , затем нажмите F2. Чтобы создать массив, нажмите Ctrl+Shift+Enter вместе. Он будет помещать формулу в {} если все сделано правильно. Эта формула будет смотреть в столбце A, чтобы найти ZIP, соответствующий ячейке G1 , а затем усреднит значения в столбце D, соответствующие ZIP.

Выбрав ячейку G2 , перетащите нижний правый угол к ячейке для 43016 и отпустите. Это должно автоматически скорректировать смещение и рассчитать в соответствии с ячейкой H1 .

Вы собираетесь сделать то же самое для следующих формул.

Для мин; =MIN(IF(($A:$A=G1),($B:$B)))

Для Макса; =MAX(IF(($A:$A=G1),($B:$B)))

Стенд Dev; =STDEV(IF(($A:$A=G1),(($B:$B)*($B:$B)*($C:$C))))

Примечание: если вы измените что-либо в формулах после того, как сделаете их массивом, вам придется снова сделать их массивом, нажав Ctrl+Shift+Enter.