Acest wikiHow vă învață cum să utilizați formatarea condiționată pentru a compara toate celulele de pe două liste separate într-o foaie de calcul Excel și să marcați celulele care apar pe ambele liste. Această caracteristică este disponibilă doar pe versiunea de desktop Excel, iar aplicația mobilă nu o acceptă.
Pași
Metoda 1 din 2: Utilizarea formulei CountIf
Pasul 1. Deschideți foaia de calcul Excel pe care doriți să o editați
Găsiți fișierul foii de calcul cu listele pe care doriți să le comparați și faceți dublu clic pe fișier pentru a-l deschide în Microsoft Excel.
Pasul 2. Selectați prima listă
Faceți clic pe prima celulă din prima listă și trageți mouse-ul până la ultima celulă a listei pentru a selecta întregul interval de date.
Pasul 3. Faceți clic pe fila Formule de pe panglica barei de instrumente
Puteți găsi această filă deasupra barei de instrumente din partea de sus a foii de calcul. Acesta vă va deschide instrumentele de formulare pe panglica barei de instrumente.
Pasul 4. Faceți clic pe Definire nume pe bara de instrumente
Puteți găsi această opțiune în mijlocul panglicii „Formule”. Se va deschide o nouă fereastră pop-up și vă va permite să vă denumiți lista.
Pasul 5. Tastați List1 în câmpul Nume
Faceți clic pe câmpul de text din partea de sus a ferestrei pop-up și introduceți aici un nume de listă.
- Puteți utiliza ulterior acest nume pentru a insera lista dvs. în formula de comparație.
- Alternativ, puteți da listei dvs. un nume diferit aici. De exemplu, dacă aceasta este o listă de locații, o puteți denumi „locații1” sau „listă de locații”.
Pasul 6. Faceți clic pe OK în fereastra pop-up
Aceasta vă va confirma acțiunea și vă va denumi lista.
Pasul 7. Denumiți a doua listă ca List2
Urmați aceiași pași ca prima listă și dați-i celei de-a doua liste un nume. Acest lucru vă va permite să utilizați rapid această a doua listă în formula de comparație mai târziu.
Puteți da listei orice nume doriți. Asigurați-vă că vă amintiți sau notați numele pe care îl dați fiecărei liste aici
Pasul 8. Selectați prima listă
Faceți clic pe prima celulă din prima listă și trageți în jos pentru a selecta întregul interval de date.
Asigurați-vă că prima listă este selectată înainte de a începe configurarea formatării condiționate
Pasul 9. Faceți clic pe fila Acasă de pe bara de instrumente
Aceasta este prima filă din colțul din stânga sus al panglicii barei de instrumente. Acesta vă va deschide instrumentele de bază pentru foaia de calcul pe bara de instrumente.
Pasul 10. Faceți clic pe Formatare condițională pe bara de instrumente
Această opțiune arată ca o pictogramă mică a foii de calcul, cu câteva celule evidențiate în roșu și albastru. Se va deschide un meniu derulant cu toate opțiunile de formatare.
Pasul 11. Faceți clic pe Regula nouă din meniul derulant
Se va deschide o nouă fereastră pop-up și vă va permite să configurați manual o nouă regulă de formatare pentru intervalul selectat.
Pasul 12. Selectați opțiunea „Folosiți o formulă pentru a determina ce celule să formatați”
Această opțiune vă va permite să tastați manual o formulă de formatare pentru a compara cele două liste.
- Pe Windows, o veți găsi în partea de jos a listei de reguli în caseta „Selectați un tip de regulă”.
- Pe Mac, Selectați Clasic în meniul derulant „Stil” din partea de sus a ferestrei pop-up. Apoi, găsiți această opțiune în al doilea meniu derulant de sub meniul Stil.
Pasul 13. Faceți clic pe câmpul formulă din fereastra pop-up
Puteți introduce aici orice formulă Excel validă pentru a configura o regulă de formatare condiționată.
Pasul 14. Tastați = countif (List2, A1) = 1 în bara de formule
Această formulă vă va scana cele două liste și va marca toate celulele din prima listă care apar și pe a doua listă.
- Înlocuiți A1 în formulă cu numărul primei celule din prima listă.
- De exemplu, dacă prima celulă din prima listă este celula D5, atunci formula dvs. va arăta ca = countif (List2, D5) = 1.
- Dacă ați dat un nume diferit celei de-a doua liste, asigurați-vă că înlocuiți Lista2 din formulă cu numele real al listei proprii.
- Alternativ, modificați formula la = countif (List2, A1) = 0 dacă doriți să marcați celulele care nu face apare pe a doua listă.
Pasul 15. Tastați = countif (List1, B1) = 1 în bara de formule (opțional)
Dacă doriți să găsiți și să marcați celulele de pe al doilea listă care apare și pe prima listă, utilizați această formulă în locul primei.
Înlocuiți Lista1 cu numele primei liste și B1 cu prima celulă a celei de-a doua liste
Pasul 16. Selectați un format personalizat pentru a marca celulele (opțional)
Puteți selecta o culoare personalizată de umplere a fundalului și diferite stiluri de font pentru a marca celulele pe care le găsește formula dvs.
- Pe Windows, apasă pe Format din dreapta jos a ferestrei pop-up. Puteți selecta o culoare de fundal în fila „Completare” și stiluri de fonturi în fila „Font”.
- Pe Mac, selectați un format presetat în meniul derulant „Format cu” din partea de jos. De asemenea, puteți selecta format personalizat aici pentru a selecta manual o umplere de fundal și stiluri de font.
Pasul 17. Faceți clic pe OK în fereastra pop-up
Acest lucru vă va confirma și aplica formula de comparație. Toate celulele din prima listă care apar și pe a doua listă vor fi marcate cu culoarea și fontul selectat.
- De exemplu, dacă selectați o umplutură roșie deschisă cu text roșu închis, toate celulele recurente se vor transforma în această culoare pe prima listă.
- Dacă utilizați a doua formulă de mai sus, formatarea condiționată va marca celulele recurente de pe a doua listă în loc de prima.
Metoda 2 din 2: Utilizarea formulei VLookup
Pasul 1. Deschideți foaia de calcul Excel
Găsiți fișierul Excel cu listele pe care doriți să le comparați și faceți dublu clic pe numele sau pictograma fișierului pentru a deschide foaia de calcul în Microsoft Excel.
Pasul 2. Faceți clic pe celula goală de lângă primul element din a doua listă
Găsiți a doua listă pe foaia de calcul și faceți clic pe celula goală de lângă primul element din listă din partea de sus.
- Puteți introduce formula VLookup aici.
- Alternativ, puteți selecta orice celulă goală din foaia de calcul. Această celulă vă va face mai convenabil să vedeți comparația dvs. lângă cea de-a doua listă.
Pasul 3. Tastați = vlookup (în celula goală
Formula VLookup vă va permite să comparați toate elementele de pe două liste separate și să vedeți dacă o valoare este o valoare repetată sau nouă.
Nu închideți paranteze formulei până când formula dvs. nu este completă
Pasul 4. Selectați primul element din a doua listă
Fără a închide paranteze formulă, faceți clic pe primul element din a doua listă. Aceasta va introduce prima celulă a celei de-a doua liste în formulă.
Pasul 5. Tastați a, virgulă în formulă
După selectarea primei celule din a doua listă, tastați o virgulă în formulă. În continuare, veți putea selecta gama de comparație.
Pasul 6. Țineți apăsat și selectați întreaga primă listă
Aceasta va insera intervalul de celule din prima listă în a doua parte a formulei VLookup.
Acest lucru vă va permite să căutați în prima listă elementul selectat din a doua listă (primul element din partea de sus a celei de-a doua liste) și să reveniți dacă este o valoare repetată sau nouă
Pasul 7. Tastați a, virgulă în formulă
Aceasta va bloca intervalul de comparație din formula dvs.
Pasul 8. Tastați 1 în formulă după virgulă
Acest număr reprezintă numărul dvs. de index al coloanei. Acesta va solicita formula VLookup să caute în coloana listă reală în loc de o coloană diferită de lângă ea.
Dacă doriți ca formula dvs. să returneze valoarea din coloana chiar lângă prima listă, tastați 2 aici
Pasul 9. Tastați a, virgulă în formulă
Aceasta vă va bloca numărul de index al coloanei (1) în formula VLookup.
Pasul 10. Tastați FALS în formulă
Aceasta va căuta în listă o potrivire exactă a elementului de căutare selectat (primul articol din partea de sus a celei de-a doua liste) în loc de potriviri aproximative.
- În loc de FALS puteți folosi 0, este exact la fel.
- Alternativ, puteți tasta TRUE sau 1 dacă doriți să căutați o potrivire aproximativă.
Pasul 11. Tastați) la final pentru a închide formula
Acum puteți rula formula și puteți vedea dacă elementul de căutare selectat din a doua listă este o valoare repetată sau nouă.
De exemplu, dacă a doua listă începe de la B1, iar prima listă merge de la celulele A1 la A5, formula dvs. va arăta ca = vlookup (B1, $ A $ 1: $ A $ 5, 1, fals).
Pasul 12. Apăsați ↵ Enter sau ⏎ Reveniți pe tastatură.
Aceasta va rula formula și va căuta prima listă pentru primul element din a doua listă.
- Dacă aceasta este o valoare repetată, veți vedea aceeași valoare imprimată din nou în celula formulă.
- Dacă aceasta este o valoare nouă, veți vedea „ #N / A"tipărit aici.
- De exemplu, dacă căutați „John” în prima listă și acum vedeți „John” în celula formulă, este o valoare repetată care apare pe ambele liste. Dacă vedeți „# N / A”, este o nouă valoare pe a doua listă.
Pasul 13. Selectați celula formulă
După ce ați rulat formula și ați văzut rezultatele pentru primul articol din listă, faceți clic pe celula formulă pentru ao selecta.
Pasul 14. Faceți clic și trageți în jos punctul verde din colțul din dreapta jos al celulei
Aceasta vă va extinde celula formulă de-a lungul listei și va aplica formula la fiecare articol din listă din a doua listă.
- În acest fel, puteți compara fiecare articol din a doua listă cu întreaga dvs. listă.
- Aceasta va căuta prima listă pentru fiecare articol din a doua listă în mod individual și va afișa rezultatul lângă fiecare celulă separat.
- Dacă doriți să vedeți un alt marker pentru valori noi în loc de „# N / A”, utilizați această formulă: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), „New Value”). Aceasta va tipări „Valoare nouă” pentru valori noi în loc de „# N / A”.
