A pivot tábla az Excelben egy praktikus eszköz az adatok elemzéséhez és bemutatásához. De mi van akkor, ha az adatok különböző forrásokból származnak? Nézzük meg, hogyan készítsünk több lapból álló pivot táblát az Excelben.
Hiába léteznek ERP és BI rendszerek, egy pénzügyi igazgató nem nélkülözheti az Excelt. Mindenféle számítás pivot táblák, kényelmes grafikonok – az Excelben szinte bármit megtehet. De tudnia kell, hogyan kell csinálni.
Kimutatástábla az Excelben a Power Query használatával
Először is meg kell jegyezni, hogy az Excel különböző méretű forrástáblázatokkal tud dolgozni. De ezeknek a táblázatoknak a címsorának és címsorának meg kell egyeznie. Erre azért van szükség, hogy a program helyesen értelmezze a felhasznált adatokat. Ellenkező esetben hiba léphet fel.
Tegyük fel, hogy szükséges, amihez az adatokat különböző lapokról kell venni. Ez a helyzet akkor állhat elő, ha a cégnek több elosztott értékesítési pontja, raktára, ugyanazon árucsoportok különböző vásárlói vannak. Ebben az esetben a jelentéseket különböző fiókok adják. A megfelelő elemzésükhöz pedig célszerű egy, az egész vállalatra kiterjedő közös adattáblát készíteni, amely alapján a jövőben egy pivot tábla épül.
Ebben az esetben létre kell hoznia egy üres új munkalapot az Excelben.
Ezen a lapon lépjen az „Adatok” fülre.
Ezután kattintson a „Kérés létrehozása” gombra, a legördülő listában válassza a „Fájlból”, majd a „Könyvből” lehetőséget. Hozzon létre egy pivot táblázatot több lapból egy példa segítségével - két üzlet küldött jelentést arról, hogy különböző színű dobozok vannak az állványokon. Minden üzlet adatai egy lapon vannak tárolva. Ezekből a lapokból alakul ki az "1. könyv", amellyel dolgozunk.
A megjelenő ablakban meg kell adni a könyvet, ahonnan a program az adatokat átveszi, majd kattintson az "Importálás" gombra.
Megjelenik egy "Navigátor" nevű ablak. Ebben ki kell választania azt a lapot, amelyről az adatokat veszi. Bármilyen lapot megadhat.
A program az előnézeti ablakban jeleníti meg az adatokat, amelyeket a megadott lapról kell átvenni.
Példánkban látható, hogy a megadott lap sok cellát tartalmaz "null" adattal. Ez nem igaz, mivel a program ezeket a cellákat is feldolgozza. A feldolgozott értékek területének csökkentése és az ilyen nulla cellák eltávolítása érdekében ki kell javítani eredeti fájl. Ehhez lépjen az eredeti táblázathoz, és nyomja meg a "Ctrl + End" gombot. Az utolsó aktív táblázatcella kiemelve lesz. Törölni kell a táblázat jobb oldali és alatti összes celláját, biztosítva, hogy a „Ctrl + End” megnyomásával a táblázat jobb alsó cellája aktívvá váljon.
Ezt követően az adatforrás nem tartalmaz majd felesleges információkat.
Törölheti a "Navigáció" és a "Módosított típus" sorokat. És kezdje el szerkeszteni az adatokat a „Forrás” részben. A fő szerkesztő ablakban megjelenik a megadott könyv összes lapjának listája. Esetünkben a "Lap1" és a "Lap2".
Ezután az "Adatok" sorban kattintson a két nyíllal ellátott ikonra, az ábrán látható módon.
A megjelenő ablakban törölje a jelet az „Eredeti oszlopnév használata előtagként” jelölőnégyzetből. És kattintson az "OK" gombra. Megjelenik egy táblázat, amely tartalmazza az összes adatot.
Az asztalt újjáépítik. A „fejléc” ismétlődő sor eltávolítható. Ehhez a "Raktár" oszlop szűrőjében törölje a jelölést a "Raktár" négyzetből, és kattintson az "OK" gombra. Ezután ugyanabban a szűrőben kattintson az "Üres törlése" gombra. A megfelelő sorok el lesznek távolítva.
A megjelenő "Letöltés ide" ablakban állítsa a kapcsolót "Csak kapcsolat létrehozása" állásba, és kattintson a "Letöltés" gombra. Megjelenik egy lekérdezés, amely alapján a pivot tábla felépül.
A megjelenő ablakban állítsa a kapcsolót "Külső adatforrás használata" állásba, és kattintson a "Kapcsolat kiválasztása" gombra.
A megjelenő ablakban válassza ki a generált kérés nevét, esetünkben - "összefoglaló", majd kattintson a "Megnyitás" gombra.
Megjelenik a PivotTable Designer. Az adatok ugyanúgy hozzáadhatók és mozgathatók, mint egy normál pivot táblában.
PivotTable az Excel 2016 előtti verzióiban
BAN BEN Excel programok, amelyet 2003-ig adtak ki, különböző forrásokból származó pivot táblákat hoztak létre a „pivot készítése több konszolidációs tartományhoz” opcióval. A táblázat ilyen felépítése azonban nem teszi lehetővé a kapott információ teljes mennyiségének teljes körű elemzését. Az így felépített táblázat csak a legalapvetőbb adatelemzésre használható.
A 2007 óta megjelent verziókban szükség volt egy speciális „Pivot Table Wizard” hozzáadására, ahol az új adattartományok egyenként kerültek megadásra párbeszédpaneleken keresztül. Az így kapott pivot táblák funkciója is meglehetősen korlátozott volt.
Mik azok az Excel Pivot táblák? Valószínűleg egy avatatlan személy a legnyilvánvalóbbat feltételezi - ezek olyan táblázatok, amelyekben számos más, egymástól eltérő forrásból származó adatokat gyűjtenek (összefoglalnak), amelyek megfelelnek bizonyos lekérdezéseknek. Valójában ez a következtetés nem teljesen helytálló.
Az Excel PivotTables egy olyan eszköz, amely egy adott algoritmus szerint a leggyakrabban innen gyűjt információkat egy adatbázis tábla! (Lehetőség van több konszolidációs tartomány használatára forrásként, de ebben a cikkben nem tárgyaljuk.)
Az adatbázis-sorok kaotikus halmazából származó pivot táblák segítségével megteheti másodpercek alatt kinyerje ki a szükségeseket, vágja le bennük a felesleges információkat, adja hozzá a hiányzó információkat, és szép és könnyen áttekinthető formában mutassa be a felhasználónak.
Ha folyamatosan nagy mennyiségű információval dolgozik az MS Excelben, és semmit sem tud a pivot táblákról, akkor gondoljon arra, hogy „szögeket ver” egy számológéppel anélkül, hogy tudná annak valódi célját!
Mikor érdemes pivot táblákat használni?
Először is, amikor nagy mennyiségű statisztikai adattal dolgozik, aminek elemzése rendezés és szűrők segítségével nagyon munkaigényes.
Másodszor, amikor a jelentéseket bizonyos időközönként meg kell ismételni (újraszámítani) a forrás - az adatbázistábla - változása miatt.
Harmadszor, amikor az adatbázis-adatok változásait elemezzük különböző időszakok vagy egyéb kritériumok összefüggésében.
Ezek azok a fő helyzetek, amelyekben feltárul az Excel PivotTable eszköz igazi varázsereje.
A pivot táblák létrehozása és a velük való munka nem befolyásolja a forrás - az adatbázis - tartalmát! Pivot táblák excellehetővé teszi, hogy ezeket az adatokat különböző szögekből nézze meg anélkül, hogy bármit megváltoztatna az adatbázisban!!!
Ez az áttekintéssorozat utolsó és legfontosabb cikke információk tárolása és nagy táblázatos adatmennyiségek kezelése.
A ciklus korábbi cikkeiben alkottunk, tanultunk, alkalmaztunk és beléptünk.
Hozzon létre egy kimutatási táblázat sablont.
Ugyanabban a munkafüzetben fogunk létrehozni egy pivot táblátexcel, ahol az információforrás található - adatbázistábla, de egy másik lapon.
Továbbra is dolgozunk a DB2 "Fémszerkezetek gyártása a 2. szakasz szerint" oktatási mini-bázissal, amellyel a ciklus összes cikkében dolgoztunk.
1. Nyissa meg a fájlt MS Excelben.
2. Aktiváljuk ("kattintsunk az egérrel") az alaptáblázaton belül bármely cellát.
3. Végrehajtjuk az "Adatok" program főmenüjének parancsát - "Pivot table ...". Ez a parancs elindítja a PivotTable varázsló miniszolgáltatást.
4. Anélkül, hogy sokáig gondolkodnánk a kapcsolók pozícióinak kiválasztásának lehetőségeiről a "Varázsló ..." legördülő ablakaiban, konfiguráljuk őket (pontosabban, ne érintse meg őket), az alábbi képernyőképeken látható módon, az ablakok közötti mozgás a "Tovább" gombokkal.
A második lépésben a "Varázsló ..." magát a tartományt választja ki, ha megfelelően előkészítette az adatbázist, és végrehajtotta a cikk ezen részének 2. lépését.
5.
A "Varázsló ..." harmadik lépésében kattintson a "Befejezés" gombra. A pivot tábla sablon létrejön, és ugyanannak a fájlnak az új lapjára kerül, ahol az adatbázis található!
Kivéve most üres sablon az újonnan létrehozott lapon Excel fájl megjelenik a "Pivot Tables" panel eszközeivel és a "Pivot Table Fields" ablak, amely akkor jelenik meg, ha a kimutatástáblán belül bármely cella aktiválva van. Automatikusan megjelennek, csak a sablon mellé tettem és beállítottam a méreteket. Ha nem látja a "Pivot Tables" panelt, akkor hívja meg a főmenüben a "Nézet" - "Eszköztárak" - "Pivot Table" paranccsal.
Az MS Excel 2007-ben és újabb verzióiban a "Varázsló..." megszűnt, mert a felhasználók 99%-a soha nem változtatja meg a javasolt kapcsolóbeállításokat, és végigmegy ezen a három lépésen, egyszerűen egyetért a javasolt lehetőségekkel (mi is ezt tettük).
MS Excel 2007-ben a "Pivot Table" paranccsal megjelenik a "Pivot Table létrehozása" párbeszédpanel, amelyben elég megadni az elemzéshez szükséges adatforrást és a létrehozandó pivot tábla helyét.
Működő Excel pivot táblák létrehozása.
Az elkészített sablonban négy zóna található, amelyekben pivot tábla létrehozásához el kell helyezni az adatbázistábla mezőinek-oszlopainak a nevét a bal egérgombbal húzva a "Pivot Table Fields" ablakból. Emlékeztetlek arra, hogy az adatbázisban a mező egy névvel ellátott oszlop.
Figyelem!!!
A "PivotTable mezők listája" ablak elemei az adatbázis mezők címei!
1. Ha a "Pivot Table Field List" egyik elemét a legtöbb felső zóna sablon, ahol lesz "Oldalmező", akkor kapunk egy kényelmes szűrje a mező rekordjait.
2. Ha a kimutatás mezőlistájának egy elemét (vagy kettőt, ritkábban hármat vagy többet) a sablon bal oldali zónájába húz, ahol "Húrok mezeje" sorfejlécek Pivot tábla.
3. Ha a "Lista ..." egy elemét (vagy kettőt, ritkábban hármat vagy többet) a sablon felső zónájába húz, ahol az lesz "Oszlop mező", akkor az adatbázistábla e mezőjének rekordjaiból kapjuk meg oszlopfejlécek Pivot tábla.
4. Ha a "Lista ..." egy elemét (vagy kettőt, ritkábban hármat vagy többet) a sablon központi területére húz, ahol az lesz "adatelem", akkor ennek az adatbázismezőnek a rekordjaiból kapjuk meg értékeket Pivot tábla.
Az értékek - adatelemek - szigorúan a kétdimenziós táblázatok szabályai szerint lesznek elhelyezve, vagyis a megfelelő sorfejlécek és oszlopfejlécek metszéspontjában!
Nem túl világos? Térjünk át a gyakorlati példákra – minden kiderül!
9. feladat:
Megrendelésenként hány tonna fémszerkezetet gyártottak?
Tegyünk két dolgot, hogy megválaszoljuk ezt a kérdést! Ezeknek a műveleteknek a sémája az előző ábrán látható kék nyilakkal.
1. Húzza a "Rendelés" elemet a "Pivot Table Fields" ablakból az üres sablon "Sormezői" területére.
2.
Húzza a „Teljes tömeg, t” elemet a „Pivot tábla mezőinek listája” ablakból a sablon „Adatelemek” zónájába. Az eredmény a bal oldali képernyőképen látható. Szerintem nincs szükség magyarázatra.
Az "Adatelemek" fejléce: "Összesség a mező felett, össztömeg, t" található magasabb a "Sormezők" címszó alatt, és attól a helytől balra, ahol az "Oszlopmezők" címsor található. Erre figyelj!
10. feladat:
Hány tonna acélszerkezetet gyártottak le egy-egy rendelésre dátumok szerint?
Folytatjuk a munkát.
3. A 10. feladat kérdésének megválaszolásához elegendő a „Dátum” elemet hozzáadni a pivot táblánkhoz a „Pivot Table Fields” ablakból a sablon „Oszlopmezők” zónájába.
4.
A rekordok csoportosíthatók például hónapok szerint. Ehhez a "Pivot Tables" panelen kattintson a "Pivot Table" fülre, és válassza a "Csoport és szerkezet" - "Csoport ..." lehetőséget. A Csoportosítás ablakban végezze el a beállításokat a bal oldali képernyőképnek megfelelően.
Mivel az adatbázisunkban szereplő összes rekord áprilisban készült, a csoportosítást követően csak két oszlopot látunk - "Ápr" és "Végösszeg". Amikor a májusi és júniusi dátumú rekordok megjelennek a forrásadatbázisban, a megfelelő két oszlop hozzáadódik a kimutatástáblához. 
11. számú feladat:
Mikor és hány tonna és darab bélyeg készült összesen a 3. számú megrendelés alapján?
5. A kezdéshez húzza a "Rendelés" mezőt a "Pivot Table Fields" ablakból vagy közvetlenül magáról a kimutatástábláról a lap legtetejére a sablon "Oldalmezők" területére.
7. Adja hozzá a "Mennyiség, db" mezőt az "Adatelemek" területhez.
A feladat kérdésének megválaszolásához szükséges Excel pivot tábla konfigurációja elkészült.
8. Kattintson a lefelé mutató nyílra a B1 cellában, és a legördülő listában az „(Összes)” bejegyzés helyett válassza ki a számunkra érdekes 3. számú rendelésnek megfelelő „3”-at, és zárja be a listát a „ OK” gombot.
A feladat kérdésére a válasz a szöveg alatti képernyőképen található.
Kimutatástábla formázása.
Ha a pivot táblát ki kell nyomtatni és meg kell mutatni valakinek, vagy csak azért, hogy egy kicsit magadnak tetsszen, akkor formázni kell - megfelelő megjelenést biztosítva, és át kell fogalmazni néhány "nem megfelelő" címsort.
A „Szám a mezőre Összsúly, t” és a „Szám a mezőre Mennyiség, db” címsorok valahogy és érthetően hangzanak, de valahogy nem oroszul. Nevezzük át őket eufonikusabb "Termékek össztömege, t" és "Termékszám, db" névre.
1. Folytassuk a létrehozott táblázattal. Aktiválja a B5 cellát az egérrel.
2. A PivotTable eszköztáron kattintson a Mezőbeállítások gombra (az alábbi képen a jobb oldalon kiemelve).
3. A "Pivot tábla mezőjének kiszámítása" legördülő ablakban módosítsa a nevet, és kattintson az "OK" gombra.
4.
Hasonló algoritmus szerint átnevezzük a második mezőfejlécet, miután először „behelyeztük” az egeret a B6 cellába.
5. A "Pivot Table" panelen kattintson a "Jelentés formátuma" gombra (a fenti képen a bal oldalon kiemelve - a 3. elem felett).
6. A megjelenő „AutoFormat” ablakban válasszon a javasolt formázási lehetőségek közül, például „Jelentés 6”, majd kattintson az „OK” gombra.
Kinézet pivot táblánk jelentősen megváltozott.
Következtetés.
Felhívom a figyelmet néhány nagyon fontos pontra!
A forrás megváltoztatásakor (például egy másik rekord hozzáadása az adatbázishoz) magában a pivot táblában nem fognak automatikusan változások történni !!!
Ahhoz, hogy az adatbázis változásai megjelenjenek a pivot táblában, minden alkalommal „manuálisan” meg kell nyomni az „Adatok frissítése” gombot (a gomb ikonja „ ! ) a PivotTables panelen!!!
Helyezze az "Oldalmargók" terület mezőit tartalmazó mezőket szöveges információk, amely válhat cím a generált pivot táblához.
A Sormezők és Oszlopmezők területen helyezze el a tartalmazó mezőket szöveges információk, amely válhat címsorok sorokhoz és oszlopokhoz létrehozta a kimutatástáblát.
Az "Adatelemek" zónában helyezze el főleg számszerű információkat.
Folytassa úgy, hogy a mezőket úgy helyezze el, ahogyan azt egy papírra tenné! Ne félj hibázni. Minden könnyen javítható.
A jelen cikk előző részeiben két vagy három egérmozdulattal létrehozott pivot táblázatok nagyon gyorsan válaszoltak a nagyon nehéz kérdésekre! Ha a rendelések több hónapon belül megtörténnek, és a márkanevek száma meghaladja az ezret, akkor mennyi idő alatt sikerül megoldani a fent tárgyalt problémákat? Óra? Nap? Az Excel PivotTable-ok azonnal, ismételten és hiba nélkül csinálják!!!
Sok jó vastag és vékony könyvet írtak az Excel kimutatástábláiról. Először is ezek Bill Jelen („Mr. Excel”) és Michael Alexander könyvei. Akit érdekel ez a téma, annak ajánlom az elolvasását.
Természetesen nagyon nehéz, szinte lehetetlen egy rövid cikkben részletesen beszélni erről a csodálatos eszközről. Egyrészt gyakorlatiasabb szempontokat szerettem volna érinteni, másrészt a bevezető cikkben nem akartam „a dzsungelbe kerülni”, elriasztani a felhasználókat az információk mennyiségével és összetettségével. Ha az MS Excelnek ez a „varázseszköze” felkelti a blogolvasók érdeklődését, akkor a jövőben ez a téma egy kis brosúra kiadásával folytatódik, amely fontos és értékes gyakorlati ajánlásokat tartalmaz.
Mindenképpen Excel PivotTable-t használjon! Szakértői értéke sokszorosára nő!
Ezen a hat cikkből álló sorozaton, amely a téma általános áttekintése nagy mennyiségű információ feldolgozása Excelbenelkészült.
Könyörgöm tiszteletben tartva szerző munkája Iratkozz fel cikkhirdetésekre az egyes cikkek végén található ablakban vagy az oldal tetején található ablakban!
Tisztelt olvasók, kérdéseket, visszajelzéseket és megjegyzéseket tegyék meg az oldal alján található megjegyzésekben.
A kimutatás adatforrása egy adatlista, ahol általában minden oszlop mezőként működik a kimutatásban. De mi van akkor, ha egy olyan tábla érkezik hozzád, ami csak úgy néz ki, mint egy pivot tábla (formázott és úgy néz ki, de lehetetlen használni a pivot táblákkal való munkavégzéshez szükséges eszközöket). És adatlistává kell alakítani, pl. végrehajtani fordított működés. Ebből a bejegyzésből megtudhatja, hogyan konvertálhat két változót tartalmazó pivot táblát adatlistává.
Az ábra az általam leírt elvet mutatja. Azok. az A2:E5 tartományban az eredeti pivot tábla, amely adatlistává alakul (H2:J14 tartomány). A második táblázat ugyanazt az adatkészletet ábrázolja, csak más szemszögből. Az eredeti pivot tábla minden értéke karakterláncnak tűnik, amely egy sormezőelemből, egy oszlopmezőből és a hozzájuk tartozó értékből áll. Ez az adatmegjelenítés akkor hasznos, ha az adatokat más módon kell rendeznie és kezelnie.
Annak érdekében, hogy megvalósítsuk egy ilyen lista létrehozásának lehetőségét, a pivot tábla eszközeit használjuk. Adjunk hozzá egy gombot PivotTable varázsló a gyorselérési panelre, amely a szalagon nem elérhető számunkra, de megmaradt az Excel korábbi verzióinak maradványaként.
Navigáljon a lapra Fájl -> Beállítások. A megjelenő párbeszédpanelen Lehetőségekexcel, lapon Panel gyors hozzáférés a bal oldali mezőben keresse meg az elemet PivotTable és Kimutatásdiagram varázslóés add hozzá a megfelelőhöz. Kattintson az OK gombra.
Mostantól új ikonja van a Gyorselérési eszköztáron.
Kattintson erre a fülre az indításhoz PivotTable varázsló.
A varázsló első lépése a kimutatás adatforrás típusának kiválasztása. A kapcsoló beszerelése Több konszolidációs tartománybanés kattintson További.
A 2a lépésben adja meg, hogyan kell létrehozni az oldalmezőket. Helyezze el a kapcsolót Oldalmezők létrehozása -> Tovább.
A 2b lépésben, a terepen Hatótávolság válassza ki az adatokat tartalmazó tartományt, és kattintson a gombra Hozzáadás. Esetünkben ez lesz az eredeti A1:E4 pivot tábla helye.
A harmadik lépésben el kell döntenie, hová kívánja elhelyezni a pivot táblát, és kattintson a gombra Kész.
Az Excel létrehoz egy kimutatást az adatokkal. A képernyő bal oldalán egy területet fog látni Pivot tábla mezőinek listája. Távolítsa el az összes elemet a sorok és oszlopok mezőiből. Erről bővebben egy korábbi cikkben írtam.
A végén egy kis pivot táblázatot kap, amely egy cellából áll, amely tartalmazza az eredeti táblázat összes értékének összegét.
Kattintson duplán erre a cellára. Az Excel létrehoz egy új lapot, amely egy táblázatot tartalmaz az értékek listájával.
Ennek a táblázatnak a fejlécei azt jelentik Általános információ valószínűleg informatívabbá szeretné tenni őket.
A pivot tábla nagy mennyiségű adat gyors elemzésére szolgál. Lehetővé teszi a különböző táblázatokból és lapokból származó információk kombinálását, az általános eredmény kiszámítását. Ez az univerzális elemző eszköz jelentősen kibővíti az Excel program lehetőségeit.
Sorok és oszlopok felcserélésével új összegeket generálhat az eredeti paraméterek alapján. Az adatokat különböző elemek megjelenítésével szűrheti. És vizuálisan is részletezi a területet.
Kimutatástábla az Excelben
Például a különböző kereskedelmi ágak áruértékesítési táblázatát használjuk.
A tányérról látható, hogy melyik osztályon, mit, mikor és mennyiért árulták. Az egyes részlegek értékesítési összegének meghatározásához manuálisan kell kiszámolnia a számológépen. Vagy készítsen egy másik Excel-táblázatot, ahol képletekkel jeleníti meg az összegeket. Az információk ilyen módon történő elemzése terméketlen. Nem elég hosszú ahhoz, hogy tévedjen.
A legracionálisabb megoldás egy pivot tábla létrehozása Excelben:
Egyszerű, gyors és minőségi.
Fontos árnyalatok:
- Az adatgyűjtéshez megadott tartomány első sorát ki kell tölteni.
- Az alaplapon minden oszlopnak saját fejléce kell, hogy legyen – egyszerűbb összefoglaló jelentést felállítani.
- Az Excelben információforrásként használható Táblázatok elérése, SQL szerver satöbbi.
Hogyan készítsünk pivot táblát több táblából
Gyakran több táblázatból kíván összefoglaló jelentéseket készíteni. Van egy-két információs tábla. Ezeket egy közössé kell egyesítenünk. A tudomány kedvéért két üzletben találjunk ki raktári maradékot.
A pivot tábla több lapból történő létrehozásának eljárása ugyanaz.
Hozzunk létre egy jelentést a kimutatás varázslóval:
Amint látja, néhány kattintással összetett jelentéseket készíthet több különböző mennyiségű információt tartalmazó lapból vagy táblázatból.
Hogyan kell dolgozni pivot táblákkal az Excelben
Kezdjük a legegyszerűbbel: oszlopok hozzáadásával és eltávolításával. Vegyünk például egy összefoglaló táblázatot a különböző részlegek értékesítéseiről (lásd fent).
A pivot tábla jobb oldalán volt egy feladatterületünk, ahol a mezőlistában oszlopokat jelöltünk ki. Ha eltűnt, egyszerűen kattintson a táblagépre.
Adjunk hozzá egy másik mezőt a jelentés kimutatástáblájához. Ehhez jelölje be a „Dátum” (vagy a „Termék”) melletti négyzetet. A jelentés azonnal megváltozik - megjelenik az értékesítés napi dinamikája az egyes részlegekben.
Adatok csoportosítása jelentésben hónapok szerint. Ehhez kattintson a gombra Jobb klikk vigye az egeret a "Dátum" mezőre. Kattintson a "Csoport" gombra. Válassza a "hónap szerint" lehetőséget. Ez a következőhöz hasonló pivot táblát eredményez:
Nak nek módosítsa a pivot tábla beállításait, elegendő a meglévő sormezők melletti jelölőnégyzetek kijelölését kivenni, és beállítani a többi mezőhöz. Terméknév szerint készítsünk jelentést, ne osztályonként.
És ez történik, ha eltávolítjuk a „dátumot”, és hozzáadjuk a „részleget”:
De ilyen feljelentést lehet tenni, ha húzza a mezőket a különböző területek között:
Ha egy sor címét egy oszlop címére szeretné változtatni, válassza ki ezt a címet, kattintson a felugró menüre. Kattintson az „áthelyezés oszlopfejlécekre” lehetőségre. Ily módon a dátumot áthelyeztük az oszlopokba.
Az áruk neve elé tesszük az „Osztály” mezőt. A menü „ugrás az elejére” szakasz használatával.
Egy adott termék részleteinek megjelenítése. A második pivot tábla példáján, amely a raktárak egyenlegeit jeleníti meg. Válasszon ki egy cellát. Kattintson a jobb egérgombbal - "kibontás".
A megnyíló menüben válassza ki a megjeleníteni kívánt adatokat tartalmazó mezőt.
Amikor egy pivot táblára kattintunk, elérhetővé válik egy lap a jelentési beállításokkal. Segítségével módosíthatja a címsorokat, adatforrásokat, csoportinformációkat.
A kiállított közüzemi számlák helyességének ellenőrzése
Az Excel pivot tábláinak segítségével könnyen ellenőrizhető, hogy a szolgáltató szervezetek mennyire helyesen számítanak fel bérleti díjat. Egy másik pozitív szempont a megtakarítás. Ha havonta ellenőrizzük, hogy mennyi áramot és gázt fogyasztunk, találhatunk tartalékot, amellyel pénzt takaríthatunk meg a lakások kifizetésén.
Kezdetben azt javasoljuk, hogy állítsunk össze egy összefoglaló táblázatot az összes közüzemi számlára vonatkozóan. A különböző városok esetében az adatok eltérőek lesznek.
Például összefoglaló táblázatot készítettünk Moszkva tarifáiról:
Oktatási célból vegyünk egy 4 fős családot, akik egy 60 nm-es lakásban élnek. m. A közüzemi számlák ellenőrzéséhez minden hónapra táblázatokat kell készítenie a számításokhoz.
Első oszlop = első oszlop a kimutatástáblából. A második a képlet a nézet kiszámításához:
Díj * létszám / mérőállás / terület
Képleteink arra a lapra vonatkoznak, ahol az árfolyamokat tartalmazó összesítő táblázat található.
Ha a közüzemi számlák kiszámításakor kedvezményeket alkalmaznak, akkor azok is beírhatók a képletbe. Kérjen tájékoztatást az elhatárolásokról a szolgáltató szervezet számviteli osztályán. Amikor a tarifák változnak, csak módosítsa a cellákban lévő adatokat.
A PivotTable egy olyan eszköz, amely segít a vállalati adatok elemzésében. Különböző szövegszerkesztőkben létrehozhat ilyen táblázatot.
A létrehozáshoz ki kell választania a sorok és az oszlopok fejléceit a generált jelentésből. A jelentések elkészítésének legkényelmesebb módja az Excel. A szűrőt a teljes jelentésre vagy annak egy meghatározott részére alkalmazhatja.
hogyan készítsünk pivot táblát Excelben
Nézzük először a 2007-es verziót. Egyes felhasználók még mindig a szerkesztő régi verziójával rendelkeznek. Az induláshoz szüksége van táblázat létrehozása jelentést készíteni. Nem tartalmazhat üres cellákat és sorokat. Elemezzük a jelentéskészítés folyamatát a legegyszerűbb példa segítségével:
- Az Excel 2007 indítása. Válassza a "Beszúrás" fület a felső sávon.
- Válassza a „Javasolt kimutatások létrehozása” lehetőséget.
- Megjelenik egy ablak a keretbeállításokkal. Az elrendezésre kattintva megtekintheti, hogyan fog kinézni a jelentése. Ezt követően kiválaszthatja a főt. Kattintson az OK gombra, és a jelentés megjelenik a lapon. Ezenkívül az Excel maga határozza meg a sorok sorrendjét.
- A programban szűrheti és rendezheti az értékeket. Erre a panelen van egy speciális ikon.
Ez a legegyszerűbb példa arra, hogyan hozhat létre pivot táblát régi verzió excel.
Pivot táblák az Excel 2010-ben és 2013-ban – példa az Excelben való munkavégzésre
A jelentéskészítés Excel 2010-ben és 2013-ban szinte ugyanaz. A folyamat ezekben a verziókban kissé eltér a 2007-es verzió létrehozásától. De, néhány pillanatot hasonló: mielőtt elkezdené, létre kell hoznia egy egyszerű táblázatot, amelyben megadja az összes szükséges adatot. A felső sorban az oszlopok neveinek kell szerepelnie.
Most már készíthet jelentést Excelben:
Mit lehet tenni a lappal:
- Hozzáférhet egy eszköztárhoz az értékek kezeléséhez. A "Jelentéshez hozzáadandó mezők kiválasztása" lapon minden mező és címük megjelenik. A szükséges elemek hozzáadásához ki kell jelölnie a szükséges mezőket, és létrejön a lista.
- Lehetőség van a mezők elhelyezkedésének szabályozására. Az oszlopokat áthúzhatja a kívánt ablakokba, és az Önnek megfelelő sorrendbe rendezheti. Az elemeket is szűrheti. Ehhez húzza az elemet a "Szűrő" ablakba. Ha külön mezőre kell számítania, húzza azt az "Érték" ablakba.
- Te tudod használni további beállítások. A következő elv szerint találhatja meg őket: "Paraméterek" "Munka a kimutatástáblákkal". Nagyon sok olyan eszköz létezik, amelyek számos beállítással és kiegészítő funkcióval rendelkeznek.
Kimutatás létrehozása a Kimutatás varázsló segítségével
A PivotTable varázsló eszköz nem jelenik meg a fő panelen. Ezért először aktiválni kell. Ehhez lépjen a "Fájl" "Opciók" "Gyorselérési eszköztár" menüpontra. veled szemben megnyílik egy lista"Select commands from", jelölje be a "Parancsok a szalagon" lehetőséget. Most válassza ki a PivotTable varázslót, és adja hozzá. Az összes végrehajtott művelet után a kiválasztott funkció ikonja megjelenik a fő panelen.
Most folytathatja az összefoglaló jelentés létrehozásának folyamatát a varázsló segítségével:
Ha egy egyszerű jelentést választottak ki forrásként, amely tartalmazza a jelentéshez szükséges összes adatot, csak el kell készítenie. Állítsa be a tartományt, azaz válasszon adatokat egyszerű asztal. Válasszon egy helyet, és kattintson a "Befejezés" gombra.
Ha olyan forrást választ ki, amelynek adatait más forrásokból kell konszolidálni, akkor be kell jelölni ezzel az elemmel szemben. Most meg kell adnia, hogyan hozza létre az oldalmezőket. Egy tartományt is meg kell adnia. Ehhez először válasszon ki egy tartományt, és kattintson a "hozzáadás" gombra. A későbbi értékek is hozzáadódnak.
Annak érdekében, hogy ne keveredjenek össze a tartományokban, nevet kell adni nekik. Ehhez válassza a lehetőséget az egyik tartomány listát, és adja meg a mezők számát, majd minden mezőnek nevet ad. És így minden tartományban.
Excel 2010 pivot táblák több lappal
Ha a jelentésekben szereplő információk azonos típusúak, akkor a legegyszerűbb a pivot tábla varázsló használata. Ha a jelentés összetett szerkezetű, akkor másként kell eljárnia. Például, két asztal van, az egyik az áru átvételét tartalmazza, a másik pedig a különböző üzletekben eladott áruk mennyiségét. A lényeg az, hogy ugyanazok a címsorok legyenek, kevesebb idő fogja átvinni őket egy jelentésbe. Ebben az esetben a jelentés a következőképpen készül:
Most összefoglaló jelentést kell készítenünk:
- Beszúrás.
- Összevont jelentés.
- Adja meg a tartományt és a helyet.
De mi van akkor, ha több táblából kell jelentést készítenie. Kézzel nehéz dolgozni. Ezért Te tudod használni speciális EXCEL bővítmény. Letölthető a fejlesztő webhelyéről.
Szóval kitaláltuk, hogyan pivot táblákat hozzon létre excelben. Ki kell mutatnia egy kis kitartást és figyelmességet, és akkor az eredmény nem fogja várni.
Betöltés...