Tagarchief: Relatief

Doorvoeren 2

Excel, , , ,
Voorbeeldbestand: Doorvoeren2.xlsxDownloaden

In een eerder artikel (Doorvoeren en sorteren) hebben we het al eens gehad over de mogelijkheden om gegevens in Excel snel in te voeren/aan te vullen.

In dit artikel komen (nogmaals) wat tips langs maar gaan we ook wat meer complexe alternatieven behandelen.

Herhaling

Even in het kort een opsomming van de basis-items uit het vorige artikel:

  1. wat bedoelen we met doorvoeren: een cel, gevuld met een waarde of formule, kun je snel kopiëren door de zogenaamde vulgreep rechtsonder in de cel met de muis naar beneden of naar rechts te verslepen.
  2. na het gebruik van de vulgreep komt rechtsonder een kleine button tevoorschijn: de Opties voor Automatisch doorvoeren.
    LET OP de opties zijn contextgevoelig; dat wil zeggen dat het aantal opties en de mogelijkheden afhankelijk zijn van de inhoud van de gekopieerde cel.
  1. als links of rechts van een cel al een kolom is gevuld dan kun je ook met de muis dubbelklikken op de vulgreep van die cel: Excel kopieert dan automatisch de cel zo vaak naar beneden als er cellen in de kolom daarnaast gevuld zijn.
    LET OP met dubbelklikken kun je geen rijen automatisch vullen
  2. het effect van het doorvoeren van een cel hangt af van de inhoud van die cel; daar gaat de rest van onderstaand artikel over.
  3. voor meer informatie: zie het artikel Doorvoeren en sorteren.

NB1 meestal zul je de vulgreep naar rechts of naar beneden trekken. Maar naar links of naar boven kan ook met dezelfde soort resultaten.

NB2 hierboven is sprake van een vulgreep van één cel. Maar als je eerst een groep cellen selecteert, dan kun je ook de vulgreep van die groep gebruiken.

Doorvoeren van tekst

Laten we beginnen met het doorvoeren van een cel die een tekst bevat.
In het tabblad VulgreepTxt van het Voorbeeldbestand beginnen we met cel C3.

‘Pak met de muis’ de vulgreep van cel C3 en trek die naar beneden tot en met cel C14. Het resultaat: alle betreffende cellen van kolom C worden gevuld met de tekst Een.

Maar wanneer we nu de cellen D3:D4 selecteren en de vulgreep naar beneden trekken dan worden de betreffende cellen van kolom D afwisselend gevuld met de tekst Een en Twee.

Het resultaat van het op deze manier doorvoeren van de cellen E3:E5 zal dan niet meer verbazen.

NB omdat de cellen B3:B14 al gevuld zijn kun je ook iedere keer op de betreffende vulgreep dubbelklikken.

LET OP met behulp van deze methode vult Excel alle nieuwe cellen met ‘harde’ waardes. Wanneer de start-gegevens wijzigen moet de Doorvoer-actie dus opnieuw worden uitgevoerd!
Dit geldt voor alle methodes die in dit artikel behandeld worden (nou ja, bijna alle).

Het wordt wat interessanter wanneer we de tekst jan (cel F3) gaan doorvoeren: Excel vult de cellen daaronder (of daarnaast) met de daarop volgende maand-aanduidingen!

NB1 voor de uitleg hiervan en wat je daarmee kunt doen, zie Doorvoeren en sorteren.

NB2 begint de maandaanduiding met een hoofdletter dan beginnen de gekopieerde cellen ook met een hoofdletter.

Eindigt een tekst op een getal (zoals hiernaast waar we met productnummer 1 beginnen) en we gebruiken de vulgreep dan nummert Excel de serie automatisch door.

NB dit is eigenlijk een voorbeeld van het doorvoeren van een getal (zie hierna).

LET OP deze ’truc’ werkt alleen als het getal op het einde van de tekst staat. Maar begint de tekst met een getal EN een spatie dan werkt het weer wel!

Een speciale variant van het kopiëren van meerdere cellen ziet u hier. Een constant verloop van cellen met tekst met daartussen een lege regel.

LET OP vergeet bij de selectie niet om onderaan ook een lege cel mee te nemen.

Doorvoeren tekst met Opties

Zoals we in de Herhaling hiervoor al hebben gezien krijg je na het doorvoeren altijd nog een button met Opties.
Deze zijn contextgevoelig.

In het geval van het doorvoeren van teksten zijn de mogelijkheden beperkt:

Cellen kopiëren is de standaard-bewerking, het wel of niet doorvoeren van opmaak spreekt voor zich. De laatste optie zullen we hieronder toelichten.

NB de Opmaak doorvoeren geldt ook voor Voorwaardelijke opmaak. Dit is makkelijk als je achteraf een cel een voorwaardelijke opmaak hebt gegeven en die wilt doorvoeren naar de rest van de kolom of rij (zie kolom M van het tabblad VulgreepTxt).

Tekst Snel aanvullen

Wanneer we in het tabblad VulgreepTxt van het Voorbeeldbestand de kolommen L en M hebben doorgetrokken naar rij 14 en daarna de vulgreep gebruiken in kolom N dan krijgen we het resultaat zoals links weergegeven.

Kiezen we bij de Opties het onderdeel Snel aanvullen dat verandert het resultaat. Excel combineert de gegevens uit de kolommen L en M.

LET OP niet altijd ‘begrijpt’ Excel direct wat je wilt bereiken met Snel aanvullen. Vaak helpt het om nog enkele voorbeelden meer met de hand te vullen.

Doorvoeren van getallen

Wanneer we cellen met getallen gaan Doorvoeren dan wordt het wat interessanter ofwel we hebben meer mogelijkheden (zie het tabblad VulgreepNum van het Voorbeeldbestand).

Voeren we één cel door (cel C3) dan zal het resultaat standaard alleen maar kopieën van deze cel bevatten (dus de waarde 1).

Selecteren we eerst 2 cellen (met bijvoorbeeld de waardes 1 en 2) dan vult Excel de volgende cellen ook met oplopende waardes.

Hetzelfde resultaat (dus opvolgende getallen) kunnen we ook bereiken vanuit het doorvoeren van één cel, maar dan moet je bij Opties het onderdeel Reeks doorvoeren kiezen.

NB kies je niet 2 opeenvolgende getallen dan zal Excel de reeks aanvullen met ook telkens hetzelfde interval:
LET OP als je meer dan 2 cellen selecteert dan wordt het voor Excel steeds moelijker te ‘bedenken’ wat de volgende getallen zullen moeten zijn:

Wil je gewoon eenzelfde reeks getallen kopiëren kies dan na het doorvoeren de optie Cellen kopiëren.

Zoals we al eerder gezien hebben gedraagt een cel die een tekst bevat, eindigend op een getal, bij het doorvoeren net alsof de cel een getal bevat. Dit geldt ook als er meerdere cellen geselecteerd worden om door te voeren.

Doorvoeren van getallen 2

Tot nu toe hebben we de vulgreep gebruikt om de inhoud van cellen ‘door te voeren’. Voor numerieke cellen zijn er nog meer mogelijkheden, maar dan moeten we wel via de menu-structuur aan de slag.

Eerst een simpel voorbeeld (zie het tabblad DoorvoerenNum van het Voorbeeldbestand):

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Bewerken de optie Doorvoeren:
  3. kies de optie Reeks en er opent een pop-up.
  4. aangezien we de reeks naar beneden willen kopiëren kiezen we Kolommen .
  5. ook vullen we de Interval– en Eindwaarde in.
  6. klik op OK.

Wanneer het aantal te vullen cellen beperkt is in aantal, dan is de volgende methode handiger:

  1. in het tabblad DoorvoerenNum is cel D3 gevuld met de waarde 1
  2. selecteer de cellen D3:D12
  3. kies in de menutab Start in het blok Bewerken de optie Doorvoeren/Reeks
  4. Kolommen is automatisch geactiveerd
  5. kies een Intervalwaarde; de Eindwaarde hoeven we niet in te vullen omdat we al een bereik hebben geselecteerd

Maar het interval hoeft niet lineair te zijn; we kunnen ook voor Groei kiezen. Excel zal dan iedere cel met de Intervalwaarde vermenigvuldigen om de volgende celwaarde te bepalen (zie kolom H in het tabblad DoorvoerenNum).

Excel kan ook een trend gebruiken om cellen te vullen:

  1. de cellen F3:F5 in het tabblad DoorvoerenNum zijn gevuld met de waardes 1, 2 en 3
  2. selecteer de cellen F3:F12
  3. kies de optie Doorvoeren zoals hiervoor besproken
  4. vink Trend aan en klik OK

LET OP met de laatste optie (Trend) kunnen gegevens overschreven worden!
De cellen G3:G5 waren gevuld met de waardes 1, 3 en 4. Na het doorvoeren met een Trend-reeks worden deze 3 getallen gewijzigd:

NB1 deze methode is geschikt om snel bestaande gegevens om te zetten in een trendlijn: vul alle bekende waardes in, selecteer deze cellen (ze moeten in een rij of kolom staan) en kies Doorvoeren/Reeks/Trend. Maar let dus op: alle waardes worden overschreven, dus gebruik een kopie van de bekende gegevens.

NB2 door niet alleen cellen met bekende gegevens te selecteren maar daarnaast/ daaronder ook lege cellen kan de berekende trend ook naar de toekomst doorgetrokken worden.

Doorvoeren van datums

Omdat datums voor Excel niets anders zijn dan getallen (met een speciale opmaak) zou je verwachten dat het Doorvoeren van datums hetzelfde zou zijn, maar niets is minder waar; er komen diverse mogelijkheden bij!

In het tabblad VulgreepDtm van het Voorbeeldbestand is cel C3 naar beneden doorgevoerd. Waar bij een getal de kopieën allemaal dezelfde waarde krijgen zien we nu dat Excel de datum ophoogt.

NB Zoals al eerder aangegeven worden alle cellen gevuld met een ‘harde’ waarde. Wil je een Excel-systeem flexibeler maken dan kun je beter de methode uit kolom D gebruiken; daar is de formule in D4 naar beneden gekopieerd. Wijzig je de datum in D3 dan is automatisch de hele kolom aangepast.

In kolom E zijn de cellen E3:E4 doorgetrokken: ook de andere cellen krijgen dan datums, die 7 dagen uit elkaar liggen. In dit geval krijgen we dus een reeks maandagen.

In kolom F is cel F3 met de vulgreep naar beneden gekopieerd. Het resultaat is standaard een opeenvolging van dagen. Maar zoals hiernaast te zien is, kunnen we bij Opties ook kiezen dat Excel alleen werkdagen doorvoert.

NB kolom F bevat ‘gewone’ datums, maar wel met een speciale opmaak.

De overige opties in dat scherm spreken voor zichzelf:

Het doorvoeren van datums via de menu-optie Doorvoeren biedt vergelijkbare mogelijkheden.

Doorvoeren van formules

De effecten van het doorvoeren van cellen met formules zijn helemaal anders dan bij cellen met harde waardes.

Om dat te laten zien gaan we een overzicht maken van het resultaat van sparen: hoe ontwikkelt het spaarbedrag zich in de loop van de tijd wanneer we maandelijks een bedrag inleggen (zie het tabblad VulgreepForm van het Voorbeeldbestand:

  1. in de cellen C2:C4 staan de basis-gegevens
  2. cel B7 bevat de start-maand; eigenlijk een dag-aanduiding maar met een speciale opmaak
  3. selecteer cel B7 en trek de vulgreep naar beneden t/m cel B30
    NB mooi toch: Excel ‘ziet’ aan de opmaak dat we geïnteresseerd zijn in de maanden en hoogt de cellen in kolom B op met 1 maand in plaats van (standaard voor een datum) met een dag.
  1. cel C7 bevat een verwijzing naar het startkapitaal: =C4
  2. de rente in de eerste maand berekenen we als volgt: =C7*$C$2/12
    NB de betekenis van de $-tekens zien we hierna
  3. de inleg in de maand: =$C$3
  4. en op het einde van de eerste maand hebben we dan in kas: =SOM(C7:E7)
  5. de begin-stand van de 2e maand is gelijk aan de eindstand van de 1ste, dus cel C8 bevat de formule: =F7
  6. de rest van de berekeningen voor de 2e maand zijn gelijk aan die van de vorige maand: selecteer de cellen D7:F7, trek de vulgreep 1 rij naar beneden. De rente in de 2e maand heeft nu de formule: =C8*$C$2/12
  7. voor de 3e maand zijn de berekeningen nu gelijk aan die van de 2e: selecteer de cellen C8:F8 en dubbelklik op de vulgreep
  8. de belangrijkste gegevens staan in een apart overzichtje bij elkaar:

Het gebruik van de vulgreep bij cellen met formules werkt dus echt anders dan hiervoor. Het grootste gedeelte van de formule blijft hetzelfde, behalve dat verwijzingen naar cellen veranderen: wordt de formule naar beneden gekopieerd dan wijzigen de rij-aanduidingen. Hadden we de vulgreep naar rechts getrokken dan zouden de kolom-aanduidingen zijn veranderd.
Wanneer een kolom- en/of rij-aanduiding wordt voorafgegaan door een $-teken blijft deze aanduiding bij de kopieer-actie ongewijzigd. We noemen dit een absolute verwijzing in tegenstelling tot een relatieve verwijzing wanneer er geen $-teken wordt gebruikt.

Absoluut en relatief in Excel-tabellen

Excel, , ,
Voorbeeldbestand: TabelRef.xlsxDownload

Absolute en relatieve verwijzingen in Excel: dat blijft altijd even opletten!
Maar sinds we met zijn allen steeds meer gebruik maken van Excel-tabellen komt daar ineens een struikelblok(je) bij.

In dit artikel kijken we eerst in algemene zin naar de implementatie van tabel-verwijzingen (Gestructureerde verwijzingen of Structured references in Microsoft-taal); daarna komt een methode langs hoe we ook tabel-verwijzingen absoluut kunnen maken.

Voorbeeld-bestand

In het Voorbeeldbestand staat in het tabblad CelRef een overzicht van bedragen per kwartaal en regio.

Er loopt blijkbaar een afspraak dat, als het bedrag groter is dan 100, er dan een korting van 10% wordt gegeven.
De daarbij gehanteerde formule is:
=ALS(D3>100;10%;0)

Ofwel (geredeneerd vanuit cel E3) als de waarde in cel D3 groter is dan 100, dan komt in E3 de waarde 0,1 anders 0.

NB 10% is voor Excel slechts een opmaak van een numerieke waarde; intern wordt gerekend met 0,1.

De formule is F3 (het bedrag inclusief korting) kan dan zijn:
=D3*(1-E3)

Allebei de formules kunnen naar beneden gekopieerd worden; dat gaat het makkelijkst door dubbel te klikken op het vierkantje rechtsonder aan de rand van een cel (de vulgreep) als die cel is geselecteerd.

Aangezien alle cel-verwijzingen relatief zijn (er staan nergens $-tekens bij de cellen D3 en E3) zullen de formules zich automatisch aanpassen: D3 wordt D4 etc.

LET OP om kolom F niet te breed te maken is de tekst in cel F2 gescheiden door een harde return (Alt-Enter). Een kop nooit over 2 cellen verdelen; altijd een harde return gebruiken.

Willen we weten hoe de verdeling over de kwartalen en/of de regio’s is, dan ligt het maken van een draaitabel voor de hand.

LET OP een bug in Excel kan er bij het aanpassen van de opmaak van de data voor zorgen dat de kop die met een harde return is gescheiden, inhoudelijk wijzigt (alles vanaf de harde return wordt verwijderd); hier is dan een handmatige aanpassing nodig.

Tabel-verwijzingen

Wanneer we dezelfde gegevens in een Excel-tabel opnemen (zie het tabblad TablRef van het Voorbeeldbestand) dan gaat Excel bij het maken van de kortingsformules automatisch met gestructureerde verwijzingen werken in plaats van celverwijzingen:

  1. selecteer cel E3
  2. tik in =als(
  3. klik met de muis in cel D3; Excel plaatst automatisch de tekst [@Bedrag] in de formule
  4. maak de formule af en druk op Enter
  5. Excel zal automatisch de formule naar de overige cellen van dezelfde kolom kopiëren.

Gelukkig hoeven we de gestructureerde verwijzingen dus niet zelf te typen; de notatie daarvan is niet altijd even eenvoudig.
De vierkante haken om de diverse onderdelen van een formule maken voor Excel het verschil tussen gestructureerde en cel-verwijzingen.
Het ‘apestaartje’ (@) voor een kolomnaam betekent dat de waarde uit dezelfde rij uit de betreffende kolom wordt opgehaald.

LET OP in cel F3 staat de formule =[@Bedrag]*(1-[@[Korting%]]); aangezien het %-teken in Excel diverse betekenissen kan hebben zijn nog eens extra vierkante haken om de kolomnaam geplaatst.
Andere speciale tekens die eenzelfde behandeling nodig hebben:
tab, nieuwe regel, Enter-teken, komma (,), dubbele punt (:), punt (.), vierkante haak openen ([), vierkante haak sluiten (]), hekje (#), enkel aanhalingsteken (‘), dubbel aanhalingsteken (“), accolade openen ({), accolade sluiten (}), dollarteken ($), accent circonflexe (^), en-teken (&), sterretje (*), plusteken (+), gelijkteken (=), minteken (-), groter dan (>), kleiner dan (<) en het deelteken (/).

Totalen

Aan een tabel kunnen snel totalen toegevoegd worden:

  1. klik op een cel van de tabel
  2. kies in de menutab Hulpmiddelen voor tabellen de optie Ontwerpen
  3. en vink de optie Totaalrij aan
  4. in de nieuwe regel onderaan de tabel kan dan per kolom het soort totalen gekozen worden (Som, Aantal, Gemiddelde etc.)

Een draaitabel die gebaseerd is op een tabel met totalen, zal deze totalen buiten beschouwing laten (gelukkig!); zie tabblad TablRefTot van het Voorbeeldbestand.

Externe verwijzing naar een tabel

Wanneer u een bepaald overzicht wilt genereren en geen gebruik wilt/kunt maken van draaitabellen dan kunt u via externe verwijzingen gebruik maken van de tabel-gegevens:

  1. selecteer cel J21 in het tabblad TablRefTot
  2. tik in =som.als(
  3. wijs met de muis de bovenkant van de kolom Regio van de tabel daarnaast aan (de cursor wordt dan een pijltje naar beneden) en klik; Excel plaats automatisch de tekst tblKwReg2[Regio] in de formule
  4. tik in ;J$20; en voeg op dezelfde manier de kolom Bedrag toe

NB omdat het verwijzingen buiten de tabel zijn plaatst Excel automatisch de naam van de betreffende tabel voor de rechte haken.

Door de formule nu naar rechts te kopiëren krijgen we ook het totaal-bedrag van regio Zuid te zien. Tenminste: als we op de juiste manier kopiëren!
Selecteer cel J21, klik op Ctrl-C, selecteer cel K21 en klik Ctrl-V en alles gaat goed.
Maar wanneer de vulgreep rechtsonder cel J21 wordt gebruikt, gaat het mis! Dan wordt de verwijzing naar Regio ineens Bedrag en Bedrag wordt Korting%!
DUS: met kopiëren/plakken worden de externe verwijzingen als absoluut beschouwd, maar gebruiken we de vulgreep dan ziet Excel de verwijzingen als relatief!

Ditzelfde effect zien we ook als we verwijzingen naar de tabellen-koppen maken (niet de bovenkant van een kolom aanklikken, maar de cel zelf) en die kopiëren (zie cellen I34 tot en met K35 van het tabblad TablRefTot van het Voorbeeldbestand).

NB wanneer gegevens aan de tabel worden toegevoegd (selecteer cel F32 en druk op Tab; de totalen schuiven automatisch naar beneden) zullen alle overzichten automatisch de nieuwe gegevens meenemen (wel nog via rechtsklikken de draaitabel Vernieuwen).
Dit geldt ook voor formules die naar de totaalrijen verwijzen.

Absoluut en relatief in gestructureerde verwijzingen

In het tabblad TablRefTot2 van het Voorbeeldbestand is een resultaat-overzicht per regio en kwartaal opgenomen. Dit overzicht is zelf ook weer een tabel (met de naam tblResult). Om dit te realiseren wordt de functie SOMMEN.ALS gebruikt.

LET OP de syntax is echt anders dan van SOM.ALS.

De formule in cel J26 berekent de som van die bedragen uit de kolom Bedrag van de tabel tblKwReg2b, waarbij de Regio-kolom van die tabel de koptekst van de kolom Noord van tblResult bevat en waarbij de waarde in de Kwartaal-kolom van de tabel tblKwRegTot2b dezelfde is als die in de Kwartaal-kolom in dezelfde tabel en rij waar deze formule staat.

De formule in J26 kopiëren naar J27 levert geen problemen op welke methode ook gehanteerd wordt (kopiëren/plakken of de vulgreep).

Maar: in het voorbeeld is J26 naar K26 gekopieerd met behulp van de vulgreep; de verwijzingen worden dan als relatief beschouwd. Dat klopt voor de regio-kop in de resultaat-tabel (die moet Zuid worden) maar niet voor de verwijzingen naar de bron-tabel tblKwRegTot2.
Cel J27 is via kopiëren/plakken naar K27 gekopieerd. De verwijzingen blijven absoluut, maar dat klopt dan niet voor de regio-kop!

Oplossing: verwijzingen die absoluut moeten blijven moeten dubbel in de formule worden opgenomen, gescheiden door een dubbele punt (:).
De formule die hier nodig is wordt dan (zie cel J31):
=SOMMEN.ALS(tblKwReg2b[[Bedrag]:[Bedrag]];
tblKwReg2b[[Regio]:[Regio]]; tblResult2[[#Kopteksten];[Noord]];
tblKwReg2b[[Kwartaal]:[Kwartaal]]; tblResult2[@[Kwartaal]:[Kwartaal]])

Wanneer deze formule via de vulgreep naar rechts en naar beneden wordt gekopieerd, krijgen we in alle cellen de juiste resultaten!

NB1 alle kolomnamen hebben vierkante haken, maar de dubbelen krijgen daar omheen nog een extra set rechte haken. Let ook op de notatie bij de @.

NB2 ook de laatste parameter (Kwartaal) moet in dit geval een tabel-aanduiding krijgen, ook al is de verwijzing binnen de tabel zelf.