Excel: Pivottabell fra flere ark.

[C-angel]

Jeg har en arbeidsbok som inneholder 7 ark. I disse 7 arkene ligger det data jeg gjerne vil få samlett i én pivottabell. Jeg har prøvd en del alternativer men jeg får ikke dette helt til å fungere.

 

Å lage en pivottabell pr. ark for så og siden summere er ikke aktuelt, bare så det er sakt. Dette vil medfører enorm størelse på dokumentet, og stort resursforbruk.

 

Er det noen her som kan hjelpe?

 

Litt info rundt arbeidbok:

- 7 regneark

- Det er likt antall kolonner i arkene. Kolonnene har også lik betegnelse i ark 1, 2 osv.

- Kolonnene strekker seg pr. idag fra A til CI

- Rader varierer fra 10 til 400 mellom arkene.

 

Jeg må i senere tid ha mulighet til å legge til kolonner og/eller rader (i tilfelle kolonner vil dette bli endrett likt i alle ark) uten at dette vil medføre noen utelatte celler ell.

 

Jeg kjører norsk excel 2000.

[toennes]

Det går i hvertfall med Excel 2003. Under kildedata i første trinn av veiviseren kan du velge "Flere konsolideringsområder".

[Harald Staff]

Det går fint i 2000 også. Men disse pivottabellene er utrolig mye mer plundrete og har mye dårligere funksjonalitet, enn tabeller som jobber mot en enkelt datakilde. Så jeg vil sterkt anbefale deg å samle dataene i ett ark før analysen. Du kan eventuelt lage/få laget en makroløsning som samler dataene fra dine 7 ark med et enkelt knappetrykk. Du havner tydeligvis bare på et firesifret radantall, det er helt uproblematisk.

 

HTH. Beste hilsen Harald

[C-angel]

Takker for svar.

 

Det er som du sier Harald, at pivottabeller som tar utganspunkt i flere konsolideringsområder ikke er veldig funksjonible. I hvertfall ikke i dette tilfellet.

 

Det å måtte samle alle dataene i ett ark før analyse var vel det som slo meg også.

 

Når det gjelder makro så har jeg begrenset kunnskap kan du si, men det kan vel kanskje bare være så enkelt at jeg registrerer en ny makro på følgende måte:

kopiere rader si f.eks 999 rader fra hvert ark (Da vil jeg være sikker på at vi er i år. 3000 før eventuelt noen av arkene skulle overstiger dettte). Videre lime inn under hverandre i ett nytt ark

 

Gangen i registreringen vil da bli noe lignende dette:

ark1 - kopi rad 1-999, lim inn fra rad1 i ark-samledata > ark2 - kopi rad 1-999, lim inn fra rad 1000 i ark-samledata.. osv...

 

Hva tror du Harald, ser du noen problemer som kan oppstå ved og gjøre det på denne måten?

 

 

 

Jeg kjenner at jeg burde hatt noen excel kurs gitt... Det er enkelte ting som tar veeeeeldig lang tid når en skal lære alt ved hjelp av "prøve og feile" metoden...

Endret 5. februar 2007 av C-angel

[Harald Staff]

Det kan kanskje gå det. Du må være helt sikker på at dataene beholder overskriftsraden sin, en og bare en gang, og at tabellen får med seg alle data som kildeområde dersom datalista blir hullete.

Vrient å bli generell på dette. Men kode som kopierer blokker av data er lett å skrive.

 

Beste hilsen Harald

[C-angel]

Takker igjen for svar.

 

Jeg skal prøve metoden litt senere i dag, så får vi se åssen det går.

 

Hvis du kan gi ett lite eksempel på hvordan en slik kode kan lages/skrives, så hadde det vært helt knall!

[Harald Staff]

Denne kopierer listene fra ark 1 og 2 til ark 3. Merk at indekseringen er slik arkfanene er stablet visuelt venstre-høyre.

 

Sub KopiTilArkTre()

Sheets(1).Range("A2").CurrentRegion.Copy _

Destination:=Sheets(3).Range("A65000").End(xlUp).Offset(1, 0)

Sheets(2).Range("A2").CurrentRegion.Copy _

Destination:=Sheets(3).Range("A65000").End(xlUp).Offset(1, 0)

End Sub

 

Du kan også bruke arknavn dersom det er kjent og indeks ukjent:

 

Sub KopiTilArkTre()

Sheets("Ark1").Range("A2").CurrentRegion.Copy _

Destination:=Sheets("Ark3").Range("A65000").End(xlUp).Offset(1, 0)

Sheets("Ark2").Range("A2").CurrentRegion.Copy _

Destination:=Sheets("Ark3").Range("A65000").End(xlUp).Offset(1, 0)

End Sub

 

Men pass altså på at du ikke får kolonneoverskriftene fra Ark2 midt i den ny konsoliderte datalista.

 

HTH. beste hilsen Harald

[C-angel]

Prøvde alternativet ditt og det fungerte jo med tanke på kopiering av data. Problemet ble bare at kolonneoverskriftene også blir kopiert, og at gammel data ikke ble slettet før innsetting av ny data..

 

Jeg har prøvd meg frem litt, som nevnt så er dette helt ukjent teritorium for meg, men kom frem til at følgende fungerer.

 

Sub KopiTilArk1()

Sheets("Ark1").Range("A2:EA5000").Delete

 

Sheets("Ark2").Range("A2:EA500").Copy _

Destination:=Sheets("Ark1").Range("A65000").End(xlUp).Offset(1, 0)

Sheets("Ark3").Range("A2:EA500").Copy _

Destination:=Sheets("Ark1").Range("A65000").End(xlUp).Offset(1, 0)

End Sub

 

Vet jo ikke om det er noe overflødig her eller om det kanskje burde stå Columns eller Rows isteden for Range i "linjen som kopierer".

 

Selvfølgelig ikke opptimalt heller med tanke på kopiering av mange tomme celler, samt at kopieringen har ett bestemt området.

 

Hva er det forresten som gjør at radene blir kopiert etter hverandre uten tomme celler? Har " End(xlUP).Offset(1,0) " noe med dette og gjøre?

[Harald Staff]

Nesten i mål da. Ja xlup finner nederste brukte celle i kolonnen. Dra nytte av det samme når du kopierer så har vi den:

 

Sheets("Ark2").Range("A2:AE" & Sheets("Ark2").Range("A65000").End(xlUp).Row).Copy

 

HTH. Beste hilsen Harald

Endret 7. februar 2007 av Harald Staff