[Excel] Tour poule spreadsheet, hulp nodig bij verbetering.

[MadDogMcCree]

Mijn vraag

Ik doe al jaren een Tour de France poultje en gebruik daarvoor uiteraard Excel. Ieder jaar zijn er alleen steeds weer meer deelnemers en het invullen van alle lijstjes duurt dan ook best wel lang. Momenteel kopieer/plak (of gewoon zelf intikken) ik de renners vanuit het invulschema tab naar de individuele lijsten en gebruik ik de = functie om de punten per deelnemer op te tellen. Zodra het bestand dus af is gaat het invullen hartstikke snel. Eén keer de punten invullen en iedere deelnemer krijgt automatisch de gescoorde punten.

Ik wil echter voor de komende jaren het bestand verbeteren. Momenteel staat bij iedereen in de cellen C5-C19 de rugnummers van de gekozen renners. Nu wil ik de cellen rondom dat rugnummer de functie geven dat wanneer ik dus het rugnummer in de C5-C19 cel invul, ze automatisch de cellen overnemen van het invulschema tabblad. Dus als ik 11 invul bij C5, dat dan in A5 de achternaam van de renner komt te staan die op het andere tablad dan twee cellen naast 11 in de C kolom staat, in dit geval A14 van tabblad invulschema. Vul ik 21 in, dan cel A23 van het invulschema tablad, enz enz.

Hopelijk is het een beetje duidelijk wat ik bedoel.

Het probleem is natuurlijk, ik kom er niet uit qua formule (lees; ik heb echt geen idee), ik heb hiervoor echt te weinig kennis van Excel. Is dit überhaupt mogelijk?


Relevante software en hardware die ik gebruik

Excel 2016


Hier een link naar een bestand, ik heb even het e.e.a. eruit gehaald en slechts drie tabs waar het om gaat erin laten staan. Speler 1 is hoe ik het op de huidige manier doe, ik vul handmatig alle namen en rugnummers in, en vervolgens '=' ik naar de overeenkomende cellen.

Bij Speler 2 heb ik een leeg veld. Cellen A5-A19, B5-B19 en E5-E19 enz enz (tot AD5-AD19) zouden dus automatisch het rijtje van het invulschema veld over moeten nemen dat overeenkomt met het ingevulde nummer. De mogelijke nummers zijn 1-218, exclusief alle tientallen en negentallen.

[Renzzie]

Beginnen met het nummer in rij a en dan in de overige rijen vervolgens verticaal zoeken gebruiken. Ook in je invulschema het nummer in rij a zettten. even Googlen hoe je dit gebruikt. Zijn vele voorbeeldjes van te vinden.

Als ik zo snel kijk op mijn mobiel moet dat prima werken.

[ Voor 9% gewijzigd door Renzzie op 04-07-2022 19:55 ]

[MadDogMcCree]

@Renzzie Heb je daar een voorbeeldje van? Ik heb even de genummerde cellen in de C kolom gelaten, en de volgende formule voor cel E5 in Speler 2:

=VERT.ZOEKEN(C5; Invulschema!C5:C201; 5; ONWAAR)

Echter krijg ik dan een '#VERW!' melding. Wat doe ik hier verkeerd?

Edit: Ook zonder spaties en met $ teken dezelfde melding, dit werkt dus ook niet:

=VERT.ZOEKEN(C5;Invulschema!$C$5:C$201;5;ONWAAR)

[ Voor 22% gewijzigd door MadDogMcCree op 04-07-2022 20:50 ]

[Renzzie]

Het werkt dus niet als je het in c laat staan.
Kolom c gewoon helemaal selecteren en dan knippen > plakken. Zowel in invul als speler 2.

Je hebt bijv nr 1 in A5 staan als nummer.
Dan plaats je de formule in b5
=VERT.ZOEKEN(a1;Invulschema!$A:$C;2;ONWAAR)

Dit zal pogo car als waarde geven. Als je 3 invult wordt het tadej.

Heb op m'n mobiel getypt, dus kan een typo inzitten

[MadDogMcCree]

Renzzie schreef op maandag 4 juli 2022 @ 21:02:
Het werkt dus niet als je het in c laat staan.
Kolom c gewoon helemaal selecteren en dan knippen > plakken. Zowel in invul als speler 2.

Je hebt bijv nr 1 in A5 staan als nummer.
Dan plaats je de formule in b5
=VERT.ZOEKEN(a1;Invulschema!$A:$C;2;ONWAAR)

Dit zal pogo car als waarde geven. Als je 3 invult wordt het tadej.

Heb op m'n mobiel getypt, dus kan een typo inzitten

Zo werkt hij inderdaad wel, maar waarom werkt die niet wanneer ik hiervoor kolom C gebruik i.p.v. kolom A?

[Renzzie]

Omdat de functie naar rechts kijkt. Hij kijkt of hij in rij a iets gevonden heeft (dit zou 1 zijn) en dan het aantal plaatsen rechts (de 2 en 3). Verticaal zoeken kijkt dus niet naar links.

Aanvullend: hij vind eerst het cijfer en kijkt dus rechts van het cijfer en geeft die waarde weer. Nu dus de naam. Als het cijfer niet bestaat geeft hij n/b weer

[ Voor 30% gewijzigd door Renzzie op 04-07-2022 21:10 ]

[MadDogMcCree]

Renzzie schreef op maandag 4 juli 2022 @ 21:08:
Omdat de functie naar rechts kijkt. Hij kijkt of hij in rij a iets gevonden heeft (dit zou 1 zijn) en dan het aantal plaatsen rechts (de 2 en 3). Verticaal zoeken kijkt dus niet naar links.

Aanvullend: hij kijkt dus rechts van het cijfer en geeft die waarde weer. Nu dus de naam

Top! Hartstikke bedankt!

[Teun_2]

Verticaal zoeken heeft inmiddels een betere en gemakkelijker te gebruiken functie: x.zoeken. ik geloof dat ze helaas niet beschikbaar is in 2016. X.ZOEKEN-functie

Wil je het echt verder automatiseren, zou ik via powerquery de resultaten van cyclingstats inlezen en verwerken. Dan heb je maar op ververs data te klikken en alles krijgt een update.
https://www.procyclingsta...022/stage-2/result/result

[ Voor 32% gewijzigd door Teun_2 op 05-07-2022 07:47 ]

[MadDogMcCree]

@Teun_2 wellicht eens een keer updaten naar 2021 dan, ik zie dat je al een key kan kopen voor 15 euro. Als ik een 2021 key gebruik, schakelen mijn huidige installaties van Office software automatisch over naar 2021, of vereist het een herinstallatie?

Helemaal automatiseren is niet nodig, het invulwerk bestaat nu uit dagelijks de punten in te vullen en het dagklassement te hersorteren, dat is in een minuutje gedaan. Verder nog wat klein, minder vitaal, invulwerk.

Voorheen had ik ook voor iedere speler een grafiek met het spelverloop, maar dat kostte me echt teveel tijd.

[Teun_2]

@MadDogMcCree dat durf ik niet te zeggen. Ik werk al langer met de 365-licentie.
Ik krijg die van mijn werkgever, dus het kost me niets. Ervoor had ik die privé ook wel. Je betaalt eigenlijk niet zo heel veel voor veel cloudstorage en je krijgt er office gratis bij. €100 voor 6 gebruikers/jaar met elks 1TB opslag.

Als je alle info in je sheet hebt, is een grafiek toch snel gemaakt?
Al begin je dan PowerBi wel de betere tool te worden.

[MadDogMcCree]

@Renzzie @Teun_2 Ik probeer mijn invulschema nu ook wat te automatiseren, maar ik loop tegen een volgend probleem aan. Ik heb een tabbladje met de daguitslagen, in de E kolom de rugnummers, in de G kolom de behaalde punten. Het probleem is alleen dat hij slechts éénmalig de punten, terwijl sommige renners 2x punten kunnen halen, via de gele, groene, bollen en witte trui. De volgende formule geeft slechts 1x de punten, namelijk naast die van het eerst gevonden overeenkomend getal.

code:

=VERT.ZOEKEN(A5;Uitslagen!E7:G25;3;ONWAAR)

Wanneer ik er de volgende formule van maak dan geeft hij een #N/B foutcode als hij het getal van A5 niet kan vinden:

code:

=VERT.ZOEKEN(A5;Uitslagen!E7:G21;3;ONWAAR)+VERT.ZOEKEN(A5;Uitslagen!E22:G22;3;ONWAAR)+VERT.ZOEKEN(A5;Uitslagen!E23:G23;3;ONWAAR)+VERT.ZOEKEN(A5;Uitslagen!E24:G24;3;ONWAAR)+VERT.ZOEKEN(A5;Uitslagen!E25:G25;3;ONWAAR)

A5 is in dit geval '1', en als hij in uitslagen geen '1' vindt in E22 tot en met E25 dan krijg ik dus die foutcode. Vul ik ook in E22 tot en met E25 '1' in, dan telt hij wel alles op.

Wat doe ik fout?


EDIT:

De volgende formule werkt wel, alleen heeft als nadeel dat het onwijs veel werk is om het één voor één in te voeren:

code:

=SOM.ALS(Uitslagen!E7:E25;"1";Uitslagen!G7:G25)
=SOM.ALS(Uitslagen!E7:E25;"2";Uitslagen!G7:G25)

En dat dan tot rugnummer 218 en 26 kolommen.

[ Voor 11% gewijzigd door MadDogMcCree op 18-07-2022 14:25 ]

[Teun_2]

Heb je misschien een update van de excel?
Ik versta volgende zin niet goed:

"Het probleem is alleen dat hij slechts éénmalig de punten, terwijl sommige renners 2x punten kunnen halen, via de gele, groene, bollen en witte trui."

Bedoel je dat je een rugnummer bijvoorbeeld meerdere keren in kolom E kan voorkomen? Maak je dan nog een onderscheid in wat voor soort punten dit zijn?

Als je met vertikaal zoeken geen resultaat krijgt, krijg je idd die foutmelding. Je kan die opvangen met 'ALS.FOUT(formule;0). Dan krijg je waarde 0 als je geen resultaat vindt.

[MadDogMcCree]

@Teun_2

Hier een voorbeeld: https://docs.google.com/s...FF7NJ9Cvw/pub?output=xlsx

Edit, hij verwijst naar een ander document, vanwege een copy paste, dat moet je even negeren, waar nu een foutmelding staat bij de rennersnamen op het uitslagen tabblad staan normaal gesproken wel gewoon de renners namen

Je kan het gros negeren, heb alleen de cellen G5, H5 en I5 op het invulschema tabblad de bovenstaande formules gegeven.

De 3e formule werkt perfect, maar die moet ik dus handmatig voor iedere cel aanpassen door per kolom de rugnummers te wijzigen.

Hoe ziet die formule eruit als ik ALS.FOUT toevoeg aan:

code:

=VERT.ZOEKEN(A5;Uitslagen!E7:G21;3;ONWAAR)+VERT.ZOEKEN(A5;Uitslagen!E22:G22;3;ONWAAR)+VERT.ZOEKEN(A5;Uitslagen!E23:G23;3;ONWAAR)+VERT.ZOEKEN(A5;Uitslagen!E24:G24;3;ONWAAR)+VERT.ZOEKEN(A5;Uitslagen!E25:G25;3;ONWAAR)

Edit2:

Zo werkt hij:

code:

=ALS.FOUT(VERT.ZOEKEN(A5;Uitslagen!L7:N21;3;ONWAAR);"0")+ALS.FOUT(VERT.ZOEKEN(A5;Uitslagen!L22:N22;3;ONWAAR);"0")+ALS.FOUT(VERT.ZOEKEN(A5;Uitslagen!L23:N23;3;ONWAAR);"0")+ALS.FOUT(VERT.ZOEKEN(A5;Uitslagen!L24:N24;3;ONWAAR);"0")+ALS.FOUT(VERT.ZOEKEN(A5;Uitslagen!L25:N25;3;ONWAAR);"0")

Alleen, ik zie al dat dit ook niet gemakkelijk te kopiëren is naar de volgende cellen. Op deze manier is het nog veel meer werk dan de SOM.ALS formule

[ Voor 39% gewijzigd door MadDogMcCree op 18-07-2022 15:54 ]

[Teun_2]

Als je de formule wil doortrekken, moet je de lookup ranges ook vastzetten. Dus Uitslagen!$E$7:$G$21 etc.
En ook $A5 ipv A5
Schakelen tussen relatieve en absolute verwijzingen.

[MadDogMcCree]

Teun_2 schreef op maandag 18 juli 2022 @ 16:12:
Als je de formule wil doortrekken, moet je de lookup ranges ook vastzetten. Dus Uitslagen!$E$7:$G$21 etc.
Schakelen tussen relatieve en absolute verwijzingen.

Ahja, natuurlijk, top! Dit gaat helemaal goedkomen. Het zal even wat werk zijn, maar zo is het wel te doen. Alweer bedankt!

[Teun_2]

MadDogMcCree schreef op maandag 18 juli 2022 @ 16:18:
[...]


Ahja, natuurlijk, top! Dit gaat helemaal goedkomen. Het zal even wat werk zijn, maar zo is het wel te doen. Alweer bedankt!

Graag gedaan.

Idealiter werk je met tabellen met je ruwe data en dan tabellen met je berekeningen, resultaten en visuele weergaves.

Door je etappes zo mooi op te splitsen en ze overzichtelijk op een tabblad te plaatsen, maak je het visueel interessant, maar niet makkelijk om met formules data op te halen. Je zou gewoon een platte lijst kunnen maken met rugnummer, etappenummer, plaats, punten, puntel bollen etc. naast een tabel met rennerinfo: ploeg, rugnummer etc. en die up to date houden (of via powerquery binnentrekken van pro cyclingstats en dan je 'mooie' tabellen de data laten oppikken uit die tabellen met ruwe data. Je zou via powerpivot dan zelfs alle resultaten relatief makkelijk aan elkaar kunnen knopen.

[ Voor 8% gewijzigd door Teun_2 op 18-07-2022 16:24 ]