Tässä artikkelissa opimme kuinka päivittää pivot -taulukon tiedot automaattisesti Excelissä.
Skenaario:
Kuten me kaikki tiedämme, aina kun teemme muutoksia pivot -taulukon lähdetietoihin, se ei heijastu välittömästi pivot -taulukkoon. Meidän on päivitettävä pivot -taulukot nähdäksemme muutokset aina, kun avaat Excel -työkirjan. Ja jos lähetät päivitetyn tiedoston päivittämättä pivot -taulukoita, saatat tuntea hämmennystä. Joten opi täältä kuinka löytää päivitysvaihtoehto pivot -taulukkoa käytettäessä
Päivitä tiedot, kun avaat tiedoston Excelissä
Luo ensin pivot -taulukko ja napsauta sitten mitä tahansa pivot -taulukon solua hiiren kakkospainikkeella.
Valitse Pivot -taulukon vaihtoehdot> Tiedot -välilehti> Valitse ruutu, jossa lukee Päivitä tiedot, kun avaat tiedoston
Tämä mahdollistaa tietojen automaattisen päivityksen aina, kun tiedosto avataan.
Esimerkki:
Kaikkien näiden ymmärtäminen voi olla hämmentävää. Ymmärrämme, miten toimintoa käytetään esimerkin avulla. Tässä meillä on joitain tietoja, ja meidän on ensin luotava pivot -taulukko ja löydettävä sitten vaihtoehdot automaattisen päivityksen pivot -taulukoiden ottamiseksi käyttöön.
Luo pivot -taulukko ja napsauta hiiren kakkospainikkeella mitä tahansa pivot -taulukon solua alla olevan kuvan mukaisesti.
Valitse Pivot -taulukon asetukset ja tämä avaa PIvot -taulukon asetukset -valintaikkunan.
Valitse tiedot -välilehti ja valitse sitten ruutu, jossa lukee Päivitä tiedot avattaessa tiedosto. Voit tehdä tämän avaamatta ja sulkematta tiedostoa VBA: n avulla.
VBA: n käyttö
Joten täällä opimme päivittämään pivot -taulukon automaattisesti VBA: n avulla. Tämä tapa on helpompi kuin kuvittelit.
Tämä on yksinkertainen syntaksi päivittää työkirjan pivot -taulukot automaattisesti.
| '' Lähdetietolomakkeen koodi
Yksityinen alityöarkki_Deactivate () taulukon_numeron_taulukko.Pivot -taulukot ("pivot_table_name"). PivotCache. Päivitä End Sub |
Mitä Pivot -välimuistit ovat?
Jokainen pivot -taulukko tallentaa tiedot pivot -välimuistiin. Siksi pivot pystyy näyttämään aiemmat tiedot. Kun päivitämme pivot -taulukoita, se päivittää välimuistin uusilla lähdetiedoilla vastaamaan pivot -taulukon muutoksia.
Tarvitsemme siis vain makron pivot -taulukoiden välimuistin päivittämiseksi. Teemme tämän käyttämällä laskentataulukkotapahtumaa, jotta meidän ei tarvitse suorittaa makroa manuaalisesti.
Mistä koodata pivot -taulukoiden automaattista päivittämistä?
Jos lähdetietosi ja pivot -taulukot ovat eri arkeissa, VBA -koodin pitäisi mennä lähdetiedostoon.
Tässä käytämme Worksheet_SelectionChange -tapahtumaa. Tämä saa koodin toimimaan aina, kun vaihdamme lähdetietolomakkeesta toiseen. Selitän myöhemmin, miksi käytin tätä tapahtumaa.
Tässä minulla on lähdetiedot taulukossa 2 ja pivot -taulukot taulukossa 1.
Avaa VBE painamalla CTRL+F11. Project Explorerissa näet kolme objektia, Sheet1, Sheet2 ja Workbook.
Koska Sheet2 sisältää lähdetiedot, kaksoisnapsauta sheet2 -objektia.
Nyt näet kaksi pudotusvalikkoa koodialueen yläosassa. Valitse laskentataulukko ensimmäisestä avattavasta valikosta. Valitse toisesta avattavasta valikosta Poista käytöstä. Tämä lisää tyhjän alinimen Worksheet_Deactivate. Koodimme kirjoitetaan tähän osaan. Kaikki tähän alaosaan kirjoitetut rivit suoritetaan heti, kun käyttäjä siirtyy tältä taulukolta mille tahansa muulle taulukolle.
Arkilla 1 minulla on kaksi pivot -taulukkoa. Haluan päivittää vain yhden pivot -taulukon. Tätä varten minun on tiedettävä pivot -taulukon nimi. Jos haluat tietää minkä tahansa pivot -taulukon nimen, valitse mikä tahansa pivot -taulukon solu siirtymällä pivot -taulukon analysointi -välilehdelle. Vasemmalla puolella näet pivot -taulukon nimen. Voit myös muuttaa pivot -taulukon nimen täällä.
Nyt tiedämme pivot -taulukon nimen, voimme kirjoittaa yksinkertaisen rivin pivot -taulukon päivittämiseksi.
| Yksityinen alityöarkki_Deactivate ()
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub |
Ja se on tehty.
Nyt kun vaihdat lähdetiedosta, tämä vba -koodi päivittää pivot -taulukon1. Kuten alla olevasta gifistä näet.
Kuinka päivittää kaikki työkirjan pivot -taulukot?
Yllä olevassa esimerkissä halusimme päivittää vain yhden tietyn pivot -taulukon. Mutta jos haluat päivittää kaikki työkirjan pivot -taulukot, sinun on vain tehtävä pieniä muutoksia koodiin.
| Yksityinen alityöarkki_Deactivate ()
'Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh Jokaiselle tietokoneelle tässä työkirjassa.PivotCaches kpl Päivitä Seuraava pc End Sub |
Tässä koodissa käytämme For -silmukkaa jokaisen työkirjan pivot -välimuistin läpi. ThisWorkbook -objekti sisältää kaikki pivot -välimuistit. Päästäksemme niihin käytämme ThisWorkbook.PivotCaches.
Miksi käyttää Worksheet_Deactivate -tapahtumaa?
Jos haluat päivittää pivot -taulukon heti, kun lähdetietoihin on tehty muutoksia, käytä Worksheet_Change -tapahtumaa. Mutta en suosittele sitä. Se saa työkirjasi suorittamaan koodin aina, kun teet muutoksia taulukkoon. Saatat joutua tekemään satoja muutoksia ennen kuin haluat nähdä tuloksen. Mutta Excel päivittää pivot -taulukon jokaisen muutoksen yhteydessä. Tämä johtaa käsittelyajan ja resurssien hukkaan. Joten jos sinulla on pivot -taulukoita ja tietoja eri arkeissa, on parempi käyttää laskentataulukon deaktivointitapahtumaa. Sen avulla voit viimeistellä työsi. Kun vaihdat pivot -taulukkolaskentaohjelmaan nähdäksesi muutokset, se muuttaa muutoksia.
Jos pivot -taulukot ja lähdetiedot ovat samassa taulukossa ja haluat, että pivot -taulukot päivittävät sen automaattisesti, voit käyttää Worksheet_Change -tapahtumaa.
| Yksityinen alityöarkin_muutos (ByVal -kohde alueena)
Sheet1.PivotTables ("PivotTable1"). PivotCache.Refresh End Sub |
Kuinka päivittää kaikki työkirjat, kun lähdetietoja muutetaan?
Jos haluat päivittää työkirjan kaiken (kaaviot, pivot -taulukot, kaavat jne.), Voit käyttää ThisWorkbook.RefreshAll -komentoa.
| Yksityinen alityöarkin_muutos (ByVal -kohde alueena)
ThisWorkbook.RefreshAll End Sub |
Huomautus : Koodi ei muuta tietolähdettä. Joten jos lisäät tietoja lähdetietojen alle, tämä koodi ei sisällä kyseisiä tietoja automaattisesti. Voit käyttää Excel -taulukoita lähdetietojen tallentamiseen. Jos et halua käyttää taulukoita, voimme käyttää VBA: ta myös uusien tietojen sisällyttämiseen. Opimme sen seuraavassa opetusohjelmassa.
Toivottavasti tämä artikkeli pivot -taulukon tietojen automaattisesta päivittämisestä Excelissä on selittävä. Täältä löydät lisää artikkeleita arvojen laskemisesta ja niihin liittyvistä Excel -kaavoista. Jos pidit blogistamme, jaa se ystävillesi Facebookissa. Voit myös seurata meitä Twitterissä ja Facebookissa. Haluaisimme kuulla sinusta, kerro meille, kuinka voimme parantaa, täydentää tai innovoida työtämme ja parantaa sitä sinulle. Kirjoita meille sähköpostitse.
Pivot -taulukon tietolähdealueen päivittäminen dynaamisesti Excelissä : Pivot -taulukoiden lähdetietoalueen muuttamiseksi dynaamisesti käytämme pivot -välimuisteja. Nämä muutamat rivit voivat päivittää dynaamisesti minkä tahansa pivot -taulukon muuttamalla lähdetietoaluetta. Käytä VBA: ssa pivot -taulukohteita alla olevan kuvan mukaisesti…
Suorita makro, jos taulukkoon tehdään muutoksia määritetyllä alueella : VBA -käytännöissäsi sinun on suoritettava makroja tietyn alueen tai solun muuttuessa. Siinä tapauksessa käytämme muutostapahtumaa suorittaaksemme makroja, kun kohdealueelle tehdään muutos.
Suorita makro, kun taulukkoon tehdään muutoksia : Käytämme siis makroasi aina, kun taulukko päivittyy, käytämme VBA: n laskentataulukkotapahtumia.
Yksinkertaisin VBA -koodi nykyisen rivin ja sarakkeen korostamiseen : Käytä tätä pientä VBA -katkelmaa korostamaan taulukon nykyinen rivi ja sarake.
Laskentataulukon tapahtumat Excel VBA: ssa : Laskentataulukkotapahtuma on todella hyödyllinen, kun haluat makrojesi suoritettavan, kun taulukossa tapahtuu tietty tapahtuma.
Suosittuja artikkeleita:
50 Excel -pikanäppäintä tuottavuuden lisäämiseksi : Nopeuta tehtävien suorittamista Excelissä. Nämä pikanäppäimet auttavat sinua parantamaan työn tehokkuutta Excelissä.
VLOOKUP -toiminnon käyttäminen Excelissä : Tämä on yksi eniten käytetyistä ja suosituimmista Excel -toiminnoista, jota käytetään arvon etsimiseen eri alueilta ja taulukoilta.
IF -toiminnon käyttäminen Excelissä : Excelin IF -käsky tarkistaa ehdon ja palauttaa tietyn arvon, jos ehto on TOSI, tai palauttaa toisen arvon, jos EPÄTOSI.
SUMIF -toiminnon käyttäminen Excelissä : Tämä on toinen kojelaudan olennainen toiminto. Tämä auttaa sinua laskemaan yhteen arvot tietyissä olosuhteissa.
COUNTIF -toiminnon käyttäminen Excelissä : Laske arvot olosuhteilla tämän hämmästyttävän toiminnon avulla. Sinun ei tarvitse suodattaa tietoja laskeaksesi tiettyjä arvoja. Laskutoiminto on välttämätön kojelaudan valmistelemiseksi.