Excel里面數(shù)組函數(shù)應(yīng)用技巧
時(shí)間：
若木1由 分享
這是一位網(wǎng)友提給我的問(wèn)題，說(shuō)給大家看看；
工作簿內(nèi)一共有兩個(gè)工作表Sheet1和Sheet2，先說(shuō)Sheei1，如下
?。痢 。隆 。?br/> 貨號(hào) 序號(hào) 名稱(chēng)
101 1 車(chē)背帶
101 2 合前片
101 3 合后片
101 4 車(chē)手帶
101 5 車(chē)邊片
101 6 合前袋
101 7 車(chē)后手帶
202 1 車(chē)前片鏈
202 2 車(chē)前袋
202 3 合包
202 4 車(chē)后片
202 5 車(chē)手墊
202 6 合前袋
表Sheet2如下：
Ａ ?。隆 。?br/> 貨號(hào) 序號(hào) 名稱(chēng)
101 5 [此單元格空]
101 2 [此單元格空]
101 3 [此單元格空]
101 7 [此單元格空]
202 3 [此單元格空]
202 1 [此單元格空]
要求根據(jù)Sheet2內(nèi)容，查找Sheet1，在Sheet2的名稱(chēng)列填寫(xiě)入正確的內(nèi)容。
題目分析：
根據(jù)要求，如果要在Sheet2某一行n填入正確的名稱(chēng)，首先需要在Sheet1找到這樣的行：該行第一列內(nèi)容等于Sheet2某行n第一列，該行第二列內(nèi)容等于Sheet2某行n第二列。
因?yàn)樯婕暗綌?shù)據(jù)的查找，擬采用數(shù)組公式實(shí)現(xiàn)。
1、首先定義名稱(chēng)：
（定義名稱(chēng)使用菜單：插入-名稱(chēng)）
dataA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
dataB=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
這樣做的好處是在于無(wú)論Sheet1添加了多少行，dataA始終包括A列有內(nèi)容的行，這樣比起直接使用A:A整列，要節(jié)約很多的計(jì)算時(shí)間。dataB同理。
2、匹配條件
先匹配A列，選中Sheet2！D2:D13（因?yàn)樵紨?shù)據(jù)從2~13行），在編輯欄輸入
=IF(dataA=A2,1,0)
用Ctrl+Shift+回車(chē)，這樣可以看到凡是與Sheet2！A2匹配的Sheet1的行，在這里都變成了1，而不匹配的則是0。
同理，在Sheet2！E2:E13輸入
=IF(dataB=B2,1,0)
這樣我們?cè)贒、E兩列就得到了分別與Sheet2!A和Sheet2!B列匹配的數(shù)組。
3、合并匹配條件
我們需要的是兩個(gè)條件的與運(yùn)算，因此使用乘法是最好的，只有兩個(gè)條件同時(shí)成立，1*1=1，否則兩個(gè)乘數(shù)里面至少有1個(gè)為0，結(jié)果為0。
well，在Sheet2!F2:F13中輸入數(shù)組公式：
=IF((dataA=A2)*(dataB=B2),1,0)
如此一來(lái)，就得到了一個(gè)新的由1和0組成的數(shù)組，某元素對(duì)應(yīng)Sheet1的行如果能夠匹配，該元素為1，否則為0。
4、檢索行號(hào)
有了這個(gè)數(shù)組，我們需要得到匹配出來(lái)的行號(hào)，因?yàn)閿?shù)組是由若干0和一個(gè)1組成的，所以我們只需要使用MATCH函數(shù)，就可以得到與Sheet2!An匹配的Sheet1的行號(hào)。
在Sheet2!G1輸入
=MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）
注意此處雖然僅僅是在一個(gè)單元格里面輸入公式，但仍然是一個(gè)數(shù)組公式，需要使用Ctrl+Shift+Enter。
結(jié)果就是Sheet1中匹配的行號(hào)。
5、檢索結(jié)果
有了行號(hào)，檢索結(jié)果就很容易了，我們使用INDIRECT函數(shù)。
在Sheet2!C2輸入數(shù)組公式：
=INDIRECT("Sheet1!$C$" & MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）)
注意依然使用Ctrl+Shift+Enter，正確的結(jié)果就出現(xiàn)了。
對(duì)于下面的行，只需要拖動(dòng)填充句柄復(fù)制公式就可以了。
另外別忘了刪除掉用來(lái)演示的D、E、F、G列的已經(jīng)不再需要的公式。
工作簿內(nèi)一共有兩個(gè)工作表Sheet1和Sheet2，先說(shuō)Sheei1，如下
?。痢 。隆 。?br/> 貨號(hào) 序號(hào) 名稱(chēng)
101 1 車(chē)背帶
101 2 合前片
101 3 合后片
101 4 車(chē)手帶
101 5 車(chē)邊片
101 6 合前袋
101 7 車(chē)后手帶
202 1 車(chē)前片鏈
202 2 車(chē)前袋
202 3 合包
202 4 車(chē)后片
202 5 車(chē)手墊
202 6 合前袋
表Sheet2如下：
Ａ ?。隆 。?br/> 貨號(hào) 序號(hào) 名稱(chēng)
101 5 [此單元格空]
101 2 [此單元格空]
101 3 [此單元格空]
101 7 [此單元格空]
202 3 [此單元格空]
202 1 [此單元格空]
要求根據(jù)Sheet2內(nèi)容，查找Sheet1，在Sheet2的名稱(chēng)列填寫(xiě)入正確的內(nèi)容。
題目分析：
根據(jù)要求，如果要在Sheet2某一行n填入正確的名稱(chēng)，首先需要在Sheet1找到這樣的行：該行第一列內(nèi)容等于Sheet2某行n第一列，該行第二列內(nèi)容等于Sheet2某行n第二列。
因?yàn)樯婕暗綌?shù)據(jù)的查找，擬采用數(shù)組公式實(shí)現(xiàn)。
1、首先定義名稱(chēng)：
（定義名稱(chēng)使用菜單：插入-名稱(chēng)）
dataA=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
dataB=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)
這樣做的好處是在于無(wú)論Sheet1添加了多少行，dataA始終包括A列有內(nèi)容的行，這樣比起直接使用A:A整列，要節(jié)約很多的計(jì)算時(shí)間。dataB同理。
2、匹配條件
先匹配A列，選中Sheet2！D2:D13（因?yàn)樵紨?shù)據(jù)從2~13行），在編輯欄輸入
=IF(dataA=A2,1,0)
用Ctrl+Shift+回車(chē)，這樣可以看到凡是與Sheet2！A2匹配的Sheet1的行，在這里都變成了1，而不匹配的則是0。
同理，在Sheet2！E2:E13輸入
=IF(dataB=B2,1,0)
這樣我們?cè)贒、E兩列就得到了分別與Sheet2!A和Sheet2!B列匹配的數(shù)組。
3、合并匹配條件
我們需要的是兩個(gè)條件的與運(yùn)算，因此使用乘法是最好的，只有兩個(gè)條件同時(shí)成立，1*1=1，否則兩個(gè)乘數(shù)里面至少有1個(gè)為0，結(jié)果為0。
well，在Sheet2!F2:F13中輸入數(shù)組公式：
=IF((dataA=A2)*(dataB=B2),1,0)
如此一來(lái)，就得到了一個(gè)新的由1和0組成的數(shù)組，某元素對(duì)應(yīng)Sheet1的行如果能夠匹配，該元素為1，否則為0。
4、檢索行號(hào)
有了這個(gè)數(shù)組，我們需要得到匹配出來(lái)的行號(hào)，因?yàn)閿?shù)組是由若干0和一個(gè)1組成的，所以我們只需要使用MATCH函數(shù)，就可以得到與Sheet2!An匹配的Sheet1的行號(hào)。
在Sheet2!G1輸入
=MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）
注意此處雖然僅僅是在一個(gè)單元格里面輸入公式，但仍然是一個(gè)數(shù)組公式，需要使用Ctrl+Shift+Enter。
結(jié)果就是Sheet1中匹配的行號(hào)。
5、檢索結(jié)果
有了行號(hào)，檢索結(jié)果就很容易了，我們使用INDIRECT函數(shù)。
在Sheet2!C2輸入數(shù)組公式：
=INDIRECT("Sheet1!$C$" & MATCH（1，IF((dataA=A2)*(dataB=B2),1,0),1）)
注意依然使用Ctrl+Shift+Enter，正確的結(jié)果就出現(xiàn)了。
對(duì)于下面的行，只需要拖動(dòng)填充句柄復(fù)制公式就可以了。
另外別忘了刪除掉用來(lái)演示的D、E、F、G列的已經(jīng)不再需要的公式。