第 3 章 通过 Excel 创建 并处理电子表格 NCRE.

Presentation on theme: "第 3 章 通过 Excel 创建 并处理电子表格 NCRE."— Presentation transcript:

1 第 3 章 通过 Excel 创建 并处理电子表格 NCRE

2 第3章 通过 Excel 创建并处理电子表格 Excel 制表基础 工作簿与多工作表操作 Excel 公式和函数 在 Excel 中创建图表
3.1 Excel 制表基础 3.2 工作簿与多工作表操作 3.3 Excel 公式和函数 3.4 在 Excel 中创建图表 3.5 Excel 数据分析与处理 3.6 Excel 与其他程序的协同与共享

3 3.3 Excel公式和函数 本节案例背景： Excel 提供大量实用函数满足各类计算的需要。通过公式和函数计 算出的结果不但正确率有保证，而且在原始数据发生改变后，计 算结果能够自动更新。 在人事档案管理和工资表的创建过程中，公式和函数给书娟的工 作提供了相当的便利，极大地提高了工作的效率和效果。 本节将通过对员工档案表和员工工资表的处理，帮助书娟实现以 下工作目标： 了解公式和函数的基本作用，了解在公式中各种引用的含义及方法。 掌握公式的构成、输入方法，学会构建公式以帮助计算。 了解 Excel 提供了哪些类型的函数，掌握函数的基本输入方法。 掌握 Exce l中常用、重要函数的使用方法，并在实际工作中应用它们。 了解公式及函数应用过程中各种常见问题及解决方法。 第3章 通过 Excel 创建并处理电子表格

4 3.3 Excel 公式和函数 3.3.1 使用公式的基本方法 1. 认识公式 公式是一组表达式，由单元格引用、常量、运算符、括号组成，复杂的 公式还可以包括函数，用于计算生成新的值。在Excel中，公式总是以 等号“=”开始。 单元格引用：即单元格地址，用于表示单元格在工作表上所处位 置的坐标。例如，显示在第B列和第3行交叉处的单元格，其引用 形式为“B3”。 常量：固定的数值或文本。例如，数字“210”和文本“姓名”均为常 量。 运算符：运算符用于连接常量、单元格引用，从而构成完整的表 达式。常用的运算符有：算术运算符，字符连接符，关系运算符。 第3章 通过 Excel 创建并处理电子表格

5 3.3.1 使用公式基本方法 2. 公式的输入与编辑 1）输入公式 =常量/单元格引用/表达式
3.3 Excel 公式和函数 3.3.1 使用公式基本方法 2. 公式的输入与编辑 1）输入公式 =常量/单元格引用/表达式 必须以等号“=”开始，在公式中所使用的运算符都必须是西文的半角字符 例如：=30，=B3，=A5*10%，=C8&C9 第3章 通过 Excel 创建并处理电子表格

6 3.3.1 使用公式基本方法 3. 公式的复制与填充 2）修改公式 拖动公式单元格右下角的填充柄 “开始”选项卡→“编辑”组→“填充”按钮。
3.3 Excel 公式和函数 3.3.1 使用公式基本方法 2）修改公式 用鼠标双击公式进入编辑状态，在单元格或者编辑栏中均可对公式进行修改即可。 删除公式：按 Del 键。 3. 公式的复制与填充 拖动公式单元格右下角的填充柄 “开始”选项卡→“编辑”组→“填充”按钮。 进行公式的复制填充时，填充的实际上是公式而非数据本身，填充时公式中对单元格的引用采用的是相对引用。 第3章 通过 Excel 创建并处理电子表格

7 3.3 Excel 公式和函数 3.3.1 使用公式基本方法 4. 单位格引用 在公式中最常用的是单元格引用。可以在单元格中引用一个 单元格、一个单元格区域、引用另一个工作表或工作簿中的 单元格或区域。 单元格引用方式分为以下几类： 相对引用：如“=A1”。 绝对引用：如“=$A$1” 。 混合引用：如“=A$1”、“=$A1”、 “=$A$1” 第3章 通过 Excel 创建并处理电子表格

8 3.3 Excel 公式和函数 3.3.2 名称的定义与引用 为单元格或区域指定一个名称，是实现绝对引用的方法之一。 可以定义为名称的对象包括：常量、单元格或单元格区域、公 式。 1. 了解名称的语法规则 唯一性原则：名称在其适用范围内必须始终唯一，不可重复。 有效字符：名称中第一个字符必须是字母、下划线 (_) 或反 斜杠 (\)，名称中不能使用大小写字母“C”、“c”、“R”或“r”。 不能与单元格地址相同：例如，名称是A1、B$2等。 不能使用空格：可选用下划线 (_) 和句点 (.) 作为单词分隔符。 名称长度有限制：一个名称最多可以包含 255 个西文字符。 不区分大小写：例如，Sales与SALES视为同名。 第3章 通过 Excel 创建并处理电子表格

9 3.3.2 名称的定义与引用 2. 为单元格或单元格区域定义名称 定义好的名称将会在公式及函数中被引用。 1）快速定义名称
3.3 Excel 公式和函数 3.3.2 名称的定义与引用 2. 为单元格或单元格区域定义名称 定义好的名称将会在公式及函数中被引用。 1）快速定义名称 选择要命名的单元格或单元格区域，在编辑栏的“名称框”中输入名称后按 Enter键确认。 在案例工作簿文档“3.3 员工档案及工资表”的“员工档案表”工作表中，将数据列表区域A3:N38的名称定义为“全体员工资料”。 第3章 通过 Excel 创建并处理电子表格

10 3.3.2 名称的定义与引用 2）将现有行和列标题转换为名称
3.3 Excel 公式和函数 3.3.2 名称的定义与引用 2）将现有行和列标题转换为名称 选择要命名的区域，必须包括行或列标题→“公式”选项 卡→“定义的名称”组→“从所选内容创建”按钮→在对话 框中，通过选中“首行”、“左列”、“末行”或“右列”复选框 来指定包含标题的位置。 在案例工作簿文档“3.3 员工档案及工资表”的“员工档案 表”中，将“基本工资”和“工龄工资”两列的首行转换为相 应列数据的名称。 第3章 通过 Excel 创建并处理电子表格

11 3.3.2 名称的定义与引用 3）使用“新名称”对话框定义名称
3.3 Excel 公式和函数 3.3.2 名称的定义与引用 3）使用“新名称”对话框定义名称 “公式”选项卡→“定义的名称”组→“定义名称”按钮→在“名 称”文本框中输入名称→ 在“范围”下设定名称的适用范围 →在“备注”说明性批注。 →“引用位置”框中修改命名对象 （可以是单元格区域、常量、公式），可选择下列操作之 一。 在案例工作簿文档“3.3 员工档案及工资表”的“员工档案表” 中，将工龄工资常量“50”元定义为名称“工龄工资_每年”。 第3章 通过 Excel 创建并处理电子表格

12 3.3.2 名称的定义与引用 3. 引用名称 名称可直接用来快速选定已命名的区域，更重要的是可以在公式中引用名称以实现精确引用。
3.3 Excel 公式和函数 3.3.2 名称的定义与引用 3. 引用名称 名称可直接用来快速选定已命名的区域，更重要的是可以在公式中引用名称以实现精确引用。 1）通过“名称框”引用 单击编辑栏中“名称框”右侧的黑色箭头，打开“名称”下拉列表→单击选择某一名称 提示：通过名称框打开的“名称”下拉列表中不包括常量和公式的名称。 第3章 通过 Excel 创建并处理电子表格

13 3.3.2 名称的定义与引用 4. 更改或删除名称 2）在公式中引用
3.3 Excel 公式和函数 3.3.2 名称的定义与引用 2）在公式中引用 “公式”选项卡→“定义的名称”组→“用于公式”按钮→选择名称 4. 更改或删除名称 如果更改了某个已定义的名称，则工作簿中所有已引用该名称的 位置均会自动随之更新。 更改名称：“公式”选项卡→“定义的名称”组→“名称管理器”按 钮→在名称列表中选择要更改的名称→“编辑”按钮→ 在“编辑 名称”对话框中修改名称属性。 删除名称： “公式”选项卡→“定义的名称”组→“名称管理器”按 钮→在名称列表中选择名称→“删除”按钮。 提示：若公式中已引用的某个名称被删除，可能导致公式出错。 第3章 通过 Excel 创建并处理电子表格

14 3.3.3 使用函数的基本方法 1. 认识函数 2. Excel 函数分类
函数实际上特殊的公式，主要是为解决那些复杂计算需求而提供的一种预置算法，如求和函数SUM，平均值函数EVERAGE，条件函数 IF等。 函数通常表示为：函数名（[参数1], [参数2],……） 函数中的参数可以是常量、单元格地址、数组、已定义的名称、公式、函数等。输入函数时必须以等号“=”开始。 2. Excel 函数分类 Excel 提供大量工作表函数，并按其功能进行分类。Excel 2010 目前默认提供的函数类别共13大类，见表3.3中所列。 第3章 通过 Excel 创建并处理电子表格

15 表 3.3 Excel 2010函数类别 3.3 Excel 公式和函数 函数类别 常用函数示例及说明 财务函数
NPV(rate,value1,[value2],...) 返回一项投资的净现值。 日期和时间函数 YEAR(serial_number) 返回某日期对应的年份 数学和三角函数 INT(number) 将数字向下舍入到最接近的整数。 统计函数 AVERAGE(number1, [number2], ...) 返回参数的算术平均值 查找和引用函数 VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 搜索某个单元格区域的第一列，然后返回该区域相同行上任何单元格中的值 数据库函数 DCOUNTA(database, field, criteria) 返回满足指定条件的非空单元格的个数。 文本函数 MID(text, start_num, num_chars) 返回文本字符串中从指定位置开始的特定数目的字符 逻辑函数 IF(logical_test, [value_if_true], [value_if_false]) 若指定条件的计算结果为 TRUE，将返回某个值；若该条件的计算结果为 FALSE，则返回另一个值。 信息函数 ISBLANK(value) 检验单元格值是否为空，若为空则返回 TRUE 工程函数 CONVERT(number, from_unit, to_unit) 将数字从一个度量系统转换到另一个度量系统中。 兼容性函数 RANK(number,ref,[order]) 返回一个数字在数字列表中的排位 多维数据集函数 CUBEVALUE(connection,member_expression1,member_expression2…) 从多维数据集中返回汇总值 与加载项一起安装的用户自定义函数 如果在系统中安装了某一包含函数的应用程序，该程序做为Excel的加载项，其所包含的函数作用自定义函数显示在这里以供选用。

16 3.3 Excel 公式和函数 3.3.3 使用函数的基本方法 3. 函数的输入与编辑 函数的输入方式与公式类似，可以直接在单元格中输入“=函数名(所引用的参数)”，但是要想记住每一个函数名并正确输入所有参数是有相当困难的。因此，通常情况采用参照的方式输入一个函数。 1）通过“函数库”组插入 “公式”选项卡→“函数库”组中的某一函数类别→ 从函数列表中单击函数→在“函数参数”对话框中输入或选择参数 第3章 通过 Excel 创建并处理电子表格

17 3.3.3 使用函数的基本方法 2）通过“插入函数”按钮插入 3）修改函数
3.3 Excel 公式和函数 3.3.3 使用函数的基本方法 2）通过“插入函数”按钮插入 “公式”选项卡→ “函数库”组→ “插入函数”按钮，打开“插入函数”对话框→ 在“选择类别”下拉表中选择函数类别，或者在“搜索函数”框中输入函数的简单描述后单击“转到”按钮→ 在“选择函数”列表中选择函数→在“函数参数”对话框中输入参数。 3）修改函数 在包含函数的单元格的双击鼠标，进入编辑状态，对函数参数进行修改后按Enter键确认。 第3章 通过 Excel 创建并处理电子表格

18 SUM(number1,[number2],...])
3.3 Excel 公式和函数 3.3.4 Excel 中常用函数的应用 1. Excel中常用函数简介 1）求和函数 SUM(number1,[number2],...]) 功能：将指定的参数number1、number2……相加求和。 例如： =SUM(A1:A5) 是将单元格A1至A5中的所有数值相加 =SUM(A1, A3, A5) 是将单元格A1、A3和A5中的数字相加。 第3章 通过 Excel 创建并处理电子表格

19 SUMIF(range, criteria, [sum_range])
3.3 Excel 公式和函数 3.3.4 Excel 中常用函数的应用 2）条件求和函数 SUMIF(range, criteria, [sum_range]) 功能：对指定单元格区域中符合指定条件的值求和。 提示：在函数中任何文本条件或任何含有逻辑或数学符号 的条件都必须使用双引号 (") 括起来。如果条件为数字， 则无需使用双引号。 例如： =SUMIF(B2:B25,">5") 表示对B2:B25区域大于5的数值进 行相加； =SUMIF(B2:B5, "John", C2:C5)，表示对单元格区域 C2:C5中与单元格区域B2:B5中等于“John”的单元格对 应的单元格中的值求和。 第3章 通过 Excel 创建并处理电子表格

20 3.3.4 Excel中常用函数的应用 3）多条件求和函数 例如：
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 功能：对指定单元格区域中满足多个条件的单元格求和。 例如： =SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10") 表示对区域A1:A20中符合以下条件的单元格的数值求和：B1:B20中的相应数值大于零、且C1:C20中的相应数值小于10。 第3章 通过 Excel 创建并处理电子表格

21 3.3.4 Excel中常用函数的应用 4）绝对值函数 ABS(number) 5）向下取整函数INT(number)
例如：=ABS(-2) 表示求-2的绝对值；=ABS(A2)，表示对单元格A2中的数值求取绝对值。 5）向下取整函数INT(number) 功能：将数值number向下舍入到最接近的整数 例如：=INT(8.9) 表示将 8.9 向下舍入到最接近的整数，结果为8；=INT(-8.9) 表示将 -8.9 向下舍入到最接近的整数，结果为 -9。 第3章 通过 Excel 创建并处理电子表格

22 ROUND(number, num_digits)
3.3 Excel 公式和函数 3.3.4 Excel中常用函数的应用 6）四舍五入函数 ROUND(number, num_digits) 功能：将指定数值number按指定的位数num_digits进行四舍五入。 例如：=ROUND( , 2)，表示将数值 四舍五入为小数点后两位。 提示：如果希望始终进行向上舍入，可使用ROUNDUP函数；如果希望始终始终进行向下舍入，则应使用ROUNDDOWN函数。 第3章 通过 Excel 创建并处理电子表格

23 TRUNC(number, [num_digits])
3.3 Excel 公式和函数 3.3.4 Excel中常用函数的应用 7）取整函数 TRUNC(number, [num_digits]) 功能：将指定数值number的小数部分截去，返回整数。num_digits为取整精度，默认为0。 例如： =TRUNC(8.9) 表示取8.9的整数部分，结果为8； =TRUNC(-8.9) 表示取-8.9的整数部分，结果为 -8。 第3章 通过 Excel 创建并处理电子表格

24 3.3.4 Excel中常用函数的应用 8）垂直查询函数
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 功能：搜索指定单元格区域的第一列，然后返回该区域相同行上任何指定单元格中的值。 例如：=VLOOKUP(1,A2:C10,2) 要查找的区域为A2:C10，因此A列为第1列，B列为第2列，C列则为第3列。表示使用近似匹配搜索A列（第1列）中的值1，如果在A列中没有1，则近似找到A列中与1最接近的值，然后返回同一行中B列（第2列）的值。 =VLOOKUP(0.7,A2:C10,3,FALSE) 表示使用精确匹配在A列中搜索值0.7。如果A列中没有0.7这个值，则所以返回一个错误 #N/A。 第3章 通过 Excel 创建并处理电子表格

25 IF(logical_test, [value_if_true], [value_if_false])
3.3 Excel 公式和函数 3.3.4 Excel中常用函数的应用 9）逻辑判断函数 IF(logical_test, [value_if_true], [value_if_false]) 功能：如果指定条件的计算结果为TRUE，IF函数将返回某个值；如果该条件的计算结果为 FALSE，则返回另一个值。 提示：在 Excel 2010 中，最多可以使用64个 IF 函数进行嵌套，以构建更复杂的测试条件。也就是说，IF函数也可以作为 value_if_true 和 value_if_false 参数包含在另一个 IF 函数中。 第3章 通过 Excel 创建并处理电子表格

26 3.3.4 Excel中常用函数的应用 9）逻辑判断函数（续） 例如：
=IF(A2>=60,"及格","不及格") 表示，如果单元格A2中的值大于等于60，则显示“及格”字样，否则显示“不及格”字样； =IF(A2>=90,"优秀",IF(A2>=80,"良好", IF(A2>=60,"及格 ","不及格"))) 表示下列对应关系： 单元格A2中的值 公式单元格显示的内容 A2>=90 优秀 90>A2>=80 良好 80>A2>=60 及格 A2<60 不及格 第3章 通过 Excel 创建并处理电子表格

27 3.3.4 Excel中常用函数的应用 10）当前日期和时间函数 NOW() 功能：返回当前计算机系统的日期和时间。
当将数据格式设置为数值时，将返回当前日期和时间所对 应的序列号，该序列号的整数部分表明其与1900年1月1日 之间的天数。 11）函数 YEAR(serial_number) 功能：返回指定日期对应的年份。返回值为 1900 到 之间的整数 例如：=YEAR(A2) 当在A2单元格中输入日期2008/12/27 时，该函数返回年份2008。 注意：公式所在的单元格不能是日期格式。 第3章 通过 Excel 创建并处理电子表格

28 3.3.4 Excel中常用函数的应用 12）当前日期函数 TODAY()
功能：返回今天的日期。通过该函数，可以实现无论何 时打开工作簿时工作表上都能显示当前日期；该函数也 可以用于计算时间间隔，可以用来计算一个人的年龄。 例如：=YEAR(TODAY())-1963 假设一个人出生在1963 年，该公式使用TODAY函数作为YEAR函数的参数来获 取当前年份，然后减去1963，最终返回对方的年龄。 第3章 通过 Excel 创建并处理电子表格

29 AVERAGE(number1, [number2], ...)
3.3 Excel 公式和函数 3.3.4 Excel中常用函数的应用 13）平均值函数 AVERAGE(number1, [number2], ...) 功能：求指定参数number1、number2……的算术平均值，最多可包含255个参数。 例如： =AVERAGE(A2:A6) 表示对单元格区域A2到A6中的数值求平均值 =AVERAGE(A2:A6, C6) 表示对单元格区域A2到A6中数值与C6中的数值求平均值。 第3章 通过 Excel 创建并处理电子表格

30 AVERAGEIF(range, criteria, [average_range])
3.3 Excel 公式和函数 3.3.4 Excel中常用函数的应用 14）条件平均值函数 AVERAGEIF(range, criteria, [average_range]) 功能：对指定区域中满足给定条件的所有单元格中的数值求算术平均值 例如： =AVERAGEIF(A2:A5,"<5000") 表示求单元格区域A2:A5中小于5,000的数值的平均值； =AVERAGEIF(A2:A5,">5000",B2:B5) 表示对单元格区域B2:B5中与单元格区域A2:A5中大于5000的单元格所对应的单元格中的值求平均值。 第3章 通过 Excel 创建并处理电子表格

31 3.3.4 Excel中常用函数的应用 15）多条件平均值函数
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) 功能：对指定区域中满足多个条件的所有单元格中的数值求算术平均值 例如： =AVERAGEIFS(A1:A20,B1:B20,">70",C1:C20, "<90") 表示对区域A1:A20中符合以下条件的单元格的数值求平均值：B1:B20中的相应数值大于70、且C1:C20中的相应数值小于90。 第3章 通过 Excel 创建并处理电子表格

32 3.3.4 Excel中常用函数的应用 16）计数函数 COUNT(value1, [value2], ...)
功能：统计指定区域中包含数值的个数。只对包含数字的单元格进行计数。 例如：=COUNT(A2:A8) 表示统计单元格区域A2到A8中包含数值的单元格的个数。 17）计数函数 COUNTA(value1, [value2], ...) 功能：统计指定区域中不为空的单元格的个数。可对包含任何类型信息的单元格进行计数。 例如：=COUNTA(A2:A8) 表示统计单元格区域A2到A8中非空单元格的个数。 第3章 通过 Excel 创建并处理电子表格

33 COUNTIF(range, criteria)
3.3 Excel 公式和函数 3.3.4 Excel中常用函数的应用 18）条件计数函数 COUNTIF(range, criteria) 功能：统计指定区域中满足单个指定条件的单元格的个数 例如： =COUNTIF(B2:B5,">55") 表示统计单元格区域B2到B5中值大于55的单元格的个数。 第3章 通过 Excel 创建并处理电子表格

34 3.3.4 Excel中常用函数的应用 19）多条件计数函数
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) COUNTIFS(区域1, 条件1, [区域2, 条件2]…) 功能：统计指定区域内符合多个给定条件的单元格的数量。可以将条件应用于跨多个区域的单元格，并计算符合所有条件的次数。 例如： =COUNTIFS(A2:A7, ">80",B2:B7,"<100") 统计单元格区域A2到A7中包含大于80的数，同时在单元格区域B2到B7中包含小于100的数的行数。 第3章 通过 Excel 创建并处理电子表格

35 3.3.4 Excel中常用函数的应用 20）最大值函数 MAX (number1, [number2], ...)
功能：返回一组值或指定区域中的最大值 例如：=MAX(A2:A6) 表示从单元格区域A2:A6中查找并返回最大数值。 21）最小值函数 MIN(number1, [number2], ...) 功能：返回一组值或指定区域中的最小值 例如：=MIN(A2:A6) 表示从单元格区域A2:A6中查找并返回最小数值。 第3章 通过 Excel 创建并处理电子表格

36 3.3.4 Excel中常用函数的应用 22）排位函数 RANK.EQ(number,ref,[order])
RANK.AVG(number,ref,[order]) 功能：返回一个数值在指定数值列表中的排位；如果多 个值具有相同的排位，使用函数RANK.AVG将返回平均 排位；使用函数RANK.EQ则返回实际排位。 例如： =RANK.EQ("3.5",A2:A6,1) 表示求取数值3.5在单元格区域A2:A6中的数值列表中的升序排位。 第3章 通过 Excel 创建并处理电子表格

37 CONCATENATE(text1, [text2], ...)
3.3 Excel 公式和函数 3.3.4 Excel中常用函数的应用 23）文本合并函数 CONCATENATE(text1, [text2], ...) 功能：将几个文本项合并为一个文本项。可将最多255个文本字符串联接成一个文本字符串。联接项可以是文本、数字、单元格地址或这些项目的组合。 例如： =CONCATENATE(B2, " ", C2) 表示将单元格B2中的字符串、空格字符以及单元格C2中的值相连接，构成一个新的字符串。 提示：也可以用文本连结运算符“&”代替CONCATENATE 函数来联接文本项。例如，=A1 & B1与=CONCATENATE(A1, B1) 返回的值相同。 第3章 通过 Excel 创建并处理电子表格

38 MID(text, start_num, num_chars)
3.3 Excel 公式和函数 3.3.4 Excel中常用函数的应用 24）截取字符串函数 MID(text, start_num, num_chars) 功能：从文本字符串中的指定位置开始返回特定个数的字符。 例如： =MID(A2,7,4) 表示从单元格A2中的文本字符串中的第7个字符开始提取4个字符。 第3章 通过 Excel 创建并处理电子表格

39 3.3.4 Excel中常用函数的应用 25）左侧截取字符串函数 LEFT(text, [num_chars])
功能：从文本字符串最左边开始返回指定个数的字符，也就是最前面的一个或几个字符。 例如：=LEFT(A2,4) 表示从单元格A2中的文本字符串中提取前四个字符。 26）右侧截取字符串函数 RIGHT(text,[num_chars]) 功能：从文本字符串最右边开始返回指定个数的字符，也就是最后面的一个或几个字符。 例如：=RIGHT (A2,4) 表示从单元格A2中的文本字符串中提取后四个字符。 第3章 通过 Excel 创建并处理电子表格

40 3.3.4 Excel中常用函数的应用 27）删除空格函数 T RIM(text) 28）字符个数函数 LEN(text)
功能：统计并返加指定文本字符串中的字符个数。 例如：=LEN(A2) 表示统计位于单元格A2中的字符串的长度。 第3章 通过 Excel 创建并处理电子表格

41 3.3.4 Excel中常用函数的应用 2. 常用函数在实际工作中的应用 公式1： 公式2： 1）运用公式及函数完善员工档案表
在案例工作簿文档“3.3 员工档案及工资表（1）”中运用公式及函数。 1）运用公式及函数完善员工档案表 在“员工档案表”工作表中，需要运用公式和函数分别提取员工的生日、 计算出员工的年龄、工龄以及工龄工资。 ① 提取员工生日： 公式1： =CONCATENATE(MID(F4,7,4),"年",MID(F4,11,2),"月",MID(F4,13,2),"日") 公式2： =MID(F5,7,4)&"年"&MID(F5,11,2)&"月"&MID(F5,13,2)&"日" 第3章 通过 Excel 创建并处理电子表格

42 3.3.4 Excel中常用函数的应用 ② 计算员工年龄： ③ 计算员工的工龄： ④ 计算工龄工资：
输入函数“=INT((TODAY()-G4)/365)” ③ 计算员工的工龄： 输入函数“=INT((TODAY()-J4)/365)” ④ 计算工龄工资： 每满一年工龄工资增加50元，用工龄乘以50即可计算工龄工资，可以通过绝对引用或已定义名称来计算。 使用绝对引用： “=K4*基础数据!$B$4”， 使用定义名称：常量“50”已事先被命名为“工龄工资_每年”。输入公式“=K5*工龄工资_每年” 第3章 通过 Excel 创建并处理电子表格

43 3.3.4 Excel中常用函数的应用 ⑤ 计算基础工资： ⑥ 统计全部员工数量： ⑦ 统计女员工的数量：
输入函数“=SUM(L4:M4)”。 ⑥ 统计全部员工数量： 在“基础数据”工作表的“员工总人数”处输入函数“=COUNTA(员工档案表!A4:A38)”。 ⑦ 统计女员工的数量： 在“基础数据”工作表的“女性员工”处输入函数“=COUNTIF(员工档案表!C4:C38,"女")”。 第3章 通过 Excel 创建并处理电子表格

44 3.3.4 Excel中常用函数的应用 ⑧ 统计工资数据 在“基础数据”工作表中的相应单元格中依次输入下列函数以计算相关数 据：
基本工资总额：=SUM(基本工资)，基本工资列已被定义名称， 所以可直接被求和函数引用。 管理人员工资总额：=SUMIF(员工档案表!D4:D38,"管理",基本 工资)，用条件求和函数计算“部门”属于“管理”的所有人员的基 本工资总和。 平均基本工资：=AVERAGE(基本工资)。 本科生平均基本工资：=AVERAGEIF(员工档案表!I4:I38,"本科", 基本工资)，用条件求平均值函数计算“学历”为“本科”的所有人 员的平均基本工资。 最高基本工资：=MAX(基本工资) 最低基本工资：=MIN(基本工资) 第3章 通过 Excel 创建并处理电子表格

45 3.3.4 Excel中常用函数的应用 2）运用公式及函数完善1月工资表
在“1月工资表”中，需要利用函数和公式获取员工的姓名、所在的部门、员工的基础工资，并计算应付工资、应交个人所得税、实付工资等工资项目。 ① 获取员工姓名、部门和基础工资： 利用VLOOKUP函数从员工档案表中直接获取相应数据。 获取姓名：=VLOOKUP(B4,全体员工资料,2,FALSE) 获取部门：=VLOOKUP(B4,全体员工资料,4,FALSE) 获取基础工资：=VLOOKUP(B4,全体员工资料,14,FALSE) 第3章 通过 Excel 创建并处理电子表格

46 3.3.4 Excel中常用函数的应用 ② 计算应付工资 ③ 计算应纳税所得额 应付工资合计＝基础工资+奖金+补贴－扣除病事假。
应纳税所得额＝应付工资合计－社保费用－费用减除标准。应纳税所得额必须大于等于零，小于零则不用交税，需要用IF函数进行判断。 输入函数“=IF((I4-J4-3500)>0,I4-J4-3500,0)”； 也可以通过绝对引用基础数据表中的减除标准构建函数“=IF((I5-J5-基础数据!$F$12)>0,I5-J5-基础数据!$F$12,0)” 第3章 通过 Excel 创建并处理电子表格

47 3.3.4 Excel中常用函数的应用 ④ 计算个人所得税 ⑤ 计算实付工资
通过多级IF函数嵌套，可构建出个人所得税计算公式，并通过ROUND函数对计算结果保留2位小数。 个人所得税税率表可参见“基础数据”表中所列。 =ROUND(IF(K4<=1500,K4*0.03,IF(K4<=4500,K4* ,IF(K4<=9000,K4* ,IF(K4<=35000,K4* ,IF(K4<=55000,K4* ,IF(K4<=80000,K4* ,K4* )))))),2) ⑤ 计算实付工资 实付工资＝应付工资合计-扣除社保-应交个人所得税。 第3章 通过 Excel 创建并处理电子表格

48 3.3 Excel 公式和函数 3.3.5 公式与函数常见问题 在输入公式或函数的过程中，当输入有误时，单元格中常常会出现各种不同的错误结果。对这些提示的含义有所了解，有助于更好地发现并修正公式或函数中的错误。 1. 常见错误值列表 第3章 通过 Excel 创建并处理电子表格

49 表3.4 公式或函数中的常见错误列表 3.3 Excel 公式和函数 错误显示 说 明 #####
说 明 ##### 当某一列的宽度不够而无法在单元格中显示所有字符时，或者单元格包含负的日期或时间值时，Excel将显示此错误。 #DIV/0! 当一个数除以零 (0) 或不包含任何值的单元格时，Excel将显示此错误。 #N/A 当某个值不允许被用于函数或公式但却被其引用时，Excel 将显示此错误。 #NAME? 当Excel无法识别公式中的文本时，将显示此错误。例如，区域名称或函数名称拼写错误，或者删除了某个公式引用的名称。 #NULL! 当指定两个不相交的区域的交集时，Excel将显示此错误。交集运算符是分隔公式中的两个区域地址间的空格字符。 例如，区域A1:A2和C3:C5不相交，因此，输入公式 =SUM(A1:A2 C3:C5) 将返回 #NULL! 错误。 #NUM! 当公式或函数包含无效数值时，Excel将显示此错误。 #REF! 当单元格引用无效时，Excel将显示此错误。例如，如果删除了某个公式所引用的单元格，该公式将返回 #REF! 错误。 #VALUE! 如果公式所包含的单元格有不同的数据类型，则Excel将显示此错误。如果启用了公式的错误检查，则屏幕提示会显示“公式中所用的某个值是错误的数据类型”。通常，通过对公式进行较少更改即可修复此问题。

50 3.3.5 公式与函数常见问题 2. 审核和更正公式中的错误 1）打开或关闭错误检查规则 2）检查并依次更正常见公式错误
3.3 Excel 公式和函数 3.3.5 公式与函数常见问题 2. 审核和更正公式中的错误 1）打开或关闭错误检查规则 “文件”选项卡→“选项”命令→打开“Excel选项”对话框→从左侧类别列表中单击“公式”选项→在“错误检查规则”区域中，选中或清除某一检查规则的复选框 2）检查并依次更正常见公式错误 “公式”选项卡→“公式审核”组→“错误检查”按钮→ 检查并处理错误。 第3章 通过 Excel 创建并处理电子表格

51 3.3.5 公式与函数常见问题 3）通过“监视窗口”监视公式及其结果
3.3 Excel 公式和函数 3.3.5 公式与函数常见问题 3）通过“监视窗口”监视公式及其结果 使用“监视窗口”可以方便地在大型工作表中检查、审 核或确认公式计算及其结果，而无需反复滚动或定位 到工作表的不同部分。 “公式”选项卡→“公式审核”组→“监视窗口”按钮→“添 加监视”按钮→“添加”按钮，增加监视点。 第3章 通过 Excel 创建并处理电子表格

52 3.3 Excel 公式和函数 3.3.5 公式与函数常见问题 3. 公式中的循环引用 如果公式引用了自己所在的单元格，则无论是直接引用还是间接引用，该公式都会创建循环引用。默认情况下，如果发生循环引用，Excel就会报错。 1）定位并更正循环引用 发生循环引用现象→“公式”选项卡→“公式审核”组→“错误检查”按钮右侧的黑色箭头→“循环引用”命令→查看当前工作表中所有发生循环引用的单元格位置 第3章 通过 Excel 创建并处理电子表格

53 3.3.5 公式与函数常见问题 2）更改 Excel 迭代公式的次数使循环引用起作用
如果想要保留循环引用，则可以启用迭代计算，并确定公式重新计算的次数。 在发生循环引用的工作表中，依次单击“文件”选项卡→“选项”→“公式” →在“计算选项”区域中，选中“启用迭代计算”复选框→ 在“最多迭代次数”框中输入进行重新计算的最大迭代次数。 →“最大误差”框中输入两次计算结果之间可以接受的最大差异值。 第3章 通过 Excel 创建并处理电子表格

54 3.3.5 公式与函数常见问题 4. 追踪单元格以显示公式与单元格之间的关系
3.3 Excel 公式和函数 3.3.5 公式与函数常见问题 4. 追踪单元格以显示公式与单元格之间的关系 有时，当公式中包含引用单元格（被其他单元格中的公式引用的单元格）或从属单元格（包含引用其他单元格的公式的单元格）时，检查公式的准确性或查找错误的根源会很困难。 为了帮助检查公式，可以通过“追踪引用单元格”和“追踪从属单元格”功能以图形方式显示或追踪这些单元格与包含追踪箭头的公式之间的关系。 第3章 通过 Excel 创建并处理电子表格

55 3.3.5 公式与函数常见问题 1）显示某个单元格中公式的引用与被引用
3.3 Excel 公式和函数 3.3.5 公式与函数常见问题 1）显示某个单元格中公式的引用与被引用 提示：需要保证“文件”选项卡→“选项”→“高级”→“此工作簿的显示选项”下→“对于对象，显示”→“全部”单选项被选中，才可以执行追踪单元格操作。 追踪引用单元格：“公式”选项卡→“公式审核”组→“追踪引用单元格” 追踪从属单元格： “公式”选项卡→“公式审核”组→“追踪从属单元格” 第3章 通过 Excel 创建并处理电子表格

56 3.3.5 公式与函数常见问题 2）查看工作表中的全部引用关系 打开要查看的工作表，在一个空单元格中输入等号“=”。
3.3 Excel 公式和函数 3.3.5 公式与函数常见问题 2）查看工作表中的全部引用关系 打开要查看的工作表，在一个空单元格中输入等号“=”。 单击工作表左上角的“全选”按钮，按回车键Enter确认。 单击选择该单元格，在“公式”选项卡的“公式审核”组中，单击两次“追踪引用单元格”。 第3章 通过 Excel 创建并处理电子表格