根据标准对投资组合进行排序

Question

我当前的表看起来像这样

公司-----年份----规模-----交易所

A-----------2000-----80-------A

A-----------2001-----85-------A

B-----------2002------90------C

我想将公司分为两类"大"和"小"。

对于特定年份，如果公司规模大于交易所A中当年公司规模的中位数，将被称为"BIG"。像这样的东西，

=if（[size]>MEDIANX（filter（filter（tbl1，[Year]=A），[Year]），[size]），"Big"，"Small"）

我知道我使用过滤器的方式是错误的。我不知道该怎么做。请帮助我。

Answer 1

你的问题措辞不是特别好，因为它让我想知道我在回答时是否做出了正确的假设，但本质上我认为你需要一个带有 Median 的数组公式之类的东西。

=IF(C2>MEDIAN(IF($D$2:$D$5="A",IF($B$2:$B$5=B2,$C$2:$C$5))),"Big","Small")

如果当年公司规模大于交易所 A 中当年公司规模的中位数，这应该给你"大"。

Answer 2

我假设您要求PowerPivot解决方案，因为您的问题被标记为PowerPivot。

DAX（又名 Power Pivot）公式MEDIAN()仅在 Excel 2016 的预览版中可用（请参阅此处：https://msdn.microsoft.com/en-us/library/dn802551.aspx）。

但是，您可以使用RANKX()简单地构建自己的中值逻辑

首先，添加一个名为 [RankInExchangeA] 的新列：

=If([Exchange]="A",RANKX(FILTER(Table1,[Exchange]="A" && EARLIER([year])=[year]),[size]),Blank())

EARLIER()函数基本上意味着 ThisRowsValue() .

现在添加所需的大/小列：

=If([Exchange]="A",If([RankInExchangeA]<=CALCULATE(max([RankInExchangeA]),filter(Table1,EARLIER([year])=[year]))/2,"Small","Big"),"Other Exchange")

编辑：在公式中添加了年份条件。

编辑2：如评论中所述，以下公式可以使用MEDIANX()：

=IF([size]>MEDIANX(FILTER(Table,[Exchange]="A"&&EARLIER([date])=[date]),[size]),‌​"Big","Small")