这才是NO.1的数据查找神器，1天的工作半小时搞定！（建议收藏）
点击蓝字【秋叶 Excel】👆
发送【交流】
立即进秋叶 Excel 读者快乐交流群！
本文作者：小爽
本文编辑：竺兰
Excel 公开课又双叒叕来啦~
今晚 19:30 正式开播
点击下方卡片立即预约
👇👇👇
大家好，我是在研究数据转换的小爽～
小 A 作为一个仓库人员，经常需要对新来的货品进行归类，并存放到指定的货柜。
在每个货柜里，会存放不同的商品。而每个商品都有自己对应的商品编码。
货柜中存放的商品编码可能是连续的也可能是不连续的，如下图：
现在来了一批新货，他需要根据商品编码找到对应的货柜，然后将货品进行入库。
比如，他要查找商品编码 105 的货品需要存放在哪个货柜，应该怎么做?
肉眼目测的话，可以直接判断 105 在货柜 A。
但是来货的商品不止一种，我们不可能一一用肉眼识别。
如果用 Excel 函数做的话，似乎也很难做到。
不过，我们还有 PowerQuery(PQ)——先将数据整理为一维表，再来进行查询匹配。
而且只需要两个简单的 M 函数就能搞定这个问题。
学会这个做法，我们只要每次更新需要匹配的数据源，直接刷新就能出现我们想要的结果，省时省力。
注意：
❶ PowerQuery 在 Office2016 以及以上版本自带插件，以下版本需要自行安装，在我们的公众号后台回复： 插件 ，即可获取安装包。
❷ 本文用 Office365 进行演示，不同版本进入 PQ 界面的选项卡可能不同，但是原理相同。
接下来，我们来看看具体操作。
转一维表
❶ 将数据导入到 PQ 编辑器中
我们先将表格设置为超级表，分别选中数据表，按住快捷键【Ctrl+T】，表名称分别设置为「参数表」、「查询编码」。
👉 参数表：
👉 查询编码：
然后选中「查询编码」数据区域，在【数据】选项卡，单击【来自表格/区域】，进入 PQ 编辑器中。
❷ 自定义列，输入 M 函数
在【添加列】选项卡下，选择【自定义列】，输入新列名：编码
输入自定义列公式。点击【确定】。
M 函数公式如下：
=Expression.Evaluate("{"&Text.Replace([商品编码],"-","..")&"}")▲ 左右滑动查看
此时，商品编码的 List 就形成了。
该公式的主要思路就是，将「-」替换成「..」，再连接两个大括号，使范围构成一个 List 列，最后使用函数将其展开，就得到了相应的结果。
简单解释一下涉及到的 M 函数。
Text.Replace([商品编码],"-","..")=Text.Replace(text,需要替换的字符串，替换为的字符串)
在 M 函数表达式中，列表的表示方式是用{中括号}，如下图，{1,2}，就是 1，2 形成的列表。
如果要表示 1 到 9 的列表，就是{1,2,3,4,5,6,7,8,9}，可简写为{1..9}：
所以案例中我们需要扩展横杆前后的数值，只需拼接 "{" 开始数值 .. 结束数值 "}"
"{"&Text.Replace([商品编码],"-","..")&"}"拼接完成后，这个还只是文本的形式，我们还要把它「翻译」成表达式，就需要使用到 Expression.Evaluate 函数了。
Expression.Evaluate 函数，它相当于 Excel 中的 Evaluate 宏表函数。
但是不同于 Excel 的宏表函数，Expression.Evaluate 函数的功能会更加强大。
它有 Evaluate 函数的作用，返回计算式的计算结果，如下图：
= Expression.Evaluate("1+2*3-4")它还可以将表达式的文本，返回表达式的结果。
所以案例中我们利用它来将"{"&Text.Replace([商品编码],"-","..")&"}" 转换为表达式，达到扩展连续数值的目的。
Expression.Evaluate("{"&Text.Replace([商品编码],"-","..")&"}")▲ 左右滑动查看
❸ 扩展数据表
删除商品编码列。
点击编码旁边的小按钮，选择扩展到新行。
此时，我们的一维表就完成了，接下来我们就要查询匹配对应的值了。
查找匹配
上面，我们已经将数据整理为一维表结构了，后面就是要匹配查询编号对应的货柜号。
具体步骤：
❶ 将查询编码表导入到 PQ 编辑器中，由于我们之前已经将数据表变成超级表，所以只需要复制一个参数表并粘贴就可以。
鼠标右键参数表，单击下面的【复制】按钮。
PS：第一个【复制】只包含复制，第二个【复制】，包含复制查询表并粘贴两个步骤，所以我们选择第二个【复制】选项。
❷ 去除其他多余步骤，将参数表改成查询编码，同时步骤改成查询编码。
动图操作：
❸ 在【主页】选项卡，单击【合并查询】-「合并查询」。
PS：合并查询，是指将两个查询表按照某一列或某几列共有的关键字段合并成一个表。
这里我们可以用查询编码表中的【查询编号】列与参数表中的【编码】列匹配，然后选取匹配到的【参数表】中的【货柜号】列。
❹ 如下图，合并窗口中，
查询编码，选查询编号列 参数表，选择编码列 联接种类为，左外部
写在最后
A. Text.Replace 是一个替换函数，它能将文本中旧字符串替换为新字符串，有点类似 Excel 函数中的 replace 函数。 B. Expression.Evaluate 跟 Excel 宏表函数 Evaluate 功能完全一样。 C. 合并查询，是指将两个以及以上的查询表按照某一列共有的字段合并成一个表。 D. 使用 PQ，刷新后，可以实时更新数据。 E. 学 Excel，找秋叶 Excel，顺便找小爽鸭😁
【今晚 19:30】
Excel 主题公开课《《Excel图表呈现，老板喜欢的表格长这样》》正式开播！
据说直播间还有免费鼠标垫等你拿~【课程无回放】建议提前扫码预约！