Combinatie van verticaal zoeken en optellen van meerdere rijen en kolommen.

[EvelienDB]

Hallo,

Bij ons op de sportclub ga ik wat zaken overnemen van iemand die er mee stopt. Die persoon deed echter alles manueel en gebruikte geen formules om gegevens in te laden en zaken te berekenen. Ik ga dit echter combineren met een andere functie waarvoor ik ook met excelbestanden ga werken en wil tijd besparen door wat zaken te automatiseren met formules. Momenteel ben ik dus bezig met het bestaande excelbestand uit te breiden en hier en daar wat formules toe te voegen.

Ik heb helaas niet al te veel ervaring met de wat complexere formules in excel en bijt me al vele uren de tanden stuk op het volgende probleem wat eigenlijk niet eens zo moeilijk is volgens mij voor de mensen met meer kennis.

In bijlage is het excel bestand bijgevoegd.

De vraag gaat over de tabbladen 'MVERD DAMES' en 'Ploegen Dames'.

Situatieschets:

In het tabblad 'Ploegen Dames' staan in de rijen (2 t.e.m. 40 maar dit is variabel) meerdere ploegen opgesomd met hun spelers. Spelers kunnen in meerdere ploegen spelen, zij komen soms dus vaker voor. In de kolommen (D t.e.m. Q) staan de verschillende speeldata opgesomd van de ontmoetingen. Voor elke ontmoeting kunnen ze punten verdienen, sommige ontmoetingen doen ze niet mee, deze cellen zijn leeg.

In het tabblad 'MVERD DAMES' staan alle dames die kunnen meedoen maar ook hier kunnen nog records bijkomen. Deze namen worden geladen vanuit tabblad 'Dames'.


Probleemstelling:

In kolom C van tabblad 'MVERD DAMES' zou de som van de punten die bij die naam horen uit tabblad 'Ploegen Dames' moeten komen. Het wil me echter niet lukken om een juiste formule werkende te krijgen.

In cellen C8, C11 en C18 heb ik een aantal formules gemaakt die geen foutmelding gaven maar geen van allen is goed.

C8:

{=SOM(VERT.ZOEKEN($B8;'Ploegen Dames'!$A2:$Q40;{4;5;6;7;8;9;10;11;12;13;14;15;16;17};0))}

Nu wordt enkel de eerste rij geteld met die naam maar wel alle kolommen.

C11:

=SOM.ALS('Ploegen Dames'!$A2:$A40;$B11;'Ploegen Dames'!$D2:$Q40)

Nu wordt enkel de eerste kolom (D) geteld maar wel alle overeenkomende rijen.

C18:

=SOM(SOM.ALS('Ploegen Dames'!$A2:$A40;$B18;'Ploegen Dames'!$D2:$D40)+SOM.ALS('Ploegen Dames'!$A2:$A40;$B18;'Ploegen Dames'!$E2:$E40))

Deze formule werkt wel zo te zien maar is niet geschikt wegens veel te lang. In bovenstaande code heb ik enkel kolom D en E gezet maar aangezien er 14 kolommen opgeteld moet worden zou die dus nog 7 keer zo lang worden. Niet wat het moet zijn dus.


Kan er iemand mij helpen met een geschikte formule a.u.b.? Ik wil wel niet met tussensommen werken. Nadien ga ik hetzelfde doen voor de heren en de mix.

Bijvraag:

Kolom D zou ik eigenlijk ook willen automatiseren met een formule maar dat lijkt me nog vrij complex. De gegevens die daar moeten komen te staan zijn het aantal ontmoetingen en het aantal wedstrijden (zie bv bij tabblad 'MVERD HEREN'). Dit kan ook opgesplitst worden in 2 aparte kolommen. Het aantal ontmoetingen komt overeen met het aantal cellen waar punten ingevuld zijn bij het tabblad 'Ploegen Dames'. Bij elke ontmoeting kan een speler echter 1,2 of 3 wedstrijden spelen. Dat gegeven zou ik dan nog moeten toevoegen bij het tabblad 'Ploegen Dames', ik weet alleen nog niet hoe. Bij elke ontmoeting een extra kolom toevoegen voor het aantal wedstrijden dat ze gespeeld hebben?

Per wedstrijd dat iemand speelt kan je 0,1,2 of 3 punten verdienen, 3 keer 3 punten is 9 punten wat dus het maximum is. Een 0 staat echter niet gelijk aan een lege cel(niet meegedaan). Dit ter verduidelijking. De bedoeling is om uiteindelijk met deze gegevens ook de nodige statistieken te kunnen maken op het einde van het seizoen, bv meest efficiënte speler (aantal punten per gespeelde wedstrijd).

Is er iemand die dit in een formule denkt te kunnen gieten?

(Kolom E kan ik zelf nog doen met een formule)

Alvast heel erg bedankt voor de hulp.

Evelien

 

[Cobbe]

Voor de punten kan deze dienen:

=SOMPRODUCT(('Ploegen Dames'!$A$2:$A$40=$B6)*('Ploegen Dames'!$D$2:$P$40))

 

[EvelienDB]

Hartelijk bedankt Cobbe!

Zelf was ik ook al even bezig geweest met de functie SOMPRODUCT maar kreeg niet de juiste oplossing.
Stom dat ik daar zo lang mee heb zitten sukkelen.

Ik ben benieuwd of er voor de tweede vraag ook een "simpele" oplossing bestaat.

edit: Bovenstaande oplossing zal wel niet compatibel zijn als ik voor de tweede vraag over kolom D telkens een extra kolom voor de wedstrijden tussenvoeg bij de ontmoetingen. Maar misschien heeft iemand daar een betere oplossing voor waardoor de formule van Cobbe wel blijft werken voor kolom C.

 

[Cobbe]

Ik weet niet of de uitkomst deze is die je voor ogen hebt.

 

[EvelienDB]

Dat zou een oplossing kunnen zijn voor het eerste deel (aantal ontmoetingen) Cobbe. Alleen geef ik wel de voorkeur aan 1 formule indien mogelijk zonder die tussen berekeningen. Maar als dat niet mogelijk is kan ik eventueel wel die kolom verbergen.

Er zou echter ook nog het aantal wedstrijden per ontmoeting moeten bijkomen. Die gegevens staan momenteel nog niet in het excel bestand dus. Hiervoor moet ik denk ik een extra kolom bij elke ontmoeting toevoegen waar dan 1,2 of 3 in komt te staan. Dit moet dan ook opgeteld worden. Een voorbeeldje:

Ontmoeting 1: persoon x 5 punten uit 3 wedstrijden, persoon y 6 punten uit 2 wedstrijden, persoon z 0 punten uit 1 wedstrijd


De vorige persoon deed dit allemaal handmatig door dit online waar de gegevens vandaan gehaald worden, zelf op te tellen.

Maar bedankt voor de moeite die je al gedaan hebt!

Edit: Ik zal een aangepaste versie maken en uploaden dan wordt het duidelijker.
Edit2: In bijlage een aangepaste versie qua lay-out. Dit was de enige mogelijke oplossing die ik momenteel in gedachten had maar dan moeten dus die formules van uw eerdere deeloplossingen aangepast worden. Aangezien slechts de helft van de kolommen geteld moeten worden. Is het zo duidelijk wat ik bedoel?

 

[Cobbe]

Snap niets van de bedoelingen.
Vul eens handmatig aan wat waar wanneer moet komen te staan.

 

[EvelienDB]

Cobbe, hierbij een nieuwe versie. Ik heb even een titelbalk gemaakt in het tabblad 'Ploegen Dames' en ik heb voor Elke en Kris de waardes voor de 3 kolommen manueel berekend in het tabblad 'MVER DAMES'.

Nog even ter verduidelijking:

- Per ontmoeting tussen twee ploegen worden er 8 wedstrijden gespeeld (4 dubbelwedstrijden en 4 enkelwedstrijden).
- Een speler kan maximaal 3 wedstrijden spelen (2 dubbels en 1 enkel).
- Een team moet met minstens 4 spelers spelen.
- De som van het aantal wedstrijden die de spelers per ontmoeting spelen is altijd 12. (Aangezien je een dubbel met 2 speelt is het 12 en geen 8 in totaal)
- Per wedstrijd krijg je 0,1,2 of 3 punten, als je 3 wedstrijden speelt en telkens 3 punten behaalt, heb je het maximum van 9 punten behaald voor die ontmoeting. 7 punten in 2 wedstrijden halen kan dus niet bv., maximum zou dan 6 punten zijn.

 

[Cobbe]

Ik denk dat je nood hebt aan een andere (betere) hulplijn.

Sorry ik zie het niet.

 

[EvelienDB]

Ik zal nog even verder verduidelijken:

kolom punten: som van de kolommen D, F, H, J, L, N, P, R, T, V, X, Z, AB en AD uit tabblad 'Ploegen Dames'
kolom ontmoetingen: som van het aantal kolommen uit bovengenoemde reeks kolommen waar een waarde ingevuld is
kolom wedstrijden: som van de kolommen E, G, I, K, M, O, Q, S, U, W, Y, AA, AC, AE uit tabblad 'Ploegen Dames'

Bedankt voor de moeite die je er al ingestoken hebt.

 

[Cobbe]

Kijk eens of de resultaten kloppen in kolommen AF:AH van tab Ploegen Dames.

 

[EvelienDB]

Voor wedstrijden klopt het, voor de ontmoetingen niet omdat nu elke kolom geteld wordt en voor elke ontmoeting zijn er 2 kolommen. Dit kan eventueel opgelost worden door /2 te doen. Maar wellicht valt dit ook met een mooiere formule te doen.

edit:Voor punten klopt het ook trouwens

 

[Cobbe]

Voor de ontmoetingen:

=AANTALLEN.ALS($D$1:$AE$1;"WED";$D3:$AE3;">0")

 

[EvelienDB]

Klopt :thumb:

Denk je dat het doenbaar is om dit met 1 geneste functie in tabblad "MVERD DAMES" te laden in die 3 kolommen? Met dus de rijen die bij 1 persoon horen opgeteld.

Of zal ik deze hulpkolommen moeten gebruiken als tussenresultaat en dan met een functie zoals:

cel C11:

=SOM.ALS('Ploegen Dames'!$A2:$A40;$B11;'Ploegen Dames'!$AF2:$AF40)

Edit: Heel erg bedankt! Ziet er prima uit!:d

 

[Cobbe]

Zie mijn laatste post, heb de hulpkolommen AF:AH verborgen, kan toch geen probleem zijn.

 

[EvelienDB]

Ja, je had uw post tegelijkertijd geëdit.

Hartelijk bedankt Cobbe!:thumb: