经常看到有人出一些Excel的题,要求用公式解,然后注明一句:用函数,不允许增加辅助列!比如这种:
——怎么样?说假话,“还不算太难……哈哈”。
不过,说实话,我对“用函数,不允许增加辅助列”这句话特别,特别,特别的反感——因为,有很多问题,本来要求用函数解就很麻烦,然后还不允许增加辅助列——以我的智商,很多时候真是写不出来嘛!
那怎么办?——该加辅助列加辅助列啊,该用Power Query用Power Query啊……本来就很简单的事,为什么要搞那么复杂?比如,这个问题用Power Query来做,分组、改个函数:
轻松,愉快,不用脑……
但是,大家其实知道我的重点是讲Power系列,所以,前面这个用Power Query轻松解决Excel中的基本问题的例子,并不是今天的重点——我今天真正想说的是,“用函数,不允许增加辅助列”这种问题,在Power Query本身的使用中,也需要注意。
下面这个例子是前几天在群里一位PQ爱好者给出的,即要根据左表中同一“姓名”同一“工作单位”的“工作时间”逐月连续的数据进行分组,并得到“开始时间”和“结束时间”,如下图所示:
当然,作为一位Power Query爱好者,纯粹是为了研究、练手,并且直接给出了自己的“一个公式搞定”的解决方案,并自觉得比较复杂——这种纯粹的分享精神,非常“抵赞”!
公式如下:
看不清楚?没关系,复制下来是这样子的(没换行缩进,看起来真的挺累的,用手指头一直往右边滑呀滑呀就能看完了,有兴趣的朋友可以复制下来做个换行然后研究一下,嘿嘿):
= Table.Group(Table.FillDown(Table.FromColumns(Table.ToColumns(a)& {List.Transform({0..Table.RowCount(a)-1},each if _=0 then 0 else if a[工作单位]{_}=a[工作单位]{_-1} and a[工作时间]{_}=Date.AddMonths(a[工作时间]{_-1},1) then null else _)},Table.ColumnNames(a)&{"分组"}),{"分组"}),{"姓名","分组","工作单位"},{{"开始时间",each List.Min([工作时间])},{"结束时间",each List.Max([工作时间])}})
我简单数了一下,这个公式大概用了10个不同的函数,再加上条件判断语句、以及对PQ数据引用的熟练运用——对于大多数的普通Excel用户来说,写出这个长公式,绝对不是一件容易的事——这也不是我推荐大家使用Power Query的初衷。
那么,对于普通用户,这个问题怎么解决呢?其实只要加个辅助列,然后要写的公式就比较简单了,具体过程如下:
Step 01添加索引列
Step 02借索引列写公式,确定到需要分组内容的第一行
if [索引]=0
then [索引]
else if a{[索引]-1}[姓名]=[姓名]
and a{[索引]-1}[工作单位]=[工作单位]
and a{[索引]-1}[工作时间]=Date.AddMonths([工作时间],-1)
then null
else [索引]
看起来很长?不过理解起来和写起来容易多了,如果感觉还有难度,那回头看一下关于PQ表结构的文章《重要!很重要!非常重要!理解PQ里的数据结构(三、跨行引用)》。
Step 03填充
Step 04分组
结果就这样出来了:
到了这里,终于到谈谈感想的时候了。
除非你是因为爱好,为了练技能……在实际工作中,绝大多数的时候,无论你是在Excel里还是Power Query里,你并不需要去写一些复杂到可以“引以为傲”的公式,而是通过换一个方式,加个辅助列,哪怕甚至加个辅助表,那些看起来很麻烦的事情,实际就变得简单了很多。
同时,非常感谢各位爱好者、大神来为大家提供多种多样的问题解决思路和方法,只要不是这样的,我们无任欢迎!
本文分享自 Excel到PowerBI 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!