Excel学习笔记-函数的应用

Excel学习笔记——函数的应用

一、行政管理

1、员工所属的快捷、选择性输入

数据→数据有效性→数据有效性→允许（序列）→来源（A部,B部,C部……）注意：中间用英文逗号隔开→输入信息（请选择部门）→出错警告（输入错误，请重新输入）2、设置身份证号码的长度

数据有效性→允许（文本长度）→数据（等于）→长度（18）→输入信息（请输入18位的身份证号码→出错警告（您输入的身份证号码的位数不是18位，请重新输入！）在输入身份证号时，在最前边加一个英文的“’”

设置电话号码的长度时也可以用这种方法。

3、日期格式的更改

选中单元格→右击选择“设置单元格格式”→在数字栏中选择日期→更改格式

还可以对小数的小数点位数，计数法等的格式进行设置。

4、根据身份证号码自动提取员工的性别

根据身份证号码判断性别的方法为：如果身份证号码的倒数第二位即第17位能够被2整除，则性别为女性，否则为男性。

选中性别下的一个单元格（假设为E3为性别单元格，假设H3为其所对应的身份证的单元格）→输入函数“=IF(MID(H3,17,1)/2=TRUNC(MID(H3,17,1)/2),"女","男")”→复制到其他单元格中

参数说明：

●MID(H3,17,1)/2：提取H3中数值的第17个字符除以2.

●TRUNC(MID(H3,17,1)/2：返回H3数值的的第17个字符除以2以后的整数部分

输入公式后按“回车”，即可显示

5、根据身份证号码自动提取员工的出生日期

身份证号码中，第7—10位为出生的“年”，第11—12位为出生的“月”，第13—14为“日”。

选择出生日期单元格，输入以下公式(H3为所对应的身份证号码)：

=MID(H3,7,4)&"年"&MID(H3,11,2)&"月"&MID(H3,13,2)&"日"

MID函数表示从文本字符串中指定的起始位置起返回指定长度的字符

●MID(H3,7,4)：返回H3中从第七位数值开始的4个字符

●MID(H3,11,2)：返回H3中从第11位数值开始的2个字符

●MID(H3,13,2)：返回H3中从第13位数值开始的2个字符

6、根据员工的出生日期计算年龄

选择年龄单元格，输入公式（G3为对应的出生日期单元格）：

=DA TEDIF(G3,TODA Y(),"Y")

计算工龄的方法同上，只不过是以入职时间为准

表示使用DA TEDIF函数返回从G3单元格中的日期到当前日期的年数。

7、统计公司中所有男员工或女员工，或者各部门、各职位的员工数

使用COUNTIF函数，其语法为：COUNTIF（range，”criteria”）

Range表示要计数的单元格的范围，Criteria表示确定哪些单元格将被计算在内的条件

如：计算C3到C20单元格内女员工的人数，则公式应写为：

=COUNTIF（C3:C20,”女”）

为更加直观的表达人数的多少，可以插入图表，先选中项目和数据的单元格，然后再插入表格

8、统计每个月过生日的员工人数

使用公式：=COUNTIF（$F$3:$F$32,”*01月*”）

此公式表示在F3：F32单元格区域中计算字符串中包含“01月”的单元格的个数

参数说明：

●$F$3:$F$32：列标和行号前加上$表示对F3:F32单元格的绝对引用，以保证在复制公式

时单元格区域的引用不会发生改变。

●”*01月*”：“*”表示任意字符

9、插入smartArt图形的应用

工作表在插入SmartArt图形后会功能区中有“设计”和“格式”两个选项卡可以对其填充、样式的那个进行设计。

二、工资管理

1、为各部门、各职位的员工设定基本工资

假设开发部为1500，基础部为1200，人事部为1000

=IF（$C3=”开发部”,1500,IF($C3=”基础部”,1200,IF($C3=”人事部”,1000))）

并复制到其他单元格即可！

求和的公式为：=SUM（E3：E15），即为求E3到E15单元格的和。

2、计算工龄工资

工龄工资的计算方法为：”工龄”×50=工龄工资，最多不超过500元

输入公式：=IF（$E3>=10,500,$E3*50）（如果大于10年，为500，否则取工龄*50）

E3为工龄单元格

3、计算加班费

加班费的计算方法为：基本工资/30/8×1.5×加班时间=加班工资

即加班费为每小时基本工资的1.5倍

输入公式为：=ROUND($E3/30/8,0)*1.5*$F3

0表示保留0位小数点

4、出勤扣款

事假扣款：=ROUND($E3/30,0)* $F3 每天扣一天的基本工资

病假扣款：=ROUND($E3/30,0)*0.5* $F3 每天扣一天基本工资的一半

迟到扣款：=$J3*50 每迟到一次扣50元

5、计算个人所得税

应纳税所得额=应发工资合计—应扣“三险一金”合计—费用扣除额（2000元）即不超过2000的不用交税

应纳个人所得税税额=应纳税所得额×适用税率—速算扣除数

输入公式：

=IF(M4>2000,IF((M4-2000<=500,(M4-2000)*0.05,IF(AND((M4-2000)>500,(M4-2000)<=2000) ,(M4-2000)*0.1-25)),0)

三、进销存管理

1、复制工作表

将鼠标指针移动到要复制的工作表的标签上，按住鼠标左键，同时按住“Ctrl”键向右拖拽，即可复制该工作表

2、通过分类汇总统计不同商品的总入库数量和金额

先将数据按升序排列（为了使数据分类排列），选择“数据”—“分级显示”—“分类汇总”，在弹出的对话框中选择要汇总的依据和汇总后要输出的数量和金额，“确定”，即可将其分类汇总并分为不同的级别（在表格的左侧显示），可将数据展开或收缩。

3、创建数据透视表

先选择单元格区域，然后选择“插入”—“表”—“数据透视表”—“数据透视表”，在弹出的对话框中选择想要显示的项目，即可得到一个数据透视表

选择“数据透视表工具设计”选项卡，可对透视表进行样式的设置

当双击透视表中的某一数据时，如果该数据是某些数据的合计，则会出现它的下线数据。

4、统计一组数据中的最大值、最小值、平均值

最大值：=MAX（E3：E37）

最小值：=MIN（E3：E37）

平均值：=A VERAGE（E3：E37）