Afhankelijke vervolgkeuzelijst maken
Afhankelijke vervolgkeuzelijst maken
Met deze handleiding kun je een keuzelijst met daarna een vervolgkeuze.
Een voorbeeld hiervan zie je hieronder.
Er zijn een aantal stappen die we moeten doen:
1. Maak een nieuw werkboek met twee mappen.
2. Creëer lijsten voor de keuzemenu’s.
3. Maak het hoofdkeuzemenu.
4. Maak het afhankelijke keuzemenu.
1. Maak een nieuw werkboek met twee mappen.
Maak een nieuwe werkmap.
Maak eventueel twee werkbladen aan.
Geef blad 1 de naam DataEntry.
Geef blad 2 de naam Lijsten.
Sla het werkboek op.
2. Creëer lijsten voor de keuzemenu’s.
Eerst gaan we de lijsten voor de vervolgkeuzemenu’s maken.
We moeten 3 dingen doen:
1. Typ een lijst met items op het Lijsten werkblad.
2. Formateer de lijsten als Excel tabel.
3. Geef de lijsten namen.
1. Typ een lijst met items op het Lijsten werkblad.
Ga naar het werkblad Lijsten.
Typ in cel B2 de naam van de hoofdlijst: ProductLijst.
Typ in cel B3 de naam van de eerste keuzelijst: Fruit.
Typ in cel B4 de naam van de tweede keuzelijst: Groente.
Let op! In de namen in de Product Lijst bestaan maar uit één woord (Fruit & Groente).
Dit is belangrijk omdat deze woorden gebruikt worden als Excel namen, en twee-woordnamen zijn niet toegestaan.
Nu de vervolglijsten maken.
Typ de Fruit Lijst in D2.
Typ de Groente Lijst in F2.
Neem onderstaande gegevens over.
1. Formateer de lijsten als Excel tabel.
Maak nu van elke lijst een Excel tabel.
Onderstaande stappen moet je voor elke lijst doen.
Selecteer een cel in de ProductLijst lijst.
Selecteer bovenin op het lint Start en kies voor Opmaken als tabel.
Kies voor een tabelstijl (ik kies meestal bij Normaal de eerste).
Er opent een nieuw schermpje.
Zorg dat het vinkje bij De tabel bevat kopteksten AAN staat.
OK.
Doe dit ook voor de andere twee lijsten.
Hier is het eindresultaat:
1. Geef de lijsten namen.
De tabellen die je net gemaakt hebt moeten namen hebben.
Deze zal je later gebruiken als je de vervolglijsten maakt.
Geef de Product Lijst een naam.
Selecteer de items in de Product Lijst tabel (B3:B4), dus niet de naam van de tabel.
Geef deze selectie in de Naambox linksboven de naam Producten.
Geef de Fruit en Groente Lijsten een naam.
Geef de Fruit selectie (D3:D6) de naam Fruit.
Geef de Groente selectie (F3:F6) de naam Groente.
3. Maak het hoofdkeuzemenu.
Maak dit menu als validatielijst.
Ga naar het DataEntry werkblad.
Typ eerst de koppen van de tabel in B2 (ProductType) en C2 (Item).
Selecteer cel B2 en selecteer bovenin op het lint Start en kies voor Opmaken als tabel.
Nu worden zowel B2 als C2 geselecteerd.
Kies voor een tabelstijl (ik kies meestal bij Normaal de eerste).
Er opent een nieuw schermpje.
Zorg dat het vinkje bij De tabel bevat kopteksten AAN staat.
OK
Nu gaan we het hoofdkeuzemenu maken.
Dit doen we in de Product Type kolom.
Selecteer op het cel B3.
Op het lint klik op de Gegevens op Gegevens validatie.
Er opent een nieuw schermpje.
Kies voor Toestaan voor Lijst, en typ bij Bron =Producten.
Klik op OK
Misschien komt onderstaande melding, klik dan op Ja.
Check nu of het goed gedaan hebt door op het pijltje rechts te klikken.
1. Maak het afhankelijke keuzemenu.
Selecteer cel C3.
Op het lint selecteer de Gegeven tab en klik dan op Gegevensvalidatie.
Er opent een nieuw schermpje.
Kies voor Toestaan voor Lijst, en typ bij Bron =INDIRECT(B3).
Klik op OK
Misschien komt onderstaande melding, klik dan op Ja.
Check nu of het goed gedaan hebt door eerst op de Product Type een selectie te maken door op het pijltje rechts te klikken en dan op de Item lijst een selectie te maken.
Mocht je erachter komen dat je op je Tab Lijsten nog items toe wil voegen dan kun je die gegevens onder de gegevens zetten die je al hebt ingevoerd.
Cellen Blokkeren
Selecteer cellen die geblokkeerd moeten worden
Rechtermuisknop
Celeigenschappen
Tab Bescherming
Zet het vinkje bij beveiligd AAN
OK
Selecteer cellen die niet geblokkeerd moeten worden
Rechtermuisknop
Celeigenschappen
Tab Bescherming
Zet het vinkje bij beveiligd UIT
OK
Selecteer het hele werkblad
Tab Controleren, kies voor Blad beveiligen.
Zorg dat het vinkje bij ontgrendelde cellen AAN staat, en de rest uit.
Beveilig blad (eventueel met wachtwoord, maar hoeft niet)
Beveiliging van blad halen
Tab Controleren, kies voor Blad beveiligen.
Beveiliging opheffen
Een zoekbare validatielijst maken
Deze tip komt van Gratiscursus.be
Hier leg ik uit hoe je een validatielijst maakt die, afhankelijk van ingetypte zoekletters, wijzigt.
Een dynamisch zoekbare validatielijst dus.
Zoals je kunt zien heb ik in cel B2 een validatielijst die is gebaseerd op de lijst in kolom G.
In deze lijst staan “slechts” 148 namen, dus dit valt nog mee om de juiste persoon te zoeken in de validatielijst, maar toch.
Stel je voor je hebt er duizenden, en je weet slechts een deel van de naam.
Ik kan je garanderen je vindt deze nooit.
Wat ik hiervoor nodig heb is een validatielijst die, afhankelijk van de ingetypte letters in de cel, dynamisch geupdate wordt.
Dus typ ik bijvoorbeeld bart of Bart in het validatievak, dan wil ik enkel de personen terugvinden die aan deze voorwaarde voldoen, in de validatielijst.
Typ ik de letters ceu, dan worden enkel de personen weergegeven waarin deze letters voorkomen.
Zoals bijvoorbeeld “Ceurvels”, “Ceulemens, “Ceustermans” en nogmaals “Ceulemans”.
Of deze letters zich bevinden in de voornaam of familienaam, dit maakt niks uit.
Wanneer ze er in staan en in de juiste volgorde, dan worden deze opgenomen in de validatielijst.
Laat ik beginnen met het begin.
Het eerste wat we gaan doen is zoeken of de ingetypte tekst in cel D2, hetzelfde is dan de gegevens in onze lijst in kolom G.
De formule hiervoor is:
=ALS(ISGETAL(VIND.SPEC($D$2;G2));MAX($F$1:F1)+1;0)
Ik probeer deze even uit te leggen.
(VIND.SPEC($D$2;G2)
De functie VIND.SPEC zal de ingetypte tekst in D2 zoeken in cel G2.
De cel D2 maken we absoluut want de formule wordt later gekopieerd naar onder.
De functie ISGETAL zal de waarde weergeven als WAAR of ONWAAR.
De functie ALS +1;0 zal deze waarden omzetten in getallen, en wanneer hij er een heeft gevonden er een bijtellen.
Heeft hij er geen gevonden dan geeft hij een nul weer.
Dus als de waarde in cel D2 gelijk is aan de waarde van cel G2, dan geef je me een een, is dit niet het geval dan geef je me een nul.
Het getal 1 voorzie je van een plusteken omdat hij er telens wanneer dit het geval is een moet bijtellen.
De functie MAX($F$1:F1) zal deze zoekopdracht uitvoeren in het bereik $F$1:F1. En ook hier maken we de eerste celverwijzing absoluut.
Dus kopieren we deze cel naar onder, dan zal je zien dat telkens hij de ingetypte letter in cel D2 vindt in kolom G hij er 1 zal bijtellen.
Nu, op basis van de gegevens in kolom F gaan we onze dynamische lijst maken in kolom I.
De formule hiervoor is:
=ALS.FOUT(VERT.ZOEKEN(RIJEN($I$2:I2);$F$2:$G$200;2;0);"")
Ik probeer deze even uit te leggen.
De functie VERT.ZOEKEN zal zoeken in het bereik ingegeven in de functie RIJEN
De functie ALS.FOUT zal voorkomen dat er een foutmelding wordt weergegeven wanneer er niks wordt gevonden. Wanneer er niks wordt gevonden moet hij een lege cel weergeven. Daarvoor staan de twee dubbele aanhalingsteken.
Nu, op basis van deze dynamische lijst moeten we onze validatielijst maken.
Omdat de dynamische lijst niet altijd evenveel namen zal bevatten, moeten we hiervan eerst het aantal bepalen.
Dit doen we met de functies AANTAL.ALS in samenwerking met de funtie VERSCHUIVING::
=VERSCHUIVING(Blad1!$I$2;;;AANTAL.ALS(Blad1!$I$2:$I$300;”?*”))
De functie AANTAL.ALS bepaalt het aantal gevonden namen.
De functie VERSCHUIVING bepaalt de te starten cel in het bereik I2, bepaalt het aantal rijen, het aantal kolommen en de hoogte.
Het aantal rijen en kolommen is steeds 1, dus hiervoor hoeven we niks te typen ;;.
En de hoogte wordt bepaalt door de functie AANTAL.ALS
En de criteria hiervoor is ?*. Vraagteken en vermenigvuldigingsteken. Vraagteken staat voor een zelfde teken, vermenigvuldigingsteken voor opeenvolging van tekens.
Deze formule gebruiken we als bereik.
Klik de knop Beheer en klik de knop Nieuw.
Geef het bereik een naam, validatie_lijst lijkt me wel wat.
En kopieer de zojuist aangemaakte formule in het vak “Verwijst naar:”::
=VERSCHUIVING(Blad1!$I$2;;;AANTAL.ALS(Blad1!$I$2:$I$300;”?*”))
Klik de knop Ok om het eerste venster te sluiten.
Klik de knop “Sluiten om het tweede venster te sluiten.
En ten laatste gaan we de nieuwe validatielijst toevoegen aan cel D2.
Dus selecteer cel D2.
Klik de knop “Validatielijst” in het lint.
En in het venster dat opent kies je in het vak “Toestaan” voor “Lijst”.
Je plaatst de cursor in het vak “Bron”, en klikt de F3 toets op je toetsenbord.
Selecteer de zojuist aangemaakte lijst in het venster dat opent en klik de knop OK.
En als laatste verwijderen we nog het vinkje voor de tekst “Foutmelding…”
Klik de knop OK
Onze zoekbare validatielijst is klaar
Excel automatisch een of meerdere bestanden laten openen
Dit is een handige tip wanneer je dagelijks hetzelfde bestand of bestanden moet openen.
Waarschuwing
Deze tip wordt uitgevoerd elke keer als je Excel opstart.
In deze tip leg ik je uit, hoe je telkens wanneer je Excel opstart, een of meerdere bestanden worden geopend.
Als voorbeeld heb ik een map gemaakt op mijn bureaublad genaamd Nieuwe Map
In deze map zitten twee Excelbestanden die ik automatisch wens te openen wanneer ik Excel opstart.
(Waar deze map staat, en hoeveel bestanden hierin zitten maakt niks uit.)
Open Excel
Klik op de Officeknop linksboven
Klik op Opties voor Excel
Klik op Geavanceerd
Ga helemaal naar beneden bij het kopje Algemeen.
In het vakje Alle bestanden bij opstarten openen moet de link naar de map komen.
Hoe deze te vinden:
Open de map waar de bestanden in staan.
Klik met je rechtermuiskop in de adresbalk en klik op Adres kopieeren.
Ga weer naar het vakje Alle bestanden bij opstarten openen.
Klik hierin met je rechtermuisknop
Klik op plakken
Wanneer je nu Excel opstart, openen automatisch alle bestanden in deze map.
Keuzelijst
Deze tip komt van Gratiscursus.be
Een keuzelijst gebruiken uit de verschillende besturingselementen.
Ga naar het tabblad “Ontwikkelaars”.
Klik de knop “Invoegen” en kies “Keuzelijst”
Klik en sleep in het document op de plaats waar je deze keuzelijst wenst.
De grootte en de positie doet er eigenlijk niet toe, je kunt dit steeds aanpassen.
Rechtsklik de keuzelijst en kies “Besturingselement opmaken”.
In het bovenste vak van het venster dat opent geef je het bereik in met de verschillende gemeenten.
In het onderste vak geef je een cel in waarmee je de koppeling wil maken. In dit geval kies ik I3, maar hiermee doe je wat je wenst.
Typ volgende formule in deze cel: =$i$3
Dit zal de inhoud van de eerste rij uit de tabel weergeven.
Afgaande op deze celverwijzing willen we het aantal inwoners van de gemeente weten.
Je kunt dit doen met de VERT.ZOEKEN functie, of met de INDEX functie.
De INDEX functie laat ons toe gegevens uit een lijst weer te geven, dus geef ik het voorbeeld met deze functie.
Maar je zou, toch in dit geval, ook de VERT.ZOEKEN functie kunnen gebruiken.
Typ volgende formule: =INDEX(C:C;i3+1)
Wat staat hier nu:
Zoek in kolom C de waarde die staat in cel i3 +1
Waarom +1? Wel omdat de lijst pas begint op de tweede rij.
Dit geeft netjes het aantal bewoners van de geselecteerde gemeente.
Selecteer je een andere gemeente in de keuzelijst, dan wordt het aantal inwoners aangepast.
Naamkaartjes maken
Dit is een handleiding om in Excel een namenlijst te maken en deze daarna samen te voegen met een word document om Naamkaartjes te maken voor bijvoorbeeld een evenement.
Ik gebruik hier het voorbeeld voor een evenement waar mensen van diverse bedrijven en provincies bijeen komen.
Start Excel.
Open eventueel het bestand met alle gegevens die je hebt.
Verwijder de kolommen die je niet nodig hebt voor de naamkaartjes.
Geef de bovenste rij de namen van de diverse kolommen.
Nummer, Voornaam, Tussenvoegsel, Achternaam, Bedrijf, Provincie.
Zet de bovenste rij in een vet lettertype.
Selecteer de bovenste rij.
Klik op het nummer 1 voor de rij (de rij wordt geselecteerd).
Klik in het lint op de letter B.
De rij is nu in vet gezet.
Zet de bovenste rij vast.
Dit zorgt ervoor dat als naar beneden scrolt de bovenste rij blijft staan.
Selecteer de bovenste rij.
Klik op tabblad Beeld.
Klik op Deelvensters blokkeren.
Er klapt een submenu open.
Klik op Titels blokkeren.
Sla het bestand op als Naamkaartjes.
Nu kun je het bestand vullen met al je gegevens.
Zie hieronder mijn voorbeeld.
Als je lijst gevuld is, is het handig als de lijst op (achter)naam gesorteerd wordt.
Selecteer al de kolommen door met je muis ingedrukt over alle kolommen te gaan.
De kolommen veranderen van kleur.
Sorteren lijst op alfabetische volgorde.
Tabblad Start.
Klik op Sorteren en filteren.
Kies voor Aangepast sorteren.
Er opent een nieuw schermpje.
Zorg dat het vinkje bij De gegevens bevatten kopteksten aangevinkt is.
Kies voor de eerste regel voor:
Sorteren op: Achternaam.
Laat de overige velden leeg.
Klik op Niveau toevoegen.
Sorteren op: Voornaam.
Sorteer eventueel de andere kolommen ook.
Klik op OK.
De lijst is nu alfabetisch gesorteerd.
Sla het bestand op.
Als je van deze lijst naamkaartjes wilt maken waar al deze gegevens opstaan is het handig als de kolom met het nummer er als een tekst veld uitziet.
Alle kommen in tekstformaat maken
Selecteer al de kolommen door met je muis ingedrukt over alle kolommen te gaan.
De kolommen veranderen van kleur.
Klik met de rechtermuisknop ergens in de kolommen die je geselecteerd hebt.
Er opent een nieuw schermpje.
Klik op Celeigenschappen.
Er opent een nieuw schermpje.
Selecteer bij de Categorie voor Tekst.
OK.
Sla het bestand op.
Het bestand is nu klaar om Naamkaartjes te maken.
Hoe je dit doet kun je lezen bij:
Ik gebruik de kaartjes van Avery met nummer C32017. De maten zijn 8,5 x 5,4 centimeter.
Reeks horizontaal doorvoeren
Soms moet je een lange reeks getallen of datums horizontaal doorvoeren in je Excel sheet.
Natuurlijk kun je dit doen door de vulgreep naar rechts te slepen,
maar als je veel cellen moet vullen is dat veel werk.
Je kunt ook het gereedschap Doorvoeren gebruiken.
Ik ga in de voorbeeld alle dagen van het huidige jaar op rij A zetten.
Zet in cel A1 de datum 1 januari (1-1).
Zet de eigenschappen van cel A1 op datum.
Klik met je rechtermuisknop op cel A1.
Kies voor Celeigenschappen.
Kies voor Datum en de juiste notatie.
Let op bij een datumnotatie kiest Excel altijd voor het huidige jaar.
Als je een ander jaar nodig hebt, moet je even handmatig het jaar aanpassen.
Klik in tabblad Start voor Doorvoeren en kies dan voor Reeks…
Ik wil alle dagen van het jaar invoegen.
Ik kies bij Type voor Datum en bij Datumeenheid voor dag.
Bij eindwaarde geef ik de laatste dag van de reeks in (3-12).
OK
Alle dagen zijn nu ingevuld.
Rijen om en om kleuren zonder tabel
Deze tip komt van Gratiscursus.be
Rijen om en om in te kleuren, maar dit zonder het invoegen van een tabel.
Voor gefilterde rijen zie halverwege deze instructie
Selecteer eerst alle rijen die je om en om wenst in te kleuren.
Klik vervolgens de knop “Voorwaardelijke opmaak”, en kies “Nieuwe regel”.
In het venster dat opent kies je het type “Een formule gebruiken…” en typ je volgende formule:
WAAR
In 2 rijen zal er een worden voorzien van opmaak.
Klik de knop “Opmaak”.
Wanneer jij bv. liever hebt dat in 5 rijen twee worden voorzien van opmaak, dan typ je de formule:
ONWAAR
Kies het tabblad “Opvulling” en kies een kleur die je wenst te gebruiken als opvulling.
Klik de knop ok.
Alle rijen zijn netjes om en om ingekleurd.
Gefilterde rijen om en om kleuren zonder tabel
Hiervoor gebruiken we volgende formule:
=REST(SUBTOTAAL(3;$A$1:$A2);2)
Filter je nu op 2 items uit de lijst,
of 3 items uit de lijst, dit maakt niet uit, de rijen worden om en om ingekleurd.
Verwijderen tekens in regel
Deze tip is handig als je uit een kolom maar een gedeelte van de gegevens nodig hebt.
Als voorbeeld heb ik even een lijstje met postcodes gebruikt.
|
A |
B |
C |
D |
E |
1 |
1001aa |
1001 |
aa |
1aa |
1a |
2 |
2002bb |
2002 |
bb |
2bb |
2b |
3 |
3003cc |
3003 |
cc |
3cc |
3c |
Kolom B geeft als resultaat alleen de eerste 4 karakters. (de cijfers van de postcode)
Gebruik hiervoor de volgende formule: =LINKS(A1;4)
Kolom C geeft als resultaat alleen de laatste 2 karakters. (de letters van de postcode)
Gebruik hiervoor de volgende formule: =RECHTS(A1;2)
Je kunt ook de laatste de laatste 2 getallen uit een rij verwijderen, terwijl het niet uitmaakt hoe lang de rij is.
Gebruik hiervoor de volgende formule: =LINKS(A1;LENGTE(A1)-2)
Natuurlijk is het ook mogelijk om slechts een gedeelte uit de tekst te halen.
(Ik weet niet precies waar je dit voor nodig zou kunnen hebben, voor de volledigheid heb ik het erbij gezet)
Kolom D geeft als resultaat een deel van de tekst, in dit geval vanaf het 4e tot en met het 999 karakter (999 is meestal wel het einde van de regel)
Gebruik hiervoor de volgende formule: =DEEL(A1;4;999)
Kolom E geeft als resultaat een deel van de tekst, in dit geval vanaf het 4e karakter en dan 2 karakters lang.
Gebruik hiervoor de volgende formule: =DEEL(A1;4;2)