KOLOM() in VERSCHUIVING() binnen INDEX() geeft fout

[RubenT86]

Dag,

Ik heb een vraag omtrent een Excel formule, gebruikt in Office 365. Ik heb de volgende matrix-formule staan op werkblad "Overzicht" in het bereik E3:E14:

code:

=INDEX(
  Cliënten!$B$5:$B$87;
  KLEINSTE(
    ALS(
      VERSCHUIVING(Cliënten!$K$5:$AD$87;    0;    2 * (KOLOM() - 2);   83;    1) = "BB";
      RIJ(Cliënten!$B$5:$B$87) - MIN(RIJ(Cliënten!$B$5:$B$87)) + 1;
      ""
    );
    RIJ()-2
  )
)

Deze formule moet van het werkblad "Cliënten" alle waarden in kolom B ophalen, waarvan in kolom K de waarde in de corresponderende rij "BB" is.

Dit resulteert in de foutmelding "Een waarde in de formule heeft een onjuist gegevenstype".
Maar... wanneer ik KOLOM() vervang door het getal 5 (het kolomnummer van E3:E14 waar de formule in staat) dan doet hij het wel.


Hopelijk is er iemand die me hier iets duidelijkheid over kan verschaffen. :-)

Met vriendelijke groet,
Ruben

[Voor 0% gewijzigd door RubenT86 op 03-03-2021 10:57. Reden: Code werd niet goed getoont]

[g0tanks]

Volgens mij gaat het mis omdat de uitkomst van ALS() in het negatieve geval "" is. Dat is geen getal en gaat niet samen met KLEINSTE(). Probeer eens "" te vervangen door 0?

[RubenT86]

g0tanks schreef op woensdag 3 maart 2021 @ 11:05:
Volgens mij gaat het mis omdat de uitkomst van ALS() in het negatieve geval "" is. Dat is geen getal en gaat niet samen met KLEINSTE(). Probeer eens "" te vervangen door 0?

Dat werkt ook niet, ik blijf dezelfde fout houden.
Het vreemde is, KOLOM() retourneert als het goed is 5 (want kolom E), maar dat werkt dus niet. Maar als ik handmatig op die plek 5 invul, werkt het wel.

[KabouterSuper]

Gooi je cel eens door de "Evaluate formula" die je bij formules kunt vinden. Zo kan je stap voor stap zien wat er gebeurt.

[RubenT86]

KabouterSuper schreef op woensdag 3 maart 2021 @ 13:23:
Gooi je cel eens door de "Evaluate formula" die je bij formules kunt vinden. Zo kan je stap voor stap zien wat er gebeurt.

Thnx, die kende ik nog niet. Werkt mooi die functie!
Ik heb hem geëvalueerd, en bij het stukje van KOLOM() doet hij precies wat je zou verwachten, hij retourneert dan 5. Alle andere stappen doorloopt hij ook prima, totdat bij de laatste stap aan wordt gekomen.
Hij evalueert hem tot

code:

INDEX(Cliënten!$B$5:$B$87; 24)

, en daarna zegt hij

code:

#WAARDE!

(wat dus de foutmelding is).

[KabouterSuper]

RubenT86 schreef op woensdag 3 maart 2021 @ 13:44:
[...]

Thnx, die kende ik nog niet. Werkt mooi die functie!

Hij evalueert hem tot

code:

INDEX(Cliënten!$B$5:$B$87; 24)

, en daarna zegt hij

code:

#WAARDE!

(wat dus de foutmelding is).

En in cel Clienten!B28 staat een waarde?

Edit: in je oorspronkelijke functie stop je drie argumenten in de buitenste INDEX, maar ik zie er nu maar twee. Klopt dat?
@g0tanks heeft helemaal gelijk.

[Voor 41% gewijzigd door KabouterSuper op 03-03-2021 14:10]

[g0tanks]

Mocht je er niet uitkomen dan zou je als workaround INDIRECT in plaats van INDEX kunnen proberen.

code:

=INDIRECT("'"&"Cliënten"&"'!"&"B"&(5+24))

24 vervangen met de code vanaf KLEINSTE().

KabouterSuper schreef op woensdag 3 maart 2021 @ 13:59:
Edit: in je oorspronkelijke functie stop je drie argumenten in de buitenste INDEX, maar ik zie er nu maar twee. Klopt dat?

In het origineel zie ik volgens mij ook twee argumenten. Het een na laatste haakje dicht hoort nog bij KLEINSTE().

[Voor 47% gewijzigd door g0tanks op 03-03-2021 14:05]

[Lustucru]

RubenT86 schreef op woensdag 3 maart 2021 @ 10:56:
[...]
Deze formule moet van het werkblad "Cliënten" alle waarden in kolom B ophalen, waarvan in kolom K de waarde in de corresponderende rij "BB" is.

Dit resulteert in de foutmelding "Een waarde in de formule heeft een onjuist gegevenstype".
Maar... wanneer ik KOLOM() vervang door het getal 5 (het kolomnummer van E3:E14 waar de formule in staat) dan doet hij het wel.

Ik kan me voorstellen dat in een matrixformule dat gegoochel met kolom() en rij([bereik]) niet meer lekker loopt. Wat je niet ziet, is dat hij een kleinste moet bepalen uit een matrix en een waarde.

Kun je de formule uitleggen, want hij lijkt onnodig gecompliceerd voor wat je zegt wat hij moet doen.

[RubenT86]

Lustucru schreef op woensdag 3 maart 2021 @ 15:55:
[...]

Ik kan me voorstellen dat in een matrixformule dat gegoochel met kolom() en rij([bereik]) niet meer lekker loopt. Wat je niet ziet, is dat hij een kleinste moet bepalen uit een matrix en een waarde.

Kun je de formule uitleggen, want hij lijkt onnodig gecompliceerd voor wat je zegt wat hij moet doen.

Nou, eerlijk is eerlijk, ik heb ooit delen van de formule van internet gehaald, dus het kan zijn dat het onnodig complex is. Wat hij moet doen is het volgende;

- In het werkblad 'Cliënten' staat een lijst met namen (kolom met bij elke naam een indeling in verschillende tijdsvakken (kolommen K-AD).
- In het werkblad 'Overzicht' moet een weekoverzicht gegenereerd worden, waarbij per tijdsvak direct te zien is wie er ingedeeld staan.

Als het me gelukt is heb ik twee screenshots meegestuurd, van beide werkbladen één. In de screenshot van werkblad 'Overzicht' heb ik in bereik C3:C14 de formule met KOLOM() gebruikt (wat drie retourneert), en bij bereik E3:E14 heb ik rechtstreeks het getal 5 gebruikt (van de 5e kolom).

Bereik C3:C14 moet in werkblad 'Cliënten' zoeken in kolom K naar alle rijen waarvan de waarde "BB" is, en daarbij de corresponderende naam (kolom teruggeven in de matrix.
Bereik E3:E14 moet vervolgens zoeken in kolom O en verder hetzelfde doen. Bereik G3:G14 in kolom S, etc.

[Lustucru]

Ik kan iig bevestigen dat de het gebruik van de kolomfunctie niet werkt in deze context. Wat wel werkt is verwijzing naar een cel met daarin de gewenste waarde voor de kolomverschuiving (die dan wel berekend mag worden met kolom()), maar direct in de matrixfunctie vindt Excel niet leuk.

Als alternatief zou je kunnen proberen of door ook op die plaats index te gebruiken (index(K5:AD100;0,Kolom(),1,1) je het werkend krijgt.

edit:
Ja dus. Dit werkt foutloos:

code:

=INDEX(A1:A8;KLEINSTE(ALS(INDEX(C1:E8;RIJ();KOLOM()-7)="BB";RIJ(C1:C8);999);RIJ()))

[Voor 16% gewijzigd door Lustucru op 03-03-2021 18:23. Reden: index voorbeeld toegevoegd]