Excel 水果分箱难题，一朝得解–满重即分箱，不到半箱不发

老读者应该知道，我经常鼓励大家学习并使用 Power Query。

哪怕是同一个案例，我也常常会给出包括 Power Query 在内的多个解决方案，相较其他方案而言，PQ 看似操作繁琐，其实是最简单的解法。

比如下面这个水果订单数和实际发货数的计算，如果用公式，会非常麻烦，但是用 Power Query 的话，几乎不用动什么脑筋就能完成。

案例：

下图 1 是水果的订单表和实际发货规则，规则如下：

水果 5.5元/公斤；

每一个箱子最多能装 10 公斤，所以超过 10 公斤需要分箱包装；

如果每箱不满 10 公斤，则至少要 5 公斤才能发；不满 5 公斤的零头，不予以发货。

请根据以上规则，分别统计：

每个人的订单各需要装几箱？

按发货规则，没人最终实际发货多少公斤？

根据实际发货重量，计算总价

效果如下图 2 所示。

解决方案：

1. 选中数据表的任意单元格 --> 选择菜单栏的“数据”-->“从表格”

2. 在弹出的对话框中点击“确定”

数据表就上传至了 Power Query。

3. 选择菜单栏的“添加列”-->“自定义列”

4. 在弹出的对话框中进行以下设置 --> 点击“确定”：

新列名：输入“每箱重量（公斤）”

在公式区域输入以下公式：

= List.Transform(List.Split(,10),List.Count)

5. 点击“每箱重量（公斤）”列标题旁边的扩展按钮 --> 选择“扩展到新行”

6. 点击“每箱重量（公斤）”列标题旁边的筛选箭头 --> 在弹出的菜单中选择“数字筛选器”-->“大于或等于...”

现在的结果，不仅完成了分箱，还扣除了不满 5 公斤的零头。

于是就复制出一个完全一样的查询。

9. 选中 Table1 (2) --> 选择菜单栏的“主页”-->“分组依据”

第一个下拉菜单：选择“姓名”

新列名：输入“箱数”

操作：选择“对行进行计数”

点击“添加聚合”按钮，会出现一行新的设置框：

新列名：输入“实际发货（公斤）”

操作：选择“求和”

柱：选择“每箱重量（公斤）”

11. 选中查询区域的 Table1 --> 选择菜单栏的“主页”-->“合并查询”

选中 Table1 的“姓名”列

在下拉框中选择 Table1 (2)

选中“姓名”列

联接种类：选择“左外部(第一个中的所有行，第二个种的匹配行)”

13. 点击 Table1(2) 旁边的扩展按钮 --> 在弹出的对话框中进行以下设置 --> 点击“确定”：

勾选“箱数”和“实际发货（公斤）”

取消勾选“使用原始列名作为前缀”

14. 继续选中 Table1 --> 选择菜单栏的“添加列”-->“自定义列”

新列名：输入“总价”

将右侧的“实际发货（公斤）”列插入到左侧的公式区域 --> 在后面输入“*5.5”

16. 选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至...”

18. 在右侧的区域选中 Table1 --> 右键单击 --> 在弹出的对话框中选择“加载到...”

19. 在弹出的对话框中选择“表格”-->“现有工作表”--> 选择所需上载的位置 --> 点击“确定”

绿色区域就是最终的统计结果，蓝色区域的数据今后如有更新，只要刷新绿色区域，就能实时同步结果。是不是比使用公式简单得多？