Excel hulp / Formule aantal en als voorwaarden

[semsem1999]

Mijn vraag

Voor de stage tijdens mijn studie gebruik ik een Excel sheet, waarin ik mijn uitgevoerde taken bijhoud en beoordeel. Nu wil ik een overzichtsblad maken. In dit overzichtblad heb ik een tabel waarin ik door de aantal.als functie het aantal keer tel dat een specifieke taak voorkomt (bijv. bezoek aan bouwplaats heeft de afkorting bzbp). Aan deze taak is een beoordeling gekoppeld, die op een ander blad vermeld staat. Op het overzichtsblad wil ik het aantal keer dat deze taak met bijvoorbeeld voldoende is beoordeeld berekenen. Zou iemand mij kunnen adviseren wat voor formule ik het beste kan gebruiken?

Relevante software en hardware die ik gebruik
Enkele screenshots van het sheet met de gebruikte codes in Excel uit Office365



Op de bovenstaande afbeelding valt te zien dat ik in de kolom op het tweede blad het aantal keer dat de taak met een stukje tekst bzbp tel.




Op de bovenstaande afbeelding staat een deel van het tweede blad waaruit ik het aantal keer dat een voldoende voorkomt (dus "V") bij een taak met de codering "*BZBP*" wil bepalen voor in de tabel op mijn overzichtsblad. Voor de kolom met BZBP geldt het volgende bereik 'B2 Competentiematrix V2'!C6:C53. En voor de tabel met de beoordeling op het blad geldt het volgende bereik 'B2 Competentiematrix V2'!E6:Z53.

Wat ik al gevonden of geprobeerd heb
Ik ben aan de slag gegaan met de aantal.als + aantal als functie en de als(en geneste functie. Tot op heden is het mij enkel gelukt om de het totaal aantal voldoendes uit de tabel op het andere blad te bepalen. Dus niet het aantal voldoendes behorend bij de specifieke taak. Ik vermoed dat de fout zit in het selecteren van de hele tabel, waarna ik paar keer de overloop en waarde fout kreeg.

[Emperor]

Volgens mij moet je 'aantallen.als' gebruiken.
Let wel even op dat de optelling anders werkt (andere volgorde)
AANTALLEN.ALS(criteriabereik1;criterium1;[criteriabereik2;criterium2]…)

[semsem1999]

Emperor schreef op maandag 28 december 2020 @ 19:08:
Volgens mij moet je 'aantallen.als' gebruiken.
Let wel even op dat de optelling anders werkt (andere volgorde)
AANTALLEN.ALS(criteriabereik1;criterium1;[criteriabereik2;criterium2]…)

Dank voor de reactie! Na wat uren puzzelen ben ik inderdaad ook uitgekomen bij de aantallen als functie, mede omdat het tekst waarden zijn. Ik heb het criteriabereik alleen ingesteld voor meerdere kolommen en rijen, dus niet enkel één kolom zoals ik op internet veel voorbij zie komen. Zo kwam ik op de volgende formule uit:

=AANTALLEN.ALS('B1 Competentiematrix V1'!C6:Z53;"*BZBP*";'B1 Competentiematrix V1'!C6:Z53;"V")

Als antwoord krijg ik helaas 0, terwijl in de kolom B1 Competentiematrix V1'!C6:C53 *BZBP* 3 keer voorkomt. Vervolgens in het "veld" 'B1 Competentiematrix V1'!E6:Z53 komt 3 keer bij de taak BZBP een voldoende voor. In mijn overzichtstabel waar ik de bovenste formule in heb gevuld, zou ik dan idealiter als antwoord 3 willen hebben. Toch blijkt er iets fout te gaan, of ik pas de verkeerde formule toe

[g0tanks]

semsem1999 schreef op maandag 28 december 2020 @ 19:42:
[...]
=AANTALLEN.ALS('B1 Competentiematrix V1'!C6:Z53;"*BZBP*";'B1 Competentiematrix V1'!C6:Z53;"V")

De criteriabereiken in deze formule zijn beide keren hetzelfde. Dan krijg je per definitie nul omdat een cel niet zowel de waarde *BZBP* als V kan hebben.

[semsem1999]

g0tanks schreef op dinsdag 29 december 2020 @ 00:45:
[...]


De criteriabereiken in deze formule zijn beide keren hetzelfde. Dan krijg je per definitie nul omdat een cel niet zowel de waarde *BZBP* als V kan hebben.

Dat maakt het een stuk duidelijker, dank voor de tip! Wat is dan eventueel een oplossing om enkel de voldoendes (V) te tellen bij de cellen die *BZBP* bevatten? Ik zit zelf te denken aan een als.voorwaarde waarin ik een aantal.als nest. Of loop ik dan tegen hetzelfde probleem aan?

• =ALS('B1 Competentiematrix V1'!C6:C53="*BZBP*";AANTAL.ALS('B1 Competentiematrix V1'!E6:Z53;"V");0)
• =ALS.VOORWAARDEN('B1 Competentiematrix V1'!C6:C53="*BZBP*";AANTAL.ALS('B1 Competentiematrix V1'!E6:Z53;"V"))

De eerste als formule geeft weer 0 als uitkomst, terwijl het criteriumbereik nu niet het probleem kan zijn. Daarentegen geeft de onderste formule een #N/B fout, waarbij het verschil in aantal rijen en kolommen de boosdoener lijkt te zijn.

[ Voor 30% gewijzigd door semsem1999 op 29-12-2020 10:05 ]

[Patrick_6369]

Zit je er aan vast om de invoer van de data zo te houden als op je tweede plaatje? Want uit een tabel dingen gaan zoeken is gewoon best complex.
Als je er gewoon een lange lijst met 'platte' data van maakt, kun je er een draaitabel overheen gooien en heb je volgens mij in een handomdraai je resultaat.

[g0tanks]

semsem1999 schreef op dinsdag 29 december 2020 @ 09:55:
[...]


Dat maakt het een stuk duidelijker, dank voor de tip! Wat is dan eventueel een oplossing om enkel de voldoendes (V) te tellen bij de cellen die *BZBP* bevatten? Ik zit zelf te denken aan een als.voorwaarde waarin ik een aantal.als nest. Of loop ik dan tegen hetzelfde probleem aan?

• =ALS('B1 Competentiematrix V1'!C6:C53="*BZBP*";AANTAL.ALS('B1 Competentiematrix V1'!E6:Z53;"V");0)
• =ALS.VOORWAARDEN('B1 Competentiematrix V1'!C6:C53="*BZBP*";AANTAL.ALS('B1 Competentiematrix V1'!E6:Z53;"V"))

De eerste als formule geeft weer 0 als uitkomst, terwijl het criteriumbereik nu niet het probleem kan zijn. Daarentegen geeft de onderste formule een #N/B fout, waarbij het verschil in aantal rijen en kolommen de boosdoener lijkt te zijn.

Ik denk dat SOMPRODUCT voor jou de oplossing biedt. Hieronder heb ik het geprobeerd met wat dummy data:

[semsem1999]

Patrick_6369 schreef op dinsdag 29 december 2020 @ 12:03:
Zit je er aan vast om de invoer van de data zo te houden als op je tweede plaatje? Want uit een tabel dingen gaan zoeken is gewoon best complex.
Als je er gewoon een lange lijst met 'platte' data van maakt, kun je er een draaitabel overheen gooien en heb je volgens mij in een handomdraai je resultaat.

Ik zit helaas deels vast aan deze indeling, daarnaast heb ik helaas nog niet alle kennis om een draaitabel succesvol toe te passen. Achteraf gezien was een database zoals in Access misschien een stuk makkelijker.

[semsem1999]

g0tanks schreef op dinsdag 29 december 2020 @ 12:27:
[...]


Ik denk dat SOMPRODUCT voor jou de oplossing biedt. Hieronder heb ik het geprobeerd met wat dummy data:
[Afbeelding]

Hartstikke bedankt voor deze tip, waarmee ik succesvol mijn gewenste doel heb kunnen behalen!
De SOMPRODUCT functie bleek inderdaad na een paar aanpassingen de voldoendes en dus ook andere beoordelingen, die gekoppeld zijn aan een taak, zonder moeite te kunnen filteren. Ik had eerder bij de aantal.als formule gebruik gemaakt van de wildcard opties, daar mijn codering bestaat uit een stabu nummer-afkortingtaak.nummer van de uitgevoerde taak. De wildcard optie blijkt niet goed te werken met een somproductfunctie, vandaar dat ik dit hier gelijk meld voor de medetweakers. Ik heb ervoor gekozen mijn codering op te splitsen in drie kolommen, waarna de somproduct functie enkel de kolom doorzoekt met de afkorting van de taak. De onderstaande afbeelding geeft mijn aanpassing in de codering weer.



De somproduct functie heb ik toegepast over meerdere bladen, waarna enkel werd uitgezocht hoeveel keer de voldoende per blad voorkomt. Ik wilde een totaal aantal voldoende bepalen, dus heb ik de somproduct functie genest in een somfunctie. Na een paar testrondes blijkt deze formule goed te werken, zoals op de onderstaande formule weergegeven staat.



Via deze weg wil ik dan ook alle betrokken tweakers bedanken voor het meedenken en het leveren van een snelle en bovendien correcte oplossing!