Een vervolgkeuzelijst maken in Excel

De vervolgkeuzelijst is een ongelooflijk handig hulpmiddel dat het werken met informatie comfortabeler kan maken. Het maakt het mogelijk om meerdere waarden tegelijk in een cel te hebben, waarmee je kunt werken, zoals met alle andere. Om degene te selecteren die u nodig hebt, klikt u gewoon op het pijlpictogram, waarna een lijst met waarden wordt weergegeven. Nadat u een specifieke hebt geselecteerd, wordt de cel er automatisch mee gevuld en worden de formules op basis daarvan opnieuw berekend.

Excel biedt veel verschillende methoden voor het genereren van een vervolgkeuzemenu, en bovendien kunt u ze flexibel aanpassen. Laten we deze methoden in meer detail analyseren.

Proces voor het maken van lijsten

Om een ​​pop-upmenu te genereren, klikt u op de menu-items langs het pad “Data” – “Data Validation”. Er wordt een dialoogvenster geopend waarin u het tabblad "Parameters" moet vinden en erop moet klikken als het nog niet eerder is geopend. Het heeft veel instellingen, maar het item "Gegevenstype" is belangrijk voor ons. Van alle betekenissen is "Lijst" de juiste.

Het aantal methoden waarmee informatie in de pop-uplijst wordt ingevoerd, is vrij groot.

1. Onafhankelijke aanduiding van lijstelementen gescheiden door een puntkomma in het veld "Bron" op hetzelfde tabblad van hetzelfde dialoogvenster.
   

   

2. Voorlopige indicatie van waarden. Het veld Bron bevat het bereik waar de vereiste informatie beschikbaar is.
   

   

3. Een benoemd bereik opgeven. Een methode die de vorige herhaalt, maar het is alleen nodig om het bereik voorlopig te noemen.
   

   

Elk van deze methoden zal het gewenste resultaat opleveren. Laten we eens kijken naar methoden voor het genereren van vervolgkeuzelijsten in levensechte situaties.

Gebaseerd op gegevens uit de lijst

Laten we zeggen dat we een tabel hebben waarin de soorten verschillende soorten fruit worden beschreven.

Om een ​​lijst te maken in een vervolgkeuzemenu op basis van deze set informatie, moet u het volgende doen:

1. Selecteer de cel die is gereserveerd voor de toekomstige lijst.
2. Zoek het tabblad Gegevens op het lint. Daar klikken we op “Gegevens verifiëren”.
   

   

3. Zoek het item "Gegevenstype" en verander de waarde naar "Lijst".
   

   

4. Voer in het veld dat de optie "Bron" aangeeft het gewenste bereik in. Houd er rekening mee dat absolute referenties moeten worden opgegeven, zodat bij het kopiëren van de lijst de informatie niet verschuift.
   

Daarnaast is er een functie om lijsten in meer dan één cel tegelijk te genereren. Om dit te bereiken, moet u ze allemaal selecteren en dezelfde stappen uitvoeren als eerder beschreven. Nogmaals, u moet ervoor zorgen dat absolute verwijzingen worden geschreven. Als het adres geen dollarteken heeft naast de kolom- en rijnamen, moet u deze toevoegen door op de F4-toets te drukken totdat het $-teken naast de kolom- en rijnamen staat.

Met handmatige gegevensregistratie

In de bovenstaande situatie is de lijst geschreven door het vereiste bereik te markeren. Dit is een handige methode, maar soms is het nodig om de gegevens handmatig vast te leggen. Dit zal het mogelijk maken om dubbele informatie in het werkboek te voorkomen.

Stel dat we voor de taak staan ​​om een ​​lijst te maken met twee mogelijke keuzes: ja en nee. Om de taak te volbrengen, is het noodzakelijk:

1. Klik op de cel voor de lijst.
2. Open “Data” en daar vindt u de ons bekende rubriek “Data Check”.
   

   

3. Selecteer opnieuw het type "Lijst".
   

   

4. Hier moet u “Ja; Nee' als bron. We zien dat informatie handmatig wordt ingevoerd met behulp van een puntkomma voor opsomming.

Nadat we op OK hebben geklikt, hebben we het volgende resultaat.

Vervolgens maakt het programma automatisch een vervolgkeuzemenu in de juiste cel. Alle informatie die de gebruiker heeft opgegeven als items in de pop-uplijst. De regels voor het maken van een lijst in meerdere cellen zijn vergelijkbaar met de vorige, met als enige uitzondering dat u de informatie handmatig moet opgeven met een puntkomma.

Een vervolgkeuzelijst maken met behulp van de OFFSET-functie

Naast de klassieke methode is het mogelijk om de functie VERWIJDERENom vervolgkeuzemenu's te genereren.

Laten we het blad openen.

Om de functie voor de vervolgkeuzelijst te gebruiken, moet u het volgende doen:

1. Selecteer de cel waarin u de toekomstige lijst wilt plaatsen.
2. Open achtereenvolgens het tabblad "Gegevens" en het venster "Gegevensvalidatie".
   

   

3. Stel "Lijst" in. Dit gebeurt op dezelfde manier als de vorige voorbeelden. Ten slotte wordt de volgende formule gebruikt: =VERSCHUIVING(A$2$;0;0;5). We voeren het in waar de cellen worden gespecificeerd die als argument zullen worden gebruikt.

Vervolgens maakt het programma een menu met een lijst met fruit.

De syntaxis hiervoor is:

=OFFSET(referentie,line_offset,kolom_offset,[hoogte],[breedte])

We zien dat deze functie 5 argumenten heeft. Eerst wordt het eerste celadres dat moet worden verschoven gegeven. De volgende twee argumenten geven aan hoeveel rijen en kolommen moeten worden verschoven. Over ons gesproken, het argument Hoogte is 5 omdat het de hoogte van de lijst vertegenwoordigt. 

Vervolgkeuzelijst in Excel met gegevensvervanging (+ met behulp van de OFFSET-functie)

In het gegeven geval: VERWIJDEREN toegestaan ​​om een ​​pop-upmenu te maken dat zich in een vast bereik bevindt. Het nadeel van deze methode is dat je na het toevoegen van het item de formule zelf moet aanpassen.

Om een ​​dynamische lijst te maken met ondersteuning voor het invoeren van nieuwe informatie, moet u:

1. Selecteer de cel van belang.
2. Vouw het tabblad "Gegevens" uit en klik op "Gegevensvalidatie".
3. Selecteer in het geopende venster opnieuw het item "Lijst" en geef de volgende formule op als gegevensbron: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Klik op OK.

Dit bevat een functie COUNTIF, om onmiddellijk te bepalen hoeveel cellen zijn gevuld (hoewel het een veel groter aantal toepassingen heeft, schrijven we het hier gewoon voor een specifiek doel).

Om de formule normaal te laten functioneren, is het noodzakelijk om te traceren of er lege cellen op het pad van de formule zijn. Dat zouden ze niet moeten zijn.

Vervolgkeuzelijst met gegevens uit een ander blad of Excel-bestand

De klassieke methode werkt niet als u informatie uit een ander document of zelfs een blad in hetzelfde bestand moet halen. Hiervoor wordt de functie gebruikt INDIRECTE, waarmee u in het juiste formaat een link kunt invoeren naar een cel in een ander blad of in het algemeen - een bestand. U moet het volgende doen:

1. Activeer de cel waar we de lijst plaatsen.
2. We openen het raam dat we al kennen. Op dezelfde plaats waar we eerder bronnen voor andere bereiken hebben aangegeven, wordt een formule aangegeven in het formaat =INDIRECT(“[Lijst1.xlsx]Blad1!$A$1:$A$9”). Natuurlijk kunt u in plaats van Lijst1 en Blad1 respectievelijk uw boek- en bladnamen invoegen. 

Aandacht! De bestandsnaam staat tussen vierkante haken. In dit geval kan Excel het momenteel gesloten bestand niet gebruiken als informatiebron.

Houd er ook rekening mee dat de bestandsnaam zelf alleen zinvol is als het vereiste document zich in dezelfde map bevindt als die waarin de lijst wordt ingevoegd. Zo niet, dan dient u het adres van dit document volledig te vermelden.

Afhankelijke vervolgkeuzelijsten maken

Een afhankelijke lijst is er een waarvan de inhoud wordt beïnvloed door de keuze van de gebruiker in een andere lijst. Stel dat we een tafel voor ons open hebben staan ​​die drie reeksen bevat, die elk een naam hebben gekregen.

U moet deze stappen volgen om lijsten te genereren waarvan het resultaat wordt beïnvloed door de optie die in een andere lijst is geselecteerd.

1. Maak de eerste lijst met bereiknamen.
   

   

2. Bij het ingangspunt van de bron worden de vereiste indicatoren één voor één gemarkeerd.
   

   

3. Maak een 2e lijst afhankelijk van het type plant dat de persoon heeft gekozen. Als u bomen opgeeft in de eerste lijst, is de informatie in de tweede lijst ook "eik, haagbeuk, kastanje" en meer. Het is noodzakelijk om de formule op te schrijven op de plaats van invoer van de gegevensbron =INDIRECT(E3). E3 – cel met de naam van het bereik 1.=INDIRECT(E3). E3 – cel met de naam van de lijst 1.

Nu is alles klaar.

Hoe meerdere waarden selecteren uit een vervolgkeuzelijst?

Soms is het niet mogelijk om slechts één waarde de voorkeur te geven, dus moeten er meer dan één worden geselecteerd. Dan moet je een macro toevoegen aan de paginacode. Met de toetsencombinatie Alt + F11 wordt de Visual Basic Editor geopend. En daar wordt de code ingevoegd.

Private Sub Worksheet_Change (ByVal-doel als bereik)

    On Error Resume Next

    Indien Niet Intersect(Target, Range(«Е2:Е9»)) is niets en Target.Cells.Count = 1 Dan

        Application.EnableEvents = False

        Als Len (Target.Offset (0, 1)) = 0 Dan

            Doel.Offset (0, 1) = Doel

        anders

            Target.End (xlToRight) .Offset (0, 1) = Doel

        End If

        Doel.ClearContents

        Application.EnableEvents = Waar

    End If

End Sub 

Om de inhoud van de cellen hieronder weer te geven, voegen we de volgende code in de editor in.

Private Sub Worksheet_Change (ByVal-doel als bereik)

    On Error Resume Next

    Als niet snijden (doel, bereik («Н2:К2»)) is niets en doel.Cellen.Aantal = 1 Dan

        Application.EnableEvents = False

        Als Len (Target.Offset (1, 0)) = 0 Dan

            Doel.Offset (1, 0) = Doel

        anders

            Target.End (xlDown) .Offset (1, 0) = Doel

        End If

        Doel.ClearContents

        Application.EnableEvents = Waar

    End If

End Sub

En tot slot wordt deze code gebruikt om in één cel te schrijven.

Private Sub Worksheet_Change (ByVal-doel als bereik)

    On Error Resume Next

    If Not Intersect(Target, Range(«C2:C5»)) is niets en Target.Cells.Count = 1 Dan

        Application.EnableEvents = False

        newVal = Doel

        Toepassing.Ongedaan maken

        oldval = doel

        If Len (oldval) <> 0 En oldval <> newVal Dan

            Doel = Doel & «,» & nieuwe Waarde

        anders

            Doel = newVal

        End If

        Als Len (newVal) = 0 Dan Target.ClearContents

        Application.EnableEvents = Waar

    End If

End Sub

Bereiken kunnen worden bewerkt.

Hoe maak je een vervolgkeuzelijst met een zoekopdracht?

In dit geval moet u in eerste instantie een ander type lijst gebruiken. Het tabblad "Ontwikkelaar" wordt geopend, waarna u op het element "Invoegen" - "ActiveX" moet klikken of tikken (als het scherm aanraakt). Het heeft een combobox. U wordt gevraagd deze lijst te tekenen, waarna deze aan het document wordt toegevoegd.

Verder wordt het geconfigureerd via eigenschappen, waarbij een bereik is opgegeven in de optie ListFillRange. De cel waarin de door de gebruiker gedefinieerde waarde wordt weergegeven, wordt geconfigureerd met de optie LinkedCell. Vervolgens hoeft u alleen de eerste tekens op te schrijven, omdat het programma automatisch mogelijke waarden zal voorstellen.

Vervolgkeuzelijst met automatische gegevensvervanging

Er is ook een functie waarmee de gegevens automatisch worden vervangen nadat ze aan het bereik zijn toegevoegd. Het is gemakkelijk om dit te doen:

1. Maak een set cellen voor de toekomstige lijst. In ons geval is dit een set kleuren. Wij selecteren het.
   

   

2. Vervolgens moet het worden opgemaakt als een tabel. U moet op de knop met dezelfde naam klikken en de tabelstijl selecteren.
   

   

   

Vervolgens moet u dit bereik bevestigen door op de knop "OK" te drukken.

We selecteren de resulterende tabel en geven deze een naam via het invoerveld bovenaan kolom A.

Dat is het, er is een tabel en deze kan worden gebruikt als basis voor een vervolgkeuzelijst, waarvoor u het volgende nodig hebt:

1. Selecteer de cel waar de lijst zich bevindt.
2. Open het dialoogvenster Gegevensvalidatie.
   

   

3. We stellen het gegevenstype in op "Lijst", en als waarden geven we de naam van de tabel door het =-teken.
   

   

   

Alles, de cel is klaar, en de namen van de kleuren worden erin weergegeven, zoals we oorspronkelijk nodig hadden. Nu kunt u nieuwe posities toevoegen door ze eenvoudigweg in een cel te schrijven die zich direct na de laatste iets lager bevindt.

Dit is het voordeel van de tabel, dat het bereik automatisch groter wordt als er nieuwe gegevens worden toegevoegd. Dit is dan ook de handigste manier om een ​​lijst toe te voegen.

Hoe een vervolgkeuzelijst te kopiëren?

Om te kopiëren volstaat het om de toetsencombinatie Ctrl + C en Ctrl + V te gebruiken. De vervolgkeuzelijst wordt dus samen met de opmaak gekopieerd. Om opmaak te verwijderen, moet u een speciale plak gebruiken (in het contextmenu verschijnt deze optie na het kopiëren van de lijst), waarbij de optie "voorwaarden voor waarden" is ingesteld.

Selecteer alle cellen die een vervolgkeuzelijst bevatten

Om deze taak te volbrengen, moet u de functie "Selecteer een groep cellen" in de groep "Zoeken en selecteren" gebruiken.

Daarna wordt een dialoogvenster geopend waarin u de items "Alle" en "Deze zelfde" in het menu "Gegevensvalidatie" moet selecteren. Het eerste item selecteert alle lijsten en het tweede selecteert alleen die lijsten die vergelijkbaar zijn met bepaalde.