7La 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…

Per 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, e.g. "ricerca"
• Evidenzia la seconda tabella che hai creato, fare clic con il tasto destro e definire un altro nome. Dagli un nome, e.g. "ricerca_inversa"

Creazione 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 (e.g. come sotto)
• Si noti in questo esempio che i dati di destinazione sono in C3 – C5. I risultati sono in D3 – F5

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)
  

  

• 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 di 5 regole per una singola cella.

Espansione 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)
• 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