Formule die een specifiek woord in een ander tabblad opzoekt

[Verwijderd]

Ik zit met een excel bestand waarbij deelnemers de wedstrijden in de eredivisie voorspellen. Nu wil ik een overzicht inbouwen waarin excel automatisch verwerkt hoevaak een deelnemer met een specifieke club punten scoort (dus welke clubs hij het vaakst de wedstrijd goed van voorspelt).

Het excel bestand bestaat uit 34 tabbladen met alle voorspellingen en puntenscores van de deelnemers en de wedstrijden, hoeveel punten er mee te behalen zijn en de uitslag). Verder nog wat tabbladen voor tussenstanden en dergelijke.

Ik wil in een apart tabblad kunnen weergeven hoe iedereen met bepaalde clubs scoort. Om dit te realiseren moet ik een formule hebben die zelf de plek van de clubnaam in het tabblad van een specifieke speelronde opzoekt en nagaat of die club gewonnen heeft (wat ook in dat tabblad is aangegeven).

Iemand enig idee hoe ik hier een begin mee zou kunnen maken?

Alvast bedankt!

[F_J_K]

Zonder concrete situatieschets incl. duiding van waar je vastloopt met een eigen poging, kunnen we moeilijk concrete tips geven Misschien wil je bijv. precies aangeven hoe ieder tabblad is opgebouwd.

Waarschijnlijk wil je wat met horizontaal of verticaal zoeken, afhankelijk van de precieze inrichting. Zoek dus even op die termen (er zijn hier op GoT in CSA vele voorbeelden waar dit wordt gebruikt). Dan combineren met een ALS() voor de check of is gewonnen, gelijk gespeeld of verloren (al kan je dat misschien in de hoofdsheets al hebben opgenomen door de punten op te nemen: 0, 1, 3).

[goldcard]

Ik heb nog even wat controlevragen hoor, kijken of ik jouw opzet begrijp:

- Per deelnemer is er 1 excel bestand
- Elk excel bestand bevat (iig) 34 tabs (1 tab per speelronde) en een nog te bouwen 'score per clubs' tab
- De speelronde tabs bevatten de voorspellingen, daadwerkelijk resultaat én het aantal behaalde punten van de deelnemer (obv vergelijking voorspelling vs resultaat)

So far so good?

Kan je aangeven hoe jouw 'score per club' tab eruit moet komen te zien? Zoals ik hem nu voorstel heb je een rij met alle clubnamen met daarachter het totaal van de behaalde punten van alle speelrondes voor die club? Een lijst dus? Of in matrix vorm, waarbij je het overzicht per club (vertikaal) per speelronde (horizontaal) ziet?

[Verwijderd]

goldcard schreef op dinsdag 19 juli 2016 @ 13:59:
Ik heb nog even wat controlevragen hoor, kijken of ik jouw opzet begrijp:

- Per deelnemer is er 1 excel bestand
- Elk excel bestand bevat (iig) 34 tabs (1 tab per speelronde) en een nog te bouwen 'score per clubs' tab
- De speelronde tabs bevatten de voorspellingen, daadwerkelijk resultaat én het aantal behaalde punten van de deelnemer (obv vergelijking voorspelling vs resultaat)

So far so good?

Kan je aangeven hoe jouw 'score per club' tab eruit moet komen te zien? Zoals ik hem nu voorstel heb je een rij met alle clubnamen met daarachter het totaal van de behaalde punten van alle speelrondes voor die club? Een lijst dus? Of in matrix vorm, waarbij je het overzicht per club (vertikaal) per speelronde (horizontaal) ziet?

Bijna so far so good...

Alles is in 1 excel bestand opgenomen. Alle deelnemers in één.

Bijgevoegd even twee bestanden.
Voorbeeldje hoe de speelrondes tabs opgebouwd zijn en een voorbeeld hoe ik de score per club tab in mijn hoofd heb (even heel basic nog)

https://1drv.ms/x/s!AlBXjrYNzBh-lgMBfpII57-xASLJ Speelronde tab
https://1drv.ms/x/s!AlBXjrYNzBh-lgLB1Dlnn8pzRHpg Score per club tab

Hetgeen het voor mij lastig maakt als dat een specifieke club niet elke week in dezelfde cel staat (omdat ze bijvoorbeeld eerst vrijdag spelen en een week later op zondag en bovendien elke week uit/thuis en tegen een andere club).
Als de club in elke speelronde tab in dezelfde cel had gestaan had ik er wel redelijk makkelijk uitgekomen, maar het probleem zit 'em in dat ik niet weet hoe ik een specifieke cel in de 'score per club' tab kan laten zoeken in de 'speelronde' tab naar de juiste club en deze dan automatisch laat checken of de club gewonnen, verloren of gelijk gespeeld heeft.

Hoe jij het voorstelt met die matrix kan ook nog er bij inderdaad. Dat er eerst een overzicht gegeven wordt van alle mogelijk behaalde punten van de club. En daarna in een andere matrix per deelnemer wordt weergegeven hoeveel punten hij of zij daarmee verzameld heeft.

[ Voor 24% gewijzigd door Verwijderd op 19-07-2016 14:55 ]

[goldcard]

Ik heb jouw 'speler vs club' matrix even in dezelfde tab gebouwd, en doe dan het volgende (ik bouw de formule stapsgewijs op):

De clubs staat in range E31 .. V31
De spelers staan range D32 .. D37

1. Zoek naar het wedstrijdnummer waarin de club in die ronde speelt met:
   
   IF(ISNA(MATCH(E$31;$F$4:$F$12;0));MATCH(E$31;$H$4:$H$12;0);MATCH(E$31;$F$4:$F$12;0))
2. [br][br][br]
3. Zoek de voorspelling van de speler - gebruikmakend van het opgezochte wedstrijdnummer - met: HLOOKUP($D32;$D$14:$Z$23;IF(ISNA(MATCH(E$31;$F$4:$F$12;0));MATCH(E$31;$H$4:$H$12;0);MATCH(E$31;$F$4:$F$12;0))+1;FALSE)
4. [br][br][br]
5. Zoek het resultaat van de wedstrijd - ook gebruikmakend van het opgezochte wedstrijdnummer - met:
   
   HLOOKUP("Uitslag";$M$3:$M$12;IF(ISNA(MATCH(E$31;$F$4:$F$12;0));MATCH(E$31;$H$4:$H$12;0);MATCH(E$31;$F$4:$F$12;0))+1;FALSE)
6. [br][br][br]
7. Uiteindelijke formule: vergelijk de voorspelling met de uitslag en geef een 1 wanneer het goed is, en een 0 wanneer het niet goed is met:
   
   =IF(HLOOKUP($D32;$D$14:$Z$23;IF(ISNA(MATCH(E$31;$F$4:$F$12;0));MATCH(E$31;$H$4:$H$12;0);MATCH(E$31;$F$4:$F$12;0))+1;FALSE)=HLOOKUP("Uitslag";$M$3:$M$12;IF(ISNA(MATCH(E$31;$F$4:$F$12;0));MATCH(E$31;$H$4:$H$12;0);MATCH(E$31;$F$4:$F$12;0))+1;FALSE);1;0)

De uitkomst is een matrix voor die betreffende speelronde.

[Verwijderd]

Super, hier kan ik wel wat mee. Heel erg bedankt.

Nog één vraagje nu we toch bezig zijn. Nu wordt er een 0 of een 1 weergegeven, maar ik zou ook graag een versie willen waarbij het gewonnen aantal punten (N4:N12) weergegeven wordt. Dan kunnen de spelers zien welke clubs ze het hoogst mee scoren.

Dit zou volgens mij een kleine aanpassing van jouw formule moeten zijn, maar ik kom er nog niet echt uit.

[F_J_K]

Verwijderd schreef op dinsdag 19 juli 2016 @ 16:36:
maar ik kom er nog niet echt uit.

Waar loop je precies vast, wat is je huidige formule?

Nu lijkt het er een beetje op dat je het volledig wilt hebben voorgekauwd

In het algemeen: met HLOOKUP (of de NL variant) kan je een kolomindex meegeven voor de kolom waar je de gegevens van wilt gebruiken.

goldcard schreef op dinsdag 19 juli 2016 @ 16:44:
HLOOKUP vraagt om de row_index (aantal rijen naar beneden/boven), VLOOKUP om de column_index (aantal kolommen naar rechts/links)

edit:
Kolom <--> rij, oeps Reden te meer om eerst de code zelf te willen snappen: mensen die meedenken kunnen domme fouten maken

[ Voor 31% gewijzigd door F_J_K op 19-07-2016 18:11 ]

[goldcard]

Hier stond een uitgekauwd antwoord
Ok je moet nog even zelf puzzelen lees ik net , maar je bent er bijna. Kijk even naar de oplossing voor het zoeken van de uitslag en gebruik een soortgelijke opbouw

[goldcard]

F_J_K schreef op dinsdag 19 juli 2016 @ 16:39:
[...]

In het algemeen: met HLOOKUP (of de NL variant) kan je een kolomindex meegeven voor de kolom waar je de gegevens van wilt gebruiken.

HLOOKUP vraagt om de row_index (aantal rijen naar beneden/boven), VLOOKUP om de column_index (aantal kolommen naar rechts/links)

[Verwijderd]

Oke oke laat mij inderdaad maar beetje zwoegen, dan snap ik straks ook daadwerkelijk waarom de formule werkt zoals die werkt.

Wat ik heb geprobeerd is om de 'value if true' waarde van 1 te veranderen. Lijkt mij immers dat dat het enige is dat aangepast hoeft te worden.

Uiteindelijk kwam ik uit op MATCH(E$31;$N$4:$N$12)

N kolom is immers de kolom waar de quoteringen staan, maar ik krijg hier #N/A

Vervolgens probeerde ik HLOOKUP("Quotering";$N$3:$N$12;0) maar dat geeft #VALUE.

Het probleem waar ik het meest tegen aanloop is dat ik niet precies weet wat ISNA doet dus ik zoek het op en vind dat je daarmee 2 lijsten in excel met elkaar vergelijkt. Ik verwacht dat ik hier nog iets mee moet in de formule mbt welke waarde er bij een juiste constatering van de formule moet worden gegeven.

Mag ik nog een hint aanvragen? haha

Got it! Gewoon even de waarden verschuiven natuurlijk van uitslag naar quotering

Many thanks!

[ Voor 6% gewijzigd door Verwijderd op 19-07-2016 18:14 ]

[F_J_K]

Mag ik nog een hint aanvragen? haha

Extra hulplijn?

N/A = not available. Zeg maar een foutmelding. Met ISNA() kan je daar op checken: als niets gevonden dan (iets anders doen)..

E31 is leeg, maar als ik aanneem dat er een clubnaam staat, dan zoek je clubnamen in kolom N: die zijn er niet en dus krijg je N/A. Als je de waarde wilt hebben dan kan je INDEX() gebruiken met als vulling het resultaat van zoeken op de club in de eerdere kolom.

Iets dergelijks doe je bij je HLOOKUP: ten eerste zoek je op het woord "Quotering". Ten tweede vraag je door de ;0 de 'nulde' rij en die bestaat niet Probeer maar eens ipv ;0 bijv. ;1 of ;2.


Edit: nevermind, zie boven \o/

[ Voor 3% gewijzigd door F_J_K op 19-07-2016 18:29 ]