Porovnávanie tabuliek hodnôt 1c 8. Ako porovnať dva stĺpce v Exceli pre zhody

Prečítanie tohto článku vám zaberie asi 10 minút. V nasledujúcich 5 minútach môžete jednoducho porovnať dva stĺpce v Exceli a zistiť, či sa v nich nenachádzajú duplikáty, odstrániť ich alebo farebne zvýrazniť. Takže, prišiel čas!

Excel je veľmi výkonná a skutočne skvelá aplikácia na vytváranie a spracovanie veľkého množstva údajov. Ak máte viacero zošitov s údajmi (alebo len jednu veľkú tabuľku), pravdepodobne budete chcieť porovnať 2 stĺpce, nájsť duplicitné hodnoty a potom s nimi niečo urobiť, napríklad odstrániť, zvýrazniť alebo vymazať obsah. Stĺpce môžu byť v rovnakej tabuľke, susediace alebo nesusediace, umiestnené na 2 rôznych hárkoch alebo dokonca v rôznych zošitoch.

Predstavte si, že máme 2 stĺpce s menami ľudí – 5 mien v stĺpci A a 3 mená v stĺpci B. Musíte porovnať mená v týchto dvoch stĺpcoch a nájsť všetky duplikáty. Ako viete, ide o fiktívne údaje, ktoré slúžia len na ilustráciu. V reálnych tabuľkách máme do činenia s tisíckami, ba až desiatkami tisíc záznamov.

Možnosť A: oba stĺpce sú na rovnakom hárku. Napríklad stĺpec A a stĺpec B.

Možnosť B: Stĺpce sú umiestnené na rôznych listoch. Napríklad stĺpec A na liste Hárok 2 a stĺpec A na liste Hárok 3.

Excel 2013, 2010 a 2007 majú vstavaný nástroj Odstrániť duplikát(Odstrániť duplikáty), ale v tejto situácii je bezmocný, pretože nedokáže porovnať údaje v 2 stĺpcoch. Okrem toho môže odstrániť iba duplikáty. Neexistujú žiadne ďalšie možnosti, ako je zvýraznenie alebo zmena farieb. A bodka!

Porovnajte 2 stĺpce v Exceli a nájdite duplicitné položky pomocou vzorcov

Možnosť A: oba stĺpce sú na rovnakom hárku

Nápoveda: Vo veľkých tabuľkách bude kopírovanie vzorca rýchlejšie, ak použijete klávesové skratky. Vyberte bunku C1 a stlačte Ctrl+C(ak chcete vzorec skopírovať do schránky), potom kliknite Ctrl+Shift+End(na označenie všetkých neprázdnych buniek v stĺpci C) a nakoniec kliknite Ctrl+V(pre vloženie vzorca do všetkých vybratých buniek).

Možnosť B: dva stĺpce sú na rôznych hárkoch (v rôznych knihách)

Prebieha spracovanie nájdených duplikátov

Skvelé, v prvom stĺpci sme našli položky, ktoré sa nachádzajú aj v druhom stĺpci. Teraz s nimi musíme niečo urobiť. Manuálne prechádzanie všetkých duplicitných záznamov v tabuľke je dosť neefektívne a zaberá príliš veľa času. Sú lepšie spôsoby.

Zobraziť iba duplicitné riadky v stĺpci A

Ak vaše stĺpce nemajú nadpisy, musíte ich pridať. Ak to chcete urobiť, umiestnite kurzor na číslo označujúce prvý riadok a zmení sa na čiernu šípku, ako je znázornené na obrázku nižšie:

Kliknite pravým tlačidlom myši a vyberte z kontextovej ponuky Vložiť(Vložiť):

Zadajte názvy stĺpcov, napríklad „ názov"A" duplikovať?“ Potom otvorte kartu Údaje(Údaje) a stlačte Filter(Filter):

Potom kliknite na malú sivú šípku vedľa „ duplikovať?“ na rozšírenie ponuky filtra; zrušte začiarknutie všetkých položiek v tomto zozname okrem Duplicitné a stlačte OK.

To je všetko, teraz vidíte len tie stĺpcové prvky A, ktoré sú v stĺpci duplikované IN. V našej tréningovej tabuľke sú len dve takéto bunky, ale ako viete, v praxi ich bude oveľa viac.

Na opätovné zobrazenie všetkých riadkov stĺpca A, kliknite na symbol filtra v stĺpci IN, ktorý teraz vyzerá ako lievik s malou šípkou a vyberte Vybrať všetko(Vybrať všetko). Alebo môžete urobiť to isté cez pás s nástrojmi kliknutím Údaje(Údaje) > Vybrať a filtrovať(Zoradiť a filtrovať) > jasný(Vymazať), ako je znázornené na obrázku nižšie:

Zmeňte farbu alebo zvýraznite nájdené duplikáty

Ak značky „ Duplicitné“ nepostačuje na vaše účely a chcete označiť duplicitné bunky inou farbou písma, farbou výplne alebo iným spôsobom...

V takom prípade filtrujte duplikáty, ako je uvedené vyššie, vyberte všetky filtrované bunky a kliknite Ctrl+1 otvoríte dialógové okno Formátovať bunky(Formát bunky). Ako príklad zmeňme farbu výplne buniek v riadkoch s duplikátmi na jasne žltú. Samozrejme, pomocou nástroja môžete zmeniť farbu výplne Vyplňte(Farba výplne). Domov(Domov), ale výhodou dialógového okna Formátovať bunky(Formát buniek) je, že môžete nakonfigurovať všetky možnosti formátovania naraz.

Teraz vám určite neunikne ani jedna bunka s duplikátmi:

Odstránenie duplicitných hodnôt z prvého stĺpca

Filtrujte tabuľku, aby sa zobrazili iba bunky s duplicitnými hodnotami, a vyberte tieto bunky.

Ak sú 2 porovnávané stĺpce na rôznych hárkoch, to znamená, že v rôznych tabuľkách kliknite pravým tlačidlom myši na vybraný rozsah a vyberte z kontextového menu Odstrániť riadok(Odstrániť riadok):

Kliknite OK keď vás Excel požiada, aby ste potvrdili, že naozaj chcete odstrániť celý riadok pracovného hárka, a potom vymažte filter. Ako vidíte, zostávajú iba riadky s jedinečnými hodnotami:

Ak sú na jednom hárku umiestnené 2 stĺpce, blízko seba (susedia) alebo nie blízko seba (nesusedia), potom bude proces odstraňovania duplikátov trochu náročnejší. Nemôžeme odstrániť celý riadok s duplicitnými hodnotami, pretože by sa tým odstránili aj bunky z druhého stĺpca. Aby sa v stĺpci zachovali iba jedinečné položky A, urobte nasledovné:

Ako vidíte, odstránenie duplikátov z dvoch stĺpcov v programe Excel pomocou vzorcov nie je také ťažké.

Niekedy je potrebné porovnať dva súbory MS Excel. Môže to byť nájdenie nezrovnalostí v cenách určitých položiek alebo zmena akýchkoľvek indikácií, na tom nezáleží, hlavná vec je, že je potrebné nájsť určité nezrovnalosti.

Nebolo by na škodu spomenúť, že ak existuje niekoľko záznamov v súbore MS Excel, potom nemá zmysel uchyľovať sa k automatizácii. Ak súbor obsahuje niekoľko stoviek, ba až tisícov záznamov, potom sa to bez pomoci výpočtového výkonu počítača nezaobíde.

Simulujme situáciu, že dva súbory majú rovnaký počet riadkov a nezrovnalosť treba hľadať v konkrétnom stĺpci alebo vo viacerých stĺpcoch. Takáto situácia je možná napríklad vtedy, ak potrebujete porovnať cenu tovaru podľa dvoch cenníkov, alebo porovnať miery športovcov pred a po tréningovej sezóne, hoci na takúto automatizáciu ich musí byť veľa.

Ako pracovný príklad si vezmime súbor s výkonom fiktívnych účastníkov: beh na 100 metrov, beh na 3000 metrov a ťahy. Prvý súbor je meranie na začiatku sezóny a druhý je koniec sezóny.

Prvý spôsob, ako vyriešiť problém. Riešením sú len vzorce MS Excel.

Keďže záznamy sú usporiadané vertikálne (najlogickejšie usporiadanie), je potrebné použiť funkciu. Ak používate horizontálne umiestnenie záznamov, budete musieť použiť funkciu.

Na porovnanie bežeckého výkonu na 100 metrov je vzorec nasledujúci:
=IF(VLOOKUP($B2;Hárok2!$B$2:$F$13,3;PRAVDA)<>D2;D2-VLOOKUP($B2;Hárok2!$B$2:$F$13,3,TRUE);"Žiadny rozdiel")
Ak nie je žiadny rozdiel, zobrazí sa hlásenie, že nie je žiadny rozdiel, ak je rozdiel, hodnota na konci sezóny sa odpočíta od hodnoty na začiatku sezóny.

Vzorec pre beh na 3000 metrov je nasledujúci:
=IF(VLOOKUP($B2;Hárok2!$B$2:$F$13,4;PRAVDA)<>E2;"Je tu rozdiel";"Nie je žiadny rozdiel")
Ak sa konečné a počiatočné hodnoty nezhodujú, zobrazí sa príslušná správa. Vzorec na ťahanie môže byť podobný ako ktorýkoľvek z predchádzajúcich, nemá zmysel ho dávať dodatočne. Konečný súbor s zistenými nezrovnalosťami je uvedený nižšie.

Trochu objasnenia. Aby sa vzorce ľahšie čítali, údaje z dvoch súborov boli presunuté do jedného (na rôznych hárkoch), ale to sa nedalo urobiť.

Video porovnávajúce dva súbory MS Excel pomocou a funkcií.

Druhý spôsob riešenia problému. Riešenie pomocou MS Access.

Tento problém možno vyriešiť, ak najprv importujete súbory MS Excel do programu Access. Pokiaľ ide o samotný spôsob importu externých údajov, nie je rozdiel v hľadaní rôznych polí (postačí akákoľvek z prezentovaných možností).

Posledne menované je prepojenie medzi súbormi Excel a Access, takže keď zmeníte údaje v súboroch Excel, nezrovnalosti sa zistia automaticky pri spustení dotazu v MS Access.

Ďalším krokom po importe je vytvorenie vzťahov medzi tabuľkami. Ako spojovacie pole vyberte jedinečné pole „Číslo položky“.
Tretím krokom je vytvorenie jednoduchého výberového dotazu pomocou nástroja Query Builder.

V prvom stĺpci uvádzame, ktoré záznamy je potrebné zobraziť, av druhom - za akých podmienok sa záznamy zobrazia. Prirodzene, pre druhé a tretie pole budú akcie podobné.

Video porovnávajúce súbory MS s Excelom pomocou MS Access.

V dôsledku vykonaných manipulácií sa zobrazia všetky záznamy s rôznymi údajmi v poli: „Beh 100 metrov“. Súbor MS Access je uvedený nižšie (bohužiaľ, SkyDrive neumožňuje vloženie ako súbor Excel)

Tieto dve metódy existujú na nájdenie nezrovnalostí v tabuľkách MS Excel. Každá má výhody aj nevýhody. Je zrejmé, že toto nie je úplný zoznam porovnaní týchto dvoch súborov programu Excel. Čakáme na vaše návrhy v komentároch.

Máme dve tabuľky objednávok skopírované do jedného pracovného listu. Je potrebné porovnať údaje z dvoch tabuliek v Exceli a skontrolovať, ktoré pozície sú v prvej tabuľke, ale nie v druhej. Nemá zmysel manuálne porovnávať hodnotu každej bunky.

Porovnajte dva stĺpce pre zhody v Exceli

Ako porovnať hodnoty v dvoch stĺpcoch v Exceli? Na vyriešenie tohto problému odporúčame použiť podmienené formátovanie, ktoré rýchlo zvýrazní položky, ktoré sú len v jednom stĺpci. Pracovný list s tabuľkami:

Prvým krokom je pomenovanie oboch tabuliek. To uľahčuje pochopenie, ktoré rozsahy buniek sa porovnávajú:

Vyberte nástroj VZORCE - Definované názvy - Priradiť názov.
V zobrazenom okne do poľa „Názov:“ zadajte hodnotu – Tabuľka_1.
Kliknite ľavým tlačidlom myši na vstupné pole „Rozsah:“ a vyberte rozsah: A2:A15. A kliknite na tlačidlo OK.

Pre druhý zoznam vykonajte rovnaké kroky, len mu dajte názov – Tabuľka_2. A špecifikujte rozsah C2:C15 - resp.

Užitočná rada! Názvy rozsahov je možné priradiť rýchlejšie pomocou poľa názvov. Nachádza sa naľavo od riadku vzorcov. Jednoducho vyberte rozsahy buniek a do poľa názvu zadajte príslušný názov rozsahu a stlačte Enter.

Teraz použijeme podmienené formátovanie na porovnanie dvoch zoznamov v Exceli. Potrebujeme získať nasledujúci výsledok:

Položky, ktoré sú v tabuľke_1, ale nie v tabuľke_2, sa zobrazia zelenou farbou. Zároveň budú modrou farbou zvýraznené pozície, ktoré sú v tabuľke_2, ale nie v tabuľke_1.

Princíp porovnávania údajov medzi dvoma stĺpcami v Exceli

Pri definovaní podmienok pre formátovanie buniek stĺpca sme použili funkciu COUNTIF. V tomto príklade táto funkcia kontroluje, koľkokrát sa hodnota druhého argumentu (napríklad A2) objaví v zozname prvého argumentu (napríklad Tabuľka_2). Ak početkrát = 0, vzorec vráti hodnotu TRUE. V tomto prípade je bunke priradený vlastný formát určený v možnostiach podmieneného formátovania.

Odkaz v druhom argumente je relatívny, čo znamená, že všetky bunky vybratého rozsahu budú postupne kontrolované (napríklad A2:A15). Napríklad na porovnanie dvoch cenníkov v Exceli aj na rôznych hárkoch. Druhý vzorec funguje podobne. Rovnaký princíp možno aplikovať na rôzne podobné úlohy.

Snáď každý, kto pracuje s dátami v Exceli, stojí pred otázkou, ako porovnať dva stĺpce v Exceli na podobnosti a rozdiely. Existuje niekoľko spôsobov, ako to urobiť. Pozrime sa bližšie na každý z nich.

Ako porovnať dva stĺpce v Exceli riadok po riadku

Pri porovnávaní dvoch stĺpcov údajov je často potrebné porovnať údaje v každom jednotlivom riadku, či ide o zhody alebo rozdiely. Takúto analýzu môžeme urobiť pomocou funkcie. Pozrime sa, ako to funguje pomocou nižšie uvedených príkladov.

Príklad 1: Ako porovnať dva stĺpce pre zhody a rozdiely v rovnakom riadku

Aby sme mohli porovnať údaje v každom riadku dvoch stĺpcov v Exceli, napíšme jednoduchý vzorec. Vzorec by sa mal vložiť do každého riadku v susednom stĺpci vedľa tabuľky, v ktorej sa nachádzajú hlavné údaje. Po vytvorení vzorca pre prvý riadok tabuľky ho môžeme roztiahnuť/skopírovať do zostávajúcich riadkov.

Aby sme skontrolovali, či dva stĺpce toho istého riadku obsahujú rovnaké údaje, potrebujeme vzorec:

=IF(A2=B2; “Zhoda”; “”)

Vzorec, ktorý určuje rozdiely medzi údajmi dvoch stĺpcov v jednom riadku, bude vyzerať takto:

=IF(A2<>B2; "Nezhodujú"; "")

Môžeme umiestniť test zhody a rozdielu medzi dva stĺpce na jeden riadok v jednom vzorci:

=IF(A2=B2; “Zhoda”; “Nezhoda”)

=IF(A2<>B2; "Nezhodujú"; "Prispôsobiť sa")

Príklad výsledku výpočtu môže vyzerať takto:

Ak chcete porovnať údaje v dvoch stĺpcoch toho istého riadka, pričom sa rozlišujú malé a veľké písmená, použite vzorec:

=IF(MATCH(A2;B2); “Zhoda”, “Jedinečné”)

Ako porovnať viacero stĺpcov pre zhody v jednom riadku Excelu

Excel vám umožňuje porovnávať údaje vo viacerých stĺpcoch toho istého riadku pomocou nasledujúcich kritérií:

Nájdite riadky s rovnakými hodnotami vo všetkých stĺpcoch tabuľky;
Nájdite riadky s rovnakými hodnotami v ľubovoľných dvoch stĺpcoch tabuľky;

Príklad 1 Ako nájsť zhody v jednom riadku vo viacerých stĺpcoch tabuľky

Predstavme si, že naša tabuľka pozostáva z niekoľkých stĺpcov údajov. Našou úlohou je nájsť riadky, v ktorých sa hodnoty zhodujú vo všetkých stĺpcoch. Excel a funkcie nám v tom pomôžu. Vzorec na určenie zhody bude nasledujúci:

=AK(A(A2=B2,A2=C2); „Zhodovať sa“; „ ““)

Ak má naša tabuľka veľa stĺpcov, bude jednoduchšie použiť funkciu v kombinácii s:

=AK(COUNTIF($A2:$C2,$A2)=3;”Zhoda”;” “)

Vo vzorci „5“ označuje počet stĺpcov tabuľky, pre ktoré sme vytvorili vzorec. Ak má vaša tabuľka viac alebo menej stĺpcov, táto hodnota by sa mala rovnať počtu stĺpcov.

Príklad 2: Ako nájsť zhody rovnakého riadku v ľubovoľných dvoch stĺpcoch tabuľky

Predstavme si, že našou úlohou je identifikovať z tabuľky s údajmi vo viacerých stĺpcoch tie riadky, v ktorých sú údaje rovnaké alebo sa opakujú aspoň v dvoch stĺpcoch. Funkcie a nám s tým pomôžu. Napíšme vzorec pre tabuľku pozostávajúcu z troch stĺpcov údajov:

=AK(ALEBO(A2=B2,B2=C2,A2=C2);”Zhoda”;” “)

V prípadoch, keď je v našej tabuľke príliš veľa stĺpcov, náš vzorec s funkciou bude veľmi veľký, pretože v jeho parametroch musíme špecifikovať kritériá zhody medzi jednotlivými stĺpcami tabuľky. Jednoduchším spôsobom je v tomto prípade použiť .

=IF(COUNTIF(B2:D2,A2)+COUNTIF(C2:D2,B2)+(C2=D2)=0; “Jedinečný reťazec”; “Nie je jedinečný reťazec”)

=IF(COUNTIF($B:$B,$A5)=0; „V stĺpci B nie sú žiadne zhody“; „V stĺpci B sú zhody“)

Tento vzorec kontroluje hodnoty v stĺpci B, aby zistil, či sa zhodujú s bunkami v stĺpci A.

Ak vaša tabuľka pozostáva z pevného počtu riadkov, môžete vo vzorci určiť jasný rozsah (napr. B2 USD: B10 USD). Tým sa urýchli vzorec.

Ako porovnať dva stĺpce v Exceli a farebne ich zvýrazniť

Keď hľadáme zhody medzi dvoma stĺpcami v Exceli, možno budeme musieť vizualizovať nájdené zhody alebo rozdiely v údajoch, napríklad pomocou farebného zvýraznenia. Najjednoduchší spôsob, ako zvýrazniť zhody a rozdiely vo farbe, je použiť podmienené formátovanie v Exceli. Pozrime sa, ako to urobiť pomocou nižšie uvedených príkladov.

Vyhľadanie a zvýraznenie zhodných farieb vo viacerých stĺpcoch v Exceli

V prípadoch, keď potrebujeme nájsť zhody v niekoľkých stĺpcoch, potom na to potrebujeme:

Vyberte stĺpce s údajmi, v ktorých chcete vypočítať zhody;
Na karte „Domov“ na paneli s nástrojmi kliknite na položku ponuky „Podmienené formátovanie“ -> „Pravidlá pre zvýraznenie buniek“ -> „Opakovanie hodnôt“;
V rozbaľovacom dialógovom okne vyberte v ľavom rozbaľovacom zozname možnosť „Opakovať“ a v pravom rozbaľovacom zozname vyberte, v akej farbe budú zvýraznené opakujúce sa hodnoty. Kliknite na tlačidlo „OK“:
Potom sa zhody zvýraznia vo vybranom stĺpci:

Nájdite a zvýraznite zodpovedajúce riadky v Exceli

Hľadanie zhodných buniek s údajmi v dvoch alebo viacerých stĺpcoch a vyhľadávanie zhôd celých riadkov s údajmi sú rôzne pojmy. Všimnite si prosím dve tabuľky nižšie:

Vyššie uvedené tabuľky obsahujú rovnaké údaje. Ich rozdiel je v tom, že v príklade vľavo sme hľadali zodpovedajúce bunky a vpravo sme našli celé opakujúce sa riadky s údajmi.

Pozrime sa, ako nájsť zodpovedajúce riadky v tabuľke:

Napravo od tabuľky s údajmi vytvoríme pomocný stĺpec, do ktorého oproti každému riadku s údajmi vložíme vzorec, ktorý kombinuje všetky hodnoty riadku tabuľky do jednej bunky:

=A2&B2&C2&D2

V pomocnom stĺpci uvidíte kombinované údaje tabuľky:

Ak chcete určiť zhodné riadky v tabuľke, vykonajte nasledujúce kroky:

Vyberte oblasť s údajmi v pomocnom stĺpci (v našom príklade ide o rozsah buniek E2:E15 );
Na karte „Domov“ na paneli s nástrojmi kliknite na položku ponuky „Podmienené formátovanie“ -> „Pravidlá pre zvýraznenie buniek“ -> „Opakovanie hodnôt“;
V rozbaľovacom dialógovom okne vyberte v ľavom rozbaľovacom zozname možnosť „Opakovať“ a v pravom rozbaľovacom zozname vyberte farbu, ktorou budú zvýraznené opakujúce sa hodnoty. Kliknite na tlačidlo „OK“:
Potom sa vo vybranom stĺpci zvýraznia duplicitné riadky:

Často je úlohou porovnať dva zoznamy prvkov. Robiť to ručne je príliš únavné a tiež nemožno vylúčiť možnosť chýb. Excel uľahčuje túto operáciu. Tento tip popisuje metódu využívajúcu podmienené formátovanie.

Na obr. Obrázok 164.1 zobrazuje príklad dvoch viacstĺpcových zoznamov mien. Pri použití podmieneného formátovania môžu byť rozdiely v zoznamoch zrejmé. Tieto príklady zoznamov obsahujú text, ale príslušná metóda pracuje aj s číselnými údajmi.

Prvý zoznam je A2:B31, tento rozsah sa nazýva OldList. Druhý zoznam je D2:E31, rozsah sa volá NewList. Rozsahy boli pomenované pomocou príkazu Vzorce Definované názvy Priraďte názov. Rozsahy nie je potrebné menovať, ale uľahčuje to prácu s nimi.

Začnime pridaním podmieneného formátovania do starého zoznamu.

Vyberte bunky v rozsahu OldList.
Vyberte.
V okne Vytvorte pravidlo formátovania vyberte položku s názvom Použite vzorec
Do poľa okna zadajte tento vzorec (obr. 164.2): =COUNTIF(NewList;A2)=0.
Kliknite na tlačidlo Formátovať a zadajte formátovanie, ktoré sa použije, keď je podmienka pravdivá. Najlepšie je zvoliť rôzne farby výplne.
Kliknite na tlačidlo OK.

Bunky v dosahu NewList použite podobný vzorec podmieneného formátovania.

Vyberte bunky v rozsahu NewList.
Vyberte Domov Podmienené formátovanie Vytvorte pravidlo otvoríte dialógové okno Vytvorte pravidlo formátovania.
V okne Vytvorte pravidlo formátovanie vyberte položku Použite vzorec na definovanie buniek, ktoré sa majú formátovať.
Do poľa okna zadajte tento vzorec: =COUNTIF(OldList;D2)=0 .
Kliknite na tlačidlo Formátovať a nastavte formátovanie, ktoré sa má použiť, keď je podmienka pravdivá (iná farba výplne).
Kliknite na tlačidlo OK.

V dôsledku toho budú zvýraznené mená, ktoré sú v starom zozname, ale nie v novom (obr. 164.3). Okrem toho sú zvýraznené aj mená v novom zozname, ktoré nie sú v starom zozname, ale inou farbou. Mená v oboch zoznamoch nie sú zvýraznené.

Obidva vzorce podmieneného formátovania používajú túto funkciu COUNTIF. Vypočíta, koľkokrát sa určitá hodnota objaví v rozsahu. Ak vzorec vráti 0, znamená to, že položka nie je v rozsahu. Takto prevezme podmienečné formátovanie a zmení sa farba pozadia bunky.