Samenvoegen formules

De kosten uit een bepaald jaar verhogen met een percentage. Hoe doe je dat?

Maak gebruik van het oefenbestand samenvoegen (nesten) formules in Excel voor de stappen die je moet doorlopen.

Laten we beginnen met het slot. Het eindresultaat moet de volgende formule zijn: =ALS(JAAR($C2)=D$1;$B2*$P$2+$B2;$B2)

We stellen deze formule stap voor stap op. Voor elke toevoeging in de formule is in het oefenbestand een apart tabblad gemaakt.

Eerst bepalen we of de datum uit kolom C past in het jaartal dat is benoemd in D1, E1 of F1. Zet daarom de formule =JAAR(C2) in cel D2

  1. Om erachter te komen of deze formule gelijk is aan de waarde in cel D1, maken we een vergelijking: =JAAR(C2)=D1
  2. De formule geeft als resultaat WAAR of ONWAAR.
  3. Deze formule willen we ook in de overige lege cellen hebben staan. Om de formule gemakkelijk te kopiëren, zetten we de celverwijzingen vast met behulp van dollartekens. Een dollarteken voor de kolom betekent dat de kolom vast blijft staan. Een dollarteken voor de rij betekent dat de rij vast blijft staan.
  4. In ons voorbeeld willen we kolom C vastzetten bij C2 en rij 1 bij D1. Waar we dan ook naar toe kopiëren, er zal altijd worden verwezen naar kolom C en rij 1. De formule wordt dus: =JAAR($C2)=D$1
  5. Op het moment dat aan deze voorwaarde wordt voldaan, moet er een berekening worden uitgevoerd. De berekening is het vermenigvuldigen van de waarde uit kolom B met het percentage uit H2 en het weer optellen van de waarde uit kolom B. De berekening zetten we voor nu even in cel G2.
  6. De berekening wordt als volgt: =B2*P2+B2
  7. Ook bij deze berekening moeten we cellen vastzetten zodat bij het kopiëren nog steeds naar de juiste waarde wordt verwezen. Er moet altijd naar kolom B verwezen worden. En – hoe u ook kopieert – er moet ook altijd naar O2 worden verwezen.
  8. Uiteindelijk wordt de formule in G2 dan als volgt: =$B2*$P$2+$B2
  9. De formule kan vervolgens gekopieerd worden naar uiteindelijk I18.
  10. Op het moment dat er niet aan de voorwaarde van het jaar wordt voldaan, moet gewoon de waarde uit B2 worden getoond. Ook deze zal echter moeten worden vastgezet, aangezien we altijd naar kolom B willen verwijzen.
  11. In J2 komt dus te staan: =$B2
  12. De verwijzing kan vervolgens worden gekopieerd naar uiteindelijk L18.

Nu hebben we alle berekeningen. Maar we willen op basis van de voorwaarde slechts één van de twee opties tonen. Met andere woorden: =$B2*$P$2+$B2 òf =$B2.

Daarvoor gebruiken we de Als-formule. De Als-formule toont op basis van een logische test een resultaat als deze WAAR is en een andere waarde als de test ONWAAR is.

  1. In cel M2 zetten we dan: =ALS(D2=WAAR;G2;J2)
  2. De formule kan vervolgens gekopieerd worden naar uiteindelijk O18.

Nu hebben we in totaal vier formules of celverwijzingen gemaakt. Deze willen we vervolgens terugbrengen naar één formule.

  1. Hiertoe kopiëren we op de plaatsen van de celverwijzingen in de Als-formule de formules zonder het = teken.
  2. Eerst de formule uit cel D2: =ALS(JAAR($C2)=D$1=WAAR;G2;J2)
  3. Vervolgens kunnen de formules uit D2 tot en met F18 worden verwijderd.
  4. De volgende stap is de formule uit cel G2: =ALS(JAAR($C2)=D$1=WAAR;$B2*$P$2+$B2;J2)
  5. Dan kunnen de formules uit G2 tot en met I18 worden verwijderd.
  6. De volgende stap is de celverwijzing uit cel J2:
  7. =ALS(JAAR($C2)=D$1=WAAR;$B2*$P$2+$B2;$B2)
  8. Nu zijn alle formules en celverwijzingen gekopieerd en kunnen de celverwijzingen uit J2 tot en met L18 ook worden verwijderd.
  9. Kopieer de formule naar cel D2 tot en met F18. Dat doet u door de formule uit M2 (zonder het = teken) naar cel D2 te kopiëren.
  10. Plaats er vervolgens het = teken voor.
  11. Daarna wordt het mogelijk om de formule ook naar de andere lege cellen te plakken en is het nesten afgerond.

Kom je er niet helemaal uit? Geen probleem. Neem gewoon even contact op.