资源简介

(共34张PPT)
电子表格处理
《信息技术》
任务目标
01
制作职工工资表
02
制作销售数据统计分析表
03
制作人事信息数据表
04
制作销售数据统计图
05
制作产品采购销售分析表
电子表格处理
《信息技术》
任务三 制作人事信息数据表
任务目标
01
任务描述
02
技术分析
03
示例演示
04
任务实现
05
能力拓展
任务描述
任务三 制作人事信息数据表
01
任务描述
综合管理部的小杨负责计算员工薪资，他知道夏雪擅长使用办公软件，于是向她请教人事信息数据的处理方法。夏雪首先帮助小杨完善了人事数据表格的字段信息，然后利用数学函数高效地完成了薪资计算等功能。
技术分析
掌握公式、常用函数及单元格的使用方法。
02
技术分析
1. 使用公式
公式是一个以等号开头的等式，通过对数据进行加、减、乘、除等运算，对单元格中的数据进行计算。公式可以引用同一工作表中的其他单元格，也可以引用同一工作簿中不同工作表的单元格，还可以引用其他工作簿中工作表的单元格。
公式的运算用运算符表示，主要有算术运算符、比较运算符、文本运算符和引用运算符四类。
（1）算术运算符
包括加（）、减（－）、乘（*）、除（/）、百分数（%）和乘方（^）等符号，适合各种基本的数学运算。算术运算符中优先级最高的是百分数（%），然后依次是乘方（^）、乘（*）和除（/）、加（）和减（－）。
技术分析
1. 使用公式
（2）比较运算符
比较运算符包括等于（=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）和不等于（<>）。比较运算符的作用是将两个值进行比较，运算结果为一个逻辑值TRUE或FALSE。其中TRUE表示条件成立，FALSE表示条件不成立。
（3）文本运算符
文本运算符只有一个，即文本连接符（&），它的作用是将一个或多个文本数据连接成一个组合文本。例如，在单元格中输入“=”Office”&”365””后回车，产生的结果为“Office365”。
技术分析
1. 使用公式
（4）引用运算符
引用运算符的作用是进行引用，使用它可以对单元格进行合并计算。引用运算符包括冒号（:）、逗号（,）、空格和感叹号（!）。
冒号（:）表示连续区域运算符，它的作用是对两个引用之间的所有单元格进行引用。例如，A1:B2表示对A1、A2、B1、B2四个单元格进行引用。
逗号（，）表示合并运算符，它的作用是将多个引用合并为一个引用。例如，“A1:A2，B1:B2”表示对A1、A2、B1、B2四个单元格的引用。
空格表示交叉运算符，它的作用是取多个引用的交集作为一个引用。例如，“A1:B2 B1:C2”表示对B1、B2两个单元格的引用。
感叹号表示三维引用运算符，它的作用是引用另外一张工作表的数据。例如“Sheet2!A1:A3”表示对Sheet2这张表中A1、A2、A3三个单元格的引用。
技术分析
2 . 使用函数
函数是预先定义的特定计算公式，按照这个特定的计算公式对一个或多个参数进行计算，得出一个或多个计算的结果，叫作函数值。使用函数进行计算可以大大提高工作效率。Excel中内置的函数主要包括财务函数、日期和时间函数、数学及三角函数、统计函数、文本函数、逻辑函数、信息函数、工程函数等。
函数的语法格式为：函数名(参数1，参数2，……)
函数名用来标记该函数，参数根据不同的函数确定，数量可以是0个或多个，内容可以是数字、文本、True或False的逻辑值、数组、公式或其他函数等。
技术分析
2 . 使用函数
下面介绍几个重要的函数。
（1）SUMIF函数
SUMIF函数的功能是对指定区域指定条件的值进行求和。
语法格式：SUMIF(range，criteria)
其中，range表示指定条件求和的单元格区域；criteria表示条件，其形式可以是数字、表达式、单元格引用、文本或函数等。
（2）COUNTIF函数
COUNTIF函数的功能是统计满足指定条件的单元格数量。
语法格式：COUNTIF(range，criteria)
其中，range表示指定条件的单元格区域。criteria表示条件，其形式可以是数字、表达式、单元格引用、文本或函数。
技术分析
2 . 使用函数
（3）IFERROR函数
IFERROR函数的功能是捕获和处理公式中的错误，IFERROR返回公式计算结果为错误时指定的值。
语法格式：IFERROR(value，value_if_error)
其中，value表示检查是否存在错误的参数，value_if_error表示公式计算结果为错误时要返回的值。
（4）REPLACE函数
REPLACE函数的功能是进行字符替换。
语法格式：REPLACE(old_text，start_num，num_chars，new_text)
其中，old_text表示要进行字符替换的文本，start_num 表示要替换为new_text的字符在旧文本中的位置，num_chars表示要从old_text中要替换的字符个数，new_text表示对old_text中字符进行替换的文本。
技术分析
2 . 使用函数
（5）MID函数
MID函数的功能是提取文本字符串中指定位置开始的特定数目的字符。
语法格式：MID(text，start_num，num_chars)
其中，text表示目标文本字符串，start_num表示准备提取的文本第一个字符的位置，text中第一个字符位置为1，num_chars表示所要提取的文本字符串的长度。
（6）TEXT函数
TEXT函数的功能是根据指定的数字格式将数值转化成文本。
语法格式：TEXT(value，format_text)
其中，value表示数值，能够求值的数值公式或者对数值单元格的引用，format_text表示文字形式的数据格式。
技术分析
2 . 使用函数
（7）VLOOKUP函数
VLOOKUP函数是一个纵向查找函数，按列查找，最终返回该列所需查询序列所对应的值。
语法格式：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)
其中，lookup_value表示需要在数据表首列进行搜索的值，可以是数值、引用或字符串，table_array表示要在其中搜索值的区域，col_index_num表示返回匹配值在table_array中的列序号，range_lookup表示匹配方式，精确匹配用0，模糊匹配用1或省略。
技术分析
3 . 单元格的引用
在Excel公式中经常需要引用单元格的内容，引用的作用就是标识工作表上单元格或单元格区域，并指明公式中所使用数据的位置。
在公式中不需要人为输入单元格数据，只需输入单元格的引用，计算机便可自动获取所引用单元格中的数据。其优点是，如果被引用的单元格数据发生了变化，公式的计算结果也会根据引用单元格的最新数据进行更新。Excel单元格的引用主要包括相对引用、绝对引用和混合引用三种。
（1）相对引用
相对引用是指引用单元格的相对位置，如A1:B2单元格区域引用是相对引用。在列上填充时，列号不变，行号会随着填充而变化；在行上填充时，行号不变，列号会随着填充而变化。
技术分析
3 . 单元格的引用
（2）绝对引用
绝对引用是指引用单元格的精确地址，与包含公式的单元格位置无关。它的引用形式是在行号或列号前都加一个符号$，如$A$1单元格引用是绝对引用。$符号的作用是对行和列进行固定 ，行号和列号都加上$符号后再进行填充，引用的区域就不会发生变化。
（3）混合引用
既包含绝对引用又包含相对引用的引用称为混合引用，它的引用形式为只在行号或列号前加符号$，进行行或列的固定。混合引用有两种形式，一种是固定行，一种是固定列。
示例演示
管理员工信息表
03
示例演示
1.员工编号升级
打开素材“员工信息表.xlsx”，对表Sheet1中数据进行操作。
员工编号由原来的5位升级成6位，升级规则是在字符A后面加上一个数字“0”，例如，A1005升级后变成A01005。
选中C3单元格，单击“公式”选项卡 中的“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入“REPLACE”，单击“转到”按钮，选中REPLACE函数，单击“确定”按钮，弹出“函数参数”设置对话框，在对话框中输入相应参数，参数设置如图所示。
示例演示
1.员工编号升级
（2）单击“确定”按钮，第一个员工编号便可升级6位，第一个员工编号升级效果图如图所示。
（3）将光标指针移至C3单元格右下角，当光标指针变为实心十字形时，双击鼠标左键，进行公式填充，升级其余员工编号。
示例演示
2. 提取员工出生年月
身份证号包含了员工的出生年月，用MID函数可提取身份证出生年月，并可按“××××年××月”的格式显示。
（1）选中G3单元格，单击“公式”选项卡中的“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入“MID”，单击“转到”按钮，选中MID函数，单击“确定”按钮，弹出“函数参数”设置对话框，在对话框中输入相应参数，参数设置如图所示。
示例演示
2. 提取员工出生年月
（2）选中G3单元格，在编辑栏中编辑TEXT公式“=TEXT(MID(F3，7，6)，"0年00月")”，按下Enter键，得到第一个员工指定格式的出生年月，格式化第一个员工出生年月效果图如图2.44所示。
示例演示
2. 提取员工出生年月
（3）选中G3单元格，在编辑栏中编辑TEXT公式“=TEXT(MID(F3，7，6)，"0年00月")”，按下Enter键，得到第一个员工指定格式的出生年月，格式化第一个员工出生年月效果图如图所示。
（4）将鼠标指针移至G3单元格右下角，当鼠标指针变为实心十字形时，双击鼠标左键，进行公式填充，提取和格式化其他员工的出生年月。
示例演示
3.计算年龄
（1）选中H3单元格，在编辑栏中输入公式“=YEAR(TODAY())-YEAR(G3)”，按下Enter键，计算出第一个员工的年龄，计算第一个员工年龄效果图如图所示。
（2）进行公式填充，计算出其他员工的年龄。
示例演示
4 . 提取性别
选中I3单元格，在编辑栏中输入公式“=IF(MOD(MID(F3，17，1)，2)=1，"男"，"女")”，按下Enter键，得到第一个员工的性别，并进行公式填充，计算其他员工的性别，计算员工性别效果图如图所示。
任务实现
管理员工工资表
04
任务实现
工资薪资关乎员工切身利益，影响员工工作积极性和工作效率，关系到企业是否能良性发展，但是薪资的计算规则复杂，项目繁多，工作量较大。夏雪利用Excel中的VLOOKUP、IFERROR等函数对员工工资表进行管理，简单且高效。员工工资表如图所示。
任务实现
岗位绩效工资表如图所示
任务实现
1. 关联岗位绩效表，查找员工岗位绩效
打开素材“员工工资表.xlsx”和“岗位绩效工资表.xlsx”，分别对两张表Sheet1中数据进行操作。
（1）在员工工资表中，选中F2单元格，单击“公式”选项卡中的“函数库”组中的“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入“VLOOKUP”，单击“转到”按钮，选中VLOOKUP函数，单击“确定”按钮，弹出“函数参数”设置对话框，在对话框中输入相应，参数设置如图所示。
任务实现
1. 关联岗位绩效表，查找员工岗位绩效
（2）按下Enter键，关联第一个员工的岗位绩效，选中F2单元格，编辑公式为“=IFERROR(VLOOKUP(B2，岗位绩效工资表.xlsx! $A$1:$C$14，3，0)，0)”在编辑栏中添加IFERROR函数，对未查找到的结果指定为0 ，关联第一个员工岗位绩效效果图如图所示。
（3）进行公式填充，完成其他员工岗位绩效工资的关联。
任务实现
应发工资=岗位工资+岗位绩效+薪级工资+基础绩效+津补贴。
（1）选中J2单元格，在编辑栏中输入“=SUM（E2：I2）”公式，按下Enter键，计算出第一个员工应发工资，效果图如图所示。
2.计算应发工资
（2）进行公式填充，完成其他员工应发工资核算。
任务实现
实发工资=应发工资-扣款
（1）选中L2单元格，在编辑栏中输入公式“=J2-K2”，按下Enter键，得到员工“李欣”的实发工资，效果图如图所示。
3.计算实发工资
（2）进行公式填充，计算其他员工的实发工资。
能力拓展
统计员工学历和年龄段
05
能力拓展
组织部请夏雪统计职工的学历和年龄段，如使用人工查找，工作量大，夏雪运用COUNTIF函数，很快就统计出了各学历人数及各年龄段人数。
打开素材“员工信息表（能力拓展）.xlsx”，对表Sheet1中数据进行操作，统计后效果图如图所示。

展开更多......

收起↑