Munkalapok összevonása, összemásolása - 1. rész: Beépített funkciók

2017. október 07. - Office Guru

Nagyon gyakran felmerülő igény az Excel használata során különböző munkalapok összemásolása egy munkalapra, akár csak az átláthatóság, akár konszolidált adatkezelés céljából - akárhogy is, az esetek többségében a sima CTRL+C és CTRL+V megoldáshoz fordulnak a felhasználók. Pedig vannak más megoldások is, ezek közül mutatok be most többet is. Adott tehát a következő kis tábla az első sheeten, és egy hasonló van a másodikon is, ezeket szeretnénk összesíteni:

merge1.JPGMár egyszerűen csak a Ribbon füleinek böngészése alapján is szemet szúrhat a Data fül alatt megbúvó Consolidate funkció, amely már a leírása alapján is valamilyen művelet alapján tömbösít, konszolidál adatokat több munkalapról:

merge2.JPGDe ha rákattintunk a funkcióra és felugrik a műveleti ablak, már látjuk is a gyenge pontját ennek az opciónak - sima összemásolást nem tudunk vele elvégezni, csak műveletek alapján tudunk konszolidálni, ahogy a funkció neve is utal rá:

merge4.JPGAhogy látható a fenti képen is, egy sima összegzést választottam a Function legördülő menüjéből, majd megadtam a konszolidálandó tartományokat, mindkét sheeten kizárólag a számokat tartalmazó oszlopokat, majd bepipáltam a Top row label checkboxot, hogy legyen fejléce az új táblámnak:

merge5.JPGLátható, összeadta a sorokban szereplő számokat, ahogy a funkciónál ki is választottuk az összegzést. Értelemszerűen persze ha az egész táblát adjuk be referenciaként mindkét sheetről, és beadjuk a Left Column checkbox pipáját is, akkor más lesz az eredmény:

merge51.JPG

merge52.JPGDe ez csak azért néz most ilyen jól ki, mert nincs két egyforma városnevünk, ami alapján összegezhetne a konszolidációs funkció. Szóval ha tényleg szeretnénk adatokat konszolidálni, akkor használható a Consolidate, ha szimplán csak összemásolást szeretnénk, akkor másfelé kell kutakodnunk. De ne gondoljunk kapásból VBA-kódra, az Excel még mindig rejt magában beépített lehetőségeket a fenti célra. Hozzunk létre egy új sheetet, mondjuk Összes néven:

merge6.JPGEzután ugyanezen a sheeten maradva a Data fül alatt lévő From Other Sources legördülő menüből válasszuk ki a From Microsoft Query opciót:

merge7.JPGMiután elindult a Microsoft Query, a felugró adatforrás ablakban válasszuk az Excel Files* menüpontot:

merge8.JPGEzután válasszuk ki azt a munkafüzetet, amelyben a két (vagy több) összemásolandó sheet található:

merge9.JPG

merge10.JPGAz ezután felugró Add Tables ablakot Close gombra kattintással zárjuk be:

merge11.JPGEzután válasszuk a fenti ikonsorról az SQL feliratú kis gombot, ami után látható válik egy SQL statement ablak:

merge12.JPGEzután jön maga a statement, ami a következő lesz:

SELECT *

FROM "C:\book1.xlsx"."Sheet1$"

Union

SELECT *

FROM "C:\book1.xlsx2"."Sheet2$"

merge13.JPGTehát válasszuk ki (SELECT) az első sheet minden adatát majd egyesítsük (Union) a második sheet összes adatával.

Ha erre most okét nyomunk, már látni is fogjuk az eredményünket Microsoft Queryben:

merge14.JPGMár nem maradt hátra más, mint a File menüben taláható Return Data to Microsoft Excel funkcióval visszaküldeni az eredményt az Összes névre hallgató sheetre:

merge15.JPGÉs íme:

merge16.JPGA következő posztban további összemásolási lehetőségeket fogunk még majd áttekinteni, remélhetőleg azonban a fenti is használható alternatívát jelent egyes Olvasók számára.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr7712936869

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

toportyánféreg 2017.10.07. 19:38:57

nem UNION ALL kellene, ha már SQL?
így ha lesz két megegyező város, szám kombináció, az csak egy sor lesz

fordulo_bogyo 2017.10.08. 09:13:56

Bocs, de egy Ctr-C Ctr-V nem egyszerubb ennel?
Idoben is leutesek szamaban is....

Vagy csak en nem latom a nagyszeruseget ennek a funkcionak?

Office Guru 2017.10.08. 09:26:27

@toportyánféreg: Igen, így van, de arra simán jó a Consolidate is. A cél a sima másolás, nem a konszolidálás - és első körben meg akartam nézni, mit lehet makró nélkül.

Office Guru 2017.10.08. 09:27:33

@fordulo_bogyo: Ennél a példánál természetesen így van. Ettől függetlenül akartam megnézni a sima CTRL+C és CTRL+V opción kívüli lehetőségeket...

toportyánféreg 2017.10.08. 11:56:03

@Office Guru: pont így "konszolidál", mert a teljesen azonos sorok csak egyszer fognak szerepelni, holott ha mindkét táblában benne van, akkor 2 sorként látszódna másolás után?!

Office Guru 2017.10.08. 20:09:32

@toportyánféreg: Amúgy igazad lenne, csak nálam nem így működik... :o)