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

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

First you need a lookup sheet

• Cre­ate a new sheet in the workbook.
• Fill in 2 columns – the left with grades (1ج, 1b, 1a, 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, مثلا. “look­up”
• High­light the second table you cre­ated, right click and define anoth­er name. Give it a name, مثلا. “reverse_lookup”

Creating 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 (مثلا. مندرجہ ذیل طور پر)
• Note in this example that tar­get data is in C3 – C5. Res­ults are in D3 – F5

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)
  

  

• 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 rules for a single cell.

Expanding 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)
• 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.  Put 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