Adatok csoportosítása Excel pivot táblába. Példák a dátumok, a szám és a szövegmezők csoportosítására. A csoportosítási problémák elhárítása és a problémák elkerülése.
Adatok csoportosítása
Egy kimutató táblázatban csoportosíthatja a dátumokat, a számot és a szövegmezőket. Például csoportosíthatja a sorrend dátumát év és hónap szerint, vagy csoportosíthatja a teszt pontszámokat 10-es sávokban. Manuálisan kiválaszthatja a szöveges elemeket az elforduló tábla mezőben, és csoportosíthatja a kijelölt elemeket. Ez lehetővé teszi, hogy gyorsan láthassa az elemeinek egy részhalmazának részösszegeit a kimutató táblázatban.
Ez a videó bemutatja, hogyan csoportosítsa a dátumokat hónap és év szerint, és hogyan csoportosítsa a szöveges elemeket manuálisan. Írásbeli lépések vannak a videó alatt.
Automatikus dátumcsoportosítás
Az Excel 2016 és újabb verzióiban a dátumok automatikusan csoportosulnak, amikor dátummezőt ad hozzá a kimutató táblához. Visszavonhatja a csoportosítást az Excel 2016 alkalmazásban, és kikapcsolhatja ezt a szolgáltatást a későbbi verziókban.
Az automatikus dátumcsoportosítás kikapcsolása
Az Excel 2019 és az Excel 365 alkalmazásban módosíthatja az Excel opciót beállítás, ennek a dátumcsoportosítási funkciónak a be- vagy kikapcsolásához.
MEGJEGYZÉS: Ez egy alkalmazásszintű beállítás, és hatással lesz az összes Excel munkafüzetére.
Az automatikus dátumcsoportosítási funkció bekapcsolása ki:
- A szalagon kattintson a Fájl fülre, majd az Opciók gombra.
- A bal oldalon kattintson az Adatok kategóriára.
- A az Adatbeállítások szakaszban jelölje be a “Dátum / idő oszlopok automatikus csoportosításának letiltása a kimutatásban” elemet.
- Kattintson az OK gombra az új beállítások alkalmazásához.
A dátumcsoportosítás visszavonása
Az összes verziónál (Excel 2016 és újabb) a dátumokat manuálisan szétválaszthatja a következő módszerek egyikével :
Billentyűparancs
- Adja hozzá a Dátum mezőt az elfordulási táblához
- A visszavonáshoz nyomja meg azonnal a Ctrl + Z billentyűkombinációt az automatikus dátumcsoportosítás
- A csoportosított mezők eltávolításra kerülnek, és az eredeti Dátum mező megmarad a kimutató táblában
Parancs csoportosításának megszüntetése
- Adja hozzá a Dátum mezőt az elfordulási táblához
- Bármikor kattintson a jobb gombbal a dátummező bármely elemére vagy fejlécére.
- A jobb egérgombbal kattintson a Csoport leválasztása gombra. >
- A csoportosított mezők eltávolításra kerülnek, és az eredeti Dátum mező a kimutató táblában marad.
Dátumok csoportosítása
Egy kimutató táblában több beépített a dátum csoportosításának lehetőségeiben – másodpercek, percek, órák, napok, hónapok, negyedek és évek.
Kiválaszthat egy vagy több opciót a kívánt dátumcsoport-típus létrehozásához. Az alábbi példák bemutatják, hogyan kell csoportosítani a dátumokat:
– hónap és év szerint
– hétenként
– 4 hetes időszak szerint
Dátumok csoportosítása hónap és év szerint
Az elemek csoportosítása egy Dátum mezőbe
- Kattintson a jobb gombbal az egyik dátumra a kimutató táblázatban.
- A felugró menüben kattintson a Csoport
- A Csoportosítás párbeszédpanelen jelöljön ki egy vagy több lehetőséget a a “By” listát.
- A csoportosított dátumok korlátozásához beállíthat egy Start és End dátumot, beírva a dátumokat a “Kezdő dátum” és “Befejezés dátum” mezőbe
- Kattintson az OK gombra a párbeszédpanel bezárásához.
Dátumok csoportosítása hetenként
A Dátum mező elemeinek csoportosítása hetenként
- Kattintson a jobb gombbal az egyik dátumra a kimutatási táblázatban.
- A felugró menüben kattintson a Csoport
- A Csoportosítás párbeszédpanelen válassza a Napok elemet a “By” listából.
- A “Napok száma” mezőbe válassza a 7 elemet.
- A hét tartománya meghatározva a “Kezdés” mezőben található dátum szerint, ezért szükség esetén állítsa be ezt. Az alábbi példában 2012. január 1-jét írták be kezdő dátumként.
- Kattintson az OK gombra
Dátumok csoportosítása 4 hetes időszakok szerint
Nézze meg ezt a videót, hogy megtudja, hogyan csoportosíthatja a dátummezőt 4 hetes időszakokra, és állítsa be a kezdő dátumot. Írásbeli utasítások találhatók a videó alatt.
Csoportosítási dátumok 4 hetes időszakok szerint
Ebben a példában a kint lévő cégnek évente 13 értékesítési periódusa van, minden időszakban 4 hét. A pivot tábla Csoportosítás párbeszédpanelen itt nincs lehetőség a hetek szerinti csoportosításra. Ehelyett használhatjuk a Days opciót.
Egy kis egyszerű számtannal kiszámíthatjuk az időszakonkénti napok számát:
- időszakonként 4 hét
- hetente 7 nap
- 7 x 4 = 28 nap per időszakonként
Kövesse az alábbi lépéseket a dátumok 4 hetes időszakokra történő csoportosításához:
- Kattintson a jobb gombbal dátumot, és kattintson a Csoport gombra.
- A Csoportosítás ablakban kattintson a Napok elemre, hogy kiválassza azt
- Ha más csoportosítási típus van kiválasztva, kattintson rájuk, és törölje a kijelölést
- Állítsa be a napok számát 28-ra
- MEGJEGYZÉS: Ha a dátumokat 1 hetes időszakokra kívánja csoportosítani, a 7-et használja a napok számaként.
Az alapértelmezett kezdési dátum használata
Kezdő dátumként az Excel automatikusan kiválasztja a forrásadatok első dátumát. A fenti képernyőképen láthatja, hogy 2013. január 1-je a kezdő dátum ezekben a forrásadatokban.
Ha bejelentkezem az Outlook programba, ez a dátum keddre esik.
Ha az OK gombra kattintok a Csoportosítás ablakban, az Excel kedden kezdődő periódusokat hoz létre. Az alább látható csoportosított dátumokban az első időszak 2013. január 1-jétől keddtől 2013. január 28-ig, hétfőig tart.
Javítsa ki a kezdő dátumot
Az alapértelmezett kezdő dátum helyett más dátumot is megadhat, hogy megkapja a szükséges kezdési dátumot.
Ebben a példában Megváltoztatom a kezdő dátumot 2012. december 31-re. Ez a dátum hétfőre esik, ezért ez arra kényszeríti az összes csoportosulást, hogy a cég értékesítési időszakainak megfelelő hétfőn induljanak.
A kezdő dátum rögzítéséhez:
- Kattintson a jobb gombbal az egyik csoportosított dátum cellára a kimutatási táblázatban.
- Kattintson a Csoport elemre
- A Csoportosítás ablakban módosítsa a kezdő dátumot – 2012. december 31.
- MEGJEGYZÉS: A “Kezdés” automatikus jelölőnégyzet automatikusan törlődik – hagyja bejelölés nélkül.
- Kattintson az OK
Most az időszakok helyesen vannak csoportosítva, az egyes periódusok kezdő dátumaként hétfő van.
Dátum szerinti csoportosítás extra elemeket ad hozzá
Nézze meg ezt a rövid videót, és nézze meg, hogyan lehet elrejteni a dátummező csoportosításakor létrehozott további elemek szövegét, vagy idővonal az Excel 2013-ban, dátumperiódus szerint történő szűréshez. Írásbeli utasítások találhatók a videó alatt.
Töltse le a minta fájlt a dátumcsoportosítás további tételeihez. A tömörített fájl xlsx formátumban van, és nem tartalmaz makrókat.
Dátum szerinti csoportosítás további elemeket ad hozzá
Ezeket az extra elemeket nem lehet eltávolítani, de megváltoztathatja a feliratokat, helyettesítheti a szöveget szóközökkel. Vagy adjon hozzá új mezőket a forrásadatokhoz az év és az év / hónap kiszámításához az egyes dátumokra, és csoportosítás helyett használja az új mezőket a kimutató táblában.
Az Excel 2013-ban az Idővonal szűrő segítségével gyorsan szűrhet év, negyedév, hónap vagy nap szerint.
Töltse le a minta fájlt az extra tételek dátumcsoportosításban. A tömörített fájl xlsx formátumú, és nem tartalmaz makrókat.
Számok csoportosítása
A csoportosítással tömör összefoglalót készíthet, ahelyett, hogy az egyes számok hosszú listáját jelenítené meg. Ez a videó bemutatja a lépéseket, és az írásbeli utasítások a videó alatt találhatók.
Számok csoportosítása
Ahelyett, hogy a Sor mezőbe külön-külön felsorolná a számokat, csoportosíthatja őket, létrehozhat egy az adatok tömör összefoglalása.
Ebben a példában az eredeti forgástábla mutatja az eladott egységeket termékenként. A C oszlopban a megrendelések száma látható, ahol az adott mennyiséget eladták.
A megrendelések összegzéséhez csoportosítsa az egységeket tízekkel. Tehát ahelyett, hogy 3 és 9 külön sorokban jelenne meg, azokat az 1-10. Csoportba foglaljuk.
A számok csoportosításához
- Kattintson a jobb gombbal az egyikre az egységszámok a pivot táblában.
- A felugró menüben kattintson a Csoport elemre.
- A Csoportosítás párbeszédpanelen írja be az 1 értéket a Kezdés: mezőbe
- Ebben a példában a legnagyobb egységszám 50, és szükség esetén nagyobb számot is beírhat.
- Kattintson az OK gombra a csoportosítás alkalmazásához
A pivot táblában az egységek mostantól csoportosan jelennek meg – 1 – 10, 11 – 20 stb.
A tömörebb elrendezés érdekében áthelyezheti a csoportosított egységek mezőjét az Oszlopok területre, fejlécként.
Szöveges elemek csoportosítása
A Csoportosítás párbeszédpanel nem érhető el, ha elemeket próbál meg csoportosítani egy szövegmezőbe. Ehelyett manuálisan csoportosíthatja az elemeket.
Nézze meg ezt a videót, ha meg szeretné tekinteni a csoportok szöveges elemekből történő létrehozásának lépéseit. Ezután nevezzen át egy csoportot, vagy adjon hozzá további elemeket. Később leválaszthat egy vagy több csoportot. Írásbeli utasítások találhatók a videó alatt.
Szöveges elemek csoportosítása
A Csoportosítás párbeszédpanel nem érhető el, ha elemeket próbál meg csoportosítani egy szövegmezőbe. Ehelyett manuálisan is csoportosíthat. az elemeket.
- Jelölje ki a csoportosítani kívánt elemeket
- Kattintson a jobb gombbal az egyik kijelölt elemre, majd kattintson a Csoport
Ez új mezőt hoz létre a mezőlistában, az eredeti mező nevével és számával, például City2
Az új mező az eredeti mező fölött helyezkedik el a kimutatási táblázat elrendezésében, és a csoportosított elemeknek általános neve van, például Group1.
A csoport átnevezéséhez:
- Kattintson az alapértelmezett névvel ellátott fejléc cellákra , például 1. csoport
- Írjon be egy új nevet, például az A kampányt.
Hozzáadás további elemek a csoporthoz:
- Jelölje ki a csoport összes elemét
- Nyomja meg a Ctrl billentyűt, és válassza ki azokat az elemeket, amelyeket hozzá kíván adni a csoporthoz
- Kattintson a jobb gombbal az egyik kijelölt elemre, majd kattintson a Csoport elemre.
- MEGJEGYZÉS: Ha a csoportot átnevezték, akkor a név felülíródik egy alapértelmezett névvel.
Szöveg elemek csoportosításának megszüntetése
A szöveges elemek kézi csoportosítása után érdemes leválasztani néhányat vagy az összeset, csoportok csoportosítása egy pivot mezőben.
Egyetlen csoport csoportjának bontása:
- Kattintson a jobb gombbal a csoport egyik elemére
- Kattintson a Csoport eltávolítása, és a csoport visszatér az egyes tárgyakhoz.
Egyetlen csoport csoportjának leválasztása:
- Kattintson a tetejére egy csoportfejlécet, vagy kattintson a pivot mező tetejére az összes elem kijelöléséhez
- Kattintson a jobb gombbal egy elemen a csoportban
- Kattintson a Csoport eltávolítása gombra, és az összes kijelölt csoport visszaáll az egyes elemekre.
Problémák az adatok csoportosításakor
Ha megpróbál dátumot vagy számot csoportosítani mezőben megjelenhet egy hibaüzenet, amely azt mondja: “A csoport nem csoportosítható”. Ennek oka lehet az adatmodell, vagy üres cellák, vagy korábban csoportosított elemek. Lásd az alábbi részleteket.
Adatmodell
A pivot tábla létrehozásakor, ha bejelölte az “Adatok hozzáadása az adatmodellhez” jelölőnégyzetet, akkor nem fogja tudni csoportosítani az elemeket. Normál pivot tábla helyett ez egy OLAP-alapú Power Pivot, és a csoportosítási funkció nem érhető el.
Üres cellák vagy szöveg
Normál pivot táblában (nem az adatmodellben) a csoportosítás A probléma általában akkor fordul elő, ha a mező rekordokat tartalmaz
- egy üres cellával a dátum / szám mezőben, vagy
- szöveges bejegyzéssel a dátum / szám mezőben.
A probléma kijavításához
- Üres cellák esetén töltse ki a dátumot / számot (szükség esetén használjon dummy dátumot / számot).
- Ha van szöveg a dátum / szám mezőben, távolítsa el.
- Ha a számokat szövegként ismerjük fel, az egyik technika segítségével módosítsa a szöveget valós számokra. Ezután térjen vissza ehhez a kimutatótáblához, és próbálja meg a csoportosítási lépéseket.
Előző csoportosítás
Ha a dátum oszlopban nincsenek üres cellák vagy szövegek, előfordulhat, hogy egy csoportosított mező maradt az adatok előző csoportosításakor.
- Ellenőrizze a mezőlistát, hogy van-e a dátummező második példánya, pl Dátum2.
- Ha van, vegye fel a sorterületre, és bontsa ki a csoportosítását.
- Ezután újra képesnek kell lennie a dátum mező csoportosítására.
Az egyik kimutatótáblába csoportosítás hatással van a másikra
Ha két kimutatótáblát hoz létre ugyanazon Excel táblázat alapján a Az Excel 2007 alkalmazásban, amikor az egyik pivot-tábla csoportosítását megváltoztatja, ugyanaz a csoportosítás jelenik meg a másik pivot-táblában.
Mivel a két kimutató táblázatot ugyanazokból a forrásadatokból hoztad létre, alapértelmezés szerint ugyanazt a kimutatási gyorsítótárat használják, a csoportosítva elemek mindkét táblázatban megegyeznek.
Az egyes pivot-táblákban különböző csoportosításhoz külön pivot-gyorsítótárat kell létrehoznia az egyes pivot-táblákhoz.
Használja a következő módszert, amelyet az Excel hírcsoportjaiban javasolt Dave Peterson.
Külön pivot-gyorsítótár létrehozása a második pivot-tábla számára:
- Vágja ki a második pivot-táblát, és illessze be egy új munkafüzetbe.
- Módosítsa a második pivot tábla csoportosítását.
- Vágja ki a második pivot táblázatot az új munkafüzetből, és illessze vissza az eredeti munkafüzetbe.
Töltse le a Mintafájl
Töltse le az oktatóanyag tömörített mintafájlját
Pivot Table Tools
Időt takaríthat meg a pivot táblák összeállításakor, formázásakor és módosításakor. a Pivot Power Premium bővítményemben. Csak néhány kattintással a következőket teheti:
- másolhatja a formázást az egyik pivot táblából, és alkalmazhatja egy másik pivot táblára.
- módosítsa az összes értéket Count-ról összegre
- távolítsa el az “Sum of” értéket az összes címsorból
és még sok minden más!
További oktatóanyagok
Régi elemek törlése a kimutató táblában
Számított mező – Számlálás
Számított Tételek
Összefoglaló funkciók