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, 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"
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 (per esempio. 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 of 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
Ciao John,
Hai un esempio di un foglio che hai realizzato? Struggling following the steps here!
Mi piacerebbe ripubblicare questo articolo sul mio sito web, andrebbe bene?
Sicuro, fintanto che inserisci un link all'articolo originale qui 🙂
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
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 😉
Ho seguito tutte le tue istruzioni sopra ma nessuna delle mie cellule è diventata colorata?? Qualche suggerimento su dove sto sbagliando??
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