DIY Главная Медиа Logo

Конечная сайт для проектирования и создания вашей установке домашнего кинотеатра и Привет-Fi.

7Microsoft Office Excel 2010Условное форматирование для сортов V целей

Так, в моей другой жизни я на самом деле учитель науки, и, как правило, становится круглым, что я «хорошо с компьютерами» я обычно задают, чтобы посмотреть на несколько вещей. Дважды за последние несколько месяцев я спросил, если бы я настроить условное форматирование в Excel, чтобы окрасить результаты тестирования коды студента по сравнению с их целевыми оценками. Хотя точно не связано с содержанием основного этого сайта, он по-прежнему DIY техническую помощь, поэтому я решил опубликовать здесь полный набор инструкций. Наслаждаться…

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с, 1б, 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, например. “look­up”
  • High­light the second table you cre­ated, right click and define anoth­er name. Give it a name, например. “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 (например. как показано ниже)
  • 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 выше, и 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 из 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, светло-красный, желтая низкий, medi­um green and light green (в этом порядке) для фоновых цветов

Sorting the order of the rules

  • Окончательно, мы должны убедиться, что правила в правильном порядке, и применять их всем итальянским разрешением-ULTS клеток, не только один разрешением-ии клеток D3.
  • Click on the con­di­tion­al format­ting but­ton again and choose “man­age rules…”
  • Убедитесь в том, что правила в правильном порядке - как и выше (medi­um red, светло-красный, желтая низкий, 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

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, в новый лист.
  • Then click on the con­di­tion­al format­ting but­ton, выбрать правила мужчина возраста, и изменить 5 “applies to” boxes to point to the cells of the new sheet which need con­di­tion­al formatting

оставьте ответ

7 Комментарии

NНикки

Я хотел бы вновь размещать эту статью на свой сайт, бы, что все будет в порядке?

Ответить
GZГэри Завадский

Здравствуй,
Great tutori­al — thanks. У меня есть 3 различных цветов прекрасно работает, но 2 выше, и 2 ниже, не форматирование их правильными цветами. Они меняются в красный и зеленый, но не оттенки, что они должны изменить, чтобы. Любые наводит на ГЭС-ные пожалуйста?

Спасибо,

Гари

Ответить
JSДжон Скэйф

Sorry for the long delay in reply­ing Gary — I’ve been busy migrat­ing the site to our new VPS. Знаете ли вы человек-возраст, чтобы получить этот Сер-Тед? 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, it’s been known to happen 😉

Ответить
ЕЭмма

Я следовал всем вашим указаниям выше, но ни один из моих клеток не пошли цветные?? Любые наводит на ГЭС-ции, куда я иду неправильно??

Ответить
JSДжон Скэйф

Я предполагаю, что вы поп-и связан-ные их с данными, и целевой столбец также заполняется данными?
By all means zap me the file over and I’ll take a quick look — I’ve e‑mailed you

Ответить