Kimutatástábla több adattartományban

A probléma megfogalmazása

A pivot táblázatok az Excel egyik legcsodálatosabb eszközei. De eddig sajnos az Excel egyik verziója sem tud olyan egyszerű és szükséges dolgot menet közben megtenni, mint összefoglalót készíteni több kiindulási adattartományhoz, amelyek például különböző lapokon vagy különböző táblázatokban találhatók:

Mielőtt elkezdenénk, tisztázzunk néhány pontot. A priori úgy gondolom, hogy az alábbi feltételek teljesülnek adatainkban:

• A táblákban tetszőleges számú sor állhat bármilyen adattal, de a fejlécnek azonosnak kell lennie.
• A forrástáblázatot tartalmazó lapokon ne legyenek extra adatok. Egy lap – egy asztal. A vezérléshez azt tanácsolom, hogy használjon billentyűkódot Ctrl+vég, amely a munkalap utoljára használt cellájába helyezi át. Ideális esetben ez az utolsó cella az adattáblázatban. Ha amikor rákattint Ctrl+vég minden üres cella a táblázat jobb oldalán vagy alatta ki van jelölve – törölje ezeket az üres oszlopokat a jobb oldalon vagy a táblázat alatti sorokat a táblázat után, és mentse a fájlt.

1. módszer: Hozzon létre táblázatokat egy kimutatáshoz a Power Query segítségével

Az Excel 2010-es verziójától kezdve létezik egy ingyenes Power Query-bővítmény, amely bármilyen adatot összegyűjthet és átalakíthat, majd forrásként megadhatja azokat a kimutatástábla felépítéséhez. Problémánk megoldása ennek a bővítménynek a segítségével egyáltalán nem nehéz.

Először is hozzunk létre egy új üres fájlt az Excelben – ebben megy végbe az assembly, majd egy pivot tábla jön létre benne.

Aztán a lapon dátum (ha Excel 2016 vagy újabb verziója van), vagy a lapon Teljesítmény lekérdezés (ha Excel 2010–2013-as verziója van) válassza ki a parancsot Lekérdezés létrehozása – Fájlból – Excel (Adatok lekérése – Fájlból – Excel) és adja meg a forrásfájlt az összegyűjtendő táblákkal:

A megjelenő ablakban válasszon ki egy tetszőleges lapot (nem mindegy, hogy melyiket), és nyomja meg az alábbi gombot Változtat (Edit):

A Power Query Query Editor ablakának meg kell nyílnia az Excel tetején. Az ablak jobb oldalán a panelen Paraméterek kérése törölje az összes automatikusan létrehozott lépést, kivéve az elsőt – forrás (Forrás):

Most egy általános listát látunk az összes lapról. Ha az adatlapokon kívül más oldallapok is találhatók a fájlban, akkor ebben a lépésben az a feladatunk, hogy csak azokat a lapokat válasszuk ki, amelyekről információkat kell betölteni, a többit kizárva a táblázat fejlécében található szűrő segítségével:

Törölje az összes oszlopot, kivéve az oszlopot dátumjobb gombbal kattintva egy oszlopfejlécre és kiválasztva Törölje a többi oszlopot (Eltávolítás egyéb oszlopok):

Ezután az oszlop tetején lévő dupla nyílra kattintva bővítheti az összegyűjtött táblázatok tartalmát (jelölőnégyzet Használja az eredeti oszlop nevét előtagként kikapcsolhatod):

Ha mindent helyesen csinált, akkor ezen a ponton látnia kell az összes táblázat tartalmát egymás alatt:

Marad az első sor felemelése a táblázat fejlécéhez a gombbal Használja az első sort fejlécként (Használja az első sort fejlécként) lap Kezdőlap (Itthon) és távolítsa el az ismétlődő táblázatfejléceket az adatokból egy szűrő segítségével:

Mentse el az összes műveletet a paranccsal 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…) lap Kezdőlap (Itthon), és a megnyíló ablakban válassza ki a lehetőséget Csak csatlakozás (Csak kapcsolat):

Minden. Már csak egy összefoglalót kell készíteni. Ehhez lépjen a lapra Beszúrás – PivotTable (Beszúrás – Pivot Table), válassza ki a lehetőséget Használjon külső adatforrást (Külső adatforrás használata)majd a gombra kattintva Válassza ki a kapcsolat, kérésünk. A pivot további létrehozása és konfigurálása teljesen szabványos módon történik a szükséges mezők behúzásával a sorok, oszlopok és értékek területére:

Ha a forrásadatok a jövőben változnak, vagy még néhány üzletlap kerül hozzáadásra, akkor elegendő frissíteni a lekérdezést és az összesítésünket a paranccsal Az összes frissítése lap dátum (Adatok – Összes frissítése).

2. módszer. Egyesítjük a táblákat az UNION SQL paranccsal egy makróban

Problémánk másik megoldását ez a makró jelenti, amely a parancs segítségével adatkészletet (gyorsítótárat) hoz létre a pivot táblához. EGYSÉG SQL lekérdezési nyelv. Ez a parancs a tömbben megadott összes táblát egyesíti Lapnevek a könyv lapjait egyetlen adattáblázatba. Ez azt jelenti, hogy a különböző lapok közötti tartományok fizikai másolása és beillesztése helyett ugyanezt tesszük a számítógép RAM-jában. Ezután a makró hozzáad egy új lapot a megadott névvel (változó ResultSheetName) és az összegyűjtött gyorsítótár alapján teljes értékű (!) összesítést készít.

Makró használatához használja a Visual Basic gombot a lapon fejlesztő (Fejlesztő) vagy billentyűparancsot más+F11. Ezután a menün keresztül beszúrunk egy új üres modult Beszúrás – Modul és másold oda a következő kódot:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Változat 'munkalap neve, ahol az eredményül kapott Referencialap meg lesz jelenítve"Pivot" =S nevek forrástáblákkal SheetsNames = Array("Alfa", "Béta", "Gamma", "Delta") 'gyorsítótárat hozunk létre a táblázatokhoz a SheetsNames lapjaiból az ActiveWorkbook ReDim segítségével arSQL(1 To (UBound(SheetsNames) + 1)) ) For i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Következő i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Vége A 'vel hozza létre újra a lapot, hogy megjelenítse az eredményül kapott pivot táblát Hiba esetén Folytatás a következő alkalmazásban.DisplayAlerts = False Worksheets(ResultSheetName).Set törlése wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'a generált gyorsítótár-összefoglaló megjelenítése ezen a lapon Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Semmi A wsPivot objRS = Semmi A wsPivot objPivotRt3S"CreatablePivotst. objPivotCache = Semmi Tartomány("A3"). Válassza az End With End Sub lehetőséget    

A kész makró ezután egy billentyűkóddal futtatható más+F8 vagy a Makrók gombot a lapon fejlesztő (Fejlesztő – Makrók).

Ennek a megközelítésnek a hátrányai:

• Az adatok nem frissülnek, mert a gyorsítótárnak nincs kapcsolata a forrástáblákkal. Ha módosítja a forrásadatokat, újra kell futtatnia a makrót, és újra össze kell állítania az összegzést.
• A lapok számának módosításakor a makrókódot (tömb Lapnevek).
  

De végül egy igazi teljes értékű pivot táblát kapunk, amely több tartományra épül, különböző lapokból:

Voálá!

Műszaki megjegyzés: Ha a makró futtatásakor olyan hibaüzenetet kap, mint a „szolgáltató nincs regisztrálva”, akkor valószínűleg az Excel 64 bites verziója van, vagy az Office egy hiányos verziója van telepítve (nincs hozzáférés). A helyzet megoldásához cserélje ki a töredéket a makrókódban:

	 Szolgáltató=Microsoft.Jet.OLEDB.4.0;  

hogy:

	Szolgáltató=Microsoft.ACE.OLEDB.12.0;  

És töltse le és telepítse az ingyenes adatfeldolgozó motort az Accessből a Microsoft webhelyéről – Microsoft Access Database Engine 2010 újraterjeszthető

3. módszer: Konszolidálja a kimutatás varázslót az Excel régi verzióiból

Ez a módszer kissé elavult, de még mindig érdemes megemlíteni. Formálisan 2003-ig minden verzióban a PivotTable varázslóban lehetőség volt „több konszolidációs tartományhoz kiépíteni egy pivotot”. Azonban egy ilyen módon felépített jelentés sajnos csak szánalmas látszata lesz egy igazi teljes értékű összefoglalónak, és nem támogatja a hagyományos pivot táblák sok „chipjét”:

Egy ilyen pivotban a mezőlistában nincsenek oszlopfejlécek, nincs rugalmas szerkezetbeállítás, korlátozott a felhasznált függvénykészlet, és általában mindez nem nagyon hasonlít egy pivot táblához. Talán ez az oka annak, hogy 2007-től a Microsoft eltávolította ezt a funkciót a standard párbeszédablakból a pivot tábla jelentések létrehozásakor. Most ez a funkció csak egyéni gombon keresztül érhető el PivotTable varázsló(Pivot Table varázsló), amely kívánt esetben hozzáadható a Gyorselérési eszköztárhoz ezen keresztül Fájl – Beállítások – Gyorselérési eszköztár testreszabása – Minden parancs (Fájl — Beállítások — Gyorselérési eszköztár testreszabása — Minden parancs):

A hozzáadott gombra kattintás után a varázsló első lépésében ki kell választania a megfelelő opciót:

Ezután a következő ablakban válassza ki az egyes tartományokat egymás után, és adja hozzá az általános listához:

De ismétlem, ez nem egy teljes értékű összefoglaló, ezért ne várj tőle túl sokat. Ezt a lehetőséget csak nagyon egyszerű esetekben tudom ajánlani.

• Jelentések készítése kimutatások segítségével
• Számítások beállítása a kimutatásokban
• Mik azok a makrók, hogyan kell használni őket, hova kell másolni a VBA kódot stb.
• Adatgyűjtés több lapról egyre (PLEX kiegészítő)