EXCEL中數組函數運用範例 - 設計前沿

收藏到QQ書籤這是一位網友提給我的問題，說給大家看看；工作簿內一共有兩個工作表Sheet1和Sheet2，先說Sheei1，如下Ａ　Ｂ　Ｃ貨號序號　名稱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如下：Ａ　Ｂ　Ｃ貨號序號　名稱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列的已經不再需要的公式。

收藏到QQ書籤 投稿信箱:wzskynet
推薦閱讀：