Obsah
Máme dve tabuľky (napríklad stará a nová verzia cenníka), ktoré potrebujeme porovnať a rýchlo nájsť rozdiely:
Hneď je jasné, že do nového cenníka niečo pribudlo (dátle, cesnak...), niečo zmizlo (černice, maliny...), pri niektorých tovaroch sa zmenili ceny (figy, melóny...). Všetky tieto zmeny musíte rýchlo nájsť a zobraziť.
Pre akúkoľvek úlohu v Exceli existuje takmer vždy viac ako jedno riešenie (zvyčajne 4-5). Pre náš problém je možné použiť mnoho rôznych prístupov:
- funkcie VPR (VLOOKUP) — vyhľadajte názvy produktov z nového cenníka v starom a zobrazte starú cenu vedľa nového a potom zachyťte rozdiely
- zlúčiť dva zoznamy do jedného a potom na jeho základe zostaviť kontingenčnú tabuľku, kde budú rozdiely jasne viditeľné
- použite doplnok Power Query pre Excel
Zoberme si ich všetky po poriadku.
Metóda 1. Porovnanie tabuliek pomocou funkcie VLOOKUP
Ak túto úžasnú funkciu úplne nepoznáte, najprv sa pozrite sem a prečítajte si alebo si pozrite videonávod – ušetrite si pár rokov života.
Táto funkcia sa zvyčajne používa na ťahanie údajov z jednej tabuľky do druhej priraďovaním nejakého bežného parametra. V tomto prípade ho použijeme na presunutie starých cien do novej ceny:
Produkty, pri ktorých sa ukázala chyba #N/A, nie sú v starom zozname, tj boli pridané. Zmeny cien sú tiež jasne viditeľné.
Pros táto metóda: jednoduchá a jasná, „klasika žánru“, ako sa hovorí. Funguje v akejkoľvek verzii Excelu.
Zápory je tam tiež. Ak chcete vyhľadať produkty pridané do nového cenníka, budete musieť urobiť rovnaký postup v opačnom smere, teda vytiahnuť nové ceny na starú cenu pomocou funkcie VLOOKUP. Ak sa zajtra zmení veľkosť tabuliek, vzorce sa budú musieť upraviť. Nuž a na naozaj veľkých stoloch (> 100 tisíc riadkov) sa všetko toto šťastie slušne spomalí.
Metóda 2: Porovnanie tabuliek pomocou kontingenčnej tabuľky
Skopírujme naše tabuľky jednu pod druhú a pridajte stĺpec s názvom cenníka, aby ste neskôr pochopili, z ktorého zoznamu ktorý riadok:
Teraz na základe vytvorenej tabuľky vytvoríme súhrn cez Vložiť – kontingenčná tabuľka (Vložiť – kontingenčná tabuľka). Hodíme pole Produkt do oblasti čiar, poľa Cena na plochu stĺpca a pole Цena do rozsahu:
Ako vidíte, kontingenčná tabuľka automaticky vygeneruje všeobecný zoznam všetkých produktov zo starého a nového cenníka (žiadne opakovanie!) a zoradí produkty podľa abecedy. Jasne vidíte pridané produkty (nemajú starú cenu), odstránené produkty (nemajú novú cenu) a prípadné zmeny cien.
Celkové súčty v takejto tabuľke nedávajú zmysel a možno ich na karte deaktivovať Konštruktor – Celkové súčty – Zakázať pre riadky a stĺpce (Dizajn – celkové súčty).
Ak sa zmenia ceny (nie však množstvo tovaru!), stačí jednoducho aktualizovať vytvorený sumár kliknutím pravým tlačidlom myši – osviežiť.
Pros: Tento prístup je pri veľkých tabuľkách rádovo rýchlejší ako VLOOKUP.
Zápory: treba ručne skopírovať údaje pod seba a pridať stĺpec s názvom cenníka. Ak sa zmenia veľkosti stolov, musíte všetko urobiť znova.
Metóda 3: Porovnanie tabuliek s Power Query
Power Query je bezplatný doplnok pre Microsoft Excel, ktorý vám umožňuje načítať údaje do Excelu z takmer akéhokoľvek zdroja a potom tieto údaje ľubovoľným spôsobom transformovať. V Exceli 2016 je tento doplnok už predvolene zabudovaný na karte dátum (údaje), a pre Excel 2010-2013 si ho musíte stiahnuť samostatne z webovej stránky Microsoftu a nainštalovať – získať novú kartu mocenský dotaz.
Pred načítaním našich cenníkov do Power Query je potrebné ich najskôr previesť na inteligentné tabuľky. Ak to chcete urobiť, vyberte rozsah s údajmi a stlačte kombináciu na klávesnici ctrl+T alebo vyberte kartu na páse s nástrojmi Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku). Názvy vytvorených tabuliek je možné opraviť na záložke staviteľ (Nechám štandard Tabuľka 1 и Tabuľka 2, ktoré sa získavajú štandardne).
Načítajte starú cenu v Power Query pomocou tlačidla Z tabuľky/rozsahu (Z tabuľky/rozsahu) z karty dátum (Dátum) alebo z karty mocenský dotaz (v závislosti od verzie Excelu). Po načítaní sa príkazom vrátime späť do Excelu z Power Query Zavrieť a načítať – Zavrieť a naložiť... (Zavrieť a načítať – Zavrieť a načítať do…):
… a v zobrazenom okne vyberte Stačí vytvoriť spojenie (Len pripojenie).
To isté zopakujte s novým cenníkom.
Teraz vytvoríme tretí dotaz, ktorý bude kombinovať a porovnávať údaje z predchádzajúcich dvoch. Ak to chcete urobiť, vyberte v Exceli na karte Údaje – Získať údaje – Kombinovať požiadavky – Kombinovať (Údaje – Získať údaje – Zlúčiť dopyty – Zlúčiť) alebo stlačte tlačidlo Kombinovať (Zlúčiť) pútko mocenský dotaz.
V okne spájania vyberte v rozbaľovacích zoznamoch naše tabuľky, vyberte stĺpce s názvami tovaru v nich a v dolnej časti nastavte spôsob spojenia – Kompletné externé (Úplný vonkajší):
Po kliknutí na OK mala by sa objaviť tabuľka troch stĺpcov, kde v treťom stĺpci musíte rozbaliť obsah vnorených tabuliek pomocou dvojitej šípky v hlavičke:
Výsledkom je zlúčenie údajov z oboch tabuliek:
Názvy stĺpcov v hlavičke je samozrejme lepšie premenovať dvojitým kliknutím na zrozumiteľnejšie:
A teraz to najzaujímavejšie. Prejdite na kartu Pridať stĺpec (Pridať stĺpec) a kliknite na tlačidlo Podmienený stĺpec (Podmienený stĺpec). A potom v okne, ktoré sa otvorí, zadajte niekoľko testovacích podmienok s príslušnými výstupnými hodnotami:
Zostáva kliknúť ďalej OK a nahrajte výsledný prehľad do Excelu pomocou rovnakého tlačidla zavrieť a stiahnuť (Zavrieť a načítať) pútko Domov (Domov):
Krásu.
Navyše, ak sa v budúcnosti vyskytnú nejaké zmeny v cenníkoch (pridávanie alebo vymazanie riadkov, zmena cien a pod.), potom bude stačiť len aktualizovať naše požiadavky klávesovou skratkou ctrl+ostatné+F5 alebo tlačidlom Obnoviť všetko (Obnoviť všetko) pútko dátum (Dátum).
Pros: Možno najkrajší a najpohodlnejší spôsob zo všetkých. Funguje inteligentne s veľkými stolmi. Nevyžaduje manuálne úpravy pri zmene veľkosti tabuliek.
Zápory: Vyžaduje sa inštalácia doplnku Power Query (v Exceli 2010 – 2013) alebo Excel 2016. Názvy stĺpcov v zdrojových údajoch sa nesmú meniť, inak sa zobrazí chyba „Stĺpec taký a taký nebol nájdený!“ pri pokuse o aktualizáciu dotazu.
- Ako zhromažďovať údaje zo všetkých súborov programu Excel v danom priečinku pomocou Power Query
- Ako nájsť zhody medzi dvoma zoznamami v Exceli
- Zlúčenie dvoch zoznamov bez duplikátov