ExcelGuides

EXCEL: Funktionen FORSKYDNING (dynamiske drop down lister)

Funktionen FORSKYDNING kan bruges i mange forskellige sammenhænge, og er for mig den vigtigste funktion i Excel.

Et eksempel på hvad funktionen kan bruges til er drop down lister. Drop down lister kan baseres på nogle udvalgte celler, hvori der står de værdier, der skal kunne vælges på listen.

I denne guide vælger jeg at navngive området, hvor drop down elementerne er – det er ikke strengt nødvendigt.

For at navngive et (dynamisk) område, skal man bruge Navnestyring under menuen FORMLER.
ScreenShot097

Her kan man navngive et område enten ved at lave en reference til cellerne, eller ved hjælp af en formel. Hvis man tilføjer et punkt på listen over drop down elementer, er vi interesseret i at det navngivne område selv skal udvide sig, så det nye punkt automatisk kommer med i drop down listen. Derfor vælger vi at indsætte en formel, der udvider området alt efter hvor mange elementer der er i listen/kolonnen. Vi bruger funktionen FORSKYDNING, som tager udgangspunkt i en celle eller et område og derefter flytter og udvider/indskrænker området – forklaring følger. Funktionen ser således ud:

FORSKYDNING(startcelle(r); flyt felter ned; flyt felter til højre [;lodret længde] [;vandret længde])
De parametre, der er omkranset af firkantede parenteser kan undlades. Lad os tage udgangspunkt i listen på billedet nedenfor.
ScreenShot098

I celle D1 har jeg indtastet en overskrift, og nedenunder står de elementer, jeg gerne vil have i min drop down liste. Hvis jeg indtaster et ekstra element, skal drop down listen automatisk udvide sig. I navnestyring laver jeg et nyt navn, som jeg har kaldt eksempel. Jeg bruger da funktionen FORSKYDNING, og starter med at fortælle den at den skal starte i celle D1 – altså overskriften. Jeg fortæller den da, at den skal gå en ned og nul til højre. Nu skal vi afgøre, hvor højt området skal være. Til det beder jeg den tælle hvor mange celler der indeholder værdier i kolonne D ved hjælp af funktionen TÆLV('ARK1'!$D:$D). Herefter trækker jeg 1 fra, nemlig overskriften. Jeg afslutter da funktionen, da sidste parameter (bredden af området) er valgfri og som standard 1. Nu vil området med navnet eksempel selv udvide sig, hvis vi indsætter flere værdier i kolonne D.

Nu kan vi indsætte drop down listen I en celle. Til det bruger vi datavalidering under menuen DATA.

ScreenShot099

I rullelisten vælger vi liste og i kilde skriver vi navnet på vores valgte område, altså =eksempel.

ScreenShot100

Nu har vi lavet en dynamisk drop down liste!

ScreenShot101

Excel-arket, der er brugt i denne guide kan hentes her: forskydning-eksempel

17 thoughts on “EXCEL: Funktionen FORSKYDNING (dynamiske drop down lister)

  1. Er det ikke bare samme funktion som Sortér og filtrer, som er under Editing? Ellers ved jeg ikke hvad jeg skal bruge denne dropdown funktion til? Pia

    1. Hej Pia.
      Nej, det er beregnet til at begrænse brugerens muligheder for at vælge hvad der skal tastes ind i cellen. Med andre ord kan man kun vælge en af mulighederne i drop down menuen i cellen. Det bruger man f.eks. til at sikre at flere brugere staver det samme ord på samme måder, så man nemmere kan lave opfølgning bagefter. Håber det gav mening.

  2. Hejsa. Kan man på nogen måde lave sådan, at hvis man ændrer navnet på en af mulighederne i ens dropdown menu, så ændrer de valg der er foretaget også?

    Problemet er, at jeg allerede har brugt den forkerte mulighed fra drowdown menuen, men hvis jeg ændrer navnet så ændrer alle mine indtastninger sig ikke. Giver det mening?

    1. Hej Hans. Nej, det ser jeg ikke lige en nem løsning på. Men du kunne relativt hurtigt markere det område, hvor du skal have rettet og trykke Ctrl+h for at søge og erstatte teksten. Så skulle det være klaret temmelig hurtigt 🙂

  3. Hej Kenneth,

    Har du eksempler på hvor valgene i en drop down liste er betinget af valget i en anden drop down liste? Altså en for formet lookup I listen.

  4. Hej, jeg vil gerne lave en tabel i excel til kalorie beregning.
    Min ide er, at jeg første vælger en kategori fx Brød fra dropdown.
    Når brød er valgt for drop down, vil jeg gerne i fødevare kun kunne vælge fødevarer indenfor brød fx lyst brød.
    Når jeg så vælger lyst brød vil jeg gerne at “mål, kalorier, fedt, protein” bliver hentet fra en anden fane, hvor jeg har tastede disse informationer ind.
    I Mål vil jeg så gerne skrive fx. 150 (hvilket betyder 150 g lyst brød), de 150 skal så ganges over på kalorier, fedt og protein.
    Giver det mening? Jeg har siddet med det længe nu selv, og kan ikke få det til at fungere, har prøvet ovenstående, men det giver mig ikke det resultat jeg ønsker, og jeg ved ikke hvad jeg skal søge efter 🙂
    Hilsen Emma

  5. Hej
    Jeg forsøger at lave navnestyringen men henvisning til et andet ark. Men rullelisten virker derfor kun når jeg har begge ark åben… Er det muligt at lave navnestyring og rulleliste i hvert sit ark, hvor man ikke behøvs at åbne begge to for at bruge rullelisten?

    1. Hej Jonas
      Desværre ikke. Excel kan ikke umiddelbart finde data fra andre lukkede ark. Det er meget omstændigt, hvis man skal forsøge at omgå det. Jeg vil derfor anbefale dig at gemme masterdata i en skjult fane. Hvis du ikke er interesseret i at folk skal kunne se den fane, kan du bruge Gennemse -> “Beskyt projektmappe”, så man skal bruge password for at vise den skjulte fane.

      1. Tænkte jeg nok desværre… Jeg har forsøgt mig at lave masterdata i skjulte faner som linkes til vores masterark med lopslag, men så virker forskydning ikke, da cellen åbenbart skal være helt tom, og det tæller ikke med “” som en tom celle?

  6. Det forstår jeg ikke helt?
    Hvis min navnestyring lyder sådan her:
    =FORSKYDNING(‘Arbejdsoversigt – eksempel’!$X$1;1;0;TÆLV(‘Arbejdsoversigt – eksempel’!$X:$X)-1)
    Hvordan skal den så lyde, for at den burde virke hvis det er celler med formler i den skal tage, og kun tage med hvis de som du siger er højere end 0? Virker det også hvis det er tekst der står i cellen og ikke tal?

    1. Prøv med FORSKYDNING('Arbejdsoversigt - eksempel'!$X$1;1;0;TÆL.HVIS('Arbejdsoversigt - eksempel'!$X:$X;"?*")-1)
      Den burde tage det med hvor der er 1 eller flere tegn – uanset om det er tal eller tekst.

Skriv et svar

Din e-mailadresse vil ikke blive publiceret. Krævede felter er markeret med *

Please Do the Math      
 

This site uses Akismet to reduce spam. Learn how your comment data is processed.