Excel的一个小技巧-如何利用”$“快速建立公式

iFangyi

软件测试工作中有一项很重要的事情就是任务跟踪，其实也是很多部门的一个重要事情。
面对众多的测试任务，使用Excel来跟踪、记录、维护是最常用的方式了。
一般，记录任务的表格会有很大的数据量，不同时间、不同内容的任务堆积到一张表格里很难区分，用颜色来加以区分是一个很不错的办法。
那么如何根据某一列的内容来确认某一行的颜色呢？
可以选择VBA、可以选择IF函数，还可以选择条件公式。这种小问题选择条件公式绰绰有余了。

不知道为什么表格粘贴过来保存后颜色就没有了，只好使用帖子编辑器上的着色按钮进行着色，实在太难看！
而且不能上传图片，呵呵。版主是否可以给我开通一下上传图片的功能，谢谢了
有需要原版Excel的，请留下邮箱。
以下以07版Excel为例


第一，确定需求：
以上文表格为例，我需要实现的效果是根据J列Result的内容来确定本行的颜色，那么在此分解需求如下，以第二行为例：

• 如果J2为”Ongoing“则J2所在行第二行为绿色
• 如果J2为”Cancel“则J2所在行第二行为灰色
• 如果J2为”Block“则J2所在行第二行为黄色
• 如果J2为”Close“则J2所在行第二行为无色
  

第二，建立规则：
首先创建某一行的规则然后应用于其他行，以第二行为例，因为第一行是表头无需创建规则。

• 开始>条件格式>管理规则
• 新建规则，选择“使用公式确定要设置的单元格”，在“为符合此公式的值设置格式（O):"这里选择你要以哪一个单元格的内容变化为判断条件。所以刚才“为符合此公式的值设置格式（O):"这里选择表格J2，或者直接在输入框里输入”=$J$2“即可。
• 然后继续输入根据上面的需求继续在”=$J$2“后输入=“Ongoing”，引号也得要写，输完后输入框里最终的结果是=$J$2=“Ongoing”
• 然后点击下方”格式“按钮，选择”填充“，选择你要填充的颜色，根据第3步填写的“Ongoing”，这里我们选择绿色，然后点击确定返回到第一步”管理规则“的位置
• 有一列内容是”应用于“，这里的应用于意思就是当表格J2改变时你所应用于的表格就会变成你规则里设置的颜色，这里我们选择A2：L2，也可以直接输入”=$A$2L$2“
  

至此，我们已经为第一个需求建立好了一个规则，接下来就重复2、3、4、5的步骤把需求的第2和第3点分别创建两个规则，由于Excel表格默认填充就是无色，所以需求的第4点可以不用创建规则。
这时，在”管理规则“这里就可以看到三条规则，分别对应于需求里的第1、2、3点。点击”确定“关闭”管理规则“对话框，到Excel里试一下改变表格J2的值，A2：L2是不是会自动变颜色！
  
第三，应用规则到所有行：
前面已经建好了第二行的规则，怎么把第二行的规则应用到所有行呢？拖动，对！

• 选中区域A2：L2，光标停留在所选区域方框的右下角
• 这时光标变成黑色十字架”＋“
• 点住黑色十字架”＋“不放开向下拖动到你所需要的行
• 放开鼠标
  

OK，大功告成，快试试吧！
恩？！什么鬼，为什么所有行都变成了黄色？

Week	Project	SW	Task	Case     number	Start	Deadline	预期完     成时间	实际完     成日期	Result	Tester	Comment
									Block
									Cancel
									Ongoing

最后的最后：美元符号
到底是哪里出了问题？让我们来分析一下：
目前看来所有行的颜色都跟随了表格J2的内容而变化，J3/J4的内容对其所在行的颜色起不到控制作用。是不是拖动出现了问题？
没错，就是在上一步拖动第二行的规则到所有行时出现了问题。
还记得上面第二步建立规则时的第3步么，在输入框里输入=$J$2=“Ongoing”，就是这个$J$2出了问题。

• 然后继续输入根据上面的需求继续在”=$J$2“后输入=“Ongoing”，引号也得要写，输完后输入框里最终的结果是=$J$2=“Ongoing”
  

$J$2这种方法叫做绝对应用，相应的还有相对引用。采用绝对应用创建的规则，拖动到其他行，其他行只会根据你从哪里拖动来的就从那里判断，所以才出现上面所有行的颜色都根据表格J2来变化。


如何处理呢？改成相对引用即可，即把第二步建立的三条规则里的$J$2改成$J2，然后再执行一遍第三步即可。
处理完之后自动变色，快去试试吧！

Week	Project	SW	Task	Case     number	Start	Deadline	预期完     成时间	实际完     成日期	Result	Tester	Comment
									Block
									Cancel
									Ongoing

补充：Excel中的相对引用和绝对引用
百度百科，看不懂的可以去百度，一大把，字数限制，就不贴了。

file:///C:\Users\yongkui.niu\AppData\Roaming\feiq\RichOle\3822452028.bmp
file:///C:\Users\yongkui.niu\AppData\Roaming\feiq\RichOle\3822452028.bmp

fhhh_eyou

写的很多很全面，非常值得学习！

iFangyi

fhhh_eyou 发表于 2015-12-14 11:24
写的很多很全面，非常值得学习！