Porovnanie rozdielov medzi 2 súbormi v Exceli

Každý používateľ sa môže stretnúť so situáciou, kedy potrebuje porovnať dve tabuľky. No a v krajnom prípade musí každý porovnať dva stĺpce. Áno, samozrejme, práca s excelovými súbormi je veľmi pohodlná a pohodlná. Prepáčte, toto nie je prirovnanie. Vizuálne triedenie malej tabuľky je samozrejme možné, ale keď sa počet buniek dostane do tisícov, musíte použiť ďalšie analytické nástroje.

Žiaľ, ešte sa neotvoril čarovný prútik, ktorý umožňuje automaticky porovnať všetky informácie medzi sebou jedným kliknutím. Preto musíte pracovať, konkrétne zbierať údaje, špecifikovať potrebné vzorce a vykonávať ďalšie akcie, ktoré vám umožnia aspoň trochu automatizovať porovnávanie.

Takýchto akcií je veľa. Pozrime sa na niektoré z nich.

Aký je účel porovnávania súborov programu Excel?

Dôvodov, prečo sa porovnáva niekoľko excelových súborov, môže byť veľké množstvo. Skôr alebo neskôr sa každý používateľ stretne s takouto potrebou a nemá také otázky. Môžete napríklad porovnať údaje z dvoch prehľadov za rôzne štvrťroky, aby ste zistili, či finančná situácia stúpla alebo klesla.

Alebo, prípadne, učiteľ potrebuje vidieť, ktorí študenti boli vyhodení z univerzity, porovnaním zloženia študentskej skupiny minulý rok a tento rok.

Takýchto situácií môže byť obrovské množstvo. Prejdime však k praxi, pretože téma je pomerne zložitá.

Všetky spôsoby porovnania 2 tabuliek v Exceli

Hoci je téma zložitá, je jednoduchá. Áno, nečudujte sa. Je zložitý, pretože sa skladá z mnohých častí. Ale tieto časti sa dajú ľahko pochopiť a vykonať. Pozrime sa, ako môžete porovnať dve excelové tabuľky priamo v praxi.

Vzorec rovnosti a test nepravda-pravda

Začnime, samozrejme, najjednoduchším spôsobom. Tento spôsob porovnávania dokumentov je možný a v pomerne širokom rozsahu. Porovnávať môžete nielen textové hodnoty, ale aj číselné. A uveďme si malý príklad. Povedzme, že máme dva rozsahy s bunkami vo formáte čísel. Ak to chcete urobiť, jednoducho napíšte vzorec rovnosti =C2=E2. Ak sa ukáže, že sú rovnaké, do bunky sa zapíše „TRUE“. Ak sa líšia, potom FALSE. Potom musíte tento vzorec preniesť do celého rozsahu pomocou značky automatického dopĺňania.

Teraz je rozdiel viditeľný voľným okom.

Zvýraznenie odlišných hodnôt

Môžete tiež urobiť hodnoty, ktoré sa navzájom líšia, zvýraznené špeciálnou farbou. Toto je tiež celkom jednoduchá úloha. Ak vám stačí nájsť rozdiely medzi dvoma rozsahmi hodnôt alebo celými tabuľkami, musíte prejsť na kartu „Domov“ a vybrať tam položku „Nájsť a zvýrazniť“. Pred kliknutím naň nezabudnite zvýrazniť skupinu buniek, v ktorých sú uložené informácie na porovnanie. 

V zobrazenej ponuke kliknite na ponuku „Vybrať skupinu buniek ...“. Ďalej sa otvorí okno, v ktorom musíme ako kritérium vybrať rozdiely podľa riadkov.

Porovnanie 2 tabuliek pomocou podmieneného formátovania

Podmienené formátovanie je veľmi pohodlná a čo je dôležité aj funkčná metóda, ktorá vám umožní vybrať si farbu, ktorá zvýrazní inú alebo rovnakú hodnotu. Túto možnosť nájdete na karte Domov. Tam nájdete tlačidlo s príslušným názvom a v zobrazenom zozname vyberte „Spravovať pravidlá“. Zobrazí sa správca pravidiel, v ktorom musíme vybrať ponuku „Vytvoriť pravidlo“.

Ďalej zo zoznamu kritérií musíme vybrať to, kde sa uvádza, že musíme použiť vzorec na určenie buniek, ktoré budú naformátované špeciálnym spôsobom. V popise pravidla musíte zadať vzorec. V našom prípade je to =$C2<>$E2, po čom potvrdíme naše akcie stlačením tlačidla „Formátovať“. Potom nastavíme vzhľad bunky a uvidíme, či sa nám páči cez špeciálne miniokno so vzorkou. 

Ak všetko vyhovuje, kliknite na tlačidlo „OK“ a potvrďte akcie.

V Správcovi pravidiel podmieneného formátovania môže používateľ nájsť všetky pravidlá formátovania, ktoré sú platné v tomto dokumente. 

Funkcia COUNTIF + pravidlá porovnávania tabuliek

Všetky metódy, ktoré sme opísali vyššie, sú vhodné pre tie formáty, ktorých formát je rovnaký. Ak tabuľky neboli predtým objednané, potom je najlepšou metódou porovnanie dvoch tabuliek pomocou funkcie COUNTIF a pravidlá. 

Predstavme si, že máme dva rozsahy s mierne odlišnými informáciami. Stojíme pred úlohou porovnať ich a pochopiť, ktorá hodnota je iná. Najprv ho musíte vybrať v prvom rozsahu a prejsť na kartu „Domov“. Tam nájdeme predtým známu položku „Podmienené formátovanie“. Vytvoríme pravidlo a nastavíme pravidlo na použitie vzorca. 

V tomto príklade je vzorec taký, ako je znázornený na tejto snímke obrazovky.

Potom nastavíme formát, ako je opísané vyššie. Táto funkcia analyzuje hodnotu obsiahnutú v bunke C1 a pozrie sa na rozsah určený vo vzorci. Zodpovedá druhému stĺpcu. Musíme vziať toto pravidlo a skopírovať ho v celom rozsahu. Hurá, všetky bunky s neopakujúcimi sa hodnotami sú zvýraznené.

Funkcia VLOOKUP na porovnanie 2 tabuliek

V tejto metóde budeme brať do úvahy funkciu VPR, ktorý skontroluje, či sú v dvoch tabuľkách nejaké zhody. Ak to chcete urobiť, musíte zadať vzorec zobrazený na obrázku nižšie a preniesť ho do celého rozsahu, ktorý sa používa na porovnanie.

Táto funkcia iteruje každú hodnotu a zisťuje, či existujú nejaké duplikáty z prvého stĺpca do druhého. Po vykonaní všetkých operácií sa táto hodnota zapíše do bunky. Ak tam nie je, dostaneme chybu #N/A, čo je dosť na to, aby sme automaticky pochopili, ktorá hodnota sa nezhoduje.

Funkcia IF

Logická funkcia AK – toto je ďalší dobrý spôsob, ako porovnať dva rozsahy. Hlavnou črtou tejto metódy je, že môžete použiť iba tú časť poľa, ktorá sa porovnáva, a nie celú tabuľku. To šetrí zdroje pre počítač aj pre používateľa.

Uveďme si malý príklad. Máme dva stĺpce – A a B. Niektoré informácie v nich musíme navzájom porovnať. Aby sme to urobili, musíme pripraviť ďalší stĺpec služby C, v ktorom je napísaný nasledujúci vzorec.

Použitie vzorca, ktorý používa funkcie IF, IFERROR и VIAC EXPOZOVANÉ môžete iterovať cez všetky požadované prvky stĺpca A a potom stĺpca B. Ak bol nájdený v stĺpcoch B a A, vráti sa do zodpovedajúcej bunky.

Makro VBA

Makro je najkomplexnejšia, ale aj najpokročilejšia metóda porovnávania dvoch tabuliek. niektoré možnosti porovnávania vo všeobecnosti nie sú možné bez skriptov VBA. Umožňujú vám automatizovať proces a šetriť čas. Všetky potrebné operácie na prípravu údajov, ak sú raz naprogramované, sa budú vykonávať aj naďalej.

Na základe problému, ktorý sa má vyriešiť, môžete napísať akýkoľvek program, ktorý porovnáva údaje bez akéhokoľvek zásahu používateľa.

Ako porovnávať súbory v Exceli

Ak si používateľ dal za úlohu (dobre, alebo dostal jeden) porovnať dva súbory, potom to možno urobiť dvoma spôsobmi naraz. Prvým je použitie špecializovanej funkcie. Ak chcete implementovať túto metódu, postupujte podľa pokynov:

1. Otvorte súbory, ktoré chcete porovnať.
2. Otvorte kartu „Zobraziť“ – „Okno“ – „Zobraziť vedľa seba“.

Potom sa otvoria dva súbory v jednom dokumente programu Excel.

To isté možno urobiť s bežnými nástrojmi systému Windows. Najprv musíte otvoriť dva súbory v rôznych oknách. Potom vezmite jedno okno a presuňte ho na ľavú stranu obrazovky. Potom otvorte druhé okno a presuňte ho na pravú stranu. Potom budú dve okná vedľa seba. 

Podmienené formátovanie na porovnanie 2 excel súborov

Veľmi často porovnávanie dokumentov znamená ich zobrazenie vedľa seba. V niektorých prípadoch je však možné tento proces automatizovať pomocou podmieneného formátovania. Pomocou neho môžete skontrolovať, či sú medzi listami rozdiely. To vám umožní ušetriť čas, ktorý môžete využiť na iné účely.

Najprv musíme porovnávané hárky preniesť do jedného dokumentu. 

Ak to chcete urobiť, musíte kliknúť pravým tlačidlom myši na príslušný hárok a potom v kontextovej ponuke kliknúť na tlačidlo „Presunúť alebo kopírovať“. Ďalej sa zobrazí dialógové okno, v ktorom môže používateľ vybrať dokument, do ktorého má byť tento list vložený.

Ďalej musíte vybrať všetky požadované bunky, aby sa zobrazili všetky rozdiely. Najjednoduchší spôsob, ako to urobiť, je kliknúť na ľavú hornú bunku a potom stlačiť kombináciu klávesov Ctrl + Shift + End.

Potom prejdite do okna podmieneného formátovania a vytvorte nové pravidlo. Ako kritérium použijeme vzorec vhodný v konkrétnom prípade, potom nastavíme formát.

pozor: adresy buniek musia byť uvedené tie, ktoré sú na inom hárku. To je možné vykonať prostredníctvom ponuky zadávania vzorca.

Porovnanie údajov v Exceli na rôznych hárkoch

Predpokladajme, že máme zoznam zamestnancov, v ktorom sú uvedené aj ich platy. Tento zoznam sa aktualizuje každý mesiac. Tento zoznam sa skopíruje na nový hárok.

Predpokladajme, že potrebujeme porovnať platy. V tomto prípade môžete ako údaje použiť tabuľky z rôznych hárkov. Na zvýraznenie rozdielov použijeme podmienené formátovanie. Všetko je jednoduché.

S podmieneným formátovaním môžete efektívne porovnávať, aj keď sú mená zamestnancov v inom poradí.

Ako porovnať 2 hárky v excelovej tabuľke

Porovnanie informácií umiestnených na dvoch hárkoch sa vykonáva pomocou funkcie VIAC EXPOZOVANÉ. Jeho prvým parametrom je dvojica hodnôt, ktoré musíte hľadať na hárku, ktorý je zodpovedný za nasledujúci mesiac. Jednoducho povedané, marec. Zobrazený rozsah môžeme označiť ako kolekciu buniek, ktoré sú súčasťou pomenovaných rozsahov, kombinovaných v pároch.

Takže môžete porovnávať reťazce podľa dvoch kritérií – priezviska a platu. No, alebo akékoľvek iné, definované používateľom. Pre všetky zhody, ktoré bolo možné nájsť, sa do bunky, do ktorej je zadaný vzorec, zapíše číslo. Pre Excel bude táto hodnota vždy pravdivá. Preto, aby sa formátovanie použilo na tie bunky, ktoré boli odlišné, musíte túto hodnotu nahradiť KLAMEŤpomocou funkcie =NOT().

Nástroj na porovnanie tabuliek

Excel má špeciálny nástroj, ktorý vám umožňuje porovnávať tabuľky a automaticky zvýrazňovať zmeny. 

Je dôležité poznamenať, že tento nástroj je dostupný iba pre používateľov, ktorí si zakúpili kancelárske balíky Professional Plus.

Môžete ho otvoriť priamo na karte „Domov“ výberom položky „Porovnať súbory“.

Potom sa zobrazí dialógové okno, v ktorom musíte vybrať druhú verziu knihy. Môžete tiež zadať internetovú adresu, na ktorej sa táto kniha nachádza.

Keď vyberieme dve verzie dokumentu, musíme naše akcie potvrdiť tlačidlom OK.

V niektorých prípadoch môže dôjsť k chybe. Ak sa zobrazí, môže to znamenať, že súbor je chránený heslom. Po kliknutí na tlačidlo OK sa zobrazí výzva na jeho zadanie. 

Nástroj na porovnanie vyzerá ako dve excelové tabuľky vedľa seba v tom istom okne. V závislosti od toho, či boli informácie pridané, odstránené alebo či došlo k zmene vo vzorci (ako aj iných typov akcií), sú zmeny zvýraznené rôznymi farbami. 

Ako interpretovať výsledky porovnania

Je to veľmi jednoduché: rôzne typy rozdielov sú označené rôznymi farbami. Formátovanie sa môže rozšíriť na výplň bunky aj samotný text. Takže ak boli do bunky zadané údaje, výplň je zelená. Ak je niečo nejasné, samotná služba má symboly, ktoré ukazujú, aký typ zmeny je zvýraznený akou farbou.