① 以一敵五它才是excel中最牛逼的技巧
在武俠小說里，經常會有一種情況——帶著主角光環的人，總是憑借「一招鮮，吃遍天」的「偉大漏洞」，處處逢凶化吉。都說「職場如戰場」，來到了職場，每一位EXCELER，面對來自四面八方的工作壓力，也算是「處處逢凶」了。那麼能不能也有這么「一招」，讓同學們「隨時化吉」呢?跟上E圖表述的步伐，來學習一招「以不變應萬變」的絕技吧。
【正文】
不多廢話，切入主題。同學們知道在日常表格中，有一種信息內容叫做「序號」嗎?我們今天就來「玩」這個序號，我保證你會「愛」上這個「小操作」的。 添加序號的方法
在日常生活中，我們每個人添加序號的方式可能都不一樣，主要歸納起來有下面幾種。
這是較常規的幾種添加序號的方法，但是也僅限於「常規」。而我們今天要學的內容，就是非常規的序號，而這樣的序號可以給我們帶來「前所未有且簡單」的體驗。
1
「動態」提取不重復的值並統計
遇到這種情況，大部分同學會使用兩種方法解決： 第一種：首先復制粘貼出E列內容，然後在「數據」選項卡中，點擊「刪除重復項」功能鍵，再用SUMIF函數求和。 B22單元格函數：=SUMIF($E$2:$E$16,A22,$F$2:$F$16)
第二種：直接使用數組函數，得到不重復的費用類別，再使用SUMIF函數求和。
A22單元格函數：{=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))),」」)} 輸入完成後，按數組函數的結束鍵CTRL+SHIFT+ENTER三鍵結束。 你會選擇哪種呢?如果說我們需要在源數據更新後，也能實現自動統計的話，肯定是選擇函數的做法，但是這個數組函數真的不是初學者能夠駕馭的(上面的數組函數不是今天的主題，故不作展開說明)，下面我們就用序號的方法來處理這個問題。
步驟1：在數據首列前插入一列「輔助列」，在A2單元格輸入函數：=IF(COUNTIF($F$2:F2,F2)=1,MAX($A$1:A1)+1,」」)，下拉填充得到被引用的序號。這里使用COUNTIF函數結合絕對引用，使區域中的首個單元格固定，末單元格逐步擴大，通過IF函數判斷，如果COUNTIF返回1，即為目標值第一次出現，再使用MAX函數結合絕對引用，累加出唯一出現的值所對應的順次; 此類引用方法在之前的教程中介紹過，小夥伴們可以點擊教程《同樣是countifs函數，為什麼同事卻使得比你好?原因在這里!》學習，此處不做贅述了。 步驟2：在B22單元格輸入函數：=IFERROR(VLOOKUP(ROW(B1),$A$2:$F$16,6,0),」」)，下拉填充函數後，引出不重復的費用類別。使用ROW函數得到序號，用VLOOKUP逐步引出這個序號對應的費用類別，當ROW函數的序號在索引區域中沒有出現時，用IFERROR函數規避錯誤值#N/A; 步驟3：在C22單元格輸入函數：=IF(B22=」」,」」,SUMIF($F$2:$F$16,B22,$G$2:$G$16))，當對應的B列內容不為空時，匯總各個費用類別的金額。 這樣一來是不是簡單了很多?而且是隨數據源的更新而變動的哦~
2
「動態」分類提取明細
按照上面的思路，我們再看一個工作中經常會遇到的問題。如下圖所示，我們需要按照不同的費用類別，提取出對應的明細數據。
步驟1：同樣在數據首列前插入空白列。在A2單元格輸入函數：=IF(F2=$G$19,MAX($A$1:A1)+1,」」)，下拉填充公式，得到滿足條件的記錄序號。當數據中的費用類別和被統計項G19單元格(注意使用絕對引用)相同時，利用MAX函數標記序號。
步驟2：在B22單元格輸入函數：=IFERROR(VLOOKUP(ROW(B1),$A$1:$G$16,MATCH(B$21,$A$1:$G$1,0),0),」」)，下拉右拉填充公式。這里依然是使用VLOOKUP函數索引ROW函數，再利用MATCH函數，找到表頭的順序號，作為VLOOKUP在索引區域中被索引的列序。當然，這個表格依舊是可以實現動態更新的。
3
「動態」多條件提取明細
同樣的思路再來「玩」一個多條件的索引。如下圖所示，需要提取出滿足多個條件的明細數據。
步驟1：同樣在A2單元格輸入函數：=IF(AND(C2>=$D$21,C2<=$D$22,E2=$F$21,F2=$F$22),MAX($A$1:A1)+1,」」)，使用AND函數，使IF函數形成多條件同時滿足與否的判斷，標記出多條件都滿足的明細記錄序號。
步驟2：在B25單元格輸入函數：=IFERROR(VLOOKUP(ROW(B1),$A$1:$G$16,MATCH(B$24,$A$1:$G$1,0),0),」」)，此函數同案例2的函數是一樣的，我們就不多介紹了。看一下動態的效果吧。
4
「動態」將明細按金額大小排序
給一列數字排名，估計很多同學都知道用RANK函數吧，但是如果有重復的數字，就需要RANK+COUNTIF函數的嵌套使用，起到不重復排名的效果，這個案例就是利用了這個原理。
步驟1：在A3單元格輸入函數：=RANK(D3,$D$3:$D$17,IF($H$1=」降序」,0,1))+COUNTIF($D$3:D3,D3)-1，先通過RANK函數得到數值的排名，用IF函數判斷H1單元格的數據，如果是「降序」則返為「0」，如果是「升序」或者單元格為空，則默認返回「1」。COUNTIF函數是去重排名的關鍵，同樣是利用絕對引用固定區域中開始單元格的位置，確定數字出現的次數，如果第一次出現，1-1=0，則直接返回RANK函數得到的排名。第二次出現就是2-1=1，則在RANK函數排名的基礎上+1。這樣就完成了重復排名增加1位次的過程。
步驟2：在F3單元格輸入函數：=IFERROR(VLOOKUP(ROW(F1),$A$2:$D$17,MATCH(F$2,$A$2:$D$2,0),0),」」)，這個函數的原理依然同案例2的索引過程。
通過選擇升序降序的選項，做到動態羅列數據的過程，如下：
5
「動態」插入空白行
對於這個需求，現下最常用的方法是「添加序號排序的方法」，如下：
但是這種方式有一個弊端，如果插入的空白行不固定，那就需要頻繁的操作，而且序號的粘貼過程也比較麻煩，插入幾行就要復制幾次序號。所以我們就一起來看一個函數+序號動態插入空白行的案例。
步驟1：在A列給源數據表添加序號，在A3單元格輸入函數：=ROW(A1)，然後下拉公式填充。 步驟2：在F2單元格輸入「輔助」，在F3單元格輸入函數：=IF(COUNTIF($F$2:F2,F2)<$I$1+1,MAX(F2:F2),F2+1)，依然是利用了COUNTIF結合絕對引用的做法，判斷從起始單元格F2到上一個單元格為止的區域中，上一個單元格的值出現了幾次，如果值小於間隔行數+1，就顯示已經出現的最大序號(用MAX函數引出)，否則序號累計1，就是函數中F2+1的運算。然後下拉填充函數，如果數據較多，這里介紹一個利用「名稱框」代替下拉填充的過程。 在一個單元格中輸入函數，然後選擇「名稱框」，輸入需要填充的區域地址，按回車鍵，再按CTRL+D(向下填充)，也可以按CTRL+R(向右填充)，即可完成對區域的填充了。
步驟3：在G3單元格輸入函數：=IFERROR(IF(AND($F3>」」,$F3>$F2),VLOOKUP($F3+1,$A$2:$D$17,MATCH(G$2,$A$2:$D$2,0),0),」」),」」)，使用AND函數，使IF函數形成多條件同時滿足與否的判斷。當F3單元格不為空，且F3單元格不等於上一個單元格的值的時候，使用VLOOKUP+MATCH函數的嵌套引用明細表內容。填充函數後就是下面的效果了。
上面的5個案例都是一個數據源，5類數據處理的需求我們只用了一招「添加序號」的方式，輕易破解，其實這個「添加序號」就是我們經常說的「輔助列」。
「輔助列」在原數據中，一般在首列之前，或者末列之後。目的是在不改變原數據結構的基礎上，為了解決一些不能通過原數據直接處理的數據分析要求，而添加輔助計算的信息內容。