Šajā rakstā wikiHow tiek mācīts, kā izmantot nosacījuma formatējumu, lai salīdzinātu visas šūnas Excel divos atsevišķos sarakstos un atzīmētu šūnas, kas parādās abos sarakstos. Šī funkcija ir pieejama tikai Excel darbvirsmas versijā, un mobilā lietotne to neatbalsta.
Soļi
1. metode no 2: izmantojot CountIf formulu
1. solis. Atveriet Excel izklājlapu, kuru vēlaties rediģēt
Atrodiet izklājlapas failu ar sarakstiem, kurus vēlaties salīdzināt, un veiciet dubultklikšķi uz faila, lai to atvērtu programmā Microsoft Excel.
2. solis. Izvēlieties savu pirmo sarakstu
Noklikšķiniet uz sava pirmā saraksta pirmās šūnas un velciet peli līdz saraksta pēdējai šūnai, lai atlasītu visu datu diapazonu.
Solis 3. Rīkjoslas lentē noklikšķiniet uz cilnes Formulas
Šo cilni varat atrast virs rīkjoslas izklājlapas augšdaļā. Tas atvērs jūsu formulas rīkus rīkjoslas lentē.
Solis 4. Rīkjoslā noklikšķiniet uz Definēt vārdu
Šo opciju varat atrast lentes "Formulas" vidū. Tas atvērs jaunu uznirstošo logu un ļaus jums nosaukt savu sarakstu.
5. solis. Nosaukuma laukā ierakstiet List1
Uznirstošā loga augšdaļā noklikšķiniet uz teksta lauka un šeit ievadiet saraksta nosaukumu.
- Vēlāk varat izmantot šo nosaukumu, lai ievietotu savu sarakstu salīdzināšanas formulā.
- Varat arī šeit savam sarakstam piešķirt citu nosaukumu. Piemēram, ja tas ir atrašanās vietu saraksts, varat to nosaukt par "locations1" vai "locationList".
6. solis Uznirstošajā logā noklikšķiniet uz Labi
Tas apstiprinās jūsu darbību un piešķirs sarakstam nosaukumu.
7. solis. Nosauciet savu otro sarakstu kā List2
Izpildiet tās pašas darbības, kas aprakstītas pirmajā sarakstā, un piešķiriet savam otrajam sarakstam nosaukumu. Tas ļaus jums vēlāk ātri izmantot šo otro sarakstu salīdzināšanas formulā.
Sarakstam varat piešķirt jebkuru vēlamo vārdu. Noteikti atcerieties vai pierakstiet nosaukumu, ko šeit piešķīrāt katram sarakstam
8. solis. Izvēlieties savu pirmo sarakstu
Noklikšķiniet uz pirmā saraksta pirmā šūnas un velciet uz leju, lai atlasītu visu datu diapazonu.
Pirms nosacījumu formatēšanas iestatīšanas pārliecinieties, vai ir atlasīts jūsu pirmais saraksts
9. solis. Rīkjoslā noklikšķiniet uz cilnes Sākums
Šī ir pirmā cilne rīkjoslas lentes augšējā kreisajā stūrī. Rīkjoslā tiks atvērti jūsu izklājlapas pamata rīki.
10. solis. Rīkjoslā noklikšķiniet uz Nosacījuma formatēšana
Šī opcija izskatās kā maza izklājlapas ikona, kurā dažas šūnas ir iezīmētas sarkanā un zilā krāsā. Tiks atvērta visu formatēšanas opciju nolaižamā izvēlne.
11. darbība. Nolaižamajā izvēlnē noklikšķiniet uz Jauns noteikums
Tas atvērs jaunu uznirstošo logu un ļaus manuāli iestatīt jaunu formatēšanas kārtulu atlasītajam diapazonam.
12. solis. Atlasiet opciju "Izmantojiet formulu, lai noteiktu, kuras šūnas formatēt"
Šī opcija ļaus jums manuāli ierakstīt formatēšanas formulu, lai salīdzinātu savus divus sarakstus.
- Ieslēgts Windows, jūs to atradīsit noteikumu saraksta apakšdaļā lodziņā "Atlasīt kārtulas veidu".
- Ieslēgts Mac, izvēlieties Klasika uznirstošā loga augšdaļā esošajā nolaižamajā izvēlnē "Stils". Pēc tam atrodiet šo opciju otrajā nolaižamajā izvēlnē zem stila.
Solis 13. Uznirstošajā logā noklikšķiniet uz formulas lauka
Šeit varat ievadīt jebkuru derīgu Excel formulu, lai iestatītu nosacījuma formatēšanas kārtulu.
14. solis. Formulas joslā ierakstiet = countertif (List2, A1) = 1
Šī formula skenēs jūsu abus sarakstus un atzīmēs visas šūnas jūsu pirmajā sarakstā, kas parādās arī otrajā sarakstā.
- Aizstājiet A1 formulā ar pirmā saraksta pirmās šūnas numuru.
- Piemēram, ja jūsu pirmā saraksta pirmā šūna ir šūna D5, tad jūsu formula izskatīsies kā = counter (List2, D5) = 1.
- Ja savam otrajam sarakstam piešķīrāt citu nosaukumu, noteikti aizstājiet List2 formulā ar sava saraksta faktisko nosaukumu.
- Alternatīvi, mainiet formulu uz = countif (List2, A1) = 0, ja vēlaties atzīmēt šūnas, kuras ne parādās otrajā sarakstā.
15. solis. Formulas joslā ierakstiet = countif (List1, B1) = 1 (pēc izvēles)
Ja vēlaties atrast un atzīmēt šūnas savā otrais sarakstā, kas parādās arī pirmajā sarakstā, izmantojiet šo formulu, nevis pirmo.
Aizstājiet sarakstu1 ar pirmā saraksta nosaukumu un B1 ar otrā saraksta pirmo šūnu
16. solis. Atlasiet pielāgotu formātu, lai atzīmētu šūnas (pēc izvēles)
Varat atlasīt pielāgotu fona aizpildījuma krāsu un dažādus fontu stilus, lai atzīmētu šūnas, kuras atrod jūsu formula.
- Ieslēgts Windows, noklikšķiniet uz Formāts pogu uznirstošā loga apakšējā labajā stūrī. Jūs varat izvēlēties fona krāsu cilnē "Aizpildīt" un fontu stilus cilnē "Fonts".
- Ieslēgts Mac, apakšā esošajā nolaižamajā izvēlnē "Formatēt" atlasiet iepriekš iestatītu formātu. Varat arī izvēlēties pielāgots formāts šeit, lai manuāli atlasītu fona aizpildījumu un fontu stilus.
17. darbība. Uznirstošajā logā noklikšķiniet uz Labi
Tas apstiprinās un piemēros jūsu salīdzināšanas formulu. Visas šūnas jūsu pirmajā sarakstā, kas parādās arī otrajā sarakstā, tiks atzīmētas ar jūsu izvēlēto krāsu un fontu.
- Piemēram, ja atlasāt gaiši sarkanu aizpildījumu ar tumši sarkanu tekstu, visas atkārtotās šūnas jūsu pirmajā sarakstā kļūs par šo krāsu.
- Ja izmantojat iepriekš minēto otro formulu, nosacījuma formatēšana atzīmēs atkārtotās šūnas otrajā sarakstā, nevis pirmajā.
2. metode no 2: izmantojot VLookup formulu
1. solis. Atveriet savu Excel izklājlapu
Atrodiet Excel failu ar sarakstiem, kurus vēlaties salīdzināt, un veiciet dubultklikšķi uz faila nosaukuma vai ikonas, lai programmā Microsoft Excel atvērtu izklājlapu.
2. solis Noklikšķiniet uz tukšās šūnas blakus pirmā saraksta otrajam sarakstam
Izklājlapā atrodiet savu otro sarakstu un augšpusē noklikšķiniet uz tukšās šūnas blakus pirmajam saraksta vienumam.
- Šeit varat ievietot savu VLookup formulu.
- Varat arī atlasīt jebkuru tukšu šūnu izklājlapā. Šī šūna tikai ļaus jums ērtāk redzēt salīdzinājumu blakus otrajam sarakstam.
3. solis. Tukšajā šūnā ierakstiet = vlookup
VLookup formula ļaus jums salīdzināt visus vienumus divos atsevišķos sarakstos un redzēt, vai vērtība ir atkārtota vai jauna vērtība.
Neaizveriet formulas iekavas, kamēr formula nav pabeigta
4. solis. Otrajā sarakstā atlasiet pirmo vienumu
Neaizverot formulas iekavas, noklikšķiniet uz otrā saraksta pirmā vienuma. Formulā tiks ievietota otrā saraksta pirmā šūna.
Solis 5. Formulā ierakstiet a, komatu
Pēc otrā saraksta pirmās šūnas atlasīšanas formulā ierakstiet komatu. Tālāk varēsit izvēlēties salīdzināšanas diapazonu.
6. solis. Turiet nospiestu un atlasiet visu pirmo sarakstu
Tādējādi pirmā saraksta šūnu diapazons tiks ievietots VLookup formulas otrajā daļā.
Tas ļaus jums meklēt pirmajā sarakstā atlasīto vienumu no otrā saraksta (pirmais vienums otrā saraksta augšdaļā) un atgriezties, ja tā ir atkārtota vai jauna vērtība
7. solis. Formulā ierakstiet a, komatu
Tas bloķēs salīdzināšanas diapazonu jūsu formulā.
8. solis. Aiz komata ierakstiet 1 formulā
Šis skaitlis apzīmē jūsu kolonnu indeksa numuru. Tas liks VLookup formulai meklēt faktisko saraksta kolonnu, nevis citu sleju blakus tai.
Ja vēlaties, lai formula atgrieztu vērtību no slejas blakus pirmajam sarakstam, ierakstiet 2 šeit
9. solis. Formulā ierakstiet a, komatu
Tas bloķēs jūsu kolonnu indeksa numuru (1) VLookup formulā.
10. solis. Formulā ierakstiet FALSE
Sarakstā tiks meklēta precīza atlasītā meklēšanas vienuma atbilstība (pirmais vienums otrā saraksta augšdaļā), nevis aptuvenās atbilstības.
- FALSE vietā varat izmantot 0, tas ir tieši tas pats.
- Varat arī ievadīt TRUE vai 1, ja vēlaties meklēt aptuvenu atbilstību.
Solis 11. Ierakstiet) beigās, lai aizvērtu formulu
Tagad varat palaist formulu un pārbaudīt, vai atlasītais meklēšanas vienums otrajā sarakstā ir atkārtota vai jauna vērtība.
Piemēram, ja jūsu otrais saraksts sākas ar B1 un jūsu pirmais saraksts ir no šūnām A1 līdz A5, jūsu formula izskatīsies šādi = vlookup (B1, $ A $ 1: $ A $ 5, 1, false).
12. solis. Nospiediet. Enter vai Atgriezieties tastatūrā.
Tiks palaista formula un jūsu pirmajā sarakstā tiks meklēts pirmais vienums no otrā saraksta.
- Ja šī ir atkārtota vērtība, formulas šūnā atkal tiks parādīta tā pati vērtība.
- Ja šī ir jauna vērtība, jūs redzēsit " #N/A"iespiests šeit.
- Piemēram, ja pirmajā sarakstā meklējat “Jānis” un formulas šūnā tagad redzat “Jānis”, tā ir atkārtota vērtība, kas parādās abos sarakstos. Ja redzat “#N/A”, tā ir jauna vērtība otrajā sarakstā.
13. solis. Atlasiet formulas šūnu
Pēc formulas palaišanas un pirmā saraksta vienuma rezultātu skatīšanas noklikšķiniet uz formulas šūnas, lai to atlasītu.
14. solis. Noklikšķiniet un velciet uz leju zaļo punktu šūnas apakšējā labajā stūrī
Tas paplašinās jūsu formulas šūnu sarakstā un izmantos formulu katram otrā saraksta vienumam.
- Tādā veidā jūs varat salīdzināt katru otrā saraksta vienumu ar visu pirmo sarakstu.
- Tādējādi jūsu pirmais saraksts meklēs katru otrā saraksta vienumu atsevišķi un parādīs rezultātu blakus katrai šūnai.
- Ja vēlaties redzēt citu marķieri jaunām vērtībām, nevis "#N/A", izmantojiet šo formulu: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "New Value"). Tādējādi jaunajām vērtībām tiks drukāts “Jauna vērtība”, nevis “#N/A”.
