Tietojen ryhmittely Excel-pivot-taulukkoon. Esimerkkejä päivämäärien, numeroiden ja tekstikenttien ryhmittelystä. Ryhmittelyongelmien vianmääritys ja ongelmien välttäminen.
Tietojen ryhmittely
Pivot-taulukossa voit ryhmitellä päivämäärät, numerot ja tekstikentät. Voit esimerkiksi ryhmitellä päivämäärät vuoden ja kuukauden mukaan tai ryhmitellä testitulokset 10: n kaistalla. Voit valita manuaalisesti pivot-taulukon kentässä olevat tekstikohdat ja ryhmitellä valitut kohteet. Tämän avulla voit nopeasti nähdä välisummat tietylle alkion joukolle pivot-taulukossa.
Tässä videossa näytetään, miten päivämäärät ryhmitellään kuukausien ja vuosien mukaan ja miten ryhmitellään tekstikohteet manuaalisesti. Videon alla on kirjoitettuja vaiheita.
Automaattinen päivämäärien ryhmittely
Excel 2016: ssa ja uudemmissa versioissa päivämäärät ryhmitellään automaattisesti, kun lisäät päivämääräkentän pivot-taulukkoon. Voit kumota ryhmittelyn Excel 2016: ssa ja poistaa ominaisuuden käytöstä myöhemmissä versioissa.
Poista automaattinen päivämäärän ryhmittely käytöstä
Excel 2019: ssä ja Excel 365: ssä voit muuttaa Excel-asetusta asetus, tämän päivämäärän ryhmittelyominaisuuden ottaminen käyttöön tai poistaminen käytöstä
HUOMAUTUS: Tämä on sovellustason asetus ja vaikuttaa kaikkiin Excel-työkirjoihisi.
Automaattisen päivämäärän ryhmittelyominaisuuden ottaminen käyttöön pois päältä:
- Napsauta valintanauhassa Tiedosto-välilehteä ja valitse sitten Asetukset
- Napsauta vasemmalla olevaa Tietoluokka
- Tietoasetukset-osiossa, lisää valintamerkki ”Poista pvot-taulukoiden päivämäärä / aika -sarakkeiden automaattinen ryhmittely käytöstä”
- Ota uudet asetukset käyttöön napsauttamalla OK.
Kumoa päivämäärän ryhmittely
Kaikissa versioissa (Excel 2016 ja uudemmat) voit purkaa päivämäärät manuaalisesti yhdellä näistä tavoista :
pikanäppäin
- Lisää päivämäärä-kenttä pivot-taulukkoon
- Kumoa painamalla heti Ctrl + Z automaattinen päivämäärän ryhmittely
- Ryhmitetyt kentät poistetaan ja alkuperäinen Päivämäärä-kenttä pysyy pivot-taulukossa
Poista komento ryhmästä
- Lisää päivämäärä-kenttä pivot-taulukkoon
- Napsauta milloin tahansa hiiren kakkospainikkeella mitä tahansa päivämääräkentän kohtaa tai otsikkoa
- Napsauta hiiren kakkospainikkeella valikossa Poista ryhmä
- Ryhmitetyt kentät poistetaan ja alkuperäinen Päivämäärä-kenttä pysyy pivot-taulukossa.
Päivämäärän ryhmittely
Pivot-taulukossa on useita sisäänrakennettuja päivämäärien ryhmittelyvaihtoehdoissa – sekunnit, minuutit, tunnit, päivät, kuukaudet, vuosineljännekset ja vuodet.
Voit valita yhden tai useamman näistä vaihtoehdoista luodaksesi tarvitsemasi päivämääräryhmittelytyypin. Alla olevat esimerkit osoittavat päivämäärien ryhmittelyn:
– Kuukauden ja vuoden mukaan
– Viikon mukaan
– 4 viikon jakson mukaan
Ryhmittele päivämäärät kuukauden ja vuoden mukaan
Ryhmittele päivämääräkentän kohteet
- Napsauta hiiren kakkospainikkeella yhtä pivot-taulukon päivämääriä.
- Valitse ponnahdusvalikosta Ryhmä
- Valitse Ryhmittely-valintaikkunassa yksi tai useampi vaihtoehto ”By” -luettelosta.
- Voit rajoittaa ryhmiteltyjä päivämääriä asettamalla aloitus- ja lopetuspäivämäärät kirjoittamalla päivämäärät ”Alkaen” ja ”Loppuun klo” -ruutuihin
- Sulje valintaikkuna napsauttamalla OK.
Ryhmitä päivämäärät viikoittain
Päivämäärä-kentän kohteiden ryhmittely viikkoittain
- Napsauta hiiren kakkospainikkeella yhtä pivot-taulukon päivämääriä.
- Valitse ponnahdusvalikosta Ryhmittele
- Valitse Ryhmittely-valintaikkunassa Päivät By-luettelosta.
- Valitse Päivien määrä -kohdasta 7
- Viikkoväli määritetään päivämäärän mukaan ”Alkaen” -ruudussa, joten säädä tätä tarvittaessa. Alla olevassa esimerkissä 1. tammikuuta 2012 annettiin aloituspäivä.
- Napsauta OK
Ryhmittele päivämäärät 4 viikon jaksojen mukaan
Katso tämä video, niin näet kuinka päivämääräkenttä ryhmitellään 4 viikon jaksoina, ja aseta aloituspäivä. Videon alla on kirjalliset ohjeet.
Ryhmäpäivämäärät 4 viikon jaksoittain
Tässä esimerkissä Out-yhtiöllä on 13 myyntijaksoa vuodessa, 4 viikkoa kussakin jaksossa. Tässä ei ole pivot-taulukon ryhmittely -valintaikkunan vaihtoehtoa ryhmittelemiseksi viikkojen mukaan. Sen sijaan voimme käyttää Days-vaihtoehtoa.
Hieman yksinkertaisella laskutoimituksella voimme laskea päivien lukumäärän jaksossa:
- 4 viikkoa jaksossa
- 7 päivää viikossa
- 7 x 4 = 28 päivää jaksossa
Noudata näitä vaiheita ryhmitelläksesi päivämäärät neljän viikon jaksoihin:
- Napsauta hiiren kakkospainikkeella päivämääränä ja napsauta Ryhmä
- Valitse Ryhmä-ikkunassa Päivät ja valitse se
- Jos muita ryhmitystyyppejä on valittu, napsauta niitä ja poista niiden valinta
- Aseta päivien lukumääräksi 28
- HUOMAUTUS: Jos haluat ryhmitellä päivämäärät yhden viikon jaksoiksi, käytä päivien lukumääränä 7.
Oletusaloituspäivän käyttäminen
Aloituspäivänä Excel valitsee automaattisesti ensimmäisen päivämäärän lähdetiedoissasi. Yllä olevasta kuvakaappauksesta näet, että 1.1.2013 on näiden lähdetietojen aloituspäivä.
Jos tarkistan Outlookissa, kyseinen päivämäärä on tiistai.
Jos napsautan Ryhmä-ikkunassa OK, Excel luo tiistaina alkavat jaksot. Alla esitetyissä ryhmitellyissä päivämäärissä ensimmäinen jakso alkaa tiistaista 1. tammikuuta 2013 maanantaihin 28. tammikuuta 2013.
Korjaa aloituspäivä
Oletusaloituspäivän sijasta voit antaa toisen päivämäärän, jotta saat tarvitsemasi alkamispäivän.
Tässä esimerkissä Muutan aloituspäivän 31. joulukuuta 2012. Tämä päivämäärä on maanantai, joten tämä pakottaa kaikki ryhmittymät aloittamaan maanantaina, joka vastaa yrityksen myyntijaksoja.
Aloituspäivän korjaamiseksi:
- Napsauta hiiren kakkospainikkeella yhtä pivot-taulukon ryhmitellyistä päivämääräsoluista
- Napsauta Ryhmä
- Muuta Ryhmittely-ikkunassa aloituspäivä – 31. joulukuuta 2012
- HUOMAUTUS: Automaattinen -valintaruutu ”Aloitus klo” tyhjennetään automaattisesti – jätä se valitsematta.
- Napsauta OK
Nyt jaksot on ryhmitelty oikein, ja kunkin jakson alkamispäivänä on maanantai.
Ryhmittely päivämäärän mukaan lisää ylimääräisiä kohteita
Katso tämä lyhyt video nähdäksesi kuinka piilottaa teksti ylimääräisissä kohteissa, jotka luodaan, kun ryhmität päivämääräkentän tai käytät aikajanan Excel 2013: ssa suodattamaan päivämääräjakson mukaan Videon alla on kirjalliset ohjeet.
Lataa esimerkkitiedosto päivämääräryhmän ylimääräisille kohteille. Pakattu tiedosto on xlsx-muodossa, eikä se sisällä makroja.
Ryhmittely päivämäärän mukaan lisää ylimääräisiä kohteita
Näitä ylimääräisiä kohteita ei voida poistaa, mutta voit muuttaa tekstityksiä ja korvata tekstin välilyönneillä. Tai lisää uusia kenttiä lähdetietoihin laskeaksesi vuosi ja vuosi / kuukausi jokaiselle päivämäärälle ja käytä uusia taulukon kenttiä ryhmittelyn sijaan.
Excel 2013: ssa voit suodata aikajanan suodattimen avulla nopeasti vuoden, vuosineljänneksen, kuukauden tai päivän mukaan.
Lataa esimerkkitiedosto ylimääräistä varten kohteet päivämääräryhmässä. Pakattu tiedosto on xlsx-muodossa, eikä siinä ole makroja.
Ryhmien numerot
Luo tiivis yhteenveto ryhmittelyllä sen sijaan, että näytettäisiin pitkä luettelo yksittäisistä numeroista. Tässä videossa näytetään vaiheet, ja kirjalliset ohjeet ovat videon alapuolella.
Numeroiden ryhmittely
Sen sijaan, että luettaisit numeroita erikseen Rivi-kentässä, voit ryhmitellä ne ja luoda tiivis yhteenveto tiedoista.
Tässä esimerkissä alkuperäinen kääntötaulukko näyttää myytävät yksiköt tuotteittain. Sarakkeessa C se näyttää tilausten lukumäärän, joissa kyseinen määrä myytiin.
Yhteenvetona tilauksista ryhmittele yksiköt kymmenillä. Joten sen sijaan, että 3 ja 9 näkyvät erillisissä riveissä, ne ”tiivistetään ryhmään 1-10.
Numeroiden ryhmitteleminen
- Napsauta hiiren kakkospainikkeella yhtä pivot-taulukon yksiköiden numeroista.
- Valitse ponnahdusvalikosta Ryhmä
- Kirjoita Ryhmittely-valintaikkunaan 1 Aloitus-kenttään
- Tässä esimerkissä suurin yksikkömäärä on 50, ja voit kirjoittaa tarvittaessa suuremman luvun.
- Ota ryhmittely käyttöön napsauttamalla OK
Pivot-taulukossa yksiköt näkyvät nyt ryhmissä – 1 – 10, 11 – 20 jne.
Lyhyemmän asettelun tekemiseksi voit siirtää ryhmiteltyjen yksiköiden kentän Sarakkeet-alueeksi otsikoina.
Tekstikohteiden ryhmittely
Ryhmittely-valintaikkuna ei ole käytettävissä, jos yrität ryhmittää kohteita tekstikenttään. Sen sijaan voit ryhmitellä kohteet manuaalisesti.
Katso tämä video nähdäksesi ohjeet ryhmien luomiseen tekstikohteista. Nimeä sitten ryhmä uudelleen tai lisää muita kohteita. Myöhemmin voit purkaa yhden tai useamman ryhmän. Videon alla on kirjalliset ohjeet.
Tekstikohteiden ryhmittely
Ryhmittely-valintaikkuna ei ole käytettävissä, jos yrität ryhmittää kohteita tekstikenttään. Voit sen sijaan ryhmittää manuaalisesti. kohteet.
- Valitse kohteet, jotka haluat ryhmitellä
- Napsauta hiiren kakkospainikkeella yhtä valituista kohteista ja valitse Ryhmä
Tämä luo kenttäluetteloon uuden kentän, jossa on alkuperäinen kentän nimi ja numero, kuten Kaupunki2
Uusi kenttä on sijoitettu pivot-taulukon alkuperäisen kentän yläpuolelle, ja ryhmitellyillä kohteilla on yleinen nimi, kuten Group1.
Ryhmän uudelleennimeäminen:
- Napsauta otsikkosoluja oletusnimellä , esimerkiksi Ryhmä1
- Kirjoita uusi nimi, kuten kampanja A.
Lisää lisää kohteita ryhmään:
- Valitse kaikki ryhmän kohteet
- Paina Ctrl-näppäintä ja valitse kohteet, jotka haluat lisätä ryhmään
- Napsauta hiiren kakkospainikkeella yhtä valituista kohteista ja valitse Ryhmä
- HUOMAUTUS: Jos ryhmä nimetään uudelleen, kyseinen nimi korvataan oletusnimellä.
Tekstikohteiden ryhmittelyn purkaminen
Kun olet ryhmitellyt tekstikohteet manuaalisesti, voit halutessasi purkaa osan tai kaikki, ryhmän pivot-kentässä.
Yksittäisen ryhmän ryhmittelyn purkaminen:
- Napsauta hiiren kakkospainikkeella ryhmän kohdetta
- Valitse Poista ryhmä, ja ryhmä palaa yksittäisiin kohteisiin.
Yhden ryhmän ryhmittelyn purkaminen:
- Napsauta yläreunaa tai valitse kaikki kohteet napsauttamalla pivot-kentän yläosaa
- Napsauta hiiren kakkospainikkeella ryhmän alkion kohdalla
- Napsauta Poista ryhmä ja kaikki valitut ryhmät palaavat yksittäisiin kohteisiin.
Ongelmia tietojen ryhmittelyssä
Jos yrität ryhmitellä päivämäärää tai numeroa -kentässä saatat nähdä virheilmoituksen, joka sanoo ”Valintaa ei voi ryhmittää”. Tämä voi johtua tietomallista, tyhjistä soluista tai aiemmin ryhmitellyistä kohteista. Katso yksityiskohdat alla.
Tietomalli
Jos olet luonut pivot-taulukon, ”Lisää nämä tiedot tietomalliin” -ruudussa et voi ryhmittää kohteita. Normaalin pivot-taulukon sijaan se on OLAP-pohjainen Power Pivot, ja ryhmittelytoiminto ei ole käytettävissä.
Tyhjät solut tai teksti
Normaalissa pivot-taulukossa (ei tietomallissa) ryhmittely ongelma ilmenee yleensä, kun kenttä sisältää tietueita, joissa
- päiväys / numero-kentässä on tyhjä solu tai
- päivämäärä / numero-kentän tekstimerkintä.
Korjaa ongelma
- Tyhjät solut täytä päivämäärä / numero (käytä tarvittaessa nuken päivämäärää / numeroa).
- Jos päivämäärä / numero-kentässä on tekstiä, poista se.
- Jos numerot tunnistetaan tekstiksi, vaihda teksti reaalilukuiksi jollakin tekniikoista. Palaa sitten tähän pivot-taulukko-oppaaseen ja kokeile ryhmittelyvaiheita.
Edellinen ryhmittely
Jos päiväyssarakkeessa ei ole tyhjiä soluja tai tekstiä, edellisestä tietojen ryhmittelyhetkestä saattaa olla jäljellä ryhmitelty kenttä.
- Tarkista kenttäluettelo, onko päivämääräkentästä toinen kopio, esim Päivämäärä 2.
- Jos on, lisää se rivialueelle ja pura se ryhmästä.
- Sitten sinun pitäisi pystyä ryhmittelemään päivämääräkenttä uudelleen.
Yhden pivot-taulukon ryhmittely vaikuttaa toiseen
Jos luot kaksi pivot-taulukkoa saman Excel-taulukon perusteella Excel 2007, kun muutat ryhmittelyä yhdessä pivot-taulukossa, sama ryhmittely näkyy toisessa pivot-taulukossa.
Koska olet luonut kaksi pivot-taulukkoa samoista lähdetiedoista, oletusarvoisesti ne käyttävät samaa pivot-välimuistia, ryhmiteltyinä kohteet ovat samat molemmissa taulukoissa.
Jos haluat käyttää eri ryhmittelyä jokaisessa pivot-taulukossa, sinun on luotava erillinen pivot-välimuisti kullekin pivot-taulukolle.
Käytä seuraavaa menetelmää, jota Dave ehdottaa Excel-uutisryhmissä Peterson.
Erillisen pivot-välimuistin luominen toiselle pivot-taulukolle:
- Leikkaa toinen pivot-taulukko ja liitä se uuteen työkirjaan.
- Muuta toisen pivot-taulukon ryhmittelyä.
- Leikkaa toinen pivot-taulukko uudesta työkirjasta ja liitä se takaisin alkuperäiseen työkirjaan.
Lataa Esimerkkitiedosto
Lataa tämän opetusohjelman pakattu näytetiedosto
Pivot-taulukon työkalut
Käytä työkaluja säästääksesi aikaa pivot-taulukoiden rakentamisessa, muotoilussa ja muokkauksessa Pivot Power Premium -lisäosassani. Vain muutamalla napsautuksella voit:
- kopioida muotoilun yhdestä pivot-taulukosta ja soveltaa sitä toiseen pivot-taulukkoon.
- muuta kaikki arvot laskusta summaan
- poista ”Summa” kaikista otsikoista
ja paljon muuta!
Lisää oppaita
Tyhjennä vanhat kohteet pivot-taulukossa
Laskettu kenttä – Määrä
Laskettu Kohteet
Yhteenvetotoiminnot