Передний груз и Задний груз | График нормального распределения и S-кривые в Excel

Ответ 1

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

С формулами

Кривая

Заметки:

1. Если вы хотите изменить ячейки, вы должны перетащить ячейки вниз или вверх, чтобы завершить серию
2. Если вы хотите изменить общую стоимость, вы можете изменить множитель
3. Если вы хотите изменить наклон кривой, вы можете изменить делитель в столбце C, который в настоящее время установлен на 2, если он равен -2, угол наклона изменит направление, вы можете поэкспериментировать с другими числами, направление зависит от них меньше нуля или больше нуля

Для копирования прошлого

=A2+180/($G$3-1)
=RADIANS(A2)
=$G$4*SIN(B2 + SIN(B2)/2)

Ответ 2

Я использовал фактические математические формулы, чтобы получить результат. Мне кажется, что вы хотели достичь. Оранжевые ячейки в разделе "Перекошено" - это те, которые можно изменить для изменения степени и направления перекоса. Ниже приведены некоторые рисунки для демонстрации, за которыми следуют используемые формулы.

Формулы в строке 5, столбец

B: =(A5*$A$2)+0 (0 - среднее значение, вы можете изменить, как вам нравится)

C: =(1/($A$2* SQRT(2*PI())))*EXP(-(B5^2)/2)

D: =0.5*(1+ERF(B5/SQRT(2)))

E: =$A$1*C5

F: =(A5*$A$2*(1+$F$2*SIN((F4*PI())/(2*$F$4))))+0 (0 - среднее значение, вы можете изменить как вам нравится)

G: =(1/($A$2* SQRT(2*PI())))*EXP(-((F5+$G$2)^2)/2)

H: =0.5*(1+ERF((B5+$G$2)/SQRT(2)))

I: =$A$1*G5

Ответ 3

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

Изменение среднего значения перемещает пик влево или вправо. Изменение стандартного отклонения делает величины более однородными или более переменными. Я использовал 0-1000 в качестве диапазона по умолчанию в примере ниже, но должно быть легко изменить формулу, чтобы получить любое значение, которое вы хотите. ПРИМЕЧАНИЕ Чтобы выполнить ваше требование о том, что все ячейки должны быть ненулевыми, вам нужно вручную корректировать числа, пока не получите кривую, которая подходит.

Желтые ячейки предназначены для ввода данных, зеленые ячейки - это количество (поэтому, если вы добавите ячейки, они должны быть пронумерованы в соответствии с последовательностью).

Формула в ячейке B7 (скопирована в ячейку B16): =NORMDIST($A7*1000/MAX($A$6:$A$17),$B$3,$B$4,TRUE)-NORMDIST($A6*1000/MAX($A$6:$A$17),$B$3,$B$4,TRUE),

Формула в ячейке C7 (скопирована в ячейку C16): =IF(A7=MAX($A$6:$A$17),$C$5-SUM(C$6:C6),ROUND(B7/SUM($B$7:$B$17)*$C$5,0))

Добавление новых корзин достаточно простое и все еще основано на диапазоне 0-1000, поэтому вам не нужно изменять какие-либо числа, кроме добавления строк и копирования формул:

Вышеприведенный пример также показывает, как узкое стандартное отклонение и высокое среднее значение объединяются, чтобы исходные бункеры имели очень небольшое количество. Но есть значение (пока счет достаточно велик).

Вы можете заранее определить различные варианты выбора асимметрии, если они будут использоваться другими людьми (например, сделать столбец B зависимым от поиска), но, надеюсь, это будет достаточно расширяемым для ваших нужд.

Ответ 4

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

import numpy as np
import pandas as pd
from scipy.stats import skewnorm

class PythonSkewedNormal(object):
    _reg_clsid_ = "{1583241D-27EA-4A01-ACFB-4905810F6B98}"
    _reg_progid_= 'SciPyInVBA.PythonSkewedNormal'
    _public_methods_ = ['GeneratePopulation','BinnedSkewedNormal']

    def GeneratePopulation(self,a, sz):
        # https://docs.scipy.org/doc/numpy-1.15.1/reference/generated/numpy.random.seed.html
        np.random.seed(10);
        #https://docs.scipy.org/doc/scipy-0.19.1/reference/generated/scipy.stats.skewnorm.html
        return skewnorm.rvs(a, size=sz).tolist();

    def BinnedSkewedNormal(self,a, sz, bins):
        # https://docs.scipy.org/doc/numpy-1.15.1/reference/generated/numpy.random.seed.html
        np.random.seed(10);
        #https://docs.scipy.org/doc/scipy-0.19.1/reference/generated/scipy.stats.skewnorm.html
        pop = skewnorm.rvs(a, size=sz); #.tolist();
        bins2 = np.array(bins)
        bins3 = pd.cut(pop,bins2)

        table = pd.value_counts(bins3, sort=False)

        table.index = table.index.astype(str)

        return table.reset_index().values.tolist();

if __name__=='__main__':
    print ("Registering COM server...")
    import win32com.server.register
    win32com.server.register.UseCommandLine(PythonSkewedNormal)

И код клиента VBA

Option Explicit

Sub TestPythonSkewedNormal()

    Dim skewedNormal As Object
    Set skewedNormal = CreateObject("SciPyInVBA.PythonSkewedNormal")

    Dim lSize As Long
    lSize = 100

    Dim shtData As Excel.Worksheet
    Set shtData = ThisWorkbook.Worksheets.Item("Sheet3") '<--- change sheet to your circumstances
    shtData.Cells.Clear

    Dim vBins
    vBins = Array(-5, -4, -3, -2, -1, 0, 1, 2, 3, 4, 5)

    'Stop
    Dim vBinnedData
    vBinnedData = skewedNormal.BinnedSkewedNormal(-5, lSize, vBins)

    Dim rngData As Excel.Range
    Set rngData = shtData.Cells(2, 1).Resize(UBound(vBins) - LBound(vBins), 2)

    rngData.Value2 = vBinnedData

    'Stop

End Sub

Образец вывода

(-5, -4]        0
(-4, -3]        0
(-3, -2]        4
(-2, -1]       32
(-1, 0]        57
(0, 1]          7
(1, 2]          0
(2, 3]          0
(3, 4]          0
(4, 5]          0

Оригинальный код хранится в моем блоге