• <dfn id="gu0yc"><input id="gu0yc"></input></dfn>
    <menu id="gu0yc"><noscript id="gu0yc"></noscript></menu>
  • <delect id="gu0yc"></delect>
  • ?
    當(dāng)前位置:首頁 >> 新聞內(nèi)容 >> 學(xué)習(xí)交流

    excel常用函數(shù)整理

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

    常用函數(shù)整理


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

    但是,在Excel中因?yàn)榇嬖诳茖W(xué)計(jì)數(shù)的方式,當(dāng)一個(gè)單元格內(nèi)容文本數(shù)字超過15位時(shí),COUNTIF函數(shù)在處理時(shí)會(huì)將文本數(shù)值識(shí)別為數(shù)值格式進(jìn)行統(tǒng)計(jì),當(dāng)文本數(shù)字超過15位時(shí),Excel對(duì)超過15位的數(shù)值只能保留15位有效數(shù)字,后3位全部置為0,也就是只對(duì)前15位數(shù)字進(jìn)行比較,例如,對(duì)身份證號(hào)進(jìn)行計(jì)數(shù)時(shí),前15位相同的會(huì)被認(rèn)為是相同數(shù)據(jù),但可以用通配符"*"來處理,通過添加&"*",強(qiáng)制將數(shù)值識(shí)別為文本,通過使用=COUNTIF(A:A,A1&"*"),下拉填充,這樣就可以準(zhǔn)確統(tǒng)計(jì)出Excel中A列中的全部重復(fù)項(xiàng)。

    PS:=IF(COUNTIF(A:A,A2&"*")>1,"重復(fù)","") 標(biāo)注出A列中的全部重復(fù)項(xiàng)



    Sum SUMIFS Subtotal 求和函數(shù)
     =sum 求和


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

     

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

     

     



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

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


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


    rounmd  取2位小數(shù) 四舍五入 


    len()函數(shù)與lenb()函數(shù)

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



    date  時(shí)間函數(shù)
    ⊙、月度時(shí)間進(jìn)度函數(shù) =TEXT(DAY(TODAY())/DAY(EOMONTH(TODAY(),0)),"0.00%") 

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


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

    簡單說明:

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

    Lookup_value為需要在數(shù)據(jù)表第一列中進(jìn)行查找的數(shù)值。Lookup_value 可以為數(shù)值、引用或文本字符串。
    當(dāng)vlookup函數(shù)第一參數(shù)省略查找值時(shí),表示用0查找。

    Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。使用對(duì)區(qū)域或區(qū)域名稱的引用。

    col_index_num為table_array 中查找數(shù)據(jù)的數(shù)據(jù)列序號(hào)。col_index_num 為 1 時(shí),返回 table_array 第一列的數(shù)值,
    col_index_num 為 2 時(shí),返回 table_array 第二列的數(shù)值,以此類推。
    如果 col_index_num 小于1,函數(shù) VLOOKUP 返回錯(cuò)誤值 #VALUE!;
    如果 col_index_num 大于 table_array 的列數(shù),函數(shù) VLOOKUP 返回錯(cuò)誤值#REF!。

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


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

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


    AVERAGEIF函數(shù)

    AVERAGEIF函數(shù)主要用來返回某個(gè)區(qū)域內(nèi)滿足給定條件的所有單元格的平均值。

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

    身份證號(hào)碼相關(guān)函數(shù)計(jì)算:

    1.判斷號(hào)碼是否正確:

    =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),"正確","錯(cuò)誤")

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

    2.提取生日

    提取出生日期主要是將號(hào)碼中的7-14位提取出來,在這里我們利用mid函數(shù)將其提取出來,然后再利用text將提取出來的一串?dāng)?shù)字設(shè)置為日期的顯示格式。

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

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

    3.計(jì)算性別

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

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

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

    身份證計(jì)算函數(shù)模板:

    身份證計(jì)算函數(shù).xlsx





    上一篇:沒有了!
    下一篇:開票軟件 航天金稅 百旺金賦 稅務(wù)UK 機(jī)動(dòng)車發(fā)票軟件
    熱門服務(wù)和內(nèi)容
    ?

    業(yè)務(wù)咨詢

  • 官方微信

    官方微信

  • 商務(wù)合作

  • 官方微信

    官方微信

  • 廣元裕豐 公司注冊 代理記賬 納稅申報(bào) 商標(biāo)注冊 資質(zhì)辦理 Copyright ? 2024 廣元裕豐財(cái)稅 All Rights Reserved. 蜀ICP備2022005873號(hào) 管理中心 財(cái)政部 國家稅務(wù)總局 四川政務(wù)服務(wù)網(wǎng) 四川電子稅務(wù)局 自然人電子稅務(wù)局稅乎網(wǎng) 國家知識(shí)產(chǎn)權(quán)局天眼查檸檬云小蝶云易代賬0839 3600200 【工商銀行郵儲(chǔ)銀行廣元貴商村鎮(zhèn)銀行中國銀行】
    電話咨詢:08393600200
    在線客服咨詢
    在线免费av观看一区三,中文字幕乱码亚洲∧ⅴ日本,国产 欧美 自拍 偷怕 日韩 亚洲,亚洲私人无码内射免费观看 亚洲熟妇视频在线观看 一区二区日韩激情综合网
  • <dfn id="gu0yc"><input id="gu0yc"></input></dfn>
    <menu id="gu0yc"><noscript id="gu0yc"></noscript></menu>
  • <delect id="gu0yc"></delect>