Скажем, у меня есть список целочисленных значений, от 1 до 10. Вместо фактического набора данных у меня есть только количество каждого значения. Например:
1 | 73
2 | 121
3 | 155
4 | 149
5 | 187
6 | 180
7 | 166
8 | 148
9 | 120
10 | 81
Как видите, перечисление каждого значения по отдельности (73 1s, 121 2s и т. д.) заняло бы невероятно много времени, но это единственный известный мне способ использования STDEV().
Как рассчитать стандартное отклонение значений?
Возможная альтернатива, предполагая, что 1 находится в A1, в C1:
=B1*(A1-SUMPRODUCT(A$1:A$10,B$1:B$10)/SUM(B$1:B$10))^2
скопировано до C10. В B11 скопировано в C11:
=SUM(B1:B10)
In D11:
=SQRT(C11/(B11-1))
2.520979 - неправильный ответ, забудьте вышесказанное!Эксель использует:
для вычисления STDEV и выше просто разбивает это на шаги, чтобы соответствовать доступному формату данных.
Пользовательская функция, определяемая пользователем (также известная как UDF), может быть наиболее целесообразным путем.
Ваши значения из столбца A были расширены до кратных значений в столбце B в D2:D1381 (см. ниже).
STDEV, STDEV.P и STDEV.S формулы в E2:G2:
=STDEV($D2:$D1381)
=STDEV.P($D2:$D1381)
=STDEV.S($D2:$D1381)
Формулы UDF в E3: G3:
=udf_STDEV_Exploded($A2:$A11, 1) ' or =udf_STDEV_Exploded($A2:$A11)
=udf_STDEV_Exploded($A2:$A11, 2)
=udf_STDEV_Exploded($A2:$A11, 3)
Формулы UDF основаны на следующем коде модуля.
Function udf_STDEV_Exploded(rng As Range, Optional iTYP As Long = 1)
Dim r As Long, v As Long, vVALs As Variant
ReDim vVALs(0)
For r = 1 To rng.Rows.Count
For v = 1 To rng.Cells(r, 2).Value2
vVALs(UBound(vVALs)) = rng.Cells(r, 1).Value2
ReDim Preserve vVALs(0 To UBound(vVALs) + 1)
Next v
Next r
ReDim Preserve vVALs(0 To UBound(vVALs) - 1)
Select Case iTYP
Case 1
udf_STDEV_Exploded = WorksheetFunction.StDev(vVALs)
Case 2
udf_STDEV_Exploded = WorksheetFunction.StDev_P(vVALs)
Case 3
udf_STDEV_Exploded = WorksheetFunction.StDev_S(vVALs)
Case Else
'do nothing
End Select
End Function
Я добавил возможность обработки массива с помощью STDEV, STDEV.P или STDEV.S. Функция СТАНДОТКЛОН используется по умолчанию.
Дополнение
Код для разбивки значений из A2: B11 в длинный столбец значений на листе:
Sub stdev_vals()
Dim rw As Long, f As Long, n As Long
With Worksheets("Sheet1")
For rw = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
.Cells(Rows.Count, 4).End(xlUp).Offset(1, 0).Resize(.Cells(rw, 2).Value2, 1) = .Cells(rw, 1).Value2
Next rw
End With
End Sub
Сначала вычислите среднее значение (здесь оно обозначено как THE_AVG):
=SUM(BIN_NUM*REPEATS)/SUM(REPEATS)
вводится как формула массива (CTRL-SHIFT-ENTER). Затем рассчитайте стандартное отклонение:
=SQRT(SUM((BIN_NUM-THE_AVG)^2*REPEATS)/(SUM(REPEATS)-1))
снова вводится как формула массива. При желании два расчета можно свернуть в одну формулу:
=SQRT(SUM((BIN_NUM-SUM(BIN_NUM*REPEATS)/SUM(REPEATS))^2*REPEATS)/(SUM(REPEATS)-1))
В этих формулах BIN_NUM представляет диапазон ячеек с номерами ячеек (скажем, A1: A10), а REPEATS представляет диапазон ячеек с количеством ячеек в каждой ячейке (скажем, B1: B10).
надеюсь, это поможет
