logo标签列表

前期加重和后期加重 | 正态分布柱形图和Excel中的S曲线

excelvbaexcel-formulanormal-distribution
12

大多数人可能已经了解正态分布曲线，但对于前装和后装的正态分布还不熟悉的人，我想提供背景知识，然后继续陈述我的问题。

前装分布：如下所示，它的开始非常迅速。例如，在一个项目中，当假定更多的资源在项目早期消耗时，成本/工时会在项目开始时积极分配。 Front-Loaded Distribution / S Curve

后装分布：与前装分布相反，它的斜率较低，并且朝着项目结束逐渐加剧。例如，当假定大多数资源在项目后期消耗时。 Rear Load Distribution S Curve

在上图中，绿线是S曲线，表示在建议时间内资源的累积分配情况，蓝色柱子则表示该时间段内资源（成本/工时）的独立分配。

为参考，在下面提供钟形曲线/标准正态分布（当平均值=中位数）图表以及相关公式。

Normal Distribution S Curve

问题陈述：我能够生成正态分布曲线（参见下图中的公式），但我无法找到前装或后装曲线的解决方案。

如何使正态分布呈现右偏（前装/正偏分布，这意味着平均值大于中位数）和左偏（后装/负偏分布，这意味着平均值小于中位数）？

Gaussian Bell Curve with Excel Formula

公式说明：

B8单元格表示任意选择的标准差。它影响正态分布的峰度。在上面的截图中，我选择正态分布的范围为-3SD到3SD。

B9到B18单元格表示使用以下公式均匀分配Z分数：

=B8-((2*$B$8)/Period)

单元格C9到C18表示基于Z得分和数量的正态分布，使用以下公式进行计算：

=(NORMSDIST(B9)-NORMSDIST(B8))*Amount/(1-2*NORMSDIST($B$8))

更新：在评论中跟随其中一个链接后，我最接近以下情况。由于使用了volatile Rand()函数，导致图表不像应该那样平滑，所以问题被用黄色标记出来。由于我给定的公式并未创建ZigZag模式，我确信我们可以获得偏斜正态分布且也很平滑！ 正态分布中的ZigZag列问题

注意：

  1. 我正在使用Excel 2016，因此如果有任何新引入的公式可以解决我的问题，我欢迎使用。此外，我也不惧使用UDF。

  2. 前置和后置分布的数量是概念性的。它们可能会变化。我只关心结果图表的形状。

请帮帮我！

1
您可能想查看JackBean在 https://www.mrexcel.com/forum/excel-questions/477702-generating-skewed-normal-distribution-random-numbers.html 的回复——它并不完全符合您的期望形状，但您可以通过编辑STDIN、AVGIN、START和END字段（在您的示例中，您需要10个bin）轻松获得前置和后置分布。 - Jon
4
非常好的表述您的问题！！！ :) - Wizhi
1
@Jon - Jackbean的回答和期望解决方案的区别在于，生成随机数的方法有很多种，但是我想要的是这些随机数总和等于期望值。因此，输入应该是3个变量：（Bins，Total Cost，Front / Back），并且需要将Total Cost以前后方式分配到Bins中。Jackbean的回答无法控制总和。 - jainashish
1
@jainashish 在单元格B3中输入总数（“Select”变量）。然后计数列将为您提供总值分布。但是，由于公式有点抽象，这可能并不是非常清楚。 - Jon
@Jon - 是的，但它又产生了另一个问题，偏度方向上的大量箱子变为空的（填充为0）。每个箱子必须有一些值。 - jainashish
显示剩余3条评论
5个回答
8
你可以使用以下方法生成曲线，并使用它们生成的数字来满足你的需求。

enter image description here

使用公式

enter image description here

曲线

enter image description here

注意：

  1. 如果您想更改箱子，请拖动单元格向下或向上以完成系列
  2. 如果您想更改总成本，可以更改乘数
  3. 如果您想更改曲线的倾斜度，可以更改列C中的分频器，当前设置为2，如果是-2，则倾斜方向将改变，您可以尝试不同的数字，方向取决于它是否小于零或大于零

复制粘贴用

=A2+180/($G$3-1)
=RADIANS(A2)
=$G$4*SIN(B2 + SIN(B2)/2)
2
我使用了实际的数学公式来得出结果。看起来这正是您想要实现的。在“倾斜”部分中，橙色单元格是可以更改的，以变化倾斜的程度和方向。下面是一些演示图片，后面跟着使用的公式。

enter image description here

enter image description here

enter image description here

第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

2
如果您想确保垃圾桶始终有值，可以使用以下方法，该方法使用正态分布并仅更改均值和标准差以获得所需的曲线。
更改均值将使峰值向左或向右移动。更改标准差会使数量更加均匀或更加变化。在下面的示例中，我使用0-1000作为默认范围，但应很容易修改公式以带入任何您想要的值。请注意，为了满足您所有垃圾桶必须非零的要求，您需要手动调整数字，直到获得适合的曲线。
黄色单元格用于数据输入，绿色单元格用于计数（因此，如果添加垃圾桶，则需要按顺序编号）。

normal and skewed distribution examples

单元格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范围，因此您不需要更改任何数字，只需添加行并复制公式即可。

skewed distribution with more bins

上面的例子也展示了一个较窄的标准差和较高的平均值是如何结合起来使得起始区间的数量非常少。但只要计数足够大，仍然存在一个值。
如果这将被其他人使用，您可能希望预定义不同的偏度选择（例如，使B列依赖于查找），但希望这对您的需求具有可扩展性。
0

基于 @usmanhaq 的答案，编写了用于分布曲线模拟的 VBA 宏。已修正前后装载曲线的 100% 缩放。 点击此处前往 Github Lib

Excel Snapshot

0
如果您愿意接受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

原始代码已上传至我的博客

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接