通过Microsoft Excel绘制CDF图表

问题描述：

我不太确定是否可以在这里或 SuperUser

I'm not quite sure if I can ask this question here or on SuperUser,

我想知道如何从我的Excel数据中绘制 CDF 图表. 我的数据是这样的(我的真实数据有22424条记录):

I want to know how can I plot a CDF chart out of my excel data. My data is something like this (my real data have 22424 records):

1   2.39E-05
1   2.39E-05
1   2.39E-05
2   4.77E-05
2   4.77E-05
2   4.77E-05
4   9.55E-05
4   9.55E-05
4   9.55E-05
4   9.55E-05
4   9.55E-05
4   9.55E-05
8   0.000190931
8   0.000190931

答

您可以使用NORMDIST函数并将最终参数设置为true:

You can use the NORMDIST function and set the final parameter to true:

作为一个例子，假设我有0.1至2.0的20个数据点，增量为0.1，即0.1、0.2、0.3 ... 2.0.

As an example, suppose I have 20 data points from 0.1 to 2.0 in increments of 0.1 i.e. 0.1, 0.2, 0.3...2.0.

现在假设该数据集的平均值为1.0，标准差为0.2.

Now suppose that the mean of that dataset is 1.0 and the standard deviation is 0.2.

要获取CDF图，我可以对每个值使用以下公式:

To get the CDF plot I can use the following formula for each of my values:

=NORMDIST(x, 1.0, 0.2, TRUE) -- where x is 0.1, 0.2, 0.3...2.0

要从数据和相同的值中删除重复的条目，可以使用以下代码.

To remove duplicate entries from your data and sum values that are the same you can use the following code.

在excel中，将数据放置在sheet1中，从单元格A1开始
按ALT + F11打开VBE
现在Insert > Module可以在编辑器中放置一个模块
将下面的代码粘贴并粘贴到模块中
将光标置于RemoveDuplicates中的任意位置，然后按F5键运行代码

In excel, place you data in sheet1, starting in cell A1
Press ALT + F11 to open VBE
Now Insert > Module to place a module in the editor
Cut and paste code below into module
Place cursor anywhere in RemoveDuplicates and Press F5 to run the code

结果，您唯一的，汇总的结果将显示在工作簿的Sheet2中.

As a result, your unique, summed results will appear in Sheet2 in your workbook.

Sub RemoveDuplicates()
    Dim rng As Range
    Set rng = Range("A1:B" & GetLastRow(Range("A1")))

    rng.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Worksheets("Sheet2").Range("A1"), Unique:=True

    Dim filteredRng As Range
    Dim cl As Range

    Set filteredRng = Worksheets("Sheet2").Range("A1:A" & GetLastRow(Worksheets("Sheet2").Range("A1")))

    For Each cl In filteredRng
        cl.Offset(0, 1) = Application.WorksheetFunction.SumIf(rng.Columns(1), cl.Value, rng.Columns(2))
    Next cl
End Sub

Function GetLastRow(rng As Range) As Long
    GetLastRow = rng.End(xlDown).Row
End Function

通过Microsoft Excel绘制CDF图表

相关推荐