Elkaar opvolgende cellen middels vertik/horizon-zoeken in één keer selecteren -> hoe?

[Lebber]

Ik ben normaal niet zo van het vragen om hulp, maar ik zit nu met iets ogenschijnlijks simpels, waar ik maar niet uit kom in Excel. Ik hoop dat jullie mij kunnen en willen helpen

Ik ben een modelletje aan het bouwen waarbij ik per product de gemiddelde prijs per periode wil bepalen. Dit aan de hand van een selectie van het product en de juiste weken, aan de hand van een drop-down menu's (middels gegevensvalidatie).

Het gaat hier bij altijd om één product en één of meerdere weken. Deze weken volgen elkaar altijd op (een periode).

Wat mij wel lukt is:
- de prijs van één week automatisch bepalen (combi van verticaal en horizontaal zoeken)
- handmatig het gemiddelde berekenen (combi van horizontaal zoeken en de "array" functie). Maar hier heb ik eigenlijk niets aan.

Wat mij niet lukt is middels de selectie van bijvoorbeeld wk 1 t/m wk 4 (eventueel met enkel de gegevens "1" en "4") alle gegevens van wk 1 t/m wk 4 te pakken te krijgen. Dat terwijl het volgens mij "makkelijk" moet kunnen. Maar ik kom er helaas niet op

Zie de bijgevoegde Excel sheet voor de aanpak. Ik hoop dat het duidelijk is!

 

[WHER]

In bijlage een aanpak met dynamisch genaamd bereik op basis van "Offset".
Let op: de inhoud van je validatielijst (wk1, wk2, etc..) is een tikkeltje veranderd.

 

[Cobbe]

Heb ook een voorstel.

 

[HSV]

Omdat het zo mooi gaat.

=GEMIDDELDE(VERSCHUIVING(INDIRECT("B"&VERGELIJKEN(C10;B1:B6;0));;VERGELIJKEN(C11;C2:G2;0);;VERGELIJKEN(C12;C2:G2;0)-VERGELIJKEN(C11;C2:G2;0)+1))

 

[Lebber]

Zo dan, die reacties kwamen snel!

Drie verschillende oplossingen welke allemaal prachtig werken. Maar zo te zien was het toch wat lastiger dan ik dacht?

Het erge is dat ik alle drie de oplossingen niet meteen snap Maar daar ga ik mij morgen in verdiepen, zodoende leer ik er nog wat van i.p.v. dat ik nu enkel klakkeloos de oplossingen overneem

@WHER
Jouw oplossing ziet er zó ingenieus simpel uit, dat ik er echt geen snars van snap Zou je er kort een kleine uitleg bij willen geven? Want ik kan de functie niet determineren. Dank alvast :thumb:

Iedereen bedankt trouwens, erg leuk en leerzaam dit!

 

[WHER]

In cel C19 zie je dus volgende formule:

=AVERAGE(Weken)

"Weken" is dus een "genaamd bereik", via ctrl+F3 kom je in de "Name Manager", daar zie alle genaamde bereiken die het workbook bevat (in dit geval 1).
Als je in de Name manager het bereik "Weken" selecteert, zie je onderaan de zogenaamde "Refers To" formule.

=OFFSET(Blad1!$B$3;MATCH(Blad1!$C$10;Blad1!$B$4:$B$6;0);MATCH(Blad1!$C$11;Blad1!$C$3:$G$3;0);1;Blad1!$C$12-Blad1!$C$11+1)

Indien je een nederlandstalige Excel versie hebt, "offset" = "verschuiving", "match" = "vergelijken".
De functie OFFSET heeft vijf argumenten:
1: het ankerpunt van waaruit we vertrekken >> Blad1!$B$3
2: het aantal rijen dat we naar beneden (of naar boven kan ook) gaan, een getal dat bepaald wordt door MATCH(Blad1!$C$10;Blad1!$B$4:$B$6;0)
3: het aantal kolommen dat we naar rechts gaan (of naar links), een getal dat bepaald wordt door MATCH(Blad1!$C$11;Blad1!$C$3:$G$3;0)
4: de hoogte (= aantal rijen) van het bereik, in dit geval gewoon het getal 1
5: de breedte (het aantal kolommen) van het bereik, een getal dat bepaald wordt door Blad1!$C$12-Blad1!$C$11+1)

 

[HSV]

Iets ingekort.

=GEMIDDELDE(VERSCHUIVING(B3;VERGELIJKEN(C10;B4:B6;0);VERGELIJKEN(C11;C2:G2;0);;VERGELIJKEN(C12;C2:G2;0)-VERGELIJKEN(C11;C2:G2;0)+1))