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

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

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

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

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

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

в С2 и:

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

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

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

Answer 2

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

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

В С2 поставь

=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.

Этот метод предполагает, что данные отсортированы по идентификатору. Среднее и стандартное вычисление Dev были бы правильными, если бы они не были отсортированы, но они будут отображаться при каждом изменении идентификатора, а не только при первом.

Answer 3

Это несложно, если вы предполагаете, что столбец A отсортирован, поэтому мы имеем дело со смежными диапазонами в столбце B (что и предлагают визуальные аспекты вашего вопроса).  Установите столбец 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() для построения диапазона между текущей ячейкой и последней ячейкой с тем же значением идентификатора.

       

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