退一步海闊天空 - 使用輔助列，事半功倍

在學習、使用Excel的過程中，很多人容易走入一個誤區，也就是一味地追求「技術」。很多人往往能寫出複雜的公式，多個函數嵌套、數組公式、多維引用等都不在話下，遇到問題都試圖用一個公式來解決。其實，我想說的是，複雜的不一定是最好的。如果有簡便一點的方法，為什麼還要捨本逐末、捨近求遠呢？所謂退一步海闊天空，今天舉幾個使用輔助列的例子，談談輔助列的用法。

說到多條件查找，有人用if({1,0},,)的用法，有人用Index Match數組，或者Offset、Indirect等配合Match數組函數，還有人用Dget函數...總之是各顯神通。但是說到查找函數，我們首先想到的是Vlookup，只需要加個輔助列，不需要絞盡腦汁去記住其他複雜的方法。

比如，以下示例。

添加輔助列如下，設置公式：

=VLOOKUP(F2&"|"&G2,C:D,2,0)

這裡需要特別指出一點，我習慣在鏈接兩個欄位時加上「|」這個分隔符，因為有些情況下，如果不加這個分隔符可能會導致一些錯誤。如果你確信兩個欄位不會有相似的內容，則不加也可以。

在這個示例中，我們只用了大家最熟悉的Vlookup函數和輔助列，簡單而不費力，對於初學者來講是一個不錯的選擇。

多條件求和

現在多條件求和一般直接使用Sumifs，但是，我相信，還有的人沒學習過Sumifs函數，不會使用。但條件求和的Sumif更好理解一些。

請看下圖示例，要按照兩個條件進行數據匯總。

添加輔助列，將兩列鏈接成一列作為Sumif函數的第一個參數，設置公式如下：

=SUMIF(C:C,G2&"|"&H2,E:E)

生成不重複的隨機整數

這個功能可以用於抽獎環節，也可以用於人員的隨機分組、分配等。我們知道，生成隨機數的函數有Rand和RandBetween。Rand生成不重複的隨機小數；RandBetween生成指定範圍內的隨機整數，這個生成的結果可能會有重複的。但是抽獎、人員安排時都要求一個人不重複出現，這就要求隨機生成的數是唯一的。

下面我們就演示用輔助列生成1~30範圍內的不重複整數來生成獲獎人員，共抽取5個人獲獎。

A列共有30個人員姓名，B列使用公式=Rand()生成不重複的隨機數，C列用Rank對B列的結果進行排序。C列公式如下：

=RANK(B2,$B$2:$B$31)

由於B列是不重複的隨機數，所以C列排序的結果也就沒有重複值。

E、G列使用了兩種方式來生成獲獎名單，兩個公式不一樣，所以結果也不一樣，取任意一個公式即可。

公式1：=INDEX($A$2:$A$31,C2)

公式2：=INDEX($A$2:$A$31,MATCH(ROW()-1,$C$2:$C$31,0))

按顏色統計

在Excel中目前沒有按顏色求和或計數的函數，大家要使用時一般是自己編寫自定義函數。除了自定義函數，我們還可以用宏表函數Get.Cell配合輔助列來進行顏色求和或計數。

獲取單元格填充色的公式：

=Get.Cell(63,單元格地址)

獲取單元格字體顏色的公式：

=Get.Cell(24,單元格地址)

注意，這裡的單元格地址需要使用正確的引用類型。

當你需要固定地獲取某列或某行單元格的顏色時，可以使用混合引用。比如你要獲取B列單元格的顏色，把滑鼠放到第一行的任意一個單元格，然後創建以下名稱。

BackColor=GET.CELL(63,按顏色統計!$B1)

FontColor=GET.CELL(24,按顏色統計!$B1)

這裡$B1是混合引用，表示列固定，行隨公式所在的單元格變化而變化。

如下示例，輔助列可以放在任意一個地方，因為我們定義名稱中固定取B列的單元格。H列公式為=SUMIF(E:E,G2,D:D)。這裡在G列直接添上了顏色對應的代碼，所以Sumif公式的第二個參數取G列單元格的值。

當你需要取活動單元格相對位置的顏色時，就在定義名稱的單元格地址中使用相對引用。比如我們需要取左側單元格的顏色，選中B1單元格，創建定義名稱如下：

BackColorLeft=GET.CELL(63,按顏色統計2!A1)

FontColorLeft=GET.CELL(24,按顏色統計2!A1)

這時需要注意一點，輔助列也應該放到取顏色的單元格右側。

如下圖所示。

C列公式=BackColorLeft

H列公式=SUMIF(C:C,BackColorLeft,E:E)

輔助列用好了可以節省很多時間，可以解決一些複雜的問題。這裡僅列舉幾個示例，希望對大家有所啟發，關鍵的地方在於有使用輔助列的思維。條條大路通羅馬，簡單才是硬道理。

--End--