华为HR：面试上百人，我希望你不仅懂VLOOKUP，还要精通这六大函数公式

★

编按

★

Hello小伙伴们，不知道大家有没有这样的体验，学习了很多Excel函数，但到了要用的时候却一个都想不到。今天我们就用一个“优秀员工组别查找”的工作案例，来实际运用一下那些常见的函数。

INDEX、OFFSET、INDIRECT、LOOKUP和HLOOKUP等函数用法不尽相同，但对于这个工作难题，你随便挑其中一个就能轻松解决。你准备好见证奇迹了吗~

最近小李遇到这样一个问题，公司将总部人员分了四个组去支援门店的促销活动，分组信息如图所示：



在支援结束后，根据门店的反馈，选出了12名优秀员工。领导让小李把这些员工所在的组别填一下，完成后是这样的。



因为领导要的比较急，而且人数也不是很多，所以小李就手工一个一个查找出来先完成了工作。但是事后小李觉得这个问题应该有公式可以一次下拉就得出结果的，所以就来求助看看该用什么公式来完成这个工作。

扫码入群，下载Excel练习文件，同步操作

小李的这种探索精神是值得肯定的，很多同学日常都会遇到各种各样的工作任务，有的任务确实可以手工去处理，但如果不去思考更快捷的解决方法，不但丧失了一次学习锻炼的机会，而且在下次遇到同样的问题时就只能干瞪眼。

#1

用COLUMN和MAX函数

计算出优秀员工对应的列号

回到正题，要用公式解决这个问题，其实方法还蛮多的，但是核心思路就一个，要能确定每个优秀员工在分组表里的第几列。

为了便于理解，把优秀员工和分组名单放到一起。=(F2=A2:D12)，用第一个优秀员工的名字与分组名单的名字做对比，结果是一个区域数组，其中只有一个是TRUE。

在Excel365版本中，借助数组公式自动扩展的功能，可以直观的看到这个TRUE所在的位置.



非365的版本只能借助F9功能键来看了。



在上述比较运算后面乘区域的列号

公式为=(F2=A2:D12)*COLUMN($A$2:$D$12)。这样就可以得到TRUE所在位置的列号。

Excel365中的效果：



其他版本用F9的效果：



注意，此处的COLUMN函数用于获取列号，使用格式COLUMN(reference)，其中Reference为需要得到其列标的单元格或单元格区域。典型用法有三种。具体用法可以参考：会用Column吗？它让公式不那么笨。

接下来要做的就是：在这组数中用MAX把最大值提取出来，得到姓名在分组区域中的列号。

公式为：=MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12))，按“Ctrl+Shift+Enter”键结束。



#2

根据列号定位出组别

通过列号要得到对应的组别，INDEX、OFFSET、INDIRECT、LOOKUP和HLOOKUP函数都是可以办到。

INDEX解法

INDEX函数的语法为INDEX(array, row_num, [column_num])，用中文表达就是INDEX(数组或区域, 行号, 列号)。

如果数组只包含一行或一列，则相对应的参数Row_num 或 Column_num 为可选参数，只需要写“行”号或者“列”号。

所以INDEX解法的函数公式如下：

=INDEX($A$1:$D$1,MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)))，按“Ctrl+Shift+Enter”键结束。



OFFSET解法

=OFFSET($A$1,,MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12))-1,)，按“Ctrl+Shift+Enter”键结束。



INDIRECT解法

=INDIRECT("r1c"&MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),0)，按“Ctrl+Shift+Enter”键结束。



LOOKUP解法

=LOOKUP(MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),{1,2,3,4},$A$1:$D$1) ，按“Ctrl+Shift+Enter”键结束。



HLOOKUP解法

=HLOOKUP(MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),IF({1;0},{1,2,3,4},$A$1:$D$1),2,0)，按“Ctrl+Shift+Enter”键结束。



TEXT解法

因为本例中的组别使用的是中文数字，所以TEXT函数也可以来凑个热闹。

=TEXT(MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),"第[dbnum1]0组")，按“Ctrl+Shift+Enter”键结束。



以上这些函数的具体用法之前的教程都有过讲解，这里只是针对G列得到的列号来返回具体的内容。

如果对于上述哪个公式理解困难的可以单独留言，我们根据大家的反馈再做讲解。

至此，解决这个问题给出了6个方法，回顾一下解题思路，分成两个过程：首先计算出优秀员工对应的列号，然后根据列号定位出组别。

在计算列号的时候，用到了比较运算和最大值函数，其实这也是一个典型的条件最大值问题。

#3

计算优秀员工对应列号的其它方法

除了前文给出的方法之外，还有MAX+IF组合的套路和SUMPRODUCT函数的解法。

MAX+IF组合：=MAX(IF(F2=$A$2:$D$12,COLUMN($A$2:$D$12)))，按“Ctrl+Shift+Enter”键结束。



SUMPRODUCT方法：=SUMPRODUCT((F2=$A$2:$D$12)*COLUMN($A$2:$D$12))



这些都是很常用的公式套路，可见每个问题背后都有多种多样的解法，但前提是大家一定要多思考，同时多积累经验，只有在不断的实战过程中，运用公式和函数的能力才能得到提高。

#4

思考题

最后给大家留一个思考题吧，如果本例中的分组明细不是这种格式，组别是位于A列的话，你会调整最终的公式吗？





今日互动话题

在评论区留下你的足迹叭~

上述的函数公式原理你能明白几个呢？