2使用Excel的公式与函数（课件）《EXCEL在财务中的应用》（航空工业出版社）

使用Excel的公式与函数 项目二 主讲人：周颖 1 知识目标： ①了解Excel公式和函数的概念、运算符、单元格引用。 ②掌握公式和函数输入方法。 ③掌握会计人员常用函数的使用方法。 能力目标： ①学会运用Excel公式和函数处理数据 素养目标： ①通过Excel的计算自检功能，提醒自己应当时时进行自检，检查自己的行为、操作是否合规，出现不合规时要及时停止并改正，严格遵守财务人员准则。 ②通过小组合作，增强团结协作能力。 ③养成善于思考的意识。 学习目标 冀中职业学院 目录 Excel公式及其应用 任务2.1 冀中职业学院 VLOOKUP函数的使用 任务2.3 Excel常用函数 任务2.2 LEFT函数解决实际问题： 取出身份证号 任务2.4 去掉银行账号中的空格 任务2.6 隐藏电话号码中的某几位 任务2.5 计算交货日 任务2.8 数据分列中中文和数字分开 任务2.7 计算工作日 任务2.9 2.1.1 公式的概念 冀中职业学院 任务 2.1 Excel 公式及其应用 公式是 Excel中的重要工具，充分灵活地运用公式，可以实现数据处理的自动化，使我们的工作高效灵活。公式可以用来执行各种运算，公式由运算符、常量、单元格引用公式的概念 值、名称、工作表函数等元素组成。 1.运算符 运算符是进行数据计算的基础。 Excel 运算符包括算术运算符、比较运算符、文本运算符和引用运算符。 2.1.1 公式的概念 冀中职业学院 任务 2.1 Excel 公式及其应用 算术运算符包括“+”“-”“*”“/”“??“^”,含义依次为加、减、乘、除、百分数、乘方。它们的作用是完成基本的数学运算，结果为数值。 例如，在单元格中输入“=10+6^2”后按“回车键”确认，结果显示为“46”。 比较操作符包括“=”“>”“<”“>=”“<=”“～”,含义依次为等于、大于、小于、大于等于、小于等于、不等于。它们的作用是可以比较两个值，结果为一个逻辑值，不是“TRUE”就是“FALSE”。 例如，在单元格中输入“=10>9”,结果是“TRUE”。 文本运算符是连接符(&),符号两边均为文本型数据才能连接，连接一个或更多字符串以产生一个长文本，连接的结果仍是文本型数据。 例如，“=‘中华人民’&‘共和国’”的结果是“中华人民共和国”。 2.1.1 公式的概念 冀中职业学院 任务 2.1 Excel 公式及其应用 引用操作符包括冒号、逗号和空格。 冒号(:)是连续区域运算符，对两个引用之间包括两个引用在内的所有单元格进行引用。 如SUM(B5:C10),计算B5到C10的连续12个单元格之和； 逗号(,)是联合运算符，可将多个引用合并为一个引用。 如SUM(B5:B10,D5:D10),计算B列、D列共12个单元格之和； 空格是交叉运算符，取多个引用的交集为一个引用，该操作符在取指定行和列的数据时很有用。 如SUM(B5:B10 A6:C8),计算B6到B8三个单元格之和。 2.1.1 公式的概念 冀中职业学院 任务 2.1 Excel 公式及其应用 2.运算符的优先级 如果公式中同时用到了多个运算符，Excel 将按一定的顺序(优先级由高到低)进行运算，相同优先级的运算符，将从左到右进行计算。若是记不清或想指定运算顺序，可用小括号括起相应部分。 优先级由高到低依次为：括号、引用运算符、算术运算符、文本运算符、比较运算符。 每类运算符根据优先级计算，当优先级相同时，按照自左向右的规则计算。 2.1.2 公式的创建 冀中职业学院 任务 2.1 Excel 公式及其应用 (1)选中准备输入公式的单元格。 (2)输入等号“=”。 (3)在单元格或者编辑栏中输入公式的具体内容。输入运算符时，注意优先级别和前后数据类型，公式中不能有多余的空格。 (4)按“回车键”或者单击“输入”按钮确认，即完成公式输入。 在输入公式时需注意：第一，运算符必须是在英文半角状态下输入；第二，公式的运算尽量要用单元格地址，以便于复制公式。公式中的单元格地址可以用键盘输入，也可以单击相应的单元格得到相应的单元格地址。 如果需要修改公式，则可以先单击包含公式的单元格，在编辑栏中修改；也可以双击该单元格，直接在单元格中修改。 2.1.3 公式出错信息与处理 冀中职业学院 任务 2.1 Excel 公式及其应用 错误代码 含义 解决办法 ### 当公式的运算结果或者输入的数据太长，单元格宽度不够时，会显示该代码 加宽单元格可以解决该问题 NAME? 当公式中有不正确或不能识别的字符时，会显示该代码 检查公式中是否包含了不正确的字符 #DIV/0 公式中引用了空单元格或者单元格为零的值作为除数，会显示该代码 检查公式中是否存在分母为零的情况 #N/A 当公式中引用的单元格没有可用数据时，会显示该代码 检查公式中所引用的单元格是否有可用数据 #NUM! 当公式中包含的函数参数不正确时，会显示该代码 检查公式中函数的参数数量、类型是否正确 在单元格中输入公式后，Excel会对其进行检查，如果发现存在问题，将对单元格显示错误代码，提示检查所输入的公式，并进行相应的修改。Excel 中常见的错误代码含义如表 所示。 2.1.4 单元格的引用 冀中职业学院 任务 2.1 Excel 公式及其应用 单元格的引用是把单元格的数据和公式联系起来，标识工作表中单元格或者单元格区域，指明公式中使用数据的位置。在Excel中，单元格的引用有三种方式：相对引用、绝对引用和混合引用。默认情况下公式是相对引用。 1.相对引用 指引用的单元格的相对位置。公式中的相对单元格引用(例如A1)是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。 例如，C1单元格有公式“=A1+B1”,将公式复制到C2单元格时变为“=A2+B2”,将公式复制到D1单元格时变为“=B1+C1”。 2.1.4 单元格的引用 冀中职业学院 任务 2.1 Excel 公式及其应用 2.绝对引用 单元格中的绝对单元格引用(例如$A$1)是指总是引用指定位置的单元格。对单元格的引用不会随着公式位置的改变而改变。如果多行或多列地复制公式，绝对引用将不做调整。 例如，C1单元格有公式“=$A$1+$B$1”,将公式复制到C2单元格时，公式仍为“=$A$1+$B$1”,将公式复制到D1单元格时公式仍为“=$A$1+SB$1”。 2.1.4 单元格的引用 冀中职业学院 任务 2.1 Excel 公式及其应用 3.混合引用 混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用$A1、$B1等形式，绝对引用行采用A$1、B$1 等形式。 如果公式所在单元格的位置的改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不做调整。 例如，C1单元格有公式“=SA1+B$1”,当将公式复制到C2单元格时变为“=$A2+B$1”,当将公式复制到D1单元格时变为：=$A1+C$1。 小技巧：如果要改变引用地址的方式，可以使用F4键在相对—绝对—混合引用之间循环切换。 2.2.1 函数的基本概念 冀中职业学院 任务 2.2 Excel 常用函数 Excel的函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。Excel提供了300多个函数，有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。 函数的基本格式是：函数名(参数1,参数 2,…)。 (1)函数名代表了该函数的功能，例如SUM函数计算所有参数数值的和，AVERAGE 函数计算所有参数的算术平均值。 (2)不同类型的函数要求不同类型的参数，可以是数值、文本、单元格地址等。 2.2.2 函数的输入 冀中职业学院 任务 2.2 Excel 常用函数 方法一：直接输入。直接输入的方法是选中单元格，输入“=”号，然后按照函数的语法格式输入。例如，要求在A5单元格计算A1到A4的和。 操作步骤：选中A5单元格，输入“=sum(A1:A4)”即可。 方法二：使用工具按钮 。例如，要求在B5单元格计算B1到B4的最大值。 操作步骤：选中B5单元格，在名称框右侧的工具栏中选择 左，在“插入函数”对话框中选择相应的函数“MAX”,可用鼠标选择B1:B4单元格区域，单击“确定”按钮即可。 方法三：使用“公式”选项卡中“插入函数”按钮。例如，要求在B5单元格计算B1到B4的平均值。 操作步骤：选中B5单元格，单击“公式”选项卡中“插入函数”按钮，弹出“插入函数”对话框，在“插入函数”对话框中选择相应的函数“AVERAGE”,可用鼠标选择B1:B4单元格区域，单击“确定”按钮即可。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 1. SUM 函数 主要功能：计算所有参数数值的和。 语法格式：SUM(Numberl, Number2,…)。 参数说明：Numberl、Number2 代表需要计算的值，可以是具体的数值、引用的单元格(区域)、逻辑值等。 应用举例：求出C2:C8单元格区域的和。 如图所示，在C11单元格中输入公式“=SUM(C2:C8)”,确认后即可求出“岗位工资总计”。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 2.AVERAGE 函数 主要功能：求出所有参数的算术平均值。 语法格式：AVERAGE(numberl, number2,…)。 参数说明：numberl、number2 代表需要求平均值的数值或引用单元格(区域),参数不超过30个。 应用举例：求出B1:B7单元格区域的平均值。在B8单元格中输入公式“=AVERAGE(B1:B7)”,如图1所示，确认后，即可求出B1:B7单元格区域的平均值。 特别提醒：如果引用区域中包含“0”值单元格，则计算在内如图1所示；如果引用区域中包含空白或字符单元格，则不计算在内如图2所示。 图1 图2 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 3.SUMIF 函数 主要功能：计算符合指定条件的单元格区域内的数值的和。 语法格式：SUMIF(Range, Criteria, Sum_Range)。 参数说明：Range代表条件判断的单元格区域；Criteria为指定条件表达式；SumRange 代表需要计算的数值所在的单元格区域。 应用举例：求出男性职工岗位工资的和，如图所示，在C9单元格中输入公式“=SUMIF(B2:B8,"男",C2:C8)”,确认后即可求出男职工岗位工资合计。 特别提醒：如果把上述公式修改为“=SUMIF(B2:B8,"女",C2:C8)”,确认后即可求出女职工岗位工资合计。其中“男”和“女”由于是文本型的，需要放在英文状态下的双引号("男"、"女")中。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 4.MAX 函数 主要功能：求出一组数中的最大值。 语法格式：MAX(numberl,number2,…)。 参数说明：numberl,number2 代表需要求最大值的数值或引用单元格(区域),参数不超过30个。 应用举例：求出B2:B8单元格区域的最大值。在B9单元格中输入公式“=MAX(B2:B8)”,如图2-2-5所示，确认后，即可求出 B2:B8单元格区域中的最大值。 特别提醒：如果参数中有文本或逻辑值，则忽略。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 5.MIN 函数 主要功能：求出一组数中的最小值。 使用格式：MIN(number1,number2,…) 参数说明：numberl,number2 代表需要求最小值的数值或引用单元格(区域),参数不超过30个。 应用举例：求出B2:B8单元格区域的最小值。在B10单元格中输入公式“=MIN(B2:B8)”,如图2-2-6所示，确认后，即可求出 B2:B8 单元格区域中的最小值。 特别提醒：如果参数中有文本或逻辑值，则忽略。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 6.LEFT 函数 主要功能：从一个文本型数据的第一个字符开始，截取指定数目的字符。 语法格式：LEFT(text, num_chars)。 参数说明：text代表要截取字符的字符串；num_chars 代表给定的截取数目。 应用举例：取出C2单元格“薛玉洁”中的姓氏“薛”。在D2单元格中输入公式“=LEFT(C2,1)”,确认后即显示出“薛”的字符，如图所示。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 7.RIGHT 函数 主要功能：从一个文本型数据的最后一个字符开始，截取指定数目的字符。 语法格式：RIGHT(text, num_chars)。 参数说明：text代表要截取字符的字符串；num_chars 代表给定的截取数目。 应用举例：取出B1单元格“钓鱼岛是中国的”中取出“中国的”。在C1单元格中输入公式“=RIGHT(B1,3)”,代表从B1单元格中从右边取，取出3个字，确认后即显示出“中国的”的字符，如图2-2-8 所示。 特别提醒：Num_chars参数必须大于或等于0,如果忽略，则默认其为1;如果 num_chars 参数大于文本长度，则函数返回整个文本。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 8.MID 函数 主要功能：从一个文本型数据的指定位置开始，截取指定数目的字符。 语法格式：MID(text, start_num, num_chars)。 参数说明：text代表一个文本型数据；start_num表示指定的起始位置；num_chars表示要截取的数目。 应用举例：从A2单元格的身份证号中提取出生年月日。在B2单元格中输入公式“=MID(A2,7,8)”,如图所示，“A2”指身份证号位于A2位置，“7”是指从身份证号中第7个位置开始提取，“8”是指按顺序一共提取8个数字，结果为“19740319”。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 9.IF函数 主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。 语法格式：=IF(Logical, Value_if_true, Value_if_false)。 参数说明：Logical代表逻辑判断表达式；Value_if_true 表示当判断条件为逻辑“真(TRUE)”时的显示内容，如果忽略返回“TRUE”;Value_if_false 表示当判断条件为逻辑“假(FALSE)”时的显示内容，如果忽略“FALSE”。 应用举例： (1)显示 B2:B11成绩的及格情况。在C2单元格中输入公式“=IF(B2<60,"不及格",”及格“)”,如图所示，“B2<60”代表判断表达式，判断是否小于60,如果满足条件，则返回“不及格”;否则，返回“及格”。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 (2)显示 B2:B11成绩的等级情况。60分以下显示“差”,60~70分显示“可”,70~90分显示“良”,90分以上显示“优”。这可以使用IF的嵌套。在C3单元格中输入公式“=IF(B2<60,"差",IF(B2<70,"可",IF(B2<90,”良","优“)))”,结果如图所示。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 10.COUNTIF 函数 主要功能：统计某个单元格区域中符合指定条件的单元格数目。 语法格式：COUNTIF(Range, Criteria) 参数说明：Range代表要统计的单元格区域；Criteria 表示指定的条件表达式。 应用举例：利用COUNTIF函数统计各个分数段的人数，如图1所示。D2单元格输入“=COUNTIF(B2:B11,">=90")”； 图1 D3 单元格输入=COUNTIF(B2:B11,">=70")COUNTIF(B2:B11,">=90"); D4单元格输入=COUNTIF(B2:B11,">=60")-COUNTIF(B2:B11,">=70"); D5单元格输入=COUNTIF(B2:B11,"<60"),就可以计算出各分数段的人数了，公式如图2所示。 图2 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 11.RANK 函数 主要功能：返回某一数值在一列数值中的相对于其他数值的排位。 语法格式：RANK(Number, ref, order)。 参数说明：Number代表需要排序的数值；ref代表排序数值所处的单元格区域；order代表排序方式参数(如果为“0”或者忽略，则按降序排名，即数值越大，排名结果数值越小；如果为非“0”值，则按升序排名，即数值越大，排名结果数值越大)。 应用举例：计算总分排名。在C2单元格中输入公式“=RANK(B2,$B$2:$B$10,0)”,确认后即可得出1号同学在全班成绩中的排名结果，如图所示。在上述公式中，Number参数采取了相对引用形式，而让ref参数采取了绝对引用形式(增加了一个“$”符号),这样设置后，选中C2单元格，将鼠标移至该单元格右下角，显示为“十”填充柄时，按住左键向下拖拉，即可将上述公式快速复制到C列下面的单元格中，完成其他同学总分的排名统计。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 12.LEN 函数 主要功能：统计文本型数据中字符数目。 语法格式：LEN(text)。 参数说明：text 表示要统计的文本字符串。 应用举例：假定A1单元格中保存了“我今年40岁”的字符串，我们在B1单元格中输入公式：“=LEN(A1)”,确认后即显示出统计结果“6”,如图所示。 特别提醒：LEN要统计时，无论是全角字符，还是半角字符，每个字符均计为“1”;与之相对应的一个函数——LENB,在统计时半角字符计为“1”,全角字符计为“2”。 13.ROW 函数 主要功能：求单元格或连续区域的行号。 语法格式：ROW(reference)。 参数说明：reference 表示需要求行号的单元格或连续区域。如果省略，则返回所在单元格的行号。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 14.COLUMN 函数 主要功能：求单元格或连续区域的列号。 语法格式：COLUMN(reference)。 参数说明：reference 表示需要求列号的单元格或连续区域。如果省略，则返回所在单元格的列号。 应用举例：在A1单元格输入“=ROW(A3)”,B1单元格输入“=COLUMN(A3)”,A2单元格输入“=ROW()”,B2单元格输入“=COLUMN( )”,如图1所示，结果如图2所示。 图1 图2 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 15.ROUND 函数 主要功能：按指定的位数对数值进行四舍五入。 语法格式：ROUND(number, num_digits)。 参数说明：number表示需要进行四舍五入的单元格，如果单元格内容非数值型，则返回错误；num_digits 表示需要保留的小数位数。 应用举例：“=ROUND(8.699,1)”,结果为8.7。 16.MOD 函数 主要功能：返回两数相除的余数。结果的正负号与除数相同。 语法格式：MOD(number, divisor)。 参数说明：number表示被除数；divisor表示除数。 应用举例：“=MOD(6,2)”,结果为“0”;“=MOD(7,2)”,结果为“1”。 17.YEAR 函数 主要功能：求出指定日期或引用单元格中的日期的年。 语法格式：YEAR(serial_number)。 参数说明：serial_number 代表指定的日期或引用的单元格 应用举例：输入公式：“=YEAR("2003-12-18")”,确认后，显示出“2003”。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 18.NOW 函数 主要功能：给出当前系统日期和时间。 语法格式：NOW(. 参数说明：该函数不需要参数。 应用举例：输入公式：“=NOW()”,确认后即可显示出当前系统日期和时间。如果系统日期和时间发生了改变，只要按一下 F9功能键，即可让其随之改变。 特别提醒：显示出来的日期和时间格式，可以通过单元格格式进行重新设置。 19.TEXT 函数 主要功能：根据指定的数值格式将相应的数字转换为文本形式。 语法格式：TEXT(value, format_text)。 参数说明：value代表需要转换的数值或引用的单元格；format_text 为指定文字形式的数字格式。 应用举例：如果A1单元格中保存有数值“1280.45”,我们在A2单元格中输入公式“=TEXT(A?,"S0.00")”,确认后显示为“$1280.45”。如果A3单元格中保存有文本字符串“19740319”,我们在A4单元格中输入公式“=TEXT(A3,“0000-00-00”)”,确认后显示为“1974-03-19”。 2.2.3 会计人员常用函数简介 冀中职业学院 任务 2.2 Excel 常用函数 20.DB函数 主要功能：用固定余额递减法，返回指定期间内某项固定资产的折旧值。 语法格式：DB(cost,salvage,life, period, month) 参数说明：cost为资产原值。salvage为资产在折旧期末的价值(也称为资产残值)。life 为折旧期限(有时也称作资产的使用寿命)。period 为需要计算折旧值的期间，period必须使用与life相同的单位。month 为第一年的月份数，如省略，则假设为12。 举例：现有某辆轿车，价值10万元，总的使用时间为20年，报废后的资产残值为10000元，公司使用的时间年限为15年。求在今年内该车的折旧值。如果 B9单元格 中保存cost为资产原值100000,在F9单元格中保存资产残值为10000,在D8单元格中保存已计提年份为15,在B12单元格输入公式“=DB(B9,F9,20,15)”,确认后显示为“2166”。即在今年内的折旧为2166元。 2.3.1 VLOOKUP 函数简介 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 VLOOKUP 函数是 Excel 中的一个纵向查找函数，它与LOOKUP 函数和 HLOOKUP 函数属于一类函数，在工作中都有广泛应用，如可以用来核对数据、多个表格之间快速导入数据等功能。功能是按列查找，最终返回该列所需查询序列所对应的值；与之对应的 HLOOKUP是按行查找的。 语法格式：VLOOKUP(lookup_value,table_array, col_index_num, range_lookup)。 主要功能：给定一个查找的目标，它就能从指定的查找区域中查找返回想要查找的值。 参数说明：它有4个参数，第1个参数是查找目标，第2个参数是查找范围，第3个参数是返回值的列数，第4个参数是精确或模糊查找。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 1.第一种用法：VLOOKUP 函数的初级使用 一个实例问题如图所示。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 操作步骤： 第1步：定位光标，单击B2 单元格； 第2步：插入函数，在全部函数中找到“VLOOKUP 函数”; 第3步：输入VLOOKUP函数参数，参数设置如图2-3-2所示，公式为“=VLOOKUP(A2,表一!$B$1:SD$19,3,0)”。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 (1)第1个参数是查找目标，要查找的是曹丽娜，单击B2; (2)第2个参数是查找范围，要从表一当中查找曹丽娜的年龄；查找范围需要注意两个问题：第1个问题是查找目标在范围里边必须是第1列，这里要从B列开始选中；第2个问题就是选中的范围必须包括所要返回的“年龄”列，这里选中“表一!$B$1:$D$19”,注意是绝对引用； (3)第3个参数是要返回值的列数，年龄在范围里面是第3列，输入“3”; (4)第4个参数是精确或模糊查找。如果是0的话是精确匹配，如果是1的话是模糊匹配。这里输入“0”。 第4步：其他行自动填充即可。最终效果如图所示。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 2.第二种用法：VLOOKUP 函数同时返回多列值 VLOOKUP(查找目标，查找范围，返回值的列数，精确 OR模糊匹配);VLOOKUP函数的第三个参数是查找返回值所在的列数，如果需要查找返回多列时，这个列数值需要一个个的更改，比如返回第2列的，参数设置为“2”,如果需要返回第3列的，就需要把值改为“3”。 列数不多的情况，当然可以手动修改，那如果是几十列呢?能不能让第3个参数随着函数的位置不同，自动变更?即向后复制时自动变为2,3,4,5,… 引入新的函数：Column。 COLUMN 函数可以返回指定单元格的列数，比如： “=COLUMN(A1)”返回值为“1”(A1所在的列为第一列); “=COLUMN(B3)返回值“2”(B3所在的列为第二列); 使用COLUMN 函数的相对引用，公式“=COLUMN(A1)”向右复制时，“A1”会变成“B1”“C1”“D1”…这样我们用COLUMN 函数就可以转换成数字“1”“2”“3”“4”… 注：这里的关键是将VLOOKUP 函数的第三个参数设置为动态变化的。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 如图所示的实例，相比第一实例所示表格，不仅要返回这些人的年龄，还要返回他们的性别、学历、职务类别。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 操作步骤： 第1步：定位光标，单击B2单元格； 第2步：插入函数，在全部函数中找到“VLOOKUP 函数”; 第3步：输入VLOOKUP函数参数，参数设置如图所示， 公式为“=VLOOKUP($A2,表一!$B$1:$F$19,COLUMN(B1),0)”。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 公式说明： (1)$A2:这里只有列前边有$符号，意味着列是绝对引用，行是相对引用。这样就能实现在向右复制时，列数保持不变(一直是A列),行数递增变化($A2→$A3→$A4) (2)$B$1:$F$19:查找范围的引用区域，行和列均为绝对引用。确保函数在复制过程中，查找的范围不会变更。多数情况下，查找范围都是需要固定的。 (3)COLUMN(B1):在性别这一列的函数中，第三个参数值需要设定为“2”(因为性别在查找区域中处于第二列),向右复制需要递增。 所以关键是 COLUMNO的第一个返回值是2即可，这里的参数可以是B列的任一单元格。 第4步：其他行和列，向右向下自动填充即可，最终结果如图所示。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 3.第三种用法：VLOOKUP函数逆序查找 VLOOKUP函数的查找目标一定要在查找范围的首列，那么如果要返回的值不在首列，如图所示。需要根据身份证号来查找姓名，但是现在身份证号在要查找的范围中不是首列。这时就会用到逆序查找。利用IF的数组用法，将A列和B列进行“调序”。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 操作步骤： 第1步：定位光标，单击B2 单元格； 第2步：插入函数，在全部函数中找到“VLOOKUP 函数”; 第3步：输入VLOOKUP函数参数，参数设置如图2-3-8所示，公式为“=VLOOKUP(A2,IF({1,0},个人档案!B:B,个人档案!A:A),2,0)”。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 公式说明： “IF({1,0},个人档案!B:B,个人档案!A:A)”的数据用法，当条件为1时，返回第一个结果“个人档案!B:B”;当条件为0时，返回第二个结果“个人档案!A:A”。这里“{1,0}”两个条件是同时判断的，所返回的两个结果组成一个B列数据在前A列数据在后的数组，以利于 VLOOKUP搜索。VLOOKUP 函数在上面生成的数组首列(B列数据)查找，返回数组第2列(单元格区域中的A列)的数据。 第4步：其他行向下自动填充即可，最终结果如图所示。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 4.第四种用法：VLOOKUP函数使用通配符 如图所示，现在有这么多的公司名称，需要从中查找与“三川实业”有关的公司和地址，但是公司名称并不完整，操作步骤如下。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 第1步：定位光标，单击B2单元格； 第2步：插入函数，在全部函数中找到“VLOOKUP 函数”; 第3步：输入VLOOKUP函数参数，参数设置如图1所示， 公式为“=VLOOKUP(A2&"*",数据源!B:E,4,0)”; 第4步：其他行向下自动填充即可，最终结果如图2所示。 图1 图2 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 5.第五种用法：VLOOKUP函数的模糊匹配 在一般情况下 VLOOKUP函数是使用精确查找，这一次第4项参数必须写“0”,不能使用默认。 如果使用模糊查找，第4项写“1”或者默认。推荐务必将数据按被查找的第一列进行从小到大的排序，此时所有超过某项而没达到下一项的都会被匹配到这项。 什么时候用模糊匹配呢?一般是用于有数值区间时。比如对于个人所得税税率的计算，销售提成的计算，成绩等级的划定。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 下面以个人所得税计算为例讲解 VLOOKUP 函数的模糊匹配。 现在看一下新个税税率表，如图1所示，将其转成如图2所示的表。 图1 图2 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 操作步骤： 第1步：定位光标，单击 B2 单元格； 第2步：插入函数，在全部函数中找到“VLOOKUP 函数”; 第3步：输入VLOOKUP函数参数，参数设置如图所示，公式为“=VLOOKUP(B13,$B$2:$E$8,3,1)”。 2.3.2 VLOOKUP 函数五种常用用法 冀中职业学院 任务 2.3 VLOOKUP 函数的使用 第4步：其他行向下自动填充即可，最终结果如图所示。 2.4 LEFT 函数解决实际问题：取出身份证号 冀中职业学院 任务2.4 LEFT 函数解决实际问题：取出身份证号 实例：如何把身份证号从单元格中取出来。如图所示。 2.4 LEFT 函数解决实际问题：取出身份证号 冀中职业学院 任务2.4 LEFT 函数解决实际问题：取出身份证号 下面介绍两种方法，一种方法是用LEFT函数的方法，一种是用数据分列的方法。 第一种方法：使用LEFT函数把它取出来。 操作步骤： 第1步，定位光标，单击 B2 单元格； 第2步，单击插入函数，LEFT函数属于文本函数，选择“文本”函数，找到“LEFT函数”,单击“确定”,如图1所示。第一个参数，单击A2单元格；第二个参数，身份证号是18位，输入“18”,单击“确定”。如图2所示。 图1 图2 2.4 LEFT 函数解决实际问题：取出身份证号 冀中职业学院 任务2.4 LEFT 函数解决实际问题：取出身份证号 第3步，其他数据自动填充，就可以完成了。效果如图所示。 2.4 LEFT 函数解决实际问题：取出身份证号 冀中职业学院 任务2.4 LEFT 函数解决实际问题：取出身份证号 第二种方法：使用数据分列的方法 操作步骤： 第1步：选中A2:A10 单元格区域。 第2步：单击“数据”→“分列”,单击“固定宽度”。因为身份证号都是18位，数据宽度一样，如图所示。 2.4 LEFT 函数解决实际问题：取出身份证号 冀中职业学院 任务2.4 LEFT 函数解决实际问题：取出身份证号 第3步：单击“下一步”,在身份证号的最后一位的后面单击建立分列线。要建立分列线，请在要建立分列线处单击鼠标；要清除分列线，请双击分列线；要移动分列线位置，请按住分列线并拖至指定位置，如图所示。 2.4 LEFT 函数解决实际问题：取出身份证号 冀中职业学院 任务2.4 LEFT 函数解决实际问题：取出身份证号 第4步：单击“下一步”。更改身份证号这列的数据格式为“文本”格式；因为汇总那一列不需要导出来，选中“汇总”这一列，单击“不导入此列(跳过)”;目标区域单击C2单元格；单击“完成”,如图1所示。最终效果如图2所示。 图1 图2 2.5 隐藏电话号码中的某几位 冀中职业学院 任务 2.5 隐藏电话号码中的某几位 为了保护大家的隐私，有时需要隐藏手机号码、电话号码、身份证号等当中的某几位。这里需要使用 Replace 函数，此函数的功能就是用来替换字符串中的某些特定文本。 语法格式：REPLACE(old_text, start_num, num_bytes, new_text); 主要功能：用来替换字符串中某些特定文本； 参数说明：old_text是要替换其部分字符的文本；start_num 是要用 new_text 替换的old_text 中字符的位置；num_bytes 是希望 REPLACEB 使用 new_text 替换 old_text 中字节的个数；new_text是要用于替换old_text 中字符的文本。 2.5 隐藏电话号码中的某几位 冀中职业学院 任务 2.5 隐藏电话号码中的某几位 隐藏电话号码中的中间4位操作步骤： 第1步：定位光标，单击C2单元格。 第2步：单击“插入函数”,在“全部”函数中找到“Replace”函数，如图所示。 2.5 隐藏电话号码中的某几位 冀中职业学院 任务 2.5 隐藏电话号码中的某几位 第3步：输入replace 函数参数。replace 函数本意就是替代的意思，将一个字符串中的部分字符串用另外一个字符串来替代，第一个参数是旧的文本，单击B2;第二个参数start 是指从第几位开始，从第4位开始，输入“4”;第三个参数是指隐藏几位，隐藏4位，输入“4”;第四个参数是指用什么来替代，用4个星号****替代，单击“确定”,参数如图所示，编辑栏上的公式为“=REPLACE(B2,4,4,"****")”。 2.5 隐藏电话号码中的某几位 冀中职业学院 任务 2.5 隐藏电话号码中的某几位 第4步：自动填充。把光标放到C2单元格的右下角，当光标变成黑色的填充柄时双击即可自动填充。最终效果如图所示。 2.6 去掉银行账号中的空格 冀中职业学院 任务2.6 去掉银行账号中的空格 银行账号一般是19位，有的时候银行账号是4个一组4个一组显示的，如果需要去掉银行账号中的空格，应该如何做呢?如图1所示。 遇到这样的问题的时候，把所有的空格都去掉，首先可能想到的是用“查找替换”的方法，“查找内容”这里输入“空格”,“替换为”这里不用输入，单击“全部替换”。它会以科学记数法的方式来显示，而且超过15位的数字都用“0”来替代了，把数据都改变了，如图2所示。这样的结果肯定不对。 图1 图2 2.6 去掉银行账号中的空格 冀中职业学院 任务2.6 去掉银行账号中的空格 1.第一种方法：数据分列+CONCATENATE字符串连接函数 操作步骤： 第1步：数据分列。 (1)选中需要处理的数据，选中A列； (2)单击“数据”→“分列”; (3)单击“分隔符号”,单击“下一步”; (4)单击分隔符号为“空格”,单击“下一步”; (5)单击第2列数据，单击“列数据格式”,更改为“文本”格式。这里一定要注意，因为第2列数据是以0开头的，如果不更改为文本格式，前面的0在分列后不显示。单击目标区域“=$B$1”,如图所示。 2.6 去掉银行账号中的空格 冀中职业学院 任务2.6 去掉银行账号中的空格 (6)单击“完成”,数据分列效果如图所示。 2.6 去掉银行账号中的空格 冀中职业学院 任务2.6 去掉银行账号中的空格 第2步：使用文本连接函数 CONCATENATE。 (1)单击“G1”单元格，单击“插入函数”,在“全部”函数中找到“CONCATENATE”函数。此函数的功能是把多个文本字符串合并成一个。 (2)输入CONCATENATE函数。在“text1”参数中输入“B1”,在“text2”参数中输入“C1”,在“text3”参数中输入“D1”,在“text4”参数中输入“E1”,在“text5”参数中输入“F1”,如图所示。单击“确定”。编辑栏上的公式为“=CONCATENATE(B1,C1,D1,E1,F1)”。 2.6 去掉银行账号中的空格 冀中职业学院 任务2.6 去掉银行账号中的空格 (3)自动填充。把光标放到G1单元格的右下角，当光标变成黑色的填充柄时双击即可自动填充。最终效果如图所示。 2.6 去掉银行账号中的空格 冀中职业学院 任务2.6 去掉银行账号中的空格 2.第二种方法：使用 SUBSTITUTE 函数 语法格式：SUBSTITUTE(text, old_text, new_text,[instance_num])。 主要功能：在某一文本字符串中替换指定的文本。 参数说明：text是需要替换其中字符的文本，或是含有文本的单元格引用；old_text是需要替换的旧文本；newtext用于替换 oldtext 的文本；instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text;如果指定了instance_num,则只有满足要求的old_text 被替换；如果缺省则将用new_text替换TEXT中出现的所有old_text。 2.6 去掉银行账号中的空格 冀中职业学院 任务2.6 去掉银行账号中的空格 操作步骤： 第1步：定位单元格，单击B1单元格。 第2步：单击“插入函数”,在全部函数中找到函数“SUBSTITUTE”。 第3步：设置SUBSTITUTE 函数的参数。 (1)第1个参数 text输入要处理的字符串，单击A1单元格。 (2)第2个参数输入旧字符串，这里输入空格。 (3)第3个参数输入新字符串，直接输入英文状态下的一对双引号，在双引号之间什么都不输入。 (4)第4个参数，指如果旧字符串出现多次替换几次，因为这里需要全部空格都替换，所以省略第4个参数就是全部替换。如图所示，编辑栏公式“=SUBSTITUTE(A1,"","")”。 2.6 去掉银行账号中的空格 冀中职业学院 任务2.6 去掉银行账号中的空格 第4步，自动填充。把光标放到B1单元格的右下角，当光标变成黑色的填充柄时双击即可自动填充其余行。最终效果如图所示。 2.7 数据分列中中文和数字分开 冀中职业学院 任务 2.7 数据分列中中文和数字分开 现在有一个这样的实际的问题，姓名和电话写在一起了，后面的电话并不是严格意义上的电话，有的是电话号码，有的是QQ号码，如图所示。现在需要把这个电话和姓名分开，应该如何操作呢? 这个问题需要用LEFT函数、RIGHT函数、LEN 函数、LENB 函数这几个函数组合来解决，在项目二前面介绍过LEFT函数、RIGHT函数、LEN函数。现在介绍一下 LEN和 LENB 函数。 2.7 数据分列中中文和数字分开 冀中职业学院 任务 2.7 数据分列中中文和数字分开 1. LEN 函数 语法格式：LEN(Text)。 主要功能：返回文本的字符个数。 参数说明：text为必需参数，表示要查找其长度的文本，空格将作为字符进行计数。 2.LENB 函数 语法格式：LENB(Text)。 主要功能：返回文本的字节个数。 参数说明：text为必需参数，表示要查找其长度的文本，空格将作为字符进行计数。 3.LEN 和 LENB函数的区别 LEN 和 LENB都用于返回文本的长度。LEN返回文本的字符个数，LENB返回文本的字节个数。 函数 LEN 始终将每个字符(不管是单字节还是双字节)按1计数；函数LENB会将每个双字节字符按2计数。比如汉字就是双字节数，在 LENB 函数中就会按照2计数，在 LEN 函数就会按照1计数。 2.7 数据分列中中文和数字分开 冀中职业学院 任务 2.7 数据分列中中文和数字分开 案例操作步骤如下。 第1步：定位光标，单击B2 单元格。 第2步：在B2单元格中输入公式“=LEFT(A2,(LENB(A2)-LEN(A2)))”,如图1所示，就返回了汉字。LENB(A2)计算的是字节个数，也就是汉字是按照2计算的；LEN(A2)计算的是字符个数，也就是汉字是按照1计算的；“LENB(A2)-LEN(A2)”得出的就是汉字个数，再用LEFT函数把汉字取出就可以了。 第3步：自动填充。把光标放到B2单元格的右下角，当光标变成黑色的填充柄时双击即可自动填充其余行。取出汉字效果如图2 所示。 第4步：在C2单元格中输入公式“=RIGHT(A2,(LEN(A2)*2-LENB(A2)))”,如图3所示，就返回了案例中的数字。 图1 图2 图3 2.7 数据分列中中文和数字分开 冀中职业学院 任务 2.7 数据分列中中文和数字分开 第5步：自动填充。把光标放到C2单元格的右下角，当光标变成黑色的填充柄时双击即可自动填充其余行。最终效果如图所示。 从“姓名/电话”中减去“姓名”,文本相加可以使用文本连接符&,但是没有文本“减”的符号。在这里可以使用SUBSTITUTE函数。 2.7 数据分列中中文和数字分开 冀中职业学院 任务 2.7 数据分列中中文和数字分开 语法格式：SUBSTITUTE(text, old_text, new_text, instance_num)。 主要功能：将字符串中的部分字符替换成新字符 参数说明：text 为需要替换其中字符的文本，或对含有文本的单元格的引用；old_text为需要替换的旧文本；new_text 用于替换 old_text 的文本；instance_num 为一数值，用来指定以 new_text 替换第几次出现的old_text。如果指定了 instance_num,则只有满足要求的 old_text 被替换；否则将用 new_text 替换text中出现的所有 old_text。 在此案例中，在D2单元格中插入SUBSTITUTE函数，此函数的参数设置如图所示，编辑栏中公式为“=SUBSTITUTE(A2,B2,"")”,从公式中可以看出，新文本为空，公式的含义是把B2单元格中的数据用“空”来替代，就相当于减去了汉字。 2.8 计算交货日 冀中职业学院 任务 2.8 计算交货日 某公司4月1号与客户签订了一份购销合同，合同规定10个工作日之后交货，如何计算哪一天交货呢?如图所示。这个问题是不是需要拿着日历数一下呢? 案例提到的情况需要用到 WORKDAY 函数。 2.8 计算交货日 冀中职业学院 任务 2.8 计算交货日 1.WORKDAY 函数简介 语法格式：WORKDAY(start_date, days, [holidays])。 主要功能：返回在某日期(起始日期)之前或之后、与该日期相隔指定工作日的某一日期的日期值。(工作日不包括周末和专门指定的假日。)。 参数说明：start_date为开始日期；days参数为 start_date 之前或之后不含周末及节假日的天数；days 为正值将生成未来日期；为负值生成过去日期；holidays为可选参数，专门指定的假期。 使用场景：一般在计算发票的到期日、预期交货时间或工作天数时，可以使用WORKDAY 来扣除周末或者假日。 2.8 计算交货日 冀中职业学院 任务 2.8 计算交货日 案例操作步骤： 第1步：定位光标，单击B2单元格； 第2步：单击“插入函数”,在全部函数中找到“WORKDAY”函数； 第3步：设置WORKDAY函数的参数，如图所示，编辑栏中公式为“=WORKDAY(A2,10)”。 如果周六周日休息的话，直接使用WORKDAY 函数。 如果规定只有周日休息的话，就不能使用这个函数了，要用另一个函数 WORKDAY.INTL函数。 这个函数是 WORKDAY 函数的双胞胎函数，或者说是它的一个扩展函数。 2.8 计算交货日 冀中职业学院 任务 2.8 计算交货日 2.WORKDAY.INTL 函数的用法 语法格式：WORKDAY.INTL(start_date, days,[weekend], [holidays])。 主要功能：返回在某日期(起始日期)之前或之后、与该日期相隔指定工作日的某一日期的日期值(工作日不包括周末和专门指定的假日)。 参数说明：从函数的参数上看，WORKDAY.INTL函数只是多了[weekend]也就是周末时间的设置，周末时间对照表如图1所示。在使用上，WORKDAY.INTL函数可以实现 WORKDAY 函数的所有功能，反之则不然。 看这个案例，如图2所示，项目如果周六周日休息，使用WORKDAY 函数；如果只有周日休息，则需要使用 WORKDAY.INTL 函数。 图1 图2 2.8 计算交货日 冀中职业学院 任务 2.8 计算交货日 在B3单元格插入函数WORKDAY.INTL,函数参数设置如图所示， 公式为“=WORKDAY.INTL(B1,B2,11)”。 2.9 计算工作日 冀中职业学院 任务2.9 计算工作日 开始工作时间是2019年9月1日，结束时间是2019年10月5日，每周周六周日都休息，计算一下他一共工作了多少天，如图所示。 这里需要用函数NETWORKDAYS和 NETWORKDAYS.INTL 函数。 2.9 计算工作日 冀中职业学院 任务2.9 计算工作日 1. NETWORKDAYS 函数简介 语法格式：networkdays(start_date, end_date, holidays)。 主要功能：专门用于计算两个日期值之间完整的工作日数值。 参数说明：start_date表示开始日期；end_date为终止日期；holidays表示作为特定假日的一个或多个日期。这些参数值既可以手工输入，也可以对单元格的值进行引用；这个工作日数值将不包含双休日和专门指定的其他各种假期。 2.NETWORKDAYS.INTL 函数 语法格式：NETWORKDAYS.INTL(start_date,end_date, weekend, holidays)。 主要功能：专门用于计算两个日期值之间完整的工作日数值。 参数说明：start_date表示开始日期；end_date 表示结束日期；weekend 为指定周末时间的参数值;holidays是在工作日中排除的特定日期。 2.9 计算工作日 冀中职业学院 任务2.9 计算工作日 此案例操作步骤如下。 第1步：定位光标，单击E2 单元格。 第2步：“插入函数”,找到“NETWORKDAYS”函数。 第3步：设置NETWORKDAYS 函数参数，如图所示。 2.9 计算工作日 冀中职业学院 任务2.9 计算工作日 第4步：如果仅周日休息，并且国庆节10月1号、2号、3号放假。在F2单元格插入 NETWORKDAYS.INTL函数，注意第三个参数仅周日休息的代码是“11”,第四个参数使用绝对引用。此函数参数设置如图2-9-3所示，公式为“=NETWORKDAYS.INTL(C2,D2,11,$H$2:$H$4)”。 2.9 计算工作日 冀中职业学院 任务2.9 计算工作日 第5步：最终效果如图所示。 $$