Excel用函數公式篩選數據的方法圖解教程

Excel用函數公式篩選數據的方法圖解教程

本文通過實例剖析Excel中經典的篩選數據的函數公式的用法。

Excel中關于篩選數據最簡單的方法，是使用數據透視表完成。如果對透視表不熟悉，可以使用Excel函數寫公式來篩選。

借論壇今天的每日一題題目：《篩選組中最高工資的人的各項資料》來剖析一下經典的篩選公式的用法。

如下圖，題目說明：A1：F11為數據源。篩選各組中工資最高的人的各項資料(如果最高工資重復，請按順序分別顯示出來），詳見附件。

A18輸入公式，按下ctrl+shift+enter組合鍵完成數組公式的輸入，然后右拉下拉復制公式。=INDEX($B:$F,SMALL(IF(($F$2:$F$11=MAX(($D$2:$D$11=$A$16)*$F$2:$F$11))*($D$2:$D$11=$A$16),ROW($2:$11),4^8),ROW(A1)),COLUMN(A1))&""

解題思路：確定兩個條件：組數：D2:D11=$A16；最高工資：F2:F11=MAX((D2:D11=A16)*F2:F11))　　公式構成：index（區域，行，列）&""——index($B:$F，行部分，COLUMN(A1)) &""。

用index+small函數構造出來的篩選公式，經典在于獲取出相應的行。剖析公式一般從內到位，用F9鍵逐一查看運算結果。

第一：small部分，獲取行號，剖析如下：

1.MAX((D2:D11=A16)*F2:F11))*(D2:D11=A16)　　D2:D11=A16，判斷D列的組別和A16組別是否相等，得到FALSE和TRUE構成的邏輯數組。　　(D2:D11=A16)*F2:F11，計算結果將符合條件的true對應的數字取出來：　　{0;0;0;9000;6000;0;0;0;0;0}　　然后用max(數字)，取出最大值9000。

2．IF部分：　　IF（條件，是，否）——if（F2:F11=9000，ROW($2:$11),4^8）　　在F2：F11區域中查找等于第一部分max計算的最大值，如果等于最大值，就返回對應的行號（ROW($2:$11)），否則就返回4^8。4^8：是4的8次方，結果等于65536 即2003中最大的行號。

3.small部分：　　Small(最大行號和符合條件的行號，row(A1)　　用SMALL在65536和對應的一個行號中取最小值，得到的就是符合條件的行號。　　SMALL({65536;65536;65536;5;65536;65536;65536;65536;65536;65536},ROW(A1))，結果是5。

第二：index(區域，行，列)　　Index($B:$F，5，COLUMN(A1))，返回B:F列這個區域的第五行第一列，對應的單元格就是B5單元格。

第三：為了美觀，最后添加&""　　上面index部分就可以完成篩選數據，但在下拉右拉復制公式時，超過結果以外的單元格會顯示“0”，如果想去掉0，直接用空白單元格，不顯示0，就可以在公式最后添加&""。

&""是什么意思呢? &是個文本粘貼符，后面的""是表示空白文本，就等于在后面強制性的把(0)粘貼成了空白文本。

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

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