VERSCHUIVING

Waarom het werkt

Door op de kop te ankeren zijn het regelnummer en het aantal rijen dat je verschuift hetzelfde: regel 1 staat één rij onder de kop. VERSCHUIVING telt alleen; het kijkt nooit welke rekening in de rij staat, dus de opbouw van de lijst maakt het antwoord juist.

Meer om op te letten

• De cliënt stuurt een nieuwe versie met een totaalregel tussen de kop en rekening 1100. De rekeningen schuiven omlaag terwijl het ankerpunt op C1 blijft, dus elk regelnummer geeft nu de rekening ervoor. INDEX(C2:C7; E2) schuift zijn bereik mee met de gegevens.

Waarom het werkt

rijen bepaalt waar het venster begint en hoogte hoe lang het is, dus als je de laatste periode verschuift, schuift het hele venster mee zonder van grootte te veranderen. VERSCHUIVING geeft SOM een echt bereik, en daarom wordt de niet geboekte periode overgeslagen in plaats van dat het totaal breekt.

Meer om op te letten

• De niet geboekte periode telt mee als een van de vier, dus dit totaal beslaat drie perioden loon. Het verschil met het loonjournaal is dan een ontbrekende boeking en geen fout in de formule.

Varianten

=SOM(INDEX(B2:B9, E1 - 3):INDEX(B2:B9, E1))

=SOM(INDEX(B2:B9; E1 - 3):INDEX(B2:B9; E1))

Waarom het werkt

AANTAL maakt van 'hoe ver is de lijst gegroeid?' een getal, en VERSCHUIVING maakt van dat getal een positie. Omdat de saldi van boven naar beneden zonder gaten worden ingevuld, is het aantal getallen gelijk aan de rij van het laatste saldo, en op die aanname rust de hele formule.

Meer om op te letten

• Een saldo dat uit de export van de cliënt als tekst is geplakt, bijvoorbeeld 226900 met een spatie erachter, telt niet mee in AANTAL en niet in het gemiddelde. Het venster verschuift en het gemiddelde verandert zonder foutmelding.
• Met minder dan 3 saldi zakt rijen naar 0 of lager, en het venster bevat dan de kop of valt buiten het werkblad.

Varianten

=GEMIDDELDE(INDEX(B2:B9, AANTAL(B2:B9) - 2):INDEX(B2:B9, AANTAL(B2:B9)))

=GEMIDDELDE(INDEX(B2:B9; AANTAL(B2:B9) - 2):INDEX(B2:B9; AANTAL(B2:B9)))

=GEMIDDELDE(NEMEN(FILTER(B2:B9, B2:B9 <> ""), -3))

=GEMIDDELDE(NEMEN(FILTER(B2:B9; B2:B9 <> ""); -3))

Waarom het werkt

Door op A1, de hoek van het raster, te ankeren, zijn beide VERGELIJKEN-posities zonder correcties van +1 of -1 bruikbaar als verschuiving; alleen het begin van het venster heeft de + 1 nodig. MIN begrenst het venster tot de perioden die bestaan, een randgeval dat VERSCHUIVING anders omzet in #VERW! of, bij een venster dat één periode te lang is, in een stille som die de kolom met rekeningen meeneemt.

Gebruik deze opbouw niet in een groot model met veel van zulke cellen: VERSCHUIVING is vluchtig en herberekent na elke wijziging in de werkmap.

Het alternatief is SOM(INDEX(B2:F5; rekeningRij; perKolom - spanwijdte + 1):INDEX(B2:F5; rekeningRij; perKolom)), dat dezelfde 56200 geeft en alleen herberekent als de invoer verandert, ten koste van een langere formule.

Meer om op te letten

• Laat je de + 1 weg, dan schuift het venster een periode terug, naar januari tot en met maart: 18500 + 18500 + 18500 = 55500. Test met een venster van 1 periode, dat alleen de eindperiode moet teruggeven.
• Een venster van 0 perioden maakt spanwijdte 0, en VERSCHUIVING met breedte 0 geeft #VERW!. Beperk I3 met gegevensvalidatie als mensen het met de hand invullen.

Varianten

=SOM(VERSCHUIVING(A1, VERGELIJKEN(I1, A2:A5, 0), VERGELIJKEN(I2, B1:F1, 0) - I3 + 1, 1, I3))

=SOM(VERSCHUIVING(A1; VERGELIJKEN(I1; A2:A5; 0); VERGELIJKEN(I2; B1:F1; 0) - I3 + 1; 1; I3))

=LET(
  rekeningRij, VERGELIJKEN(I1, A2:A5, 0),
  perKolom, VERGELIJKEN(I2, B1:F1, 0),
  spanwijdte, MIN(I3, perKolom),
  SOM(INDEX(B2:F5, rekeningRij, perKolom - spanwijdte + 1):INDEX(B2:F5, rekeningRij, perKolom))
)

=LET(
  rekeningRij; VERGELIJKEN(I1; A2:A5; 0);
  perKolom; VERGELIJKEN(I2; B1:F1; 0);
  spanwijdte; MIN(I3; perKolom);
  SOM(INDEX(B2:F5; rekeningRij; perKolom - spanwijdte + 1):INDEX(B2:F5; rekeningRij; perKolom))
)