在线观看日韩一区,欧美日韩国产专区,精品尤物写真福利网址,欧美性第一页,国产一级插插插,欧美久久网,日本大蕉香蕉大视频在线观看

?
當(dāng)前位置:首頁 >> 新聞內(nèi)容 >> 學(xué)習(xí)交流

excel常用函數(shù)整理

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

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

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



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ù),用來指定分類匯總計算使用的函數(shù)
1 、1 AVERAGE(算術(shù)平均值)
2、 2 COUNT(數(shù)值個數(shù))
3、 3 COUNTA(非真空單元格數(shù)量)
4 、4 MAX(最大值)
5 、5 MIN(最小值)
6 、6 PRODUCT(括號內(nèi)所有數(shù)據(jù)的乘積)
7 、7 STDEV(估算樣本的標(biāo)準(zhǔn)偏差)
8 、8 STDEVP(返回整個樣本總體的標(biāo)準(zhǔn)偏差)
9、 9 SUM(求和)
10 、10 VAR(計算基于給定樣本的方差)
11 、11 VARP(計算基于整個樣本總體的方差) 


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ù)字 的個數(shù)
 同理 列2:A1單元格為: 0839廣元 將 廣元 分離出來:=right(A1,LENB(A1)-LEN(A1))



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

⊙、20160101單元格格式自定義為日期格式:年月日 ####-##-##
  方法一:1.利用MID函數(shù)分解為三個數(shù)值A(chǔ)1 A2 A3 分別為 2016 01 01  
  2利用DATE函數(shù)DATE(A1,A2,A3)組合三個數(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返回數(shù)據(jù)在查找區(qū)域的第幾列數(shù)正整數(shù)
range_lookup模糊匹配/精確匹配TRUE(或不填)/FALSE

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

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

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

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


IFERROR 函數(shù)
  IFERROR函數(shù)用于判斷表達式的計算結(jié)果是否有效,
當(dāng)有效時會返回表達式的值,而當(dāng)表達式計算結(jié)果無效時將返回事先設(shè)定的字符串或其它內(nèi)容。
value_if_error 必需。公式的計算結(jié)果為錯誤時要返回的值。計算得到的錯誤類型有:#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  函數(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,則計算單元格區(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個舊文本) 參數(shù)Instance_num ——為一數(shù)值,用來指定以 new_text (新文本)替換第幾次出現(xiàn)的 old_text(舊文本)。 參數(shù)Instance_num 可省略,這表示用 new_text(新文本)替換掉所有的old_text(舊文本)。
列:A1=13208390839影藏中間號碼替換為  *   公式=SUBSTITUTE(A8,MID(A8,4,4),"****",1)=132****0839 因為原文本中有2個0839我替換中間的 也就是第一次出現(xiàn)0839 所以公式最后一個字符寫1 入需要替換后面的也就是第二個則寫 2


AVERAGEIF函數(shù)

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

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

身份證號碼相關(guān)函數(shù)計算:

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函數(shù)將其提取出來,然后再利用text將提取出來的一串?dāng)?shù)字設(shè)置為日期的顯示格式。

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

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

3.計算性別

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

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

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

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

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





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

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

  • 官方微信

    官方微信

  • 商務(wù)合作

  • 官方微信

    官方微信

  • 廣元裕豐 公司注冊 代理記賬 納稅申報 商標(biāo)注冊 資質(zhì)辦理 Copyright ? 2024 廣元裕豐財稅 All Rights Reserved. 蜀ICP備2022005873號 管理中心 財政部 國家稅務(wù)總局 四川政務(wù)服務(wù)網(wǎng) 四川電子稅務(wù)局 自然人電子稅務(wù)局稅乎網(wǎng) 國家知識產(chǎn)權(quán)局天眼查檸檬云小蝶云易代賬0839 3600200 【工商銀行郵儲銀行廣元貴商村鎮(zhèn)銀行中國銀行】
    電話咨詢:08393600200
    在線客服咨詢