Excel: tellen met meerdere opties per item

[EsthervdB]

Hallo,

Ik heb de volgende vraag: ik heb in Excel 365 een set gegevens van cursisten die één of meerdere cursussen hebben gevolgd. Ik zoek een overzicht van de aantallen per combinatie van cursussen (evt met gebruikmaking van hulpkolommen als dat nodig is). In de bijlage staat een voorbeeld van de gegevensset, het tussenresultaat (wat ik niet per se nodig heb) en het gewenste eindresultaat.

Ik heb al heel veel soorten formules uitgeprobeerd, onder andere met aantallen.als, sommen.als, somproduct en hulpkolommen maar kom er niet uit, ik zie het niet. Ook met draaitabellen kom ik er niet. Heeft iemand een idee in welke richting ik het moet zoeken, ik vind het niet erg om het zelf uit te moeten snorren, normaal lukt dat namelijk wel maar nu even niet .



Al heb ik maar een hint in welke hoek van excel ik verder moet, dan ben ik al geholpen! Ik maak altijd dankbaar gebruik van dit forum om te zoeken naar tips, heb er nog niet eerder iets geplaatst, dus als iemand meer informatie nodig heeft dan hoor ik het graag. Alvast bedankt!

[Voor 30% gewijzigd door EsthervdB op 11-02-2021 08:29]

[Patrick_6369]

Post even een screenshot van hoe je data er uit ziet - ik zie geen bijlage en ben er veiligheidshalve ook niet zo kapot van die zomaar van een forum te downloaden.

Ik zou zeggen op basis van je probleemomschrijving dat AANTALLEN.ALS het zou moeten doen. Welke formule had je zelf al bedacht daarmee?

[EsthervdB]

Hallo Patrick,
Ik had het bericht per ongeluk iets te vroeg gepost, ik heb nu een voorbeeld als afbeelding erbij gezet. Ik heb zoveel geprobeerd met aantallen.als (en steeds weer verwijderd...), dus dat zou ik opnieuw moeten zoeken, maar ik kwam er niet bij in de buurt van wat ik zoek.

[g0tanks]

Je kan een draaitabel maken op je gegevensset zodat je per persoon makkelijk kunt zien welke cursussen degene heeft afgerond. Met wat ALS-formules kun je dan bepalen welke combinatie het is. Hieronder een voorzet waarbij ik per persoon kijk of degene aan de combinatie Vooropleiding + Niveau 1 voldoet. De kolom kun je vervolgens sommeren zoals ik heb gedaan in H9.

[webfreakz.nl]

Ik probeer het te reproduceren, mij lukte het met extra tussenstap als ik een nieuwe tabel maak (handmatig of met pivottable) met de kolommen "naam + vooropleiding + niveau1 + niveau2" en daarin Y / N te plaatsen. Daarna kan je daar wel een goede PivotTable van maken. Maar drie dingen (originele tabel, tussentabel, uiteindelijke tabel) lukte mij niet dynamisch.

Dus of je databron is niet goed, of je moet een tussenstap zien te maken maar het lukte mij niet met pivottable van een pivottable. Dat is waar ze hier bij https://www.excelcampus.c...-pivot-table-source-data/ solution #1 wel naartoe werken. Zij gebruiken daar de "Unpivot Colomns", denk dat jij de "Pivot Columns" nodig hebt (zie screenshot filmpje). En je hebt Power Query nodig, is zo te zien een gratis tooltje van Microsoft.

[EsthervdB]

Hoi!
Ik ga het uitproberen, de oplossing van @g0tanks begrijp ik, ik maak idd ook al gebruik van die draaitabel maar kom dan vervolgens handmatig via sorteringen en tellen tot mijn uiteindelijke telling, maar dit werkt waarschijnlijk sneller.
De oplossing van @webfreakz.nl ga ik uitzoeken, ik zou het liefst een volledige oplossing hebben zonder dat ik nog handmatige tussenstappen moet zetten. De databron is wat het is, die kan ik niet wijzigen , ik had idd ook al draaitabel van draaitabel geprobeerd, dat lukte niet, maar misschien wel via Power Query, ik ga gewoon eens wat tweaken.
Bedankt allen!

[Patrick_6369]

Als je de draaitabel maakt zoals @g0tanks aangeeft kun je daaroverheen de formules laten lopen.
Door je bereik van de formules slim te maken kun je met een refresh van de draaitabel ook meteen je eindresultaat vernieuwen.

Dan ziet het er in een simpele opzet als volgt uit:


De onderliggende formules zijn dan zoals onderstaand:


Toch met AANTALLEN.ALS

Overigens heb ik dus een tabelletje gemaakt met alle mogelijke combinaties en de weergave die ik voor iedere combinatie in de draaitabel zou verwachten. Daarheen verwijst de AANTALLEN.ALS formule, die kun je dus hiden. In de draaitabel moet je in de opties van de draaitabel de weergave van lege velden aanpassen naar nullen.

[Voor 15% gewijzigd door Patrick_6369 op 11-02-2021 18:12. Reden: Extra uitleg vergeten]

[EsthervdB]

@Patrick_6369 Tof dat je deze oplossing ook hebt gestuurd, nu kan ik gaan puzzelen. Tx!