通过Microsoft Excel绘制CDF图表 [英] Plot a CDF chart by Microsoft Excel

问题描述

我不太确定是否可以在这里或 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.

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

1. In excel, place you data in sheet1, starting in cell A1
2. Press ALT + F11 to open VBE
3. Now Insert > Module to place a module in the editor
4. Cut and paste code below into module
5. 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图表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！