Autore:
Clyde Lopez
Data Della Creazione:
22 Lang L: none (month-012) 2021
Data Di Aggiornamento:
7 Maggio 2024
Contenuto
Altre sezioniQuesto articolo mostra come utilizzare la formattazione condizionale per confrontare tutte le celle di due elenchi separati in un foglio di calcolo Excel e contrassegnare le celle che compaiono su entrambi gli elenchi. Questa funzione è disponibile solo sulla versione desktop di Excel e l'app per dispositivi mobili non la supporta.
Passi
Metodo 1 di 2: utilizzo della formula CountIf
Apri il foglio di calcolo Excel che desideri modificare. Trova il file del foglio di calcolo con gli elenchi che desideri confrontare e fai doppio clic sul file per aprirlo in Microsoft Excel.
Seleziona il tuo primo elenco. Fai clic sulla prima cella del tuo primo elenco e trascina il mouse fino in fondo all'ultima cella dell'elenco per selezionare l'intero intervallo di dati.
Clicca il Formule scheda sulla barra multifunzione della barra degli strumenti. Puoi trovare questa scheda sopra la barra degli strumenti nella parte superiore del foglio di lavoro. Aprirà gli strumenti delle formule sulla barra degli strumenti.
Clic Definisci nome sulla barra degli strumenti. Puoi trovare questa opzione al centro della barra multifunzione "Formule". Si aprirà una nuova finestra pop-up e ti permetterà di dare un nome alla tua lista.
genere List1 nel campo Nome. Fare clic sul campo di testo nella parte superiore della finestra a comparsa e immettere qui un nome per l'elenco.
- Successivamente è possibile utilizzare questo nome per inserire l'elenco nella formula di confronto.
- In alternativa, puoi dare alla tua lista un nome diverso qui. Ad esempio, se si tratta di un elenco di località, puoi denominarlo "località1" o "elenco località".
Clic ok nella finestra pop-up. Ciò confermerà la tua azione e nominerà il tuo elenco.
Assegna un nome alla seconda lista come List2. Segui gli stessi passaggi del primo elenco e assegna un nome al secondo elenco. Ciò ti consentirà di utilizzare rapidamente questo secondo elenco nella formula di confronto in un secondo momento.
- Puoi dare all'elenco il nome che desideri. Assicurati di ricordare o annotare il nome che dai a ciascuna delle tue liste qui.
Seleziona il tuo primo elenco. Fare clic sulla prima cella del primo elenco e trascinare verso il basso per selezionare l'intero intervallo di dati.
- Assicurati che il tuo primo elenco sia selezionato prima di iniziare a configurare la formattazione condizionale.
Clicca il Casa scheda sulla barra degli strumenti. Questa è la prima scheda nell'angolo superiore sinistro della barra multifunzione della barra degli strumenti. Aprirà gli strumenti di base del foglio di calcolo sulla barra degli strumenti.
Clic Formattazione condizionale sulla barra degli strumenti. Questa opzione sembra una piccola icona di un foglio di calcolo con alcune celle evidenziate in rosso e blu. Si aprirà un menu a discesa con tutte le opzioni di formattazione.
Clic Nuova regola nel menu a discesa. Si aprirà una nuova finestra pop-up e ti consentirà di impostare manualmente una nuova regola di formattazione per l'intervallo selezionato.
Seleziona l'opzione "Usa una formula per determinare quali celle formattare". Questa opzione ti consentirà di digitare manualmente una formula di formattazione per confrontare i tuoi due elenchi.
- Sopra finestre, lo troverai in fondo all'elenco delle regole nella casella "Seleziona un tipo di regola".
- Sopra Mac, Selezionare Classico nel menu a discesa "Stile" nella parte superiore del popup. Quindi, trova questa opzione nel secondo menu a discesa sotto il menu Stile.
Fare clic sul campo della formula nella finestra a comparsa. È possibile inserire qui qualsiasi formula Excel valida per impostare una regola di formattazione condizionale.
genere = countif (List2, A1) = 1 nella barra della formula. Questa formula scansionerà i tuoi due elenchi e segnerà tutte le celle del tuo primo elenco che appaiono anche nel secondo elenco.
- Sostituire A1 nella formula con il numero della prima cella del tuo primo elenco.
- Ad esempio, se la prima cella del tuo primo elenco è la cella D5, la formula sarà simile = countif (List2, D5) = 1.
- Se hai dato un nome diverso al tuo secondo elenco, assicurati di sostituirlo List2 nella formula con il nome effettivo del tuo elenco.
- In alternativa, modifica la formula in = countif (List2, A1) = 0 se vuoi contrassegnare le celle che non appaiono nel secondo elenco.
genere = countif (List1, B1) = 1 nella barra della formula (opzionale). Se vuoi trovare e contrassegnare le celle nel tuo file secondo elenco che compare anche nel primo elenco, utilizza questa formula al posto del primo.
- Sostituire List1 con il nome del tuo primo elenco e B1 con la prima cella del secondo elenco.
Seleziona un formato personalizzato per contrassegnare le celle (facoltativo). È possibile selezionare un colore di riempimento dello sfondo personalizzato e diversi stili di carattere per contrassegnare le celle trovate dalla formula.
- Sopra finestre, clicca il Formato pulsante in basso a destra della finestra pop-up. È possibile selezionare un colore di sfondo nella scheda "Riempimento" e gli stili dei caratteri nella scheda "Carattere".
- Sopra Mac, seleziona un formato preimpostato nel menu a discesa "Formato con" in basso. Puoi anche selezionare formato personalizzato qui per selezionare manualmente un riempimento di sfondo e gli stili dei caratteri.
Clic ok nella finestra pop-up. Ciò confermerà e applicherà la formula di confronto. Tutte le celle del primo elenco che appaiono anche nel secondo elenco saranno contrassegnate con il colore e il carattere selezionati.
- Ad esempio, se selezioni un riempimento rosso chiaro con testo rosso scuro, tutte le celle ricorrenti assumeranno questo colore nel tuo primo elenco.
- Se utilizzi la seconda formula sopra, la formattazione condizionale segnerà le celle ricorrenti nel secondo elenco invece del primo.
Metodo 2 di 2: utilizzo della formula VLookup
Apri il tuo foglio di calcolo Excel. Trova il file Excel con gli elenchi che desideri confrontare e fai doppio clic sul nome del file o sull'icona per aprire il foglio di calcolo in Microsoft Excel.
Fare clic sulla cella vuota accanto al primo elemento del secondo elenco. Trova il tuo secondo elenco nel foglio di calcolo e fai clic sulla cella vuota accanto al primo elemento dell'elenco in alto.
- Puoi inserire la tua formula VLookup qui.
- In alternativa, puoi selezionare qualsiasi cella vuota nel foglio di lavoro. Questa cella renderà più conveniente vedere il confronto accanto al secondo elenco.
genere = vlookup ( nella cella vuota. La formula VLookup ti consentirà di confrontare tutti gli elementi su due elenchi separati e vedere se un valore è un valore nuovo o ripetuto.
- Non chiudere la parentesi della formula finché la formula non è completa.
Seleziona il primo elemento nel secondo elenco. Senza chiudere le parentesi della formula, fai clic sul primo elemento nel secondo elenco. Questo inserirà la prima cella del tuo secondo elenco nella formula.
Digitare un , virgola nella formula. Dopo aver selezionato la prima cella del secondo elenco, digita una virgola nella formula. Successivamente potrai selezionare il tuo intervallo di confronto.
Tieni premuto e seleziona l'intero primo elenco. Questo inserirà l'intervallo di celle del tuo primo elenco nella seconda parte della formula VLookup.
- Ciò ti consentirà di cercare nel primo elenco l'elemento selezionato dal secondo elenco (il primo elemento in cima al secondo elenco) e di tornare indietro se si tratta di un valore di ripetizione o nuovo.
Digitare un , virgola nella formula. Ciò bloccherà l'intervallo di confronto nella formula.
genere 1 nella formula dopo la virgola. Questo numero rappresenta il numero di indice della colonna. Richiederà alla formula VLookup di cercare la colonna dell'elenco effettivo invece di una colonna diversa accanto ad essa.
- Se desideri che la formula restituisca il valore dalla colonna accanto al tuo primo elenco, digita 2 Qui.
Digitare un , virgola nella formula. Ciò bloccherà il numero di indice della colonna (1) nella formula VLookup.
genere FALSO nella formula. Questo cercherà nell'elenco una corrispondenza esatta dell'elemento di ricerca selezionato (il primo elemento all'inizio del secondo elenco) invece di corrispondenze approssimative.
- Invece di FALSO Puoi usare 0, è esattamente lo stesso.
- In alternativa, puoi digitare VERO o 1 se vuoi cercare una corrispondenza approssimativa.
genere ) alla fine per chiudere la formula. È ora possibile eseguire la formula e vedere se l'elemento di ricerca selezionato nel secondo elenco è un valore di ripetizione o nuovo.
- Ad esempio, se il tuo secondo elenco inizia da B1 e il tuo primo elenco va dalle celle A1 ad A5, la formula sarà simile = vlookup (B1, $ A $ 1: $ A $ 5,1, falso).
stampa ↵ Invio o ⏎ Ritorno sulla tastiera. Questo eseguirà la formula e cercherà il primo elenco per il primo elemento dal secondo elenco.
- Se questo è un valore di ripetizione, vedrai lo stesso valore stampato di nuovo nella cella della formula.
- Se questo è un nuovo valore, vedrai "#N / A"stampato qui.
- Ad esempio, se stai cercando "Giovanni" nel primo elenco e ora vedi "Giovanni" nella cella della formula, si tratta di un valore di ripetizione che compare in entrambi gli elenchi. Se vedi "# N / A", è un nuovo valore nel secondo elenco.
Seleziona la cella della formula. Dopo aver eseguito la formula e visualizzato i risultati per il primo elemento dell'elenco, fare clic sulla cella della formula per selezionarla.
Fare clic e trascinare verso il basso il punto verde in basso a destra della cella. Questo espanderà la cella della formula lungo l'elenco e applicherà la formula a ogni elemento dell'elenco nel secondo elenco.
- In questo modo puoi confrontare ogni articolo del tuo secondo elenco con l'intero primo elenco.
- Questo cercherà individualmente il tuo primo elenco per ogni elemento del secondo elenco e mostrerà il risultato accanto a ciascuna cella separatamente.
- Se desideri visualizzare un indicatore diverso per i nuovi valori invece di "# N / A", utilizza questa formula: = iferror (vlookup (B1, $ A $ 1: $ A $ 5,1, false), "Nuovo valore"). Verrà stampato "Nuovo valore" per i nuovi valori invece di "# N / A".
