excelperfect 在Excel中,基于AND或OR條件從數(shù)據(jù)集中提取數(shù)據(jù)是經(jīng)常要做的事。可以使用下列方法來實現(xiàn): 1.篩選 2.高級篩選 3.使用輔助列的非數(shù)組公式 4.使用SMALL函數(shù)和INDEX函數(shù)的數(shù)組公式 5.使用AGGREGATE函數(shù)和INDEX函數(shù)的數(shù)組公式 關(guān)于上述5種方法的要點如下: 1.篩選和高級篩選通常比公式更容易使用,但是不像公式能即時更新。 2.當單元格中的條件改變時,公式能夠即時更新。 3.使用輔助列的非數(shù)組公式解決方案比數(shù)組公式計算速度更快。 4.數(shù)組公式可能使用許多單元格引用、包含許多計算,因此可能要更長的計算時間。要減小計算時間,考慮使用輔助列、布爾邏輯構(gòu)造和有效的函數(shù)。 5.這里沒有考慮使用VBA解決方案,有時使用它們是自動執(zhí)行數(shù)據(jù)提取的好方法。 為何提取數(shù)據(jù)的公式如此復雜? 當從表中提取數(shù)據(jù)時,實際上是在執(zhí)行查找。在Excel中,標準的查找函數(shù)例如INDEX、MATCH、VLOOKUP等都非常好,但當存在重復值時就比較困難了。如下圖1所示,提取滿足3個條件的數(shù)據(jù)記錄,可以看出有2條記錄滿足條件。對于垂直表,從多列中提取數(shù)據(jù)的查找公式不會很難;查找公式難于在多行中使用。如果需要使用公式提取記錄,那么有兩個基本的方法: 1.基于輔助列使用標準的查找函數(shù)。輔助列包含提供順序號的公式,只要公式找到了滿足條件的記錄。這些順序號解決了重復值問題,因為對于每條匹配的記錄都有唯一的標識號。輔助列作為查找列,供查找函數(shù)查找并提取數(shù)據(jù)。 2.基于全數(shù)據(jù)集的數(shù)組公式。這些公式是獨立的,不需要額外的列輔助。對于這些公式,必須在公式內(nèi)為與條件相匹配的記錄創(chuàng)建一個相對位置數(shù)組。 圖1:需要提取兩條記錄,標準的查找函數(shù)對于重復值有些困難。 使用輔助列來提取數(shù)據(jù) 假設(shè)有3個AND條件來決定要提取的記錄,如下圖2所示,可以在輔助列中使用AND函數(shù)。輔助列作為INDEX函數(shù)的查找列。單獨使用AND函數(shù)的問題是獲得了兩個TRUE值,這意味著又回到了查找列中有重復項的問題。真正想要的是查找列包含數(shù)字,其中單元格E14中第一個TRUE是數(shù)字1,而E17中第二個TRUE是數(shù)字2。 圖2:輔助列公式的第1部分涉及到AND函數(shù) 如下圖3所示,將AND函數(shù)作為SUM函數(shù)的第1個參數(shù),使用相對引用將公式所在單元格的上方單元格作為SUM函數(shù)的第2個參數(shù)。注意,SUM函數(shù)將邏輯值轉(zhuǎn)換成1或0,并且忽略文本值。 圖3:最終的輔助列公式使用SUM函數(shù)將AND函數(shù)的邏輯值與上方單元格中的值相加 單元格H6是一個輔助單元格。在該單元格中,輸入公式: =MAX(E8:E17) 得到匹配的記錄的條數(shù)。 使用輔助單元格,可以幫助減小公式的計算時間。 如下圖4所示,在單元格H12中輸入公式: =IF($G12>$H$6,'',INDEX(A$8:A$17,MATCH($G12,$E$8:$E$17,0))) 向左向下拖動復制。 圖4:在單元格H12中輸入的最終公式 當條件改變或者數(shù)據(jù)增加時,提取區(qū)域的數(shù)據(jù)會自動更新,如下圖5所示。 圖5:數(shù)據(jù)變化時,公式結(jié)果會自動更新 示例:使用輔助列,OR條件,VLOOKUP作為查找函數(shù) 如下圖6所示,使用了OR條件的輔助列并且放置在第1列,因此可以使用VLOOKUP函數(shù)。注意,VLOOKUP公式中的參數(shù)col_index_num使用了COLUMNS函數(shù),當公式向下復制時,其數(shù)值將依次遞增為2、3、4、5,等等。 圖6:使用輔助列,OR條件和VLOOKUP 注:當所有OR邏輯測試都指向同一列時,可以使用下列兩種公式構(gòu)造之一:ISNUMBER/MATCH函數(shù),或者OR函數(shù)。而ISNUMBER/MATCH函數(shù)組合運算更快且對于多個條件更容易構(gòu)造。 對于本示例,構(gòu)造輔助列的公式: =SUM(ISNUMBER(MATCH(D10,$B$3:$B$4,0)),A9) 或: =SUM(OR(D10=$B$3,D10=$B$4),N9) 示例:使用輔助列,AND和OR條件,使用INDEX和MATCH函數(shù)僅提取部分列的數(shù)據(jù) 如下圖7所示,使用AND和OR條件的輔助列,只從日期和商品數(shù)列中提取數(shù)據(jù)。 圖7:AND和OR條件,雙向查找從日期和商品數(shù)列中獲取數(shù)據(jù) 未完待續(xù)>>> 注:本文為電子書《精通Excel數(shù)組公式(學習筆記版)》中的一部分內(nèi)容節(jié)選。你可以到知識星球App的完美Excel社群下載這本電子書的完整中文版。 歡迎到知識星球:完美Excel社群,進行技術(shù)交流和提問,獲取更多電子資料。
|
|
來自: hercules028 > 《excel》