Google Sheets – Træk data ud af vagtplan
Her kan du se et konkret eksempel på løsning af en opgave i Google Sheets, hvor jeg ville trække data ud af Google Sheets og gøre dem mere læsbare.
Jeg arbejder til daglig som læge, og har derfor ofte nogle vagtplaner der er meget komplicerede at læse. Desuden kan det være vigtig at tjekke vagtplanen for dobbeltvagter eller overenskomstbrud, der skal gøres opmærksom på, så de rettes op i den endelige vagtplan.
Vagtplanen får jeg som regel udleveret i et Excelark, der er opbygget lignende det du kan se nedenunder. De er sorteret efter henholdsvis dato og funktioner der skal dækkes – men ikke efter den enkelte person. Det kan gøre det uoverskueligt for mig at se hvad jeg skal lave, fordi jeg skal bevæge mig inden for flere kollonner.
Det fik jeg løst ved at lave et nyt ark, hvor jeg ville sortere det efter dato og person i stedet for. Hele Excelarket kopierede jeg ind i Google Sheets.
Hver af felterne i midten består af en kode der ligner denne:
=IFNA(VLOOKUP(MATCH(INDIRECT(ADDRESS(2;COLUMN();4);True);INDIRECT("Vagtplan!$E$" & ROW()+4 & ":$AR$" & ROW()+4);0);Funktionsliste!$A$2:$B$100;2;False);"")Det er denne kode jeg gerne vil gennemgå om lidt. Rådata-vagtplanen ligger på et ark der hedder “Vagtplan” og så har jeg lavet et andet ark “Funktionsliste”, der indeholder en liste med alle de funktioner der findes:
Nu vil jeg gennemgå de forskellige formler, du kan se i hele formlen.
COLUMN & ROW
COLUMN funktionen er en funktion der returnerer den nuværende kolonne du står i. Hvis du fx. står i feltet C2, dvs. 3. kolonne, så vil COLUMN() give værdien 3.
=COLUMN()På samme måde kan du bruge ROW() til at returnere det aktuelle rækkenummer.
ADDRESS
ADDRESS funktionen er en funktion der ved hjælp af række- og kolonne numre giver den reference til et ark du har brug for.
Funktionen kan indeholde mellem 3 og 5 parametre, adskilt af ;. De 4 parametre betyder følgende i rækkefølge:
- Rækkenummer
- Kolonnenummer
- Type
- Brug A1 eller R1C1 notation (true = A1, false = R1C1)
- Arknavn
De sidste to parametre er frivillige. Typen angives som et tal mellem 1 og 4, hvor de betyder følgende:
- 1 = Absolut række og kolonne (fx. $B$2)
- 2 = Relativ kolonne, absolut række (fx. B$2)
- 3 = Absolut kolonne, relativ række (fx. $B2)
- 4 = Relativ række og kolonne (fx. B2)
Lad os tage et eksempel:
=ADDRESS(2;3;4; True; "Vagtplan")Dette vil give resultatet Vagtplan!C2. Bemærk at resultatet er en tekststreng, og ikke hvad der står i Vagtplan!C2.
INDIRECT
INDIRECT tager tekststrengen for en adresse og udfører funktionen. Hvis vi tager eksemplet fra sidste afsnit, ville følgende kode:
=ADDRESS(2;3;4)Give resultatet C2. Hvis vi i stedet skriver:
=INDIRECT(ADDRESS(2;3;4))Vil vi i stedet få returneret værdien der står i C2. Den tager altså tekststrengen og udregner den som en funktion.
MATCH
MATCH funktionen er en funktion der leder efter en bestemt værdi i et område, og returnerer hvilken pladsnummer værdien findes. Her er et eksempel:
=MATCH("Anders And";A2:A10;0)Funktionen vil lede i området A2:A10 efter værdien “Anders And”. Når den findes vil du få returnet hvilket nummer i rækkefølgen den findes. Den sidste parameter er et tal fra -1 til 1, hvor det betyder:
- -1 = Det antages at området er en liste med værdier sorteret stigende (fx. stigende tal), og der returneres den største værdi der er lig eller mindre end den værdi du søger efter.
- 0 = Eksakt match, hvor du ikke har en sorteret liste, men kun leder efter den korrekte værdi.
- 1 = Det antages at området er en liste med værdier sorteret aftagende (fx. aftagende tal), og der returneres den mindste værdi der er lig eller større end den værdi du søger efter.
I ovenstående eksempel leder vi efter “Anders And” og ikke et tal, og derfor giver det kun mening at angive 0, dvs. eksakt match. Hvis vi fx. får returneret tallet 3, betyder det at “Anders And” står nummer 3 i rækken.
Hvis du anvender eksakt match og der ikke findes noget match, får du returneret værdien #N/A, dvs. der kan ikke findes et match. For at undgå der så bare står #N/A, kan du sætte denne funktion udenom, og som 2. parameter angive hvad der skal stå i stedet:
=IFNA(MATCH("Anders And";A2:A10;0);"Intet fundet")VLOOKUP
VLOOKUP er en funktion der ligesom MATCH søger i et område. Den kan godt være lidt komplekst at forstå, men nu prøver jeg lige at give et eksempel:
=VLOOKUP(3;Funktionsliste!A2:B8;2;False)Denne betyder: Gå ind i arket “Funktionsliste” og kig i området A2:B8. Led her efter værdien 3 og returner så den værdi der står i samme række, men kolonne 2. Hvis du går op og kigger på arket “Funktionsliste lidt længere oppe, ville resultatet være “MV DV” fordi det er den værdi der står 3 udenfor.
Dette var bare et eksempel på hvordan man kan bruge Google Sheets til at løse helt konkrete opgaver. Hvis du har nogle spørgsmål er du meget velkommen til at skrive en kommentar.
