点击蓝字【秋叶 Excel】
发送【交流】
立即进【秋叶同学会】交流 Excel！
本文作者：小爽
本文编辑：竺兰
每到开学前，有些学校的教务员需要对各科老师下一个学期上课的对应班级进行安排。于是，制作了如下图的表格。
为了方便查看每个班级，每个科目，对应的任课老师，他还需要将下表左图的数据，匹配到对应右表中。
这应该怎么做呢？
直接用我们常见的匹配函数，Vlookup 函数？
由于数据源表并不是一个规范的一维表。Vlookup 函数很难做到。
那，来试试 Lookup 函数?
函数法
我们先来观察一下数据源。
关于 Lookup 函数查询匹配，需要找到相同区域大小（单行/单列）的条件区域，以及返回区域。
如下图，我们可以看到条件区域 2 并不是单列的数据区域。
那如果我们把条件区域 2 整合为一列，是不是就可以用查找函数 Lookup 了呢？
如下图，将条件区域 2，利用 Textjoin 函数合并为一列。
对于需要匹配数据结果，有条件区域，返回区域，可以用到 Lookup 函数经典用法。
Lookup 函数多条件查询的套路公式（具体解释见文末）：
=LOOKUP(1,0/((条件 1=条件区域 1)*(条件 2=条件区域 2)*…*(条件 n=条件区域 n)),返回区域)涉及 Lookup 模糊匹配的用法，比如说要在 901,902,903 中查找是否存在 901，在 Excel 函数中，我们一般会用 Find 函数。
假设，我们要查找班级 901 对应的位置。
如下图，利用 Find 函数，如果返回值为数值，则存在班级 901，如果返回错误值，则表示不存在。后续我们可以用 Isnumber 函数判断是否存在数值，即是否存在班级 901。
PS : Isnumber(value) , 如果参数为数值则返回 True，否则返回 False。
辅助列都做好了，我们来尝试用 Lookup 查找班级为 901，科目为语文。
经典公式 ：
=LOOKUP(1,0/((条件 1=条件区域 1)*(条件 2=条件区域 2)),返回区域)输入对应区域的公式：
=LOOKUP(1,0/(($B$2:$B$59="语文")*ISNUMBER($H$2:$H$59)),$A$2:$A$59)条件 1：语文
条件区域 1：科目列（$B$2:$B$59）
条件区域 2：ISNUMBER($H$2:$H$59)
返回区域：姓名列 ( $A$2:$A$59 )
将查找班级 901 的 Find 函数列合并到公式中：
=LOOKUP(1,0/(($B$2:$B$59="语文")*ISNUMBER(FIND("901",$G$2:$G$59))),$A$2:$A$59)
将完整的公式输入到下图右表中，查找值语文和班级 901，分别替换成对应的混合引用：
=LOOKUP(1,0/(($B$2:$B$59=J$1)*ISNUMBER(FIND($I2,$G$2:$G$59))),$A$2:$A$59)"语文"替换为 J$1
"901"替换为$I2
到这里就搞定了~
上面是用辅助列的做法完成的，如果想要一个公式完成也可以。如下图，有兴趣的小伙伴可以自行去研究一下。
=LOOKUP(1,0/(($B$2:$B$59=I$1)*MMULT(--($C$2:$F$59=$H2),ROW(1:4)^0)),$A$2:$A$59)PS：MMULT 函数是个矩阵函数，使用这个函数的目的也是在于将多列判断合并为一列。
前面我们用辅助列和函数的做法，完成的，对吧，特别复杂。
复杂的主要原因在于我们的数据源是个二维表，并不是我们查找匹配中最喜欢的一维表。
那要是将数据源先转化为一维表，是不是就简单很多了呢？
下面我们用 PowerQuery，只需三步，动动鼠标就可以轻松搞定。
PS : PowerQuery（简称 PQ）是 Excel2016 以及以上版本自带插件，Excel2013 后台回复【插件】自行安装后使用。
PQ 法
我们来看看具体操作~
既然说到 PQ，自然是先把数据导入到 PQ 编辑器中。
选中数据源区域-在【数据】选项卡下-单击【来自表格/区域】-创建表对话框单击【确定】按钮。
❶利用逆透视列，将数据源转换为一维数据表
选中所有的班级列，鼠标右键【逆透视列】。
删除属性列，此时就是一维数据表了。
操作动图如下：
❷透视列 , 班级为行，科目为列，姓名为值
选中科目列- 在【转换】选项卡下-单击【透视列】。
值列：姓名
聚合值函数：不要聚合
操作动图如下：
❸更改列的顺序
我们发现，此时的列名不是按照我们所需的列名语文 , 数学……顺序排列的 。
只需将公式中的第二参数改成科目 list，就可以搞定！
原先的：
= Table.Pivot(删除的列,List.Distinct(删除的列[科目]),"科目","姓名")更改后的：
= Table.Pivot(删除的列,{"语文","数学","英语","物理","化学","政治","历史"},"科目","姓名")操作动图如下：
到这里，我们就搞定了。
是不是超级简单鸭~
最后的话
本文讲解的是教师比较常见的，分班查询的表格需求，主要难点在于二维表查询二维表数据源。
Lookup 函数法中， 巧借合并函数，将多列合并为一列，利用 Find 函数查找位置，返回数值表示存在，返回错误值表示不存在。
利用经典函数套路Lookup(1,0/((条件 1=条件区域 1)＊(条件 1=条件区域 1)),返回区域)查询匹配结果。
关于 Lookup 经典用法可以戳下文：
在 PQ 方法中，我们通过【逆透视列】+【透视列】，点点鼠标就完成了我们的表格需求。
对于二维数据转换为一维数据，PQ 的逆透视列是比较强大的功能。有了一维表后，数据的查询匹配就难不到我们啦~
下面出个选择题考考大家，巩固一下本文的知识点。
下面说法正确的是(多选)
生活中处处都是 Excel ，处处都会用到 Excel 。
如果你还在为 Excel 头疼不已，偶尔还会为 Excel 熬夜加班，看教程觉得自己都会了，实操起来还是两眼一抹黑……
这个时候就需要系统地学习 Excel 啦！
小 E 推荐你加入《秋叶 Excel 3 天集训营》~
集训营里有1 场直播+2 场录播+老师助教答疑服务；
不仅教常用技巧、函数、图表，更教 Excel 数据处理的思路方法、表格设计的内功心法！
学完直接用到工作中，点点鼠标就搞定数据整理分析，拖拖拽拽轻松完成工作任务！
现在报名，还免费赠送 【35 个常用函数说明】 ！
赶紧扫码预约吧！
本文到这里就结束啦~大家关于表格还遇到哪些「坑」，可以在留言区一起交流哦~
遇到有价值的文章
不放过 !
动动小手
分享给朋友~
热门跟贴