Ako porovnať dve tabuľky v Exceli a nájsť zmenené riadky

Dodávateľ pošle nový cenník, z účtovného programu vyexportujete zoznam faktúr druhýkrát alebo dostanete opravenú verziu tabuľky. Otázka je vždy rovnaká: čo sa zmenilo? Pozerať dve tabuľky vedľa seba funguje pri desiatich riadkoch. Pri stovkách riadkov zmenu ľahko prehliadnete.

V návode porovnáme októbrový a novembrový cenník. Každý produkt má kód, názov, cenu a počet kusov na sklade. Riadky môžu byť v každom súbore v inom poradí.

Kód	Názov	Cena	Sklad
00101	Káva zrnková 1 kg	18,90	42
00102	Čaj zelený 100 g	4,50	120
00105	Olivový olej 0,75 l	11,40	64
…	…	…	…

Skôr než začnete: stĺpec, podľa ktorého sa riadky spárujú

Excel musí vedieť, ktorý riadok v novej tabuľke patrí ku ktorému v starej. Na to potrebujete stĺpec, v ktorom má každý riadok inú hodnotu a ktorý sa medzi verziami nemení – takzvaný kľúč. Hodí sa kód produktu, číslo objednávky, číslo faktúry alebo IČO. Názov produktu sa nehodí: dva produkty sa môžu volať rovnako a názov sa môže zmeniť.

1. Skopírujte obe tabuľky do jedného zošita, každú na samostatný hárok.
2. Hárky premenujte na Oktober a November (dvakrát kliknite na názov hárka dole). Bez dĺžňov a medzier sa vzorce píšu ľahšie.
3. Skontrolujte, či je kľúč v oboch hárkoch v rovnakom stĺpci. V príklade je kód v stĺpci A a cena v stĺpci C.

Ktoré riadky pribudli a ktoré zmizli (COUNTIF)

Funkcia COUNTIF spočíta, koľkokrát sa hodnota nachádza v inom stĺpci. Ak sa kód z novembra v októbri nenachádza ani raz, produkt pribudol.

1. Na hárku November kliknite do prvej voľnej bunky vedľa tabuľky, v príklade E2.

2. Napíšte vzorec a stlačte Enter:

   =IF(COUNTIF(Oktober!A:A;A2)=0;"pribudol";"")

3. Bunku skopírujte do všetkých riadkov tabuľky: dvakrát kliknite na malý štvorček v pravom dolnom rohu bunky.

4. Rovnako zistíte, čo zmizlo. Na hárku Oktober napíšte do E2:

   =IF(COUNTIF(November!A:A;A2)=0;"zmizol";"")

5. Aj túto bunku skopírujte nadol. Riadky so slovom „zmizol“ označujú produkty, ktoré v novembrovom cenníku už nie sú.

Slovenský Excel používa medzi časťami vzorca bodkočiarku (;). Ak vzorec kopírujete z anglického návodu s čiarkami, Excel ho neprijme.

Ktoré hodnoty sa zmenili (XLOOKUP)

Funkcia XLOOKUP nájde podľa kódu riadok v druhom hárku a vráti z neho hodnotu, napríklad starú cenu. Potom ju porovnáte s novou.

1. Na hárku November napíšte do F2 vzorec, ktorý vyhľadá októbrovú cenu:

   =XLOOKUP(A2;Oktober!A:A;Oktober!C:C;"–")

   Pri produktoch, ktoré v októbri neboli, sa zobrazí pomlčka.

2. Do G2 napíšte vzorec, ktorý ceny porovná:

   =IF(F2="–";"";IF(F2<>C2;"zmena ceny";""))

3. Obe bunky (F2 a G2) označte a skopírujte nadol dvojklikom na štvorček v rohu.

4. Ak chcete vidieť len zmenené riadky, kliknite do tabuľky, na karte Údaje zvoľte Filter a v stĺpci G nechajte len „zmena ceny“.

5. Pre každý ďalší stĺpec, ktorý chcete porovnať (napríklad Sklad), pridajte ďalšiu dvojicu vzorcov. Vo vzorci zmeňte C:C a C2 na písmeno daného stĺpca.

Prečo vzorce niekedy ukážu zmenu, ktorá nie je zmenou

Porovnanie vzorcami je presné len vtedy, keď sú obe tabuľky zapísané rovnako. Najčastejšie problémy:

• Nuly na začiatku kódu. Keď dvojklikom otvoríte súbor CSV, Excel z kódu 00101 urobí číslo 101. V druhom súbore ostane 00101 a vzorec ich nespáruje. Kódy preto načítajte ako text (karta Údaje → Z textu alebo CSV) alebo ich v oboch hárkoch upravte rovnako.
• Medzery navyše. „Káva “ s medzerou na konci nie je to isté ako „Káva“. Pomôže funkcia TRIM, ktorá medzery na začiatku a na konci odstráni.
• Číslo verzus text. Cena 1,5 zapísaná ako číslo a 1,50 zapísaná ako text vyzerajú podobne, ale vzorec ich vyhodnotí ako rozdielne.
• Opakujúci sa kľúč. Ak je ten istý kód v tabuľke dvakrát, XLOOKUP vráti len prvý výskyt a zmenu v druhom riadku nenájdete. Opakujúce sa kódy zvýrazníte cez Domov → Podmienené formátovanie → Pravidlá zvýrazňovania buniek → Duplicitné hodnoty.
• Veľkosť. Hárok Excelu má najviac 1 048 576 riadkov. Pri stovkách tisíc riadkov a viacerých porovnávaných stĺpcoch sa zošit so vzorcami výrazne spomalí.

Rýchlejšia cesta: porovnanie bez vzorcov

Ak porovnávate často, máte veľa stĺpcov alebo veľké exporty, je pohodlnejšie použiť nástroj, ktorý spáruje riadky podľa kľúča sám. Ukážeme to na bezplatnom nástroji Calamo. Funguje v prehliadači, ale súbory sa nikam nenahrávajú a porovnanie prebehne priamo vo vašom počítači. Rozhranie je po anglicky. Calamo je projekt autora tohto webu.

1. Obe tabuľky uložte ako CSV: Súbor → Uložiť ako → Prehľadávať, v časti Typ súboru zvoľte CSV (oddelený čiarkami). Bezplatná verzia nástroja číta len súbory CSV.

2. Otvorte calamo.io. Na úvodnej stránke je ukážkový výsledok, ten po vložení vašich súborov zmizne.

3. Kliknite na Old file (A) a vyberte starší súbor (október).

   

4. Kliknite na New file (B) a vyberte novší súbor (november).

5. V riadku Match rows by skontrolujte stĺpec, podľa ktorého sa riadky spárujú. Nástroj navrhne stĺpec, ktorý má v každom riadku inú hodnotu. V príklade je to Kód.

6. Kliknite na Compare.

Výsledok sa zobrazí o chvíľu pod súbormi:

1. Hore vidíte počty: koľko riadkov pribudlo (added), zmizlo (removed), zmenilo sa (changed) a ostalo rovnakých (unchanged). Na poradí riadkov v súboroch nezáleží.

   

2. Záložka Changed (na širokej obrazovke Changed cells) ukazuje každú zmenenú bunku: kód, stĺpec, pôvodnú hodnotu (before) a novú (after).

3. Tlačidlom Download full report (CSV) si stiahnete všetky rozdiely v jednom súbore, ktorý otvoríte v Exceli.

4. Na záložkách Added a Removed sú celé riadky, ktoré pribudli alebo zmizli.

   

Nuly na začiatku kódu nástroj zachová. Medzery na začiatku a na konci ani rozdielny zápis čísel (1.50 a 1,5) pri predvolenom nastavení nepovažuje za zmenu; nastavíte to v časti Treat as equal. Platená aplikácia Calamo Pro pre počítače otvorí aj súbory .xlsx bez prevodu na CSV a súbory väčšie ako niekoľko stoviek MB.

Časté otázky

Dá sa porovnať tabuľka, ktorá nemá žiadny kód ani ID?

Áno, ak niektorá kombinácia stĺpcov riadok jednoznačne určuje, napríklad meno a dátum narodenia. V Exceli si ich spojte do pomocného stĺpca vzorcom =A2&"|"&B2 v oboch hárkoch a ten použite ako kľúč. Ak sa riadky nedajú jednoznačne určiť, môžete ich porovnať len podľa poradia a jeden vložený riadok posunie všetky ďalšie.

Ako porovnať dva stĺpce v rámci jednej tabuľky?

Na porovnanie dvoch stĺpcov vedľa seba stačí vzorec =IF(B2<>C2;"rozdiel";""). Postup z tohto návodu použite pri porovnávaní dvoch verzií tej istej tabuľky, v ktorých môžu byť riadky v inom poradí.

Nemôže to porovnať ChatGPT alebo iná umelá inteligencia?

Pri malej tabuľke áno, ale výsledok si treba skontrolovať. Pri tisícoch riadkov môže jazykový model niektoré riadky vynechať alebo si zmenu domyslieť a ťažko to odhalíte. Súbor pritom posielate na cudzí server. Vzorce v Exceli alebo nástroj, ktorý riadky páruje podľa kľúča, dajú pri tých istých súboroch vždy rovnaký výsledok.

Funguje to aj v LibreOffice alebo v Tabuľkách Google?

Áno. COUNTIF, IF aj VLOOKUP tam fungujú rovnako, XLOOKUP majú Tabuľky Google aj novšie verzie LibreOffice Calc.