Az Excel olyan sok hatékony adatelemző eszközzel és funkcióval rendelkezik, hogy felmerülhet benned a kérdés, miért kell még egy másikat megtanulnod: a kimutatástáblát. A rövid válasz az, hogy a PivotTable hasznos fegyver az adatelemzési arzenálhoz. A hosszú válasz az, hogy a PivotTable-okat érdemes megtanulni, mert nem csak egy vagy kettő, hanem egy hosszú előnyök listája is van.
Számoljuk meg a módokat: Az Excel PivotTable- ok könnyen létrehozhatók és karbantarthatók; elképesztően gyorsan végeznek nagy és összetett számításokat; gyorsan és egyszerűen frissítheti őket az új adatok figyelembevételével; A PivotTable-ok dinamikusak, így az összetevők könnyen áthelyezhetők, szűrhetők és hozzáadhatók; és végül a kimutatások a legtöbb formázási beállítást használhatják, amelyeket a szokásos Excel-tartományokra és -cellákra alkalmazhat.
Ó, várj, van még egy: a kimutatások teljes mértékben személyre szabhatók, így minden jelentést tetszés szerint készíthet. Íme tíz olyan technika, amellyel profi PivotTable-vé varázsolhat.
Ismerje meg, hogyan hozhat létre kimutatást az Excel gyorselemző eszközével .
Alapértelmezés szerint, amikor a kimutatástáblába kattint, az Excel megjeleníti a kimutatásmezők munkaablakot, majd ismét elrejti a kimutatásmezők munkaablakot, amikor a kimutatáson kívülre kattint.
Első pillantásra nincs ezzel semmi baj. Ha azonban a szalag Kimutatáseszközök kontextuális lapján lévő parancsokkal szeretne dolgozni, akkor legalább egy cellát ki kell jelölnie a kimutatásban. A kimutatás bármely cellájának kiválasztása azonban azt jelenti, hogy a Kimutatásmezők munkaablak is értékes képernyőterületet foglal el.
Szerencsére az Excel lehetővé teszi a Kimutatásmezők munkaablak kézzel történő ki- és bekapcsolását is, ami több helyet biztosít a kimutatás jelentésének megjelenítéséhez. Ezután újra bekapcsolhatja a kimutatásmezők munkaablakot, amikor mezőket kell hozzáadni, áthelyezni vagy törölni.
A PivotTable Fields munkaablak ki- és bekapcsolásához kövesse az alábbi lépéseket (mindkettőt!):
Kattintson a PivotTable belsejébe.
Válassza az Elemzés → Megjelenítés → Mezőlista menüpontot.
A PivotTable Fields munkaablak gyors elrejtésének módja, ha a panel jobb felső sarkában található Bezárás gombra kattint.
Alapértelmezés szerint a Kimutatásmezők munkaablak két részre van osztva: a Mezők szakasz felsorolja az adatforrás elérhető mezőit, és a panel tetején jelenik meg, a Területek pedig a kimutatásterületeket – szűrők, oszlopok, sorok és értékek – tartalmazza. és a panel alján jelenik meg. Ezt az elrendezést a munkamódszerhez igazíthatja. Itt vannak a lehetőségek:
A következő lépéseket kell követnie a kimutatásmezők munkaablak elrendezésének módosításához:
Kattintson bármelyik cellára a kimutatásban.
Kattintson az Eszközök elemre.
Az Eszközök gombon a fogaskerék ikon látható.
Kattintson az Eszközök elemre a Kimutatástábla-mezők munkaablak elrendezési beállításainak megtekintéséhez.
Az Excel megjeleníti a PivotTable Fields munkaablak eszközeit.
Kattintson a használni kívánt elrendezésre.
Az Excel módosítja a Kimutatástábla-mezők munkaablak elrendezését a választás alapján.
A PivotTable Fields munkaablak eszközei megjelenésekor vegye figyelembe, hogy a mezőlistát is rendezheti . Az alapértelmezett a Rendezés adatforrás sorrendjében, ami azt jelenti, hogy az Excel a mezőket abban a sorrendben jeleníti meg, ahogyan az adatforrásban megjelennek. Ha inkább betűrendben szeretné rendezni a mezőket, kattintson a Rendezés A-tól Z-ig lehetőségre.
A kimutatások használatának fő előnye, hogy egyszerű módszert kínálnak nagy mennyiségű adat összefoglalására egy tömör jelentésben adatelemzés céljából. Röviden: a kimutatások az erdőt mutatják a fák helyett. Néha azonban látnia kell néhány fát. Például, ha egy marketingkampány eredményeit tanulmányozza, a kimutatás megjelenítheti az 1 ingyenes 10-zel promóció eredményeként eladott fülhallgatók teljes számát. De mi van akkor, ha szeretné látni a szám alapjául szolgáló részleteket? Ha a forrásadatok több száz vagy több ezer rekordot tartalmaznak, akkor valamilyen módon szűrnie kell az adatokat, hogy csak a kívánt rekordokat lássa.
792 fülhallgatót adott el, de mik a részletek ennek a számnak a hátterében?
Szerencsére az Excel egyszerűbb módot kínál a kívánt rekordok megtekintéséhez, mivel lehetővé teszi egy adott adatérték alapjául szolgáló részletek közvetlen megtekintését. Ez az úgynevezett lebontásban a részleteket. Amikor részletezi egy adott adatértéket egy kimutatásban, az Excel visszatér a forrásadatokhoz, kibontja az adatértéket tartalmazó rekordokat, majd egy új munkalapon jeleníti meg a rekordokat. Egy tartományon vagy táblán alapuló kimutatás esetén ez a kivonás csak egy-két másodpercet vesz igénybe, attól függően, hogy a forrásadatok hány rekordot tartalmaznak.
A PivotTable adatpontok mögött rejlő részletek részletezéséhez használja a következő módszerek egyikét:
Az Excel egy új munkalapon jeleníti meg a mögöttes adatokat. Ez a kép például a fenti képen látható 792 fülhallgató részleteit mutatja, amelyeket a fenti képen látható 1 ingyenes 10-es promócióval együtt árusítanak.
Részletek a fülhallgatók értékesítése mögött.
Amikor megpróbál részletezni egy adatérték mögöttes részleteit, az Excel a következő hibaüzenetet jelenítheti meg: Nem módosítható a kimutatás-jelentés ezen része. Ez a hiba azt jelenti, hogy az a funkció, amely általában lehetővé teszi a részletezést, ki van kapcsolva. A funkció újbóli bekapcsolásához kattintson a kimutatás bármely cellájára, majd kattintson az Elemzés → Kimutatás → Beállítások elemre a Kimutatás beállításai párbeszédpanel megjelenítéséhez. Kattintson az Adatok fülre, jelölje be a Részletek megjelenítésének engedélyezése jelölőnégyzetet, majd kattintson az OK gombra.
Az ellenkező helyzet fordul elő, ha a kimutatást tartalmazó munkafüzetet terjeszti, és nem szeretné, hogy a többi felhasználó részletezze a munkafüzetet, és telezsúfolja a részletező munkalapokat. Ebben az esetben kattintson az Elemzés → Kimutatás → Beállítások elemre, kattintson az Adatok fülre, törölje a Részletek megjelenítésének engedélyezése jelölőnégyzetet, majd kattintson az OK gombra.
Néha érdemes lehet látni egy kimutatás összes mögöttes forrásadatát. Ha a forrásadat egy másik munkalapon lévő tartomány vagy táblázat, akkor a mögöttes adatokat a munkalap megjelenítésével tekintheti meg. Ha azonban a forrásadatok nem állnak rendelkezésre olyan könnyen, az Excel segítségével gyorsan megtekintheti az összes mögöttes adatot. Kattintson a jobb gombbal a kimutatás végösszegére (azaz a kimutatás jobb alsó sarkában lévő cellára), majd kattintson a Részletek megjelenítése parancsra. (A cellára duplán is kattinthat.) Az Excel a kimutatás összes mögöttes adatát egy új munkalapon jeleníti meg.
A Kimutatás egyik jó tulajdonsága, hogy egy normál Excel-munkalapon található, ami azt jelenti, hogy alkalmazhat formázási beállításokat, például igazításokat és betűtípusokat a kimutatás egyes részein. Ez jól működik, különösen akkor, ha egyéni formázási követelményei vannak.
Például előfordulhat, hogy házon belüli stílusirányelvei vannak, amelyeket be kell tartania. Sajnos a formázás alkalmazása időigényes lehet, különösen, ha több különböző formázási lehetőséget alkalmaz. A teljes formázási idő pedig megterhelő lehet, ha különböző formázási beállításokat kell alkalmaznia a kimutatás különböző részein. Ha inkább stílust alkalmaz, jelentősen csökkentheti a kimutatások formázásával töltött időt.
A stílus formázási beállítások – betűtípusok, szegélyek és háttérszínek – gyűjteménye, amelyeket az Excel a kimutatás különböző területeihez határoz meg. Például egy stílus használhat félkövér, fehér szöveget fekete alapon címkék és végösszegek esetén, és fehér szöveget sötétkék háttéren elemek és adatok esetében. Ezen formátumok manuális meghatározása fél órától egy óráig tarthat. A stílusfunkcióval azonban kiválaszthatja azt, amelyet a kimutatás egészéhez használni szeretne, és az Excel automatikusan alkalmazza az egyes formázási beállításokat.
Az Excel több mint 80 stílust határoz meg, három kategóriába sorolva: Világos, Közepes és Sötét. A Light kategória tartalmazza a Pivot Style Light 16-ot, az Ön által létrehozott kimutatásokra alkalmazott alapértelmezett formázást; és a Nincs, amely eltávolítja az összes formázást a kimutatásból. Saját stílusformátumokat is létrehozhat.
Az alábbi lépések szerint kell stílust alkalmazni egy Excel kimutatástáblára:
Kattintson a formázni kívánt cellára a kimutatásban.
Kattintson a Tervezés fülre.
A PivotTable-stílusok csoportban kattintson a Továbbiak gombra.
Megjelenik a stílusgaléria.
Kattintson az alkalmazni kívánt stílusra.
Az Excel alkalmazza a stílust.
Előfordulhat, hogy az előre meghatározott kimutatás-stílusok egyike sem adja meg a kívánt megjelenést. Ebben az esetben saját maga határozhatja meg ezt a megjelenést úgy, hogy a semmiből létrehoz egy egyéni kimutatási stílust.
Az Excel rendkívüli rugalmasságot kínál az egyéni kimutatás-stílusok létrehozásakor. 25 különálló kimutatáselemet formázhat. Ezek az elemek közé tartozik a teljes táblázat, az oldalmezők címkéi és értékei, az első oszlop, a fejléc sor, a végösszeg sor és a végösszeg oszlop. Meghatározhat csíkokat is, amelyek a váltakozó sorokra vagy oszlopokra alkalmazott külön formátumok. Például a First Row Stripe formázást alkalmaz az 1., 3., 5. és így tovább; a Second Row Stripe formázást alkalmaz a 2., 4., 6. és így tovább. A csíkok könnyebben olvashatóvá tehetik a hosszú vagy széles jelentéseket.
A sok elem feletti ellenőrzés lehetővé teszi, hogy egyéni stílust hozzon létre az igényeinek megfelelően. Például szüksége lehet a kimutatásra, hogy megfeleljen a vállalati színeknek. Hasonlóképpen, ha a kimutatás egy nagyobb jelentés részeként jelenik meg, előfordulhat, hogy a kimutatás formátuma megfelel a nagyobb jelentésben használt témának.
Az egyéni kimutatás-stílus létrehozásának egyetlen hátránya, hogy ezt a semmiből kell megtennie, mivel az Excel nem teszi lehetővé egy meglévő stílus testreszabását. Jaj, Excel! Tehát ha meg kell határoznia a formázást mind a 25 kimutatáselemhez, az egyéni stílus létrehozása időigényes lehet.
Ha azonban még mindig készen áll rá, a következő lépésekkel hozza létre saját Excel-kimutatás-stílusát:
Kattintson a Tervezés fülre.
A PivotTable-stílusok csoportban kattintson a Továbbiak lehetőségre.
Megjelenik a stílusgaléria.
Kattintson az Új kimutatás-stílus lehetőségre.
Megjelenik a New PivotTable stílus párbeszédpanel.
Az új PivotTable-stílus párbeszédpanel.
Adjon nevet egyéni stílusának.
A Táblaelemek listában válassza ki a formázni kívánt kimutatás funkciót.
Kattintson a Formátum gombra.
Megjelenik a Cellák formázása párbeszédpanel.
Az elem szövegének formázásához használja a Betűtípus lap beállításait.
Kiválaszthat egy betűtípust, betűstílust (például félkövér vagy dőlt) és betűméretet. Választhat aláhúzást, színt és áthúzási hatást is.
Az elem szegélyének formázásához használja a Szegély lap beállításait.
Kiválaszthatja a szegély stílusát, színét és helyét (például a bal szélt, a felső élt vagy mindkettőt).
Használja a Kitöltés lap beállításait az elem háttérszínének formázásához.
Választhat egyszínű vagy mintázatot. A Kitöltési effektusok gombokra kattintva megadhat egy színátmenetet is, amely egyik színről a másikra változik.
Kattintson az OK gombra.
Az Excel visszatér az Új kimutatás-stílus párbeszédpanelhez.
További táblázatelemek formázásához ismételje meg az 5–10. lépéseket.
Az Új kimutatás-stílus párbeszédpanelen található egy Előnézet szakasz, amely megmutatja, hogyan fog kinézni a stílus, ha egy kimutatásra alkalmazzák. Ha különösen büszke az új stílusára, érdemes lehet használni az összes kimutatáshoz. Miért ne? Ha azt szeretné, hogy az Excel az új stílust használja alapértelmezettként minden jövőbeni hamisított kimutatásnál, jelölje be a Beállítás alapértelmezett kimutatási stílusként ehhez a dokumentumhoz jelölőnégyzetet.
Ha végre végzett, kattintson az OK gombra.
Az Excel menti az egyéni kimutatás-stílust.
Furcsa módon az Új kimutatás-stílus párbeszédpanel bezárása után az Excel nem alkalmazza az új stílust az aktuális kimutatásra. Néma! Ha saját maga szeretné alkalmazni a stílust, jelölje ki bármelyik cellát a kimutatásban, kattintson a Tervezés lehetőségre, majd a Kimutatásstílusok csoportban kattintson a További gombra a stílusgaléria megnyitásához, majd kattintson a stílusára a galéria tetején megjelenő Egyéni részben.
Ha módosítania kell egyéni stílusát, nyissa meg a stílusgalériát, kattintson jobb gombbal az egyéni stílusra, majd kattintson a Módosítás parancsra. A módosítások végrehajtásához használja a Kimutatástábla stílusának módosítása párbeszédpanelt, majd kattintson az OK gombra.
Ha úgy találja, hogy egy másik egyéni stílust kell létrehoznia, amely hasonló egy meglévő egyéni stílushoz, ne foglalkozzon az új stílus létrehozásával. Ehelyett nyissa meg a stílusgalériát, kattintson jobb gombbal a meglévő egyéni stílusra, majd kattintson a Másolás parancsra. A Kimutatástábla stílusának módosítása párbeszédpanelen módosítsa a stílus nevét és formázását, majd kattintson az OK gombra.
Ha már nincs szüksége egyéni stílusra, törölje azt, hogy csökkentse a zsúfoltságot a stílusgalériában. Kattintson a Tervezés fülre, nyissa meg a kimutatás-stílusok galériáját, kattintson a jobb gombbal arra az egyéni stílusra, amelyre már nincs szüksége, majd kattintson a Törlés parancsra. Amikor az Excel megerősítést kér, kattintson az OK gombra.
Az Excelnek van egy csúnya szokása, hogy néha nem őrzi meg az egyéni formázást, amikor frissíti vagy újraépíti a kimutatást. Ha például félkövér betűtípust alkalmazott egyes címkéknél, ezek a címkék a frissítés után visszatérhetnek normál szöveggé. Az Excel rendelkezik a Formázás megőrzése nevű funkcióval, amely lehetővé teszi a formázás megőrzését a frissítés során; aktiválásával megtarthatja egyéni formázását.
A Formázás megőrzése funkció mindig aktiválva van az alapértelmezett kimutatásokban. Egy másik felhasználó azonban kikapcsolhatta volna ezt a funkciót. Például előfordulhat, hogy Ön egy másik személy által létrehozott kimutatással dolgozik, és lehet, hogy ő deaktiválta a Formázás megőrzése funkciót.
Ne feledje azonban, hogy amikor frissít vagy újraépít egy kimutatást, az Excel újra alkalmazza a jelentés jelenlegi stílusformáit. Ha nem adott meg stílust, az Excel újra alkalmazza az alapértelmezett PivotTable stílust (amelynek neve Pivot Style Light 16); ha stílust adott meg, az Excel újra alkalmazza azt a stílust.
Az alábbi lépéseket követve konfigurálhatja az Excel kimutatását a formázás megőrzése érdekében:
Kattintson bármely cellára a kimutatásban, amellyel dolgozni szeretne.
Válassza az Elemzés → PivotTable → Beállítások menüpontot.
Megjelenik a Kimutatás beállításai párbeszédpanel az Elrendezés és formázás lappal.
Törölje az Oszlopszélességek automatikus igazítása frissítéskor jelölőnégyzet bejelölését.
A beállítás törlése megakadályozza, hogy az Excel automatikusan formázza a dolgokat, például az oszlopszélességeket, amikor elforgatja a mezőket.
Jelölje be a Cellaformázás megőrzése frissítéskor jelölőnégyzetet.
Click OK.
Excel preserves your custom formatting each time you refresh the PivotTable.
When you create the first PivotTable in a workbook, Excel gives it the uninspiring name PivotTable1. Subsequent PivotTables are named sequentially (and just as uninspiringly): PivotTable2, PivotTable3, and so on. However, Excel also repeats these names when you build new PivotTables based on different data sources. If your workbook contains a number of PivotTables, you can make them easier to distinguish by giving each one a unique and descriptive name. Here’s how:
Click any cell within the PivotTable that you want to work with.
Click Analyze → PivotTable.
Use the PivotTable Name text box to type the new name for the PivotTable.
The maximum length for a PivotTable name is 255 characters.
Click outside the text box.
Excel renames the PivotTable.
A default PivotTable that has at least one row field contains an extra row at the bottom of the table. This row is labeled Grand Total and includes the total of the values associated with the row field items. However, the value in the Grand Total row may not actually be a sum. For example, if the summary calculation is Average, the Grand Total row includes the average of the values associated with the row field items.
Similarly, a PivotTable that has at least one column field contains an extra column at the far right of the table. This column is also labeled “Grand Total” and includes the total of the values associated with the column field items. If the PivotTable contains both a row and a column field, the Grand Total row also has the sums for each column item, and the Grand Total column also has the sums for each row item.
Besides taking up space in the PivotTable, these grand totals are often not necessary for data analysis. For example, suppose you want to examine quarterly sales for your salespeople to see which amounts are over a certain value for bonus purposes. Because your only concern is the individual summary values for each employee, the grand totals are useless. In such a case, you can tell Excel not to display the grand totals by following these steps:
Click any cell within the PivotTable that you want to work with.
Click Design → Grand Totals.
Excel displays a menu of options for displaying the grand totals.
Click the option you prefer.
The menu contains four items:
Excel puts the selected grand total option into effect.
The field headers that appear in the report are another often-bothersome PivotTable feature. These headers include Sort & Filter buttons, but if you don’t use those buttons, the field headers just clutter the PivotTable. To turn off the field headers, click inside the PivotTable and then choose Analyze → Show → Field Headers.
PivotTables often result in large workbooks because Excel must keep track of a great deal of extra information to keep the PivotTable performance acceptable. For example, to ensure that the recalculation involved in pivoting happens quickly and efficiently, Excel maintains a copy of the source data in a special memory area called the pivot cache.
If you build a PivotTable from data that resides in a different workbook or in an external data source, Excel stores the source data in the pivot cache. This greatly reduces the time Excel takes to refresh and recalculate the PivotTable. The downside is that it can increase both the size of the workbook and the amount of time Excel takes to save the workbook. If your workbook has become too large or it takes too long to save, follow these steps to tell Excel not to save the source data in the pivot cache:
Click any cell in the PivotTable.
Click Analyze → PivotTable → Options.
The PivotTable Options dialog box appears.
Click the Data tab.
Deselect the Save Source Data with File check box.
Click OK.
Excel no longer saves the external source data in the pivot cache.
You might need to use a PivotTable value in a worksheet formula. You normally reference a cell in a formula by using the cell’s address. However, this won’t work with PivotTables because the addresses of the report values change as you pivot, filter, group, and refresh the PivotTable.
To ensure accurate PivotTable references, use Excel’s GETPIVOTDATA function. This function uses the data field, PivotTable location, and one or more (row or column) field/item pairs that specify the exact value you want to use. This way, no matter what the PivotTable layout is, as long as the value remains visible in the report, your formula reference remains accurate.
Here’s the syntax of the GETPIVOTDATA function:
GETPIVOTDATA(data_field, pivot_table, [, field1, item1][, …]
The two required fields are data_field, which is the name of the field you’re using in the PivotTable’s Values area, and pivot_table, which specifies the cell address of the upper-left corner of the PivotTable. The rest of the arguments come in pairs: a field name and an item in that field.
For example, here’s a GETPIVOTDATA formula that returns the PivotTable value where the Product field item is Earbuds and the Promotion field item is 1 Free with 10:
=GETPIVOTDATA("Quantity", $A$3, "Product", "Earbuds", "Promotion", "1 Free with 10")
The GETPIVOTDATA function doing its thing.
A GETPIVOTDATA egy kicsit bonyolult, de ne aggódj. Szinte soha nem kell kézzel kiszúrnia ezt a függvényt és az összes érvét. Ehelyett az Excel kényelmesen kezel mindent, ha rákattint a képletben használni kívánt kimutatás-értékre. Fú!
Lábjegyzetek és végjegyzetek létrehozása a Word 2013-ban. Részletes útmutató, hogyan készíthet különféle stílusú jegyzeteket bibliográfiai információkhoz vagy magyarázó megjegyzésekhez.
Tudjon meg mindent arról, hogyan lehet az Excelben kiemelni a statisztikailag kiugró értékeket, mégpedig egyszerű eszközökkel és módszerekkel. Kiemelt figyelmet fordítunk a kiugró értékek azonosítására és kezelésére.
Az Excel Mintavételi eszközével véletlenszerűen kiválaszthat elemeket egy adatkészletből vagy választhat minden n-edik elemet. Ismerje meg, hogyan használhatja ezt a hasznos funkciót az adatelemzéshez!
Fedezze fel a Microsoft Word 2019 legjobb trükkjeit, amelyek segítenek a hatékonyabb munkavégzésben. Tudd meg, hogyan használhatod ki a program funkcióit!
A Word 2019-ben a bekezdések igazítása és behúzása kulcsfontosságú a dokumentumok megfelelő megjelenítéséhez. Ismerje meg a formázási lehetőségeket és tippeket a hatékonyabb munkához.
Fedezze fel, hogyan használhatja az Excel 2019 Solver bővítményt a komplex problémák megoldására. A célcella, változócellák és kényszerek beállítása lépésről lépésre.
A Word lehetővé teszi dinamikus elemek hozzáadását a dokumentumhoz. Fedezze fel, hogyan lehet különböző mezőket használni a Word programban a dinamikus tartalom létrehozásához.
A SharePoint 2010 oszlopai az adatok tárolására szolgálnak. Fedezze fel a különböző oszlop típusokat és azok alkalmazását a SharePoint rendszeren belül.
A Word 2013 szövegtörlésének képessége kulcsfontosságú, legyen szó szövegalkotásról vagy törlésről. Ismerje meg a hatékony szövegtörlési módszereket!
A fotóalbum funkció a PowerPoint 2016-ban lehetővé teszi, hogy egyszerre több fényképet illesszen be egy prezentációba, megkönnyítve ezzel a többszörös képek kezelését.
