你加班1小时做Excel表格，我用这个插件只需1分钟！(建议收藏)

大家好，我是学 Excel 的小爽鸭~

产品从工厂生产出来之后，需要进行装箱操作。

不同的产品会有各自对应的箱规(箱子规格)，每一个箱子需装有规定数量的产品。

但是在实际生产过程中，不可能所有的产品刚好装满指定的箱子，大概率会有剩余。

所以，有时需要将数量按照箱规进行一个个拆分，如下图所示。

那么如何将左表拆分成右表的形式呢？

用函数？用 VBA？用 PQ！！！

PowerQuery(简称 PQ)是 Excel 自带的利器，利用它，我们可以获取数据，清洗数据，还有整理数据。

现在，我们尝试用 PQ 去解决这个问题。

由于我们需要将数量按照指定规格数拆成一条条数据，最后不足规格数量的为一条。

所以只需要根据取整数，重复对应的箱规数。

最后与剩余数进行拼接即可。

思路已经有了，接下来就开始操作啦 ~

整除数与取余数

具体操作 :

我们先将数据导入到 PQ 编辑器中。

选中数据表，在【数据】选项卡下，单击【来自表格/区域】;

单击【确定】按钮，进入 pq 编辑器中。

下面，为了方便大家理解，我采取新增列的方式，一步步带大家完成这个问题。

首先我们来获取数量与规格之间相除后的余数，使用的是 Number.Mod。

• =Number.Mod(被除数 , 除数)
  

在【添加列】选项卡，单击【自定义列】；

新列名：取模。

自定义列公式为：

• =Number.Mod([数量],[箱规])
  

然后获取数量与规格之间相除后的整数，使用的是 Number.IntergerDivide。

• =Number.IntergerDivide(被除数，除数

在【添加列】选项卡，单击【自定义列】；

新列名：取整重复。

自定义列公式为：

• =Number.IntegerDivide([数量],[箱规])

整除数和余数都有了，接下来就是重复取整数后拼接啦。

重复拼接扩展

我们要将数量按照指定规格数拆成一条条数据，也就是要重复箱规数的数据。

对于重复列表，我们使用的是 List.Repeat 函数。

• =List.Repeat({值}，重复次数)

下面，我们在取整后的公式编辑器，加上重复函数。

如下图所示。

• = Table.AddColumn(已添加自定义,

•   "取整重复",

•   each  List.Repeat(

•    {[箱规]},

•    Number.IntegerDivide([数量],[箱规])))

  
  

由于余数为 0 部分，不需要进行拼接，所以我们可以用 if 语句进行判断。

在【添加列】选项卡，单击【自定义列】；

新列名：拼接。

自定义列公式为：

• if [取模]=0

• then [取整重复]

• else [取整重复]&{[取模]}
  

这时可以看到，我们已经将数据拼接好了。

现在，只需要把其他不需要的列删除掉就可以。

删除其他列，保留【产品名称】和【拼接】列。

选择【产品名称】列，按住【Ctrl 键】，再选择【拼接】列，鼠标右键选择【删除其他列】。

选择扩展按钮，选择 「扩展到行」。

到这里，我们就完成了。

是不是很简单~

延伸一下

如果大家掌握到一定程度，也可以不选择通过自定义列的方式一步步完成效果。直接写一个 m 函数公式也可以。

• = Table.ExpandListColumn(

•  Table.AddColumn(

•   源,"a",

•   each

•   let

•     m= Number.Mod([数量],[箱规]),

•     n=List.Repeat({[箱规]},Number.IntegerDivide([数量],[箱规]))

•   in

•     if m=0 then n else n&{m})

•     [[产品名称],[a]],

•   "a")

  
  

当然方法不是只有这种，比如我们可以用递归，List.Accumulate 等等方法。掌握最基础的方法就行啦。

总结一下


本文讲解的是，将数量根据指定的规格进行扩展的 PQ 做法。

涉及三个基础 m 函数：

❶ Number.Mod(取模函数)，类似于 Excel 的 mod 函数。

❷ Number.IntergerDivide(取整函数)，类似于 Excel 的 int 函数。

❸ List.Repeat 列表重复函数。

❹ if ……then……else 语句，跟 if 函数一样是判断作用，不过语句和函数概念不同。

今天讲解就到这里就结束啦～

文中提到的三个 m 函数大家掌握了嘛，是不是觉得 PQ 中的 m 函数其实也不是这么难？

到目前为止，读到这里的你，脑子过一下，认识了哪些 m 函数了呢，留言区一起聊聊吧~

如果你在工作中遇到问题想有老师指点，想学习更多 Excel 技巧，想拥有更多练手机会……

欢迎报名我们秋叶家的《3 天 Excel 集训营》，这里有老师直播+录播教学，有助教群内 1 对 1 答疑，还有同学一起交流进步！

3 天时间，每天 30 分钟左右，你也有可能成为 Excel 高手！

3 天 Excel 集训营

提升效率，助你准时下班

数据可视化，让领导刮目相看

秋叶 Excel 读者专享

官网价 99 

现在仅需 1 元

点下方图片扫码即可报名

👇👇👇

↑↑↑

现在报名免费获取

307 个函数清单

35 个常用函数说明

……

*广告