|
★ 编按 ★ 最近小李遇到这样一个问题，公司将总部人员分了四个组去支援门店的促销活动，分组信息如图所示：  在支援结束后，根据门店的反馈，选出了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列的话，你会调整最终的公式吗？ 今日互动话题 在评论区留下你的足迹叭~ 上述的函数公式原理你能明白几个呢？ |
|
|
