一区二区三区日韩精品-日韩经典一区二区三区-五月激情综合丁香婷婷-欧美精品中文字幕专区

分享

精通數(shù)組公式16:基于條件提取數(shù)據(jù)

 hercules028 2021-02-15

excelperfect

Excel中,基于ANDOR條件從數(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è)有3AND條件來決定要提取的記錄,如下圖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)換成10,并且忽略文本值。

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ù)值將依次遞增為23、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)

示例:使用輔助列,ANDOR條件,使用INDEXMATCH函數(shù)僅提取部分列的數(shù)據(jù)

如下圖7所示,使用ANDOR條件的輔助列,只從日期和商品數(shù)列中提取數(shù)據(jù)。

7ANDOR條件,雙向查找從日期和商品數(shù)列中獲取數(shù)據(jù)

未完待續(xù)>>>

注:本文為電子書《精通Excel數(shù)組公式(學習筆記版)》中的一部分內(nèi)容節(jié)選。你可以到知識星球App的完美Excel社群下載這本電子書的完整中文版。

歡迎在下面留言,完善本文內(nèi)容,讓更多的人學到更完美的知識。

歡迎到知識星球:完美Excel社群,進行技術(shù)交流和提問,獲取更多電子資料。

    本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間,所有內(nèi)容均由用戶發(fā)布,不代表本站觀點。請注意甄別內(nèi)容中的聯(lián)系方式、誘導購買等信息,謹防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容,請點擊一鍵舉報。
    轉(zhuǎn)藏 分享 獻花(0

    0條評論

    發(fā)表

    請遵守用戶 評論公約

    類似文章 更多

    精品熟女少妇av免费久久野外| 国产不卡免费高清视频| 国产精品日韩精品最新| 青青操视频在线观看国产| 日韩特级黄片免费在线观看| 亚洲国产四季欧美一区| 国产三级欧美三级日韩三级| 在线观看国产午夜福利| 99久久精品国产日本| 日本久久精品在线观看| 亚洲中文字幕乱码亚洲| 国产精品一区二区视频成人| 亚洲欧美日韩熟女第一页| 免费特黄一级一区二区三区| 亚洲欧洲在线一区二区三区| 久久黄片免费播放大全 | 开心激情网 激情五月天| 国产一区二区三区成人精品| 在线免费观看黄色美女| 国产免费操美女逼视频| 日韩和欧美的一区二区三区| 国产成人精品久久二区二区| 国产精品丝袜一二三区| 欧美午夜色视频国产精品| 日本深夜福利在线播放| 欧洲偷拍视频中文字幕| 日本高清不卡在线一区| 国产毛片对白精品看片| 日韩特级黄片免费观看| 一区中文字幕人妻少妇| 午夜精品成年人免费视频| 亚洲av日韩av高潮无打码| 国产在线视频好看不卡| 久久成人国产欧美精品一区二区| 欧美日韩国产欧美日韩| 亚洲中文字幕视频一区二区| 国产精品午夜小视频观看| 亚洲一区二区精品国产av| 色涩一区二区三区四区| 91爽人人爽人人插人人爽| 国产精品亚洲二区三区|