Fr. Kansasilainen Mark lähetti tämän viikon Excel-kysymyksen:
Excel-pivot-taulukot toimivat parhaiten, kun tiedot on jaettu mahdollisimman moniin tietueisiin, mutta tämä ei ole aina intuitiivisin tapa ladata tiedot Exceliin. Esimerkiksi on intuitiivista ladata tietoja, kuten kuva 1, mutta paras tapa analysoida tietoja on kuten kuvassa 2.
Ensinnäkin käyn läpi Excelin vähemmän kuin täydellinen tapa käsitellä useita tietokenttiä. Toiseksi esitän yksinkertaisen ja nopean makron, jolla muunnetaan kuva 1 kuvaksi 2.
Jos tietosi ovat kuten kuvassa 1, ohjatun pivot-taulukon ohjatun toiminnon vaiheen 3/4 aikana on mahdollista vetää kaikki 4 neljänneskenttää pivot-taulukon data-alueelle oikealla olevan kuvan mukaisesti.
Tässä on vähemmän kuin täydellinen tulos. Oletusarvon mukaan Excelillä on useita tietokenttiä alaspäin sivua pitkin. Voit napsauttaa harmaata "Data" -painiketta ja vetää sitä ylös ja oikealle, jotta vuosineljännekset menevät sivun yli. Sinulta puuttuu kuitenkin kutakin aluetta koskevat kokonaissummat, ja vuosineljänneksen kokonaismäärät näyttävät aina olevan paikoillaan.
Joten, Fr. Mark löi naulaa päähän, kun hän sanoi, että tietojen on todellakin oltava kuvan 2 mukaisia, jotta ne voidaan analysoida oikein. Alla on makro, joka siirtää tiedot nopeasti taulukon 1 kuvan 1 muodossa taulukossa 2 kuvan 2 muodossa. Tämä makro ei ole tarpeeksi yleinen toimiakseen minkä tahansa tietojoukon kanssa. Sen pitäisi kuitenkin olla suhteellisen helppo mukauttaa omaan tilanteeseen.
Public Sub TransformData() ' Copyright 1999.com Sheets("Sheet2").Select Range("A1").CurrentRegion.Clear Sheets("Sheet1").Select Range("A1:B1").Copy Destination:=Sheets("Sheet2").Range("A1") Sheets("Sheet2").Select Range("C1").Value = "Qtr" Range("D1").Value = "Sales" Sheets("Sheet1").Select FinalRow = Range("A16000").End(xlUp).Row NextRow = 2 LastRow = FinalRow ' Loop through the data columns For i = 3 To 6 ThisCol = Mid("ABCDEFGHIJK", i, 1) ' Copy the left columns from sheet1 to sheet2 Range("A2:B" & FinalRow).Copy Destination:= _ Sheets("Sheet2").Range("A" & NextRow) ' Copy the header from ThisCol to column C Range(ThisCol & "1").Copy Destination:= _ Sheets("Sheet2").Range("C" & NextRow & ":C" & LastRow) ' Copy the data for this quarter to column D Range(ThisCol & "2:" & ThisCol & FinalRow).Copy _ Destination:=Sheets("Sheet2").Range("D" & NextRow) NextRow = LastRow + 1 LastRow = NextRow + FinalRow - 2 Next i Sheets("Sheet2").Select End Sub
Tämän makron suorittamisen jälkeen tiedot ovat helpommin analysoitavassa muodossa, kuten yllä olevassa kuvassa 2 on esitetty. Kun käytät näitä tietoja pivot-taulukkoon, sinulla on täysi hallinnan tiedot kuten normaalisti.
