时间戳

首页 > 解决方案 > excel结合if语句和stdev

excel - excel结合if语句和stdev

问题描述

问题：给定交易列表，我必须为交易类型以及所有交易生成箱线图。

背景：我正在使用 Excel 2016。

我有一张表格，里面填满了被审核方提供的数据。

我必须创建一个 Excel 工作表，它允许我生成生成箱线图所需的值，类似于 Peltier 使用的方法（https://peltiertech.com/excel-box-and-whisker-diagrams-box-plots/）不修改受审核方提供的数据（不允许排序，不允许修改受审核方提供的数据，以防审核在行政程序中使用）

虚假交易文件的图像

该文件显示：

我已将以下命名字段命名为简单的公式输入，并使其更容易审核我的公式：

我需要为所有交易以及交易类型类别（上市、销售和双重代理）。

获得所有交易所需的值相对容易。我的问题是当我必须按交易类型分解事情时。

我目前正在使用以下公式来命名定义我的动态字段的范围：

ClosingDate = Offset($A$2,0,0,Counta($A:$A),1)
SalesPrice = Offset($C$2,0,0,Counta($C:$C),1)
TransactionType = Offset($D$2,0,0,Counta($D:$D),1)
ListingAgent = Offset($E$2,0,0,Counta($E:$E,1)
SellingAgent = Offset($F$2,0,0,Counta($F:$F,1)

以下是我尝试结合这些动态字段、if 函数和适当的函数来为显示列表交易的箱线图生成我的数据：

COUNT:   =Countif(TransactionType,"Listing")
SUM:     =Sumif(TransactionType,"Listing",SalesPrice)
MEAN:    =Averageif(TransactionType,"Listing",SalesPrice)
STDEV:   =Stdev(If(TransactionType="Listing",SalesPrice))
MIN:     =Min(If(TransactionType="Listing",SalesPrice))
1ST Q:   =Quartile(If(TransactionType="Listing",SalesPrice),1)
MEADIAN: =Median(If(TransactionType="Listing",SalesPrice))
3RD Q:   =Quartile(If(TransactionType="Listing",SalesPrice),3)
MAX:     =Max(If(TransactionType="Listing",SalesPrice))

但是，对于除计数、总和和平均值之外的所有内容，我都会遇到错误，并且 excel 的输出与我手动生成的答案不匹配。

这是我在我的 StatAnalysis 工作表上的输出的副本，其中一个包含公式

统计 输出 带公式的统计输出

由于我使用的数据是机密的，因此我无法共享我使用的源文件。然而，为了帮助您可视化我所做的事情，我生成了一个模拟我正在使用的事务的虚假事务文件（一切都完全相同，只是名称、日期和地址是为了保护身份而进行的更改）。

标签： excelexcel-formula

解决方案

您在两个命名范围公式中存在错误，其中倒数第二个括号位于错误的位置：

ListingAgent = Offset($E$2,0,0,Counta($E:$E),1)
SellingAgent = Offset($F$2,0,0,Counta($F:$F),1)

此外，请务必将这些条件 If 公式作为数组公式输入Ctrl+ Shift+Enter

例如

=STDEV(IF(TransactionType="Listing",SalesPrice))

{}当作为数组公式输入时，应该出现在它周围。

对于中位数和四分位数，您还可以使用可以处理数组的 AGGREGATE 函数，并且您可以指定其他参数，例如 6 来忽略错误值。这不需要使用Ctrl+ Shift+输入Enter

例如，使用四分位数函数参数的下四分位数并忽略错误值参数：

=AGGREGATE(17,6,SalesPrice/(TransactionType="Listing"),1)

例如中位数：

=AGGREGATE(16,6,SalesPrice/(TransactionType="Listing"),0.5)

感谢@BarryHoudini展示了如何做最后的那些。

推荐阅读