Tagarchief: Vulgreep

Doorvoeren 2



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.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd?


Doorvoeren en sorteren

De titel klinkt als een term uit de breiwereld, maar het heeft toch echt alles met Excel te maken.

Een paar weken geleden, tijdens een Excel-cursus die ik gaf, bleek weer eens hoe weinig mensen de handige optie ‘Doorvoeren’ van Excel ten volle kennen; laat staan gebruiken.
En bijna niemand weet dat je je eigen reeksen kunt maken, die op dezelfde manier gebruikt kunnen worden; en dat deze reeksen ook voor sortering gebruikt kunnen worden.

Doorvoeren

Laten we eerst even kijken wat doorvoeren eigenlijk is:
Doorvoereneen cel,  gevuld met een waarde (dat kan een getal, datum of tekst zijn) kun je snel kopiëren door de zogenaamde vulgreep rechtsonder in de cel met de muis naar beneden of naar rechts te verslepen.

Stel dat cel B1 het getal 1 bevat (download  het Voorbeeldbestand);  wanneer we dan de vulgreep ‘naar beneden trekken’ tot in rij 10 dan zal Excel alle tussenliggende cellen met een 1 vullen.
DoorvoerenMaar let op: rechtsonder komt een kleine button tevoorschijn: de Opties voor Automatisch doorvoeren.
Deze opties zijn context-gevoelig, dat wil zeggen dat het aantal opties en de mogelijkheden afhankelijk zijn van de inhoud van de gekopieerde cel.
DoorvoerenIn dit geval (we hebben het cijfer 1 gekopieerd) zal Excel ons 4 opties tonen wanneer we op de button klikken: Cellen kopiëren (in dit geval de standaardactie), Reeks doorvoeren, Alleen opmaak doorvoeren en Doorvoeren zonder opmaak.
De laatste 2 opties spreken voor zich. De tweede (Reeks doorvoeren) is het meest interessante: wanneer je daar nu op klikt zal Excel niet de 1 uit cel B1 kopiëren, maar de volgende rijen vullen met 2, 3 etcetera!

NB in iedere regel van de Opties voor Automatisch doorvoeren is een letter onderstreept; dat betekent dat je niet met de muis op zo’n optie hoeft te klikken, je kunt ook de betreffende onderstreepte letter op het toetsenbord intikken.

Laten we dat ook eens met een datum doen: in het Voorbeeldbestand is cel C1 gevuld met 1-1-2015.
DoorvoerenTrek de vulgreep van deze cel naar rij 10 en de cellen worden gevuld met de eerste 10 dagen van het jaar!
DoorvoerenKlik op de optie-button rechtsonder en we zien de diverse mogelijkheden. Blijkbaar gebruikt Excel bij een datum doorvoeren standaard de 2e optie (Reeks); dit in tegenstelling tot getallen, waar standaard de kopieeractie wordt uitgevoerd.
De eerste vier opties snappen we nu, de vijfde is exact gelijk aan de tweede, maar de zesde is om van te watertanden! En de zevende en de achtste zijn ook uiterst handig.
Probeer de mogelijkheden uit. Trek de reeks verder door dan 31 dagen en daarna ook voorbij februari: Excel weet precies hoeveel dagen er in een maand zitten.

Nog even terug naar de getallen: wanneer we de cellen D1 en D2 vullen met respectievelijk 1 en 2, we selecteren de TWEE cellen Doorvoerenen slepen de vulgreep naar beneden, dan kiest Excel wel als standaard de optie Reeks doorvoeren (en krijgen we dus de getallen 1 t/m 10); cellen kopiëren levert in dit geval allemaal blokjes van de getallen 1 en 2 op.

Stel we vullen de cellen E1 en E2 met de getallen 1 en 3; doorvoeren van deze 2 cellen levert een mooie reeks van 1, 3, 5, 7 etc.
LET OP:  met een reeks als 1, 4, 9 (de volgende zal dan toch wel 16 zijn?) kan Excel niet overweg.

Dit opent perspectieven: willen we een overzicht van alle maandagen in 2015 dan vullen we een cel met de eerste maandag (5-1-2015) en de cel daaronder met de tweede (12-1-2015), we selecteren deze twee cellen en slepen de vulgreep!

Teksten doorvoeren

Hiervoor hebben we gezien welke mogelijkheden Excel biedt voor het doorvoeren van getallen (voor Excel zijn datums gewoon getallen maar met een speciale opmaak).
Welke mogelijkheden zijn er voor teksten?

In het Voorbeeldbestand is cel F1 gevuld met de tekst een. Voer deze cel door en Excel vult alle cellen met dezelfde tekst. Klikken op de button Opties voor Automatisch doorvoeren laat zien dat er maar drie mogelijkheden zijn: Kopiëren (de standaardactie) en wel of niet de opmaak doorvoeren.

Zijn er twee cellen gevuld (G1 en G2 met een en twee), worden deze geselecteerd en doorgevoerd, dan ontstaat er een opeenvolging van deze twee tekstcellen.

Verrassing!?
DoorvoerenCel H1 is gevuld met het woord ma.
Wanneer we deze cel doorvoeren, ontstaat er de bekende reeks ma, di , wo, do, vr, za, zo!
Is de doorvoering groter dan 7 rijen of kolommen, dan wordt de reeks herhaald.

En ook hier zit onder de button Opties voor Automatisch doorvoeren de handige optie om alleen Weekdagen door te voeren.

Wat de mogelijkheden zijn, wanneer we een cel met het woord maandag, jan of januari doorvoeren, zal nu wel niet meer verbazen.

NB wanneer de eerste cel met een hoofdletter begint (of alles is in hoofdletters) dan zal Excel bij het doorvoeren dat automatisch overnemen.

Snel doorvoeren

Stel dat we van een grote reeks getallen het kwadraat zouden willen weten (en wie wil dat niet?) dan kunnen we diverse, benodigde handelingen snel uitvoeren (zie ook het tabblad SnelDoorv in het Voorbeeldbestand):

  1. in de cellen B1 en B2 zetten we de getallen 1 respectievelijk 2
  2. selecteer deze 2 cellen en voer met behulp van de vulgreep deze selectie naar beneden door voor het aantal getallen, waarvan je het kwadraat wilt weten
  3. in cel C1 zetten we de tekst in het kwadraat =
  4. dubbelklik op de vulgreep van cel C1 en de tekst wordt automatisch zo vaak naar beneden gekopieerd als er in kolom B getallen staan!
  5. in cel D1 komt de formule =B1^2
    Het caret-teken wordt in Excel gebruikt om een machtsverheffing aan te duiden (zoals Wikipedia zegt: Not to be confused with Carrot or Carat).
  6. dubbelklikken op de vulgreep van cel D1 en ook deze formule is tig-keer gekopieerd

LET OP: dubbelklikken heeft alleen effect als de kolom links of rechts van de door te voeren cel gevuld is.

Sorteren

De reeksen voor de dagen en maanden kunnen ook gebruikt worden om een logische sortering door te voeren.

DoorvoerenIn het Voorbeeldbestand heb ik ook een tabblad Sorteren opgenomen, waarin een overzicht staat van de omzet van een heel kleine witgoedhandel: per verkocht apparaat staat vermeld in welke maand voor welk bedrag dit is verkocht.

Om dit schema wat overzichtelijker te maken is het handig om te sorteren op de maand: selecteer een cel in de maand-kolom en sorteer in de volgorde A-Z (met behulp van de button Steekproef binnen de menuoptie Gegevens of door rechts te klikken en dan Sorteren te kiezen).
Het resultaat is waarschijnlijk niet wat je verwacht (of gehoopt) had: Excel zal standaard de gewone alfabetische volgorde aanhouden.

DoorvoerenMaar daar kunnen we wat aan doen:

  1. klik rechts op een cel in de maand-kolom
  2. kies Sorteren en dan de optie Aangepast sorteren
  3. klik in het vervolgscherm onder Volgorde op het keuzedriehoekje Doorvoeren
  4. klik op Aangepaste lijst en kies de lijst met maanden
  5. nog 2x op OK klikken en de nieuwe sortering is klaar!

De nieuw ‘geleerde’ volgorde blijft actief totdat deze op de voorgaande manier wordt aangepast.

Eigen sortering/reeks

We kunnen onze omzet ook op product sorteren: we krijgen dan de volgorde Koelkast, Magnetron, Strijkijzer.

Wanneer we (om een of andere reden) de keukenapparaten achteraan willen hebben, is dat standaard niet mogelijk, maar …..

  1. open het tabblad Sorteren van het Voorbeeldbestand
  2. klik rechts op een cel in de product-kolom
  3. kies Sorteren, Aangepast sorteren, keuzedriehoekje bij Volgorde en dan Aangepaste lijst
  4. Doorvoerenin het blok Gegevens in lijst zetten we de drie apparaten in de gewenste volgorde (gescheiden door Enter)
  5. klik op Toevoegen
  6. 2x OK en de nieuwe sortering is klaar!

NB deze aangepaste lijst zal door Excel voortaan ook gebruikt worden bij het Doorvoeren. Tik in een cel Strijkijzer in en voer de cel met behulp van de vulgreep door: de volgende cel wordt Magnetron, dan Koelkast, opnieuw Strijkijzer etc.

NB2 de aangepaste lijsten worden NIET in het betreffende werkblad vastgelegd, waarin ze zijn gemaakt, maar vormen een aanpassing op de Excel-omgeving. Ook in andere werkbladen kunnen de nieuwe reeksen dus meteen toegepast worden.

Dergelijke aangepaste lijsten zijn zeer bruikbaar: bij een bedrijf-eigen productindeling, bij waarderingscodes (denk aan de financiële waardering AAA, AA+, AA, AA-, A, BBB …) etcetera


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: