2024年3月25日发(作者：)
第
1
章
Excel
公式编辑问题
1.1
公式的输入与编辑问题
1．什么是公式
公式可以说成是
Excel
中由用户自行设计对工作表进行计算和处理的计算式。例如：
=AVERAGE(A2:F2)*B1+50
这种形式的表达式称为公式。它要以等号“
=
”开始，等号后面可以包括函数、引用、运算符和常量。上式
中的
AVERAGE(A2:F2)
是函数，
B1
则是对单元格
B1
的引用（计算时使用
B1
单元格中存储的数据），
50
则是
常量，“
*
”和“
+
”则是算术运算符，另外还有很多其他运算符，如比较运算符、文本运算符等。
2．如何输入公式
输入公式要使用“
=
”号开头，如果直接输入公式（不加起始符号），
Excel 2007
自动将输入的内容作为数
据。了解了这一规则之后，可按如下方法操作。
Step 01
例如，在如图1-1所示的表格中，要计算1月份应缴纳税额，选中B7单元格。
Step 02
首先输入“=”号，然后依次输入公式的整体部分，如图1-2所示。
公式编辑栏
选中单元格
图1-1 图1-2
Step 03
输入公式后，按Enter键即可得到计算结果，如图1-3所示。
Excel
公式、函数、图表应用技巧800问
计算结果
小知识：
图1-3
在单元格中输入的公式会自动显示在公式编辑栏中，因此也可以在选中要返回值的目标单元格之后，在公
式编辑栏中双击鼠标进入编辑状态，然后直接输入公式。
3．如果公式中引用了单元格，只能采用手工方法进行输入吗
如果公式中引用了单元格，除了采用手工方法直接输入公式之外，还可以用鼠标选择单元格配合公式的输
入。例如输入公式“
=SUM(B3:D3)*B6
”，具体操作如下。
Step 01
输入“=”公式起始符号，再输入“sum(”，选中B3:D3单元格区域。
Step 02
输入“)*”，然后再用鼠标选中B6单元格，即可完成公式的输入。
4．除了使用“=”开头来输入公式外，还可以使用其他符号开头吗
公式的输入一般以“
=
”为起始符号，除此之外，还可以使用“
+
”和“-”两种符号来开头。具体操作如下。
Step 01
先输入“+”符号，再输入公式的后面部分，输入完成后按Enter键，程序会自动在公式前面加
上“=”符号，如图1-4所示。
图1-4
“=”符号，并将第一个数据源当作负值来计算，如图1-5所示。
Step 02
先输入“
-
”符号，再输入公式的后面部分，输入完成后按Enter键，程序会自动在公式前面加上
图1-5
2
第
1
章
Excel
公式编辑问题
5．如何编辑公式
输入公式后，如果需要对公式进行更改或是发现有错误需要更改，可以利用下面的方法来重新对公式进行
编辑。
方法
1
：
双击法：在输入了公式且需要重新编辑公式的单元格中双击鼠标，此时即可进入公式编辑状态，
直接重新编辑公式或对公式进行局部修改即可。
方法
2
：
按F2功能键：选中需要重新编辑公式的单元格，按F2键，即可对公式进行编辑。
方法
3
：
利用公式编辑栏：选中需要重新编辑公式的单元格，鼠标在公式编辑栏中单击一次，即可对公
式进行编辑。
6．有没有办法将公式转换为文本
有两种方法可以将公式转换为文本，具体操作如下。
方法
1
：
去除“=”：双击要转换公式的单元格，去除公式起始符号（“=”）即可。如需转换回来，只需
在公式前加上“=”或“+”符号即可，如图1-6所示。
方法
2
：
添加“‘”（单引号）符号：双击要转换公式的单元格，在公式起始符号前面加上“‘”符号即
可。如要重新转换回来，去除“‘”符号即可，如图1-7所示。
去除“
=
”号
添加“‘”号
图1-6 图1-7
7．公式中有哪些常用的运算符
运算符是公式的基本元素，也是必不可少的元素，每一个运算符代表一种运算。在
Excel 2007
中有
4
种运
算符类型，每类运算符和作用如表
1-1
所示。
表1-1
运算符类型
运
算
符
+
-
算术运算符
*
/
%
^
比较运算符
=
>
加法运算
减号运算
乘法运算
除法运算
百分比运算
乘幂运算
等于运算
大于运算
作
用
示
例
10+5
或
A1+B1
10
-
5
或
A1
-
B1
或-
A1
10*5
或
A1*B1
10/5
或
A1/B1
85.5%
2^3
A1=B1
A1>B1
3
Excel
公式、函数、图表应用技巧800问
续表
运算符类型
运
算
符
<
小于运算
>=
<=
<>
作
用
大于或等于运算
小于或等于运算
不等于运算
用于连接多个单元格中的文本字
符串，产生一个文本字符串
特定区域引用运算
联合多个特定区域引用运算
交叉运算，即对两个引用区域中共
有的单元格进行运算
示
例
A1 A1>=B1 A1<=B1 A1<>B1 A1&B1 A1:D8 SUM(A1:C2,C2:D10) A1:B8 B1:D8 比较运算符 文本连接运算符 & : （冒号） 引用运算符 , （逗号） （空格） 小知识： 在 Excel 2007 中输入公式时，注意运算符要在半角状态下输入，否则输入的公式将得不到正确的结果。 8．公式中常用运算符的优先顺序是怎样的 公式中众多的运算符在进行运算时很显然有着不同的优先顺序，正如最初接触数学运算时就知道“ * ”、“ / ” 运算符优于“ + ”、“-”运算符一样，只有这样它们才能默契合作实现各类复杂的运算。公式中运算符的优先 顺序如表 1-2 所示。 表1-2 优 先 顺 序 1 2 3 4 5 6 7 8 - % ^ * 和 / + 和 - & = 、 < 、 > 、 <= 、 >= 、 <> 运 算 符 : （冒号） （空格） , （逗号） 说 明 引用运算符 作为负号使用（如：- 8 ） 百分比运算 乘幂运算 乘和除运算 加和减运算 连接两个文本字符串 比较运算符 9．如何在已有的单元格中批量加入一段固定字符 要实现在已有的单元格中批量加入一段固定字符，可以使用“ & ”连接符号来实现。例如在员工档案管理 表中，要将所有员工编号前都加上 BX ，可以按如下方法操作。 Step 01 假设“编号”显示在A列，首先在A列前插入一个空白列。 Step 02 选中A2单元格，输入公式“"BX" & B2”，即可在编号前添加BX，如图1-8所示。 Step 03 选中A2单元格，向下复制公式，可快速在所有员工编号前添加BX，如图1-9所示。 4 第 1 章 Excel 公式编辑问题 编号前添 加字符 图1-8 图1-9 10．使用运算符默认的优先顺序不能满足计算要求，有没有办法改变运算符的优先顺序 运算符的优先顺序是可以改变的，此时需要借助于括号来实现。 Step 01 如图1-10所示，需要求取应缴纳税额，如果采用图中的公式，根据默认的运算优先顺序，很显 然计算结果是错误的。 Step 02 因此可以按如图1-11所示的方法添加括号，更改默认的运算顺序，从而得到正确的运算结果。 图1-10 图1-11 11．当公式很长时，有没有办法只查看某一步的计算结果 在一个复杂的公式中，若要调试其中某部分的运算公式或只想知道某部分的运算结果，此时可以通过下面 的操作来实现。 Step 01 选中含有公式的单元格，按F2键或双击，进入公式编辑状态。 Step 02 用鼠标选中需要查看其结果的部分公式，按F9键，即可计算出选中部分对应的结果，如图1-12 所示。 显示运算结果 图1-12 5 Excel 公式、函数、图表应用技巧800问 小知识： 示信息。 Step 03 查看后按Ctrl+Z组合键，即可还原。 在选择要查看结果的部分公式时，注意一定要选择完整，否则不能得到正确的结果，同时还会显示错误提 12．有没有办法在公式与运算结果之间进行切换 若要快速地在公式与运算结果之间进行切换，可以通过下面的操作来实现。 Step 01 在运行结果状态下，按Ctrl+`（“`”键位于Esc键的下方）组合键，即可显示公式内容。 Step 02 在公式内容状态下，按Ctrl+`组合键，即可显示运算结果。 13．如何快速选取工作表中所有包含公式的单元格 有时需要对工作表中所有包含公式的单元格加以保护，或设置与其他单元格不同的颜色以达到提醒的作用， 此时可按如下方法选中工作表中所有包含公式的单元格。 Step 01 切换到要选中其中公式的工作表，单击“开始”菜单项。 Step 02 在“编辑”工具栏中单击“查找和选择”按钮，打开下拉菜单，选择“公式”命令，此时该工 作表中所有公式都被选中了，如图1-13所示。 包含公式的单 元格都被选中 图1-13 14．如何一次性选中公式中引用的单元格 若要一次性选中公式中引用的单元格，具体操作如下。 Step 01 在工作表中选中B8单元格（B8单元格中的公式为“=SUM(B3:D3)*B6”）。 Step 02 在英文输入状态下按Ctrl+[组合键，即可快速选取公式中直接引用的所有单元格，如图1-14所示。 选中 B8 单元格 图1-14 6 第 1 章 Excel 公式编辑问题 15．如何隐藏公式 如果要将工作表中所有公式都隐藏起来，可以按如下方法来操作。 Step 01 在工作表中，单击行列交叉处的，可以选中整张工作表所有单元格，单击鼠标右键，在弹出 的快捷菜单中选择“设置单元格格式”命令，如图1-15所示。 单击此处选中整张工作表 图1-15 Step 02 打开“自定义序列”对话框，选择“保护”选项卡，选中“隐藏”复选框，如图1-16所示。 图1-16 Step 03 设置完成后回到工作表中，单击“审阅”主菜单，再单击“保护工作表”按钮，打开“保护工作 表”对话框，设置保护密码，如图1-17所示。 图1-17 Step 04 设置完成后，单击“确定”按钮，弹出“确认密码”对话框，提示再次输入密码。设置完成后， 选中输入了公式的单元格，可以发现无论是在单元格中还是在公式编辑栏中都看不到公式了，如图1-18所示。 7 Excel 公式、函数、图表应用技巧800问 图1-18 16．通过隐藏公式仍可以对公式计算结果进行更改，这时如何保护公式不被更改 要实现保护工作表中所有公式不被更改，可以按如下方法来操作。 Step 01 选中整张工作表，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“自 定义序列”对话框。选择“保护”选项卡，取消选中“锁定”复选框，如图1-19所示。 Step 02 单击“确定”按钮，即可解除对整个工作表的锁定。在工作表中单击“开始”主菜单，在“编辑” 工具栏中的“查找和选择”下拉菜单中选择“公式”命令，将工作表中所有公式选中，如图1-20所示。 图1-19 图1-20 Step 03 单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“设置单元格格式”对 话框 ，在“保护”选项卡下重新选中“锁定”复选框。 Step 04 单击“确定”按钮回到工作表中，在“审阅”主菜单下单击“保护工作表”按钮，打开“保护工 作表”对话框，设置保护密码。 Step 05 设置完成后，单击“确定”按钮，弹出“确认密码”对话框，提示再次输入密码。设置完成后， 选中输入了公式的单元格，当试图更改时，将弹出如图1-21所示的提示信息。 小知识： 在输入记录时要注意的问题？ 图1-21 如果要解除工作表保护，只需要选择“工具 → 保护 → 撤销工作表保护”命令，在弹出的对话框中输入保 护密码即可。 8 第 1 章 Excel 公式编辑问题 17．如何手动控制公式运算结果 默认状态下，在单元格中输入公式后，如果相关的单元格已经准备好数据，则公式会自动计算出结果；如 果相关的单元格没有准备好数据，则计算结果显示为“ 0 ”。而在公式引用的单元格中输入数据后，公式会自动 对其运算。如果想自行控制公式的运算结果，即在单元格中输入数据后利用手动方法来进行运算，可以按如下 方法来操作。 Step 01 单击Office按钮，选择“Excel选项”命令，打开“Excel选项”对话框。 Step 02 单击“公式”标签，选中“手动重算”单选按钮，如图1-22所示。 图1-22 Step 03 单击“确定”按钮，即可自行控制公式的运算结果。 18．如何将公式运算结果转换为数据而不再是公式 在单元格中输入公式之后，按 Enter 键即可计算出结果，但再次选中该单元格时，它仍然显示的是公式。如 果要将公式永久转换为数值，具体操作如下。 Step 01 选中包含公式的单元格，按Ctrl+C组合键进行复制，然后再按Ctrl+V组合键进行粘贴。 Step 02 此时会在选中单元格的右下位置出现一个“选择粘贴”按钮，单击该按钮打开下拉菜单，选择“只 有值”命令，此时原本包含公式的单元格就转换为数值了，如图1-23所示。 包含公式的单 元格只显示值 图1-23 19．可以只将某个公式中的部分公式转换为数值吗 如果并不想把整个公式都转换为数值，而只是想把公式的一部分转换为数值，也是可以实现的。 Step 01 首先选中包含公式的单元格，然后再选中公式中要转换为数值的部分。 9 Excel 公式、函数、图表应用技巧800问 Step 02 按F9键，然后再按Enter键，选中的部分公式就转换为了数值，而不再是公式了。 1.2 公式中数据源的引用问题 20．在默认状态下公式中的数据源是如何引用的 在默认状态下 Excel 2007 程序是以 A1 方式来引用数据源的，即通过引用字母标识和数字标识，通过横纵 相交来确认单元格。关于 A1 方式如何引用数据源，通过表 1-3 所示可以很清晰地看到。 表1-3 引 用 格 式 A8 B8:B18 C8:H8 C8:H18 D:D D:H 8:8 8:18 引 用 区 域 第 A 列和第 8 行交叉处的单元格 第 B 列中第 8 行到第 18 行的所有单元格 第 8 行中第 C 列到第 H 列的所有单元格 第 C 列第 8 行到第 H 列第 18 行所有的单元格 第 D 列全部单元格 第 D 列和第 H 列的所有单元格 第 8 行全部单元格 第 8 行和第 18 行的所有单元格 21．除了使用A1格式来引用数据源还有其他方式吗 除了按默认的 A1 格式来引用数据源，还可以利用 R1C1 格式引用数据源。它是通过引用 R 加行数字和 C 加列数字来指示单元格的位置。 R1C1 引用样式对于计算位于宏内的行和列非常有用，而且当录制宏时， Excel 也使用 R1C1 引用样式录制宏命令，而不是使用 A1 引用样式。但是这并不代表 A1 样式不能完全代表 R1C1 样 式， R1C1 能表示的 A1 样式同样也能表示，只是在宏里使用 R1C1 更容易些。 关于 R1C1 方式如何引用数据源，通过表 1-4 可以很清晰地看到。 表1-4 引 用 格 式 R2C8 R[2]C[8] R[ - 2] R[ - 2]C[8] R[2]C[ - 8] C 引 用 区 域 第 2 行和第 8 列交叉处的单元格 在当前光标所在位置，向下 2 行再向右 8 行所指的单元格 在当前光标所在位置，向上 2 行所有行单元格 在当前光标所在位置，向上 2 行再向右 8 行所指的单元格 在当前光标所在位置，向下 2 行再向左 8 行所指的单元格 当前光标所在位置的所有列单元格 在使用 R1C1 格式引用数据源之前，必须启用此引用方式。具体操作如下。 Step 01 单击Office按钮，选择“Excel选项”命令，打开“Excel选项”对话框。 Step 02 单击“公式”标签，选中“R1C1引用样式”复选框。设置完成后，回到工作表中可以看到工作 表的引用样式发生了变化，如图1-24所示。 10 第 1 章 Excel 公式编辑问题 引用样式发生了改变 图1-24 Step 03 如果不使用R1C1格式引用数据源，则只需要取消上面选中的选项即可。 22．什么是数据源的相对引用 在建立公式时需要使用大量数据源，默认使用的数据源引用方式就是相对引用方式。采用相对方式引用的 数据源，当将公式复制到其他位置时，公式中的单元格地址会随着改变。当多行或多列数据需要使用相似公式 进行计算时，使用数据源的相对引用方式是十分方便和快捷的。 Step 01 例如统计各个月份销售额总计值，可以首先统计出第一个月的销售总计值（注意数据源的引用要 使用相对引用）。 Step 02 接下来要求出2月份与3月份的销售总计值，则不需要重新建立公式，只需复制公式就可以了。 选中E3单元格，将光标定位到该单元格右下角，当其变为黑色十字形时按住鼠标左键向下拖动即可快速复制公 式，即求出2月份与3月份的销售总计值。 复制得到的公式，数据源自动更改，E3单元格的公式为“=SUM(B3:D3) ”，E4单元格的公式为 “=SUM(B4:D4) ”， 这正是所需要的求解结果，如图1-25所示。 图1-25 Step 03 另外，采用相对引用的数据源也可以横向进行复制。设置B6单元格的公式为“=SUM(B3:B5)”， 向右复制公式，可以看到数据源作相应更改，如D6单元格的公式为“=SUM(D3:D5)”，如图1-26所示。 图1-26 11 Excel 公式、函数、图表应用技巧800问 23．什么是数据源的绝对引用 所谓数据源的绝对引用，是指把公式复制或者填入到新位置，公式中的固定单元格地址保持不变。要对数 据源采用绝对引用方式，需要使用“ $ ”符号来标注。 Step 01 例 如采用公式“=SUM($B$3:$D$3)”来计算1月的销售总计值，可以得到正确值（注意使用了“$” 符号，即绝对引用方式），如图1-27所示。 Step 02 在计算2月、3月销售总计值时，如果直接复制公式，可以看到返回的计算结果相同，即公式没 有更改，如图1-28所示。 绝对引用数据源，复 制公式时数据不改变 图1-27 图1-28 由此可见，在这种情况下不适合使用绝对引用方式。 24．在同一个公式中可不可以既使用相对引用方式又使用绝对引用方式 在同一个公式中既使用相对引用的数据源又使用绝对引用的数据源，称为数据源的混合引用。这种方式不 但可以使用而且使用频率非常高。采用这种方式引用的数据源，在复制公式时，只有相对数据源改变，而绝对 数据源始终不改变。 例如统计各个月份应缴纳的税额，其计算方法是首先统计出各个月份总销售额，然后乘以税率。各个月份 总销售额是不同的，而税率是固定不变的。 Step 01 选中B9单元格，输入公式“=SUM(B3:D3)*$B$7”，注意求和区域采用相对引用，显示税率的单元格 采用绝对引用，如图1-29所示。 Step 02 复制公式，可以看到采用相对引用的单元格区域发生了变化，而采用绝对引用的单元格区域未发 生变化，例如B11单元格的公式更改为“=SUM(B5:D5)*$B$7”，如图1-30所示。 复制公式时，绝对引 用数据源不改变，相 对引用数据源改变 图1-29 图1-30 25．在公式中可以引用当前工作表之外的单元格吗 在进行公式运算时，很多时候都需要使用其他工作表的数据源来参与计算。在引用其他工作表的数据来进 12 行计算时，需要按如下格式来引用“‘工作表名’！数据源地址”。 第 1 章 Excel 公式编辑问题 例如在统计产品的销售额时，是按季度来进行统计的，要统计出各个季度的总销售额，则需要引用多张工 作表中的数据。 Step 01 选中要显示统计值的单元格，首先输入“=”号，如图1-31所示。 当前工作表 图1-31 Step 02 在“一季度销售额”工作表标签上单击，切换到“一季度销售额”工作表中，选中要求和的单元 格，可以看到引用单元格的前面都添加了工作表名称标识，如图1-32所示。 引用了“一季度销售 额”工作表中的数据 图1-32 Step 03 输入运算符号，然后切换到“四季度销售额”工作表中，按相同方法选择参与运算的单元格或单 元格区域，按Enter键即可得到计算结果（可以看到公式中凡是引用其他工作表中的数据，前面都添加了工作 表的名称），如图1-33所示。 返回计算结果 图1-33 26．在引用其他工作表中的数据源进行计算时出现“#NAME？”错误，怎么办 出现此情况可能是因为工作表名称中包含空格而导致的。如图 1-34 所示，“二季度销售额”工作表标签中 包含空格，而在引用其中数据源进行计算时，可以看到公式编辑栏中的工作表名称也包含着空格，此时按 Enter 键，就会显示“ #NAME ？”错误。 图1-34 出现此情况并非说明工作表名称中不能使用空格，只是当工作表名称中包含空格时，在引用数据源时要注 13 Excel 公式、函数、图表应用技巧800问 意，公式中数据表的名称要使用单引号。 如本例中只要将公式更改为“='二 季度销售额'!E5+三季度销售额!E5”，按Enter键即可得到正确值， 如图1-35所示 。 使用单引号 图1-35 27．如果想在公式中引用多个工作表中的同一单元格该如何操作 这种计算方式为日常工作带来很多便利。例如在本例中要计算全年的销售额总计值，需要将各个季度的总 销售额相加得到，而各个季度的总销售额分别显示在不同工作表的同一单元格，此时可按如下方法来操作。 Step 01 选中要显示统计值的单元格，首先输入“=SUM（”，如图1-36所示。 图1-36 Step 02 按住Shift键，利用鼠标在工作表标签中选中需要参与计算的工作表，此例为“一季度销售 Step 03 再选中相同数据源所在的单元格，此例为E6。输入右圆括号“）”，即公式为“=SUM(一 额:四季度销售额”（即“一季度销售额”～“四季度销售额”4张工作表）。 季度销售额:四季度销售额!E6)”，如图1-37所示。 小知识： 图1-37 上面公式的意义就是将“一季度销售额” ～ “四季度销售额” 4 张工作表中的 E6 单元格（该单元格中保存 的是各个季度的总销售额）的数值相加，从而统计出全年总销售额 。 28．能否引用多个工作簿中的数据源来进行计算 有时为了实现一些复杂的运算或是对数据进行比较，还需要引用其他工作簿中的数据进行计算才能达到求 解目的。多工作簿数据源引用的格式为“ [ 工作簿名称 ] 工作表名！数据源地址”。 例如本例中要计算本年销售额与上年销售额的差值，而上年销售额数据显示在一个名为“ 06 年销售统计” 的工作簿中，下面来看看如何设置公式进行比较操作。 14 第 1 章 Excel 公式编辑问题 Step 01 首先打开“06年销售统计”工作簿，这张工作簿的“全年销售统计”工作表的D2单元格中显示 了全年销售额总计值，如图1-38所示。 Step 02 在当前工作簿中选中显示求解值的单元格，输入公式的前半部分“=D3 - ”，如图1-39所示。 Step 03 接着切换到“06年销售统计”工作簿，选择参与运算数据源所在工作表（即“全年销售统计”）， 然后再选择参与运算的单元格或单元格区域（注意看公式编辑栏中的公式），如图1-40所示。 图1-38 图1-39 引用其他工作簿数据 小知识： 如果引用的数据源工作簿没有打开，则必须给出该工作簿在硬盘中的完整保存路径，否则将不能正确 计算出结果（建议将工作簿打开再引用）。 图1-40 29．用什么办法可以在相对引用和绝对引用之间进行切换 按 F4 键可以快速在相对引用和绝对引用之间进行切换。下面以 =SUM(B3:D3) 为例，依次按 F4 键，得到结 果如下。 Step 01 在包含公式的单元格上双击，选中公式全部内容，按F4键，该公式内容变为“=SUM($B$3:$D$3)”， 表示对行列单元格均进行绝对引用。 Step 02 第二次按下F4键，公式内容又变为“=SUM(B$3:D$3)”，表示对行进行绝对引用，列仍采用相 对引用。 Step 03 第三次按下F4键，公式则变为“=SUM($B3:$D3)”，表示对列进行绝对引用，行仍采用相对引用。 Step 04 第四次按下F4键时，公式变回到初始状态“=SUM( B3:D3 )”。 Step 05 继续按F4键，再次进行循环。 小知识： F4 键的切换功能，只能改变选中的公式的引用方式，对于没有选中的公式内容不进行任何改变操作。 30．如何通过复制公式快速进行计算 建立公式一方面可以实现复杂的计算，另一方面很多情况下工作表中需要使用相同或相似的公式来进行计 算，此时就可以利用复制公式的方法来进行计算。 15 Excel 公式、函数、图表应用技巧800问 Step 01 如图1-41所示，选中的E4单元格中包含求和公式“=SUM(B4:D4)”，按Ctrl+C组合键进行 复制。 Step 02 切换到“二季度销售额”工作表中，该工作表也需要求解合计值，因此可以选中E3单元格，按 Ctrl+V组合键进行粘贴，此时可以看到公式自动更改为“=SUM(B3:D3)”，如图1-42所示（注意复制前公式 为“=SUM(B4:D4)”，复制后公式为“=SUM(B3:D3)”，可见公式在进行复制时，会自动判断当前数据源，以 得到正确的计算结果)。 小知识： 图1-41 图1-42 这里使用相对数据源引用，对于使用绝对数据源引用，将不能实现快速复制公式来进行统计。 31．如何通过移动公式快速进行计算 除了使用复制公式来进行统计运算外，还可以利用移动公式来快速进行统计运算，这种方式适用于连续单 元格需要使用相似公式的情况。 Step 01 如图1-43所示，E3单元格中显示了计算总计值的公式“=SUM(B3:D3)”。此时可以通过移动公 式快速计算出其他月份的总计值。选中E3单元格，将光标移至单元格右下角处，当其变为黑色十字形时，按住 鼠标左键不放向下拖动，释放鼠标即可快速计算出其他月份总计值，如图1-44所示。 图1-43 图1-44 Step 02 除了可以纵向移动公式之外，还可以横向移动公式，功能相同，如图1-45所示。 图1-45 16 第 1 章 Excel 公式编辑问题 1.3 公式中名称的定义问题 32．在Excel 中定义名称有什么作用 在 Excel 2007 中，用定义的名称可以帮助简化公式编辑和搜索定位数据单元格区域。下面举例介绍几点。 1 ． 减少输入的工作量 如果在一个文档中要输入很多相同的文本，可以使用名称。例如，定义公司=“清华 大学出版社！”，那么在需要输入该文本的位置处输入“=公司”，都会显示“清华大学出版社！” 2 ． 定位 在大型数据库中，经常需要选择某些特定的单元格区域进行操作，则可以事先将这些特定的单元 格区域定义为文字名称。当再次需要使用时，可以单击“名称框”右侧下拉按钮，在下拉列表中选择名称，程 序会自动选中特定的单元格区域。 如图1-46所示，之前选择E列中的单元格区域定义为名称“基本工资”， 那么再次需要使用该单元格区域时，只需要在“名称框”下拉列表中选择“基本工资”名称，即可快速选中该 单元格区域。 3 ． 计算 简化了编辑公式时单元格区域的引用，并尽可能地减少出错几率。例如上面定义了“基本工资” 名称，如果要求基本工资总和，则可以使用公式“=SUM(基本工资)”，非常简捷。 名称框 图1-46 33．定义的名称名有没有规则限制 在定义单元格、数值、公式等名称时，定义的名称名不能是任意的字符，而需要遵循以下规则。 名称的第一个字符必须是字母、汉字或下划线，其他字符可以是字母、数字、句号和下划线。 名称不能与单元格名称相同。 名称之间不能有空格符，可以使用“ . ”。 名称长度不能超过 255 个字符，字母不区分大小写。 同一工作簿中定义的名称不能相同。 34．如何利用名称框来定义名称 利用名称框来定义名称具有方便快速的特点，具体操作如下。 Step 01 选中要自定义名称的单元格区域，将光标移到“名称框”中并单击，进入编辑状态，如图1-47 17 Excel 公式、函数、图表应用技巧800问 所示。 Step 02 输入要定义的名称（如“一季度毛利”），如图1-48所示。 定义名称 图1-47 图1-48 Step 03 输入完成后，按Enter键即可完成名称的定义。 35．如何使用“名称定义”功能来定义名称 除了使用名称框来定义名称外，还可以利用 Excel 2007 提供的“名称定义”功能来进行名称定义，具体操 作如下。 Step 01 选中要定义为名称的单元格区域，单击“公式”主菜单，在“定义的名称”工具栏中单击“定义 名称”按钮，如图1-49所示。 Step 02 打开“新建名称”对话框，设置名称名，如图1-50所示。 图1-49 图1-50 Step 03 设置完成后，单击“确定”按钮，即可完成名称的定义。 36．如何使用定义的名称来进行计算 在完成名称的定义之后，即可使用定义的名称来进行计算，具体操作如下。 Step 01 例如要计算一、二、三季度的销售额总计值，选中要显示求解结果的单元格，在公式编辑栏中输 入公式的前面部分“=SUM(” ，接着切换到“公式”主菜单下，在“定义的名称”工具栏中单击“用于公式” 按钮，打开下拉菜单，选择要应用的名称，如图1-51所示。 Step 02 接着再输入公式的后面部分，需要使用名称时按相同的方法引用，如图1-52所示（注意，公式 编辑栏中没有定义名称的单元格区域需要采用“工作表名称!单元格区域”的引用格式）。 18 第 1 章 Excel 公式编辑问题 参数使用了定义的名称 图 1-51 图 1-52 37．有没有办法一次性定义多个名称 在特定的情况下可以一次性定义多个名称，此时只能使用工作表中默认的行标识或列标识来作为名称名， 具体操作如下。 Step 01 在工作表中选中要定义名称的单元格区域，单击“公式”主菜单，在“定义的名称”工具栏中单 击“根据所选内容创建”按钮，如图1-53所示。 Step 02 在打开的“以选定区域创建名称”对话框中，可以根据需要进行选择，此处选中“最左列”复选 框，即以最左列的文字作为名称名（其他如“首行”，表示利用顶端行的文字标记作为名称名），如图1-54所示。 图1-53 图1-54 Step 03 设置完成后，在“名称”下拉列表中可以看到一次性定义的3个名称，如图1-55所示。 一次定义 了 3 个名称 图1-55 19 Excel 公式、函数、图表应用技巧800问 38．一个常量可以定义为名称吗 常量也是可以定义为名称的。当某一个数值经常需要使用时（如营业税率），则可以将其定义为名称来 使用。 Step 01 打开工作表，单击“公式”主菜单，在“定义的名称”工具栏中单击“定义名称”按钮，打开“新 建名称”对话框。 Step 02 输入名称名，如tax， 将“引用位置”中的区域直接删除，输入当前的营业税率（如：0.25）， 如图1-56所示。 图1-56 Step 03 单击“确定”按钮，即可完成名称的定义。 小知识： 定义的常量并不会显示在名称框中，这是因为常量不属于哪一个可知区域。但是它们可以直接使用，并且 出现在“定义的名称”工具栏中的“用于公式”和“粘贴名称”下拉列表中。 39．在将常量定义为名称之后，如何使用名称来进行计算 在将常量定义为名称之后，可以按下述方法引用名称进行计算。 Step 01 例如此处要计算各个月份应缴纳税额，其计算方法是各月销售总额乘以营业税率。选中要显示计 算结果的单元格，输入公式的前面部分“=E3*”，然后单击“用于公式”按钮，从下拉列表中选择名称tax， 如图1-57所示。 Step 02 按Enter键即可得到计算结果，其他单元格中同样可以使用所定义的名称来进行计算，如图1-58 所示。 图1-57 图1-58 20 第 1 章 Excel 公式编辑问题 40．不连续的单元格区域可以定义为一个名称吗 不连续的单元格区域也可以定义为名称。其方法是在定义之前就准确地选中不连续的单元格区域，然后进 行定义。 Step 01 使用Shift或Ctrl键配合鼠标准确选中要定义为名称的不连续的单元格区域，如图1-59所示。 Step 02 然后单击“定义的名称”工具栏中的“定义名称”按钮，按照前面的方法定义即可。 选择不连续的 单元格区域 图1-59 41．默认情况下定义的名称都是应用于整个工作簿，可以让定义的名称只应用于当前工作表吗 在默认情况下，工作簿中的所有名称都是工作簿级的，即定义的变量适用于整个工作簿。如果要定义只适 用于某张工作表的名称（称为工作表级的名称），该操作是可以实现的，而且在日常工作中还经常需要使用这 种方式来进行定义。例如， Sheet1 是用于记录公司 1 月份的销售数据，其中的 E1:E100 被定义名称为“销售总 额”。 Sheet2 与 Sheet1 的表格形式完全相同，用于记录公司 2 月份的销售数据，如果希望为 Sheet2 的 E1:E100 也定义名称为“销售总额”，此时可以按下面的方法来操作。 Step 01 首先在Sheet1工作表中选中要定义的单元格区域，打开“新建名称”对话框，输入名称名，然 后在“范围”下拉列表中选择Sheet1（当前工作簿的所有工作表都会显示在下拉列表中），如图1-60所示。 Step 02 接着切换到Sheet2工作表中选中要定义的单元格区域，打开“新建名称”对话框，输入名称名， 然后在“范围”下拉列表中选择Sheet2，如图1-61所示。 小知识： 图1-60 图1-61 除了这种定义方法之外，还可以在选中要定义的单元格区域之后，在名称框中，以如下格式来定义“工作表 名称!名称名”，即上面的两个名称分别为： Sheet1! 销售总额、 Sheet2! 销售总额。 21 Excel 公式、函数、图表应用技巧800问 42．如果想查看当前工作簿中定义的所有名称该如何操作 在定义了多个名称之后，要想快速查看所有定义的名称，可以使用 Excel 2007 中的“名称管理器”来实现。 打开要查看其中名称的工作簿，单击“公式”主菜单，在“定义的名称”工具栏中单击“名称管理器”按 钮，打开“名称管理器”窗口，如图1-62所示，其中每一个名称的引用位置都能清晰地看到。 图1-62 43．公式可以定义为名称吗 公式是可以定义为名称的，尤其是在进行一些复杂运算或实现某些动态数据源效果时，经常会将特定的公 式定义为名称。 Step 01 例如此处将一季度平均销售额的公式定义为名称，其方法是首先单击“公式”主菜单下的“定义名称” 按钮，打开“新建名称”对话框。 Step 02 输入名称名，将“引用位置”中的区域直接删除，输入“=SUM(一季度销售额!$B$3:$D$5)/3”， 如图1-63所示。 Step 03 将公式设置为名称之后，就可以使用名称进行运算了。如图1-64所示，在D7单元格中输入公式 “=average”，按Enter键即可得到一季度平均销售额。 图1-63 图1-64 44．定义名称之后，如果想更改其引用的位置就只能重新定义吗 定义名称之后，如果需要修改名称，只需要对其重新编辑即可，而不需要重新定义。具体操作如下。 Step 01 单击“公式”主菜单下的“名称管理器”按钮，打开“名称管理器”窗口，如图1-65所示。 Step 02 在列表框中选中要重新编辑的名称，单击“编辑”按钮，打开“编辑名称”对话框，如图1-66所 示。 Step 03 在“引用位置”栏中，可以手工对需要修改的部分进行更改，也可以选中要修改的部分，然后单 22 击右侧的“拾取器”按钮回到工作表中重新选择数据源。 第 1 章 Excel 公式编辑问题 选择要修 改的区域 图1-65 图1-66 Step 04 重新选择单元格区域，如图1-67所示（可以在当前工作簿的任意工作表间切换）。 重新选择单 元格区域 图1-67 Step 05 设置完成后，单击“编辑名称-引用位置：”对话框中的“拾取器”按钮回到“编辑名称”对话框 中，单击“确定”按钮即可完成名称的修改。 45．如何删除不再使用的名称 对于一些不再使用的名称，可以通过下面的操作来删除。 单击“公式”主菜单下的“名称管理器”按钮，打开“名称管理器”窗口，在列表框中选中要删除的名称， 单击“删除”按钮即可删除，如图1-68所示。 图1-68 23 Excel 公式、函数、图表应用技巧800问 1.4 “ 公式审核 ” 工具的应用问题 46．“公式审核”工具有什么作用 “公式审核”工具，顾名思义，就是利用该工具可以对公式进行审核，从而帮助用户在计算结果出现错误 时，能够快速找到问题所在。具体表现如下： 显示公式。 公式错误检查。 公式分步计算。 追踪引用的单元格与从属单元格。 47．如何利用“公式审核”工具快速查看工作表中所有的公式 利用 Excel 2007 中的“公式审核”工具可以快速地显示出当前工作表中所有的公式，具体操作如下。 切换到要显示公式的工作表中，单击“公式”主菜单，在“公式审核”工具栏中单击“显示公式”按钮， 即可将工作表中所有公式显示出来，如图1-69所示。 图1-69 48．如何使用“公式审核”工具中的“错误检查”功能 当公式计算结果出现错误时，可以使用“错误检查”功能来逐一对错误值进行检查，同时还可以给出导致 错误产生原因的提示，具体操作如下。 出“错误检查”对话框。 Step 01 选中任意单元格，在“公式”主菜单下的“公式审核”工具栏中单击“错误检查”按钮，即可弹 Step 02 在“错误检查”对话框中，可以看到提示信息，指出单元格C11中出现错误。主要原因是“值不 可用”。单击“下一步”按钮，根据向导逐一检查错误值，并获取错误值产生的原因，如图1-70所示。 24 第 1 章 Excel 公式编辑问题 图1-70 49．如何使用“公式审核”工具中的“公式求值”功能 使用“公式求值”功能可以分步求出公式的计算结果（根据优先级求取），如果公式没有错误，使用该功 能可以便于对公式的理解；如果公式有错误，则可以方便快速地找出导致错误的发生具体是在哪一步。 Step 01 选中显示公式的单元格，在“公式”主菜单下的“公式审核”工具栏中单击“公式求值”按钮， 即可弹出“公式求值”对话框，如图1-71所示。 图1-71 Step 02 单击“求值”按钮，开始对公式进行逐一求值。首先对B11进行赋值，如图1-72所示。 Step 03 单击“步入”按钮，按照赋值数据在A2:D8单元格区域中进行查找，如图1-73所示。 Step 04 单击“求值”按钮，即可返回计算结果，而此时显示错误值，如图1-74所示。 图1-72 图1-73 图1-74 25 Excel 在。 公式、函数、图表应用技巧800问 Step 05 通过上述分析得到是最后一步查找发生错误，此时需要查看AH_031在A2:D8单元格中是否 存 50．什么是“追踪引用单元格” 所谓追踪引用单元格是指查看当前公式是引用哪些单元格进行计算的。当公式有错误值时，通过该功能也 可辅助对公式错误原因进行查找。 选中要查看的单元格，在“公式”主菜单下的“公式审核”工具栏中单击“追踪引用单元格”按钮，即可 使用箭头显示数据源引用指向，如图1-75所示。通过箭头指向可以看到当前公式是引用B6:D6单元格区域进 行计算的。 图1-75 51．什么是“追踪从属单元格” 所谓追踪从属单元格是指在追踪引用单元格的基础上再进行追踪，即查看引用单元格又是通过哪些单元格 进行计算的。 Step 01 选中要查看的单元格，在“公式”主菜单下的“公式审核”工具栏中，首先单击“追踪引用单元 格”按钮。 Step 02 接着再单击“追踪从属单元格”按钮，此时显示结果如图1-76所示。 图1-76 Step 03 查看完成后，可以单击“移去箭头”按钮将箭头移动。 26
发布评论