“E战到底”特训营 Day11 《11.2认识函数》学习笔记

《认识函数》课件目录


一、绝对、相对引用

1、引用的概念

        引用就是通过工作簿、工作表和单元格地址来引用其中的数值,可以通过输入“=”+鼠标点击来实现,也可以通过公式查看所引用的位置是否准确。

        ①引用单元格的值

            公式为  =(单元格位置)

        ②引用另一个工作表的值

            公式为  =‘(工作表名称)’!(单元格位置)

        ③引用另一个工作簿的值

            公式为  =‘[(工作簿名称)](工作表名称)’!(单元格位置)

        注意:引用单元格和另一个工作表的值,单元格位置默认是相对引用;引用另一个工作簿的值,单元格位置是默认绝对引用。

引用

2、相对与绝对引用:

        ①相对引用,是指引用地址不固定。

        ②绝对引用,是指引用的地址是固定的。

        ③而固定和不固定,是指在复制单元格到其他位置时,引用的地址是否会发生变化。固定就是不变化,不固定就是变化。

        ④一句话说明,加美元符号$,就是不变化的。

            $行,复制公式时,行不变。

            $列,复制公式时,列不变。

        ⑤就像是在烘焙时,使用某个配方(材料+重量)。

            $材料,就是材料品种不能变化,重量可以变化,这个可以少,那个可以多。

            $重量,就是材料品种可以变,重量不能变。

            $材料$重量,就是材料和重量都不能变,严格遵守配方要求,不管是什么牌子的材料,也不论是什么牌子的称料秤。

相对与绝对引用
列、行不变举例

3、切换绝对、相对引用的快捷键——F4

        敲一下 →行列都绝对引用——$列$行→行列都不变

        敲两下 →行绝对引用——列$行→行不变

        敲三下 →列绝对引用——$列行→列不变


二、乘法口诀表(案例与作业)

1、结合绝对、相对引用的知识点来编制乘法口诀表。

        难点在于判断公式里,绝对引用的是行还是列。

        例如

            ①单元格B2(1×1=1),向右边复制公式到单元格C2(1×2=2),应该是列不变,行变

            ②单元格单元格B2(1×1=1),向下复制公式到单元格B3(2×1=2),应该是列变,行不变

        简单来说,可以这样看。在制作乘法口诀表时,

            ①公式向右复制要确保“×”号左边的数字不变,如举例的1×1=1变化为1×2=2,数字1在A列,所以要绝对引用A列。

            ②公式向下复制要确保“×”号右边的数字不变,如举例的1×1=1变化为2×1=2,数字1在1行,所以要绝对引用1行。

乘法表求积举例

2、关于作业一

        作业一比较好操作,首先要确保计算公式正确,其次要结合之前的运算符知识中文本连接符“&”的知识。

            ①选择区域(B19:J27)→输入公式=$A19*B$18→快捷键Ctrl+Enter快速填充→查看是否有计算错误

            ②确保计算正确后→再一次选择区域(B19:J27)→输入公式=$A19&"×"&B$18&"="&$A19*B$18→快捷键Ctrl+Enter快速填充


作业1

3、关于作业二

        作业二的难点在于使用可以返回空值的函数——IF

        语法:

            IF(logical_test,value_if_true,value_if_false)

            Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。

            Value_if_true logical_test 为 TRUE 时返回的值。

            Value_if_false logical_test 为 FALSE 时返回的值。

        在计算参数 value_if_true 和 value_if_false 后,函数 IF 返回相应语句执行后的返回值。

        简单来说,就是这样一个函数

            IF(条件,符合条件时显示什么,不符合条件时显示什么)

            例如“=IF(A,B,C)”,意思是“如果A,那么B,否则C” 

        方法一:

        虽然老师在视频里提到用数组,可是既然是绝对、相对引用的知识点,不妨先用一用。

            ①选择区域(B49:J57)→输入公式=$A49*B$48→快捷键Ctrl+Enter快速填充→查看是否有计算错误

            ②确保计算正确后→再一次选择区域(B49:J57)

            ③选择区域后→输入公式=IF($A49>=B$48,$A49&"×"&B$48&"="&$A49*B$48,"")→快捷键Ctrl+Enter快速填充

        提示:在函数IF的公式中,可以这样拆解。

            条件——“×”前的数字要大于等于(>=)“×”后的数字。例如3×2=6,3>2;3×3=9,3=3。

            符合条件时显示——用连接符&合并字符串,英文双引号""表示文本名称,这样就能在单元格里显示乘法等式。

                                            (单元格1)&“×”&(单元格2)&“=”&“(单元格1)*(单元格2)”

            不符合条件时显示——空值,直接用双引号“”表示。

方法一

        方法二:

         老师在视频里提到用数组,所以思路应该是多对多的处理,也就是把一个区域当成一个个体来

        选择区域(B76:J84)→输入公式=IF(A76:A84>=B75:J75,A76:A84&"×"&B75:J75&"="&A76:A84*B75:J75,"")

        →数组快捷键Ctrl+Shift+Enter→{IF(A76:A84>=B75:J75,A76:A84&"×"&B75:J75&"="&A76:A84*B75:J75,"")}

        提示:数组里,把区域(A76:A84)和(B75:J75)分别当作一个个体,在IF函数里可以这样拆解。

        条件——“×”前的个体1要大于等于(>=)“×”后的个体2。

        符合条件时显示——(个体1)&“×”&(个体2)&“=”&“(个体1)*(个体2)”

        不符合条件时显示——空值,直接用双引号“”表示。


方法二——利用数组


三、公式操作小技巧

1、隐藏和保护公式

        ①点击单元格→鼠标右键或CTRL+1调取设置单元格格式命令框→“保护”→勾选“锁定”或“隐藏”

        ②“审阅”→“更改”工作组→“保护工作表”

2、快速复制公式

        要点有两个:快速复制公式的快捷键,以及如何快速选择待填充区域

        提示:公式复制在函数基本用法里有介绍4种方法(拖拽填充柄、双击填充柄、Ctrl+D、选择性粘贴),但是不适合数据量较大的情况使用。

        快速选择待填充区域——单击首行单元格→Ctrl+End跳转到该列数据末的单元格→记住单元格位置→Ctrl+↑回到首行单元格

         →在名称框输入数据列末单元格位置→Shift+Enter快速选中待填充单元格→复制公式快捷键Ctrl+Enter

3、把公式转换为数值

        方法一:复制公式→选择性粘贴为数值

        方法二:复制公式→Alt+1

        注意:方法二的前提是,提前将选择性粘贴值的快捷方式添加到自定义快速访问工具栏

4、合并单元格复制公式

        步骤:输入公式→快速复制公式

        以填充合并单元格序列为例:

        选择填充区域→输入公式“=MAX(绝对引用上方单元格列行:相对引用上方单元格)+1”→快捷键Ctrl+Enter

        函数MAX,返回最大值。

        例如“=MAX(A,B)”,意思是求A和B中的最大值;“=MAX(A:B)”,意思是求AB单元格区域内的最大值。

5、快速修改公式

        点击带公式的单元格→F2→修改公式

6、引用其他工作簿中的数据

        输入等号“=”→点击要引用的单元格

        一般默认为绝对引用

7、编辑链接

        “数据”→“连接”工作组→“编辑链接”

        可以更新数据、打开源文件、断开链接

8、快速替换公式

        选择公式列→Ctrl+H替换→将公式位置列替换(如E替换F)

        提示:如果公式中的函数有包含字母与列位置相同,可能会导致错误


四、公式返回的错误值

        七种错误值,分别有具体的解决办法

        ①####——拉宽,也可以双击列宽

        ②#DIV/0! ——修改单元格引用,或者输入不为0的除数

        ③#VALUE! ——确认运算符和参数

        ④#REF! ——恢复引用单元格范围

        ⑤#N/A——填充数据

        ⑥#NAME? ——文本要双引号

        ⑦#NUM! ——数据太大,修改公式或参数类型

        ⑧#NULL!——没有公共单元格,取消两个区域之间的空格


五、快捷键—— Alt + =


六、跨工作表使用

        要点:使用Shift协助选择多个工作表

        步骤举例:(汇总12个月数据,汇总表与12个数据表格式完全一致)

        在汇总表选择待填充区域→输入函数→如“=SUM(”→进入1月表→点相应数据单元格→按Shift+鼠标单击12月工作表标签→快捷键Ctrl+Enter

        汇总单元格公式:=SUM('1:12'!B2))

推荐阅读更多精彩内容