閱： 3521 | 回： 0
等級:初學(xué)者
-
積分:0
-
財富值:0.00
-
身份:普通用戶
類似自定義篩選（包含）功能的數(shù)組公式
通常在Excel中的一列數(shù)據(jù)中找出包含指定文本的數(shù)據(jù)，可以用自定義自動篩選中的“包含”選項。有時可能也需要用公式來實現(xiàn)這樣的功能，這時可用下面的數(shù)組公式。例如A2:A101中包含100個三位數(shù)字，在D1單元格中輸入要查找（包含）的內(nèi)容，在其下方的單元格中返回結(jié)果，如圖所示。
在D3單元格輸入數(shù)組公式：
=OFFSET($A$1,SMALL(IF(ISERROR(SEARCH($D$1,$A$2:$A$101)),65535,ROW($1:$100)),ROW(A1)),)&""
公式輸入完畢后按Ctrl+Shift+Enter結(jié)束，然后拖動填充柄向下填充公式。
說明：該公式先用SEARCH函數(shù)判斷A列中是否包含指定的文本，如果A列某個單元格包含指定文本就返回該單元格的在A2:A101中的位置，否則返回“65535”。然后用SMALL和OFFSET函數(shù)按先后順序返回包含指定文本的單元格值，對于不包含指定文本的單元格，公式會在D列的后面返回A65536這個幾乎用不到的空單元格值。
例如要查找A列包含“12”的數(shù)據(jù)，在D1中輸入“12”即可。
由于用到了SEARCH函數(shù)，因而可以支持通配符。如要返回A列中結(jié)尾為“8”的數(shù)字，只需在D1中輸入“??8”。
通常在Excel中的一列數(shù)據(jù)中找出包含指定文本的數(shù)據(jù)，可以用自定義自動篩選中的“包含”選項。有時可能也需要用公式來實現(xiàn)這樣的功能，這時可用下面的數(shù)組公式。例如A2:A101中包含100個三位數(shù)字，在D1單元格中輸入要查找（包含）的內(nèi)容，在其下方的單元格中返回結(jié)果，如圖所示。
在D3單元格輸入數(shù)組公式：=OFFSET($A$1,SMALL(IF(ISERROR(SEARCH($D$1,$A$2:$A$101)),65535,ROW($1:$100)),ROW(A1)),)&""
公式輸入完畢后按Ctrl+Shift+Enter結(jié)束，然后拖動填充柄向下填充公式。
說明：該公式先用SEARCH函數(shù)判斷A列中是否包含指定的文本，如果A列某個單元格包含指定文本就返回該單元格的在A2:A101中的位置，否則返回“65535”。然后用SMALL和OFFSET函數(shù)按先后順序返回包含指定文本的單元格值，對于不包含指定文本的單元格，公式會在D列的后面返回A65536這個幾乎用不到的空單元格值。
例如要查找A列包含“12”的數(shù)據(jù)，在D1中輸入“12”即可。
由于用到了SEARCH函數(shù)，因而可以支持通配符。如要返回A列中結(jié)尾為“8”的數(shù)字，只需在D1中輸入“??8”。
我的個性簽名