Excel-regneark indeholder ofte celle dropdowns for at forenkle og / eller standardisere dataindtastning. Disse dropdowns oprettes ved hjælp af data validering funktionen til at angive en liste over tilladte poster.
For at oprette en simpel rulleliste, vælg den celle, hvor data skal indtastes, og klik derefter på Data validering (på fanen Data ), vælg Datavalidering, vælg Liste (under Tillad :), og indtast derefter listeposterne (adskilt af kommaer ) i feltet Source : (se figur 1).
I denne type grundlæggende dropdown er listen over tilladte poster angivet inden for data validering selv; Derfor skal brugeren åbne og redigere data validering for at foretage ændringer i listen. Dette kan dog være vanskeligt for uerfarne brugere, eller i tilfælde af, at listen over valg er lang.
En anden mulighed er at placere listen i et navngivet interval inden for regnearket, og angiv derefter dette intervalnavn (præfaced med et tilsvarende tegn) i Source : -feltet i data validering (som vist i Figur 2).
Denne anden metode gør det lettere at redigere valgene i listen, men at tilføje eller fjerne elementer kan være problematisk. Da den angivne rækkevidde (FruitChoices, i vores eksempel) refererer til et fast antal celler ($ H $ 3: $ H $ 10 som vist), hvis flere valg bliver tilføjet til cellerne H11 eller derunder, vil de ikke vises i dropdownen (da disse celler ikke er en del af FruitChoices sortimentet).
På samme måde, hvis pærerne og jordbærbetegnelserne slettes, vises de ikke længere i rullelisten, men i stedet vil dropdown'en inkludere to "tomme" valg, da dropdownen stadig refererer til hele FruitChoices-serien, herunder de tomme celler H9 og H10.
Af disse grunde skal det navngivne område i sig selv bruges til at indeholde flere eller færre celler, hvis der tilføjes eller slettes fra listen, når der anvendes et normalt navngivet område som listekilden til en dropdown.
En løsning på dette problem er at bruge et dynamisk områdenavn som kilden til dropdown-valgene. Et dynamisk rækkevidde navn er en, der automatisk udvider (eller kontrakter) til nøjagtigt at matche størrelsen af en blok af data, da indgange tilføjes eller fjernes. For at gøre dette bruger du en formel i stedet for et fast udvalg af celleadresser til at definere det navngivne interval.
Sådan opsættes et dynamisk område i Excel
Et normalt (statisk) rækkevidde henviser til et bestemt antal celler ($ H $ 3: $ H $ 10 i vores eksempel, se nedenfor):
Men et dynamisk område er defineret ved hjælp af en formel (se nedenfor, taget fra et separat regneark, der bruger dynamiske rækkevidde):
Før vi kommer i gang, skal du sørge for at downloade vores Excel-eksempelfil (sortering af makroer er blevet deaktiveret).
Lad os undersøge denne formel i detaljer. Valgene til Frugter er i en blok af celler lige under en overskrift ( FRUITS ). Denne overskrift hedder også et navn: FruitsHeading :
Hele formlen, der bruges til at definere det dynamiske område for Fruits valg er:
= FORSKYDNING (FruitsHeading, 1, 0, IFERROR (MATCH (SAND, INDEX (ISBLANK (OFFSET (FruitsHeading, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1)
FruitsHeading henviser til overskriften, der er en række over den første post i listen. Nummeret 20 (brugt to gange i formlen) er den maksimale størrelse (antal rækker) for listen (dette kan indstilles efter ønske).
Bemærk, at der i dette eksempel kun er 8 poster på listen, men der er også tomme celler under disse, hvor yderligere poster kan tilføjes. Nummeret 20 refererer til hele blokken, hvor der kan indtastes, ikke til det faktiske antal indgange.
Lad os nu nedbryde formlen i stykker (farvekodende hvert stykke), for at forstå, hvordan det virker:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( OFFSET (FruitsHeading, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1)
Det "inderste" stykke er OFFSET (FruitsHeading, 1, 0, 20, 1) . Dette refererer til blokken på 20 celler (under FruitsHeading-cellen), hvor valg kan indtastes. Denne OFFSET funktion siger i grunden: Start ved FruitsHeading cellen, gå ned 1 række og over 0 kolonner, vælg derefter et område der er 20 rækker lang og 1 kolonne bred. Så det giver os den 20-rækkede blok, hvor frugternes valg er indtastet.
Det næste stykke af formlen er ISBLANK- funktionen:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (ovenstående), 0, 0), 0) -1, 20), 1)
Her er OFFSET-funktionen (forklaret ovenfor) blevet erstattet med "ovenstående" (for at gøre tingene lettere at læse). Men ISBLANK-funktionen fungerer på 20-rækken af celler, som funktionen OFFSET definerer.
ISBLANK opretter derefter et sæt på 20 TRUE og FALSE værdier, der angiver, om hver enkelt af de enkelte celler i 20-rækken rækkevidden refereret af OFFSET-funktionen er tom (tom) eller ej. I dette eksempel vil de første 8 værdier i sættet være FALSE, da de første 8 celler ikke er tomme, og de sidste 12 værdier vil være SAND.
Det næste stykke af formlen er INDEX-funktionen:
= OFFSET (FruitsHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX (ovenstående, 0, 0), 0) -1, 20), 1)
Igen henviser "ovenstående" til de ovenfor beskrevne ISBLANK- og OFFSET-funktioner. INDEX-funktionen returnerer et array indeholdende de 20 TRUE / FALSE-værdier, der er oprettet af ISBLANK-funktionen.
INDEX bruges normalt til at vælge en bestemt værdi (eller rækkevidde af værdier) ud af en blok af data ved at angive en bestemt række og kolonne (inden for den pågældende blok). Men indstilling af række og kolonneindgange til nul (som det gøres her) får INDEX til at returnere et array indeholdende hele databasen.
Det næste stykke af formlen er MATCH-funktionen:
= OFFSET (FruitsHeading, 1, 0, IFERROR ( MATCH (TRUE, ovenstående, 0) -1, 20), 1)
MATCH- funktionen returnerer placeringen af den første TRUE-værdi inden for det array, der returneres af INDEX-funktionen. Da de første 8 poster i listen ikke er tomme, vil de første 8 værdier i arrayet være FALSE, og den niende værdi vil være SAND (siden den 9. række i rækken er tom).
Så vil MATCH-funktionen returnere værdien på 9 . I dette tilfælde ønsker vi imidlertid virkelig at vide, hvor mange poster der er på listen, så formlen trækker 1 fra MATCH-værdien (som giver positionen for den sidste post). Så til sidst returnerer MATCH (TRUE, ovenstående, 0) -1 værdien på 8 .
Det næste stykke af formlen er IFERROR-funktionen:
= OFFSET (FruitsHeading, 1, 0, IFERROR (ovenstående, 20), 1)
IFERROR-funktionen returnerer en alternativ værdi, hvis den angivne første værdi resulterer i en fejl. Denne funktion er inkluderet, da hele cellen af celler (alle 20 rækker) er fyldt med indgange, returnerer MATCH-funktionen en fejl.
Dette skyldes, at vi fortæller MATCH-funktionen for at kigge efter den første TRUE-værdi (i rækken af værdier fra ISBLANK-funktionen), men hvis ingen af cellerne er tomme, bliver hele arrayet fyldt med falske værdier. Hvis MATCH ikke kan finde målværdien (TRUE) i array, søger den, den returnerer en fejl.
Så hvis hele listen er fuld (og derfor returnerer MATCH en fejl), returnerer IFERROR-funktionen i stedet værdien på 20 (ved at der skal være 20 poster på listen).
Endelig returnerer OFFSET (FruitsHeading, 1, 0, ovenstående 1) det område, vi rent faktisk søger efter: Start ved FruitsHeading-cellen, gå ned 1 række og over 0 kolonner, vælg derefter et område, der dog er mange rækker længe som der er poster på listen (og 1 kolonne bred). Så hele formlen sammen returnerer rækkevidden, der kun indeholder de faktiske poster (ned til den første tomme celle).
Ved at bruge denne formel til at definere det område, der er kilden til rullemenuen, kan du frit redigere listen (tilføj eller fjern poster, så længe de resterende indtastninger starter ved den øverste celle og er tilstødende), og rullemenuen afspejler altid strømmen liste (se figur 6).
Eksempelfilen (Dynamiske lister), der er brugt her, er inkluderet og kan downloades fra denne hjemmeside. Makroerne virker imidlertid ikke, fordi WordPress ikke kan lide Excel-bøger med makroer i dem.
Som et alternativ til at angive antallet af rækker i listeblokken kan listeblokken tildeles dets eget rækkenavn, som derefter kan bruges i en ændret formel. I eksempelfilen bruger en anden liste (Navne) denne metode. Her tildeles hele listenblokken (under overskriften "NAMES", 40 rækker i eksempelfilen) navnet på navnet på NameBlock . Den alternative formel til definering af navnelisten er så:
= OFFSET (NamesHeading, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NamesBlock) ), 1)
hvor NamesBlock erstatter OFFSET (FruitsHeading, 1, 0, 20, 1) og ROWS (NamesBlock) erstatter 20 (antal rækker) i den tidligere formel.
Så, for dropdown lister, der nemt kan redigeres (herunder af andre brugere, der måske er uerfarne), så prøv at bruge dynamiske serienavne! Og bemærk, at selv om denne artikel er blevet fokuseret på dropdown lister, kan dynamiske rækkevidde benyttes, hvor som helst du skal henvise til en rækkevidde eller liste, der kan variere i størrelse. God fornøjelse!
