使用 Microsoft Excel 绘制 CDF 图表

发布于 2024-10-09 21:00:54 字数 459 浏览 0 评论 0原文

我不太确定是否可以在这里或在 SuperUser 上提出这个问题，

我想知道如何绘制 CDF 图表来自我的 Excel 数据。我的数据是这样的（我的真实数据有22424条记录）：

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

原文

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

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

锦爱 2024-10-16 21:00:54

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

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

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

然后按 Shift+Enter，将其作为数组公式输入。现在它在公式栏中看起来像这样：

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

向下复制单元格以覆盖 C1:C14。然后以B1：B14为X，C1：C14为Y绘制散点图。它将显示四个点。

不需要排序或删除重复项
使用范围名称，或利用 Excel 表格功能，更自动地管理输入范围
这是一个单单元格数组公式，因此根据您复制和粘贴的方式，您将收到消息“无法更改数组的一部分”。如果使用复制粘贴，请复制单元格 C1，然后选择单元格 C2:c14 并粘贴。
理想情况下，图表应该以阶跃函数的形式呈现，但我没有时间找出任何方法（好或坏）来做到这一点。

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

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

=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:

{=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.

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
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.
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.

回复收藏 0 原文

め可乐爱微笑 2024-10-16 21:00:54

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

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

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

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

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

alt text

从数据中删除重复条目和总和值相同，您可以使用以下代码。

在 Excel 中，将数据放入工作表 1 中，从单元格 A1 开始
按 ALT + F11 打开 VBE
Now Insert >; Module 将模块放置在编辑器中
将下面的代码剪切并粘贴到模块中
将光标放在 RemoveDuplicates 中的任意位置，然后按 F5 运行代码

因此，您的独特的汇总结果将显示在工作簿的 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

You can use the NORMDIST function and set the final parameter to 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.

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

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

alt text

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

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

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

回复收藏 0 原文