所謂一對多,就是符合某個指定條件的有多個結(jié)果,要把這些結(jié)果都提取出來。如下圖所示,希望根據(jù)F2單元格中指定的部門,提取出左側(cè)列表中“生產(chǎn)部”的所有人員姓名。 如果你使用的是Excel 2019及以下版本,可以在H2單元格輸入以下公式,按住Shift+ctrl不放,按回車,再將公式向下拖動到出現(xiàn)空白單元格為止: =INDEX(A:A,SMALL(IF(B$2:B$16=F$2,ROW($2:$16),4^8),ROW(A1)))&'' 公式有點復(fù)雜,具體的解釋可參考這里:一對多數(shù)據(jù)查詢,萬金油公式請拿好 如果你使用的是Excel 2021,可以在H2單元格輸入這個公式,按回車,公式結(jié)果會自動溢出到其他單元格。=FILTER(A2:A16,B2:B16=F2)FILTER函數(shù)的作用是篩選符合條件的單元格。函數(shù)寫法為:=FILTER(要返回內(nèi)容的數(shù)據(jù)區(qū)域,指定的條件,[沒有記錄時返回的內(nèi)容])本例中,要返回內(nèi)容的數(shù)據(jù)區(qū)域是A2:A16。指定的條件是“B2:B16=F2”,這部分對比后,返回一組由邏輯值TRUE或FALSE組成的內(nèi)存數(shù)組。如果數(shù)組中的某個元素是TRUE,FILTER函數(shù)就返回第一參數(shù)中對應(yīng)位置的內(nèi)容。如下圖所示,希望提取出部門為“生產(chǎn)部”,并且學(xué)歷為“本科”的所有記錄。如果你使用的是Excel 2019及以下版本,可以在I2單元格輸入以下公式,按住Shift+ctrl不放,按回車,再將公式向下拖動到出現(xiàn)空白單元格為止:=INDEX(A:A,SMALL(IF((B$2:B$16=F$2)*(C$2:C$16=G$2),ROW($2:$16),4^8),ROW(A1)))&''如果你使用的是Excel 2021,可以在I2單元格輸入這個公式,按回車,公式結(jié)果會自動溢出到其他單元格。=FILTER(A2:A16,(B2:B16=F2)*(C2:C16=G2))本例中,FILTER函數(shù)的第二參數(shù)使用兩組等式,對部門和學(xué)歷兩個條件進(jìn)行判斷,得到兩組由邏輯值組成的內(nèi)存數(shù)組。再將這兩個內(nèi)存數(shù)組中的元素對應(yīng)相乘,如果兩個內(nèi)存數(shù)組中同一位置的元素都是TRUE,相乘后結(jié)果為1,否則為0,計算后得到一組新的內(nèi)存數(shù)組。如果數(shù)組中的某個元素是1,FILTER函數(shù)就返回第一參數(shù)中對應(yīng)位置的內(nèi)容。如下圖所示,希望查詢學(xué)歷中包含關(guān)鍵字“科”的所有姓名。不論是本科、??七€是民科,都符合要求。如果你使用的是Excel 2019及以下版本,可以在H2單元格輸入以下公式,按住Shift+ctrl不放,按回車,再將公式向下拖動到出現(xiàn)空白單元格為止:=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(F$2,C$2:C$16)),ROW($2:$16),4^8),ROW(A1)))&'' 如果你使用的是Excel 2021,可以在H2單元格輸入這個公式,按回車,公式結(jié)果會自動溢出到其他單元格。 =FILTER(A2:A16,ISNUMBER(FIND(F2,C2:C16))) 本例中,FILTER函數(shù)的第二參數(shù)中,先使用FIND函數(shù)查詢F2單元格的關(guān)鍵字在C2:C16區(qū)域的每個單元格中所處的位置。如果C2:C16區(qū)域的單元格內(nèi)包含有關(guān)鍵字,就返回表示位置的數(shù)字。如果沒有關(guān)鍵字,F(xiàn)IND函數(shù)會返回錯誤值。 接下來再使用ISNUMBER函數(shù),判斷FIND函數(shù)的結(jié)果是不是數(shù)值,返回由邏輯值TRUE或FALSE組成的內(nèi)存數(shù)組。 在某個單元格中包含關(guān)鍵字時,ISNUMBER函數(shù)返回的是TRUE,否則返回的是FALSE。
最后使用FILTER函數(shù),返回A列中與TRUE對應(yīng)位置的內(nèi)容。 好了,今天咱們的分享就是這些吧,祝各位一天好心情~~
|