二维码 购物车
部落窝在线教育欢迎您!

Power Query实战:按指定次数递增数据

 

作者:阿硕来源:部落窝教育发布时间:2021-09-22 10:28:42点击:182

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

哈喽各位小伙伴,Power Query(后文简称PQ)作为数据清洗整理的利器,能够轻松解决很多复杂函数才能搞定的问题。在日常的工作中,我们会遇到这样一种问题:要求以某一条数据为起始点,按照指定次数进行递增。最常规的运用就是做公司规划、值班排班表等等。今天我们就来学习一下如何用PQ解决这个问题。

 

哈喽,大家好,欢迎来到部落窝教育!我是阿硕。前段时间,我们推送过一个教程,讲的是利用PQ按指定次数重复数据:《Excel教程:比vlookup简单!用这个技巧做重复数据,只用1分钟!》很多小伙伴看完这个教程之后,感觉很实用。同时呢,也收到一些小伙伴的留言,咨询一个十分类似的问题——想要按照指定次数生成一组递增的数据,有什么好的办法吗?

 

今天,让我们共同来学习一下该如何解决这个问题。

 

数据如下图所示,A列中的数据是起始数,B列中的数据是递增数。要达成什么样的目的呢?以第2行的数据为例,起始数为1,递增数为4,则需要生成1234这四个数据。再以第3行的数据为例,起始数为6,递增数为3,则需要生成678这三个数据。其余以此类推。

 

 

下面,让我们开启今天的学习之旅。

 

Step 1  进入PQ编辑器


首先,我们点击数据区域中的任意一个单元格,如B2。然后依次点击【数据】-【自表格/区域】,弹出“创建表”对话框。“表的数据来源”已经由Excel自动为我们判断出来($A$1:$B$5区域),保持其不变即可。接着勾选“表包含标题”(若已勾选则保持其被勾选即可),如下图所示。

 

 

点击“确定”之后,就可以进入PQ编辑器,如下图所示。

 

 

Step 2  添加自定义列


接下来,我们要添加一个自定义列,这个自定义列就是辅助我们生成递增数据的列。依次点击“添加列”-“自定义列”,弹出“自定义列”对话框,如下图所示。

 

 

在“新列名”下方的对话框中,PQ为我们自动设置了新增加的数据列的列名,为“自定义”,我们保持其不变即可(注:在此处,对列名进行更改也是可以的;本例中采用的是在最后微调数据的时候更改,见后文)。

 

在“自定义列公式”下方的等于号(“=”)后面,我们输入一个M函数公式: ={[起始数]..[起始数]+[递增数]-1}。如下图所示。

 

 

M函数公式解析:

起始数和“递增数”是原始数据中的标识字段,进入PQ后,它们各自代表PQ中的一个变量,其所对应的值就是相应的起始数或递增数。

..”的意思就是从起始数至递增数的“至”,M函数公式的最外层是一对大括号,其意思是生成一个List(具体含义后文我们有讲到)。

[起始数]+[递增数]-1}”,它的意思就是从起始数开始,按照递增的数量,所递增到的最大值。

此处,大家要注意理解一下为什么要减去1。这是因为数据是从自身开始递增的,如果不减去1的话,递增的次数就会多出一次。

综上,上面所写的M函数公式,它的意思就是在PQ中增加一个自定义列,这个自定义列里的数据为从数据自身到所递增的最大值之间的所有数据(整数)。

 

点击“确定”后,得到的结果如下图所示。

 

 

可以看到,在原有的“起始数”、“递增数”这两列数据后面,又增加了新的一列,该列名为“自定义”,该列中每一个数据的值均为“List”。

 

TipsList”是PQ中的一种数据类型,我们把它理解成一个数组就行啦!说简单点,一个“List”中一般含有多个数据,它是一个数据的集合。如果想要查看“List”中的数据,我们可以点击任意一个“List”进行查看。例如,我们点击第1行中的“List”,则可以在数据下方的预览框中看到,其中的数据为1234(每个数字各占一行),如下图所示。

 

 

Step 3  对数据进行“展开”操作


接下来,我们点击“自定义”列名后面的“展开”按钮,然后点击“扩展到新行”,如下图所示。

 

图形用户界面, 文本, 应用程序, 表格

描述已自动生成

 

点击“扩展到新行”之后,得到的结果如下图所示。

 

 

可以看到,此时数据中没有新增的列,但却有新增的行。数据已经由原来的4行,变成了14行。同时,请大家注意看一下“自定义”这一列!其中的数据,已经由“List”变成了具体的数值。而这些具体的数值,就是我们所需要的从起始数按指定次数递增之后的数据。

 

接下来,我们可以根据需要,再微调一下数据。

(1)  删除“起始数”和“递增数”这两列。具体操作为:分别右键单击“起始数”或“递增数”两列数据的列名,在弹出的菜单中,选择“删除”,即可。最终,我们在数据中只保留“自定义”这一列。

(2)  对“自定义”列重命名。具体操作为:右键单击“自定义”这一列的列名,选择“重命名”,然后将这一列重命名为“递增后的数据”。

这几个微调的操作比较简单,就不给小伙伴们截图演示了,大家自己来操作一下就行了。经过微调之后,得到的数据如下图所示。

 

 

Step 4  上载数据


最后,我们依次点击“上载”-“关闭并上载”-“关闭并上载”,将数据上载至Excel数据表中即可。所得到的结果如下图所示。

 

 

 

小伙伴们,今天学的内容,你掌握了吗?

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

7个Excel小技巧,提高表格查看效率

Excel运用规范1:一个单元格只记录一条信息

快速整理不规范的Excel表格的7个公式

9条最实用的计算excel中关于日期的公式!(建议收藏)

版权申明:

本文作者阿硕;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。