Excel函数01：函数的基础——单元格的引用方式

相信不少初入职场的同学们若是进入到办公室当白领，想必办公三大件是必须要学会的一个技能，这里我就直接讲解Excel函数的基础和应用吧，若是连打开、另存为都不清楚的话，可以翻出《大学计算机基础》这本书好好回炉深造一下，哈哈，言归正传。

一、微软和金山

讲解函数前，先将一下两家公司表格（即MicroSoft Office Excel和金山WPS表格）的区别，Excel和WPS表格在公式的使用上基本互相兼容，也有一些新的函数在Excel内可使用，但WPS不能使用，比如Excel365的XLOOKUP，若是在WPS上使用是无效的，不过这类函数少之又少可忽略，考虑到大部分同学和机关单位人员普遍使用WPS个人版（毕竟免费），我就以WPS表格来讲解函数的使用。

二、单元格的“坐标值”

当我们新建一个表格后，我们称之为“工作簿（bù）”；工作簿下面有“Sheet1、Sheet2、Sheet3”，称之为“工作表”；工作表的每一个矩形框称之为“单元格”。

每一个工作表都有列标行号，列标即是每一列对应的字母“A、B、C、D、E、F... ...”，行号即是每一行对应的数字“1、2、3、4 ... ...”（如下图），则选中一个单元格后，它的坐标值就出来了，如下图绿色框的坐标即为C6，左上角的名称框也会其显示坐标值，了解到坐标值后我们再来学习单元格的引用。

列标与行号

三、坐标值的状态

单元格的引用方式，就是改变坐标值的状态是锁定还是活动，若列标或者行号前有一个美元符号“$”，表示该列标或者行号已被锁定，根据锁定的情况不同，我们将单元格的引用分为三种状态：相对引用、绝对引用、混合引用。

1、相对引用：行列都不锁定，如C6。

2、绝对引用：行列都被锁定，如$C$6。

3、混合引用：行锁列不锁，如C$6；行不锁列锁，如$C6。

4、切换方式：在引入单元格后默认为相对引用，按一下F4切换为绝对引用，再按一下F4切换为混合引用-行锁，再按一下F4切换为混合引用-列锁，再按一下就循环到相对引用，或者手动在行号列标前输入$。

这三种引用方式，复制单元格函数时所产生的结果如下：

举一个简单的例子来说明，如下图：

第一步，在每一种方式的左上角引入B3单元格的值，得到的结果是一致为“甲”；

不同方式引入B3单元格

不同方式引入的结果

第二步，将每一种方式左上角的公式复制粘贴到该引用方式剩余的9个空白单元格中，会得到下列值，我们发现不同的引用方式会得出不同的求值结果。

不同的引用方式会得出的求值结果

第三步，将第二步的计算公式显示如下，会发现一些结论：

第二步的计算公示

1）相对引用与旁边的引用单元格区域一一对应，如“求值单元格E3”=“引用单元格B3”、F3=C3、E6=B6，即无论求值单元格上下左右移动多少个位置，引用单元格都会同步移动；

2）绝对引用只引用了B3单元格，无论是G3、G4还是H6、H7都等于B3，即无论求值单元格上下左右移动多少个位置，引用单元格都不会同步移动；

3）混合引用-锁列只引用了第B列，即无论求值单元格上下左右移动多少个位置，引用单元格只会上下同步，左右不同步；

4）混合引用-锁行只引用了第3行，即无论求值单元格上下左右移动多少个位置，引用单元格只会左右同步，上下不同步；

5）概括一下就是，我把谁锁了，谁就不能移动。

四、实例应用

实例1-1：计算六家公司各类产品的月总销售额【相对引用】

图实例1-1 计算总销售额

在B9单元格中输入“=SUM(B3:B8)”回车，然后复制B9单元格的求和函数粘贴至C9、D9，即可计算出三个品种的月销售总额（如图实例1-2）。

实例1-2：计算每家公司每个产品的销售占比

图实例1-2 计算销售占比

方法1：先计算单一品种的销售占比（相对引用/绝对引用）

在计算每家公司化妆品占总额的百分比时，分子是每家公司的销售额，是变量因此可用相对引用；分母是化妆品的销售总额，是不变量因此可用绝对引用；

如E3单元格输入“=B3/$B$9”，然后复制粘贴到化妆品下方剩余5家的单元格中；

在食品、服装的第一个单元格中重复该操作即可得到结果如图实例1-2-1

计算化妆品的销售占比

重复操作计算食品、服装的销售占比

图实例1-2-1 方法1计算销售占比

方法2：直接计算多个品种的销售占比（混合引用）

若只在E3单元格中输入一个公示，剩余17个求值单元格中复制粘贴即可得到所有结果会更快速；

【分析】从E3复制到G8，分子销售额都是一一对应的，所以分子只能使用相对引用；

分母销售总额要与同一品种对应，而方法1中在使用时可以看到，求值单元格进行了上下移动，同时分母始终保持第9行的数据，因此可以将分母的绝对引用换成只锁定第9行的相对引用；

分母只锁定第9行后（B$9），当复制E3单元格内容到F3时，我们会发现分母变成了“C$9”，不正是食品的总销售额吗？

从上面分析可以在E3单元格中输入“=B3/B$9”，然后复制粘贴到剩余17个空白求值单元格中即可得到结果。

E3单元格中输入“=B3/B$9”

方案2的计算公式

图实例1-2-2 方法2计算销售占比

实例1-3：九九乘法表（混合引用的经典案例）

图实例1-3 九九乘法表

【分析】第2行第A列的“1”分别去乘以第1行的每一个数字，即复制粘贴时需要向右移动，但A2坐标值不能变，而左右移动改变的是列标，因此引用A2时需要锁定列标即为“$A2”；

同理引用B1时需要锁定行号，即为“B$1”；

即B2单元格输入“=$A2*B$1”，回车并复制粘贴至剩余单元格即可得到下列结果。

九九乘法表计算公式

图实例1-3-1 九九乘法表计算结果

【末尾总结】混合引用锁定的对象，取决于在复制粘贴的移动过程中不能让列标还是行号同步移动。