7MicrosoftOfficeExcel 2010La formattazione condizionale per i gradi v obiettivi

Così, nell'altra mia vita sono in realtà un insegnante di scienze, e poiché di solito si dice che sono "bravo con i computer", di solito mi viene chiesto di dare un'occhiata ad alcune cose. Due volte negli ultimi mesi mi è stato chiesto se avessi impostato la formattazione condizionale in Excel per codificare a colori i risultati dei test degli studenti rispetto ai loro voti target. Sebbene non esattamente correlato al contenuto principale di questo sito, è ancora DIY aiuto tecnico così ho deciso di pubblicare una serie completa di istruzioni qui. Godere…

Microsoft Excel Conditional Formatting: Lookup TablesPer prima cosa hai bisogno di un foglio di ricerca

  • Crea un nuovo foglio nella cartella di lavoro.
  • Compilare 2 colonne – la sinistra con i voti (1c, 1b, 1un, 2c ecc) e la destra con un valore di "punteggio". (può solo essere 1, 2, 3, 4, 5 eccetera)
  • Compilare 2 colonne aggiuntive con una "ricerca inversa" che è la stessa di prima ma con l' 2 colonne invertite
  • Evidenzia la tabella a sinistra completa che hai creato, fate clic destro su di essa, e seleziona “definisci nome”. Dagli un nome, per esempio. "ricerca"
  • Evidenzia la seconda tabella che hai creato, fare clic con il tasto destro e definire un altro nome. Dagli un nome, per esempio. "ricerca_inversa"

Microsoft Excel Conditional Formatting: Define NameCreazione dei fogli di lavoro

  • Ora crea un foglio di lavoro per ogni classe, gruppo o anno (come abiti).  Includi una colonna per i voti target e varie colonne per i risultati dei test o delle valutazioni (per esempio. come sotto)
  • Si noti in questo esempio che i dati di destinazione sono in C3 – C5. I risultati sono in D3 – F5

Microsoft Excel Conditional Formatting: Example Markbook

Costruire la formattazione condizionale di base

  • Selezionare la cella D3, in alto a sinistra delle celle dei dati dei risultati. Assicurati di essere nella scheda "home" e fai clic su "formattazione condizionale" e seleziona "nuova regola"
  • Seleziona "usa una formula per determinare quali celle formattare"
  • Quello che vogliamo fare è confrontare la cella corrente con quella di destinazione, ma non è così semplice, a causa del modo in cui Excel confronta i valori, quindi dobbiamo utilizzare le tabelle di ricerca.
  • Abbiamo anche bisogno 5 regole - 2 sottolivelli o più al di sotto, 1 sotto, sul bersaglio, 1 sopra e 2 o più sopra. Puoi creare più regole se vuoi più dettagli o meno se vuoi solo sotto, sopra e sopra il bersaglio.
  • L'eventuale formula per 2 sottolivelli o più sotto saranno =VLOOKUP($C3,Lookup,2,FALSE)-1>VLOOKUP(D3,Lookup,2,FALSE)

    Microsoft Excel Conditional Formatting: New Rule Formula

  • Il C3 nella prima parte si riferisce alla cella di destinazione superiore, e D3 si riferisce alla cella del risultato effettivo superiore. La "ricerca" si riferisce all'intervallo della tabella di ricerca che abbiamo denominato in precedenza.
  • Per suddividere questo aspetto, stiamo cercando i valori numerici sia per l'obiettivo che per il punteggio effettivo nella cella corrente. Perché vogliamo 2 sottolivelli o più sotto prendiamo 1 fuori dal bersaglio e dire che deve essere ancora maggiore del punteggio. IL $ prima della C di C3 garantisce che quando usiamo la stessa formula per altre colonne verranno comunque confrontate con la colonna degli obiettivi.
  • Ora fai clic su formato e applica la formattazione desiderata. Uso un colore di sfondo rosso chiaro
  • Ora abbiamo creato 1 of 5 regole per una singola cella.

Microsoft Excel Conditional Formatting: Format CellsEspansione della formattazione condizionale per più regole

  • Quindi ripetere il processo per creare 4 più regole.
  • Quando tutto sarà finito, il mio 5 gli esempi apparirebbero come di seguito. Tra loro cambiano solo il confronto e l'eventuale addizione o sottrazione. Si noti anche l'ordine (cui veniamo dopo)
=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)
  • Io uso il rosso medio, rosso chiaro, giallo, verde medio e verde chiaro (in questo ordine) per i colori di sfondo

Ordinamento dell'ordine delle regole

  • Finalmente, dobbiamo assicurarci che le regole siano nel giusto ordine, e applicali a TUTTE le celle dei risultati, non solo la singola cella di risultato D3.
  • Fai di nuovo clic sul pulsante di formattazione condizionale e scegli “gestisci regole…”
  • Assicurati che le regole siano nell'ordine giusto, come sopra (rosso medio, rosso chiaro, giallo, verde medio, verde chiaro)Microsoft Excel Conditional Formatting: Rules Manager
  • Fai clic nella casella "Si applica a" e inserisci l'intero intervallo di celle che conterrà i risultati. Nel mio esempio questo è =$D$3:$F$5.  Metti questo in tutto 5 caselle "si applica a".
  • Questo foglio dovrebbe ora funzionare completamente con la formattazione condizionale

Espandere facilmente a fogli aggiuntivi

  • Per applicare ad altri fogli è sufficiente copiare e incollare una singola cella a cui è già applicata la formattazione condizionale, nel nuovo foglio.
  • Quindi fare clic sul pulsante di formattazione condizionale, selezionare gestisci regole, e cambia il 5 caselle "si applica a" per puntare alle celle del nuovo foglio che necessitano di formattazione condizionale

lascia un commento

7 Commenti

GZGary Zawadzki

Ciao,
Ottimo tutorial - grazie. Ho avuto 3 diversi colori funzionano perfettamente ma il 2 sopra e 2 di seguito non sono formattati con i colori corretti. Stanno cambiando in rosso e verde ma non nelle sfumature a cui devono cambiare. Eventuali suggerimenti per favore?

Thanks,

Gary

rispondere
JSJon Scaife

Ci scusiamo per il lungo ritardo nella risposta Gary, sono stato impegnato a migrare il sito al nostro nuovo VPS. Sei riuscito a risolvere questo problema?? Se non l'hai fatto, allora fammi saltare il tuo file excel per dare un'occhiata. Ricontrollerò questa guida il giorno successivo o 2 per assicurarsi che non ci siano errori di battitura o parti fuorvianti, è risaputo che succede 😉

rispondere
EEmma

Ho seguito tutte le tue istruzioni sopra ma nessuna delle mie cellule è diventata colorata?? Qualche suggerimento su dove sto sbagliando??

rispondere
JSJon Scaife

Presumo che tu li abbia popolati di dati, e anche la colonna di destinazione è popolata di dati?
In ogni caso, fammi lo zapping del file e darò un'occhiata veloce: ti ho inviato un'e-mail

rispondere