|
每天一点小技能 职场打怪不得怂  编按：在使用自定义条件格式时，常常遇到明明很简单却无法正确显示结果的情况？那，很有可能是你在相对引用和绝对引用的选择中弄错！这是个小问题，却总能让你和成功失之交臂。那么，我们究竟要如何避免重蹈覆辙？  自定义条件格式，是使用Excel时经常会用到的一个功能。但是，笔者总能听到有小伙伴说，在实际操作过程中，经常做不出预期的效果，要么是一些不满足条件的单元格变成了自定义的格式，要么是整个工作表都变成了自定义的格式。这究竟是为什么呢？其主要原因，是没有用好自定义条件格式中的相对引用和绝对引用。今天，大家一起来探索其中的奥秘吧！ 以某学校的成绩表为例，A至E列分别是序号、姓名、班级、数学成绩、语文成绩，如下图所示。 公众号回复：入群，下载练习课件 问题：如何通过自定义条件格式，将数学和语文都大于等于90分的成绩，设置成浅绿色填充？ 对条件格式有所了解的小伙伴肯定知道，设置自定义条件格式的第一步，就是选定要设置条件格式的区域。 1 常常使用的错误方案一 本例中，由于要设置自定义条件格式的是数学成绩和语文成绩两种成绩，所以我们首先选中D2:E12（注意：不要把表头字段D1、E1选中）。然后，依次点击“开始”－“条件格式”－“新建规则”，弹出“新建格式规则”对话框，再点击其中的“使用公式确定要设置格式的单元格”。 在“为符合此公式的值设置格式”下面的函数框中输入“=AND(D2>=60,E2>=90)”，如下图所示。（注：点击本界面中右下角的“格式”按钮后，弹出“设置单元格格式”对话框后，点击“填充”选项卡，然后在下面的备选颜色中，选择浅绿色，再点击“确定”，即可将填充颜色设置为浅绿色。） 点击“确定”之后，在弹出的“条件格式规则管理器”对话框中可以看到，“应用于”下面的函数框中显示的是“=$D$2:$E$12”，这就是大家之前选中的D2:E12区域，即应用自定义条件格式的区域，不过Excel自动为它加上了绝对引用符号，这个不用理它。 点击“确定”后，“条件格式规则管理器”对话框消失。这时，表格中有些数据已经被标绿了，如下图所示。仔细观察，其所标绿的单元格，没能满足我们的预期要求，如E2、E4、E10，都大于等于90，应该被标绿，但未被标绿。自定义格式操作没成功！ 2 常常使用的错误方案二 大家先通过“开始”－“条件格式”－“清除规则”－“清除整个工作表的规则”，将刚才自定义的条件格式删除，然后重新设置自定义条件格式。 这次，大家在函数框中，将AND函数中的D2、E2变成绝对引用，在函数框中输入“=AND($D$2>=90,$E$2>=90)”。（注：也可通过“开始”－“条件格式”－“管理规则”－“编辑规则”来修改函数，但是为了让之前的操作不影响到我们接下来的操作，笔者还是建议清除规则之后重新建立规则。） 使用此种方法设置条件格式之后，做出来的效果如下图所示，D2:E12全部标绿。又没成功！ 3 正确使用方法 将刚才自定义的条件格式删除，完成前面步骤以后，大家将函数框中的函数D2、E2改为混合引用，在函数框中输入 “=AND($D2>=90,$E2>=90)”。 使用此种方法设置自定义条件格式之后，做出来的效果如下图所示，达到了大家的预期要求。成功了！喜大普奔！
 以上三种方案的思路解析： 上面一共做了三次尝试，在函数框中写入的函数分别是“=AND(D2>=60,E2>=90)”、“=AND($D$2>=90,$E$2>=90)”、“=AND($D2>=90,$E2>=90)”。 熟悉Excel的小伙伴，都知道“$”是Excel中标识绝对引用的符号，如果不写“$”，则为相对引用。 一般情况下，大家在Excel中写函数的时候，随着单元格向下或者向右复制，在单元格中是可以看到相对引用和绝对引用随着单元格位置的变化而变化的。但是，在自定义条件格式的时候，大家在函数框中只能输入一次函数，是无法看到绝对引用或者相对引用的变化的，这就常常让人很费解，感觉看不见、摸不着。 那么，大家该怎么理解这个问题呢？确保自己的引用没有使用错误呢？ 大家要明白自定义条件格式中一个很重要的对应关系：在函数框中输入的公式，其参数的引用关系是对应于所选择的自定义条件格式区域中的第一个单元格（即最左上角的那个单元格）来写的；同时，函数是带着引用关系自动扩展到应用自定义条件格式区域中的其他单元格的。 比如，本例中，在我们输入的AND公式中，公式参数中的引用关系是针对D2单元格写的，但是它会自动扩展到E2、D3，E3、D4、E4、……、D12、E12，扩展时引用会发生变化。 下面，笔者将依次剖析一下三种方案，其中每一次单元格引用的变化。 方案一的错误解析： 第一种方案中，大家使用的是纯相对引用，公式是以针对D2写的，当公式扩展E2、D3:E12时，每一次都是对单元格右侧的两个单元格进行判断，如果二者都大于等于90，则标绿。 ① 以D2为例，它判断的是D2、E2是否同时大于等于90。本例中，由于第二行中A1同学的数学成绩和语文成绩均满足条件，故D2被标绿。 ② 以E2为例，由于函数中使用的是纯相对引用，所以,E2单元格判断的是E2和F2是否同时大于等于90。本例中，E2为99，但是F2中没有内容， E2、F2同时大于等于90这个条件不成立，故E2未被标绿。 其他单元格，以此类推，如下图所示：
方案二的错误解析： 第二种方案中，大家使用的是纯绝对引用，当公式扩展到其他单元格时，都是对D2和E2的关系进行判断。 ① 以D2为例，函数判断的是D2、E2是否同时大于等于90。本例中，D2、E2均大于等于90，所以D2被标绿。 ② 以E2为例，函数判断的也是D2、E2是否同时大于等于90。本例中，D2、E2均大于等于90，所以E2被标绿。 D3:E12中的每一个单元格，判断条件都是D2、E2否同时大于等于90，本例中，由于D2、E2这两个单格均大于等于90，所以就出现了D2:E12全部被标绿的效果。
方案三的解析： 第三种方案中，大家使用的是混合引用，用“$”锁定的是列，没锁定行，当公式扩展到其他单元格时，列不变，行变。 ① 以D2为例，函数判断的是D2、E2是否同时大于等于90，本例中，D2、E2均大于等于90，满足条件，故标绿； ② 以E2为例，函数判断的是D2、E2是否同时大于等于90，本例中，D2、E2均大于等于90，满足条件，故标绿； 以D3为例，函数判断的是D3、E3是否同时大于等于90，本例中，D3、E3均小于等于90，不满足条件，故未标绿； 以E3为例，函数判断的是D3、E3是否同时大于90等于，本例中，D3、E3均小于等于90，不满足条件，故未标绿。 其他单元格，以此类推，如下图所示。
好了，小伙伴们，讲了这么多，自定义条件格式中的相对引用和绝对引用，你弄明白了吗？一定要多操作几次，自己模拟一下数据，才能更好地掌握哦！ 留一个小练习：如下图所示，请通过自定义条件格式，将第1列A2:A16单元格与第1行B1:E1单元格中字母一致的单元格标绿！
操作方法如下图所示，你做出来了吗？
在线咨询Excel课程 Excel教程相关推荐 我花了5小时，整理出这13个办公中最实用的Excel技巧（建议收藏） |
|
|
