excelperfect

工作表中記錄了人名及對(duì)應(yīng)的檔案編號(hào)，可能是由于疏忽，有一些人名重復(fù)錄入，導(dǎo)致出現(xiàn)了同一人名有多個(gè)檔案編號(hào)，現(xiàn)在我們想要在某人查詢其檔案時(shí)，判斷其是否有多個(gè)檔案編號(hào)。

示例數(shù)據(jù)如下圖1所示。

圖1

如果使用公式：

=INDEX(B2:B12,MATCH(D4,A2:A12,0))

只能返回找到的第一個(gè)人名對(duì)應(yīng)的編號(hào)，如果有重復(fù)人名就得不到后面的編號(hào)了。

自然而然地，我們會(huì)想到使用SMALL函數(shù)或者LARGE函數(shù)，然后與ROW函數(shù)或COLUMN函數(shù)配合，取出相應(yīng)的數(shù)據(jù)。

這里使用了一個(gè)巧妙的公式。在單元格E4中輸入：

=INDEX($B:$B,SMALL(($A:$A<>$D4)/1%+ROW($A:$A),COLUMN(A1)))&''

然后，向右拖放復(fù)制到其他單元格。

注意，這是一個(gè)數(shù)組公式，輸入完成后要按Ctrl+Shift+Enter組合鍵。

公式中：

($A:$A<>$D4)/1%+ROW($A:$A)

很巧妙。通過將列A中的數(shù)據(jù)與單元格D4中的數(shù)據(jù)相對(duì)比，得到一組由TRUE/FALSE值組成的數(shù)組，除以1%，既可將TRUE/FALSE值轉(zhuǎn)換成1/0，又可將數(shù)值縮小，以避免與后面的ROW($A:$A)相加時(shí)產(chǎn)生重復(fù)的數(shù)值。

然后，SMALL函數(shù)/COLUMN函數(shù)組合，取出相應(yīng)小的值，即為要查找的人名所在的位置。

還可以使用數(shù)組公式：

=INDEX($B:$B,SMALL(IF($A$2:$A$12=$D$4,ROW($A$2:$A$12),4^8),COLUMN(A1)))&''

其原理與上述相同，將與查找值不相同的對(duì)應(yīng)的數(shù)值足夠大，從而避免重復(fù)。