常用函数整理

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