Bekijk bijlage voorbeeld 2 Excel omzet MB.xlsx
Hoi Rudy,
Jouw nieuwe bestand lijkt verdacht veel op de eerste...
Geen probleem: ik heb zelf het aantal kolommen uitgebreid.
De formule in A12 (en gekopieerd naar beneden), is nu:
Code:
=INDEX($A$2:$A$7;1+SOM($B$11:$B11))
Dit geeft de naam uit $A$2:$A$7 op rij 1+SOM($B$11:$B11).
In A12 is dat rij 1 + de som van B11. B11 is tekst ("Aantal") en SOM beschouwt tekst als nul. Dus 1+0 = 1.
In A13 is dat rij 1 + de som van B11 t/m B12 = 0+2. Dus 1+2 = 3.
In A14 is dat rij 1 + de som van B11 t/m B13 = 0+2+1. Dus 1+3 = 4.
In B12 t/m B14 krijg je de aantallen rijen per persoon in de originele matrix.
De formule in C12 (en gekopieerd naar rechts en naar beneden t/m AF14) is nu:
Code:
=ALS(AFRONDEN.BOVEN(KOLOMMEN($C12:C12)/10;1)>$B12;"";INDEX($C$2:$L$7;INTEGER((KOLOMMEN($C12:C12)-1)/10)+VERGELIJKEN($A12;$A$2:$A$7;0);1+REST(KOLOMMEN($C12:C12)+9;10)))
Toelichting per stukje:
AFRONDEN.BOVEN(KOLOMMEN($C12:C12)/10;1)>$B12
Als een persoon 1x voorkomt, dan moeten 10 kolommen gevuld worden, bij 2x 20 kolommen en bij 3x 30 kolommen.
In dit deel van de formule wordt gecontroleerd of er nog gegevens zijn voor deze persoon, door het aantal kolommen vanaf kolom C te delen door 10, afgerond naar boven (dus je krijgt van links naar rechts gezien 10x een 1, 10x een 2, 10x een 3).
Als dat aantal groter is dan het aantal in kolom 2, dan wordt de cel gevuld met "".
Het deel van de formule vanaf INDEX zoekt de juiste gegevens op uit de matrix met gegevens, structuur INDEX(matrix;rij;kolom)
Matrix: $C$2:$L$7
Dit is het bereik van de originele gegevensmatrix.
Rij: INTEGER((KOLOMMEN($C12:C12)-1)/10)+VERGELIJKEN($A12;$A$2:$A$7;0)
Dit levert van links tot rechts op 10x0, 10x1, 10x2 plus het regelnummer waarin de naam voorkomt in de oorspronkelijke matrix.
Kolom: 1+REST(KOLOMMEN($C12:C12)+9;10))
Dit levert van links naar rechts gezien telkens de getallen 1 t/m 10 op: 1+de restwaarde als je het relatieve kolomnummer (vanaf kolom C) + 9 deelt door 10:
1+rest((1+9)/10) = 1+rest(10/10) = 1+0 = 1. 1+rest(11/10) = 1+1 = 2 enzovoorts.
Hopelijk kun je met deze toelichting een en ander aanpassen naar het echte bestand.
