常用函數整理

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