編按：

Hello小伙伴們，不知道大家有沒有這樣的體驗(yàn)，學(xué)習(xí)了很多Excel函數(shù)，但到了要用的時(shí)候卻一個(gè)都想不到。今天我們就用一個(gè)“優(yōu)秀員工組別查找”的工作案例，來(lái)實(shí)際運(yùn)用一下那些常見的函數(shù)。INDEX、OFFSET、INDIRECT、LOOKUP和HLOOKUP等函數(shù)用法不盡相同，但對(duì)于這個(gè)工作難題，你隨便挑其中一個(gè)就能輕松解決。你準(zhǔn)備好見證奇跡了嗎~

最近小李遇到這樣一個(gè)問(wèn)題，公司將總部人員分了四個(gè)組去支援門店的促銷活動(dòng)，分組信息如圖所示：

在支援結(jié)束后，根據(jù)門店的反饋，選出了12名優(yōu)秀員工。領(lǐng)導(dǎo)讓小李把這些員工所在的組別填一下，完成后是這樣的。

因?yàn)轭I(lǐng)導(dǎo)要的比較急，而且人數(shù)也不是很多，所以小李就手工一個(gè)一個(gè)查找出來(lái)先完成了工作。但是事后小李覺得這個(gè)問(wèn)題應(yīng)該有公式可以一次下拉就得出結(jié)果的，所以就來(lái)求助看看該用什么公式來(lái)完成這個(gè)工作。小李的這種探索精神是值得肯定的，很多同學(xué)日常都會(huì)遇到各種各樣的工作任務(wù)，有的任務(wù)確實(shí)可以手工去處理，但如果不去思考更快捷的解決方法，不但喪失了一次學(xué)習(xí)鍛煉的機(jī)會(huì)，而且在下次遇到同樣的問(wèn)題時(shí)就只能干瞪眼。

一.用COLUMN和MAX函數(shù)計(jì)算出優(yōu)秀員工對(duì)應(yīng)的列號(hào)

回到正題，要用公式解決這個(gè)問(wèn)題，其實(shí)方法還蠻多的，但是核心思路就一個(gè)，要能確定每個(gè)優(yōu)秀員工在分組表里的第幾列。

為了便于理解，把優(yōu)秀員工和分組名單放到一起。=(F2=A2:D12)，用第一個(gè)優(yōu)秀員工的名字與分組名單的名字做對(duì)比，結(jié)果是一個(gè)區(qū)域數(shù)組，其中只有一個(gè)是TRUE。

在Excel365版本中，借助數(shù)組公式自動(dòng)擴(kuò)展的功能，可以直觀的看到這個(gè)TRUE所在的位置。

非365的版本只能借助F9功能鍵來(lái)看了。

在上述比較運(yùn)算后面乘區(qū)域的列號(hào)，公式為=(F2=A2:D12)*COLUMN($A$2:$D$12)，就可以得到TRUE所在位置的列號(hào)。

Excel365中的效果：

其他版本用F9的效果：

注意，此處的COLUMN函數(shù)用于獲取列號(hào)，使用格式COLUMN(reference)，其中Reference為需要得到其列標(biāo)的單元格或單元格區(qū)域。典型用法有三種。具體用法可以參考：會(huì)用Column嗎？ 它讓公式不那么笨。

接下來(lái)要做的就是：在這組數(shù)中用MAX把最大值提取出來(lái)，得到姓名在分組區(qū)域中的列號(hào)。

公式為：=MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12))，按“Ctrl+Shift+Enter”鍵結(jié)束。

二. 根據(jù)列號(hào)定位出組別

通過(guò)列號(hào)要得到對(duì)應(yīng)的組別，INDEX、OFFSET、INDIRECT、LOOKUP和HLOOKUP函數(shù)都是可以辦到。

INDEX解法

INDEX函數(shù)的語(yǔ)法為INDEX(array, row_num, [column_num])，用中文表達(dá)就是INDEX(數(shù)組或區(qū)域, 行號(hào), 列號(hào))。如果數(shù)組只包含一行或一列，則相對(duì)應(yīng)的參數(shù)Row_num 或 Column_num 為可選參數(shù)，只需要寫“行”號(hào)或者“列”號(hào)。所以INDEX解法的函數(shù)公式如下：

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

OFFSET解法：=OFFSET($A$1,,MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12))-1,)，按“Ctrl+Shift+Enter”鍵結(jié)束。

INDIRECT解法：=INDIRECT("r1c"&MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),0)，按“Ctrl+Shift+Enter”鍵結(jié)束。

LOOKUP解法：=LOOKUP(MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),{1,2,3,4},$A$1:$D$1) ，按“Ctrl+Shift+Enter”鍵結(jié)束。

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”鍵結(jié)束。

因?yàn)楸纠械慕M別使用的是中文數(shù)字，所以TEXT函數(shù)也可以來(lái)湊個(gè)熱鬧。

TEXT解法：=TEXT(MAX((F2=$A$2:$D$12)*COLUMN($A$2:$D$12)),"第[dbnum1]0組")，按“Ctrl+Shift+Enter”鍵結(jié)束。

以上這些函數(shù)的具體用法之前的教程都有過(guò)講解，這里只是針對(duì)G列得到的列號(hào)來(lái)返回具體的內(nèi)容。

至此，解決這個(gè)問(wèn)題給出了6個(gè)方法，回顧一下解題思路，分成兩個(gè)過(guò)程：首先計(jì)算出優(yōu)秀員工對(duì)應(yīng)的列號(hào)，然后根據(jù)列號(hào)定位出組別。

在計(jì)算列號(hào)的時(shí)候，用到了比較運(yùn)算和最大值函數(shù)，其實(shí)這也是一個(gè)典型的條件最大值問(wèn)題。

三. 計(jì)算優(yōu)秀員工對(duì)應(yīng)列號(hào)的其它方法

除了前文給出的方法之外，還有MAX+IF組合的套路和SUMPRODUCT函數(shù)的解法。

MAX+IF組合：=MAX(IF(F2=$A$2:$D$12,COLUMN($A$2:$D$12)))，按“Ctrl+Shift+Enter”鍵結(jié)束。

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

這些都是很常用的公式套路，可見每個(gè)問(wèn)題背后都有多種多樣的解法，但前提是大家一定要多思考，同時(shí)多積累經(jīng)驗(yàn)，只有在不斷的實(shí)戰(zhàn)過(guò)程中，運(yùn)用公式和函數(shù)的能力才能得到提高。

最后給大家留一個(gè)思考題吧！如果本例中的分組明細(xì)不是這種格式，組別是位于A列的話，你會(huì)調(diào)整最終的公式嗎？

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

７個(gè)Excel小技巧，提高表格查看效率

Excel運(yùn)用規(guī)范1：一個(gè)單元格只記錄一條信息

快速整理不規(guī)范的Excel表格的7個(gè)公式

9條最實(shí)用的計(jì)算excel中關(guān)于日期的公式?。ńㄗh收藏）

版權(quán)申明：

本文作者郅龍；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。