編按：哈嘍，大家好！不管是在小說，還是電視劇中，主角們都有著一招制勝的法寶，這讓他們每次都能“逢兇化吉”。那么回到職場(chǎng)中，每一位EXCELER在面對(duì)各種各樣的問題時(shí)，能不能也有一招能“碾壓四方”的絕技呢？趕緊來看看吧！
＊＊＊＊＊＊＊＊＊
【前言】
在武俠小說里，經(jīng)常會(huì)有一種情況——帶著主角光環(huán)的人，總是憑借“一招鮮，吃遍天”的“偉大漏洞”，處處逢兇化吉。都說“職場(chǎng)如戰(zhàn)場(chǎng)”，來到了職場(chǎng)，每一位EXCELER，面對(duì)來自四面八方的工作壓力，也算是“處處逢兇”了。那么能不能也有這么“一招”，讓同學(xué)們“隨時(shí)化吉”呢？跟上E圖表述的步伐，來學(xué)習(xí)一招“以不變應(yīng)萬變”的絕技吧。
【正文】
不多廢話，切入主題。同學(xué)們知道在日常表格中，有一種信息內(nèi)容叫做“序號(hào)”嗎？我們今天就來“玩”這個(gè)序號(hào)，我保證你會(huì)“愛”上這個(gè)“小操作”的。
添加序號(hào)的方法
在日常生活中，我們每個(gè)人添加序號(hào)的方式可能都不一樣，主要?dú)w納起來有下面幾種。
這是較常規(guī)的幾種添加序號(hào)的方法，但是也僅限于“常規(guī)”。而我們今天要學(xué)的內(nèi)容，就是非常規(guī)的序號(hào)，而這樣的序號(hào)可以給我們帶來“前所未有且簡(jiǎn)單”的體驗(yàn)。
一、“動(dòng)態(tài)”提取不重復(fù)的值并統(tǒng)計(jì)
遇到這種情況，大部分同學(xué)會(huì)使用兩種方法解決：
第一種：首先復(fù)制粘貼出E列內(nèi)容，然后在“數(shù)據(jù)”選項(xiàng)卡中，點(diǎn)擊“刪除重復(fù)項(xiàng)”功能鍵，再用SUMIF函數(shù)求和。
B22單元格函數(shù)：
=SUMIF($E$2:$E$16,A22,$F$2:$F$16)
第二種：直接使用數(shù)組函數(shù)，得到不重復(fù)的費(fèi)用類別，再使用SUMIF函數(shù)求和。
A22單元格函數(shù)：
{=IFERROR(INDEX($E$2:$E$16,SMALL(IF(MATCH($E$2:$E$16,$E$2:$E$16,0)=ROW($1:$15),ROW($1:$15),99^9),ROW(A1))),"")}
輸入完成后，按數(shù)組函數(shù)的結(jié)束鍵CTRL+SHIFT+ENTER三鍵結(jié)束。
你會(huì)選擇哪種呢？如果說我們需要在源數(shù)據(jù)更新后，也能實(shí)現(xiàn)自動(dòng)統(tǒng)計(jì)的話，肯定是選擇函數(shù)的做法，但是這個(gè)數(shù)組函數(shù)真的不是初學(xué)者能夠駕馭的（上面的數(shù)組函數(shù)不是今天的主題，故不作展開說明），下面我們就用序號(hào)的方法來處理這個(gè)問題。
步驟1：在數(shù)據(jù)首列前插入一列“輔助列”，在A2單元格輸入函數(shù)：=IF(COUNTIF($F$2:F2,F2)=1,MAX($A$1:A1)+1,"")，下拉填充得到被引用的序號(hào)。這里使用COUNTIF函數(shù)結(jié)合絕對(duì)引用，使區(qū)域中的首個(gè)單元格固定，末單元格逐步擴(kuò)大，通過IF函數(shù)判斷，如果COUNTIF返回1，即為目標(biāo)值第一次出現(xiàn)，再使用MAX函數(shù)結(jié)合絕對(duì)引用，累加出唯一出現(xiàn)的值所對(duì)應(yīng)的順次；
此類引用方法在之前的教程中介紹過，小伙伴們可以點(diǎn)擊教程《同樣是countifs函數(shù)，為什么同事卻使得比你好？原因在這里！》學(xué)習(xí)，此處不做贅述了。
步驟2：在B22單元格輸入函數(shù)：
=IFERROR(VLOOKUP(ROW(B1),$A$2:$F$16,6,0),"")，下拉填充函數(shù)后，引出不重復(fù)的費(fèi)用類別。使用ROW函數(shù)得到序號(hào)，用VLOOKUP逐步引出這個(gè)序號(hào)對(duì)應(yīng)的費(fèi)用類別，當(dāng)ROW函數(shù)的序號(hào)在索引區(qū)域中沒有出現(xiàn)時(shí)，用IFERROR函數(shù)規(guī)避錯(cuò)誤值#N/A；
步驟3：在C22單元格輸入函數(shù)：
=IF(B22="","",SUMIF($F$2:$F$16,B22,$G$2:$G$16))，當(dāng)對(duì)應(yīng)的B列內(nèi)容不為空時(shí)，匯總各個(gè)費(fèi)用類別的金額。
這樣一來是不是簡(jiǎn)單了很多？而且是隨數(shù)據(jù)源的更新而變動(dòng)的哦~
二、“動(dòng)態(tài)”分類提取明細(xì)
按照上面的思路，我們?cè)倏匆粋€(gè)工作中經(jīng)常會(huì)遇到的問題。如下圖所示，我們需要按照不同的費(fèi)用類別，提取出對(duì)應(yīng)的明細(xì)數(shù)據(jù)。
步驟1：同樣在數(shù)據(jù)首列前插入空白列。在A2單元格輸入函數(shù)：=IF(F2=$G$19,MAX($A$1:A1)+1,"")，下拉填充公式，得到滿足條件的記錄序號(hào)。當(dāng)數(shù)據(jù)中的費(fèi)用類別和被統(tǒng)計(jì)項(xiàng)G19單元格（注意使用絕對(duì)引用）相同時(shí)，利用MAX函數(shù)標(biāo)記序號(hào)。
步驟2：在B22單元格輸入函數(shù)：
=IFERROR(VLOOKUP(ROW(B1),$A$1:$G$16,MATCH(B$21,$A$1:$G$1,0),0),"")，下拉右拉填充公式。這里依然是使用VLOOKUP函數(shù)索引ROW函數(shù)，再利用MATCH函數(shù)，找到表頭的順序號(hào)，作為VLOOKUP在索引區(qū)域中被索引的列序。當(dāng)然，這個(gè)表格依舊是可以實(shí)現(xiàn)動(dòng)態(tài)更新的。
三、“動(dòng)態(tài)”多條件提取明細(xì)
同樣的思路再來“玩”一個(gè)多條件的索引。如下圖所示，需要提取出滿足多個(gè)條件的明細(xì)數(shù)據(jù)。
步驟1：同樣在A2單元格輸入函數(shù)：
=IF(AND(C2>=$D$21,C2<=$D$22,E2=$F$21,F2=$F$22),MAX($A$1:A1)+1,"")，使用AND函數(shù)，使IF函數(shù)形成多條件同時(shí)滿足與否的判斷，標(biāo)記出多條件都滿足的明細(xì)記錄序號(hào)。
步驟2：在B25單元格輸入函數(shù)：
=IFERROR(VLOOKUP(ROW(B1),$A$1:$G$16,MATCH(B$24,$A$1:$G$1,0),0),"")，此函數(shù)同案例2的函數(shù)是一樣的，我們就不多介紹了?？匆幌聞?dòng)態(tài)的效果吧。
四、“動(dòng)態(tài)”將明細(xì)按金額大小排序
給一列數(shù)字排名，估計(jì)很多同學(xué)都知道用RANK函數(shù)吧，但是如果有重復(fù)的數(shù)字，就需要RANK+COUNTIF函數(shù)的嵌套使用，起到不重復(fù)排名的效果，這個(gè)案例就是利用了這個(gè)原理。
步驟1：在A3單元格輸入函數(shù)：=RANK(D3,$D$3:$D$17,IF($H$1="降序",0,1))+COUNTIF($D$3:D3,D3)-1，先通過RANK函數(shù)得到數(shù)值的排名，用IF函數(shù)判斷H1單元格的數(shù)據(jù)，如果是“降序”則返為“0”，如果是“升序”或者單元格為空，則默認(rèn)返回“1”。COUNTIF函數(shù)是去重排名的關(guān)鍵，同樣是利用絕對(duì)引用固定區(qū)域中開始單元格的位置，確定數(shù)字出現(xiàn)的次數(shù)，如果第一次出現(xiàn)，1-1=0，則直接返回RANK函數(shù)得到的排名。第二次出現(xiàn)就是2-1=1，則在RANK函數(shù)排名的基礎(chǔ)上+1。這樣就完成了重復(fù)排名增加1位次的過程。
步驟2：在F3單元格輸入函數(shù)：
=IFERROR(VLOOKUP(ROW(F1),$A$2:$D$17,MATCH(F$2,$A$2:$D$2,0),0),"")，這個(gè)函數(shù)的原理依然同案例2的索引過程。
通過選擇升序降序的選項(xiàng)，做到動(dòng)態(tài)羅列數(shù)據(jù)的過程，如下：
五、“動(dòng)態(tài)”插入空白行
對(duì)于這個(gè)需求，現(xiàn)下最常用的方法是“添加序號(hào)排序的方法”，如下：
但是這種方式有一個(gè)弊端，如果插入的空白行不固定，那就需要頻繁的操作，而且序號(hào)的粘貼過程也比較麻煩，插入幾行就要復(fù)制幾次序號(hào)。所以我們就一起來看一個(gè)函數(shù)+序號(hào)動(dòng)態(tài)插入空白行的案例。
步驟1：在A列給源數(shù)據(jù)表添加序號(hào)，在A3單元格輸入函數(shù)：=ROW(A1)，然后下拉公式填充。
步驟2：在F2單元格輸入“輔助”，在F3單元格輸入函數(shù)：=IF(COUNTIF($F$2:F2,F2)<$I$1+1,MAX(F2:F2),F2+1)，依然是利用了COUNTIF結(jié)合絕對(duì)引用的做法，判斷從起始單元格F2到上一個(gè)單元格為止的區(qū)域中，上一個(gè)單元格的值出現(xiàn)了幾次，如果值小于間隔行數(shù)+1，就顯示已經(jīng)出現(xiàn)的最大序號(hào)（用MAX函數(shù)引出），否則序號(hào)累計(jì)1，就是函數(shù)中F2+1的運(yùn)算。然后下拉填充函數(shù)，如果數(shù)據(jù)較多，這里介紹一個(gè)利用“名稱框”代替下拉填充的過程。
<小常識(shí)>：在一個(gè)單元格中輸入函數(shù)，然后選擇“名稱框”，輸入需要填充的區(qū)域地址，按回車鍵，再按CTRL+D（向下填充），也可以按CTRL+R（向右填充），即可完成對(duì)區(qū)域的填充了。
步驟3：在G3單元格輸入函數(shù)：
=IFERROR(IF(AND($F3<>"",$F3<>$F2),VLOOKUP($F3+1,$A$2:$D$17,MATCH(G$2,$A$2:$D$2,0),0),""),"")，使用AND函數(shù)，使IF函數(shù)形成多條件同時(shí)滿足與否的判斷。當(dāng)F3單元格不為空，且F3單元格不等于上一個(gè)單元格的值的時(shí)候，使用VLOOKUP+MATCH函數(shù)的嵌套引用明細(xì)表內(nèi)容。填充函數(shù)后就是下面的效果了。
<小常識(shí)>：上面的5個(gè)案例都是一個(gè)數(shù)據(jù)源，5類數(shù)據(jù)處理的需求我們只用了一招“添加序號(hào)”的方式，輕易破解，其實(shí)這個(gè)“添加序號(hào)”就是我們經(jīng)常說的“輔助列”。
“輔助列”在原數(shù)據(jù)中，一般在首列之前，或者末列之后。目的是在不改變?cè)瓟?shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上，為了解決一些不能通過原數(shù)據(jù)直接處理的數(shù)據(jù)分析要求，而添加輔助計(jì)算的信息內(nèi)容。
【編后語】
在這里，作者E圖表述建議大家：如果大部分的情況，你都是使用函數(shù)來解決工作中的問題的話，“使用輔助列，不丟人”！很多人為了練習(xí)函數(shù)，甚至一些練習(xí)題，都是要求一步寫出函數(shù)，但是“學(xué)習(xí)”和“工作”一定不要產(chǎn)生羈絆，學(xué)以致用沒有錯(cuò)，但是你的領(lǐng)導(dǎo)不會(huì)等你學(xué)會(huì)了再安排工作。在工作上，永遠(yuǎn)是要效率第一。只有完成工作，你才有更多的時(shí)間去“學(xué)習(xí)”。今天的內(nèi)容，希望能夠在工作上幫你提速，能夠有更多的時(shí)間來“部落窩”繼續(xù)深造。
＊＊＊＊部落窩教育－excel輔助列應(yīng)用技巧＊＊＊＊
原創(chuàng)：E圖表述/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）
聯(lián)系客服
