tartalom
Két táblázatunk van (például az árlista régi és új verziója), amelyeket össze kell hasonlítanunk, és gyorsan meg kell találnunk a különbségeket:
Azonnal kiderül, hogy valami felkerült az új árlistára (datolya, fokhagyma…), valami eltűnt (szeder, málna…), egyes áruk ára megváltozott (füge, dinnye…). Gyorsan meg kell találnia és megjelenítenie kell ezeket a változásokat.
Az Excelben minden feladatra szinte mindig több megoldás létezik (általában 4-5). Problémánkra számos különböző megközelítés alkalmazható:
- funkció VPR (VLOOKUP) - keresse meg a termékneveket az új árlistából a régiben, és jelenítse meg a régi árat az új mellett, majd fogja meg a különbségeket
- egyesítsen két listát egybe, majd építsen fel egy pivot táblát az alapján, ahol jól láthatóak lesznek a különbségek
- használja a Power Query bővítményt az Excelhez
Vegyük sorra mindet.
1. módszer. Táblázatok összehasonlítása a VLOOKUP függvénnyel
Ha egyáltalán nem ismeri ezt a csodálatos funkciót, akkor először nézzen be ide, és olvassa el vagy nézzen meg egy oktatóvideót róla – spóroljon meg magának pár év életet.
Általában ezt a függvényt arra használják, hogy adatokat húzzanak át egyik táblából a másikba valamilyen általános paraméter egyeztetésével. Ebben az esetben azt használjuk, hogy a régi árakat az új árba toljuk:
Azok a termékek, amelyeknél kiderült a #N/A hiba, nem szerepelnek a régi listán, azaz felkerültek. Az árak változása is jól látható.
Érvek ez a módszer: egyszerű és világos, „a műfaj klasszikusa”, ahogy mondani szokás. Az Excel bármely verziójában működik.
Hátrányok ott is van. Az új árlistára felvett termékek kereséséhez ugyanezt az eljárást ellenkező irányban kell végrehajtania, azaz a VLOOKUP segítségével új árakat kell felemelnie a régi árra. Ha holnap változnak a táblázatok méretei, akkor módosítani kell a képleteket. Nos, és az igazán nagy asztalokon (> 100 ezer sor) ez a boldogság rendesen lelassul.
2. módszer: Táblázatok összehasonlítása pivot segítségével
Másoljuk egymás alá a táblázatainkat, adjunk hozzá egy oszlopot az árlista nevével, hogy később megértsük, melyik listából melyik sorból:
Most a létrehozott táblázat alapján készítünk egy összefoglalót Beszúrás – PivotTable (Beszúrás – Pivot Table). Dobjunk mezőt Termékek vonalak területére, mezőre Ár oszlopterületre és mezőre Цena a tartományba:
Amint látja, a pivot tábla automatikusan generál egy általános listát a régi és új árlisták összes termékéről (nincs ismétlés!), és ábécé sorrendbe rendezi a termékeket. Jól láthatóak a hozzáadott termékek (nem a régi ára), az eltávolított termékek (nem az új ár) és az esetleges árváltozások.
A végösszegeknek egy ilyen táblázatban nincs értelme, és a lapon letilthatók Konstruktor – Végösszeg – Letiltás soroknál és oszlopoknál (Dizájn – Grand Totals).
Ha az árak változnak (de az áruk mennyisége nem!), akkor elegendő a létrehozott összesítőt egyszerűen frissíteni, jobb gombbal kattintson rá – felfrissít.
Érvek: Ez a megközelítés egy nagyságrenddel gyorsabb nagy táblákkal, mint a VLOOKUP.
Hátrányok: kézzel kell átmásolni az adatokat egymás alá, és hozzá kell adni egy oszlopot az árlista nevével. Ha az asztalok mérete megváltozik, akkor mindent elölről kell csinálni.
3. módszer: Táblázatok összehasonlítása a Power Query segítségével
A Power Query a Microsoft Excel ingyenes bővítménye, amely lehetővé teszi, hogy szinte bármilyen forrásból adatokat töltsön be az Excelbe, majd ezeket az adatokat tetszőleges módon átalakítsa. Az Excel 2016-ban ez a bővítmény már alapértelmezés szerint be van építve a lapon dátum (Adat), az Excel 2010-2013 verzióhoz pedig külön le kell töltenie a Microsoft webhelyéről, és telepítenie kell – kap egy új lapot Teljesítmény lekérdezés.
Mielőtt betöltené árlistáinkat a Power Querybe, először intelligens táblákká kell konvertálni őket. Ehhez válassza ki az adatokkal rendelkező tartományt, és nyomja meg a kombinációt a billentyűzeten Ctrl+T vagy válassza ki a fület a szalagon Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása). A létrehozott táblák neve a fülön javítható építész (Hagyom a szabványt Táblázat 1 и Táblázat 2, amelyeket alapértelmezés szerint kapunk meg).
A gombbal töltse be a régi árat a Power Querybe Táblázatból/Tartományból (A táblázatból/Tartományból) a lapról dátum (Dátum) vagy a lapról Teljesítmény lekérdezés (az Excel verziójától függően). A betöltés után a Power Queryből a paranccsal visszatérünk az Excelbe Zárja be és töltse be – Zárja be és töltse be… (Bezárás és betöltés – Bezárás és betöltés…):
… és a megjelenő ablakban válassza ki a lehetőséget Csak hozzon létre egy kapcsolatot (Csak kapcsolat).
Ismételje meg ugyanezt az új árlistával.
Most hozzunk létre egy harmadik lekérdezést, amely egyesíti és összehasonlítja az előző kettő adatait. Ehhez válassza ki az Excel fület Adatok – Adatgyűjtés – Kérelmek egyesítése – Összevonás (Adatok – Adatok lekérése – Lekérdezések összevonása – Egyesítés) vagy nyomja meg a gombot Kombájn (Összeolvad) lap Teljesítmény lekérdezés.
A csatlakozási ablakban a legördülő listákból válassza ki tábláinkat, jelölje ki a bennük lévő áruneveket tartalmazó oszlopokat, majd alul állítsa be az összekapcsolási módot – Komplett külső (Teljes külső):
Miután rákattintott OK meg kell jelennie egy három oszlopból álló táblázatnak, ahol a harmadik oszlopban ki kell bontani a beágyazott táblázatok tartalmát a fejlécben található dupla nyíl segítségével:
Ennek eredményeként mindkét tábla adatainak egyesítését kapjuk:
Természetesen jobb, ha a fejlécben lévő oszlopneveket átnevezzük úgy, hogy az érthetőbbekre duplán kattintunk:
És most a legérdekesebb. Ugrás a lapra Oszlop hozzáadása (Oszlop hozzáadása) és kattintson a gombra Feltételes oszlop (Feltételes oszlop). Ezután a megnyíló ablakban adjon meg több tesztfeltételt a megfelelő kimeneti értékekkel:
Már csak rá kell kattintani OK és ugyanezzel a gombbal töltse fel az eredményül kapott jelentést az Excelbe zárja be és töltse le (Bezárás és betöltés) lap Kezdőlap (Itthon):
Szépség.
Sőt, ha a jövőben bármilyen változás történik az árlistákban (sorok hozzáadása vagy törlése, árak változása stb.), akkor elég lesz, ha frissítjük kéréseinket egy billentyűkóddal. Ctrl+más+F5 vagy gombbal Az összes frissítése (Összes frissítése) lap dátum (Dátum).
Érvek: Talán a legszebb és legkényelmesebb módja az összes közül. Okosan működik nagy asztalokkal. Nem igényel kézi szerkesztést a táblázatok átméretezésekor.
Hátrányok: Telepíteni kell a Power Query bővítményt (Excel 2010–2013-ban) vagy az Excel 2016-ot. A forrásadatokban az oszlopneveket nem szabad megváltoztatni, ellenkező esetben „Ilyen és olyan oszlop nem található!” hibaüzenetet kapunk. amikor megpróbálja frissíteni a lekérdezést.
- Hogyan lehet adatokat gyűjteni egy adott mappában lévő összes Excel-fájlból a Power Query segítségével
- Hogyan találhat egyezést két lista között az Excelben
- Két lista egyesítése ismétlődések nélkül