公式单元格引用条件格式.docx

公式单元格引用条件格式.docx

《公式单元格引用条件格式.docx》由会员分享，可在线阅读，更多相关《公式单元格引用条件格式.docx（11页珍藏版）》请在冰豆网上搜索。

公式单元格引用条件格式

授课班级

会计1、2、3、4、5、6班

授课日期

课题

公式、单元格引用、条件格式

学时

2

教

学

目

标

知识目标了解Excel公式的作用及公式的结构

了解Excel中数据的调用——单元格引用，条件格式

技能目标能自己编写公式

能根据需求灵活设计条件格式

会用引用调用不同区域数据及不同表格的数据

素质目标培养学生对Excel的认识及学习兴趣

教学重点难点及突破

公式的构成

单元格引用——相对、绝对、混合、多表数据引用

条件格式

教学资源

教材、多媒体、Excel帮助文件、参考书

教

学

环

节

安

排

一、回顾上节课内容

二、新课引入

三、本节内容讲解

四、课堂知识总结

五、师生互动，学生提问，教师答疑

六、布置课后作业——复习、预习

教

学

后

记

教学步骤与内容

教学随记

1、回顾上节课内容

数据格式化、美化表格、记录单（数据清单）的建立、数据有效性、自定义数字格式

2、新课引入——公式、单元格引用

三、本节内容讲解

公式

公式：

就是由用户自行设计并结合常量数据、单元格引用、运算符等元素进行数据处理和计算的算式。

公式是为了有目的地进行计算，因此公式必须（且只能）返回值.

构成公式的元素通常包括等号、常量、引用和运算符等元素。

公式还可以使用数组、函数、名称（命名公式）来进行计算。

在输入公式时必须以等号“＝”开头。

（系统也允许用加减号为公式的开始，系统会自动前置等号，否则只能识别为文本）

如果用户希望对原有公式进行编辑，可以用以下几种方法进入单元格编辑状态：

选中单元格，并按下键；双击公式所在的单元格；选中单元格，单击编辑栏。

如果希望在连续的区域中使用相同公式，可以通过“双击”或“拖动”单元格右下角的填充柄进行公式复制；如不连续区域，可以借助“复制”和“选择性粘贴粘贴”完成。

使用快捷键在连续区域中填充公式与填充值得方法相同：

选择E3：

E7后，再按下可以实现公式的快速复制。

公式中的运算符

运算符是构成公式的基本元素之一,Excel包含4种类型的运算符:

算术运算符:

用于加、减、乘、除等算术运算

比较运算符：

用于比较数据的大小，包括文本或数值的比较

文本运算符&：

用于将文本字符进行连接、合并

引用运算符：

用于单元格引用

运算符说明及其优先级如下表：

符号

说明

实例

-

算术运算符：

负号

=8*-5=-40

%

算术运算符：

百分号

=60*5%=3

^

算术运算符:

乘幂

=3^2=9

*和/

算术运算符:

乘和除

=3*2/4=1.5

+和-

算术运算符:

加和减

=3+2-5=0

=<>

><

>=<=

比较运算符:

等于、不等于、大于、小于、大于等于、小于等于

=（A1=A2）判断A1与A2是否相等

=（B1<>"ABC“）判断B1不等于"ABC“

=（C1>=5）判断C1大于等于5

&

文本运算符:

连接文本

="Excel"&"在会计中的应用"

:

区域运算符:

冒号

=SUM（A1:

A10）对A1:

A10区加总求和

（空格）

交叉运算符:

单个空格

=SUM（A1:

B5A4:

D9）相当有A4：

B5求和

联合运算符:

逗号

=SUM（A1,A10）

数据的比较：

在EXCEL中，除错误值外，文本、数值、逻辑值之间都存在大小关系。

各类数据的大小排列顺序为：

…、-2、-1、0、1、2、…、A-Z、FALSE、TRUE

即数值小于文本，本文小于逻辑值，错误值不参与排序（不区分英文大小写）

文本型数值与数值是两个不同的概念，如一定要比较文本型数值与数值的大小，可以将两者相减的结果与0比较大小来实现。

运算符的优先顺序：

系统中按照从左向右的顺序进行公式运算，当公式中使用多个运算符时，将根据运算符的优先级进行运算，对于同一级次运算符，则按自左向右的顺序运算。

优先级次

符号

说明

1

:

空格,

引用运算符

2

-

算术运算符：

负号

3

%

算术运算符：

百分比

4

^

算术运算符：

乘幂

5

*和/

算术运算符：

乘和除

6

+和-

算术运算符：

加和减

7

&

文本运算符：

连接文本

8

＝,>,<,<>,>=,<=

比较运算符:

比较两个值

括号的优先级高于上表的运算符。

比较运算符不区分大小写。

编辑公式时如果有问题，Excel会出现错误信息，常见的错误信息有以下几种：

（1）####当使用了负的日期或负的时间时，出现错误

（2）#DIV/0！

当数字被0除时，出现错误

（3）#VALUE！

当使用的参数或操作数类型错误时，出现错误

（4）#NAME?

当EXCEL未识别公司中的文本时

（5）#N/A当数值对函数或公式不可用时

（6）#REF!

当单元格引用无效时

（7）#NUM!

公式或函数中使用无效数字值时

（8）#NULL!

当指定并不相交的两个区域的交点时。

逻辑值与数值的互换准则

在EXCEL中，逻辑值与数值的关系为：

（1）在四则运算中，TRUE＝1，FLASE＝0

（2）在逻辑判断中，0＝FALSE，所有非0数值＝TRUE

（3）在比较运算中，数值<文本

这些准则在公式的编写和优化中有重要作用

认识单元格引用

引用是Excel的一个重要特征，Excel使用单元格在工作表中所处位置的行列坐标，来标示对该单元格的引用。

引用是Excel函数与公式的构成要素之一引用的作用在于标识工作表上的单元格或单元格区域，并指明公式中所使用的数据的位置。

通过引用，可以在公式中使用工作表中不同单元格内的数据，或者在多个公式中使用同一单元格内的数据。

引用样式

A1引用样式

是以“字母+数字”的形式来标记单元格地址（列标在前+行号在后）

同时还可以对整行或整列进行引用，如5:

5，则表示引用第五行，C:

C表示引用第C列。

R1C1引用样式

是以“R+行号数字+C+列号数字”的形式来标记单元格地址（行号在前列号在后），既可以标记单元格的绝对位置，还能标记单元格的相对位置。

相对引用、绝对引用

相对引用：

当公式的位置发生改变时，引用的单元格也随之改变。

即：

公式所在单元格与公式中所引用的单元格的相对位置关系不变。

绝对引用：

是指公式中所引用的单元格位置固定不变。

当公式的位置发生改变，所引用的单位格保持不变。

混合引用

行相对、列绝对引用：

即仅在行方向上为相对引用，而在列方向上为绝对引用；

列相对、行绝对引用：

即仅在列方向上为相对引用，而在行方向为绝对引用。

注：

在A1引用样式中，不同引用方式的区别在于：

绝对引用和混合引用使用了美元符号“$”来锁定行号或列号，所有前置“$”的行号或列号在公式的复制过程中都保持不变，如$C$5，$A10。

R1C1引用样式中，相对引用中的行列号使用成对的中括号[]进行包含，而绝对引用直接使用数字表示。

在变换引用方式时，手工输入符号非常繁琐。

在编写公式过程中，当选择或输入单元格引用后，再按下，系统会自动在4种引用方式之间进行循环切换。

切换顺序为：

绝对引用——列相对行绝对引用——行相对列绝对引用——相对引用。

多工作表区域的引用

若希望在公式中引用其他工作表的单元格区域，系统会自动在引用前添加工作表名，引用格式为：

工作表名+半角感叹号+引用区域。

若工作表名中包含空格、特殊符号或其他首字符为数字，则对须用一对单撇号“’’”其首位进行标识。

如果希望引用多个连续工作表的相同区域，可以使用“引用运算符”，按工作表的排列顺序将起始表名和终止表名进行连接，而不需要手工逐个输入。

如：

=SUM（‘1:

5’!

A1），还可以使用单击起始表，再按下SHIFT键单击终止表来完成多个连续工作表引用的输入。

另外，可以很方便生成出对当前工作表之外的左右两侧多个工作表相应区域的引用，而不用考虑所引用的工作表是否连续。

如=SUM（‘*’!

A1）

对其他工作簿中工作表进行引用时，引用格式为：

[工作簿名称]工作表名称!

单元格引用

条件格式

EXCEL的条件格式是指：

可以先预置一种单元格格式，并在指定的某种条件被满足时自动应用于目标单元格。

可预置的单元格格式包括：

边框、底纹、字体颜色等。

利用此功能可以快速对特定单元格进行必要的标识，以起到突出显示的作用。

要对某个单元格或单元格区域设置条件格式，可按照以下步骤进行操作：

1、选定目标

2、菜单“格式”—“条件格式”弹出对话框

3、在“条件格式”对话框的“条件1

（1）“下拉列表中选择一种条件类型（单元格数值或公式）

4、设置具体的条件。

步骤3选择不同，条件设置也有所不同，条件可以是常量、单元格用或公式

5、单击“格式”按钮，弹出“条件格式”对话框。

在格式对话框中设置条件为真时要应用的格式。

如要添加条件格式，单击“添加”按钮，重复步骤3~5。

然后单击“确定”按钮，完成设置。

注：

当添加了多个条件格式时，EXCEL按顺序判断，如所有条件都不满足，则不应用格式。

如有一个以上的条件同时被满足时，则只应用顺序在先的条件所对应的格式。

条件个数最多只能有3条。

数值条件

当条件格式的条件类型为单元格数值时，只能以本单元格的数据为基准与其他数据（可以是常量、单元格引用或公式结果）对比。

设置单元格数值条件时有以下几个判断选项：

介于与未介于

1、用来判断单元格的数值是否在某一区间内，如条件成立，即单元格的数值介于或未介于某一区间内，则应用条件格式中所设置的格式。

2、等于与不等于：

用来判断单元格数值等于或不等于目标值，如条件为真，则应用条件格式中设置的格式。

示例：

标识指定的行政区单元格

3、大于与小于：

用来判断单元格的数据大于或小于某个数据，如条件为真，则应用格式。

4、大于或等于：

用来判断单元格的数据大于或等于某个数据。

如条件成立，则应用格式。

5、小于或等于：

用来判断单元格的数据小于或等于某个数据，如条件为真，则应用该格式，用法与大于或等于相同。

公式条件

当条件格式的条件类型为公式时，可处理比较复杂的条件，而且条件既可以与本单元格数值有关，也可以与本单元格数值无关。

示例：

在成绩表中标识出总分低于80的行

步骤：

1、选定单元格区域，菜单“格式”—“条件格式“

2、在条件下拉列表中选择“公式”，在文本框中输入“＝$G3<80”。

3、单击“格式”按钮，在弹出的对话框中选择“图案”，选择颜色为茶色。

4、单击“确定”，关闭对话框。

本例中，除了G列单元格的条件与单元格本身数据有关，其他的都与本单元格无关，只要成绩小于80,就要应用条件格式中所设置的格式。

注：

公式“=$G3<80”中使用了行相对列绝对的混合引用，当公式复制到其他单元格时，列的位置不变，都绝对引用G列，行的位置随单元格位置的改变而相对改变。

四、课堂知识总结

五、师生互动，学生提问，教师答疑

六、布置课后作业——复习、预习