Как правильно выровнять средние значения и стандартное отклонение в электронной таблице?

Question

Я не уверен, как лучше всего сформулировать этот вопрос прямо сейчас, поэтому я воспользуюсь примером со случайными числами. Я начинаю со значений, назначенных идентификаторам, так что каждый идентификатор может быть n=1, n=2, ... и т. д.

ID  Value   
1   1235        
1   326     
1   567     
2   768     
2   646     
3   4367        
3   346     
3   35      
4   436     
5   3467        
5   46      
6   3467        
6   3532        
6   457     
7   3463        
7   3463
7   9328
7   2498

и т. д.

Я хочу рассчитать в Excel/Calc среднее значение и SD так, чтобы значения были правильно выровнены (в идеале были бы объединены ячейки), учитывая, что это одна, две, три... и т. д. ячейки ввода и одна ячейка вывода.

Пример скриншота:

Что я хочу получить. AVG и SD означают правильные значения для (случайных) заданных данных; таким образом, AVG и SD правильно выровнены]1

То есть мне нужен автоматизированный способ расчета среднего значения и SD с учетом различных n, чтобы они были правильно выровнены/отформатированы.

Должен быть какой-то простой способ сделать это, но сейчас я не имею ни малейшего понятия. -_-

Буду признателен за любые предложения.

Answer 1

Это несложно, если предположить, что Column Aотсортирован, поэтому мы имеем дело с непрерывными диапазонами в Column B (что и предполагают визуальные аспекты вашего вопроса). Настройте Column Eкак вспомогательный столбец с помощью этой формулы:

• E2→=IF(A2=A3, E3, ROW())

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

• B2→=IF(A1=A2, "", AVERAGE(B2:INDIRECT("B" & E2)))
• C2→=IF(A1=A2, "", STDEV(B2:INDIRECT("B" & E2)))

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

       

И, конечно, вы можете скрыть Column Eили использовать какой-либо невидимый столбец (например, Z) в качестве вспомогательного столбца. Обратите внимание, что это решение не использует формулы массива.

Answer 2

Это не совсем то, что вы просили, но я бы использовал сводную таблицу:

(Я изменил заголовок первого столбца на ID и отформатировал второй и третий столбцы так, чтобы отображалось только два десятичных знака, в противном случае это будет просто то, что отображается в PivotTable Builder.)

Вы можете получить то, что просили, разместив:

=IF($A2=$A1,"",AVERAGEIF($A2:$A19,$A2,$B2:$B19))

в C2, и:

=IF($A2=$A1,"",STDEV.P(INDIRECT("R"&MATCH($A2,$A:$A,0)&"C2:R"&MATCH($A2,$A:$A,1)&"C2",0)))

в D2 и заполняя оба столбца вниз. Внешний IF в каждой формуле должен поместить значение только в первую строку, содержащую определенный ID. Остальная часть формулы C2 должна быть простой, AVERAGEIF усредняет числа, для которых определенный критерий верен. В этом случае он смотрит в первый столбец, выбирает числа с тем же значением, что и значение в текущей строке в первом столбце, затем усредняет соответствующие числа во втором столбце.

К сожалению, функции "STDEVIF" нет (по крайней мере, в Excel 2011 на Mac, возможно, она есть в любой другой программе для работы с электронными таблицами, которую вы используете. Если да, просто используйте ее вместо AVERAGE в формуле C2), так что вам придется быть хитрым :-). Подход заключается в том, чтобы найти диапазон ячеек, для которых вы хотите получить стандартное отклонение, создать ссылку на эти ячейки, а затем передать эту ссылку в STDEV.P. Диапазон создается путем нахожденияпервыйстроку в столбце 1 с тем же значением, что и значение в текущей строке в столбце 1, затем находимпоследнийстроку в столбце 1 с тем же значением, что и значение в текущей строке в столбце 1. Эти два значения определяют верхнюю и нижнюю границы поддиапазона столбца 1, который вы хотите использовать, поэтому создайте ссылку в стиле R1C1 в строке, используйте INDIRECT, чтобы превратить ее в фактическую ссылку, а затем передайте ее в STDEV.P. Просто! :-) Ладно, это немного отвратительно, но это работает.

Answer 3

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

Для построения таблицы с формулами используйте следующие две функции.

В C2 поставьте

=IF(A2<>A1,AVERAGEIF($A$2:$A$13,A2,$B$2:$B$13),"")

В D2 поставьте и введите, нажавctrl+shift+enter

=IF(A2<>A1,STDEV(IF($A$2:$A$13=A2,$B$2:$B$13)),"")

Затем скопируйте эти формулы вниз.

IF(A2<>A1... в начале по сути говорит, что нужно показывать что-либо только в том случае, если столбец A в этой строке отличается от столбца выше.

Averageif работает именно так, как вы и ожидали.

Столбец D представляет собой формулу массива, поэтому сначала он входит и выполняет оператор if для каждой ячейки в диапазоне, и он возвращает массив, например (1,14,13,3,FALSE,FALSE...) для каждой ячейки, а затем вычисляет стандартное отклонение для этого, которое по сути должно игнорировать значения FALSE.

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