Ako porovnať dva listy programu Excel (pre rozdiely)

Pozrite si video - Ako porovnať dva listy programu Excel s rozdielom

Porovnanie dvoch súborov programu Excel (alebo porovnanie dvoch hárkov v jednom súbore) môže byť náročné, pretože zošit programu Excel zobrazuje iba jeden list naraz.

Keď máte veľa údajov, ktoré je potrebné porovnať, je to ešte náročnejšie a náchylnejšie na chyby.

Našťastie v programe Excel existuje niekoľko skvelých funkcií, ktoré vám umožňujú otvoriť a ľahko porovnať dva súbory programu Excel.

V tomto tutoriále k Excelu vám ukážem niekoľko spôsobov, ako porovnať dva rôzne súbory programu Excel (alebo hárky) a skontrolujte rozdiely. Metóda, ktorú si vyberiete, bude závisieť od toho, ako sú vaše údaje štruktúrované a aký druh porovnania hľadáte.

Začnime!

Porovnajte dva listy programu Excel v oddelených súboroch programu Excel (vedľa seba)

Ak chcete porovnať dva samostatné súbory programu Excel vedľa seba (alebo dva listy v tom istom zošite), v programe Excel je na to vstavaná funkcia.

To je Pohľad vedľa seba možnosť.

Toto sa odporúča iba vtedy, ak máte malú množinu údajov a manuálne porovnávanie týchto súborov bude pravdepodobne menej časovo náročné a náchylné na chyby. Ak máte veľkú množinu údajov, odporúčam použiť podmienenú metódu alebo metódu vzorca popísanú ďalej v tomto návode.

Pozrime sa, ako to použiť, keď musíte porovnať dva samostatné súbory alebo dva listy v jednom súbore.

Predpokladajme, že máte dva súbory na dva rôzne mesiace a chcete skontrolovať, aké hodnoty sa v týchto dvoch súboroch líšia.

V predvolenom nastavení pri otvorení súboru pravdepodobne zaberie celú vašu obrazovku. Aj keď zmenšíte veľkosť, v hornej časti sa vždy zobrazí jeden súbor programu Excel.

S možnosťou zobrazenia vedľa seba môžete otvoriť dva súbory a potom ich usporiadať horizontálne alebo vertikálne. To vám umožní ľahko porovnávať hodnoty bez prepínania tam a späť.

Nasledujú kroky na zarovnanie dvoch súborov vedľa seba a ich porovnanie:

1. Otvorte súbory, ktoré chcete porovnať.
2. V každom súbore vyberte list, ktorý chcete porovnať.
3. Kliknite na kartu Zobraziť
4. V skupine Windows kliknite na možnosť „Zobraziť vedľa seba“. Táto možnosť bude k dispozícii iba vtedy, ak máte otvorené dva alebo viac súborov programu Excel.

Hneď ako kliknete na možnosť Zobraziť vedľa seba, Excel usporiada zošit horizontálne. Oba súbory budú viditeľné a vy ich môžete voľne upravovať/porovnávať, pokiaľ sú usporiadané vedľa seba.

V prípade, že chcete súbory usporiadať vertikálne, kliknite na možnosť Usporiadať všetko (na karte Zobraziť).

Otvorí sa dialógové okno „Usporiadať systém Windows“, kde môžete vybrať „Vertikálne“.

V tomto mieste, ak prejdete nadol v jednom z pracovných hárkov, druhý zostane tak, ako je. Môžete to zmeniť tak, že keď sa posúvate v jednom hárku, súčasne sa bude posúvať aj druhý. To uľahčuje porovnanie po riadkoch a zisťovanie rozdielov.

Na to však musíte povoliť Synchrónne posúvanie.

Ak chcete povoliť synchrónne posúvanie, kliknite na kartu Zobraziť (v ktoromkoľvek zošite) a potom kliknite na možnosť Synchrónne posúvanie. Toto je prepínacie tlačidlo (takže ak ho chcete vypnúť, jednoducho naň znova kliknite).

Porovnanie viacerých hárkov v oddelených súboroch programu Excel (vedľa seba)

S možnosťou „Zobraziť vedľa seba“ môžete naraz porovnať iba dva súbory programu Excel.

V prípade, že máte otvorených viac súborov programu Excel, po kliknutí na možnosť Zobraziť vedľa seba sa vám zobrazí dialógové okno „Porovnať vedľa seba“, kde si môžete vybrať, ktorý súbor chcete porovnať s aktívnym zošitom.

V prípade, že chcete naraz porovnať viac ako dva súbory, otvorte všetky tieto súbory a potom kliknite na možnosť Usporiadať všetko (je to na karte Zobraziť).

V dialógovom okne Usporiadať systém Windows vyberte položku Vertikálne/Horizontálne a potom kliknite na tlačidlo OK.

To usporiada všetky otvorené súbory programu Excel vo vybranom poradí (vertikálne alebo horizontálne).

Porovnajte dva listy (vedľa seba) v tom istom zošite programu Excel

V prípade, že chcete porovnať dva samostatné listy v tom istom zošite, nemôžete použiť funkciu Zobraziť vedľa seba (pretože funguje iba pre samostatné súbory programu Excel).

Ale stále môžete robiť to isté porovnanie vedľa seba.

To je možné vďaka „Nová funkcia systému Windows v Exceli, ktorý vám umožní otvoriť dve inštancie v jednom zošite. Keď máte otvorené dve inštancie, môžete ich usporiadať vedľa seba a potom ich porovnať.

Predpokladajme, že máte zošit programu Excel, ktorý má dva listy na dva rôzne mesiace (január a február) a chcete ich porovnať vedľa seba, aby ste zistili, ako sa zmenili tržby na obchod:

Nasledujú kroky na porovnanie dvoch hárkov v programe Excel:

1. Otvorte zošit obsahujúci listy, ktoré chcete porovnať.
2. Kliknite na kartu Zobraziť
3. V skupine Okno kliknite na možnosť „Nové okno“. Tým sa otvára druhá inštancia toho istého zošita.
4. Na karte „Zobraziť“ kliknite na „Usporiadať všetko“. Tým sa otvorí dialógové okno Usporiadať systém Windows
5. Ak chcete porovnávať údaje v stĺpcoch, vyberte „Vertikálne“ (ak chcete porovnávať údaje v riadkoch, vyberte možnosť Horizontálne).
6. Kliknite na tlačidlo OK.

Vyššie uvedené kroky by vertikálne usporiadali obe inštancie zošita.

V tomto čase by mali oba zošity vybratý rovnaký pracovný list. V jednom zošitov vyberte druhý list, ktorý chcete porovnať s aktívnym listom.

Ako to funguje?

Keď kliknete na Nové okno, znova sa otvorí ten istý zošit s trochu iným názvom. Ak je napríklad názov vášho zošita „Test“ a kliknete na Nové okno, už otvorený zošit sa pomenuje „Test - 1“ a druhá inštancia ako „Test - 2“.

Všimnite si toho, že sú to stále tie isté zošity. Ak vykonáte zmeny v niektorom z týchto zošitov, prejaví sa to v oboch.

A keď zatvoríte jednu inštanciu otvoreného súboru, meno sa vráti späť na pôvodné.

Ak chcete, môžete tiež povoliť synchrónne posúvanie (kliknutím na možnosť „Synchrónne posúvanie“ na karte „Zobraziť“)

Porovnajte dva listy a zvýraznite rozdiely (pomocou podmieneného formátovania)

Aj keď môžete použiť vyššie uvedenú metódu na zarovnanie zošitov a manuálne prechádzanie údajov po riadkoch, nie je to dobrý spôsob v prípade, že máte veľa údajov.

Ručné vykonávanie tejto úrovne porovnávania môže viesť k mnohým chybám.

Takže namiesto toho, aby ste to robili ručne, môžete využiť silu podmieneného formátovania na rýchle zvýraznenie rozdielov v dvoch listoch Excelu.

Táto metóda je skutočne užitočná, ak máte dve verzie na dvoch rôznych listoch a chcete rýchlo skontrolovať, čo sa zmenilo.

Všimnite si, že vy NEMôŽE porovnajte dva listy v rôznych pracovných zošitoch.

Pretože podmienené formátovanie nemôže odkazovať na externý súbor programu Excel, listy, ktoré potrebujete na porovnanie, musia byť v tom istom zošite programu Excel. V prípade, že to tak nie je, môžete skopírovať list z druhého súboru do aktívneho zošita a potom vykonať porovnanie.

V tomto prípade predpokladajme, že máte množinu údajov, ako je uvedené nižšie, na dva mesiace (január a február) v dvoch rôznych hárkoch a chcete rýchlo porovnať údaje v týchto dvoch hárkoch a skontrolovať, či sa ceny týchto položiek zmenili alebo nie.

Nasledujú kroky, ako to urobiť:

1. Vyberte údaje v hárku, kde chcete zvýrazniť zmeny. Keďže chcem skontrolovať, ako sa ceny zmenili od januára do februára, vybral som údaje do hárku Február.
2. Kliknite na kartu Domov
3. V skupine Štýly kliknite na „Podmienené formátovanie“
4. V zobrazených možnostiach kliknite na položku „Nové pravidlo“
5. V dialógovom okne „Nové pravidlo formátovania“ kliknite na „Pomocou vzorca určiť, ktoré bunky sa majú formátovať“
6. Do poľa vzorca zadajte nasledujúci vzorec: = B2Jan! B2
7. Kliknite na tlačidlo Formátovať
8. V zobrazenom dialógovom okne Formát buniek kliknite na kartu „Vyplniť“ a vyberte farbu, v ktorej chcete zvýrazniť nesúlad údajov.
9. Kliknite na tlačidlo OK
10. Kliknite na tlačidlo OK

Vyššie uvedené kroky by okamžite zvýraznili všetky zmeny v množine údajov v oboch hárkoch.

Ako to funguje?

Podmienené formátovanie zvýrazní bunku, keď daný vzorec pre túto bunku vráti PRAVDU. V tomto prípade porovnávame každú bunku v jednom hárku so zodpovedajúcou bunkou v druhom hárku (pomocou vzorca, ktoré sa vo vzorci nerovná).

Keď podmienené formátovanie nájde nejaký rozdiel v dátach, zvýrazní to, že v hárku Jan (ten, v ktorom sme použili podmienené formátovanie).

Všimnite si toho, že som v tomto prípade použil relatívnu referenciu (A1 a nie $ A $ 1 alebo $ A1 alebo A $ 1).

Pri použití tejto metódy na porovnanie dvoch hárkov v programe Excel si zapamätajte nasledujúce;

• Táto metóda je dobrá na rýchle rozpoznanie rozdielov, ale nemôžete ju používať priebežne. Ak napríklad zadám nový riadok do ktorejkoľvek z množín údajov (alebo riadok odstránim), poskytlo by mi to nesprávne výsledky. Hneď ako vložím/odstránim riadok, všetky nasledujúce riadky sa budú považovať za odlišné a podľa toho sa zvýraznia.
• Môžete porovnať iba dva listy v jednom súbore programu Excel
• Porovnať môžete iba hodnotu (nie rozdiel vo vzorci alebo formátovaní).

Porovnajte dva súbory/listy programu Excel a získajte rozdiely pomocou vzorca

Ak vás zaujíma iba rýchle porovnanie a identifikácia rozdielov medzi dvoma listami, pomocou vzorca môžete načítať iba tie hodnoty, ktoré sú odlišné.

Na túto metódu budete potrebovať samostatný pracovný hárok, kde môžete rozdiely načítať.

Táto metóda by fungovala, ak chcete porovnať dva samostatné zošity alebo pracovné hárky programu Excel v jednom zošite.

Ukážem vám príklad, kde porovnávam dva súbory údajov v dvoch listoch (v tom istom zošite).

Predpokladajme, že máte množinu údajov, ako je znázornené nižšie, v hárku s názvom Jan (a podobné údaje v hárku s názvom Feb) a chcete vedieť, aké hodnoty sa líšia.

Ak chcete porovnať tieto dva hárky, najskôr vložte nový pracovný hárok (nazveme ho „Rozdiel“).

Do bunky A1 zadajte nasledujúci vzorec:

= IF (Jan! A1Feb! A1, "Jan Value:" & Jan! A1 & CHAR (10) & "Feb Value:" & Feb! A1, "")

Skopírujte a prilepte tento vzorec pre rozsah tak, aby pokrýval celý súbor údajov v oboch listoch. Keďže mám malý súbor údajov, skopírujem a prilepím tento vzorec iba v rozsahu A1: B10.

Vyššie uvedený vzorec používa na kontrolu rozdielov podmienku IF. V prípade, že v hodnotách nie je žiadny rozdiel, vráti sa prázdne a v prípade rozdielu vráti hodnoty z oboch hárkov do oddelených riadkov v tej istej bunke.

Dobré na tejto metóde je, že vám poskytne iba rozdiely a presne ukáže, v čom je rozdiel. V tomto príklade ľahko vidím, že cena v bunke B4 a B8 je odlišná (rovnako ako presné hodnoty v týchto bunkách).

Porovnajte dva súbory/listy programu Excel a získajte rozdiely pomocou jazyka VBA

Ak potrebujete pomerne často porovnávať súbory alebo hárky programu Excel, je vhodné mať k dispozícii pripravený kód VBA pre makro programu Excel a používať ho vždy, keď potrebujete vykonať porovnanie.

Makro môžete tiež pridať na panel s nástrojmi Rýchly prístup, aby ste k nim mali prístup jediným tlačidlom a okamžite vedeli, aké bunky sa líšia v rôznych súboroch/listoch.

Predpokladajme, že máte dva hárky v januári a vo februári a chcete porovnať a zvýrazniť rozdiely v hárku Jan, môžete použiť nasledujúci kód VBA:

Sub CompareSheets () Dim rngCell as Range for each rngCell in Worksheets ("Jan"). UsedRange If Not rngCell = Worksheets ("Feb"). Cells (rngCell.Row, rngCell.Column) Then rngCell.Interior.Color = vbYellow End Ak Next rngCell End Sub

Vyššie uvedený kód používa slučku For Next na prechod každou bunkou v hárku Jan (celý použitý rozsah) a porovná ju so zodpovedajúcou bunkou v hárku Feb. V prípade, že nájde rozdiel (ktorý sa kontroluje pomocou príkazu If-Then), zvýrazní tieto bunky žltou farbou.

Tento kód môžete použiť v bežnom module v editore VB.

A ak to potrebujete robiť často, je lepšie tento kód uložiť do zošita Osobné makro a potom ho pridať na panel s nástrojmi Rýchly prístup. Takýmto spôsobom budete môcť vykonať toto porovnanie kliknutím na tlačidlo.

Tu sú kroky na získanie osobného zošita makier v programe Excel (v predvolenom nastavení nie je k dispozícii, takže ho musíte povoliť).

Tu sú kroky na uloženie tohto kódu do osobného zošita makier.

A tu nájdete kroky na pridanie tohto kódu makra do QAT.

Použitie nástroja tretej strany - porovnávača XL

Ďalším rýchlym spôsobom, ako porovnať dva súbory programu Excel a skontrolovať zhody a rozdiely, je použiť bezplatný nástroj tretej strany, ako je napríklad XL Comparator.

Jedná sa o webový nástroj, do ktorého môžete nahrať dva súbory programu Excel a vytvorí sa porovnávací súbor, ktorý bude obsahovať údaje, ktoré sú spoločné (alebo rôzne údaje podľa toho, akú možnosť ste vybrali.

Predpokladajme, že máte dva súbory, ktoré obsahujú množiny údajov o zákazníkoch (napríklad meno a e -mailovú adresu), a chcete rýchlo skontrolovať, kde sa nachádzajú zákazníci, súbor 1 a nie súbor 2.

Nasleduje porovnanie dvoch súborov programu Excel a vytvorenie porovnávacej správy:

1. Otvorte https://www.xlcomparator.net/
2. Na výber dvoch súborov použite možnosť Vybrať súbor (maximálna veľkosť každého súboru môže byť 5 MB)
3. Kliknite na tlačidlo Ďalej.
4. V oboch týchto súboroch vyberte spoločný stĺpec. Nástroj bude používať tento spoločný stĺpec na hľadanie zhody a rozdielov
5. Vyberte jednu zo štyroch možností, či už chcete získať zodpovedajúce údaje alebo iné údaje (na základe súboru 1 alebo súboru 2)
6. Kliknite na Ďalej
7. Stiahnite si porovnávací súbor, ktorý bude obsahovať údaje (podľa toho, akú možnosť ste vybrali v kroku 5)

Nasleduje video, ktoré ukazuje, ako nástroj XL Comparator funguje.

Jednou z obáv, ktoré môžete mať pri používaní nástroja tretej strany na porovnávanie súborov programu Excel, je ochrana osobných údajov. Ak máte dôverné údaje a súkromie je pre nich skutočne dôležité, je lepšie použiť iné metódy uvedené vyššie. Web XL Comparator uvádza, že odstránia všetky súbory po 1 hodine vykonania porovnania.

Toto sú niektoré z metód, ktoré môžete použiť na porovnanie dvoch rôznych súborov programu Excel (alebo pracovných hárkov v tom istom súbore programu Excel). Dúfam, že ste našli tento návod na Excel užitočný.