Nincs Power Pivot? Nem számít. A Power Pivot nagy része beépül az Excel 2013-ba, és még inkább az Excel 2016-ba. Ma Ash tippje egyesíti a táblákat egy pivot-táblában.
Hét héten át minden szerdán bemutatom Ash Sharma egyik kedvenc tippjét. Ash az Excel csapat termékmenedzsere. Csapata forgóasztalokat és sok más jó dolgot hoz Önnek. Ma Ash kedvenc funkciója, hogy a Kapcsolatok és az Adatmodell segítségével több adathalmazhoz csatlakozik.
Tegyük fel, hogy az informatikai részleg megadja az A: D oszlopokban látható adatsort. Vannak mezők az ügyfél és a piac számára. Egyes piacokat régiókba kell egyesítenie. Minden ügyfél egy szektorhoz tartozik. A Régió és az Ágazat nem szerepel az eredeti adatokban, de Ön rendelkezik keresési táblákkal ezen információk megadásához.
Normális esetben az adatokat a VLOOKUP használatával lapítja le a narancssárga és sárga táblákból a kék táblába. De mivel a kulcsmező nem az egyes táblázatok bal oldalán található, akkor vagy át kell váltania az INDEX és a MATCH menüpontokra, vagy át kell rendezni a keresési táblázatokat.
Az Excel 2013-tól kezdődően a keresési táblázatokat ott hagyhatja, ahol vannak, és egyesítheti azokat magában a kimutatótáblázatban.
Ahhoz, hogy ez a technika működjön, mindhárom táblázatot táblának kell formázni. Jelöljön ki egy cellát minden adatsorból, majd válassza a Home, a Formázás táblázatként parancsot, vagy nyomja meg a Ctrl + T billentyűkombinációt. A három tábla neve először Table1, Table2 és Table3 lesz. A szalag Asztali eszközök tervezése lapját használom, és átnevezem az egyes táblákat. Az egyes asztalok színét is megváltoztatom. Ebben a példában a kék táblát Data-nak hívják. A narancssárga tábla a RegionTable. A sárga tábla a SectorTable.
jegyzet
Néhányan azt mondják, hogy olyan geeky neveket kell használnia, mint a Fact, a TblSector és a TblRegion. Ha valakinek ez tetszik, csak ellopja a zsebvédőjét, és közölje vele, hogy inkább az angol hangzású neveket részesíti előnyben.
Tábla átnevezéséhez írjon be egy új nevet a Táblázateszközök tervezése lap bal oldalán található mezőbe. A táblák nevében nem lehet szóköz.
Miután meghatározta a három táblázatot, menjen az Adatok fülre, és kattintson a Kapcsolatok elemre.
A Kapcsolatok kezelése párbeszédpanelen kattintson az Új gombra. A Kapcsolat létrehozása párbeszédpanelen adja meg, hogy az Adattábla Ügyfél mezője kapcsolódjon a SectorTable Ügyfél mezőjéhez. Kattintson az OK gombra.
Adjon meg egy másik új kapcsolatot a Piac mező között az Adatok és a RegionTable mezőkben. Miután meghatározta mindkét kapcsolatot, meglátja őket a Kapcsolatok kezelése párbeszédpanelen.
Gratulálunk: a munkafüzetbe épített egy adatmodellt. Itt az ideje egy forgóasztal elkészítésének.
Jelölje ki azt az üres cellát, ahol meg szeretné jeleníteni a forgatótáblát. Alapértelmezés szerint a PivotTable létrehozása párbeszédpanelen a Munkafüzet adatmodelljének használata lehetőséget választja. Az elforduló tábla helye alapértelmezés szerint a kiválasztott cella lesz. Kattintson az OK gombra.
A Pivot Table Fields lista mindhárom táblázatot felsorolja. A táblázat bal oldalán található háromszög segítségével bővítse ki a táblázat nevét a mezők megjelenítéséhez.
Bontsa ki az Adatok táblázatot. Válassza a Bevétel mezőt. Automatikusan az Értékek területre költözik. Bontsa ki a SectorTable elemet. Válassza a Szektor mezőt. A Sorok területre költözik. Bontsa ki a RegionTable elemet. Húzza a Régió mezőt az Oszlopok területre. Most egy pivot tábla lesz, amely összefoglalja a három táblázat adatait.
jegyzet
Minden könyvben, amelyet ma írtam, más technikát használok a jelentés elkészítéséhez. A három táblázat meghatározása után kiválasztom az A1 cellát és az Insert, Pivot Table elemet. Jelölöm az Adatok hozzáadása az adatmodellhez jelölőnégyzetet. A Pivot Table Fields listában válassza a lista tetején az Összes lehetőséget. Válasszon mezőket a jelentéshez, majd a tények után határozza meg a kapcsolatokat. A fent leírt technika gördülékenyebbnek tűnik, és valójában egy apró előre tervezést jelent. Azok az emberek, akik az Option Explicit-ot használják a VBA-kódjukban, mindenképpen szeretnék ezt a módszert.
Az adatmodell kapcsolatai miatt az Excel jobban érzi magát, mint az Access vagy az SQL Server, de az Excel minden jóságával.
Szeretem kérni az Excel csapatától a kedvenc funkcióikat. Minden szerdán megosztom az egyik válaszukat. Köszönet Ash Sharmának, hogy átadta ezt az ötletet.
Excel gondolata a napnak
Az Excel Master barátaimtól kértem tanácsokat az Excel-mel kapcsolatban. Mai gondolat, hogy elgondolkodjak:
"Ne keressen, ha kapcsolatban áll"
John Michaloudis
