《Excel数据处理与可视化》教案27——条件展现（二）.docx

《Excel数据处理与可视化》课程教案

授课题目

条件展现（二）

授课类型

理实一体条件展现

教学目标

1、掌握条件展现典型方法

2、掌握条件展现方法应用中的公式应用

重点

1、数据条件展现等方法

2、根据场景灵活选用条件展现方法

难点

1、根据场景灵活选用条件展现方法

教学过程

一用不同颜色突出显示前3名的数据

以突出显示第1名的数据为例。

（1）选中数据单元格区域，单击“开始”→“条件格式”→“新建规则”，如图334所示。

（2）在打开的“新建格式规则”对话框中，选择“使用公式确定要设置格式的单元格”命令，并在“为符合此公式的值设置格式”中输入公式“=B2=MAX($B2:$M2)”，如图335所示。

（3）单击“格式”按钮，在打开的“设置单元格格式”对话框的“填充”选项卡中添加一种背景色，即可将第1名的数据单元格填充背景色。

二、根据货号设置间隔色

在“为符合此公式的值设置格式”中输入公式“=MOD(INT(SUMPRODUCT(1/COUNTIF($A$2:$A2,$A$2:$A2))),2)”。

三在所有员工的工资表中，仅能查看本人信息

公式“=IFERROR(INDEX($A$9:$G$24,MATCH($C$2,$C$9:$C$24,0),COLUMN()),)”.

四商品到期的文字提醒不醒目，可加个图标

公式“=IF(B2=TODAY(),到期,IF(B2TODAY(),超期,IF(B2-TODAY()=3,即将到期,未到期)))

五实现智能添加单元格边框

在“为符合此公式的值设置格式”中输入公式“=A1”。

六突出显示多次考核业绩不合格的员工姓名

在“为符合此公式的值设置格式”中输入公式“=COUNTIF($B3:$G3,6)=3”。

七标记两组中不同的数据

（1）选中A2:A21单元格区域，单击“开始”→“条件格式”→“新建规则”，如图366所示。

（2）在打开的“新建格式规则”对话框中，选择“使用公式确定要设置格式的单元格”命令，并在“为符合此公式的值设置格式”中输入公式“=OR(EXACT(A2,$B$2:$B$21))=FALSE”，如图3-67所示，然后单击“格式”按钮，在打开的“设置单元格格式”对话框的“填充”选项卡中添加一种背景色。

通过以上两个步骤，将A列与B列中不同的身份证号码标记出来。

选中B2:B21单元格区域，重复以上两个步骤，只不过公式改为“=OR(EXACT(B2,$A$2:$A$21))=FALSE”，即可标记出B列与A列中不同的身份证号码

八计算合同到期天数，并设置“交通三色灯”提醒

（1）根据合同签订日期与合同期限计算合同到期日期。

在E2单元格中输入公式“=EDATE(C2,D2)-1”，按Enter键执行计算，再将公式向下填充，即可得合同到期日期。

EDATE函数的语法：EDATE(开始日期,开始日期之前或之后的月份数)。其中，月份数为正值将生成未来日期；月份数为负值将生成过去日期。

（2）计算距离合同到期日期的天数。

在F2单元格中输入公式“=IF(E2=TODAY(),DATEDIF(TODAY(),E2,d),合同到期)”，按Enter键执行计算，再将公式向下填充，可得距离合同到期日期的天数所示。

（3）用“三色交通灯”提醒距离合同到期日期的天数。

选中“距离合同到期日期的天数”一列，单击“开始”→“条件格式”→“新建规则”。

在打开的“新建格式规则”对话框中，选择“基于各自值设置所有单元格的格式”命令，并在“格式样式”中选择“图标集”，在“图标样式”中选择“三色交通灯”，设置根据数字类型显示各图标，同时输入数值范围。

通过以上设置，可得合同到期模板。

作业

条件展现数据练习

教学小结

本节知识：

用不同颜色突出显示前3名的数据

根据货号设置间隔色

在所有员工的工资表中，仅能查看本人信息

商品到期的文字提醒不醒目，可加个图标

实现智能添加单元格边框

突出显示多次考核业绩不合格的员工姓名

标记两组中不同的数据

计算合同到期天数，并设置“交通三色灯”提醒