“逼死”强迫症的不规则表格，原来点点鼠标就能整理好！（必学）

点击蓝字【秋叶 Excel】👆

发送【交流】

立即进秋叶 Excel 读者快乐交流群！

本文作者：明镜在心

本文审核：小爽

本文编辑：雅梨子

民间流传着这样一句话：不怕神一样的对手，就怕「ZHU」一样的队友。

这不，说曹操，曹操到！

有天下班，同事发来一张表格，请我帮忙统计一下每种产品的总数量。

如下图，是一张门店库存报表：

（为方便演示，图中仅展示部分数据~）

我一看到这个表格就很吃惊，他居然把多个规格的数量写在一个单元格中。

因为表格结构的问题，如果坚持要用函数，就会出现下面的公式：

公式大概的意思是：

先用 Substitute 替换函数将冒号替换为 99 个空格，然后使用 Mid 截取函数将其分别截取出来，形成一个内存数组；

再用 Find 查找函数查找冒号的位置。再用 Mid 函数截取其中的数字，最后再用 Sum 求和函数将其加总在一起。

听上去就像天书一样？

那就对了！

这个看似比较简单的数量统计问题，如果使用函数来解决很复杂！！

那有没有比较简单易行的方法呢？

当然有啦！用 Power Query（以下简称 PQ）来处理这个问题就行了。

PQ 操作步骤

❶ 将数据添加到 PQ 编辑器中。

点击数据区域中的任一单元格，然后依次点击【数据】选项卡中的【来自表格/区域】，在【创建表】对话框中点击【确定】。

❷ 拆分列。

先选中【数量】列，然后点击【添加列】中的【重复列】。

如下图就会新增一列。

选中【数量-复制】列，然后点击【主页】选项卡中的【拆分列】-【按分隔符】。

在【按分隔符拆分列】对话框中，选择【自定义】分隔符，勾选【使用特殊字符进行拆分】，并在【插入特殊字符】下拉菜单中选择【换行】符。

最后点击【确定】，结果如下：

此时，我们就将原来在一个单元格中的多行数据拆分成一行多列显示的数据。

❸ 提取数字。

选中【数量 - 复制.1】，并按住【Shift】键，再点击【数量 - 复制.5】，选中这五列。

然后依次点击【转换】选项卡中的【提取】【分隔符之后的文本】。

在【分隔符之后的文本】对话框中，输入冒号（:），最后点【确定】

注意：这里使用的是英文半角冒号。

如下图所示：

这样就完成了提取数字的操作。

但是上面提取出来的还是文本型的数字，需要将其转换成真正的数字。

依次点击【主页】选项卡，【数据类型】中选择【整数】。

这样一来数字的对齐方式跟工作表中的对齐是一样的，即靠右对齐，此时就是真正的数字了。

❹ 计算出总数量。

依然选中这五列，然后在【添加列】选项卡中，点击【统计信息】【求和】。

如下图，自动添加一列【加法】列，将前面的数量自动相加在一起了，最终完成求和。

此时将前面的五列数量都删除掉。

选中五列之后，点击【主页】选项卡，【删除列】。

如下图：

双击【加法】列名称，将其更改为【总数量】。

❺ 将结果上载到工作表中。

点击【主页】选项卡中的【关闭并上载】。

这里会单独生成一张工作表，用于存放在 PQ 中处理好的数据。

选中【D】列，在点击【开始】选项卡中的【自动换行】。

最终结果如下：

怎么样？上述操作看似很复杂，其实就是动动鼠标的事！

你学会了吗？

另外，除了 PQ 法，其实还有两种比较巧妙的方法，也可以解决上述问题~

我们一起来看看

知识扩展

▋快速填充法

❶ 在【E2】单元格输入任一字母（z），然后输入等于号（=）并将其中的数字全部相加起来，即：

z=30+40+80+20

❷ 按【Ctrl+E】，此时会自动将单元格中的数字全部相加起来。

如下图：

❸ 最后，我们只需要查找替换字母「z」，就能完成计算了。

最终结果如下：

▋分列法

除了用【快速填充】这个方法之外，我再给大家介绍一种【分列法】。

❶ 选中【D】列，然后依次点击【数据】选项卡，【分列】，调出【文本分列向导】对话框。

❷ 点击其中的【下一步】，在【文件分列向导】第 2 步中，勾选【其他】，并在旁边文本框中输入换行符（【Ctrl+J】）。

此时在【数据预览】窗口中已经将各个数据分列开来了。最后点击【完成】。

❸ 将规格和冒号都替换掉。

按【Ctrl+F】调出【查找和替换】对话框，并在【查找内容】中输入「*：」，【替换为】文本框中不输入内容。

点击【全部替换】，效果如下：

❹ 在【I2】单元格输入如下公式：

=SUM(D2:H2)

完成数量计算。

总结

今天分享的案例，解决方法有四种：

▋函数法

优点：可以随数据的变化实时更新。

缺点：此方法要求有较高的函数水平才能完成。对于数据量比较多的情况下，会导致表格卡顿。

▋PQ 法

优点：操作简单易行，结果能够实时更新。数据量大的情况下，也不会导致表格卡顿。

缺点：需要在 2016 或以上版本中使用。（2010 和 2013 需要安装插件。）

▋快速填充法

优点：此方法最为简单。

缺点：提取出来的结果并不能完全保证准确。只能 2013 版本以上使用。

▋分列法

优点：此方法相对简单，可以在各种不同版本中使用。

缺点：不能随数据变化而更新。

好了今天的分享就到这里。

大家还有哪些方法，欢迎在留言区与我们一起分享噢！

苦等多日，双十二活动终于来啦！

今天小 E 给大家推荐这个四合一套装，《PPT+Excel+Word+PS 新手到高手》 ，不仅帮助你提高 Office 技巧，还能教你学习更多 PS 干货知识，实在不容错过！

原价 806 元，现在到手仅需 269 元，立省 537 元！

购买套装还赠送价值 149.7 元的秒懂全彩版新套装哦！

还等什么，赶快扫描图片二维码，和秋叶一起学习吧！（现在点击阅读原文，还能参与更多活动哦~）

欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

↓↓↓

解你所惑

点「在看」~

👇👇👇

点这里

双十二特惠活动等你来~

👇👇👇