Pivot -välimuisti Excelissä - mikä se on ja miten sitä käytetään parhaiten

Jos työskentelet Excel -pivot -taulukoiden kanssa, Pivot -välimuisti on ehdottomasti tiedettävä asia.

Mikä on Pivot -välimuisti?

Pivot -välimuisti luodaan automaattisesti, kun luot pivot -taulukon.

Se on objekti, joka sisältää kopion tietolähteestä. Vaikka et näe sitä, se on osa työkirjaa ja yhdistetty pivot -taulukkoon. Kun teet muutoksia pivot -taulukkoon, se ei käytä tietolähdettä, vaan käyttää pivot -välimuistia.

Pivot -välimuisti luodaan siksi, että pivot -taulukon toiminta on optimoitu. Vaikka sinulla olisi tuhansia rivejä tietoja, pivot -taulukko on erittäin nopea tiivistämään sen. Voit vetää ja pudottaa kohteita rivien/sarakkeiden/arvojen/suodattimien laatikoihin ja se päivittää tulokset heti.

Pivot -välimuisti mahdollistaa tämän pivot -taulukon nopean toiminnan.

Vaikka luulet olevasi suoraan yhteydessä lähdetietoihin, todellisuudessa käytät pivot -välimuistia (etkä lähdetietoja), kun teet muutoksia pivot -taulukkoon.

Tästä syystä sinun on päivitettävä pivot -taulukko vastaamaan kaikkia tietojoukkoon tehtyjä muutoksia.

Pivot -välimuistin sivuvaikutukset

Yksi kääntövälimuistin haittapuoli on se, että se lisää työkirjasi kokoa.

Koska se on kopio lähdetiedoista, kun luot pivot -taulukon, kopio näistä tiedoista tallennetaan Pivot -välimuistiin.

Kun luot pivot -taulukon suurilla tietojoukoilla, työkirjan tiedoston koko kasvaa merkittävästi.

Pivot -välimuistin jakaminen

Excel 2007: stä alkaen, jos sinulla on jo pivot -taulukko ja luot uuden pivot -taulukon käyttäen samaa lähdetietoa, Excel jakaa automaattisesti pivot -välimuistin (mikä tarkoittaa, että molemmat pivot -taulukot käyttävät samaa pivot -välimuistia). Tästä on hyötyä, koska se välttää pivot -välimuistin päällekkäisyyksiä ja puolestaan ​​johtaa vähemmän muistin käyttöön ja pienempään tiedoston kokoon.

Jaetun Pivot -välimuistin rajoitukset

Vaikka jaettu pivot -välimuisti parantaa pivot -taulukon toimintaa ja muistin käyttöä, se kärsii seuraavista rajoituksista:

• Kun päivität yhden pivot -taulukon, kaikki samaan välimuistiin linkitetyt pivot -taulukot päivitetään.
• Kun ryhmität kentät yhteen pivot -taulukoista, sitä sovelletaan kaikkiin pivot -taulukoihin samaa pivot -välimuistia käyttäen. Jos esimerkiksi ryhmittelet päivämäärät kuukausittain, tämä muutos näkyy kaikissa pivot -taulukoissa.
• Kun lisäät lasketun kentän/kohteen johonkin pivot -taulukkoon, se näkyy kaikissa pivot -taulukoissa, jotka jakavat pivot -välimuistin.

Näiden rajoitusten kiertäminen on pakottaa Excel luomaan erillinen pivot -välimuisti eri pivot -taulukoille (samalla tietolähteellä).

Huomautus: Jos käytät eri tietolähteitä eri pivot -taulukoille, Excel luo sille automaattisesti erilliset pivot -välimuistit.

Päällekkäisen pivot -välimuistin luominen (samalla tietolähteellä)

Tässä on 3 tapaa luoda kaksoiskapivivälimuisti samalla kun luodaan pivot -taulukoita samasta tietolähteestä:

#1 Erilaisten taulukonimien käyttäminen

• Napsauta mitä tahansa tietolähdettä ja siirry kohtaan Lisää -> Taulukko (tai voit käyttää pikanäppäintä - Control + T).
• Napsauta Luo taulukko -valintaikkunassa OK. Se luo taulukon, jonka nimi on Table1.
• Kun mikä tahansa solu on valittu taulukosta, siirry Lisää -> Pivot -taulukkoon.
• Huomaa Luo pivot -taulukko -valintaikkunassa, että Taulukko/Alue -kentässä on taulukon nimi. Napsauta OK.
  • Tämä luo ensimmäisen pivot -taulukon.
• Siirry tietolähteeseen (taulukko), valitse mikä tahansa solu ja valitse Taulukkotyökalut Suunnittelu -> Työkalut -> Muunna alueeksi. Se näyttää kehotteen, jossa kysytään, haluatko muuttaa taulukon normaalialueeksi. Napsauta Kyllä. Tämä muuntaa taulukon tavallisiksi taulukkotiedoiksi.

Toista nyt yllä olevat vaiheet ja muuta vain taulukon nimi (taulukosta 1 taulukkoon 2 tai mitä haluat). Voit muuttaa sen kirjoittamalla nimen Taulukkotyökalut -välilehden taulukon nimen alapuolella olevaan kenttään.

Vaikka molemmat taulukot (taulukko 1 ja taulukko 2) viittaavat samaan tietolähteeseen, tämä menetelmä varmistaa, että kullekin taulukolle luodaan kaksi erillistä pivot -välimuistia.

#2 Ohjatun vanhan pivot -taulukon käyttäminen

Noudata näitä ohjeita, kun haluat luoda uuden pivot -taulukon erillisellä pivot -välimuistilla samalla tietolähteellä.

• Valitse mikä tahansa solu tiedoista ja paina ALT + D + P.
  • Tämä avaa ohjatun pivot -taulukon ja pivot -kaavion.
• Napsauta vaiheessa 1/3 Seuraava.
• Varmista vaiheessa 2/3, että tietoalue on oikea ja napsauta Seuraava.
• Excel näyttää kehotteen, joka sanoo, että napsauta Kyllä, jos haluat luoda jaetun pivot -välimuistin, ja Ei, jos haluat luoda erillisen pivot -välimuistin.
• Napsauta Ei.
• Valitse ohjatun toiminnon vaiheessa 3, haluatko Pivot -taulukon uuteen laskentataulukkoon tai samaan laskentataulukkoon, ja napsauta sitten Valmis.

Huomautus: Varmista, että tiedot eivät ole Excel -taulukkoa.

Laske pivot -välimuistien määrä

Haluat ehkä laskea pivot -välimuistien määrän vain välttääksesi useita pivot -välimuisteja samasta tietolähteestä.

Tässä on nopea tapa laskea se:

• Avaa VB Editor painamalla ALT + F11 (tai siirry Kehittäjä -välilehteen -> Visual Basic).
• Valitse Visual Basic Editor -valikosta Näytä ja valitse Välitön ikkuna (tai paina Control + G). Tämä tekee välittömästä ikkunasta näkyvän.
• Liitä seuraava koodi välittömään ikkunaan ja paina Enter:

  ActiveWorkbook.PivotCaches.Count

Se näyttää välittömästi työkirjan Pivot -välimuistien määrän.

Suorituskyvyn parantaminen pivot -taulukoita käytettäessä

Voit parantaa työkirjojen suorituskykyä (tiedoston kokoa ja muistin käyttöä) muutamalla tavalla pivot -taulukoiden kanssa:

#1 Poista lähdetiedot

Voit poistaa lähdetiedot ja käyttää vain Pivot -välimuistia. Voit silti tehdä kaiken käyttämällä pivot -välimuistia, koska se sisältää tilannekuvan alkuperäisistä tiedoista. Mutta koska olet poistanut lähdetiedot, työkirjasi tiedoston koko pienenee.

Jos haluat palauttaa lähdetiedot, kaksoisnapsauta kyseisen pivot-taulukon kokonaissummien leikkauspistettä. Se luo uuden laskentataulukon ja näyttää kaikki pivot -taulukon luomiseen käytetyt tiedot.

#2 Älä tallenna tietoja Pivot -välimuistiin

Kun tallennat tiedoston, jossa on pivot -taulukko ja lähdetiedot, se tallentaa myös pivot -välimuistin, jossa on kopio lähdetiedoista. Tämä tarkoittaa, että tallennat lähdetiedot kahteen paikkaan: laskentataulukkoon, jossa tiedot ovat, ja pivot -välimuistiin.

On mahdollista, että tietoja ei tallenneta välimuistiin ja suljetaan. Tämä johtaa pienempään tiedoston kokoon.

Tehdä tämä:

• Valitse mikä tahansa pivot -taulukon solu.
• Valitse Analysoi -> Pivot -taulukko -> Asetukset.
• Siirry Pivot Table Options -valintaikkunassa Data -välilehdelle.
• Poista valinta - Tallenna lähdetiedot tiedostoon.
• Valitse vaihtoehto - Päivitä tiedot, kun avaat tiedoston.
  • Jos et valitse tätä vaihtoehtoa, kun avaat Excel -työkirjan, se ei päivitä tietoja etkä voi käyttää pivot -taulukon toimintoja. Jotta se toimisi, sinun on päivitettävä pivot -taulukko manuaalisesti.

Kun teet tämän, Excel ei tallenna tietoja pivot -välimuistiin, mutta päivittää ne, kun avaat Excel -työkirjan seuraavan kerran. Tiedot voivat olla samassa työkirjassa, jossakin muussa työkirjassa tai ulkoisessa tietokannassa. Kun avaat tiedoston, se päivittää tiedot ja Pivot -välimuisti luodaan uudelleen.

Vaikka tämä saattaa pienentää tiedostokokoa, tiedoston avaaminen voi kestää hieman kauemmin (kun Excel luo uudelleen välimuistin).

Katso myös: Lähdetietojen tallentaminen pivot -taulukon avulla.

Huomautus: Jos käytät tätä vaihtoehtoa, varmista, että tietolähde on ehjä. Jos poistat lähdetiedot (työkirjasta tai mistä tahansa ulkoisesta tietolähteestä), et voi luoda pivot -välimuistia uudelleen.

#3 Pivot -välimuistin jakaminen parantaa suorituskykyä

Jos vahingossa (tai tarkoituksella) joudut tilanteeseen, jossa sinulla on kaksoiskapivivälimuisti ja haluat poistaa kaksoiskappaleen ja jakaa pivot -välimuistin, toimi seuraavasti:

• Poista yksi pivot -taulukoista, joiden välimuistin haluat poistaa. Voit tehdä tämän valitsemalla pivot -taulukon ja valitsemalla Koti -> Tyhjennä -> Tyhjennä kaikki.
• Kopioi nyt yksinkertaisesti pivot -taulukko, jonka haluat kopioida, ja liitä se (joko samaan laskentataulukkoon tai erilliseen laskentataulukkoon).
  • On suositeltavaa liittää se erillisiin laskentataulukoihin, jotta se ei mene päällekkäin toisen pivot -taulukon kanssa, kun laajennat sitä. Vaikka joskus kopioin sen vierekkäin eri näkemysten vertaamiseksi. Tämä pivot -taulukon kopioiminen varmistaa, että pivot -välimuisti on jaettu.

• Microsoft -ohje - Poista välimuistin jakaminen Pivot -taulukon raporttien välillä.

Muut pivot -taulukon opetusohjelmat, joista saatat pitää:

• Lähdetietojen valmistelu pivot -taulukkoa varten.
• Päivämäärien ryhmittely Excelin pivot -taulukoissa.
• Numeroiden ryhmittely Excelin pivot -taulukossa.
• Pivot -taulukon päivittäminen Excelissä.
• Viipaloiden käyttäminen Excel -pivot -taulukossa.
• Excel -pivot -taulukon lasketun kentän lisääminen ja käyttäminen.
• Ehdollisen muotoilun käyttäminen Excel -pivot -taulukossa.