EXCEL中數組函數運用范例

　　工作簿內一共有兩個工作表Sheet1和Sheet2，先說Sheei1，如下

　　A 　 B 　 C

　　貨號 序號 　 名稱

　　101 　 1 　 車背帶

　　101 　 2 　 合前片

　　101 　 3 　 合後片

　　101 　 4 　 車手帶

　　101 　 5 　 車邊片

　　101 　 6 　 合前袋

　　101 　 7 　 車後手帶

　　202 　 1 　 車前片鏈

　　202 　 2 　 車前袋

　　202 　 3 　 合包

　　202 　 4 　 車後片

　　202 　 5 　 車手墊

　　202 　 6 　 合前袋

　　表Sheet2如下：

　　A 　 B 　 C

　　貨號 序號 　 名稱

　　101 　 5 　 [此單元格空]

　　101 　 2 　 [此單元格空]

　　101 　 3 　 [此單元格空]

　　101 　 7 　 [此單元格空]

　　202 　 3 　 [此單元格空]

　　202 　 1 　 [此單元格空]

　　要求根據Sheet2內容，查找Sheet1，在Sheet2的名稱列填寫入正確的內容。

　　題目分析：

　　根據要求，如果要在Sheet2某一行n填入正確的名稱，首先需要在Sheet1找到這樣的行：該行第一列內容等於Sheet2某行n第一列，該行第二列內容等於Sheet2某行n第二列。

　　因為涉及到數據的查找，擬采用數組公式實現。

　　1.首先定義名稱：

　　(定義名稱使用菜單：插入-名稱)

　　dataA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

　　dataB=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

　　這樣做的好處是在於無論Sheet1添加了多少行，dataA始終包括A列有內容的行，這樣比起直接使用A:A整列，要節約很多的計算時間。dataB同理。

　　2.匹配條件

　　先匹配A列，選中Sheet2!D2:D13(因為原始數據從2~13行)，在編輯欄輸入

　　=IF(dataA=A2,1,0)

　　用Ctrl+Shift+回車，這樣可以看到凡是與Sheet2!A2匹配的Sheet1的行，在這裡都變成了1，而不匹配的則是0。

　　同理，在Sheet2!E2:E13輸入

　　=IF(dataB=B2,1,0)

　　這樣我們在D、E兩列就得到了分別與Sheet2!A和Sheet2!B列匹配的數組。

　　3.合並匹配條件

　　我們需要的是兩個條件的與運算，因此使用乘法是最好的，只有兩個條件同時成立，1*1=1，否則兩個乘數裡面至少有1個為0，結果為0。

　　well，在Sheet2!F2:F13中輸入數組公式：

　　=IF((dataA=A2)*(dataB=B2),1,0)

　　如此一來，就得到了一個新的由1和0組成的數組，某元素對應Sheet1的行如果能夠匹配，該元素為1，否則為0。

　　4.檢索行號

　　有了這個數組，我們需要得到匹配出來的行號，因為數組是由若干0和一個1組成的，所以我們只需要使用MATCH函數，就可以得到與Sheet2!An匹配的Sheet1的行號。

　　在Sheet2!G1輸入

　　=MATCH(1，IF((dataA=A2)*(dataB=B2),1,0),1)

　　注意此處雖然僅僅是在一個單元格裡面輸入公式，但仍然是一個數組公式，需要使用Ctrl+Shift+Enter。

　　結果就是Sheet1中匹配的行號。

　　5.檢索結果

　　有了行號，檢索結果就很容易了，我們使用INDIRECT函數。

　　在Sheet2!C2輸入數組公式：

　　=INDIRECT("Sheet1!$C$" & MATCH(1，IF((dataA=A2)*(dataB=B2),1,0),1))

　　注意依然使用Ctrl+Shift+Enter，正確的結果就出現了。

　　對於下面的行，只需要拖動填充句柄復制公式就可以了。

　　另外別忘了刪除掉用來演示的D、E、F、G列的已經不再需要的公式。