第3章 Excel公式基础知识

1第3章公式基础知识Excel是办公室自动化中非常重要的一款软件，很多巨型国际企业都是依靠Excel进行数据管理。它不仅仅能够方便的处理表格和进行图形分析，其更强大的功能体现在对数据的自动处理和计算，具有技术先进、使用灵活方便等特点，能够有效地管理人们在企业管理中遇到的工资、税收、财务预算、产品销售统计、人员统计、员工绩效考评或计算存款利息等方面的数据表。这些数据表，简单的可能只有几行或几十行数据，复杂的则可能有几千或几万行数据。然而很多缺少理工科背景或是对Excel强大数据处理功能不了解的人却难以进一步深入，人们在应用Excel的深度方面仍然处于低级水平，往往只用它从事简单的表格制作和运算，没有发挥其强有力的数据管理与分析的作用。Excel是一个功能强大的数据分析软件，它提供了300多个函数和公式，可用于解决数学、财务、会计、证券、统计、工程计算、线性规划、线性分析及数据查找等方面的数学计算和数学分析问题。如何充分发挥Excel的功能，高水平地甚至是开发性地使用Excel，在企业现代化管理方面就显得非常重要。2数据的运算与分析是Excel的特色功能，它不仅能够建立用户日常工作中所用的各种数据表格，而且可以通过公式、函数对这些数据进行运算，并且将运算结果有条理地显示出来，这是普通的文本处理软件所不及的。主要内容：输入和编辑公式

Excel是如何计算公式的怎样防止公式被查看循环引用消息和校正技术公式中使用的不同的运算符公式错误类型Excel的变量求解功能33.1输入和编辑公式公式元素

可以输入到单元格中的公式共有5种元素：★运算符：+，-，*，/等★单元格引用：它们包括命名的单元格和范围(名称)、指向当前工作表的单元格、同一工作簿其它工作表中的单元格，甚至可以是其它工作簿工作表上的单元格。★值或字符串：如7.5，“年末结果”。★工作表函数和它们的参数：如SUM或AVERAGE以及它们的参数

★括号：它们可以控制着公式中各种表达式被处理的次序。

42、输入公式

有两种方法：手工输入和通过制定单元格引用来输入。公式一般都是以等号开头，以回车结束。也可以用@作为起始符，但回车后，系统自动将@转换成等号。按回车键后，单元格会显示出公式的结果。当单元格被激活时，公式本身只会显示在公式栏（编辑栏）中。

注意：

（1）.输入数组公式时，按Ctrl+Shift+Enter组合键，而不是单纯地按回车键；（2）.公式中可以使用被命名的单元格或范围，即可以用它们的名称代替单元格地址；（3）.输入公式时不需要使用任何空格和空行；（4）.一个公式最多可以容纳1024个字符，若超过该范围，就必须把公式分成多个公式。也可以创建一个自定义公式（使用VBA).3、编辑公式

让单元格进入编辑状态有几种方法：

★双击单元格；★

按F2键；★

选择要编辑公式的单元格，然后单击公式栏。注意：a灵活使用Shift、Ctrl+Shift、Home、End。

b公式→文本→公式

c可以使用公式栏作为计算器（F2→编辑→F9）dF9的使用561、运算符是公式的基本元素。一个运算符就是一个符号，代表着一种运算。Excel支持以下运算符：3.2在公式中使用的运算符

算术运算符：+、-、*、/、^、（%）。

比较运算符：=、>、<、<>（不等于），比较结果为真（True）和假（False）。

文本运算符：&

引用运算符：它可以将单元格区域进行合并运算，包括以下几种：

区域运算符：：联合运算符：，

交叉运算符：空格

这四种运算符的运算优先顺序为：引用运算符、算术运算符、文本运算符、比较运算符。+、-、*、/，加减乘除符号%百分号&文本连接符^求幂=、>、<、>=、<=、<>，逻辑比较符，分别是等于、大于、大于等于，小于等于、不等于。逻辑运算符的返回值是True/False：，单个空格引用运算符，分别是范围（区域）运算符、联合运算符、交叉运算符。72、运算符的优先级（除引用运算符外）

括号在Excel的内部优先次序中排在最前面，且可以嵌套（输入括号时，Excel有一个自动提示）。

优先次序：-负号%百分号

^求幂

*和/乘和除

+和-加和减

&文本连接符

=

>

<

>=

<=

<>逻辑比较符8

◆=“Part-”&“23A”

；得到一个新的字符串Part-23A

◆=A1&A2

；将单元格A1和A2的内容连接在一起

◆=6^3；求6的3次方，结果为216

◆=A1^3

；单元格作为参数

◆=216^(1/3)

；求216的立方根，结果为6

◆=A1<A2

；若A1的值小于A2的值，则返回TRUE，否则为FALSE

◆=OR(A1=100,A1=1000)

；如果A1包含100或1000，则返回TRUE

◆=AND(A1<100,A2<100）；如果A1和A2的值小于100，则返回TRUE

◆=（收入-支出）*税率；名称作为参数

◆=（（B2*C2+B3）*C3+B5*C4）*B6

；使用了嵌套括号

9103.3计算公式

如果改变了公式中使用的某个单元格，公式会显示一个新的结果，不需要自己的任何操作——Excel的自动计算模式当选用人工计算模式时，如果还有任何没有计算的公式，Excel状态栏中会显示“计算”。可以使用下面的快捷键重新计算公式：

※F9:在所有打开的工作簿中计算公式

※Sfift+F9：只计算被激活工作表中的公式，同一工作簿中的其它工作表不被计算；

※

Ctrl+Shift+F9：强制重新计算所有打开的工作簿

※Ctrl+Shift+Alt+F9：重新检查全部从属公式，然后强迫重新计算所有打开的工作簿。

◆

注意：选择“工具”→“选项”命令，可打开一个对话框，重新设置”重新计算“。113.4单元格和范围引用

大多数公式都会使用单元格和范围地址（如果只有一个单元格，可以使用名称）引用一个或多个单元格，单元格引用有4种类型，分别用美元符号加以区别

※

相对:是完全相对的引用。当公式被复制的时候，单元格引用将调整到新的位置上。例A1

※

绝对：是完全绝对的引用。当公式被复制的时候，单元格不会有变化，例如$A$1。※

行绝对：是部分绝对的引用。当公式被复制的时候，列部分调整，但是行部分不会有变化，例如A$1。※列绝对：是部分绝对的引用。当公式被复制的时候，行部分调整，但是列部分不会有变化，例如$A1。

1、引用本工作表的单元格①绝对引用绝对引用是指被引用的单元格与引用单元格之间的关系是绝对的，无论将该公式复制到任何单元格，公式中的引用还是原单元格。方法是在引用单元格的行和列标题前加上“$”符号例如：公式“=C3+D3+E3+F3”改为“=$C$3+$D$3+$E$3+$F$3”②相对引用相对引用是指被引用的单元格与引用单元格的位置关系是相对的，当将一个带有单元格引用的公式复制到其它单元格时，公式中引用的单元格将变成与目标单元格（即获得复制公式的单元格）一样相对位置上的单元格。例如：公式“=C3+D3+E3+F3”若将公式复制到H3,请仔细观察.12③混合引用混合引用是指被引用的单元格与引用单元格之间的关系既有相对的,也有绝对的。例如：公式“=$C3+D$3+$E3+F$3”当一个混合引用的公式复制到任何单元格时，绝对引用的部分将保持绝对引用的性质，而相对引用的部分依然保持相对引用的变化规律。方法是在引用单元格的行和列标题前加上“$”符号例如：将公式“=$C3+D$3+$E3+F$3”复制到H3,则公式变为“=$C3+E$3+$E3+G$3”。13

2、引用其它工作表或工作簿的单元格一个公式可以引用不同工作表中的单元格和范围，也可引用不同工作簿中的单元格，方法是：=sheet2!A1+1;

=[Budgt.xls]sheet1!A1+1=‘[Budgt

Analysis.xls]sheet1’!A1+1=‘C:\Excel\[BudgtAnalysis.xls]sheet1’!A1+1注意：引用中出现的连接符“！”、文件名、路径等14

如公式：＝‘c:\学生成绩[book1.xls]sheet1’A1+sheet2!B3+sheet3!C4

提示1：若引用的工作簿book1.xls已经打开，则在公式中可以省略工作簿文件的路径部分，直接应用＝‘[book1.xls]sheet1’A1+sheet2!B3+sheet3!C4即可。

提示2：在引用其它工作表中的单元格时，同样可以使用绝对引用、相对引用和混合引用，且各个引用的性质保持不变。1516方法一：1、双击A1，激活编辑模式（或按F2)；

2、按End键，接着按Shift+Home选中所有公式文本，或者可以使用鼠标拖动选中全部公式；

3、单击“常用”工具栏中的“复制”按钮（或Ctrl+C），将选中的文本复制到剪切板上；

4、按回车键结束编辑模式；

5、激活单元格A2；

6、单击“常用”工具栏上的“粘贴”按钮（或Ctrl+V），将公式文本粘贴到单元格A2。3.5准确地复制公式

有两种方法：例如：将A1中的公式复制到A2。方法二：1、先删除A1公式中的等号，即将公式转换成了文本；

2、再选择“复制”到A2，编辑原始公式，加上等号即可。3.6把公式转换成值1、选中要转换的一个或多个公式；

2、单击“常用”工具栏中的“复制”按钮（或Ctrl+C）；

3、选择“编辑”→“选择性粘贴”命令，Excel显示“选择性粘贴”复选框；

4、选择“数值”单选项，单击“确定”按钮；

5、按回车键或ESC键，取消粘贴模式。1718方法：

1、选中公式或多个公式

2、“格式”菜单→“单元格”。在“单元格格式”对话框中，单击“保护”选项卡。

3、选中“隐藏”复选框。

4、选择“工具”菜单→“保护”→“保护工作表”命令，来保护工作表，还可设置密码。

注意：默认状态下，所有单元格都是被“锁定的”。3.7隐藏公式19Excel错误值：

#DIV/0：0作为除数

#NAME?：不能识别的名称

#N/A：使用NA函数所识别的不能使用的数据

#NULL!：公式中使用了一种不允许出现交叉但交叉在一起的两个区域

#NUM!：问题与值有关。如负值

#REF：公式中引用了一个无效的单元格

#VALUE：公式中包含了错误形式的变量或运算对象3.8公式中的错误注意：正确处理和识别循环引用20很多电子表格包括的公式允许你提出问题。例如：贷款额度问题。

求解：还款期限（月）36，利率为6.08%，如果月还款2000元，贷款额度为多少？3.9单变量求解（后台求解）做如下操作：B5输入贷款额度B6输入还款期限(月)B7输入利率(年)B8输入月还款C5为要求的贷款额度?不用输入数据C6输入36C7输入6.08%C8输入公式=C5/36+C5*C7/12

然后，将光标定位于C8中，执行Excel菜单“工具”→“单变量求解”,在出现的单变量求解对话框中选择1）目标单元格中选择C82）目标值中输入1539.293）可变单元格中选择C54）按确定，C5将显示贷款额度为46866.0721为单元格和范围（图表、图形）命名是一个好办法，它可以提供很多方便之处：名称可以增强公式的可读性，方便使用，容易记忆，不易出错，更为直观。一、创建名称方法有3种：使用“定义名称”对话框“插入”→“名称”→“定义”命令（添加）使用名称框自动创建“插入”→“名称”→“指定”命令

（创建多工作表名称）名称的命名规则……3.10使用名称22二、一个名称的范围

有：工作簿级名称（全局名称）工作表级名称（本地名称）三、使用范围和单元格名称工作

一旦创建了范围和单元格名称，就可以以不同方式来使用它们。

1、创建一个名称列表使用“插入”菜单→“名称”→“粘贴”命令（或F3），若需要粘贴一个名称列表，单击“粘贴列表”按钮。23

2、在公式中使用名称因单元格和范围是工作簿级名称，故可以在此工作簿中任何表中使用该名称，即用名称代替单元格引用。书写公式时使用定义好的表单上工作表级名称，就不需要把工作表名包括进范围名称中。如果在不同的工作表公式中使用这个名称，就一定要使用全名（表名、叹号和名称）。例如:=SUM（销售额）

=SUM（单变量求解！销售额）在键入公式时可以选择“插入”→“名称”→“粘贴命令（或F3），显示“粘贴名称”对话框，选择列表中的名称，单击“确定”按钮，Excel将插入这个名称到公式中。243、使用带名称的交叉运算符Excel的交叉运算符是一个空格

例如：=SUM(B1:C20A8:D8)；两个范围交叉位置包含2个单元格：B8和C8.

=SUM(名1名2）

范围交叉、隐式交叉4、使用带名称的范围运算符Excel的范围运算符是一个冒号（：）

例如：=SUM(DATA1:DATA2)5、使用多单元格命名范围中的单个单元格

假定A1:A50被命名为DataRange，

有公式：=INDEX(DataRange，2);即显示的是A2的值=INDEX(DATA3，4，5)；即返回第4行和第5列的值6、在现有公式中使用名称

在示例工作表Sheet2中进行操作。7、创建公式时自动使用名称当通过指向的方法插入一个单元格或范围引用到一个公式时，如果这个单元格有名称，Excel可以自动地应用该单元格和范围的名称。258、不应用名称Excel没有提供直接的方法实现不应用名称。就是说，不能自动地使用实际的单元格引用去替换公式中的名称。（精简版无此功能）9、删除名称选择“插入”→“名称”→“定义”命令，显示“定义名称”对话框，在列表中选择希望删除的名称，单击“删除”按钮，即可。注意：删除名称的后果。10、删除命名单元格或范围

如果删除包含命名单元格和范围的行和列，这些名称不会被删除。此外每个名称都包含一个无效引用。为了避免出错，需手工删除这个名称，或重新定义这个名称。11、重新定义名称

在定义好一个名称后，也许希望改变引用的单元格和范围。选择“插入”→“名称”→“定义”命令，显示“定义名称”对话框，适当编辑，即可26

12、改变名称选择“插入”→“名称”→“定义”命令，显示“定义名称”对话框，创建新名，删除旧名。当改变名称的时候，Excel不能自动改变调整使用名称的公式，但是可以使用“编辑”→“替换”命令，查找并使用新名称替换旧名称。13、查看命名范围当缩放一个工作表到39%或更小的时候，你会看到命名范围的边界，同时包括使用蓝色字体显示的名称。边界和名称是不能打印出来的，但它们有助于观察工作中的命名范围。14、在图表中使用名称当创建一个图表时，每个数据系列都有一个相关的SERIES公式，这个SERIES公式包含图表中使用的范围引用。如果定义了范围名称，就可编辑一个公式，并替换这个名称的范围引用。27四、Excel如何维护范围和单元格名称

一旦创建了范围和单元格名称，Excel在编辑或修改这个工作表的时候，可以自动地维持你这个名称。1、插入行或列

2、删除一个行和列

3、剪切和粘贴

注意：如果删