彩票直通車_彩票直通車官網-彩票直通車專家預測

當前位置:主頁 > 電腦教程 > 工具軟件 > 辦公軟件 > Excel教程 > excel函數 > >

Excel函數篩選與排序使用教程

來源::未知 | 作者:admin | 本文已影響
  Excel本身具有很方便的排序與篩選功能,下拉“數據”菜單即可選擇排序或篩選對數據清單進行排序或篩選。但也有不足,首先無論排序或篩選都改變了原清單的原貌,特別是清單的數據從其它工作表鏈接來而源數據發生變化時,或清單錄入新記錄時必須從新進行排序或篩選。其次還有局限,例如排序只能最多對三個關鍵字(三列數據)排序,篩選對同一列數據可用“與”、或“或”條件篩選,但對不同列數據只能用“與”條件篩選。

  例如對某張職工花名冊工作簿,要求篩選出年齡大于25歲且小于50歲或年齡大于50歲或小于25歲都是可行的,如同時要求性別是男的或女的也是可行的。但要求篩選出女的年齡在22歲到45歲,男的年齡在25歲到50歲時Execl本身具有的篩選功能則無能為力了。再者排序與篩選不能結合使用,即不能在排序時根據條件篩選出來的記錄進行排序。例如有一張職工資料清單,其中有的職工已經退休,對在職職工的年齡進行排序時無法剔除已退休職工的數據。

  本文試圖用Excel函數來解決上述問題。

  一、用函數實現篩選

  題目:如有一張職工名冊表,A2:F501,共6列500行3000個單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為性別、D1 為年齡、E1為學歷、F1職稱。現要求對職工的性別、年齡、學歷、職稱進行交錯篩選,例如要求在同一張表上篩選出1、女的年齡在22歲到45歲,男的年齡在25歲到50歲,2、女博士,3、男博士后。

  方法:第一步在G2單元格輸入公式”=IF(OR(AND(C2="女",D2>=22,D2<=45),AND(C2="男",

  D2>=25,D2<=50)),ROW(A1),0)“,在H2單元格輸入公式”=IF(AND(C2="女",E2="博士"),

  ROW(B1),0)“,在I2單元格輸入公式”=IF(AND(C2="男",E2="博士后"),ROW(B1),0)“。在J2單元格輸入公式“=IF(K$2=1,LARGE(G:G,ROW(A1)),IF(K$2=2,LARGE(H:H,ROW(A1)),

  IF(K$2=3,LARGE(I:I,ROW(A1)),0)))”然后用上述提到的方法向下拖放。G、H、I列的公式的含義就是凡符合篩選條件的行記錄下行號否則為零,J列的公式的含義根據K2的數值選擇G、H、I中的一列進行排序并把不合條件的行除去。

  第二步在K1單元格輸文字”篩選選擇”,A1到F1表頭復制到L1到Q1,在L2單元格輸入

  公式“=IF($J2=0,0,INDEX($A$2:$F$501,$J2,COLUMN(A$1)))”,然后向右拖放到Q2,再向下拖放。INDEX函數的含義上文已說明。

  第三步在P1單元格輸入1或2或3便可實現上述三種篩選。

  二、用函數實現排序

  題目:如有一張工資表,A2:F501,共6列500行3000個單元格。表頭A1為姓名代碼(1至500)、B1為姓名、C1為津貼、D1為獎金、E1為工資、F1收入合計。現要求對職工收入從多到少排序,且在職工總收入相同時再按工資從多到少排序,在職工總收入和工資相同時再按獎金從多到少排序,在職工職工總收入和工資、獎金相同時再按津貼從多到少排序。

  方法:G1單元格填入公式

  “=if(F2=0,10^100,INT(CONCATENATE(999-f2,999-e2,999-d2,999-c2)))”,

  CONCATENATE 是一個拼合函數,可以把30個以下的單元的數據拼合成一個數據,這些被拼合的數據之間用逗號分開。用f2、e2等被拼合的數據用999來減,是為了使它們位數相同。(假定任何一個職工的總收入少于899元)。被拼合成的函數是文本函數,CONCATENATE與INT函數套用是為了使文本轉換為數字。最外層的if函數是排序時用來剔除不進行排序的記錄,在本例中指收入為零的記錄。(在上文提到的職工年齡排序,則公式改為“if(f2="退休", 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列中最小的數隨著向下拖放依次為第2、第3、..小的數,MATCH(SMALL(G:G,ROW(A1)),G:G,0)即為G列各行的數據中最小、第2、第3小等的數據在第幾行。

  第四步把A1至F1單元格的表頭復制到I1至N1單元格,在I2單元格輸入公式“=INDEX($A$2:$F$501,$H2, COLUMN(A$1))”INDEX函數是一個引用函數,即把$A$2:$F$501單元格列陣第$H2行第COLUMN(A$1)列的數據放入I2單元格。然后把I2單元格的公式拖放到N2單元格,點擊N2單元格后向N2單元格右下方移動鼠標見到黑十時雙擊鼠標就完成了I2到N501單元格的填充到此全部完成。

  以上敘述看似繁雜實際非常簡單,只要把A1至F1的表頭復制到I1至N1單元格,再分別在G1、H2、I2單元格輸入公式然后向下拖放,即使對EXCEL應用不熟練的同志一分鍾內便能完成。

  對上述程序稍作變化還可得到更多用度。上面例子數據是從大到小排列的,如H列的函數中的SMALL改為LARGE,上面例子數據就從小到大排列了。如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上面例子數據是從大到小排列的,O1單元格輸入1以外的數上面例子數據就從小到大排列了。

  如在H列前插入若干列,如插入一列,則現在的H列輸入類似G列的公式,例如“=if(F2=0,10^100,d2)”,現在的I列的公式改為“=IF(P1=1,MATCH(SMALL(G:G,ROW(A1)),G:G,0),MATCH(SMALL(H:H,ROW(A1)),H:H,0)))”即在P單元格輸入1以外的值就實現了按獎金大小排序.這樣只要通過改變P1(原來的O1單元格)單元格內容的改變就能立即得到按不同要求的排序。

分享到: 更多
彩票直通車_彩票直通車官網-彩票直通車專家預測

熱榜閱讀TOP

本周TOP10

Excel數據查找與引用相關的函數教

Excel數據查找與引用相關的函數教

★Lookup數與行列比 Lookup的工作職責是什么呢?用一個數與一行或一列數據依次進行比較,發現匹配的數值...

网球世界排名 下载天天重庆麻将 黑龙江6加1特等奖多少钱 天天手机捕鱼下分版 股票各种线详解图 欧冠联赛积分 3d开奖结果175 平码组49选6计算公式 深圳风采开奖与走势图 安徽11选5历史开奖结果 为什么股票只跌不涨 微乐吉林麻将下载安装 北京11选5一小时开几期 海南麻将 股票涨跌概率 山东十一运夺金11 湖南幸运赛车开奖记录