6Microsoft Office Excel中 2010条件格式年级v目标

所以, 在我的其他生命我其实是一名科学教师, 并且,往往会让一轮我“好了电脑”我通常会问到有没有看看几件事情。两次在过去的几个月中,我一直在问我是否愿意设立条件格式在Excel相比,他们的目标产品用颜色标记学生的考试成绩。虽然不完全与这个网站的核心内容, 它还是 DIY tech­nic­al help so I decided to pub­lish a full set of instruc­tions here. Enjoy…

Microsoft Excel Conditional Formatting: Lookup Tables首先你需要一个查找表

  • Cre­ate a new sheet in the workbook.
  • 填写 2 列 - 左边有傲人的成绩 (1Ç, 1b, 1一, 2Ç等。) 并用“得分”值的权利 (可以只是 1, 2, 3, 4, 5 等等)
  • 填写 2 addi­tion­al columns with a “reverse look­up” which is the same as before but with the 2 列对调
  • High­light the full left hand table that you’ve cre­ated, 右键单击它, 并选择“定义名称”。给它一个名字, e.g. “look­up”
  • High­light the second table you cre­ated, right click and define anoth­er name. Give it a name, e.g. “reverse_lookup”

Microsoft Excel Conditional Formatting: Define Name创建工作表

  • Now cre­ate a work­sheet for each class, 组或年 (西服).  Include a column for tar­get grades and vari­ous columns for test or assess­ment res­ults (e.g. 如下)
  • Note in this example that tar­get data is in C3 – C5. Res­ults are in D3 – F5

Microsoft Excel Conditional Formatting: Example Markbook


  • Select cell D3 – the top left of the res­ults data cells. Make sure you are on the “home” tab and click on “con­di­tion­al format­ting” and select “new rule”
  • Select “use a for­mula to determ­ine which cells to format”
  • What we want to do is com­pare the cur­rent cell to the tar­get, 但它并不像这么简单, because of the way excel com­pares val­ues – so we need to use the look­up tables.
  • 我们还需要 5 规则 - 2 sub­levels or more below, 1 下面, on tar­get, 1 与以上 2 or more above. You can cre­ate more rules if you want more detail or less if you just want below, 对以上目标.
  • The even­tu­al for­mula for 2 sub­levels or more below will be =VLOOKUP($C3,Lookup,2,FALSE)-1>VLOOKUP(D3,Lookup,2,FALSE) 

    Microsoft Excel Conditional Formatting: New Rule Formula

  • The C3 in the first part refers to the top tar­get cell, and the D3 refers to the top actu­al res­ult cell. The “Look­up” refers to the look­up table range that we named earlier.
  • To break this down – we’re look­ing up numer­ic­al val­ues for both the tar­get and the actu­al score in the cur­rent cell. Because we want 2 sub­levels or more below we take 1 off the tar­get and say it must still be great­er than the score. The $ before the C of C3 ensures that when we use the same for­mula for oth­er columns they will still be com­pared to the tar­gets column.
  • Now click on format and apply the format­ting you want. I use a back­ground col­our of light red
  • Now we have cre­ated 1 的 5 用于单个小区的规则.

Microsoft Excel Conditional Formatting: Format Cells扩大对多条规则的条件格式

  • Next repeat the pro­cess to cre­ate 4 更多的规则.
  • When all are fin­ished my 5 examples would look as below. Only the com­par­is­on and pos­sible addi­tion or sub­trac­tion change between them. Also note the order (我们来到下一个)
  • I use medi­um red, 红灯, yel­low, medi­um green and light green (在该顺序) for back­ground colours


  • 最后, 我们需要确保这些规则以正确的顺序, and apply them to ALL the res­ults cells, not just the single res­ult cell D3.
  • Click on the con­di­tion­al format­ting but­ton again and choose “man­age rules…”
  • 确保这些规则以正确的顺序 - 如上 (medi­um red, 红灯, yel­low, medi­um green, 葱绿)Microsoft Excel Conditional Formatting: Rules Manager
  • Click in the “Applies to” box and enter the full range of cells which will con­tain res­ults. In my example this is =$D$3:$F$5.  把这个在所有 5 “适用于”盒子
  • This sheet should now fully work with con­di­tion­al formatting


  • To apply to oth­er sheets simple copy and paste a single cell which already has con­di­tion­al format­ting applied to it, 进入新的工作表.
  • Then click on the con­di­tion­al format­ting but­ton, select man­age rules, 并改变 5 “applies to” boxes to point to the cells of the new sheet which need con­di­tion­al formatting

发现这个有用? 请不要让我们知道通过下降到低于评论. 如果您想订阅,请使用菜单上的订阅链接右上方. 您还可以通过使用下面的链接社会分享这与你的朋友. 干杯.


6 条评论


I would love to re-post this art­icle on my own web­site, 会是这样好吗?


Great tutori­al — thanks. 我有 3 dif­fer­ent col­ours work­ing per­fectly but the 2 与以上 2 below aren’t format­ting to their cor­rect col­ours. They are chan­ging to red and green but not the shades that they need to change to. Any sug­ges­tions please?



的gravatar乔恩·斯卡夫(Jon Scaife)

Sorry for the long delay in reply­ing Gary — I’ve been busy migrat­ing the site to our new VPS. Did you man­age to get this sor­ted? If you did­n’t then by all means zap me over your excel file to have a look at. 我会在第二天重新检查本指南或 2 to make sure there aren’t any typo­’s or mis­lead­ing parts, 人们知道发生😉


I have fol­lowed all your instruc­tions above but none of my cells have gone col­oured?? Any sug­ges­tions where I’m going wrong??