7مائیکروسافٹ آفس ایکسل 2010گریڈ وی کے اہداف کے لئے مشروط فارمیٹنگ

تو, میری دوسری زندگی میں میں نے اصل میں ایک سائنس ٹیچر ہوں, اور یہ عام طور پر میں نے "کمپیوٹرز کے ساتھ اچھا" ہوں کہ دور ہو جاتا ہے کے طور پر میں نے عام طور پر چند چیزوں پر ایک نظر ہے کرنے کے لئے کہا ہو. میں نے ان کا ہدف گریڈ کے مقابلے کوڈ طالبہ ٹیسٹ کے نتائج رنگ کرنا ایکسل میں مشروط فارمیٹنگ قائم ہوتا تو دو بار گزشتہ چند مہینوں میں مجھے کہا گیا ہے. بالکل اس سائٹ کے کور کا حصہ نہ کی حالت, ابھی ویسا ہی ہے DIY tech­nic­al help so I decided to pub­lish a full set of instruc­tions here. Enjoy…

Microsoft Excel Conditional Formatting: Lookup TablesFirst you need a lookup sheet

  • Cre­ate a new sheet in the workbook.
  • Fill in 2 columns – the left with grades (1ج, 1B, 1ایک, 2c etc) and the right with a “score” value (can just be 1, 2, 3, 4, 5 وغیرہ)
  • Fill in 2 addi­tion­al columns with a “reverse look­up” which is the same as before but with the 2 columns reversed
  • High­light the full left hand table that you’ve cre­ated, صحیح اس پر کلک کریں, and select “define name”. Give it a name, 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 NameCreating the worksheets

  • Now cre­ate a work­sheet for each class, group or year (as suits).  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

Building the basic conditional formatting

  • 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, but it isn’t quite as simple as that, because of the way excel com­pares val­ues – so we need to use the look­up tables.
  • We also need 5 rules – 2 sub­levels or more below, 1 ذیل میں, on tar­get, 1 above and 2 or more above. You can cre­ate more rules if you want more detail or less if you just want below, on and above target.
  • 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 of 5 rules for a single cell.

Microsoft Excel Conditional Formatting: Format CellsExpanding the conditional formatting for multiple rules

  • Next repeat the pro­cess to cre­ate 4 more rules.
  • 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 (which we come to next)
=VLOOKUP($C3,Lookup,2,FALSE)-1>VLOOKUP(D3,Lookup,2,FALSE)
=VLOOKUP($C3,Lookup,2,FALSE)>VLOOKUP(D3,Lookup,2,FALSE)
=VLOOKUP($C3,Lookup,2,FALSE)=VLOOKUP(D3,Lookup,2,FALSE)
=VLOOKUP($C3,Lookup,2,FALSE)+1<VLOOKUP(D3,Lookup,2,FALSE)
=VLOOKUP($C3,Lookup,2,FALSE)<VLOOKUP(D3,Lookup,2,FALSE)
  • I use medi­um red, light red, yel­low, medi­um green and light green (in that order) for back­ground colours

Sorting the order of the rules

  • آخر, we need to make sure the rules are in the right order, 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…”
  • Make sure the rules are in the right order – as above (medi­um red, light red, yel­low, medi­um green, light 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$5Put this in all 5 “applies to” boxes
  • This sheet should now fully work with con­di­tion­al formatting

Expanding easily to additional sheets

  • 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, into the new sheet.
  • Then click on the con­di­tion­al format­ting but­ton, select man­age rules, and change the 5 “applies to” boxes to point to the cells of the new sheet which need con­di­tion­al formatting

جواب چھوڑیں

7 Comments

CMچلو مور

ہیلو جان,

کیا آپ کے پاس ایک شیٹ کی مثال ہے جو آپ نے بنائی ہے۔? Strug­gling fol­low­ing the steps here!

جواب دیں
GZGary Zawadzki

ہیلو,
Great tutori­al — thanks. مجھے مل گیا ہے 3 dif­fer­ent col­ours work­ing per­fectly but the 2 above and 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?

شکریہ,

Gary

جواب دیں
JSJon 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. I’ll recheck this guide in the next day or 2 to make sure there aren’t any typo­’s or mis­lead­ing parts, it’s been known to happen 😉

جواب دیں
ایEmma

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??

جواب دیں
JSJon Scaife

I assume you have pop­u­lated them with data, and the tar­get column is also pop­u­lated with data?
By all means zap me the file over and I’ll take a quick look — I’ve e‑mailed you

جواب دیں