王佩丰Excel24讲_第15讲：条件格式与公式

一、简单的条件格式

1.为特定范围的数值标记特殊颜色

• 将Grand Total列中大于1500000的值填充为红色
  首先选中Grand Total列的所有数值，然后点击“条件格式-突出显示单元格规则-大于”，输入1500000，并设置背景色为红色
  
  
  
  
• 将部门列中包含车间的标记为红色背景
  首先选中部门列的所有数据，然后点击“条件格式-突出显示单元格规则-文本包含”，输入“车间”，并设置背景色为红色
  
  
  
  

2.查找重复值

• 找到凭证号中的重复值
  首先选中凭证号数列的所有数据，然后点击“条件格式-突出显示单元格规则-重复值”，选择“重复值”，并设置背景色为红色（背景色可随意设置，一般默认即可）
  
  
  
  
  

3.为数据透视表中的数据制作数据条（切片器）

• 原数据
  

• 目标表
  

• 实现步骤

  • 首先选中原数据中任意一个单元格，点击“插入-数据透视表”，将“订购日期”拖入行标签，选中任意一个日期单元格，右键选择“组合”，将步长改为“月”
    
    
    
    
  • 再将“所属区域”拖入列标签，将“金额”拖入值，至此，目标表已完成
    

• 那么如何为数据透视表中的数据制作数据条呢？
  首先选中数据透视表中除“总计”外的所有数据，然后点击“条件格式-数据条”，选择任意一种填充样式，比如这里选择了“实心填充-红色数据条”。
  
  
  
  

• 现在想再上面的基础上多加一个筛选维度，比如“产品”
  选中数据透视表任意一个单元格，点击“插入-切片器”，选择“产品类别”，即可根据各产品类别进行筛选，比如根据“暖靴”筛选所有数据。
  
  
  
  
  

二、定义多重条件的条件格式

• 0-1000000 红色背景
  1000000-2000000 蓝色背景
  2000000以上 黄色背景
  条件格式的设置范围遵循从大到小原则，后做的会覆盖先做的
  • 首先选中Grand Total列的所有数据，并点击“条件格式-突出显示单元格规则-大于”，输入“2000000以上”，并设置背景色为黄色
    
    
  • 然后点击“条件格式-突出显示单元格规则-介于”，输入“1000000-2000000”，并设置背景色为蓝色
    
    
  • 最后点击“条件格式-突出显示单元格规则-小于”，输入“1000000”，并设置背景色为红色
    
    
    
    

三、使用公式定义条件格式

1.跨字段标记背景色

• 将数量大于100的项目日期标记为红颜色背景
  首先选中订购日期列的所有数据，并点击“条件格式-新建规则”，选择“使用公式确定要设置格式的单元格”，输入条件“D2>100”（注意D2是相对引用，会随着订购日期列的变化而变化），并设置背景色为红色
  
  
  
  
  

2.跨字段整行标记背景色

• 将数量大于100的项目整行标记为红颜色背景
  选中表格中的所有数据，并点击“条件格式-新建规则”，选择“使用公式确定要设置格式的单元格”，输入条件“$D2>100”（注意D2是混合引用，其实相当于除了D列外的所有列都会根据D列做判断，并将符合条件的每个字段数值标记为红色，总而组成整行标记为红色），并设置背景色为红色
  
  
  
  
  

3.使用weekday函数标记周末

• 标记周末为红色背景
  首先根据日期利用weekday函数计算出是周几，然后利用text函数将计算出的结果转化成可显示的文本；接着选中“日期”列，点击新建规则，选择“使用公式确定要设置格式的单元格”，输入条件“D2>“5””（因为这里D列是文本，因此需要加双引号），并设置背景色为红色
  
  
  
  
  
  
  

• 将周末整行标记为红颜色背景
  具体操作步骤同上，不同的点在于：
  1.选中整个数据区域，然后新建规则
  2.输入条件为混合引用“$D2>“5””
  
  
  
  
  

4.标记未来15天的日期

• 标记未来15天过生日的员工姓名为红色背景
  首先根据生日利用datediff函数计算出与2023/6/30的相差天数，然后利用text函数将计算出的结果转化成可显示的文本；接着选中“姓名”列，点击新建规则，选择“使用公式确定要设置格式的单元格”，输入条件“D2<“15””（因为这里D列是文本，因此需要加双引号），并设置背景色为红色
  
  
  
  
  

系列文章目录

第1讲：excel常用的基础操作
第2讲：excel单元格格式设置
第3讲：查找、替换、定位
第4讲：排序与筛选
第5讲：分类汇总、数据有效性
第6讲：数据透视表
第7讲：excel常用函数与公式
第8讲：if函数
第9讲：countif函数
第10讲：sumif函数
第11讲：vlookup函数
第12讲：match+index函数
第14讲：日期函数
第15讲：条件格式与公式
第16讲：简单文本函数
第17讲：数学函数
第18讲：lookup和数组
第19讲：indirect函数
第20讲：图表基础
第21讲：经典excel动态图表实现原理
第22讲：制作甘特图与动态甘特图
第23讲：双坐标柱形图、饼图美化与ppt图表
第24讲：宏表函数