• <option id="c8k46"></option>
  • <blockquote id="c8k46"></blockquote>
    ?
    當前位置:首頁 >> 新聞內容 >> 學習交流

    excel常用函數整理

    錄入編輯:裕豐財稅 | 發布時間:2022-05-11
    常用函數整理COUNTIF 重復函數 假設數據在A列 B1輸入 =COUNTIF(A:A,A1) 公式下拉復制。*B列顯示各個數字在A列出現的次數。若需求某一特定的數據(如:123)的出現頻率可在任意單元格數據=COUNTIF(A:A,123)Sum SUMIFS Subtotal 求和函數=s···

    常用函數整理


    COUNTIF 重復函數
        假設數據在A列  B1輸入 =COUNTIF(A:A,A1)  公式下拉復制。*B列顯示各個數字在A列出現的次數。
    若需求某一特定的數據(如:123)的出現頻率可在任意單元格數據=COUNTIF(A:A,123)

    但是,在Excel中因為存在科學計數的方式,當一個單元格內容文本數字超過15位時,COUNTIF函數在處理時會將文本數值識別為數值格式進行統計,當文本數字超過15位時,Excel對超過15位的數值只能保留15位有效數字,后3位全部置為0,也就是只對前15位數字進行比較,例如,對身份證號進行計數時,前15位相同的會被認為是相同數據,但可以用通配符"*"來處理,通過添加&"*",強制將數值識別為文本,通過使用=COUNTIF(A:A,A1&"*"),下拉填充,這樣就可以準確統計出Excel中A列中的全部重復項。

    PS:=IF(COUNTIF(A:A,A2&"*")>1,"重復","") 標注出A列中的全部重復項



    Sum SUMIFS Subtotal 求和函數
     =sum 求和


    =SUMIFS($M$4:$M$411,$A$4:$A$411,$B$415,$J$4:$J$411,C$414) (五段字符分別為:待求和數值,條件1區域,條件1,條件2區域,條件2) 

     

    舉例:=SUMIFS(D3:D17,B3:B17,H4,C3:C17,I3)

     

     



    =round(單元格,num)單元格是你之前的函數或單元格,num是你想要保留的幾位小數位(0,1,2,.....

    =subtotal(9,h1:h100)
    9可換為1-11不同的數字表示不同的意思
    為1到11之間的自然數,用來指定分類匯總計算使用的函數
    1 、1 AVERAGE(算術平均值)
    2、 2 COUNT(數值個數)
    3、 3 COUNTA(非真空單元格數量)
    4 、4 MAX(最大值)
    5 、5 MIN(最小值)
    6 、6 PRODUCT(括號內所有數據的乘積)
    7 、7 STDEV(估算樣本的標準偏差)
    8 、8 STDEVP(返回整個樣本總體的標準偏差)
    9、 9 SUM(求和)
    10 、10 VAR(計算基于給定樣本的方差)
    11 、11 VARP(計算基于整個樣本總體的方差) 


    mid  rifht 取數函數
      第幾位到第幾位連續取數:例:19119801231在A1單元格,取第5位起4位數1980。公式:=mid(a1,5,4) ;取最后4位1231:=rifht(a1,4)


    rounmd  取2位小數 四舍五入 


    len()函數與lenb()函數

    區別是:LEN()函數返回文本字符串中的字符數(漢字為單數);LENB()函數返回文本字符串中用于代表字符的字節數(漢字為雙數)
    此函數可用于漢字數字在同一單元格的取數
    列1:A1單元格為: 廣元0839 將0839分離出來:=right(A1,2*LEN(A1)-LENB(A1)) 或=mid(A1,3,2*LEN(A1)-LENB(A1))
     這里  2*len()-lenb  的意思是 先將所有字符(漢字+數字)*2-漢字*2-數字*1=1*數字 的個數
     同理 列2:A1單元格為: 0839廣元 將 廣元 分離出來:=right(A1,LENB(A1)-LEN(A1))



    date  時間函數
    ⊙、月度時間進度函數 =TEXT(DAY(TODAY())/DAY(EOMONTH(TODAY(),0)),"0.00%") 

    ⊙、20160101單元格格式自定義為日期格式:年月日 ####-##-##
      方法一:1.利用MID函數分解為三個數值A1 A2 A3 分別為 2016 01 01  
      2利用DATE函數DATE(A1,A2,A3)組合三個數據得到日期 2016-01-01
    方法二:一步到位:A1單元格為 20160101 轉換成日期A2單元格 =date(mid(a1,1,4),mid(a1,5,2),mid(a1,7,2)) 并設置單元格格式為日期 語法:DATE(year,month,day)


    VLOOKUP 查找函數
      語法:
    VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
    參數

    簡單說明:

    lookup_value要查找的值數值、引用或文本字符串
    table_array要查找的區域數據表區域
    col_index_num返回數據在查找區域的第幾列數正整數
    range_lookup模糊匹配/精確匹配TRUE(或不填)/FALSE

    Lookup_value為需要在數據表第一列中進行查找的數值。Lookup_value 可以為數值、引用或文本字符串。
    當vlookup函數第一參數省略查找值時,表示用0查找。

    Table_array為需要在其中查找數據的數據表。使用對區域或區域名稱的引用。

    col_index_num為table_array 中查找數據的數據列序號。col_index_num 為 1 時,返回 table_array 第一列的數值,
    col_index_num 為 2 時,返回 table_array 第二列的數值,以此類推。
    如果 col_index_num 小于1,函數 VLOOKUP 返回錯誤值 #VALUE!;
    如果 col_index_num 大于 table_array 的列數,函數 VLOOKUP 返回錯誤值#REF!。

    Range_lookup為一邏輯值,指明函數 VLOOKUP 查找時是精確匹配,還是近似匹配。
    如果為false或0 ,則返回精確匹配,如果找不到,則返回錯誤值 #N/A。
    如果 range_lookup 為TRUE或1,函數 VLOOKUP 將查找近似匹配值,也就是說,
    如果找不到精確匹配值,則返回小于 lookup_value 的最大數值。如果range_lookup 省略,則默認為近似匹配


    IFERROR 函數
      IFERROR函數用于判斷表達式的計算結果是否有效,
    當有效時會返回表達式的值,而當表達式計算結果無效時將返回事先設定的字符串或其它內容。
    value_if_error 必需。公式的計算結果為錯誤時要返回的值。計算得到的錯誤類型有:#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!。
    =IFERROR(A1/B1,"0") =IFERROR(計算A1除以B1的正確的值,若計算錯誤返回值 0)
    和其他公司套用=IFERROR(VLOOKUP(AM208,Sheet2!$E:$F,2,FALSE),"0")

    IF  函數
      IF  函數 條件函數 判斷真假
    (1)IF(A2<=100,"Withinbudget","Overbudget"), 說明:如果上面的數字小于等于100,則公式將顯示“Withinbudget”。否則,公式顯示“Overbudget”。 結果:Withinbudget。
    (2)IF(A2=100,SUM(B2:B5),"") 說明:如果上面數字為100,則計算單元格區域B2:B5之和,否則返回空文本。 結果:" " =IF((N4-5000)<=0,0,IF((N4-5000)>0,(N4-5000)*0.03)) N4為扣稅前工資,若N4小于等于5000 扣稅0  若N4大于5000  扣稅金額為 兩者相差的數*0.03
    (3) =IF(COUNTIF(AU2,"*B*"),"旭駿","旭日")  如果AU2 單元格數值包含B 那么返回結果為旭駿   否則為旭日   pre>SUBSTITUTE 替換函數  SUBSTITUTE 替換函數
    (1)=substitute(text,old_text,new_text,[instance_num] =substitute(需要替換的文本,舊文本,新文本,第N個舊文本) 參數Instance_num ——為一數值,用來指定以 new_text (新文本)替換第幾次出現的 old_text(舊文本)。 參數Instance_num 可省略,這表示用 new_text(新文本)替換掉所有的old_text(舊文本)。
    列:A1=13208390839影藏中間號碼替換為  *   公式=SUBSTITUTE(A8,MID(A8,4,4),"****",1)=132****0839 因為原文本中有2個0839我替換中間的 也就是第一次出現0839 所以公式最后一個字符寫1 入需要替換后面的也就是第二個則寫 2


    AVERAGEIF函數

    AVERAGEIF函數主要用來返回某個區域內滿足給定條件的所有單元格的平均值。

    它的基本語法:=AVERAGEIF(條件區域,條件,求平均值的區域)

    身份證號碼相關函數計算:

    1.判斷號碼是否正確:

    =IF(IF(RIGHT(A2,1)="X","X",--RIGHT(A2,1))=VLOOKUP(MOD(SUMPRODUCT(--MID(A2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17},1),{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11),{0,1;1,0;2,"X";3,9;4,8;5,7;6,6;7,5;8,4;9,3;10,2},2,0),"正確","錯誤")

    使用方法:粘貼公式,按下快捷鍵Ctrl+Shift+回車填充公式。將公式中的三個A2替換為你表格中的號碼位置即可,人事行政的必備公式

    2.提取生日

    提取出生日期主要是將號碼中的7-14位提取出來,在這里我們利用mid函數將其提取出來,然后再利用text將提取出來的一串數字設置為日期的顯示格式。

    公式為:=TEXT(MID(A3,7,8),"0000-00-00")

    使用方法:復制粘貼公式,直接更改A3為你表格中的單元格位置即可

    3.計算性別

    計算性別主要是判斷第17位的奇偶性,如果是奇數則表示為男性,如果是偶數則表示為女性。在這里我們用mid函數將第17位提取出來,利用iseven判斷是否為偶數,最后利用IF函數輸出性別。

    公式:=IF(ISEVEN(MID(A3,17,1)),"女","男")

    使用方法:粘貼公式后,更改A3的位置即可

    身份證計算函數模板:

    身份證計算函數.xlsx





    上一篇:沒有了!
    下一篇:開票軟件 航天金稅 百旺金賦 稅務UK 機動車發票軟件
    熱門服務和內容
    ?

    業務咨詢

  • 官方微信

    官方微信

  • 商務合作

  • 官方微信

    官方微信

  • 廣元裕豐 公司注冊 代理記賬 納稅申報 商標注冊 資質辦理 Copyright ? 2023 廣元裕豐財稅 All Rights Reserved. 蜀ICP備2022005873號 管理中心 財政部 國家稅務總局 四川政務服務網 四川電子稅務局 自然人電子稅務局稅乎網 國家知識產權局天眼查檸檬云小蝶云易代賬0839 3600200 【工商銀行郵儲銀行廣元貴商村鎮銀行中國銀行】
    電話咨詢:08393600200
    在線客服咨詢
    国产精品无码视 亚洲区手机在线中文无码播放 亚洲人妻中文日本 亚洲欧洲丝袜动漫另类视一二三区