用MicrosoftExcel绘制CDF图-PlotaCDFchartbyMicrosoftExcel

3  

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

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

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.

例如，假设我有20个数据点，从0.1到2.0，增量为0.1，即0.1,0.2,0.3…2.0。

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

假设数据集的均值是1。0，标准差是0。2。

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

为了得到CDF图，我可以对我的每个值使用以下公式:

=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. In excel, place you data in sheet1, starting in cell A1
2. 在excel中，将数据放在sheet1中，从单元格A1开始
3. Press ALT + F11 to open VBE
4. 按ALT + F11打开VBE
5. Now Insert > Module to place a module in the editor
6. 现在插入>模块，在编辑器中放置一个模块
7. Cut and paste code below into module
8. 将下面的代码剪切粘贴到模块中
9. Place cursor anywhere in RemoveDuplicates and Press F5 to run the code
10. 将光标放在removeduplicate文件中的任何位置，按F5运行代码

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

因此，您的独特的汇总结果将出现在您的工作簿中的Sheet2中。

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

4  

Let's see if I understood your problem. Assuming Excel 2007 and up. Assuming your data is in columns A and B.

让我看看我是否理解你的问题。假设Excel 2007及以上。假设数据在A和B列。

Step 1

步骤1

Use this formula in cell C1:

在单元C1中使用此公式:

=B1*COUNTIF(A:A,A1) 

And this formula in cell D1:

D1细胞的这个公式

=SUM($C$1:C1)  

and copy both formulas down to the end of data.

并将两个公式复制到数据的末尾。

Step 2

步骤2

Select the four columns.
Select in Ribbon Data->Delete Duplicates
Uncheck Columns B,C and D

选择四列。在Ribbon数据中选择->删除重复的列B,C和D。

Step 3

步骤3

Select Columns A and D. Select in Ribbon Insert->Scatter->Line

选择列A和d，在Ribbon Insert->Scatter->Line中选择

Is this what you want to achieve?

这就是你想要达到的目标吗?

HTH!

HTH !

3  

This answer is how to create an 'empirical distribution function', which is what many people really have in mind (myself included) when they say CDF... https://en.wikipedia.org/wiki/Empirical_distribution_function

这个问题的答案是如何建立一个“经验分布函数”，这是很多人(包括我自己)在说CDF时真正想到的(包括我自己)。https://en.wikipedia.org/wiki/Empirical_distribution_function

Assuming the second column of the sample data starts in cell B1, in cell C1, type:

假设样本数据的第二列从B1单元格开始，在单元C1中，输入:

=SUM(IF($B$1:$B$14<=B1,1,0))/COUNT($B$1:$B$14)

then press Shift+Enter, to enter it as an array formula. It will now look like this in the formula bar:

然后按Shift+Enter，以数组公式的形式输入。现在公式栏是这样的:

{=SUM(IF($B$1:$B$14<=B1,1,0))/COUNT($B$1:$B$14)}

Copy the cell down to cover C1:C14. Then make Scatter plot with B1:B14 as X, C1:C14 as Y. It will show four points.

复制单元格，覆盖C1:C14。然后用B1:B14作为X, C1:C14作为y做散点图，显示4点。

• Don't need to sort or remove duplicates
• 不需要排序或删除副本
• Use range names, or take advantage of Excel table capabilities, to manage the input ranges more automatically
• 使用范围名称，或者利用Excel表功能更自动地管理输入范围
• It is a single-cell array formula, so depending on how you copy-and-paste, you will get a message "Cannot change part of an array". If you use Copy-Paste, copy cell C1, then select cells C2:c14 and Paste.
• 它是一个单单元数组公式，因此根据复制粘贴的方式，您将得到一条消息“无法更改数组的一部分”。如果你使用复制粘贴，复制单元C1，然后选择细胞C2:c14和粘贴。
• Ideally, the graph should be presented as a step function, but I didn't have time to figure out any way (good or bad) to do that.
• 理想情况下，图形应该作为一个步骤函数来呈现，但是我没有时间去想办法(好或坏)去做那个。