常用函數(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ù)字超過(guò)15位時(shí)，COUNTIF函數(shù)在處理時(shí)會(huì)將文本數(shù)值識(shí)別為數(shù)值格式進(jìn)行統(tǒng)計(jì)，當(dāng)文本數(shù)字超過(guò)15位時(shí)，Excel對(duì)超過(guò)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ù)，但可以用通配符"*"來(lái)處理，通過(guò)添加&"*"，強(qiáng)制將數(shù)值識(shí)別為文本，通過(guò)使用=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ù)，用來(lái)指定分類(lèi)匯總計(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分離出來(lái)：=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廣元 將 廣元 分離出來(lái)：=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è)置單元格格式為日期 語(yǔ)法:DATE(year,month,day)

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

簡(jiǎn)單說(shuō)明:

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ū)域名稱(chēng)的引用。

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ù)值，以此類(lèi)推。
如果 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 為T(mén)RUE或1，函數(shù) VLOOKUP 將查找近似匹配值，也就是說(shuō)，
如果找不到精確匹配值，則返回小于 lookup_value 的最大數(shù)值。如果range_lookup 省略，則默認(rèn)為近似匹配

IFERROR 函數(shù)
  IFERROR函數(shù)用于判斷表達(dá)式的計(jì)算結(jié)果是否有效，
當(dāng)有效時(shí)會(huì)返回表達(dá)式的值，而當(dāng)表達(dá)式計(jì)算結(jié)果無(wú)效時(shí)將返回事先設(shè)定的字符串或其它內(nèi)容。
value_if_error 必需。公式的計(jì)算結(jié)果為錯(cuò)誤時(shí)要返回的值。計(jì)算得到的錯(cuò)誤類(lèi)型有：#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")， 說(shuō)明：如果上面的數(shù)字小于等于100，則公式將顯示“Withinbudget”。否則，公式顯示“Overbudget”。 結(jié)果：Withinbudget。
（2）IF(A2=100,SUM(B2:B5),"") 說(shuō)明：如果上面數(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ù)值，用來(lái)指定以 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è)字符寫(xiě)1 入需要替換后面的也就是第二個(gè)則寫(xiě) 2

AVERAGEIF函數(shù)

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

它的基本語(yǔ)法：=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+回車(chē)填充公式。將公式中的三個(gè)A2替換為你表格中的號(hào)碼位置即可，人事行政的必備公式

2.提取生日

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

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

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

3.計(jì)算性別

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

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

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

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

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