本文為大家介紹實例教你學excel:函數(shù)排序與篩選數(shù)據(jù)(excel函數(shù)排序和篩選怎么操作)，下面和小編一起看看詳細內(nèi)容吧。
execl本身就有非常方便的排序和過濾功能。下拉“數(shù)據(jù)”菜單選擇排序或過濾，對數(shù)據(jù)列表進行排序或過濾。但也存在不足。首先，無論是排序還是過濾都會改變原來列表的本來面目，尤其是當列表中的數(shù)據(jù)是從其他工作表鏈接過來的，源數(shù)據(jù)發(fā)生變化時，或者當列表中輸入了新的記錄時，都必須進行排序或再次過濾。其次，有局限性。比如排序最多只能對三個關(guān)鍵字（三列數(shù)據(jù)）進行排序。過濾可以用“and”，或者“or”條件過濾同一列的數(shù)據(jù)，但是對于不同列的數(shù)據(jù)，只能用“and”條件過濾。
比如一個員工花名冊工作簿，過濾掉年齡大于25歲小于50歲或者年齡大于50歲小于25歲是可行的，同時要求性別為男或女也是可行的.但execl本身具有的篩選功能在要求篩選出22-45歲的女性年齡和25-50歲的男性年齡時卻無能為力。此外，排序和過濾不能結(jié)合使用，即排序時不能按條件對記錄進行排序。比如有一個員工數(shù)據(jù)列表，其中有一些已經(jīng)退休，在職員工年齡排序時不能排除退休員工的數(shù)據(jù)。
本文試圖利用execl的功能來解決上述問題。
1.用函數(shù)排序
題目：如果有一張工資表，a2:f501，共有6列，500行，3000個單元格。表頭a1是姓名代碼（1到500），b1是姓名，c1是津貼，d1是獎金，e1是工資，f1是總收入。現(xiàn)在要求將員工收入從多到少排序，當員工總收入相同時，再按工資從多到少排序；如果工資和獎金相同，則將津貼從多到少排序。
方法：在g1單元格填寫公式
“=if(f2=0, 10^100, int(連接(999-f2, 999-e2, 999-d2, 999-c2)))”,
concatenate是一個展平函數(shù)，可以將小于30個單元的數(shù)據(jù)合并為一個數(shù)據(jù)，這些合并后的數(shù)據(jù)之間用逗號分隔。將f2、e2等合并后的數(shù)據(jù)減去999，目的是使它們的位數(shù)相同。 （假設(shè)任何一名員工的總收入低于899元）。要組合的函數(shù)是文本函數(shù)，應(yīng)用concatenate和int函數(shù)將文本轉(zhuǎn)換為數(shù)字。最外層的if函數(shù)用來在排序的時候排除不需要排序的記錄，這里指的是收入為零的記錄。 （上面提到的員工年齡排序，公式改為“if(f2='retired', 10^100,)”，即排除退休員工。）
第二步，將g1單元格中的公式拖放到g500單元格（最簡單的方法是點擊g1單元格，將鼠標移到g1單元格右下方，看到黑色十時雙擊鼠標完成從g1 到g500 的填充）。
第三步，在h2單元格中填寫公式“=match(small(g:g, row(a1)), g:g, 0)”，同第二步一樣拖到h501單元格。這個公式實際上是將三個列公式合并為一個列公式。 row(a1)表示a1的行號為1，然后向下拖拽到2,3,4.small(g:g,row(a1))是g列中最小的數(shù)。隨著拖拽往下，變成第2，第3，小數(shù)，match(small(g:g, row(a1)), g:g, 0) 就是g列中最小的行，第二小的，第三小的等等. 數(shù)據(jù)中的數(shù)據(jù)。
第四步，將a1到f1單元格的表頭復(fù)制到i1到n1單元格，在i2單元格中輸入公式“=index($a$2:$f$501, $h2, column(a$1))”。 index函數(shù)是一個引用函數(shù)，即將元胞數(shù)組$a$2:$f$501的$h2行column(a$1)列的數(shù)據(jù)放入i2單元格中。然后將i2單元格中的公式拖放到n2單元格中，點擊n2單元格，將鼠標移至n2單元格的右下方。當看到黑色的十時，雙擊鼠標完成i2到n501單元格的填充。
上面的描述看似復(fù)雜，其實很簡單。只需將a1到f1的表頭復(fù)制到i1到n1單元格，然后分別在g1、h2、i2單元格中輸入公式然后拖拽，即使不熟悉excel的同志們也能在一分鐘內(nèi)搞定。
上述過程的輕微變化可以產(chǎn)生更多。上面的例子數(shù)據(jù)是從大到小排列的，比如把h列函數(shù)中的small改成large，上面的例子數(shù)據(jù)是從小到大排列的。例如，將單元格h2 中的公式更改為“=if(o1=1, match(small(g:g, row(a1)), g:g, 0), match(large(g: g, row(a1)), g:g , 0) )”并將單元格h2 中的公式向下拖放。這樣，在o1單元格輸入1，上面的例子數(shù)據(jù)從大到小排列，o1單元格輸入1以外的數(shù)字，上面的例子數(shù)據(jù)從小到大排列。
如果在h列前插入幾列，比如插入一列，則在h列輸入類似g列的公式，如“=if(f2=0, 10^100, d2)”，把i列的公式改一下在單元格p 中輸入“=if(p1=1, match(small(g:g, row(a1)), g:g, 0), match(small(h:h, row(a1)), h:h, 0)))” 1以外的值實現(xiàn)按獎金大小排序。這樣，只要改變p1單元格（原來的o1單元格）的內(nèi)容，就可以立即得到按不同要求排序的結(jié)果。
好了，實例教你學excel:函數(shù)排序與篩選數(shù)據(jù)(excel函數(shù)排序和篩選怎么操作)的介紹到這里就結(jié)束了，想知道更多相關(guān)資料可以收藏我們的網(wǎng)站。