Dynamisch bereik - Functie Verschuiving

We starten terug van onze databank met de gegevens voor werknemers. We geven deze databank een bereiksnaam. Indien we onderaan nieuwe medewerkers toevoegen, wensen we dat deze collega's automatisch worden toegevoegd aan dit bereik. Kortom we maken een dynamisch bereik. Hiervoor gebruiken we de eerder geziene functies aantalarg en verschuiving.

• Maak een databank met gegevens over personeelsleden.

We selecteren de volledige databank.

• Selecteer één willekeurig cel van de databank.
• Gebruik de sneltoets CTRL * om de volledige databank te selecteren.

Via het naamvak kunnen we deze databank een bereiksnaam geven.

• Klik in het naamvak.
• Typ "personeel" welke de bereiknaam wordt voor deze databank.
• Bevestig deze naam met de "Enter"-toets.

Het is ook handig om te testen of de bereiksnaam bestaat.

• Klik ergens in het werkblad.
• Klik op het selectiepijltje van het naamvak.

Normaal gezien verschijnt de bereiksnaam.

• Klik op de naam "personeel".

De volledige databank wordt terug geselecteerd.
Via "Namen beheren" kan je eigenschappen voor de bereiksnaam wijzigen.

• Klik op het tabblad "Formules".
• Klik op de opdracht "Namen beheren".

Het dialoogvenster "Namen beheren" verschijnt. Hier kan je de eigenschappen zien voor het bereik "personeel".

In het vak "Verwijst naar" staat momenteel een vast bereik. Dit vervangen we door de functie "Verschuiving". In een vorig item werd de syntax voor deze functie beschreven. Om deze functie hier toe te passen is het vereist om de syntax in te typen. Ik wil er ook op wijzen dat het nodig is dat het werkblad met de databank geselecteerd is. Alsook dat tijdens het typen geen pijltjestoetsen kan worden gebruikt.

• Klik in het vak "Verwijst naar".
• Verwijder de tekst in dit vak.
• Typ "=verschuiving(a1"

• Duw op de functie "F4".

Er verschijnen dollartekens rond A1.

• Typ ";0;0;"

Nu komt de eigenschap Hoogte voor de functie verschuiving. De eerste kolom van de databank is een combinatie van de kolomtitel en nummers. Dus we gebruiken de functie aantalarg om te bepalen hoeveel rijen er zijn in deze databank. Komen er onderaan gegevens bij dan zal deze functie aantalarg ook de nieuwe rijen tellen. Wat de bedoeling is voor een dynamisch bereik.

• Typ "aantalarg(a:a"
• Duw op de functie "F4".

Nu komt de eigenschap Breedte voor de functie verschuiving. Bovenaan de databank zijn er titels aangebracht. Dit zijn teksten. Dus we gebruiken de functie aantalarg om te bepalen hoeveel kolomtitels er zijn.

• Typ ";aantalarg($1:$1))"

De syntax is volledig ingetypt. Vooraan het vak "Verwijst naar" staan twee pictogrammen. Het eerste is om het hetgeen werd ingetypt te verwijderen. Hierdoor keer je terug naar de oorspronkelijke inhoud. Het tweede pictogram is om de syntax te bevestigen. Het is beter om dit pictogram te gebruiken. Hierdoor wordt de syntax gecontroleerd. Indien je de knop "Sluiten" gebruikt en de syntax is niet correct, dan wordt hetgeen was ingetypt verwijderd. En dan kan je alles terug intypen.

• Klik op het pictogram met groen vinkje.

De syntax wordt aangevuld met de naam van het geselecteerd werkblad. Vandaar dat het nodig was om de databank visueel te zien.

• Klik op de knop "Sluiten".

Wanneer je nu op het pijltje klikt van de keuzelijst "Naamvak", dan is de bereiknaam "personeel" niet meer aanwezig.

Je kan wel nog deze bereiksnaam intypen. De databank wordt dan terug geselecteerd na de enter-toets. Dit kan eventueel een controle zijn.


We kunnen ook de functie Rijen gebruiken om te testen uit hoeveel rijen de databank bestaat.

• Selecteer een cel naast de databank.
• Breng de tekst "Aantal rijen" aan gevolgd door de tabulator toets.
• Klik op het tabblad "Formules" in het lint.
• Klik op het boekje "Zoeken en verwijzen".
• Klik op de functie "Rijen".

Het dialoogvenster "Functieargumenten" verschijnt. De cursor staat in het vak "Matrix".

• Duw op de functie-toets F3.

Het dialoogvenster "Naam plakken" verschijnt. In dit dialoogvenster staan alle bereiksnamen.

• Selecteer de naam "Personeel".
• Klik op "OK".

De bereiksnaam verschijnt in het vak "Matrix". Je kon deze naam ook intypen.

• Klik op de knop "OK".

De databank bestaat uit 11 rijen. We brengen een nieuwe werknemer aan in onze databank. En zie het aantal rijen wordt 12. We hebben een dynamisch bereik.

functie DBsom

Op onze databank personeel passen we de database-functie DBSom toe. We wensen het totaal loon te bekomen voor de mannelijke personeelsleden.

We geven onze databank een bereiksnaam.

• Selecteer één willekeurige cel van de databank.
• CTRL + *
• Klik in het naamvak.

• Typ "personeel" gevolgd door de enter-toets.

Vervolgens selecteren we enkel de kolomtitels.

• Klik in de eerste kolomtitel "PNR".
• CTRL SHIFT Pijltje naar rechts.

Vervolgens kopiëren we deze titels.

• kopiëren via de sneltoets CTRL C

We plaatsen een kopie van deze titels naast de databank.

• Selecteer de cel I1.
• Plakken via de sneltoets CTRL V.

We zorgen ervoor dat alle teksten in de kolommen leesbaar zijn. Daartoe selecteren we eerst het volledig werkblad.

• Klik op het knopje links van de kolom A.

Ga met de muisaanwijzer op een kolomovergang staan. Let op het uitzicht van de muisaanwijzer.

• Dubbelklik op deze kolomovergang.

• Typ in de cel I5 het woordje "loon".
• Typ in de cel L2 de letter "M".
• Klik in de cel J5.

Voor de databasefuncties is er geen boekje aanwezig. We openen de functiebibliotheek van excel. Daar vinden we de functie DBSom in de categorie "Database".

• Klik op het knopje fx.

Het dialoogvenster "Functie invoegen". De categorie "Laatst gebruikt" is geselecteerd.

• Klik op het keuzelijstje "Of selecteer een categorie".
• Klik op "Database".

• Schuif naar de functie DBSom.
• Klik op de functie DBSom.

Het dialoogvenster "Functieargumenten" verschijnt. Het vak "Database" is reeds geselecteerd. Hier komt de naam van de databank. Hiervoor laten we het dialoogvenster "Naam plakken" verschijnen.

• gebruik de functietoets F3.
• klik op de naam "personeel".
• Klik op "OK".

• Selecteer het vak "Veld".

Hier komt de naam van de kolomtitel waar de getallen staan die je wenst op te tellen. De naam kan ingetypt worden of je kan ook de kolomtitel selecteren.

• Klik in cel I5.
• Klik in het vak "Criteria".

• Selecteer de gekopieerde kolomtitels met daaronder de tweede rij met de voorwaarde erin.

• Klik op "OK".

Door de databank te filteren kunnen we het resultaat van de functie DBSom controleren.

• Selecteer een willekeurige cel van de databank.
• Klik op het tabblad "Gegevens".
• Klik op het knopje "Filter".

Bij de kolomtitels verschijnen pijltjes. Door hier op te klikken kan je beslissen welke rijen er zichtbaar blijven.

• Klik op het pijltje bij de kolomtitel "Geslacht".

Zowel V als M hebben een vinkje.

• Klik op het vinkje bij "(Alles selecteren)".

Hierdoor verdwijnen alle vinkjes.

• Klik op het vierkantje bij de letter "M".

Alle rijen met mannelijke personeelsleden blijven zichtbaar. We selecteren alle getallen in de kolom "Loon".

• Selecteer de cel G2.
• CTRL SHIFT pijltje naar beneden.

In de taakbalk verschijnen een aantal functies, waaronder ook de som.

Met het knopje "Wissen" kan je terug de volledige databank laten verschijnen.

• Klik op het knopje "Wissen".

Alle werknemers zijn terug zichtbaar.
De berekening past zich automatisch aan wanneer de voorwaarde wordt gewijzigd. We wensen het totaal loon te bekomen voor alle mannelijke personeelsleden uit de afdeling productie.

• Klik in de cel N2
• Typ "PROD".

Het nieuw totaal loon verschijnt.

We zorgen voor een keuzelijst voor het kiezen van het geslacht. Hiervoor kunnen we de validatie gebruiken.

• Klik in cel L2.
• Klik in het tabblad "Gegevens".
• Klik op het knopje "Gegevensvalidatie".

Het dialoogvenster "Gegevensvalidatie" verschijnt. Momenteel worden alle ingetypte waarden in deze wel aanvaard.

• Klik op de keuzelijst "Toestaan".
• Wijzig "Alle waarden" door "Lijst".

• Selecteer het vak "Bron".
• Typ "M;V".
• Klik op "OK".

In de cel L2 is nu een keuzelijst aangebracht. Hier kan je kiezen uit M of V.

• Klik op het pijltje van de keuzelijst in cel L2.
• Klik op "V".

Het totaal Loon voor alle vrouwelijke werknemers uit de afdeling "Productie" verschijnt.

Je kan bij de voorwaarden ook het jokerteken "*" gebruiken. "*" staat voor een willekeurige tekenreeks.

• Selecteer de cel K2.
• Typ "P*".

Nu verschijnt het totaal loon voor alle vrouwelijk werknemers uit de afdeling Productie waarvan de voornaam begint met de letter P. Dit is het loon voor Peggy Janssens.