办公软件函数使用技巧视频(第一天:学好EXCEL函数和公式必须掌握的基础知识和操作)
- 办公技巧
- 2023-07-02 21:36:37
- 0
朋友们,大家好!
在日常工作中,我们经常用EXCEL函数处理表册,函数的运用,大幅度提升了我们的工作效率。笔者集合工作实际,把最常用的EXCEL函数进行逐一详细讲解,课程中有大量案例,为便于朋友们更加深入了解各个函数的用法,将于每天上午7:00同步发布视频教程和图文教程(包含公式)。只要不懈努力和不断实践,通过30天的系统学习,你也能成为EXCEL函数高手,从此告别加班,让同事和朋友刮目相看。
今天,我将和大家一起分享学好EXCEL函数和公式必须学会的基础知识和操作,涉及的知识点有点多,如果有恒心和毅力想学好EXCEL函数和公式,请耐心往下看;如果你只是想随便看看,抱着无所谓的态度,那请止步,首先你的学习态度就不端正,谈何学好?我花费大量时间整理学习资料,把每个知识点做成实例进行讲解,自己从中又巩固了一遍,进步不是一点点,要说学习感受,真的非常辛苦。
俗话说得好:“基础不牢,地动山摇”“不积跬步,无以至千里;不积小流,无以成江海”,只有把基础打好了、打牢了,一点一点积累经验,才会在以后的学习过程中得心应手。真心希望点进来的朋友抱着必学会的态度认真学习,反复练习,对课程中的知识点要学会举一反三进行利用,学会变通。学无止境,只有学到手的东西才是自己的,只有自己学会了,才会在工作中如鱼得水,在职场上如虎添翼,否则,神马都是浮云。
说得有点多,戳到痛点的朋友请多谅解,这也说明你有学习的态度和决心。
下面,我们进行今天的课程。
本套课程以WPS2019版的EXCEL进行讲解,其实,在一般用户使用过程中,WPS和OFFICE基本上没太大差别,甚至WPS更加人性化,但是在一些专业领域,WPS处理EXCEL表格的某些复杂函数可能就不如微软的OFFICE。
一、什么是EXCEL工作簿、工作表、单元格
(一)工作簿
所谓工作簿,是指EXCEL环境中用来储存并处理工作数据的文件。也就是说,EXCEL文档就是工作簿,它是EXCEL工作区中一个或多个工作表的集合,其扩展名有XLS、XLSX、XLSM、XLSB等。每一本工作簿可以拥有许多不同的工作表,工作簿中最多可建立255个工作表。
(二)工作表
工作表是显示在工作簿窗口中的表格,在后续新版本的EXCEL中,一个工作表可以由1048576行和256列构成,行的编号从1到1048576,列的编号依次用字母A、B……XFD表示,行号显示在工作簿窗口的左边,列号显示在工作簿窗口的上边。
EXCEL默认一个工作簿有三个工作表,用户可以根据需要添加工作表,但每一个工作簿中的工作表个数受可用内存的限制,当前的主流配置已经能轻松建立超过255个工作表了。
每个工作表有一个名字,工作表名字显示在工作表标签上。工作表标签显示了系统默认的前三个工作表名:Sheet1、Sheet2、Sheet3。其中白色的工作表标签表示活动工作表。单击某个工作表标签,可以选择该工作表为活动工作表。
工作薄中的每一张表格称为工作表。工作薄如同活页夹,工作表如同其中的一张张活页纸。工作表是EXCEL存储和处理数据最重要的部分,其中包含排列成行和列的单元格,它是工作簿的一部分,也称电子表格。可以同时在多张工作表上输入并编辑数据,并且可以对来自不同工作表的数据进行汇总计算。
(三)单元格
单元格是表格中行与列的交叉部分,它是组成表格的最小单位,可拆分或者合并。单个数据的输入和修改都是在单元格中进行的。
单元格按所在的行列位置来命名,例如:地址“A1”指的是“A”列与第1行交叉位置上的单元格;地址“A1:D8”指的是单元格A1到D8之间的区域。
二、什么是函数、公式和运算符
(一)EXCEL公式
EXCEL中,公式是对EXCEL工作表中的值进行计算的等式,在EXCEL中可以使用常量和算术运算符创建简单的公式。
公式总是以“=”开始,然后将各种计算数据(单元格地址、名称、常量、函数等)使用不同的运算符连接起来,从而有目的地完成某种数据结果的计算。
例如:=A1 B1 C1就是一个简单的公式。
(二)EXCEL函数
EXCEL中,函数实际上是一个预先定义的特定计算公式,按照这个特定的计算公式对一个或多个参数进行计算,并得出一个或多个计算结果,叫做函数值。使用这些函数不仅可以完成许多复杂的计算,而且还可以简化公式的繁杂程度。
例如:SUM(A1:A8)就是一个简单的函数。
每个函数都有唯一的名称,并且不区分大小写,它决定了函数的功能和用途。在函数中,括号是成对出现的,它是函数的标识。
要想让函数起作用,必须在公式中使用,直接在函数前添加“=”,是函数最简单的应用。因此,从广义上讲,函数是一种特殊的公式。
(三)运算符
运算符是公式的基本要素,利用它可对公式中的参数进行特定类型的运算,它是影响数据计算结果的重要因素之一。
在EXCEL中,运算符包括:算术运算符、比较运算符、文本连接运算符和引用运算符等4种类型。
1.算术运算符:算术运算符主要用于对数据进行各种数学运算,各种算术运算符的具体作用和实例如下:
提示:“ ”和“-”运算符的特殊用法:“ ”和“-”运算符既可以连接两个参数,也可以连接一个参数,当只连接一个参数时,用于标识数据的正负数,例如:“ 8”表达式表示正数8,“-8”表达式表示负数8。
2.比较运算符:比较运算符主要用于比较两个不同数据的值,当等式成立,则结果返回逻辑值TRUE;如果等式不成立,则结果返回逻辑值FALSE。各种比较运算符的具体作用和实例如下:
3.文本运算符:在EXCEL中,文本运算符只有一个,即和号(&),通常被称为文本串联符或文本连接符,其具体作用和实例如下:
4.引用运算符:引用运算符主要用于对指定的单元格区域进行合并计算,在EXCEL中,引用运算符只有两个,即“:”和“,”,其具体作用和实例如下:
通过以上学习,我们已经了解几种运算符的作用,那么运算顺序是怎样的呢?
在使用公式计算数据时,单纯的一个运算符的数据计算比较少,通常都是同时使用多个运算符,此时就有必要了解计算过程中的运算顺序,这样才能知道数据的计算结果是什么。
在EXCEL中,系统遵循从高到低的顺序进行计算,相同优先级的运算符,遵循从左到右的原则进行计算。
引用运算符>算术运算符>文本连接运算符>比较运算符
对于算术运算符来说,同级运算符的优先顺序为:负数→百分比→乘方→乘和除→加和减。
如果要改变运算符的优先顺序,可以使用括号,而且在Excel中允许括号嵌套使用,系统将按照先内后外的顺序优先处理最内部括号的内容,然后依次向外扩展。
例如:
要计算((3 2)*5-1)/6,计算过程为:
第一步,计算嵌套括号中的3 2,结果为(5*5-1)/6
第二步,在括号中先计算5*5,结果为(25-1)/6
第三步,计算括号中的25-1,结果为24/6
第四步,计算24/6,结果为4
三、EXCEL中的引用方式
(一)数据的引用方式
在EXCEL中,数据的引用方式有三种,分别是相对引用、绝对引用和混合引用,各种引用方式的说明如下:
在公式中,如果要快速在各种引用方式之间进行切换,可以选择单元格地址或者将文本插入点定位到单元格地址的前、中、后,然后按键盘上的【F4】键切换。
例如:在公式“=A1”中,将文本插入点定位到“A”和“1”之间(或在“A”的前面,或在“1”的后面),连续按键盘上的【F4】键的顺序效果如下:
第一次按【F4】键,结果为=$A$1
第二次按【F4】键,结果为=A$1
第三次按【F4】键,结果为=$A1
第四次按【F4】键,结果为=A1
无论单元格的初始状态为哪种引用,按【F4】键后都会按如上顺序变化,如初始状态为上面第二次按【F4】键的效果,在A1单元格地址中按【F4】键后变为如上第三次按【F4】键的效果,而不是如上第一次按【F4】键的效果。
知识拓展:
复制$A$1到其他单元格,引用的单元格永远是A1单元格。
复制A$1,往右复制时,会变为B$1、C$1,以此类推;往下复制时,会固定在A1单元格。
复制$A1,往右复制时,会固定在A1单元格;往下复制时,会变为$A2、$A3,以此类推。
复制A1,往右复制时,会变为B1、C1,以此类推;往下复制时,会变为A2、A3,以此类推。
(二)同一工作簿跨表引用
在使用公式和函数计算数据时,有时候会引用当前工作簿的其他工作表的数据,这时就涉及到跨表引用。
在同一工作簿的不同工作表中引用单元格或单元格区域,其引用格式为:“工作表名称!单元格地址”,这里的单元格地址引用也可以包括相对引用、绝对引用和混合引用。
例如:“工作表!A1”或“工作表!A1:D1”
除了按照引用格式直接手动输入公式外,还可以通过选择单元格的方式快速生成引用,具体操作步骤如下:
第一,打开文件,切换到“个人基本信息”工作表,在B2单元格中输入“=”,如下图:
第二,选择“信息汇总表”工作表,用鼠标点击A3单元格,然后按回车键,即可引用该单元格的数据,见下图:
返回“个人基本信息”工作表,我们发现,这时候已经把数据引用过来了,见下图:
提示:如果跨表引用的工作表的名称以数字、空格、特殊符号(如$、%、#、~、!、 、-、@、=等)开头,则在公式中引用工作表名称时,需要使用一对半角单引号引起来,如:“1月会员费!A1”。
(三)跨工作簿引用
在EXCEL中,除了在同一个工作簿中引用数据以外,使用公式计算数据时,还经常会跨工作簿引用数据,具体的引用格式为:“=[工作簿名称]工作表名称!单元格地址”。在跨表、跨工作簿引用数据时,有两种方法,具体如下:
1.选择工作表数据。如果当前在EXCEL中同时打开了被引用的工作簿,可以通过直接选择工作表中的数据单元格完成引用,具体操作方法与上面讲的方法类似。
2.手动输入引用。如果被引用的工作簿当前没有打开,只能通过手动输入的方式完成引用,需要注意的是,在输入引用时,必须包含完整的文件路径,并且要用半角单引号将文件路径、工作簿和工作簿名称引起来。
例如:='[工作簿名称]工作表名称!单元格地址'
四、逻辑值和常用的逻辑函数
(一)逻辑值
在EXCEL中,逻辑值有两个,分别是TRUE和FALSE,主要作用是对某条件判定是否成立,成立则为真(TRUE),不成立则为假(FALSE)。
例如:在单元格中输入"=5 5=10"(输入时不包括引号),按回车键后显示结果为TRUE,5 5=10是正确的,即真值,说明等式成立;如果在单元格中输入"=5 5=8",则结果显示FALSE,计算结果不正确,说明等式不成立。
1.逻辑值的转换规则
在EXCEL公式计算时,逻辑值可以用相应的数值代替,但是,在不同的运算中有不同的要求,逻辑值的转换规则有三种,具体如下:
(1)在四则运算中的转换规则
在四则运算中,通常用1代替TRUE,用0代替FALSE。
例如:
公式“=1*TRUE”,结果为1
公式“=1 TRUE”,结果为2
公式“=1*FALSE”,结果为0
公式“=1 FALSE”,结果为1
(2)在逻辑运算中的转换规则
在逻辑运算中,通常用非零值(不是0的任何数值)代替TRUE,用0代替FALSE。
例如:
公式“=IF(1,"正确","错误")”,结果为:正确
公式“=IF(-1,"正确","错误")”,结果为:正确
公式“=IF(0.1,"正确","错误")”,结果为:正确
公式“=IF(0,"正确","错误")”,结果为:错误
(3)在比较运算中的转换规则
在比较运算中,逻辑值与数值和文本之间存在一定的关系,即:“数值<文本 例如: 公式“=TRUE>2”,结果为:TRUE 公式“=TRUE<2”,结果为:FALSE 公式“=FALSE=2”,结果为:FALSE 公式“=FALSE>2”,结果为:TRUE 以上三条转换规则,对编写公式和优化公式运算有很大的作用,在以后的课程中你将会体会得到。 2.逻辑值在EXCEL中的运用案例:根据性别计算退休年龄 我们在填写职工信息表时,需要计算职工的退休年龄,不同性别的职工,其退休年龄不同,男性职工的退休年龄为60岁,女性职工的退休年龄为55岁,现在需要根据B3单元格中职工的性别自动填写退休年龄。见下图: B3单元格中的性别为“女”,则C3单元格计算退休年龄的公式过程如下: 公式:=60-(B3="女")*5 第一步,引用B3单元格的值,结果为:60-("女"="女")*5 第二步,先计算出括号中的值("女"="女"),逻辑值为TRUE,结果为:60-(TRUE)*5 第三步,根据转换规则,将TRUE转换为1,结果为:60-1*5 第四步,计算1*5,结果为:60-5 第五步,计算60-5,结果为:55 最终结果和公式见下图: 本例是利用逻辑值计算不同性别职工的退休年龄,只要理解了,不用其他函数也可以计算。当然,利用逻辑值计算类似的数据,一般只针对简单的运算,复杂的公式需要用函数处理,后续课程中会针对此类运算作详细讲解。 (二)常用的逻辑函数 在公式计算中,对于逻辑判断,我们通常会使用逻辑函数OR(“或者”)和AND(“与”或“并且”)进行运算。 其中,AND函数是“与”或“并且”的意思,即所有的条件都满足时返回TRUE,只要任意一个条件不满足则返回FALSE;OR函数是“或者”的意思,即在多条件时,满足任意一个条件或所有条件都满足返回TRUE,如果一个条件都不满足则返回FALSE。 在复杂的公式中,为了简化公式,我们可以用数学运算代替相应的逻辑函数,加法运算代替OR,乘法运算代替AND。 1.用加法运算代替逻辑函数OR 例如:要判断只有局长和副局长才有审批权限,否则无法审批,此时需要用条件判断函数IF和逻辑函数OR进行运算。见下图: 2.用乘法运算代替逻辑函数AND 例如:要判断学生成绩考试语文、数学、英语分数都大于等于60才算及格,否则不及格,此时需要用条件判断函数IF和逻辑函数AND进行运算。见下图: 当然,以上只是举例,并不是说只要一个科目不及格就统统不及格,大家不要纠结。 五、空单元格和空文本的区别 在EXCEL中,默认状态下,单元格中不显示任何内容,或将有内容的单元格清空后,此时的单元格即为空单元格。而空文本则是使用一对半角双引号("")来表示。 如果某单元格的数据是通过公式引用的空文本,即单元格的值为“=""”,此时,该单元格的显示效果和空单元格的显示效果是一样的,不过,二者在公式运用中有明显的区别。 (一)两者的区别 1.从公式的角度看,空单元格和空文本等同于一样 例如:A1单元格为空单元格,A2单元格为空文本,在A3单元格中输入公式“=A1=A2”,计算结果为TRUE。 2.空单元格的值视为0 例如:A1单元格为空单元格,在A2单元格中输入公式“=A1=0”,计算结果为TRUE;在A2单元格中输入公式“=A1*2”,计算结果为0。 3.空文本的值不能视为0 例如:A1单元格为空文本,在A2单元格中输入公式“=A1=0”,计算结果为FALSE;在A1单元格中输入公式“=A1*2”,计算结果为#VALUE错误值。 (二)屏蔽空单元格显示0的方法 在EXCEL中,如果公式的结果是对某个空单元格的引用,公式的计算结果不是空文本,而是数值0,在某些情况下,数值0和空单元格还是有区别的。 例如:下图所示的是学生的语数外考试成绩,学生“姓名三”的数学考试成绩为空,我们在对该名学生的数据进行查询时,在数学列显示为0,这个0值代表该生的数学成绩为0,还是缺考或者漏录成绩呢? 为了区别这两种情况,避免公式结果引用空单元格时公式结果显示0的情况,可以在公式末尾使用&运算符强制将引用空单元格的结果转换为空文本。如下图: 此时,程序虽然查到了该生的数学成绩单元格为空单元格,但没有显示0值,而是显示空文本。 六、处理数据计算的几种方法 (一)计算公式结果 输入公式结束后,可通过多种方法计算公式结果,如点击编辑栏中的“√”(即“输入”按钮)显示公式结果。 此外,还可以通过按快捷键的方式计算公式结果,这是最便捷、快速的计算公式结果的方法,具体快捷键及计算效果如下: 1.【Enter】键:输入公式后,按【Enter】键可在计算出结果的同时选择该单元格下方的单元格。 2.【Tab】键:输入公式后,按【Tab】键可在计算出结果的同时选择该单元格右侧的单元格。 3.【Ctrl Enter】组合键:输入公式后,按【Ctrl Enter】组合键,在计算出结果的同时仍然保持该单元格的选中状态,该方法可同时查看数据结果和所使用的公式。 4.【Ctrl Shift Enter】组合键:如果是数组公式,要计算出结果,必须在输入公式结束后按【Ctrl Shift Enter】组合键完成,以上3种方法都不能正确计算公式结果。数组公式不能在合并单元格使用。 (二)使用【F9】键将公式转换为运算结果 【F9】键在公式应用中具有公式重算的功能,如果将文本插入点定位到公式中,按【F9】键后系统自动将整个公式转换为运算结果。 例如:未按【F9】键前显示的是公式。见下图: 当我按下【F9】键后,直接显示公式结果。见下图: 提示:在使用【F9】键将公式转换为结果后,原来的公式将被替换,如果想改回公式,可以通过撤销操作将结果还原为公式。 (三)批量输入和复制公式 1.拖动控制柄复制公式 在EXCEL中,选择单元格后,其右下角有一个实心的小方块,我们将其称之为控制柄,将鼠标光标移动到控制柄上时,鼠标光标会变成黑色十字形状,通过拖动该控制柄到需要的位置,可完成公式的批量复制。 拖动控制柄批量复制公式后,在结束位置将出现“自动填充选项”按钮,单击该按钮,在弹出的列表中包括“复制单元格”“仅填充格式”“不带格式填充”和“智能填充”4个单选按钮。见下图: 在公式运用中,这4个选项的作用如下: “复制单元格”:选择该选项,程序自动将源单元格的公式和单元格格式全部填充到其他单元格。 “仅填充格式”:选择该选项,程序只将源单元格的格式填充到其他单元格,而不复制源单元格的公式。 “不带格式填充”:选择该选项,程序自动将源单元格的公式填充到其他单元格,而不复制源单元格的格式。 “智能填充”:快捷键【Ctrl E】,是一个相当不错的快捷键,可以批量提取身份证号码中的出生日期,从姓名手机号中提取手机号,轻松合并多列数据等。 2.双击控制柄复制公式 双击控制柄批量复制公式比拖动控制柄复制更加快捷,只需要双击包含公式的单元格的控制柄,程序自动向下填充公式到整个表格的结束位置。见下图: 双击后的最终结果见下图: 需要注意的是,双击控制柄完成公式的批量复制时,对数据源的表格结构有一定的条件限制,具体如下: 第一,需要批量复制公式的单元格是在某列单元格上向下填充,不能在某列向上填充,也不能在某行上向右填充。 第二,批量复制公式的单元格相邻列必须有数据,否则双击控制柄无效。 3.用【Ctrl Enter】组合键批量输入公式 在EXCEL中,如果某一列数据的计算公式相似,那么可以用【Ctrl Enter】组合键批量录入公式。首先选择所有的结果单元格,输入公式后,按【Ctrl Enter】组合键即可快速录入选中单元格的公式结果。 例如:要计算每位同学的考试总成绩,首先选择总分列需要计算总分的E3:E8区域,输入公式“=SUM(B3:D3)”,见下图: 然后按【Ctrl Enter】组合键即可得出所有学生的总分,如下图: 4.用【Ctrl D】和【Ctrl R】组合键批量复制公式 我们在处理表册数据时,遇到内容相同的单元格,往往采用复制【Ctrl C】、粘贴【Ctrl V】的办法进行,需要按两次按键,虽然省去了输入,但还是不够快,对于需要向下复制公式的时候,我们可以使用【Ctrl D】组合键。这个功能其实很实用,因为一般我们都是从上往下填充数据,如果这个单元格的内容同上,就可以直接按【Ctrl D】组合键。 例如:我在E3单元格中输入的公式,需要把公式向下填充到数据表中的最后一个单元格,只需要选择该单元格和最后一个单元格的区域,然后按【Ctrl D】组合键即可完成复制。见下图: 与【Ctrl D】用法类似的,还有【Ctrl R】,两者的区别在于,【Ctrl D】向下填充,【Ctrl R】向右填充。 相邻列填充时,只要选中需要填充的单元格,按【Ctrl R】组合键,你会发现,已经把相邻的左侧列的数据复制过来了,无需选择源数据。 间隔列填充时,需要同时选中数据源和需要填充的单元格,这是新版本中的“隔空填充”。例如:我要把D列的英语成绩复制到新的列,同时选中D列和G列的数据区域,然后按【Ctrl R】组合键即可完成复制。见下图: 5.使用复制粘贴功能批量复制公式 在EXCEL中,软件提供了“选择性粘贴”功能,粘贴时设置粘贴选项为粘贴公式后,软件只将源单元格的公式批量粘贴到其他选中的单元格。 例如:我在E3单元格中输入了公式,需要把公式复制到数据表中的其他空白单元格,首先复制E3单元格内容,然后选择需要填充公式的其他单元格,在“开始”选项卡下左侧“粘贴”两个字,在弹出的列表中选择红框中“公式”,即可完成公式的填充。见下图: 七、充分利用函数屏幕提示功能 (一)提示函数的功能和语法结构 在最近几个版本的EXCEL中,程序提供了屏幕提示功能,默认情况下,该功能是启用的,对我们学习函数提供了很好的作用。 在单元格或编辑栏中输入等号后,在输入函数名称过程中,会自动提示以你输入的内容为开头的多组函数,鼠标点击或按向上向下的箭头可以查看所提示函数的功能,双击需要的函数或按回车键即可录入单元格。见下图: 在单元格或编辑栏中输入等号、函数名称和左括号这3部分内容后,在弹出的屏幕提示信息中将会自动显示该函数对应的语法结构。见下图: 从上图可以看出,RANDBETWEEN函数共有2个参数。 除此之外,在屏幕提示中,还会智能地提示当前正在编辑的参数,文本插入点定位到哪个参数,当前参数在语法结构中会以加粗的字体格式显示。见下图: 输入公式后,如果你想查看参数对应表达式,只需要点击屏幕提示的参数名称,然后在单元格和编辑栏中自动选中对应的表达式。见下图: (二)快速获取函数的帮助信息 在单元格或编辑栏中输入函数名称后,如果要更准确查看函数的帮助信息,不需要单独打开帮助系统,直接使用屏幕提示功能就可以快速获取相关函数的帮助信息。 如上图,输入函数后,在屏幕提示中单击函数名称超链接,即可打开SUMIF函数的帮助信息。如下图: 以上就是学习EXCEL函数、公式需要掌握的基本知识和操作,想要学习好EXCEL函数,最基本的操作要熟记于心,通过后续大量的函数不断实践,学会多个函数的组合,后续课程中我们会讲到多函数结合的用法,能进行更为复杂的计算,对提升工作效率有很大帮助。 个人建议:在学习EXCEL函数时,首先要熟悉函数的功能和语法,尽量多写,通过反复书写和实践,对照文中的案例,举一反三,我相信,你的EXCEL技巧会得到快速提升,日积月累,必成大器! 感谢朋友们的支持,如果你有好的意见建议和问题,欢迎评论区留言交流,期待你的精彩!
本文由 京廊文化根据互联网搜索查询后整理发布,旨在分享有价值的内容,本站为非营利性网站,不参与任何商业性质行为,文章如有侵权请联系删除,部分文章如未署名作者来源请联系我们及时备注,感谢您的支持。
本文链接: /bangong/504.html