AANTALLEN.ALS functie op een variabele kolom toepassen

[Steen92]

Goedemiddag,

Ik ben al een tijdje aan het worstelen met een formule waar ik maar niet uit kom.
Ik ben benieuwd of jullie mij verder kunnen helpen.

Wij werken met 4 verschillende vakantie regio's, te weten noord, midden, zuid en geen.
Daarnaast wordt er binnen iedere regio onderscheid gemaakt waarbij er 52, 50, 48, 42 of 40 weken wordt gewerkt.

Nu heb ik voor iedere variant een aparte datumtabel aangemaakt, zodat voor iedere variant de werkbare dagen per week gepland kunnen worden (werkbare dagen worden aangeduid met een vinkje "ý", vrije dagen met een kruisje "ü")

De formule is als volgt:
=ALS.FOUT(ALS($B2="Geen52";D$2/(AANTALLEN.ALS(Datumtabel!$I$7:$I$373;"ü";Datumtabel!$F$7:$F$373;E$1)+AANTALLEN.ALS(Datumtabel!$I$7:$I$373;"ý";Datumtabel!$F$7:$F$373;E$1))*AANTALLEN.ALS(Datumtabel!$I$7:$I$373;"ü";Datumtabel!$F$7:$F$373;E$1);0);0)

In het bijgevoegde voorbeeld zie je hoe dat eruit ziet.

Nu kan ik de best wat als formules achter elkaar zetten waardoor ik aardig wat varianten naar voren krijg, maar na 12 varianten zit ik aan de maximale lengte van een formule, terwijl ik 17 varianten heb. Er moet een manier zijn die logischer is.

Concreet is de vraag dus, is er een formule waardoor ik de aantallen.als berekening kan baseren op een variabele kolom op basis van een voorwaarde (bijvoorbeeld als "Geen 52" dan kolom I; als "Noord 52" dan kolom K; als "Midden 52" dan kolom P etc...

Ik zou er enorm door geholpen zijn!

[the___butcher]

Je kunt de verwijzingen zoals Datumtabel!$I$7:$I$373 binnen een OFFSET-functie zetten (zo heet die tenminste in het Engels, in het Nederlands wellicht BEREIK.VERSCHUIVEN oid). Het aantal kolommen dat je de verwijzing moet verschuiven kun je dan bepalen met een INDEX - MATCH combinatie.

[F_J_K]

Iderdaad. Dan hoef je slechts 1 nesting te gebruiken. Kan ook zonder verschuiving met een hulptabel:
"Geen 52" I
"Noord 52" K
etc. En dan met verticaal zoeken de juiste kolom te pakken en met indirect() gebruiken: als in cel A1 een A staat levert =INDIRECT(A1&"2") de waarde van cel A2. Etc.

Anyway, ik verplaats je topic even van Windows Clients naar het subforum die gaat over clientsoftware die draait op bijv. Windows. Welkom!

[Steen92]

Dank voor je snelle bericht!

Ik ben niet bekend met de OFF-SET formule, met INDEX - VERGELIJKEN wel, zou je misschien iets concreter kunnen zijn met de opbouw van de formule?

Met vriendelijke groet,

[Steen92]

Op een ander forum antwoord gekregen op mijn vraag, mocht ik iemand er mee kunnen helpen:

Hallo Ramon,

Wat jij wil kan inderdaad en het houdt de formule redelijke simpel. Ik heb jouw voorbeeld op tabblad 'Voorbeeld' eronder nagebouwd in het geel. Ik ga uit van matrixberekeningen, vandaar dat ik SOMPRODUCT gebruik. De benodigde formule in cel E8 beslaat drie regels - complexer wordt ie niet - en luidt zonder ALS.FOUT voorwaarde:
CODE: SELECTEER ALLES

=$D8/(SOMPRODUCT((Datumtabel!$F$7:$F$373=Voorbeeld!E$7)*(INDIRECT("Datumtabel!"&INDIRECT("C8")&7):INDIRECT("Datumtabel!"&INDIRECT("C8")&373)="ü"))+SOMPRODUCT((Datumtabel!$F$7:$F$373=Voorbeeld!E$7)*(INDIRECT("Datumtabel!"&INDIRECT("C8")&7):INDIRECT("Datumtabel!"&INDIRECT("C8")&373)="ý")))*SOMPRODUCT((Datumtabel!$F$7:$F$373=Voorbeeld!E$7)*(INDIRECT("Datumtabel!"&INDIRECT("C8")&7):INDIRECT("Datumtabel!"&INDIRECT("C8")&373)="ü"))
De essentie is dat INDIRECT("Datumtabel!"&INDIRECT("C8")&7):INDIRECT("Datumtabel!"&INDIRECT("C8")&373)="ü" geheel flexibel een zoekkolom samenstelt uit tabblad 'Datumtabel' o.b.v. de kolom letter in C8 op dit tabblad 'Voorbeeld'.

Kortom INDIRECT("Datumtabel!"&INDIRECT("C8")&7):INDIRECT("Datumtabel!"&INDIRECT("C8")&373)="ü" betekent dus niet anders dan Datumtabel!I7:I373="ü" en als in C8 de letter Y zou staan wordt het geheel Datumtabel!Y7:Y373="ü"

Deze formule kun je vanuit E8 vervolgens naar rechts kopiëren (F8, G8...) en dat gaat goed. Echter, als je de formule naar beneden kopieert, bijvoorbeeld naar E9, dan moet je de tekstuele verwijzing naar "C8" (6 stuks) veranderen in "C9". Dit zijn namelijk teksten vanwege de quotes en geen echte celadressen. Daarna kan je het geheel weer naar rechts kopiëren (F9, G9, ...) . Ik heb dit 4x voor je gedaan zodat je ziet wat ik bedoel.

Natuurlijk moet in cel C9 op tab 'Voorbeeld' dan ook de juiste kolomletter staan. Let op: Hiervoor heb ik ook een formule gemaakt die de naam in kolom B gebruikt en de juiste kolom uit tab 'Datumtabel' opzoekt. Dan hoef je de kolomletter niet eens meer zelf te zoeken/typen. De juiste naam in kolom B, zoals 'Noord 50', is voldoende.

De ALS.FOUT voorwaarde zou je zelf nog kunnen toevoegen.

[the___butcher]

Ik ben niet zo'n fan van INDIRECT-functies, omdat daarmee de verwijzingen niet meer transparant zijn. Als je vervolgens ook nog eens formules handmatig moet aanpassen bij het omlaag kopiëren, haak ik helemaal af.

Ik heb even gegoogled (tip: echt handige website) en de Nederlandse vertaling van de OFFSET formule is VERSCHUIVING. Het stuk

code:

AANTALLEN.ALS(Datumtabel!$I$7:$I$373;"ü";Datumtabel!$F$7:$F$373;E$1)

uit jouw voorbeeld zou je kunnen vervangen door:

code:

AANTALLEN.ALS(VERSCHUIVING(Datumtabel!$I$7:$I$373,0,[kolomverschuiving]);"ü";Datumtabel!$F$7:$F$373;E$1)

, waarbij
[kolomverschuiving] aangeeft hoeveel kolommen je moet verschuiven t.o.v. de het referentiebereik (I7:I373). Dit kun je 'opzoeken' met een VERGELIJKEN functie. Het totaal wordt iets als

code:

AANTALLEN.ALS(VERSCHUIVING(Datumtabel!$I$7:$I$373,0,VERGELIJKEN(I$6,$I$6:$Z$6;0));"ü";Datumtabel!$F$7:$F$373;E$1)

Zonder beschikbare Excel-file als voorbeeld en met een Engelse Excel hier kan ik er niet meer van maken. Mocht je nog meer hulp nodig hebben, maak dan even een voorbeeld-Excelfile beschikbaar.