Van invoerlijst naar overzicht

Het kan je haast niet ontgaan zijn dat Ashwin Brouwer en ik weer een oproep geplaatst hebben voor docenten om ervaringen te delen van goed gebruik door hun leerlingen van smartphones voor de les.

Daarvoor hebben we een formulier beschikbaar gesteld waarop tools kunnen worden aangevinkt. Op Twitter publiceer ik regelmatig een tussenstand met een top 20 overzicht. Maar hoe genereer ik nu dat overzicht vanuit al die (inmiddels meer dan 60) inzendingen?

Dat zal ik je uitleggen!

De reacties komen uiteraard in een Google Sheet binnen en wel als volgt:

Kahoot, Wikipedia

Facebook, Filmpjes maken, Foto's maken, Geluid opnemen, Google translate, Instagram, Magister, Opzoeken via Google, Socrative, Stopwatch functie, klasbord, WhatsApp, snapchat, bellen, smsen

klasbord

AnswerGarden, Filmpjes maken, Foto's maken, Geluid opnemen, Google formulieren invullen, Kahoot, LinkedIn, Magister, Opzoeken via Google

Mentimeter, Nearpod, QR codes, Socrative, Todaysmeet

Evernote, Foto's maken, Google translate, Magister, NFC, Opzoeken via Google, WRTS Mobile

Opzoeken via Google, Opgaves en uitwerkingen gebruiken die op bb staan

activity tracking

Filmpjes maken, Kahoot, Magister, Opzoeken via Google, Socrative, Stopwatch functie, Toongenerator App, oscilloscope app, decibel meter app, beschleunigung app voor natuurkunde

Zoals je ziet heel veel regels met door komma's gescheiden bijdragen. Hoe destilleer je daar nu een lijst uit? Ik moet toegeven, het heeft me behoorlijk wat uitzoekwerk gekost, maar het is me gelukt om dat te automatiseren! De toverformule die ik hier uiteindelijk voor gebruik ziet er zo uit:

=sort(transpose(trim(split(join(",";Formulierreacties!B2:B); ","; true))))

Deze formule plaats ik in de eerste cel (A1) op een nieuw blad in mijn sheet.
De gegevens staan in kolom B op het blad Formulierreacties.
Zo'n formule moet je van binnen naar buiten lezen.
Join zorgt ervoor dat de gegevens uit alle rijen in kolom B in één cel komen te staan, gescheiden door een komma.
Split zorgt ervoor dat alles wat door komma's gescheiden is in aparte cellen wordt geplaatst.
Het gevolg was dat veel woorden vooraf werden gegaan door een spatie, waardoor ze weer gezien werden als aparte bijdrage.
Trim zorgt er daarom voor dat die eerste spaties verwijderd worden.
Transpose zorgt ervoor dat alles wat in één rij werd geplaatst nu in een kolom terechtkomt.
Sort doet wat het zegt: Het sorteert de gegevens netjes op alfabet!

Daarmee heb ik dan één kolom met in iedere cel alle afzonderlijke bijdragen, maar dan ben ik er nog niet! Ik wil namelijk weten welke tool hoe vaak genoemd is. Daarvoor zet ik in de kolom ernaast in iedere cel een 1. Vervolgens gebruik ik in een kolom daar weer naast de volgende formule:

=sort(transpose(query(A1:B;"Select sum(B) pivot (A)"));2;FALSE)

Deze genereert een lijst van alle unieke bijdragen met daarachter een getal dat aangeeft hoe vaak de tool voorkomt in de eerste kolom. Vraag me niet hoe het werkt... het werkt!!!

Waarom zo'n uitgebreid verhaal? Omdat ik deze formules vaker gebruik en ik het een keer goed wilde documenteren. Ik weet zeker dat ik hier de nodige mensen blij mee maak. Zeker hen die het tot hier aan toe hebben volgehouden om dit technische verhaal te lezen:-)

Wil je zien hoe het werkt of de code hebben?
Klik dan hier voor een voorbeeldbestand. Ga naar Blad2 en klik op de cellen A2 en D2 om de formules te zien. Wil je gelijk een copy van het voorbeeldbestand om er zelf verder mee te werken, klik dan hier.

Heb je zelf ook zo'n superformule die je graag gebruikt? Doe me een lol en deel hem met me!