iFangyi 发表于 2015-12-10 21:29:21

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

本帖最后由 iFangyi 于 2015-12-11 15:17 编辑


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

不知道为什么表格粘贴过来保存后颜色就没有了,只好使用帖子编辑器上的着色按钮进行着色,实在太难看!
而且不能上传图片,呵呵。版主是否可以给我开通一下上传图片的功能,谢谢了:loveliness:
有需要原版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$2:$L$2“

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

第三,应用规则到所有行:
前面已经建好了第二行的规则,怎么把第二行的规则应用到所有行呢?拖动,对!

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

OK,大功告成,快试试吧!
恩?!什么鬼,为什么所有行都变成了黄色?

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

[*]然后继续输入根据上面的需求继续在”=$J$2“后输入=“Ongoing”,引号也得要写,输完后输入框里最终的结果是=$J$2=“Ongoing”
$J$2这种方法叫做绝对应用,相应的还有相对引用。采用绝对应用创建的规则,拖动到其他行,其他行只会根据你从哪里拖动来的就从那里判断,所以才出现上面所有行的颜色都根据表格J2来变化。


如何处理呢?改成相对引用即可,即把第二步建立的三条规则里的$J$2改成$J2,然后再执行一遍第三步即可。
处理完之后自动变色,快去试试吧!
WeekProjectSWTaskCase
    numberStartDeadline预期完
    成时间实际完
    成日期ResultTesterComment
         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 发表于 2015-12-14 11:24:12

写的很多很全面,非常值得学习!

iFangyi 发表于 2015-12-16 20:57:28

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

:handshake
页: [1]
查看完整版本: Excel的一个小技巧-如何利用”$“快速建立公式