Computer help - file server


[ Blog ] - [ File Server ] - [ Удаленная компьютерная помощь ]

Excel-spreadsheets bevatten vaak vervolgkeuzelijsten voor cellen om de gegevensinvoer te vereenvoudigen en/of te standaardiseren. Deze vervolgkeuzelijsten worden gemaakt met behulp van de gegevensvalidatiefunctie om een ​​lijst met toegestane vermeldingen op te geven.

Om een ​​eenvoudige vervolgkeuzelijst in te stellen, selecteert u de cel waarin gegevens worden ingevoerd, klikt u op Gegevensvalidatie (op het tabblad Gegevens), selecteert u Gegevensvalidatie, kiest u Lijst (onder Toestaan ​​:) en voert u in de lijstitems (gescheiden door komma's) in het veld Bron: (zie Afbeelding 1).



In dit type standaard dropdownmenu wordt de lijst met toegestane items gespecificeerd binnen de gegevensvalidatie zelf; Daarom moet de gebruiker de gegevensvalidatie openen en bewerken om wijzigingen in de lijst aan te brengen.Dit kan echter moeilijk zijn voor onervaren gebruikers of in gevallen waar de lijst met keuzes lang is.

Een andere optie is om de lijst in een benoemd bereik in de spreadsheet te plaatsen en vervolgens die bereiknaam (voorafgegaan door een gelijkteken) op te geven in het Bron: veld van de gegevensvalidatie (zoals weergegeven in Figuur 2 ).



Deze tweede methode maakt het gemakkelijker om de keuzes in de lijst te bewerken, maar het toevoegen of verwijderen van items kan problematisch zijn. Aangezien het benoemde bereik (FruitChoices, in ons voorbeeld) verwijst naar een vast celbereik ($ H $ 3: $ H $ 10, zoals weergegeven), worden deze niet weergegeven in de vervolgkeuzelijst als er meer keuzes worden toegevoegd aan de cellen H11 of lager. (aangezien die cellen geen deel uitmaken van het FruitChoices-assortiment).

Evenzo, als, bijvoorbeeld, de peren en aardbeien-items worden gewist, zullen ze niet langer in de vervolgkeuzelijst verschijnen, maar in plaats daarvan zal de vervolgkeuzelijst twee 'lege' keuzes bevatten, aangezien de vervolgkeuzelijst nog steeds verwijst naar het volledige FruitChoices-assortiment , inclusief de lege cellen H9 en H10.

Om deze redenen moet bij het gebruik van een normaal benoemd bereik als de lijstbron voor een vervolgkeuzelijst het benoemde bereik zelf worden bewerkt om meer of minder cellen op te nemen als items worden toegevoegd aan of verwijderd uit de lijst.

Een oplossing voor dit probleem is om de naam van een dynamisch bereik te gebruiken als bron voor de vervolgkeuzelijsten.De naam van een dynamisch bereik is een naam die automatisch wordt uitgebreid (of samengetrokken) om exact overeen te komen met de grootte van een gegevensblok wanneer items worden toegevoegd of verwijderd. Om dit te doen, gebruikt u een formule in plaats van een vast bereik van celadressen om het benoemde bereik te definiëren. Een dynamisch bereik instellen in Excel

Een normale (statische) bereiknaam verwijst naar een gespecificeerd celbereik ($ H $ 3: $ H $ 10 in ons voorbeeld, zie hieronder):



Maar een dynamisch bereik wordt gedefinieerd met behulp van een formule (zie hieronder, afkomstig uit een aparte spreadsheet die dynamische bereiknamen gebruikt):



Voordat we beginnen, zorg ervoor dat u ons Excel-voorbeeldbestand downloadt (sorteermacro's zijn uitgeschakeld).

Laten we deze formule eens in detail bekijken. De keuzes voor Fruit staan ​​in een blok cellen direct onder een kop (FRUITS). Aan die kop is ook een naam toegewezen: FruitsHeading:



De volledige formule die wordt gebruikt om het dynamische bereik voor de Fruits-keuzes te definiëren, is: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHeading, 1,0,20,1)), 0,0), 0) -1,20), 1)

FruitsHeading verwijst naar de kop die één rij boven het eerste item in de lijst staat. Het getal 20 (twee keer gebruikt in de formule) is de maximale grootte (aantal rijen) voor de lijst (dit kan naar wens worden aangepast).

Merk op dat in dit voorbeeld er slechts 8 vermeldingen in de lijst zijn, maar er zijn ook lege cellen daaronder waar extra vermeldingen kunnen worden toegevoegd. Het getal 20 verwijst naar het hele blok waar vermeldingen kunnen worden gemaakt, niet naar het werkelijke aantal vermeldingen.

Laten we nu de formule opsplitsen in stukjes (kleurcodering elk stuk), om te begrijpen hoe het werkt: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET ( FruitsHeading, 1,0,20,1)), 0,0), 0) -1,20), 1)

Het 'binnenste' stuk is OFFSET (FruitsHeading, 1,0,20, 1).Dit verwijst naar het blok van 20 cellen (onder de FruitsHeading-cel) waar keuzes kunnen worden ingevoerd. Deze OFFSET-functie zegt in feite: begin bij de FruitsHeading-cel, ga 1 rij omlaag en meer dan 0 kolommen en selecteer vervolgens een gebied dat 20 rijen lang en 1 kolom breed is. Dus dat geeft ons het blok met 20 rijen waar de fruitkeuzes worden ingevoerd.

Het volgende deel van de formule is de ISBLANK-functie: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (the above), 0,0), 0) -1 , 20), 1)

Hier is de OFFSET-functie (hierboven uitgelegd) vervangen door "het bovenstaande" (om dingen gemakkelijker leesbaar te maken).Maar de ISBLANK-functie werkt op het celbereik van 20 rijen dat de OFFSET-functie definieert.

ISBLANK maakt vervolgens een set van 20 TRUE en FALSE waarden, waarmee wordt aangegeven of elk van de individuele cellen in het bereik van 20 rijen waarnaar wordt verwezen door de OFFSET-functie leeg (leeg) is of niet. In dit voorbeeld zijn de eerste 8 waarden in de set ONWAAR, aangezien de eerste 8 cellen niet leeg zijn en de laatste 12 waarden WAAR.

Het volgende deel van de formule is de INDEX-functie: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (the above, 0,0), 0) -1,20) , 1)

Nogmaals, "het bovenstaande" verwijst naar de ISBLANK en OFFSET-functies die hierboven zijn beschreven.De functie INDEX retourneert een array met de 20 TRUE/FALSE-waarden die zijn gemaakt door de functie ISBLANK.

INDEX wordt normaal gesproken gebruikt om een ​​bepaalde waarde (of reeks waarden) uit een gegevensblok te kiezen door een bepaalde rij en kolom (binnen dat blok) op te geven. Maar het instellen van de rij- en kolominvoer op nul (zoals hier wordt gedaan) zorgt ervoor dat INDEX een array retourneert die het volledige gegevensblok bevat.

Het volgende deel van de formule is de MATCH-functie: = OFFSET (FruitsHeading, 1,0, IFERROR (MATCH (TRUE, the above, 0) -1,20), 1)

De MATCH-functie retourneert de positie van de eerste TRUE-waarde, binnen de array die wordt geretourneerd door de INDEX-functie.Aangezien de eerste 8 items in de lijst niet leeg zijn, zijn de eerste 8 waarden in de array FALSE en is de negende waarde TRUE (aangezien de 9e rij in het bereik leeg is).

Dus de MATCH-functie retourneert de waarde 9. In dit geval willen we echter echt weten hoeveel items er in de lijst staan, dus trekt de formule 1 af van de MATCH-waarde (wat de positie van de laatste invoer). Dus uiteindelijk geeft MATCH (TRUE, het bovenstaande, 0) -1 de waarde 8 terug.

Het volgende deel van de formule is de IFERROR-functie: = OFFSET (FruitsHeading, 1,0, IFERROR (the above, 20), 1)

De IFERROR-functie retourneert een alternatieve waarde als de eerste opgegeven waarde resulteert in een fout.Deze functie is inbegrepen omdat, als het hele blok cellen (alle 20 rijen) gevuld is met items, de MATCH-functie een foutmelding geeft.

Dit komt omdat we de MATCH-functie vertellen om te zoeken naar de eerste TRUE-waarde (in de array met waarden van de ISBLANK-functie), maar als GEEN van de cellen leeg is, zal de hele array worden gevuld met FALSE waarden. Als MATCH de doelwaarde (TRUE) niet kan vinden in de array die wordt gezocht, retourneert het een fout.

Dus als de hele lijst vol is (en dus MATCH retourneert een fout), zal de IFERROR-functie in plaats daarvan de waarde 20 retourneren (wetende dat er 20 items in de lijst moeten zijn).

Ten slotte geeft OFFSET (FruitsHeading, 1,0, de bovenstaande, 1) het bereik terug waarnaar we eigenlijk op zoek zijn: begin bij de FruitsHeading-cel, ga 1 rij omlaag en meer dan 0 kolommen, en selecteer vervolgens een gebied dat is echter veel rijen lang als er items in de lijst zijn (en 1 kolom breed). Dus de hele formule samen retourneert het bereik dat alleen de werkelijke items bevat (tot aan de eerste lege cel).

Door deze formule te gebruiken om het bereik te definiëren dat de bron is voor de vervolgkeuzelijst, kunt u de lijst vrij bewerken (items toevoegen of verwijderen, zolang de resterende items beginnen bij de bovenste cel en aaneengesloten zijn) en de vervolgkeuzelijst geeft altijd de huidige lijst weer (zie Afbeelding 6).



Het voorbeeldbestand (Dynamic Lists) dat hier is gebruikt, is bijgevoegd en kan worden gedownload vanaf deze website. De macro's werken echter niet, want WordPress houdt niet van Excel-boeken met macro's erin.

Als alternatief voor het specificeren van het aantal rijen in het lijstblok, kan aan het lijstblok een eigen bereiknaam worden toegewezen, die vervolgens kan worden gebruikt in een aangepaste formule. In het voorbeeldbestand gebruikt een tweede lijst (namen) deze methode. Hier krijgt het volledige lijstblok (onder de kop “NAMES”, 40 rijen in het voorbeeldbestand) de bereiknaam NameBlock toegewezen.De alternatieve formule voor het definiëren van de NamesList is dan: = OFFSET (NamesHeading, 1,0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (NamesBlock), 0,0), 0) -1, ROWS (NamesBlock)), 1)

waar NamesBlock OFFSET (FruitsHeading, 1,0,20,1) vervangt en ROWS (NamesBlock) de 20 (aantal rijen) in de eerdere formule vervangt.

Dus, voor vervolgkeuzelijsten die gemakkelijk kunnen worden bewerkt (inclusief door andere gebruikers die misschien onervaren zijn), probeer het gebruik van dynamische bereiknamen! En merk op dat, hoewel dit artikel is gericht op vervolgkeuzelijsten, dynamische bereiknamen overal kunnen worden gebruikt waar u naar een bereik of lijst wilt verwijzen die in grootte kan variëren.Geniet ervan!

.
SETUP UA COMPUTER BLOG