Skatieties video - salīdziniet divas Excel kolonnas, lai atrastu atbilstības un atšķirības
Viens vaicājums, ko es saņemu daudz, ir - “kā salīdzināt divas kolonnas programmā Excel?”.
To var izdarīt daudzos dažādos veidos, un izmantotā metode būs atkarīga no datu struktūras un lietotāja vēlmēm.
Piemēram, iespējams, vēlēsities salīdzināt divas kolonnas un atrast vai izcelt visus atbilstošos datu punktus (kas atrodas abās slejās) vai tikai atšķirības (ja datu punkts atrodas vienā kolonnā, bet ne citā) utt.
Tā kā man par to tik daudz jautā, es nolēmu uzrakstīt šo milzīgo pamācību ar nolūku aptvert lielāko daļu (ja ne visus) iespējamos scenārijus.
Ja jums tas šķiet noderīgi, nododiet to citiem Excel lietotājiem.
Ņemiet vērā, ka šajā apmācībā parādītās kolonnas salīdzināšanas metodes nav vienīgās.
Pamatojoties uz jūsu datu kopu, jums, iespējams, būs jāmaina vai jāpielāgo metode. Tomēr pamatprincipi paliks nemainīgi.
Ja jūs domājat, ka šai apmācībai var pievienot kaut ko, dariet man to zināmu komentāru sadaļā
Salīdziniet divas kolonnas precīzai rindu atbilstībai
Šis ir vienkāršākais salīdzināšanas veids. Šajā gadījumā jums jāsalīdzina rindas pa rindām un jānosaka, kurām rindām ir vienādi dati un kurām nav.
Piemērs: salīdziniet šūnas vienā rindā
Tālāk ir datu kopa, kurā man jāpārbauda, vai nosaukums A slejā ir vienāds B slejā.
Ja ir sakritība, man rezultātu vajag kā “PATIESU”, un, ja nesakrīt, tad rezultātu vajag kā “FALSE”.
Tālāk sniegtā formula to darītu:
= A2 = B2
Piemērs: salīdziniet šūnas vienā rindā (izmantojot IF formulu)
Ja vēlaties iegūt vairāk aprakstošu rezultātu, varat izmantot vienkāršu IF formulu, lai atgrieztu “Match”, ja nosaukumi ir vienādi, un “Nesakritība”, ja nosaukumi ir atšķirīgi.
= IF (A2 = B2, "Atbilstība", "Neatbilstība")
Piezīme. Ja vēlaties salīdzināšanas reģistru padarīt par reģistrjutīgu, izmantojiet šādu IF formulu:
= JA (PRECĪZS (A2, B2), "Atbilstība", "Neatbilstība")
Izmantojot iepriekš minēto formulu, “IBM” un “ibm” tiktu uzskatīti par diviem dažādiem nosaukumiem, un iepriekš minētā formula atgrieztu “Neatbilstība”.
Piemērs: iezīmējiet rindas ar atbilstošiem datiem
Ja vēlaties izcelt rindas ar atbilstošiem datiem (nevis iegūt rezultātu atsevišķā slejā), varat to izdarīt, izmantojot nosacīto formatējumu.
Lai to izdarītu, veiciet tālāk norādītās darbības.
- Atlasiet visu datu kopu.
- Noklikšķiniet uz cilnes Sākums.
- Grupā Stili noklikšķiniet uz opcijas Nosacījuma formatēšana.
- Nolaižamajā izvēlnē noklikšķiniet uz “Jauns noteikums”.
- Dialoglodziņā “Jauns formatēšanas noteikums” noklikšķiniet uz “Izmantot formulu, lai noteiktu, kuras šūnas formatēt”.
- Formulas laukā ievadiet formulu: = $ A1 = $ B1
- Noklikšķiniet uz pogas Formatēt un norādiet formātu, kuru vēlaties lietot atbilstošajām šūnām.
- Noklikšķiniet uz Labi.
Tas iezīmēs visas šūnas, kuru nosaukumi katrā rindā ir vienādi.
Salīdziniet divas kolonnas un izcelto atbilstību
Ja vēlaties salīdzināt divas kolonnas un izcelt atbilstošos datus, nosacījumu formatēšanā varat izmantot dublikātu funkcionalitāti.
Ņemiet vērā, ka tas atšķiras no tā, ko mēs redzējām, salīdzinot katru rindu. Šajā gadījumā mēs neveiksim rindu salīdzināšanu.
Piemērs: salīdziniet divas kolonnas un iezīmējiet atbilstošos datus
Bieži vien jūs saņemsiet datu kopas, kurās ir atbilstības, taču tās var nebūt vienā rindā.
Kaut kas, kā parādīts zemāk:
Ņemiet vērā, ka A slejas saraksts ir lielāks nekā B. Arī daži nosaukumi ir abos sarakstos, bet ne vienā rindā (piemēram, IBM, Adobe, Walmart).
Ja vēlaties izcelt visus atbilstošos uzņēmumu nosaukumus, varat to izdarīt, izmantojot nosacījuma formatējumu.
Lai to izdarītu, veiciet tālāk norādītās darbības.
- Atlasiet visu datu kopu.
- Noklikšķiniet uz cilnes Sākums.
- Grupā Stili noklikšķiniet uz opcijas Nosacījuma formatēšana.
- Virziet kursoru uz opcijas Izcelt šūnu noteikumus.
- Noklikšķiniet uz Dublēt vērtības.
- Dialoglodziņā Dublēt vērtības pārliecinieties, vai ir atlasīta opcija “Dublēt”.
- Norādiet formatējumu.
- Noklikšķiniet uz Labi.
Iepriekš minētās darbības sniegs jums rezultātu, kā parādīts zemāk.
Piezīme. Nosacījuma formatēšanas dublikāta noteikums nav reģistrjutīgs. Tātad “ābols” un “ābols” tiek uzskatīti par vienādiem un tiks izcelti kā dublikāti.
Piemērs: salīdziniet divas kolonnas un iezīmējiet neatbilstošos datus
Ja vēlaties izcelt nosaukumus, kas ir vienā sarakstā, nevis otrā, varat izmantot arī nosacījuma formatējumu.
- Atlasiet visu datu kopu.
- Noklikšķiniet uz cilnes Sākums.
- Grupā Stili noklikšķiniet uz opcijas Nosacījuma formatēšana.
- Virziet kursoru uz opcijas Izcelt šūnu kārtulas.
- Noklikšķiniet uz Dublēt vērtības.
- Dialoglodziņā Dublēt vērtības pārliecinieties, vai ir atlasīts “Unikāls”.
- Norādiet formatējumu.
- Noklikšķiniet uz Labi.
Tas sniegs jums rezultātu, kā parādīts zemāk. Tas izceļ visas šūnas, kurām ir nosaukums, kura nav citā sarakstā.
Salīdziniet divas kolonnas un atrodiet trūkstošos datu punktus
Ja vēlaties noteikt, vai datu saraksts no viena saraksta atrodas otrā sarakstā, jums jāizmanto uzmeklēšanas formulas.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk, un vēlaties identificēt uzņēmumus, kas atrodas A slejā, bet ne B slejā,
Lai to izdarītu, es varu izmantot šādu VLOOKUP formulu.
= KĻŪDA (APSKATS (A2, $ B $ 2: $ B $ 10,1,0))
Šī formula izmanto funkciju VLOOKUP, lai pārbaudītu, vai A slejā esošais uzņēmuma nosaukums ir B slejā. Ja tas ir pieejams, tas atgriezīs šo nosaukumu no slejas B, pretējā gadījumā atgriezīs kļūdu #N/A.
Šie nosaukumi, kas atgriež kļūdu #N/A, trūkst B slejā.
Funkcija ISERROR atgriezīs TRUE, ja VLOOKUP rezultāts ir kļūda, un FALSE, ja tā nav kļūda.
Ja vēlaties iegūt sarakstu ar visiem nosaukumiem, kuriem nav atbilstības, varat filtrēt rezultātu sleju, lai iegūtu visas šūnas ar TRUE.
Lai to izdarītu, varat izmantot arī funkciju MATCH;
= NĒ (ISNUMBER (MATCH (A2, $ B $ 2: $ B $ 10,0)))
Piezīme. Personīgi es labprātāk izmantoju funkciju Match (vai INDEX/MATCH kombināciju), nevis VLOOKUP. Es uzskatu, ka tas ir elastīgāks un spēcīgāks. Šeit varat izlasīt atšķirību starp Vlookup un Index/Match.
Salīdziniet divas kolonnas un velciet atbilstošos datus
Ja jums ir divas datu kopas un vēlaties salīdzināt viena saraksta vienumus ar citu un iegūt atbilstošu datu punktu, jums jāizmanto uzmeklēšanas formulas.
Piemērs: velciet atbilstošos datus (precīzi)
Piemēram, zemāk esošajā sarakstā es vēlos iegūt tirgus slejas vērtību 2. slejai. Lai to izdarītu, šī vērtība ir jāatrod 1. slejā un pēc tam jāiegūst atbilstošā tirgus novērtējuma vērtība.
Zemāk ir formula, kas to darīs:
= MEKLĒŠANA (D2, $ A $ 2: $ B $ 14,2,0)
vai
= INDEKSS ($ A $ 2: $ B $ 14, MATCH (D2, $ A $ 2: $ A $ 14,0), 2)
Piemērs: velciet atbilstošos datus (daļēji)
Ja iegūstat datu kopu, kuras nosaukumos abās kolonnās ir neliela atšķirība, iepriekš norādīto uzmeklēšanas formulu izmantošana nedarbosies.
Lai iegūtu pareizo rezultātu, šīm uzmeklēšanas formulām ir nepieciešama precīza atbilstība. Funkcijā VLOOKUP vai MATCH ir pieejama aptuvena atbilstības opcija, taču to šeit nevar izmantot.
Pieņemsim, ka jums ir datu kopa, kā parādīts zemāk. Ņemiet vērā, ka 2. slejā ir nosaukumi, kas nav pilnīgi (piemēram, JPMorgan, nevis JPMorgan Chase un Exxon, nevis ExxonMobil).
Šādā gadījumā varat izmantot daļēju uzmeklēšanu, izmantojot aizstājējzīmes.
Šāda formula sniegs pareizo rezultātu šajā gadījumā:
= MEKLĒŠANA ("*" & D2 & "*", $ A $ 2: $ B $ 14,2,0)
vai
= INDEKSS ($ A $ 2: $ B $ 14, MATCH ("*" & D2 & "*", $ A $ 2: $ A $ 14,0), 2)
Iepriekš minētajā piemērā zvaigznīte (*) ir aizstājējzīme, kas var attēlot jebkuru rakstzīmju skaitu. Ja uzmeklēšanas vērtību papildina abas puses, jebkura vērtība 1. slejā, kurā ir uzmeklēšanas vērtība 2. slejā, tiks uzskatīta par atbilstību.
Piemēram, * Exxon * atbilst ExxonMobil (jo * var attēlot neierobežotu rakstzīmju skaitu).
Jums varētu patikt arī šādi Excel padomi un apmācības:
- Kā salīdzināt divas Excel lapas (atšķirībām)
- Kā izcelt tukšās šūnas programmā Excel.
- Programmā Excel iezīmējiet KATRU citu rindu.
- Excel uzlabotais filtrs: pilnīga rokasgrāmata ar piemēriem.
- Iezīmējiet rindas, pamatojoties uz šūnas vērtību programmā Excel.
