本文介绍了根据标准对投资组合进行排序的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我目前的表格如下



公司-----年----大小-----交换



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



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



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



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



对于特定的一年,如果公司规模大于该交易所A当中公司规模的中位数,将被称为大。
这样的东西,

= if([size]> MEDIANX(filter(filter(filter(tbl1,[Year] = A),[Year] [size]),Big,Small)



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

解决方案

我假设您要求一个PowerPivot解决方案,因为您的问题被标记为这样。



DAX(aka Power Pivot)公式 MEDIAN()仅在Excel 2016的预览版中可用(参见此处:)



但是,您可以使用 RANKX()

$ b $创建自己的中间逻辑b

首先,添加一个新列,名为 [RankInExchangeA]

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

EARLIER()功能基本上意味着 ThisRowsValue()



现在添加您想要的大/小列:

  = If([Exchange] =A,If([RankInExchangeA]< = CALCULATE(max([RankInExch angeA]),过滤器(表1,EARLIER([年])= [年]))/ 2,小,大),其他交换)
pre>

编辑:将年份条件添加到公式。



编辑2:如注释所述,以下公式将使用 MEDIANX()

  = IF([大小] GT; MEDIANX(FILTER(表,[交换] = A &安培;&安培; EARLIER([日期])= [日期]),[大小]),大,小)


My current table looks like this

Company-----year----size-----Exchange

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

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

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

I want to allocate the companies into two categories "Big" and "Small".

For a particular year, if the companies size is bigger than the median of the size of the companies in that year in Exchange A, will be called "BIG".something like this,

=if([size]>MEDIANX(filter(filter(tbl1,[Year]=A),[Year]),[size]),"Big","Small")

I know the way I used the filters are wrong. I do not know how to do this. Please help me.

解决方案

I assume you ask for a PowerPivot solution, since your question is tagged as such.

The DAX (a.k.a. Power Pivot) formula MEDIAN() is only available in the preview edition of Excel 2016 (see here: https://msdn.microsoft.com/en-us/library/dn802551.aspx).

However, you can simply build your own median logic using RANKX()

First, add a new column called [RankInExchangeA]:

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

The EARLIER() function basically means ThisRowsValue().

Now add your desired Big/Small column:

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

EDIT: Added the year condition to the formulas.

EDIT 2: As mentioned in the comments, the following formula would work using MEDIANX():

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

这篇关于根据标准对投资组合进行排序的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-21 12:04