Labdarúgó EB

Feladat

Egy labdarúgó EB-n 16 csapat indul. Kezdetben négy 4-es csoportba vannak beosztva, ahol mindenki mindenkivel játszik, és a csoport első két helyezettje jut tovább. Ezután a 8 továbbjutó egyenes kiesése rendszerben játszik tovább.
Készítsünk táblázatot, amibe csak a mérkőzések eredményét kell begépelni "3:1" alakban, és kiszámítja a csoportok eredményét, meghatározza a továbbjutókat, és végigköveti a kieséses szakaszt is.

Adatok

A 2000-es EB a Wikipédián

Lépések

Eredmény karakterlánc felbontása

Azt szeretnénk, ha a meccsek eredményét egyetlen cellába lehetne begépelni, például "6:3". Ehhez a cella formátumát szövegre kell állítani, hogy ne akarja a táblázatkezelő dátummá konvertálni. Az eredmények feldolgozásakor viszont a "6:3" szöveget fel kell bontani a 6 és 3 számokra.

A lépések a következők:
  • Megkeressük a szövegben a ":" karaktert. (SEARCH / SZÖVEG.KERES)
  • A ":" előtti részt számmá alakítva kapjuk az első csapat góljainak számát. (LEFT / BAL, DECIMAL)
  • A ":" mögötti részt számmá alakítva kapjuk a második csapat góljainak számát. (RIGHT / JOBB, LEN / HOSSZ, DECIMAL)
  • Ha az A1 cellában van az eredményt leíró karakterlánc, akkor a következő képletekkel dolgozhatunk:
  • csapat1_góljai = DECIMAL(BAL(A1;SZÖVEG.KERES(":";A1)-1);10)
  • csapat2_góljai = DECIMAL(JOBB(A1;HOSSZ(A1)-SZÖVEG.KERES(":";A1));10)

Csapatok pontozása

A csoportmérkőzések után ki kell számítanunk a csapatok pontszámát, majd ki kell választanunk csoportonként a két legjobbat, akik továbbjutnak az egyenes kieséses szakaszba.
A pontszám meghatározása a következő: a győzelem 3 pontot ér, a döntetlen 1 pontot, a vereségért nem jár pont.


Részletek:
A képen látható sárga mezőkbe szabad gépelni, a többi cella értékét függvényekkel állítjuk elő.

E1-es cella:
                =B2

Az eredmény-mátrix cellái "szöveg" formátumúak. A későbbiek miatt célszerű a még nem lejátszott meccsek celláját is jelölni, például egy "-" karakterrel.

F2-es cella:
                 =HA(B7<>"";B7;"-")

Az eredmény-mátrixot soronként értékeljük ki, minden mérkőzés kétszer jelenik meg, de mindig annak a csapatnak a "szemszögéből", akinek a sorába van írva. Tehát mondjuk a felső háromszögbe másoljuk át a végeredményeket, az alsó háromszögben pedig képlettel megfordítjuk a gólok számának sorrendjét.

E4-es cella:
                 =HA(G2<>"-";
                    ÖSSZEFŰZ(
                        JOBB(G2;HOSSZ(G2)-SZÖVEG.KERES(":";G2));
                        ":";
                        BAL(G2;SZÖVEG.KERES(":";G2)-1)
                    );
                 "-")

A már ismert függvénnyel kiszámíthatjuk csapatonként (soronként) a rúgott gólok számát egy segédtáblázatban.
A gólok táblázata alapján az is meghatározható (egy másik segédtáblázatban), hogy melyik csapat, melyik meccsen hány pontot szerzett.

G8-as cella:
                =HA(G2<>"-";HA(M8>K10;3;HA(M8=K10;1;0));"")


Minden csapatra megadjuk a lejátszott mérkőzések számát (M), a győzelmek (Gy), döntetlenek (D) és vereségek (V) számát, illetve az összes rúgott és kapott gólt. Itt már használhatjuk a segédtáblázatokat.
Kiszámítjuk a csapatok pontszámát, egyszerű összegzéssel.

Pontegyenlőség kezelése

Előfordulhat, hogy két csapat azonos pontszámmal zárja a csoportmérkőzéseket, és ilyen esetben is rendeznünk kell őket.


Egy lehetséges döntési mód: pontegyenlőség esetén a nagyobb gólkülönbség dönt, és ha az is egyenlő, akkor pénzfeldobás. Egyszerűsíti a folytatást, ha mindezt egy pontszámba (pont*) foglaljuk bele, a következő módon:

pont* = 1000*P+10*(Rg-Kg)+pénzfeldobás

A szorzók garantálják, hogy a "magasabb szempontot" ne írja felül az "alacsonyabb". Feltettük, hogy a gólkülönbség 100-nál kisebb, így a pont 1000-es szorzója garantálja, hogy egyetlen ponttöbblet többet ér, mint 99-cel jobb gólkülönbség. A pénzfeldobásokat "előre lejátszhatjuk", ami azzal egyenértékű, hogy a 4 csapatnak négy különböző 1 és 9 közé eső számot generálunk. Ez csak akkor fog számítani ha a pontszám és a gólkülönbség is egyezik.

Továbbjutók meghatározása

Ha már minden csapathoz sikerült úgy értéket rendelni, hogy ezek az értékek biztosan különbözők, és a szabályok szerint jobb csapathoz nagyobb számot rendeltünk, akkor ki kell választani csoportonként a két továbbjutót.
Ehhez az INDEX, MATCH / HOL.VAN, MAX és LARGE / NAGY függvényeket használhatjuk.


A képletek:

B7-es (összevont) cella: =INDEX(A2:A5;HOL.VAN(MAX(H2:H5);H2:H5;0))
B8-as (összevont) cella: =INDEX(A2:A5;HOL.VAN(NAGY(H2:H5;2);H2:H5;0))

A kieséses szakasz

Itt már nem lehet döntetlen, az esetleges tizenegyesrúgás eredményét is hozzáadjuk a végeredményhez. Mivel minden mérkőzésnek van győztese, egyszerűen meghatározhatók a továbbjutók:


Az ábrán az A oszlopban láthatók a csoportkörből továbbjutó csapatok. Ezeket a cellákat is képlettel kell kitölteni, az előző ábrán látott módon.

A munkalap védelme

Ha a munkalapot "alkalmazásnak" szeretnénk tekinteni, biztosítanunk kell, hogy az adatokat feltöltő ember nem tudja véletlenül / figyelmetlenségből törölni a képleteinket. Ehhez védetté kell tenni a munkalapot, majd csak azon cellákra kell írási jogot adni, amelyekbe a "6:3" formátumú eredményeket írjuk be.