excel公式与函数基础docWord文档格式.docx

excel公式与函数基础docWord文档格式.docx

《excel公式与函数基础docWord文档格式.docx》由会员分享，可在线阅读，更多相关《excel公式与函数基础docWord文档格式.docx（7页珍藏版）》请在冰豆网上搜索。

'

＜、＜=、〉、〉=和＜〉

比较

7

Excel中有两种比较特殊的运算符：

逗号和空格。

使用逗号分隔两个单元格区域时，说明在一个公式里需要同时使用这两个区域。

例如，COUNT（A1:

A3,A5:

B6）表示统计A1:

A3和A5:

B6两个区域中包含数字的单元格总数。

如果使用一个空格来连接两个区域，则表示要得到两个区域的交集，即重叠的区域。

例如＜A1:

B5A3:

B6）的交叉部分包含的单元格有A3、A4、A5、B3、B4、B5这6个单元格。

二、数组公式

一个数组公式可以完成多步计算。

与输入普通公式不同，需要使用【Ctrl+Shift+Enter］组合键输入数组公式，Excel会自动在公式两侧添加一对大括号，以此来表明这是数组公式而非普通公式。

选择数组公式所在的区域：

1、单击数组公式所在区域的任一单元格，然后按【Ctrl+/】组合键。

或2、单击数组公式所在区域的任一单元格，然后按【F5】键打开【定位】对话框，单击【定位】按钮打开【定位条件】对话框，选择【当前数组】单选按钮，【确定】。

修改数组公式：

如果同时在多个单元格中输入同一数组公式，将无法单独编辑数组公式所在的某个单元格内的公式。

，要编辑这样的数组公式，需要先选中数组公式所在的整个区域，然后按【F2】键,再修改，最后按【Ctrl+Shift+Enter】组合键确认修改。

删除方法与编辑类似，选中所有区域，再按【Delete】键

扩展或缩小多单元格数组公式：

1、

选择包含数组公式的区域，然后按【F2】键进入单元格

的编辑的状态。

2、按【Ctrl+Enter］组合键，将数组公式改为普通公式，并将公式输入到区域内的每个单元格中。

然后扩展选区，以便包含新数据的单元格。

3、

再次按【F2】键，进入单元格的编辑状态，修改公式中

的区域引用。

然后按［Ctrl+Shift4-Enter］组合键,以数组公式的形式将公式输入到新区域的每个单元格内。

三、A1引用和R1C1引用

在默认情况下，工作表单元格区域的顶部由字母A、B、C等大写英文字母组成，而左侧由1、2、3等数字组成，这种表示单元格的方式称为A1引用样式。

在另一种引用样式R1C1引用样式中用字母R+行数字以及字母C+列数字来引用某个单元格。

（相对引用：

在R、C后面的数字上加上方括号；

绝对引用：

不加方括号）

将R1C1样式改为A1样式的具体操作如下：

1、单击【文件】选项卡，选择【选项】命令

2、选择【公式】选项卡，取消选中右侧的【R1C1引用样式】复选框。

审核公式

1、使用公式错误检查器

如果Excel检测到单元格中包含错误，当单击该单元格时，单元格附件将显示错误检查器的图标，单击该图标，弹出菜单顶部显示的是错误的类型，菜单中其他命令的含义如下：

A、关于此错误的帮助：

选择该选项，将打开帮助窗口，并显示相应的错误帮助主题。

B、显示计算步骤：

选择该选项，可通过分步计算检查发生错误的位置。

C、忽略错误：

选择该选项，将不理会发生的错误，保留公式的当前值。

D、在编辑栏编辑：

选择该选项，将允许用户直接在公示栏中修改公式错误，在状态栏中显示“编辑”字样。

E、错误检查选项：

选择该选项，可设置Excel对错误的检查

规则

跟踪单元格关系

追踪错误单元格时，需要了解一下3个概念：

A、引用单元格：

被其他单元格的公式引用的单元格。

例如，单元格B1中包含公式“=A1+A2”，单元格A1和A2就是单元格B1的引用单元格，更确切地讲

是直接引用单元格。

如果上面公式的单元格A1又包含公式“=A3*A4”，那么单元格A3和A4就是单元格B1的间接引用单元格

B、从属单元格：

从属单元格中包含引用其他单元格的公式。

例如,单元格B1中包含公式“=A1+A2”，那么单元格B1就是单元格A1和A2的从属单元格。

C、错误单元格：

在公式中直接或间接引用的、包含错误的单元格。

要追踪为公式提供数据的单元格（引用单元格），需要先单击包含公式的单元格，然后单击功能区中的【公式】一【公式审核】一【追踪引用单元格】按钮。

这样讲显示所有为该公式提供数据的单元格，并使用箭头由这些单元格指向包含公式的单元格。

（在追踪错误时，红色箭头表示有错误的单元格，蓝色箭头表示无错误的单元格。

）

使用公式求值器：

如果出错的公式很复杂，由多个计算步骤组成，那么查找

错误根源时会比较费力。

使用Excel的分步计算功能可以

将复杂计算过程分解为单步计算，找到错误出在哪里，以便快速解决公式问题。

要进行分步计算，需要先单击出错公式所在的单元格，然后单击功能区中的【公式】一【公式审核】一【公式求值】按钮，打开公式求值对话框。

在【公式求值】对话框中还有两个按钮——【步入】和【步出】，当要求值的公式包含从属单元格时，【步入】按钮将变得可用。

单击该按钮，将显示当前单元格的第一个从属单元格。

如果该单元格还有从属单元格，还可以再次单击【步入】按钮，显示间接从属单元格。

如果不想显示从属单元格，可以单击【步出】按钮。

五、处理公式中的错误

1、以#号填充单元格

有时输入公式后，单元格会被#号填充。

出现这种情况可能有以下原因：

A、列宽不足以容纳单元格中的内容。

B、单元格包含一个无效的日期或时间。

第一个问题很好解决，只需加大列宽即可。

第二个问题则需要检查公式中的日期是否在Excel的有效范围之内。

Excel不支持1900以前的日期或负的时间值。

2、空但非空的单元格

有些单元格中看似并无任何内容，但是使用ISBLANK函数或COUNTA函数进行判断或统计时，这些看似空白的

单元格仍被计算在内。

3、返回错误值

下表列出了Excel中的7种错误值，每类错误值都以符号

#开头。

错误值

发生原因

#DIV/O!

当数字除以0时，出现该错误

#NUM!

如果公式或函数中使用了无效的数值，出现该错误

#VALUE!

当在公式或函数中使用的参数或操作数类型错误时，出现该错误

#REF!

当单元格引用无效时，出现该错误

#NULL!

如果指定两个并不相交的区域的交点，出现该错误

#NAME?

当Excel无法识别公式的文本时，出现该错误

#N/A

当数值对函数或公式不可用时，出现该错误

A,#DIV/O!

错误及解决方法

可能包含以下情况：

a、公式除数中包含数字0。

b、公式中的除数包含值为0的单元格或空白单元

格。

解决方法：

检查输入的公式中是否包含除数为0的情

况；

如果除数为一个空白单元格，则Excel会将其当

做0来处理。

可通过修改该单元格的数据或单元格的

引用来解决问题。

B、#NUM!

可能包含以下几种情况：

a、当公式或函数需要数字参数时，传递给公式或函数的却是非数字参数。

b、公式或函数使用了一个无效参数。

例如：

公式“=SQRT（-4）”

C、一个使用迭代算法的函数并且该函数无法得到

计算结果。

IRR和RATE函数。

d、公式的返回值太大或太小，超出Excel限制的范

围。

Excel对值得限制范围是-IE-307〜1E307

确保函数的参数类型正确；

确保输入的参数为有效参数；

检查公式的返回值是否超出Excel的限制。

C、#VALUE!

错误及解决方法可能包含以下情况：

a、当公式或函数的参数类型不正确时。

例如，公式“=SUM（abc,def）”将返0#VALUE!

错误值，因为SUM函数的参数必须是数字。

b、当需要使用单个值却使用了区域

C、输入或编辑数组公式后未按【Ctri+Shift+Enter】组合键确认公式的输入。

解决方法:

确保公式或函数使用的参数类型正确无误；

检查公式中的引用范围是否正确；

确保在输入数组公

式时按下【Ctrl+Shift+Enter】组合键。

#REF!

错误及解决方法可能包含以下几种情况：

a、删除了已经被公式引用的单元格。

b、复制公式到其他位置时，其中的单元格引用变

为无效，

C、剪切一个单元格，然后将其粘贴到公式使用的单元格中，公式将返回#REF!

错误值。

撤销已删除的被公式引用的单元格；

确保引用的单元格未出现在工作表范围之外。

E、#NULL!

使用空格运算符连接两个不相交的单元格区域时，就会返回错误值#NULLL

确保两个区域有重叠部分，或改用其他引用运算符连接不同的区域。

F、#NAME!

a、公式中使用了未定义的名称。

b、函数名称或已定义的名称拼写不正确。

C、公式中的文本未使用双引号括起、区域引用中

未输入冒号分隔、引用其他工作表时未使用单引号括起。

d、公式使用了未安装的加载项中的函数

检查是否使用了工作表或工作薄中不存在

的名称，如果是则重新定义该名称；

检查公式中是否存在未加双引号的文本，如果有则用双引号括起来；

区域引用中为使用冒号“：

”分隔，在区域引用中添加冒号。

G、#N/A错误及解决方法可能包含以下几种情况：

a、手工标记单元格为暂时缺少的数据。

例如，在单元格中输入#N/A或公式“=NA（）”。

b、省略了函数中必不可少的参数。

c、使用LOOKUP、VLOOKUP等函数查找值时，当要查找的值小于查找区域中的第一个值时，将返回#N/A错误值。

d、参加运算的数组为同一个方向，且尺寸不同。

解决方法：

删除手工标记的错误值#?

4/八；

将函数的必选参数输入完整；

修改LOOKUP、VLOOKUP等函数的参数，保证可以得到正确查找结果。

六、公式使用技巧

1、显示公式：

【Ctrl+'

】

（【'

】为【ESC】键下方的按键），

切换公式与计算结果，或单击【公式】一【公式审核】一【显示公式】按钮，在工作表显示所有公式。

2、查看公式的中间结果荘公式栏中选中要查看结果的部分，按【F9】键，将得到选中部分的计算结果。

3、将公式转换为值：

如果公式不需要再变化，可以将公式转换为数值，防止由于误操作而意外改变公式结果。

可以使用下面任一种方法：

A、选择公式所在的单元格，然后单击编辑栏，接着选中整个公式后按【F9】键，得到计算结果，最后按【Enter】键。

B、选择公式所在单元格，按【Ctrl+C】键复制该单元格。

然后，•右击该单元格，在弹出的菜单中选择【粘贴选项】中的【值】图标。

复制公式但不使用相对引用

当公式中包含单元格的相对引用时，复制这个公式到其他单元格时，Excel会自动调整其中的相对引用。

如果希望按原样复制公式，可以使用下面的方法：

在公式的编辑栏直接复制，然后粘贴到目单元格。

隐藏公式

步骤1选择包含要隐藏公式的单元格或单元格区域右击选区，在弹出的菜单中选择【设置单元格格式】命令。

步骤2切换到【保护】选项卡，选中【隐藏】复选框，【确定】。

步骤3单击功能区中的【审阅】一【更改】一【保护工作

表】按钮，在打开的对话框中输入一个密码，【确定】，并再次输入密码。

禁止任何人修改公式

要对工作表中的所有公式进行包含最好的方法是使用定位条件快速选择所有包含公式的单元格，然后在对工作表设置保护，具体操作方法如下：

步骤1单击工作表左上角的标记选择工作表中的所有单元格,步骤2右击选区并选择【设置单元格格式】命令，然后在【保护】选项卡中取消选中【锁定】复选框，【确定】步骤3按【F5】键，打开【定位】对话框，单击【定位条件】按钮。

步骤4选中【公式】单选按钮及其下方的4个复选框，然后单击【确定】按钮。

步骤5再次打开【保护】选项卡，重新选中【锁定】复选框。

最后按隐藏公式的方法对工作表进行保护。