您现在的位置：电子表 > 电子表优势 > > Excel函数公式20个常用公式和示例,

Excel函数公式20个常用公式和示例,

2023/5/12 来源：不详

Excel函数是Excel功能中最为实用、强大的存在。不夸张地说，不懂函数的同事需要花一天时间才能做完的工作，到了会用函数的同事这里，可能仅需要2分钟。尤其是需要做大量数据和表格的部门，差别更是明显。

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)，如上图

5、籍贯(J列)

=VLOOKUP(LEFT(E3,2),籍贯代码!A1:B35,2,0)”，如上图

即：=vlookup(提取身份证前两位，与籍贯代码表匹配，返回第二列数值，精准匹配）

注意：如果输入公式后，显示#N/A，那可能是因为身份证号录入的是文本格式，而我们的身份证号代码表是数字格式，公式无法匹配，无法返回数据，就会显示错误。

遇到这种情况我们可以在LEFT之前加--，即“=VLOOKUP(--LEFT(E3,2),籍贯代码!A1:B35,2,0)”，运用数学里负负得正的原理使得文本格式转换成数字格式，就可以和代码表进行匹配了。回车确定后填充即可。

如果想提取详细的籍贯信息，可以到网上下载六位的区域代码表，并把公式中，提取前两位数字，改成提取前六位即可。

6、生肖公式（K列）

公式=MID(鼠牛虎兔龙蛇马羊猴鸡狗猪,MOD(YEAR(G3)-4,12)+1,1)。

注：一共有12个生肖，因此将年龄除以“12（函数“MOD(年龄,12)”）得到的数字相同的就是同一个生肖。如上图

7、星座公式（L列）

=LOOKUP(--TEXT(G3,mdd),{,摩羯座;,水瓶座;,双鱼座;,白羊座;,金牛座;,双子座;,巨蟹座;,狮子座;,处女座;,天秤座;,天蝎座;,射手座;,摩羯座})，如上图

8、社会工龄(N列)

=DATEDIF(M3,NOW(),y)

7、公司工龄(Q列)

=DATEDIF(P3,NOW(),y)年DATEDIF(P3,NOW(),ym)月DATEDIF(P3,NOW(),md)天

8、合同续签日期(S列)

=DATE(YEAR(P3)+LEFTB(R3,2),MONTH(P3),DAY(P3))-1

9、合同到期日期(T列)

=TEXT(EDATE(P3,LEFTB(R3,2)*12)-TODAY(),[0]过期0天;[30]即将到期0天;还早)

10、工龄工资(U列)

=MIN(,DATEDIF($M3,NOW(),y)*50)

11、本科学历人数

=COUNTIF(V:V,本科)

12、销售部本科学历人数

=COUNTIFS(C:C,销售部,V:V,本科)

13、30~35岁总人数

=COUNTIFS(H:H,=30,H:H,35)

14、工资条公式

=CHOOSE(MOD(ROW(A3),3)+1,工资数据源!A$1,OFFSET(工资数据源!A$1,INT(ROW(A3)/3),,),)

注：A3:标题行的行数+2,如果标题行在第3行，则A3改为A5；工资数据源!A$1:工资表的标题行的第一列位置。

15、年的个税计算公式

=ROUND(MAX((A2-B2-C2-D2-)*{0.03,0.1,0.2,0.25,0.3,0.35,0.45}-{0,,,,,,},0),2)

注：首先在excel表格中输入对应的数据，A2为税前工资，B2为专项扣除(一般指五险一金等个人缴纳的部分)，C2为专项附加扣除(指子女教育支出/继续教育支出/大病医疗支出/住房贷款利息/住房租金/赡养老人支出等的专项附加扣除),D2为其他扣除(如健康商业险等个人缴纳的部分)。然后在E2单元格中输入上述计算公式，点回车。

16、隔行隔列求和

=SUMIF(A1:E12,A2,B1:F12)

17、单一条件统计个数

比如，年龄大于28岁的人数。

=COUNTIF(H3:H13,=28)

18、多条件个数统计

比如，年龄大于28岁的男性人数。

=COUNTIFS(H3:H13,=28,F3:F13,男)

19、单条件求平均

比如，男性的平均年龄

=AVERAGEIF(F3:F13,男,H3:H13)

20、多条件求和