excel數組公式提取區域中的重復值

excel數組公式提取區域中的重復值

有時需要要提取多行多列區域中的重復數據到某列，如下圖所示，A2:C11包含一些重復的水果名稱（已用深紅色進行了標記），要將重復的名稱提取到E列。

在E2中輸入數組公式：

=INDIRECT(TEXT(MIN(IF((COUNTIF($A:$C,$A:$C)>1)*(COUNTIF($E:E1,$A:$C)=0),ROW(:)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

公式輸入完畢按Ctrl+Shift+Enter結束，然后拖動填充柄向下填充，直到公式結果返回空為止。

說明：

①INDIRECT函數的第二個參數為“0”（未輸入即為0），表示返回R1C1樣式的引用。

②TEXT函數的第二個參數為“R0C00”，將第一個參數的數值返回為R1C1樣式的文本。受該參數的限制，包含重復值的區域需在CV列（第100列）的左側，否則公式可能不會返回正確的結果。

③TEXT函數的第一個參數，以E3單元格中的公式為例說明如下。IF函數返回一個數組，本例為2行10列。當區域中的重復數據未出現在E3單元格上方的區域中時，數組元素為區域中相應單元格行號和列數所構成數值，即“行號×100+列數”；對于不重復的數據，或者雖重復但已出現在E3單元格的上方，數組元素為“7^8”（5764801），受“R0C00”格式的規定，其前5位為行數，后兩位為列數。表示如果出現這種情況，將返回R57648C01（即A57648）單元格的值，通常為空。MIN函數返回上述數組的最小值，如E3單元格為“203”，此時TEXT函數返回“R2C03”，INDIRECT函數返回“橘子”。

④公式中的“$A$2:$C$11”、“$2:$11”、“$A:$C”和“$E$1:E1”都需根據實際進行修改。

另外，當區域中只包含數值時，還可用下面的數組公式按從大到小的順序提取重復數值：

首先在E1單元格中輸入某個文本，如本例中的“重復值”字樣。然后在E2單元格中輸入數組公式：

=LARGE(IF(COUNTIF($A$2:$C$11,$A$2:$C$11)>1,$A$2:$C$11),SUM(COUNTIF($A$2:$C$11,$E$1:E1))+1)

公式輸入完畢按Ctrl+Shift+Enter結束，然后拖動填充柄向下填充，直到公式結果返“#NUM!”為止

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。