(Excel) Hulp bij voortgangslijst

[JKleinMiddelink]

Mijn vraag:
Als docent op een mbo-school willen wij graag een Excel-lijst gebruiken om bij te houden hoeveel procent een student voldoende heeft gehaald. Dit willen we zowel per periode als per half jaar kunnen uitrekenen. Ze moeten namelijk minimaal 75% behaald hebben. Nu heb ik al vaker met Excel gewerkt, maar weet ik niet of het volgende een mogelijkheid is:

Is het mogelijk om een percentage uit te rekenen uit gegevens waar zowel cijfers als 'voldoende/onvoldoende' aangegeven wordt? Zo niet, is het mogelijk om een flag of 'waar' te geven aan deze gegevens zodat de opmaak aangepast kan worden?

Relevante software en hardware die ik gebruik
Het gaat hier om Excel365.

Wat ik al gevonden of geprobeerd heb
Ik heb in een eerder stadium al een gemiddelde uitgerekend tussen twee periodes per vak, zowel gemiddelde cijfer (wat makkelijk is) danwel 'goed/voldoende/onvoldoende' met de volgende formule:

ALS(OF(M36="Onvoldoende"; C36="Onvoldoende");"Onvoldoende";ALS(EN(M36="Voldoende";C36="Voldoende");"Voldoende";ALS(EN(M36="Voldoende";C36="Goed");"Voldoende";ALS(EN(M36="Goed";C36="Voldoende");"Voldoende";ALS(EN(M36="Goed";C36="Goed");"Goed";"")))))

Waarbij de cellen staan voor een enkel vak.

Ik zou het op prijs stellen als jullie met mij mee willen denken om dit enigzins te automatiseren.

Ik had al gedacht aan de volgende oplossingen (als die uberhaupt mogelijk zijn):
- Uitrekenen hoeveel procent/welk deel van de cijfers rood (of nietrood) kleuren.
- Gemiddelde van alle cijfers uitrekenen en dezelfde formule gebruiken als hierboven voor de vakken met O/V/G voor twee waardes.

Nu heb ik een klein beetje verstand met Excel, maar met het schrijven van bovenstaande formule ben ik wel even bezig geweest, dus een expert ben ik zeker niet.

Graag hoor ik van jullie!

-Joost

[Dryjognos]

Eh, om heel kortaf je vraag te beantwoorden; 'ja'

Bij de voldoende / onvoldoende is het de vraag hoe zwaar ze tellen als je daar een 'gemiddelde' van wil hebben (bij meer dan 2 opties)

Je voorbeeld zelf kan korter door alleen de twee uiterste te pakken. Is er een onvoldoende -> dan onvoldoende. Zijn beide 'goed' -> dan goed en al het andere is voldoende.

Maar als je met meer rijen / kolomen gaat werken, zijn functies zoals SUMIF, SUMIFS, COUNTIF, COUNTIFS erg handig, dan tel je het aantal van een waarde (voldoende, onvoldoende, goed) en kan je daar percentages van uitrekenen.

[Voor 3% gewijzigd door Dryjognos op 05-11-2018 15:19]

[CappieL]

Ik neem aan dat je al een Excel-tabel hebt, zou je die kunnen delen? (met anonieme gegevens natuurlijk)
Zodoende is het makkelijker voor forumleden om ergens naar te verwijzen.
(screenshot zou bijvoorbeeld al helpen)

[sypie]

Ik zou in een 2e werkblad de berekeningen uitvoeren en de uitkomsten in werkblad 1 laten zien.

Kolom 1: aantal toetsen (=aantalarg)
Kolom 2: voldoendes (aantal.als)
Kolom 3: percentage voldoende ((kolom1/kolom2)*100)x

De excel waar ik mee bezig ben geweest, ook voor het genereren van documenten voor examendossiers ziet er deels zo uit:

[Voor 28% gewijzigd door sypie op 05-11-2018 15:26]

[B-Real]

Paar variabelen die van toepassing zijn gok ik?:
- Als een leerling een toets nog niet heeft gemaakt dan moet het resultaat zijn dat er een "onvoldoende" is?
- Leerlingen mogen x aantal onvoldoendes hebben of moet het gemiddelde >5,5 zijn?
- Indien er een x aantal onvoldoendes behaalt mogen worden, moeten dit dan bijv. minimaal het cijfer 5 zijn?
- Leerlingen kunnen ook de beoordeling onvoldoende, voldoende en goed halen?
- In totaal moet dus 75% voldoende zijn, je zult de vakken zonder een cijfer als beoordeling (dus onvoldoende, voldoende, goed) ook als "waarde" mee moeten nemen in je berekening van het percentage.

Zijn er nog meer variabelen?

Een geanonimiseerd voorbeeld zou handig zijn. Ik zou echter beginnen door de leerlingen op de regels op te nemen en de kolommen te gebruiken voor de verschillende vakken.

Vervolgens zou ik stap de variabelen uitrekenen in kolommen rechts van deze tabel (of een apart tabblad, maar dat kan later ook nog). Hiervoor gebruik je formules zoals "gemiddelde", "als", "subtotaal" etc.
Die zijn stuk voor stuk niet lastig, maar wel als je deze in 1x in één grote formule wil proppen.

Daarom stap voor stap en daarna de formules "nesten" zoals dat in Excel termen heet.

[BernardV]

Een simpel voorbeeldje hoe je uit een lijst een aantal cijfers > 5.5 en < 5.5 haalt en het totaal.

[dix-neuf]

@JKleinMiddelink,
de combinaties "voldoende" - "onvoldoende" en "onvoldoende - "voldoende" voor resp. C36 en M36 (en het resultaat daarvan) ontbreken. Die zullen toch ook kunnen voorkomen neem ik aan?

En als je als resulaat een getal/percentage wil, zul je aan moeten geven welke waardering je aan je niet-cijfermatige waardering wil toekennen.

[sypie]

@JKleinMiddelink Hoe staat het er nu voor?

[JKleinMiddelink]

Hoi allemaal!

Bedankt voor jullie berichtjes,

Ik zit vandaag in de vergaderingen (onderwijs, yay), maar heb donderdag (en misschien vanavond) een paar uur om alles door te nemen en uit te vogelen. Ik hou jullie op de hoogte!

-Joost

[Voor 5% gewijzigd door JKleinMiddelink op 06-11-2018 09:28]

[JKleinMiddelink]

Update!

Zie onderstaande link voor een screenshot (namen weggehaald)
https://imgur.com/a/5gmr3dT
Ik heb jullie reacties bekeken en vul aan:

@Dryjognos : De vakken moeten voor het uiteindelijke resultaat tellen als voldoende of onvoldoende. Dit omdat ze gemiddeld 75% van alle vakken gehaald dienen te hebben voor we bekijken of ze over mogen of niet. Omdat er enkele vakken van een O/V/G-waardering uitgaan en anderen weer van een cijfer, is het denk ik een lastig perikel.

Ik moet wel zeggen dat ik inmiddels denk, dankzij jullie reacties, dat ik de O/V/G-waarderingen moet berekenen tot een percentage voldoende of onvoldoende, dan de cijfers tot een percentage voldoende of onvoldoende en daar vervolgens het percentage van uitreken. (Tenzij dat niet handig is...)

@sypie Het is lastig om de berekening in een ander werkblad te doen, aangezien ze een excelbestand willen voor alle studenten. Tenzij het een algemene formule wordt per periode, dan zou het nog te doen zijn, ook al weet ik niet zo goed hoe dat zou moeten. Google is mijn vriend

@B-Real Het klopt dat als een toets niet gemaakt is (leeg) het als onvoldoende moet gelden, zodat er toch een melding/flag is.
Ze moeten in totaal per periode 75% van alle vakken hebben behaald (74 is onvoldoende, 76 is prima)
Welk cijfer het onvoldoende is, maakt voor deze berekening niet uit.
Het klopt dat ze bij het ene vak O/V/G kunnen halen en het ander juist een cijfer.
- Hoe geef ik een waarde aan een O/V/G-waardering?

Bedankt voor de tips! Ik zal even uitzoeken wat dat 'nesten' precies is...

@dix-neuf Klopt, dat kan voorkomen, maar bij 1 onvoldoende is het resultaat al onvoldoende, vandaar de 'OF'-functie, dan maakt het andere resultaat voor de eindstand niet uit.

Ik heb vandaag nog tot 10 uur/half 11 om wat te klungelen, dus ik ga weer aan de slag, anders heb ik dinsdagochtend weer tijd om te sleutelen...

Nogmaals bedankt voor jullie ondersteuning! Het uitvogelen is nooit het ergste, maar een aanwijzing in de goede (of in ieder geval nieuwe) richting is altijd prettig.

Prettige vrijdag!

-Joost

[Lustucru]

Als ik het goed begrijp heb je dus een resultatenlijstje in de vorm van {V,G,3.5,O,7,5,V} en je wilt het percentage voldoende of beter weten?

De meest simpele manier om dat te realiseren is domweg tellen:
=aantal.als([resultaatlijst];"V")+aantal.als([resultaatlijst];"G")+aantal.als([resultaatlijst];">5,5")

Wil je de totale voortgang weten in percentage dan deel je dat het aantal mogelijke resultaten, wil je de relatieve voortgang weten in procenten dan deel je dat door het aantal behaalde resultaten.

Er zijn nog heel veel andere manieren om het te noteren, maar dit is wel de meest inzichtelijke voor iemand die niet heel erg Excel vaardig is.

[B-Real]

Wat misschien ook nog een handige is, is de optie "Conditional formatting" (voorwaardelijke opmaak).
Daarmee kun je in de cijferlijst aangeven dat als een cijfer <5,5 of "onvoldoende" deze cel rood kleurt en bijv geel als een cel leeg (in Excel middels: "") is en het vak dus nog niet afgerond is.

Dan kun je vlot zien per student bij welk vak het fout gaat.

Overigens zou ik geen waarde hangen aan O/V/G, immers het is toch juist een vak "zonder cijfer"?
Dat wordt dus een formule in de trant van: als het resultaat V/G is dan "akkoord", is het resultaat O dan "niet akkoord"

Je afbeelding doet het niet, maar aan je posts te lezen ben je op de goede weg. Lekker alles stap voor stap in losse kolommen uitvoeren, samenvoegen tot één grote formule en dus één kolom kan altijd nog

[JKleinMiddelink]

Hoi allemaal,

Vandaag een nieuwe periode, nieuwe kansen. Op de maandag zowaar wat tijd over om dit beter te bekijken.

@Lustucru Ik ga vanmiddag jouw idee proberen toe te passen en kijken hoe deze werkt in de resultatenlijst. Thanks voor de tip! Ik was zo bezig met het bedenken tot een formule die alles controleert, dat domweg tellen weg is gevallen.

@B-Real Ik maak al gebruik van voorwaardelijke opmaak om inderdaad snel te zien of iets onvoldoende is (dikke vette rode invulling), en dat werkt prima. Alleen ik heb een aantal collega's die digibeet zijn en snel in een kolom willen zien of een student 75% van alle cursussen in een periode heeft behaald, daar werkt dat helaas niet goed voor.

Het klopt dat we geen waarde willen hangen aan O/V/G, daarom moet het eigenlijk ook een check zijn; behaald of niet behaald, voor de 75% passmark. Want als ze alle cijfers dikke voldoende hebben maar bijna allemaal onvoldoendes hebben gescoord bij O/V/G, dan is dat alsnog onder de 75%.

Ik zal proberen wederom een afbeelding te posten vanmiddag.

--Joost

[JKleinMiddelink]

Update!

@Lustucru Ik heb jouw idee toegepast in de lijst en het is inderdaad de meest simpele manier om een check te doen naar bepaalde vakken. Dit werkt top! Thanks!

Ik ben nu aan het klooien om het percentage te berekenen van het aantal bepaalde vakken. Voor de eerste periode lijkt dit goed te kloppen (12 vakken) maar de periode erna (14 vakken) lijkt het percentage waarop de 'goed' wordt gegeven op 8 van 14 vakken gegeven, terwijl dat geen 75% is, ondanks dat de voorwaardelijke opmaak wel op 75% staat.

Enig idee hoe dit kan/opgelost kan worden?

Ik weet dat het op te lossen is door ipv percentages dan maar het getal uit te rekenen wat minimaal gehaald moet worden, maar de percentage berekening kan makkelijk gekopieerd en geplakt worden.

Also: afbeelding (poging 2)
https://imgur.com/a/vjCUkHE

--Joost

EDIT: Schijnbaar heeft het zichzelf opgelost zodra ik meer gegevens invulde..? Snap niet helemaal waarom, maar het lijkt te werken! Bedankt allemaal!

EDIT2: Nieuw probleem: In de lijst verandert de 75% op basis wat een andere regel scoort.

Voorbeeld:

Bij leerling 1 (Random waardes ingevuld om te testen) zijn er 5 van de 10 onderdelen behaald. 50% dus, maar Excel geeft een '75% gehaald'-waarde. Zodra ik bij leerling 2 10 voldoendes invul (dus 80%), dan verandert de 5/10 van een 'gehaald' naar een 'niet gehaald' zodra ik de zevende voldoende invul.

HUH?

Zie onderstaande screenshots:

https://imgur.com/a/7dhjcZE

Enig idee waardoor dit veroorzaakt wordt? Of toch het aantal vakken (getal) per periode invullen in plaats van percentage?

--Joost

EDIT3: (jaja, druk bezig) Als ik een rij volledig invul met 100% score, dan gaat de berekening wel volgens plan, dus vermoedelijk heeft het een basiswaarde nodig om de 75% op te berekenen, dus daarvan overal een regel ingevuld en verborgen. Het werkt, I guess.

[Voor 39% gewijzigd door JKleinMiddelink op 13-11-2018 14:25]