Lage basis for pivot tabeller i Excel?

[trn100]

Halloen!

 

Forsøker å lage et "Dashboard" i Excel som raskt kan gi et overblikk over omsetningen i et firma med omsetning pr. bransje, pr. selger, pr. fylke samt en del andre statistikker.

 

I utgangspunktet har jeg en arbeidsbok med mange forskjellige ark. Ett ark med en komplett kundeliste som inneholder kundenummer, navn, postnummer, bransje, selgerkode, bransjekode etc.

 

Videre har jeg et ark med bransjekoder og bransjenavn, ett ark med postnummer og fylkesnavn, et med selgerkode og selger navn osv.

 

Dette med kanskje noe gammeldagse forestillingen om at i en databasestruktur er det en fordel at ingen data er lagret flere steder.

 

I tillegg til disse basisarkene har jeg ett ark hvor tanken er å kopiere inn månedomsetning hver måned fra en SAP rapport som inneholder kundenummer, fakturanummer, fakturabeløp og fakturadato.

 

Tanken var da å ha et par skjulte kolonner i dette omsetningsarket som hentet info fra de andre arkene med INDEKS/SAMMENLIGNE for å ha alt datagrunnlaget for diverse pivot tabeller i samme ark.

 

Problemet er bare at arbeidsboken blir sinnsykt stor om jeg legger inn de nødvendige formlene i alle radene i omsetningsarket. Samtidig vet jeg ikke hvor mange rader det kan bli totalt og kvier meg derfor for å legge inn formlene i et begrenset antall rader. I øyeblikket har jeg lagt inn 4 år med omsetning og dette utgjør ca. 60.000 rader.

 

Hvis jeg legger inn formlene i 80.000 rader (med kun 3 formelkolonner og jeg trenger fler!) endrer filstørrelsen seg fra ca. 28 MB til 5 MB så det hjelper jo mye, men jeg synes ikke det er optimalt. 

 

Det jeg lurer på er for det første om dette er den smarteste måten å lage arbeidsboken på? Er det kanskje like lurt å ha kun ett ark med all kundeinformasjonen i og kutte ut arkene med postnummer, bransjer osv?

 

Finnes det en smart måte å lage arket med omsetningstallene på som gjør at det kun finnes formler i de radene det faktisk finnes data? Har lurt litt på en Makro som kjøres når man går ut av og som sjekker hvor mange rader det finnes data i og deretter setter inn formlene?

 

Er det eventuelt mulig å lage en pivot tabell som direkte henter data fra flere ark i samme arbeidsbok og kobler disse?

 

Mange spørsmål her men håper noen kloke hoder kan lede meg litt i riktig retning :-)

 

Takk på forhånd!

[Harald Staff]

Ja, ikke driv med formler. Maskinen som kan dette (koble flere Exceltabeller og andre datakilder som om det er snakk om en enkel relasjonsdatabase) heter PowerPivot. Det er et gratis(!) nedlastbart tillegg til Excel 2010 og Excel 2013. Excel 2016 har jeg ennå ikke hatt anledning til å finne ut av, det er visst noe relatert som heter PowerBi.

 

Tør ikke guide deg videre, men google PowerPivot for din Excelversjon (og gjerne PowerQuery, som har blitt døpt til noe "merge"/"slå sammen"-aktig på datafanen i 2016).

 

Det er ikke kjempeenkle verktøy dette. Men til de grader kjempekraftige.

 

(Edit, "ja" var til det siste spørsmålet. Jeg er overbevist om at det er løsningen du ønsker deg)

 

HTH. Beste hilsen Harald

Endret 3. februar 2016 av Harald Staff

[trn100]

Takker så mye for tips Harald! :-)

 

Da skal jeg prøve det og se hvor langt jeg kommer. Begge tilleggene er helt ukjente for meg så det kan bli spennende!

 

Har også tenkt litt på muligheten å lage en egen Excel bok med fakturadatene på en server, men siden "dashboardet" kan bli distribuert til brukere uten tilgang til serveren er heller ikke det en god løsning.

 

Har også vært inne på tanken å importere dataene til Access og bearbeide de der for å lage et ferdig Excel ark der, men det krever også en del, ikke minst fordi brukeren som skal gjøre dette ikke er er spesielt god på hverken Access eller Excel, så det må liksom være "idiotsikkert". Det ville i så fall kreve at jeg laget en Access applikasjon som ved et knappetrykk importerte dataene, bearbeidet de, og eksporterte til en Excel mal helt automatisk, og det er jeg også noe usikker på hvordan jeg skal gjøre. ;-)