非常实用的Excel公式大全,在表格再也不用求人了!【转存!实用Excel公式大全!】长知识实用的公式有哪些?做表格有哪些常用的快捷方式?做表格不再求人!via@央视新闻
会计常用的Excel函数公式大全(共21个)1、计算性别(F列) =IF(MOD(MID(E3,17,1),2),"男","女") 2、出生年月(G列) =TEXT(MID(E3,7,8),"0-00-00") 3、年龄公式(H列) =DATEDIF(G3,TODAY(),"y") 4、退休日期(I列) =TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/dd aaaa") 5、籍贯(M列) =VLOOKUP(LEFT(E3,6)*1,地址库!E:F,2,) 注:附带示例中有地址库代码表 6、社会工龄(T列) =DATEDIF(S3,NOW(),"y") 7、公司工龄(W列) =DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天" 8、合同续签日期(Y列) =DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-1 9、合同到期日期(Z列) =TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[ 10、工龄工资(AA列) =MIN(700,DATEDIF($V3,NOW(),"y")*50) 11、生肖(AB列) =MID("猴鸡狗猪鼠牛虎兔龙蛇马羊",MOD(MID(E3,7,4),12)+1,1) 12、本月工作日天数(AG列) =NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),) 13、调休天数公式(AI列) =COUNTIF(B9:AE9,"调") 14、扣钱公式(AO列) 婚丧扣10块,病假扣20元,事假扣30元,矿工扣50元 =SUM((B9:AE9={"事";"旷";"病";"丧";"婚"})*{30;50;20;10;10}) 15、本科学历人数 =COUNTIF(D:D,"本科") 16、办公室本科学历人数 =COUNTIFS(A:A,"办公室",D:D,"本科") 17、30~40岁总人数 =COUNTIFS(F:F,">=30",F:F," 18、提成比率计算 =VLOOKUP(B3,$C$12:$E$21,3) 19、个人所得税计算 假如A2中是应税工资,则计算个税公式为: =5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},) 20、工资条公式 =CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),"") 注:
21、Countif函数统计身份证号码出错的解决方法 由于Excel中数字只能识别15位内的,在Countif统计时也只会统计前15位,所以很容易出错。不过只需要用 &"*" 转换为文本型即可正确统计。 =Countif(A:A,A2&"*")
超简单最实用的九个EXCEL函数公式一, &符的使用 二, 快速汇总求和 三, VLOOKUP条件查询 四, SUMIF条件求和 五, COUNTIF条件计数 六, IF条件判断 七, MID字符串处理 八, TEXT日期处理 九, LOOKUP区间判断
一、身份证号码类。 (一)提取性别。 方法: 在目标单元格中输入公式:=IF(MOD(MID(C3,17,1),2),"男","女")。 解读: 1、身份证号码共计18位,其中第17位代表性别,如果除以2余数为1,则为“男”性,如果余数为0,则为“女”性。所以首先用Mid函数提取第17位数值。 2、Mod函数为求余函数,其语法结构为:=Mod(被除数,除数)。被除数÷除数的结果,即商为Mod函数的结果。 3、用Mod函数计算出结果之后,利用IF函数判断,如果余数为1,则返回“男”,如果余数为0,则返回女。 (二)提取出生年月 方法: 在目标单元格中输入公式:=TEXT(MID(C3,7,8),"0!/00!/00")。 解读: 1、身份证号码中的第7位至14位(长度为8)为出生年月,所以用Mid函数提取。 2、用MId函数提取的仅为一串数字,需要对其“美化”,所以用Text函数对其设置格式。 (三)计算年龄 方法: 在目标单元格中输入公式:=DATEDIF(E3,TODAY(),"y")、=DATEDIF(TEXT(MID(C7,7,8),"0!/00!/00"),TODAY(),"y")。 解读: 1、年龄就是当前年份减去出生年份,而在Excel函数中,Datedif函数就是按照指定的类型返回两个日期之间的间隔数。其语法结构为=Datedif(开始日期,结束日期,统计方式)。常见的统计方式有“Y”、“M”、“D”;分别为“年”、“月”、“日”。 2、如果在现有的数据中已经有出生年月,则用公式=DATEDIF(E3,TODAY(),"y")实现,否则要从身份证号码中提取出生年月,则用公式=DATEDIF(TEXT(MID(C7,7,8),"0!/00!/00"),TODAY(),"y")。实现。 二、常用汇总类。 (一)求和类 1、单条件求和 方法: 在目标单元格中输入公式:=SUMIF(C3:C9,H3,D3:D9)、=SUMIF(C3:C9,H3,E3:E9)。 解读: 1、从示例中可以看出目的为:按性别统计“总销量”和“总销售额”,暨分别计算“男”、“女”销售员的总销量和总销售额。 2、Sumif函数为单条件求和函数,语法结构为:=Sumif(条件范围,条件,求和范围)。 2、多条件求和。 方法: 在目标单元格中输入公式:=SUMIFS(D3:D9,C3:C9,H3,D3:D9,">"&I3)、=SUMIFS(E3:E9,C3:C9,H3,D3:D9,">"&I3)。 解读: 多条件求和函数和单条件求和函数类似,为Sumifs函数,语法结构为:=Sumifs(求和范围,条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。 (二)最值类 方法: 在目标单元格中输入公式:=MAXIFS(D3:D9,C3:C9,H3)、=MINIFS(D3:D9,C3:C9,H3)。 解读: 1、如果没有附加条件,求最大值或最小值,可以使用Max函数或Min函数。 2、如果有附加条件,求最大值或最小值,则必须使用Maxifs函数或Minifs函数。其语法结构是相同的,暨=函数名(数值范围,条件区域1,条件1,条件区域2,条件2,……,条件区域N,条件N)。 (三)平均值类。 方法: 在目标单元格中输入公式:=AVERAGE(D3:D9)、=AVERAGEIF(C3:C9,H5,D3:D9)、=AVERAGEIFS(E3:E9,C3:C9,H5)。 解读: 1、计算平均值的函数可以分为两类,普通类(Average)和条件类(Averageif、Averageifs)。 2、Averageif函数为单条件求平均值,语法结构为:=Averageif(条件范围,条件,数值范围);Averageifs函数为多条件求平均值,语法结构为:=Averageifs(数值范围,条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。 (四)个数统计类。 1、一般个数统计。 方法: 在目标单元格中输入公式:=COUNTA(B3:B9)、=COUNTBLANK(B3:B9)、=COUNTA(D3:D9)、=COUNTIF(D3:D9,">5000")、=COUNTIFS(C3:C9,"男",D3:D9,">5000")。 解读: 文本个数类统计:Counta函数;空单元格个数统计:Countblank函数;数值类个数统计:Count函数;单条件计数函数:Countif;多条件计数函数:Countifs函数。 2、分段统计。 方法: 1、在目标单元格中输入公式:=FREQUENCY(D3:D9,H3:H9)。 2、Ctrl Shift Enter填充。 解读: 1、函数Frequency的功能为:计算数值在指定区域内出现的频次。语法结构为:=Frequency(数据范围,统计值范围)。 2、公式=FREQUENCY(D3:D9,H3:H9)的意思为:≤2000的数为0;2001-3000之间的数为2;3001-4000之间的数为0;4001至5000之间的数为1;5001至6000之间的数为1;6001至7000之间的数为2;7001至8000之间的数为1。 7个IF函数,15个Excel实用公式,每一个都不可小瞧哦! |
|