Functie LIJNSCH

In een werkblad zijn een aantal waarden aangebracht voor verschillende maanden. We wensen de parameter Lijnsch voor de vergelijking van de trendlijn via een formule te berekenen. Bij het aanbrengen van de trendlijn werd de formule reeds weergegeven. De vergelijking ziet er uit als y = mx + b. We bepalen de parameter m met behulp van de functie LIJNSCH.

• Selecteer de cel H6.

• Klik op het tabblad "Formules" in het lint.

• Klik op het boek "Meer functies".
• Schuif naar het boek "Statistisch".

• Scroll naar "Lijnsch" en klik erop.

Het dialoogvenster "Functieargumenten" verschijnt. Het argument "Y-bekend" is reeds geselecteerd.

• Klik in de cel C2.

• Druk de toetsen CTRL, SHIFT en pijltjestoets naar beneden in.

• Klik in het tekstvak "X-bekend".

• Selecteer de cellen B2 tot B11.

De argumenten Const en Stat vullen we niet in.

• Klik op "OK".

De waarde voor de parameter m van de vergelijking voor de trendlijn verschijnt in de geselecteerde cel.

VBA object Worksheets

In een vorig punt hebben we de VBA-code gezien om tekst te plaatsen in een cel van het geselecteerd werkblad via het object Cells of Range. In dit punt bespreken we het object Worksheets. We gaan verder met het vorige document "invullen.xlsm".
Met het object Worksheets kunnen we bepalen in welk werkblad de tekst gaat komen. In het punt van gisteren hebben reeds het werkblad "Invullen" aangebracht".

We selecteren het werkblad "Blad1".

• Klik op onderaan op het werkblad "Blad1".

We brengen in de module "mdlInvullen2" een nieuwe subroutine "Invullen3" aan.

• Gebruik de sneltoets ALT F11 om de terug naar de visual basic editor te gaan.
• Plaats de cursor onder de subroutine "Invullen2".
• Typ "Sub Invullen3".
• Druk op de toets Enter.

Automatisch wordt de subroutine afgesloten met "End Sub".

• Plaats de cursor tussen "Sub Invullen3" en "End Sub".

Om iets naar een bepaald werkblad te schrijven kan je het object "Worksheets" gebruiken.

• Typ worksheets(

In een tip wordt een index gevraagd. Dit kan de naam van het werkblad zijn of een getal dat aangeeft het hoeveelste werkblad dit is.
Je kan ofwel worksheets("Invullen") gebruiken. Maar ook worksheets(2) is mogelijk.
We kiezen voor de naam van het werkblad. We wensen dat de tekst nu verschijnt in de cel A5.

• Typ "Invullen").Cells(5,1).value = "Hier iets invullen"

We testen de code.

• Plaats de cursor in de regel van sub Invullen3
• Klik op de pictogram met groene pijl.

We gaan terug naar Excel. Het werkblad "Blad1" is nog steeds geselecteerd.

• Klik onderaan op het tabblad "Invullen" om dit werkblad te selecteren.

We merken dat onze tekst in cel A5 is verschenen.

Een nieuw werkblad Invoegen

In dit punt zien we ter voorbereiding van het volgend item, hoe we een nieuw werkblad kunnen invoegen.

• Start vanuit een nieuw excel-werkmap of open een bestaand excel-werkmap.

Wij gebruiken het bestand "invullen.xlsm" van vorig punt.

• Klik onderaan op het plusteken.

Een nieuw werkblad verschijnt met in ons geval de standaardnaam "Blad2". De naam van dit ingevoegd werkblad hangt af van hoeveel werkbladen er nog de standaardnaam bevatten.

We wijzigen de naam van het werkblad.

• Dubbelklik op "Blad2" (of de naam van het ingevoegd werkblad".
• Typ "Invullen".
• Bevestig deze naam met de toets Enter.

VBA - Object Cells

Naargelang de instellingen verschijnt er bij het openen van een document met vba-code bovenaan een waarschuwing. Om de code te kunnen gebruiken is het vereist om op de vermelding "inhoud inschakelen" te klikken.

In een vorig punt hebben we het object Range besproken, om iets weg te schrijven, in de cel van het werkblad. In dit punt bespreken we het object Cells. We gebruiken het vorig document "Invullen.xlsm".

• Open het bestaand document "Invullen.xlsm" of start met een nieuw document.
• Klik indien nodig op de knop "Inhoud inschakelen" bij het bestaand document.
• Gebruik de sneltoets ALT F11 om de visual basic editor te openen.

We zien de vorige code staan dat zich bevindt in de module "mdlInvullen" omdat we van het bestand "Invullen.xlsm" zijn vertrokken. Bij een nieuw document is er nog geen enkele module noch code zichtbaar. De volgende stappen kunnen ook in een nieuw document worden toegepast. We brengen daartoe een nieuwe module aan.

• Klik op "Invoegen" in de menubalk.
• Klik op "Module".

In het venster met de eigenschappen verschijnt de standaardnaam "Module1" van deze module. We gaan dit wijzigen.

We wijzigen de standaardnaam "Module1".

• Klik in het venster "Eigenschappen" naast de vermelding "(Name)".
• Vervang door typen de standaardnaam "Module1" door "mdlInvullen2".

De volgende stap is enkel uit te voeren voor diegene die de vorige code zien voor het object Range. Wanneer je van een nieuw document start kan je de code onmiddellijk aanbrengen.

• Dubbelklik in het venster "Project" op het item "mdlInvullen2"

We brengen rechts de code aan.

• Typ "Sub Invullen2".
• Druk op de enter-toets.

Automatisch verschijnt "End Sub".

• Plaats de cursor boven de tekst "End Sub".
• Typ Cells(

De syntax van dit object verschijnt. Er zijn twee parameters nodig. Een rijindex gevolgd door een kolomindex. Deze parameters zijn cijfers. Dus cel A1 komt overeen met rijindex 1 en kolomindex 1. Dus met Cells(1,1).

We wensen iets weg te schrijven in cel A3.

• Typ 3,1).

Bij het object Range verschijnen de eigenschappen van het object. Bij Cells niet. We weten uit ervaring dat ook hier de eigenschap "Value" nodig is, om iets in de cel weg te schrijven.

• Typ value = "tweede methode"

Merk op dat de tekst wordt geplaatst tussen "dubbele aanhalingstekens".

We kunnen deze code testen.

• Klik bovenaan in de werkbalk op het pictogram met groene pijl.

De tekst "tweede methode" verschijnt in de cel "A3".

De tekst verschijnt in het geselecteerd werkblad. In een volgend punt gaan we bepalen in welk werkblad de tekst tevoorschijn komt.

Functie Richting

In een werkblad zijn een aantal waarden aangebracht voor verschillende maanden. We wensen de parameter richting voor de vergelijking van de trendlijn via een formule te berekenen. Bij het aanbrengen van de trendlijn werd de formule weergegeven. De vergelijking ziet er uit als y = mx + b. We bepalen de parameter m met behulp van de functie Richting.

• Selecteer cel H5.

• Klik op het tabblad "Formules" in het lint.

• Klik op het boekje "Meer Functies".
• Klik op het boekje "Statistisch".

• Klik op de functie "Richting".

Het dialoogvenster "Functieargumenten" verschijnt. Het vak "Y-bekend" is reeds geselecteerd.

We selecteren de waarden via een sneltoets.

• Klik in de cel "C2".

• Gebruik de sneltoetsen CTRL SHIFT Pijl-naar-beneden.

• Klik in het vak "X-bekend".
• Selecteer de cellen B2 tot B11.

• Klik op de knop "OK".

Geneste Functies - Als - Vert.Zoeken

In een excel-document zijn er de twee werkbladen "Werf" en "WerkUren". Op het werkblad "WerkUren" worden de prestaties op een werf bijgehouden. Elke werknemer krijgt eveneens een vergoeding naargelang werf, afstand en aard van passagier. Het bedrag van de vergoeding kan je achterhalen via het tweede werkblad "Werf". Zo kan je daar zien dat de vergoeding 0.25 bedraagt voor een werknemer die als bijzitter naar werf x rijdt met een afgelegde afstand van 50 km. Het is de bedoeling dat deze waarde automatisch verschijnt in het werkblad "WerkUren". We gebruiken hiervoor de functies Als en Vert.Zoeken.


Voor de functie Vert.Zoeken is een sleutelkolom nodig. De gegevens in deze kolom zijn uniek. We voegen een kolom toe aan de tabel die zich bevindt op het werkblad "Werf".

• Selecteer het werkblad "Werf".
• Klik op de kolomletter A.

De volledige kolom A is geselecteerd. We voegen een kolom toe met behulp van een sneltoets.

• Hou de CTRL-toets ingedrukt.
• Druk op toets "+" van het nummeriek klavier.

Vooraan verschijnt een nieuwe kolom. Bovenaan verschijnt een pictogram met een borsteltje als uitzicht. Hier kunnen we beslissen welke opmaak wordt gebruikt in deze kolom. We wensen dat de aangebrachte titel in de cel A1 dezelfde opmaak krijgt als de bestaande titels van de tabel.

• Klik op het pictogram.
• Stip het item "Opmaak hetzelfde als rechts" aan.

We brengen nu de tekst "Sleutel" aan als kolomtitel.

• Selecteer de cel A1.
• Typ "Sleutel" en bevestig dit met de toets "Enter".

Vervolgens komen in de eerste kolom unieke waarden. We gebruiken hiervoor bestaande waarden uit de kolommen "Werf" en "Afstand". De waarden uit de kolommen "Werf" en "Afstand" gaan we samenvoegen via een formule. Om teksten samen te voegen gebruiken we het teken ampersand "&" als operator.

• Selecteer de cel A2, indien dit nog niet is gebeurd.
• Typ "=".

• Klik in de cel "B2".
• Typ "&".
• Klik in de cel "C2".

Normaal gezien gebruiken we de toets "Enter" om de formule te bevestigen. Echter hierdoor selecteren we een cel onder de formule. En moeten we terugkeren naar de cel met de formule om deze te kopiëren naar beneden. Het is beter om direct in de cel met de formule te komen. Hiervoor gaan we ook de toets CTRL gebruiken.

• Hou de toets "CTRL" ingedrukt.
• Druk op de toets "Enter".

We kunnen nu via de vulgreep de formule naar beneden kopiëren.

• Ga met de muisaanwijzer naar het groen vierkantje rechtsonder de geselecteerde cel.

De muisaanwijzer verandert in een zwart plusteken.

• Dubbelklik op de linkermuisknop.

De formule wordt gekopieerd tot helemaal beneden.

We geven onze tabel een bereiksnaam. We selecteren de volledige tabel. Hiervoor is het nodig dat één willekeurige cel van de tabel is aangeduid. We kiezen de cel A1.

• Selecteer de cel "A1".

• Hou de toets CTRL ingedrukt.
• Druk op de toets "*" van het nummeriek klavier.

De tabel is geselecteerd. Nu kunnen we via het naamvak aan deze selectie een bereiksnaam geven. Het naamvak bevindt zich naast de formulebalk. Hierin staat nu de geselecteerde cel. In ons geval "A1".

• Klik in het naamvak.

• Typ "Vergoeding".
• Druk op de toets "Enter".

We kunnen nu in het werkblad met de prestaties de vergoeding laten verschijnen per rij. Hiervoor gebruiken we de formule "Vert.Zoeken".

• Selecteer het werkblad "WerkUren".
• Klik in de cel "E2".

• Klik op het tabblad "Formules" in het lint.
• Klik op het boekje "Zoeken en verwijzen".
• Klik op de functie "Vert.Zoeken".

Het dialoogvenster "Functieargumenten" verschijnt. Het vak "Zoekwaarde" is reeds geselecteerd.

De zoekwaarde is een samenstelling van de werf en de afstand per rij. De eerste werknemer "De Coen" is als chauffeur naar werf x gegaan. De afstand van deze rit bedroeg 50 km. Chauffeur komt overeen met de letter "C". Een bijzitter wordt aangeduid door de letter "B".

• Klik in de cel "B2".
• Druk op toets "&".
• Klik in de cel "C2".

De zoekwaarde is hier nu "x50".

In het vak "Tabelmatrix" komt de bereiksnaam voor de tabel welke staat op het werkblad "Werf".

• Klik in het vak "Tabelmatrix".

Via de functietoets F3 krijgen we een overzicht van alle bereiksnamen in dit document.

• Druk op de functietoets "F3".

Het dialoogvenster "Naam plakken" verschijnt.

• Selecteer de bereiksnaam "Vergoeding".
• Klik op de knop "OK".

De bereiksnaam is ingevuld in het vak "Tabelmatrix".
Vervolgens hebben we Kolomindex_getal. Hier komt een getal. De vergoeding van een chauffeur staan in de vierde kolom van de tabel "Vergoeding".

• Klik in het vak "Kolomindex_getal".
• Typ "4".

Tenslotte is er nog "Benaderen". De tekst vooraan dit vak is grijs. Dit betekent dat het niet altijd vereist is om dit vak in te vullen. Het al of niet invullen hangt af van verschillende factoren. Wanneer hier een fout zou worden ingetypt voor de werf en/of de afstand, dan wensen we een foutmelding te bekomen. Dus vullen we een "0" in.

• Klik in het vak "Benaderen".
• Typ "0".
• Klik op de knop "OK".

De waarde 0,5 wordt door de functie Vert.Zoeken opgehaald uit de tabel "Vergoeding".

Echter we kunnen deze formule nog niet kopiëren naar de andere rijen. Werknemer Janssens was een bijzitter. En de bedragen hiervoor bevinden zich in de vijfde kolom. De waarde voor het vak "Kolomindex_getal" voor de functie "Vert.Zoeken" wordt bepaald door de toegekende letter "B" of "C". Indien er een "C" wordt ingevuld dan is de waarde 4. Indien er een "B" wordt ingevuld dan is de waarde 5. Dit kunnen we bekomen via de functie Als. We testen dit eerst uit in een hulpcel.

• Klik in de cel "H1".
• Typ "Welke Kolom?".
• Druk op de toets "Enter".

• Klik op het tabblad "Formules" in het lint, indien dit nog niet is geselecteerd.
• Klik op het boekje "Logisch".
• Klik op de functie "Als".

Het dialoogvenster "Functieargumenten" verschijnt. Het vak "Logische test" is geselecteerd. Hier komt de controle. In ons geval testen we of er in de kolom "Chauffeur/Bijzitter" de letter "B" of "C" is ingevuld.

• Klik op de cel "D2".
• Typ ="C".

Als de werknemer een chauffeur is dan bevindt zich de vergoeding in kolom 4. Indien de letter C wordt ingevuld in cel D2, dan is de logische test D2 = "C" waar. Dus komt het getal 4 in het vak "Waarde-als-waar". In het andere geval hebben we het getal 5. Dus het getal 5 komt in het vak "Waarde-als-onwaar".

• Klik in het vak "Waarde-als-waar".
• Typ "4".
• Klik in het vak "Waarde-als-onwaar".
• Typ "5".

• Klik op de knop "OK".

In ons geval is werknemer "De Coen" een chauffeur. Dus er verschijnt 4 als resultaat voor de functie als.

De functie als gaan we nu plaatsen in de functie "Vert.Zoeken". De functie als wordt een geneste functie.

• Klik in de cel "E2".

• Klik op het knopje "fx", vooraan de formulebalk.

Het dialoogvenster "Functieargumenten" verschijnt. We zien terug alle ingevulde waarden voor alle vakken. We plaatsen in het vak "Kolomindex_getal" de functie "Als". Vooraan de formulebalk bevindt zich het naamvak. Hierin staat de laatst gebruikte functie. In ons geval is dit de functie "Als".

• Klik in het vak "Kolomindex_getal".
• Verwijder het getal 4.
• Klik vooraan op het naamvak, indien daar de tekst "Als" staat.

Indien er in het naamvak geen "Als" staat vermeld, dan klik je op het pijltje. En via de keuzelijst ga je op zoek naar de functie "als".

Het dialoogvenster "functieargumenten" voor de functie "Als" verschijnt. Op dezelfde manier als daarnet, via de hulpcel, kunnen we de verschillende vakken invullen.

• Klik op de cel "D2".
• Typ ="C".
• Klik in het vak "Waarde-als-waar".
• Typ "4".
• Klik in het vak "Waarde-als-onwaar".
• Typ "5".

• Klik op de knop "OK".

Nu kunnen we de formule naar de andere rijen kopiëren.

• Ga met de muisaanwijzer naar het groen vierkantje rechtsonder in de geselcteerde cel.
• Bij het zien van het zwart kruisje, ga je dubbelklikken met de linkermuisknop.

De vergoedingen voor de verschillende werknemers komen tevoorschijn.