Как я могу оценить N-е значение (или N-й процентиль) по частотному распределению в Excel?

Question

У меня есть таблица Excel, содержащая данные о частоте, а не необработанные данные. Я хотел бы легко найти такие вещи, как 653-е значение или 95-й процентиль.

В качестве примера, скажем, необработанные данные - это задержка на пакет. Данные подсчета частоты могут выглядеть так:

1ms = 1234567
3ms = 34254
5ms = 33034
7ms = 6901
10ms = 76

В корзину попало 6901 пакет «больше 5 миллисекунд, но не более 7 миллисекунд».

Поскольку сегменты довольно гранулированные, я подумал, что мог бы сделать некоторую хитрость для вычисления некоторой стандартной статистической информации, как если бы у меня были необработанные данные. Для вычисления среднего значения это не требуется, но что-то вроде медианы, стандартного отклонения или 99-го процентиля зависит от диапазона данных. Кто-нибудь может помочь?

Answer 1

Я опоздал на вечеринку примерно на 1,5 года, но я думал, что отправлю сообщение только для тех, кто наткнется на это.

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

Чтобы получить CDF, вам нужно рассчитать совокупную вероятность для каждого сегмента. Я предполагаю, что всегда есть некоторая задержка, поэтому примите 0 как минимальное значение с частотой 0. Чтобы найти значение CDF для каждой верхней границы сегмента, используйте формулу

(sum of frequencies in bucket and previous buckets)/(sum of all frequencies)

Для предоставленных вами выборочных данных кумулятивные точки вероятности будут

{(0,0); (1;0.943); (3,0.969); (5,0.995); (7,0.999); (10,1)}

Теперь для фишинга. Если у вас есть здравый смысл понять, как выглядит распределение, например, вы думаете, что где-то около 25% задержек составляют менее 0,1 мс, вы можете добавить эти интуиции к своим данным. Если вы не знаете, как должен выглядеть дистрибутив, вы можете просто посмотреть, что у вас есть.

Отсюда у вас есть два варианта: либо (а) линейно интерполировать между имеющимися точками, либо (б) подобрать функциональную форму, такую как бета-распределение, к вашим данным. (а) проще, потому что не требует регрессии; однако, это не даст вам более детальную картину, чем у вас уже есть, и для расчета частот требуется немного формулы кунг-фу в Excel. (б) предоставит вам детализированную картину, которая, скорее всего, больше похожа на базовые данные, чем линейная интерполяция, и для получения частот для любого сегмента или процентиля требуются только простые простые формулы Excel; однако для этого требуется регрессия, для которой требуется надстройка Solver. Я предпочитаю вариант (б), потому что он дает вам максимальную отдачу (т. Е. Усилия).

Answer 2

Для частот, которые у вас есть, в Excel не хватает строк для хранения необработанных данных. Я уменьшил частоты в 100 раз (кроме последней), чтобы они поместились на листе Excel. Если вы запустите этот код, вы должны получить необработанные данные, которые при использовании с такой формулой, как

{=FREQUENCY($A$2:$A$13095,{1,3,5,7,10})}

дает вам (мои уменьшенные частоты) 12345, 343, 330, 69, 7. Он производит случайные числа в ваших диапазонах.

Sub MakeRawData()

    Dim i As Long
    Dim dRaw As Double

    For i = 1 To 12345
        dRaw = Rnd
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 343
        dRaw = (2 - 1 + 1) * Rnd + 1
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 330
        dRaw = (4 - 3 + 1) * Rnd + 3
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 69
        dRaw = (6 - 5 + 1) * Rnd + 5
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

    For i = 1 To 7
        dRaw = (9 - 7 + 1) * Rnd + 7
        Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = dRaw
    Next i

End Sub

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