Условное стандартное отклонение и стандартные ошибки в Excel

Question

У меня есть данные, которые я бы хотел отобразить в электронной таблице Excel 2013. Вот фрагмент данных

-------------------------------------------
| intervalMode  | keyboardEventsPerSecond |
-------------------------------------------
| orienting     | 0                       |
| orienting     | 0                       |
| orienting     | 0.171115674             |
| orienting     | 0                       |
| orienting     | 0                       |
| opportunistic | 0                       |
| opportunistic | 0.016913605             |
| opportunistic | 0                       |
| opportunistic | 0                       |
-------------------------------------------

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

=AVERAGEIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)

который использует первый и второй столбец фрагмента, показанный в качестве первого и третьего аргумента. Используя эту формулу, я получаю эту таблицу:

--------------------------------------------------
| intervalMode  | average keyboardEventsPerSecond |
--------------------------------------------------
| lean-back     | 0.009044655                     |
| opportunistic | 0.01058782                      |
| orienting     | 0.036665215                     |
| purposeful    | 0.03851359                      |
| respite       | 0.120037091                     |
--------------------------------------------------

Замечательно. Если я нарисую эту таблицу, я получу это

Я хотел бы добавить строки ошибок, которые я могу сделать в инструментах диаграммы Excel. Однако я в конечном итоге с этим

Столбики ошибок для первых двух столбцов простираются ниже оси, и они не должны.

Чтобы это исправить, я собирался переключиться на пользовательские панели ошибок и добавить в свою таблицу два столбца для положительных и отрицательных баров ошибок. Следовательно, я ожидаю использовать формулу что-то вроде

=STERRIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)

Но никакие формулы, кажется, не встроены для стандартной ошибки. Таким образом, я мог бы свернуть это сам, используя что-то вроде

=STDEVIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)/SQRT(COUNTIF(AllDataFromSQL!A2:A14057,Graphs!A3))

Но STDEVIF , кажется, не существует. Конечно, я могу добавить дополнительные столбцы и вычислить их вручную, но есть ли лучший способ рассчитать стандартную ошибку таким же образом, как я использовал AVERAGEIF для простого вычисления условного среднего?

Answer 1

Вы можете рассчитать условное стандартное отклонение, используя "формулу массива" - синтаксис будет таким

=STDEV(IF(AllDataFromSQL!A$2:A$14057=Graphs!A3,AllDataFromSQL!B$2:B$14057))

Это формула массива, и вам необходимо подтвердить это нажатием CTRL+SHIFT+ENTER. Для этого выберите ячейку с формулой, нажмите F2, чтобы выбрать формулу, а затем, удерживая нажатой клавишу ENTER, удерживайте клавиши CTRL и SHIFT. Если все сделано правильно, то фигурные скобки, такие как {и}, появятся вокруг формулы в строке формул.

Вы должны сделать это для первой формулы .... затем вы можете скопировать / заполнить