Vi har været ude for det så tit, at skulle lave en udlånstatistik fordelt på login, men loginerne skal grupperes efter filialen hvor udlånet er sket.
Der findes en funktion i Excel som kan være til stor nytte bl.a. i denne forbindelse, nemlig funktionen LOPSLAG (på engelsk hedder funktionen VLOOKUP).
Hvis man f.eks. danner en statistik over udlån i april måned, fordelt på filial, afdeling og login, og gør det til Excel, så starter man med at indsætte en række i toppen med:
antal, filial, afd., login
og en 5 kolonne kaldet "sted"
Vi skal nu oprette et hjælpe-regneark, som vi kalder gblogin.xsl og vi kalder arket for login. Arket skal ikke have overskriftrække, men i første kolonne skal være - i alfabetisk orden, hvilket er vigtigt (sorter den evt. bagefter, der skelnes ikke mellem små og store bogstaver) - de loginer man har, i næste kolonne skriver man det man gerne vil have den grupperet som.
Derefter opretter man i det første felt i kolonnen "sted" i det oprindelige regne en formel:
=LOPSLAG(D2, [gblogin.xsl]login!$A$1:$B$131;2;FALSK)
Det betyder, at data i D2 skal oversættes efter søjle nr 2 i filen gblogin.xsl arket login, og det skal være eksakt oversættelse, dvs. værdien i D2 skal findes i listen. Området $A$1:$B$131 skal afspejle hvor mange værdier der er i tabellen. Så kopierer man formlen til resten af cellerne i kolonne E - og det var det.
Man kan have opslagstabellen i et eksternt regneark som her, men det kan naturligvis også være et faneblad i det aktuelle regneark. Fidusen ved at have den i et eksternt ark er naturligvis at det er nemmere at genbruge den og kun at skulle vedligeholde den eet sted.
Hvis der nemlig kommer nye loginer, skal tabellen opdateres med disse. Manglende værdier er nemme at spotte, der markeres nemlig med #!/T, som i eksemplet neden, hvor GBZJAG1 mangler.
Den store fidus kommer videre, hvis man så tømmer denne tabel for data og kun lader formlen stå tilbage, og gemmer dette som en skabelon, f.eks. udlaan.xlt
Så kan man senere når man skal kigge på næste måneds statistik, danne det oprindelige udtræk som Excel-regneark, åbne skabelonen og kopiere den nye måneds tal ind i kolonne A-D. Kopiere formlen til de nødvendige række - og så er den klar. Skabelonen kan også indeholde en pivot-tabel, som så bliver dannet når man opdaterer data.
/Niels Jensen
Ingen kommentarer:
Send en kommentar