Hai bisogno di evidenziare valori duplicati in una colonna? È necessario selezionare le prime 5 celle massime? Hai bisogno di realizzare una scala termica per chiarezza (il colore cambia a seconda dell'aumento/diminuzione del valore della cella)? In Excel, l'evidenziazione delle celle in base al colore può essere eseguita molto rapidamente. In Excel, una funzione speciale chiamata "Formattazione condizionale" è responsabile dell'evidenziazione delle celle con il colore. Altamente raccomandato! Leggi di più qui sotto:
Per iniziare, sulla barra multifunzione delle attività nel menu principale, trova la sezione Stili e fai clic sul pulsante Formattazione condizionale.
Quando si fa clic, si aprirà un menu con diverse opzioni per questa modifica. Come puoi vedere, le opportunità qui sono davvero tantissime.
Ora di più su quelli più utili:
Excel evidenzia le celle per colore in base alle condizioni. Condizioni semplici
Per fare ciò, vai a Regole di selezione della cella. Se, ad esempio, devi selezionare tutte le celle maggiori di 100, fai clic sul pulsante Altro. Nella finestra:
Per impostazione predefinita, si suggerisce di evidenziare le condizioni in rosso, ma è possibile impostare la formattazione della cella desiderata facendo clic nella finestra di destra e selezionando l'opzione richiesta.
Evidenziazione dei valori duplicati, incl. su più colonne
Per evidenziare tutti i valori ripetuti, selezionare il menu appropriato (vedere l'immagine all'inizio dell'articolo). Successivamente, verrà visualizzata nuovamente la finestra di formattazione. Configuralo come preferisci.
Cosa dovresti fare se hai bisogno di trovare ripetizioni in due o più colonne, ad esempio quando il tuo nome completo è in colonne diverse? Crea un'altra colonna e combina i valori con la formula =, ad es. In una cella separata avrai scritto IvanovIvanIvanovich, quindi utilizzando questa colonna potrai selezionare valori duplicati. È importante capire che se l'ordine delle parole è diverso, Excel considererà tali righe non ripetitive.
Evidenziazione a colori del primo/ultimo valore. Ancora una volta la formattazione condizionale
Per fare ciò, vai alla sezione Regole per la selezione della prima e dell'ultima cella e seleziona l'elemento desiderato. Oltre al fatto che puoi evidenziare il primo/ultimo valore (anche in percentuale), puoi utilizzare la possibilità di evidenziare i dati sopra e sotto la media (lo uso ancora più spesso). Molto comodo per visualizzare risultati che differiscono dalla norma o dalla media!
Costruzione di un diagramma termico e di un istogramma
Una caratteristica interessante per la visualizzazione dei dati è il grafico termico/temperatura. Il punto è che, a seconda del valore in una colonna o riga, la cella viene evidenziata con una certa tonalità di colore. Le tabelle vengono percepite molto meglio a occhio nudo e il processo decisionale diventa più semplice. Dopotutto, uno dei migliori analizzatori è il nostro occhio e, di conseguenza, il nostro cervello, non una macchina!
Anche l'istogramma in una cella (immagine sotto) è una funzione estremamente utile per identificare le variazioni dei valori e confrontarli.
Evidenziazione delle celle contenenti testo specifico con il colore
Molto spesso hai bisogno di trovare celle che contengono un certo set di caratteri, ovviamente puoi usare la funzione =, ma è più semplice e veloce applicarla nella formattazione condizionale, passa attraverso - Regole per la selezione delle celle - Il testo contiene (vedi immagine 2).
Molto utile quando si lavora con il testo. Un esempio in cui hai i nomi completi dei dipendenti scritti in una colonna, ma devi selezionare tutti i colleghi di Ivanov. Seleziona le celle, vai alla voce di menu e seleziona il testo contenente Ivanov, quindi filtra la tabella per colore:
Evidenziazione del colore di Excel. Filtra per colore
Oltre alle opzioni di cui sopra, puoi filtrare le celle selezionate per colore utilizzando un filtro normale. Con mia sorpresa, pochissime persone lo sanno - apparentemente echi della versione del 2003 - questa funzione non era presente.
Verifica delle condizioni di formattazione
Per verificare quale formattazione condizionale hai già impostato, vai su Home - Formattazione condizionale - Gestisci regole, qui puoi modificare le condizioni già impostate per evidenziare le celle con il colore, nonché selezionare la priorità della formattazione specificata (a seconda di quale è più alta è più importante, puoi cambiarlo con i pulsanti - frecce)
Intervallo di formattazione condizionale non valido
Importante! La formattazione condizionale, se utilizzata in modo errato, è spesso causa di rallentamenti di Excel. Ciò accade se copi più volte le celle con l'evidenziazione del colore. Quindi puoi avere molte condizioni con il colore. Io stesso ho visto più di 3mila condizioni: il file ha rallentato in modo empio. Può anche rallentare quando viene specificato un intervallo come nell'immagine sopra; è meglio specificare A:A per l'intero intervallo.
Ulteriori informazioni sui freni Excel e sulle loro cause. Questo articolo ha aiutato più di cento persone;)
Sono felice che tu abbia imparato come evidenziare le celle in base alle condizioni in Excel.
Condividi il nostro articolo sui tuoi social network:In una videolezione dettagliata. Oggi vorremmo ampliare un po’ questo articolo per risolvere un problema più specifico. Diciamo che devi contare il numero di celle in base al colore delle celle o al colore del testo.
A partire da Excel 2007, il programma ha la possibilità incorporata di ordinare le celle per colore. Pertanto, possiamo filtrare il colore di cui abbiamo bisogno, selezionare le celle rimaste in vista e vedere visivamente il numero totale di celle. Ma cosa succede se dobbiamo farlo spesso e allo stesso tempo abbiamo bisogno che tutto venga contato e ricalcolato utilizzando le formule.
Per questi scopi è necessario utilizzare una macro molto semplice, o meglio una funzione personalizzata, chiamiamola così ColoreNom, ci permetterà di estrarre il codice numerico relativo al colore di riempimento e poi utilizzando questo codice conteremo il numero totale di celle utilizzando le tecniche descritte nell'articolo
Quindi iniziamo. Vai all'editor di Visual Basic, per fare questo:
V Excel2003 Premere Servizio, Ulteriore Macro poi Editor di Visual Basic.
V Excel 2007, 2010 e 2013è fatto diversamente. Vai alla sezione Sviluppatore, quindi seleziona Visual Basic
Attenzione! Sezione della barra degli strumenti Sviluppatore in Excel 2007 è disponibile per impostazione predefinita, ma in Excel 2010 e 2013 deve essere abilitato. Ciò è particolarmente utile per quegli utenti che lavoreranno spesso con le macro. Per abilitare la barra degli strumenti per sviluppatori in Excel 2010 o 2013, è necessario eseguire File | Opzioni | Personalizza la barra multifunzione dopodiché, sul lato destro è necessario selezionare la casella accanto alla scritta Sviluppatore
Dopo l'apertura dell'editor Visual Basic, inserire un modulo vuoto, per fare questo selezionare il menu Inserire e poi Modulo
Funzione pubblica ColorNom (cella come intervallo)
ColorNom = Cell.Font.ColorIndex
Fine funzione
Importante! Non sarai in grado di trovare il numero del colore della cella utilizzando questa funzione quando utilizzi la formattazione condizionale. Inoltre, quando si cambia il colore di una cella, Excel non ricalcola i valori; è necessario farlo manualmente premendo Ctrl+Alt+F9, altrimenti le modifiche verranno apportate quando si apre nuovamente il file. Ciò si verifica perché Excel non considera la modifica del colore di una cella come una modifica alla formula. A questo proposito, se questo è fondamentale, allora puoi apportare una modifica alla formula stessa semplicemente aggiungendo una funzione che viene costantemente ricalcolata e non influirà sulla determinazione del colore della cella. Ad esempio, specificare la funzione per determinare la data corrente, moltiplicata per zero.
Nel nostro caso, la funzione sarà simile a questa.
=ColoreNom (A1)+Oggi()*0
Un esempio di conteggio del numero di valori in base al colore di riempimento della cella in Excel
Considera l'esempio sopra con un elenco di frutti. Abbiamo definito il codice della cella e lo abbiamo visualizzato accanto a ciascuna cella.
Ecco come appaiono gli argomenti di questa funzione
CONTA.SE(intervallo, criterio)
Scriviamo la formula:
CONTA.SE($B$1:$B$8 ;E2 )
Abbiamo indicato l'intervallo con il simbolo del dollaro in modo che fosse fisso e la formula potesse essere estesa. Abbiamo solo tre criteri e sono indicati nella nostra tabella ausiliaria. Allunghiamo la formula e otteniamo il numero di celle per colore.
Scarica file di esempio: (file con supporto macro)
Gli esseri umani sono progettati in modo tale da percepire i colori meglio dei semplici numeri: non per niente le aree più importanti dei diagrammi o delle linee delle tabelle a cui vale la pena prestare attenzione sono solitamente evidenziate in colori contrastanti. Tuttavia, contrassegnare manualmente le celle della tabella “buone” e “cattive” in Excel non è la cosa più divertente. Se solo MS Excel potesse farlo da solo e persino cambiare automaticamente gli stessi colori se la situazione cambia...
Ma lui sa già come farlo perfettamente: non dobbiamo fare altro che aiutarlo un po'!
Risolviamo questo problema applicato: nella nostra tabella “frutta” il peso di un particolare articolo è indicato in chilogrammi. Per facilitare la navigazione tra ciò che ci manca e ciò che, al contrario, abbiamo in eccesso, coloreremo in rosso tutti i valori inferiori a 20 e in verde tutto ciò che è superiore a 50. In questo caso, tutto ciò che rimane in questo intervallo non verrà affatto contrassegnato con il colore. E per complicare il compito, andiamo ancora oltre e rendiamo dinamica l'assegnazione del colore: quando cambia il valore nella cella corrispondente, cambierà anche il suo colore.
Innanzitutto, seleziona l'intervallo di dati, ovvero il contenuto della seconda colonna della tabella MS Excel, quindi vai alla sezione " casa", dove nel gruppo "Stili" attiviamo il " Formattazione condizionale"e nell'elenco a discesa selezionare" Crea una regola«.
Nella finestra "Crea regola di formattazione" che appare, seleziona Tipo di regola: "Formatta solo le celle che contengono" e nel costruttore sottostante, imposta i parametri: "Valore cella", "Meno di" e inserisci manualmente il nostro "bordo": il numero 20.
Fai clic sul pulsante "Formato" in basso, vai alla scheda "Riempimento" e seleziona il colore rosso. Fare clic su "Ok".
Nota: ovviamente puoi contrassegnare le celle non in base al colore di sfondo, ma, ad esempio, in base alla dimensione o al colore del carattere: per fare ciò, devi utilizzare le schede corrispondenti nella stessa finestra.
Guarda la tabella: chiaramente ci sono rimaste pochissime mele e mandarini, è ora di fare un nuovo acquisto!
Ottimo, i dati sono già evidenziati a colori!
Ora, per analogia, creeremo un'altra regola, solo che questa volta con i parametri "Valore cella", "Maggiore", 20. Specificheremo il colore verde come riempimento. Pronto.
Questo non mi è sembrato sufficiente: il testo nero su sfondo rosso e verde è difficile da leggere, quindi ho deciso di decorare un po' le nostre regole e sostituire il colore del testo con il bianco. Per fare ciò, apri lo strumento Formattazione condizionale, ma invece di scegliere Nuova regola, seleziona Gestione delle regole", sotto.
Nella finestra che appare, seleziona entrambe le regole a turno e premi il pulsante “ Modifica", dopodiché, nella finestra già familiare " Formato"vai alla scheda" Font" e cambia il colore del testo in bianco. Questo è quello che ho ottenuto:
Ora ho cambiato non solo lo sfondo delle celle della tabella, ma anche il colore del carattere
Proviamo a cambiare i valori "cattivi" in quelli "buoni"? Una volta fatto, il colore cambia automaticamente non appena le celle corrispondenti contengono valori che rientrano in una delle regole.
Modifichiamo i valori nella nostra tabella Excel... funziona tutto!
IN Eccellere Ci sono funzionalità utili che molte persone non conoscono. Una di queste è la funzione di formattazione condizionale, che applica automaticamente un determinato formato, ad esempio colorando in rosso le celle che soddisfano una determinata condizione impostata dall'utente.
Qui ti diremo come impostare le condizioni in Excel per poi cambiare il colore delle celle in modo che corrisponda automaticamente.
Condizioni per l'ombreggiatura delle celle di Excel in Windows
Colorare le celle alle condizioni è molto semplice. Seleziona le celle, le colonne o le righe di cui desideri modificare il colore. Quindi sulla scheda casa vai al menù Stili e seleziona Formattazione condizionale > Condizioni di selezione della cella. Seleziona la condizione più adatta a te. Immettere un valore di riferimento, aprire il menu a discesa e selezionare Formato personalizzato. Fare clic sulla scheda Riempire e seleziona il colore delle celle. Infine, fai clic OK.Condizioni per ombreggiare le celle di Excel su Mac
Su Mac, l'operazione di selezione delle celle utilizzando le condizioni è esattamente la stessa. L'unica differenza è la gamma più ampia di opzioni di condizione da personalizzare.Immagine: © Mika Baumeister – Unsplash.com
Diciamo che uno dei nostri compiti è inserire informazioni sul fatto che il cliente abbia effettuato un ordine nel mese corrente. Quindi, in base alle informazioni ricevute, è necessario evidenziare le celle per colore in base alla condizione: quale dei clienti non ha effettuato un solo ordine negli ultimi 3 mesi. Per tali clienti sarà necessario inviare nuovamente l'offerta.
Naturalmente, questo è un compito per Excel. Il programma dovrebbe trovare automaticamente tali controparti e evidenziarle a colori di conseguenza. Per queste condizioni utilizzeremo la formattazione condizionale.
Riempi automaticamente le celle con le date
Per prima cosa prepariamo la struttura per la compilazione del registro. Prima di tutto, consideriamo condizionatamente un esempio già pronto di un registro automatizzato, mostrato nella figura seguente:
L'utente deve solo indicare se il cliente ha effettuato un ordine nel mese corrente, quindi nella cella corrispondente deve essere inserito il valore testuale "ordine". La condizione principale per l'evidenziazione: se la controparte non ha effettuato un solo ordine per 3 mesi, il suo numero viene automaticamente evidenziato in rosso.
La soluzione presentata dovrebbe automatizzare alcuni flussi di lavoro e semplificare l'analisi visiva dei dati.
Riempi automaticamente le celle con le date correnti
Innanzitutto, per la cassa con i numeri cliente, creeremo delle intestazioni di colonna di colore verde e dei mesi correnti, che visualizzeranno automaticamente i periodi orari. Per fare ciò, inserisci la seguente formula nella cella B1:
Come funziona la formula per la generazione automatica dei mesi in uscita?
Nella figura la formula restituisce il periodo di tempo che trascorre a partire dalla data di stesura dell'articolo: 17/09/2017. Il primo argomento della funzione DATI contiene una formula che restituisce sempre l'anno corrente nella data odierna grazie alle funzioni: ANNO e OGGI. Il secondo argomento specifica il numero del mese (-1). Un numero negativo significa che siamo interessati a quale mese era nel passato. Condizioni di esempio per il secondo argomento con un valore:
- 1 – indica il primo mese (gennaio) dell'anno specificato nel primo argomento;
- 0 è 1 mese fa;
- -1 è 2 mesi. a partire dall'inizio dell'anno in corso (ovvero: 10/01/2016).
L'ultimo argomento è il numero del giorno del mese specificato nel secondo argomento. Di conseguenza, la funzione DATA raccoglie tutti i parametri in un unico valore e la formula restituisce la data corrispondente.
Come puoi vedere, la funzione DATA ora utilizza il valore della cella B1 e aumenta il numero del mese di 1 rispetto alla cella precedente. Di conseguenza, otteniamo 1: la data del mese successivo.
Ora copia questa formula dalla cella C1 al resto delle intestazioni di colonna nell'intervallo D1:L1.
Seleziona l'intervallo di celle B1:L1 e seleziona lo strumento: "HOME" - "Celle" - "Formatta celle" o semplicemente premi CTRL+1. Nella finestra di dialogo che appare, nella scheda “Numero”, nella sezione “Formati numerici:”, seleziona l'opzione “(tutti i formati)”. Nel campo “Tipo:”, inserisci il valore: MMM.AA (lettere maiuscole obbligatorie). Grazie a ciò, otterremo una visualizzazione abbreviata dei valori delle date nelle intestazioni dei registri, che semplificherà l'analisi visiva e la renderà più comoda grazie ad una migliore leggibilità.
Nota! Quando arriva il mese di gennaio (D1), la formula cambia automaticamente la data all'anno successivo.
Come colorare una colonna in Excel in base alle condizioni
Adesso bisogna evidenziare le celle relative al mese corrente. Grazie a questo, possiamo facilmente trovare la colonna in cui dobbiamo inserire i dati attuali per questo mese. Per questo:
La colonna sotto l'intestazione del registro corrispondente viene automaticamente evidenziata in verde secondo le nostre condizioni:
Come funziona la formula per evidenziare il colore di una colonna in base alla condizione?
Poiché abbiamo coperto l'intera area della tabella per inserire i dati del caso prima di creare la regola di formattazione condizionale, la formattazione sarà attiva per ogni cella nell'intervallo B2:L15. Il riferimento misto nella formula B$1 (indirizzo assoluto solo per le righe, indirizzo relativo per le colonne) significa che la formula farà sempre riferimento alla prima riga di ogni colonna.
Evidenziazione automatica di una colonna in base al mese corrente
La condizione principale per riempire le celle con il colore: se l'intervallo B1:L1 contiene la stessa data del primo giorno del mese corrente, le celle dell'intera colonna cambiano immediatamente il loro colore con il colore specificato nella formattazione condizionale.
Nota! Nelle condizioni di questa formula, l'ultimo argomento della funzione DATA è impostato su 1, proprio come le formule che definiscono le date per le intestazioni delle colonne del registro.
Nel nostro caso, questo è il riempimento verde delle celle. Se apriremo la nostra cassa il mese prossimo, la colonna corrispondente verrà evidenziata in verde, indipendentemente dal giorno corrente.
La parte tabellare è formattata, ora riempiamola con il valore testuale “ordine” in un ordine misto di clienti per il mese corrente e quelli precedenti.
Come evidenziare le celle in rosso in base a una condizione
Adesso bisogna evidenziare in rosso le celle con il numero dei clienti che non hanno effettuato un solo ordine da 3 mesi. Per questo:
I numeri cliente vengono evidenziati in rosso se la loro riga non presenta il valore “ordine” nelle ultime tre celle per il mese corrente (compreso).
Analisi della formula per evidenziare le celle per colore:
Innanzitutto, occupiamoci della parte centrale della nostra formula. La funzione OFFSET restituisce un offset di riferimento dell'intervallo dall'intervallo di base in base a un numero specificato di righe e colonne. Il riferimento restituito può essere una singola cella o un intero intervallo di celle. Facoltativamente, è possibile definire il numero di righe e colonne da restituire. Nel nostro esempio, la funzione restituisce un riferimento a un intervallo di celle negli ultimi 3 mesi.
La parte importante per la nostra condizione di evidenziazione del colore è nel primo argomento della funzione OFFSET. Determina da quale mese iniziare la compensazione. In questo esempio, questa è la cella D2, ovvero l'inizio dell'anno è gennaio. Naturalmente, per le restanti celle della colonna, il numero di riga della cella base corrisponderà al numero di riga in cui si trova. I successivi 2 argomenti della funzione OFFSET determinano su quante righe e colonne deve essere eseguito l'offset. Poiché eseguiremo i calcoli per ciascun client nella stessa riga, specifichiamo il valore di offset per le righe come –¬ 0.
Allo stesso tempo, per calcolare il valore del terzo argomento (offset della colonna), utilizziamo la formula annidata MESE(OGGI()), che, in base alle condizioni, restituisce il numero del mese corrente nell'anno corrente. Sottraiamo il numero 4 dal numero del mese calcolato dalla formula, ovvero nel caso di novembre otteniamo un offset di 8 colonne. E, ad esempio, per giugno: solo 2 colonne.
Gli ultimi due argomenti della funzione OFFSET specificano l'altezza (in numero di righe) e la larghezza (in numero di colonne) dell'intervallo restituito. Nel nostro esempio, questa è un'area di celle con un'altezza di 1 riga e una larghezza di 4 colonne. Questo intervallo copre le colonne dei 3 mesi precedenti e quello corrente.
La prima funzione nella formula CONTA.SE verifica il numero di volte in cui il valore di testo "ordine" appare nell'intervallo restituito utilizzando la funzione OFFSET. Se la funzione restituisce il valore 0, significa che non ci sono stati ordini da un cliente con questo numero per 3 mesi. E in conformità con le nostre condizioni, la cella con il numero di questo cliente è evidenziata con un colore di riempimento rosso.
Se vogliamo registrare i dati dei clienti, Excel è l'ideale per questo scopo. Puoi facilmente registrare il numero di prodotti ordinati, nonché le date delle transazioni, nelle apposite categorie. Il problema inizia gradualmente a sorgere man mano che il volume dei dati cresce.
Se ce ne sono così tanti, dedichiamo diversi minuti alla ricerca di una posizione specifica nel registro e all'analisi delle informazioni inserite. In questo caso, vale la pena aggiungere meccanismi alla tabella dei registri per automatizzare alcuni flussi di lavoro dell’utente. Questo è quello che abbiamo fatto.
