Pitkäveto 1 x 2 ennuste Excelissä

Pitkäveto 1 x 2 ennuste Excelissä

Toissapäivänä en olisi uskonut tekeväni Excelillä mitään jalkapalloon, pitkävetoon, veikkaamiseen ja vedonlyöntiin liittyvää. Verenkierto lakkaa nimittäin aivoissani sillä hetkellä, kun keskustelu siirtyy veikkaamiseen, vedonlyöntiin, kertoimiin, todennäköisyyksiin tai salakieleen 1 x 2 …

… kunnes eräs HExcelligentin Facebook-tykkääjä otti yhteyttä ja kohteliaan sitkeästi sekä pyysi että painosti tekemään pitkävetoa varten todennäköisyystaulukon.

Olen helposti yllytettävissä ja koska mukavuusalueen ulkopuolinen elämä on nyt muotia, aloitin lukemalla Wikipedian Urheiluvedonlyönti-artikkelin. Hetken hengitettyäni jatkoin Sportsbetting –sivuille, mistä löysin aiheeseen liittyvän Poissonin jakaumaa ennustamisessa hyödyntävän ohjeen.

Lopulta otin itseäni sekä hiirtä niskasta kiinni ja toteutin ennustetyökirjan kyseisiä ohjeita röyhkeästi lunttaamalla. (Mielestäni löysin ohjeista myös virheen: ohjesivun kolmanteen kuvaan on laskettu Average Goals For kaavalla Games Played / Goals For, kun se kaiketi pitäisi laskea Goals For / Games Played?)

Lataa työkirja ja muokkaa siitä oma ennustemallisi: pitkäveto 1×2 ennustemalli.xlsx

[EDIT 1.3.2015 Laadin työkirjasta uuden version, johon voit syöttää ehdon, mistä syöttämästäsi kaudesta lähtien tai miltä kaudelta ottelut huomioidaan ennusteessa: pitkäveto 1×2 ennustemalli versio 2.xlsx. Uudessa versiossa on käytetty kaavoja, jotka toimivat vain Excel 2007 ja sitä uudemmissa versioissa.]

[EDIT 20.6.2017: Ja tänään perehdyin erään lukijan toiveesta myös sarjataulukon saloihin ja tein pitkäveto-esimerkkityökirjastani jo kolmannen version, joka laskee myös kaikki sarjataulukon luvut joukkueittain pitkäveto-1×2-ennustemalli-versio-3.xlsx]

Mallia ei ole suojattu, joten varo tuhoamasta kaavoja. Malli on toteutettu perinteisesti Excelillä (ilman Power-työkaluja), joten se sisältää VLOOKUP (PHAKU) -hakufunktioita, jotka poimivat arvoja sarakenumeroiden perusteella. Jos lisäät luetteloihin uusia sarakkeita, joudut myös muokkaamaan hakufunktioita.

Olen käyttänyt mallissa VLOOKUP (PHAKU) -funktiota lähinnä sen tunnettuuden ja helppouden takia. Ylläpidon kannalta parempi ratkaisu olisi INDEX (INDEKSI) ja MATCH (VASTINE) -funktioiden yhdistelmä, mutta näiden funktioiden yhdistelmä olisi vaikeampi ymmärtää. Lisätietoja: VLOOKUP vai INDEX ja MATCH?

Toimintaidea lyhyesti

Löydät lisäinfoa työkirjasta, mutta tässä lyhyt kuvaus toimintaperiaatteesta.

  1. Syötä pelatut ottelut Kaikki matsit -taulukkoon. Jokainen malliin syötetty ottelu vaikuttaa todennäköisyyksien laskemiseen, joten poista mallista turhat eli liian vanhat ottelut.Matsit
  2. Syötä/kopioi myös Maalit-taulukon Kotipelit– ja Vieraspelit -luetteloihin joukkueiden nimet. Työkirja laskee luetteloihin joukon tuloksia (mm. tehdyt kotimaalit, hävityt kotimaalit, tehdyt vierasmaalit, hävityt vierasmaalit).maalit2
  3. Tämän jälkeen työkirja laskee Maalit-taulukoiden perusteella muutamia voimalukuja, kuten kotihyökkäysvoima, kotipuolustusvoima, jne. Huolehdi, että Voimaluvut-taulukon ensimmäisessä sarakkeessa on riittävän pitkälle kaavoja, jotta kaikki joukkueet linkittyvät luetteloon edellisestä taulukosta.
  4. Viimeisessä taulukossa valitset sekä koti- että vierasjoukkueen. Malli laskee sekä koti- että vierasjoukkueen odotusarvot, 1 x 2 todennäköisyydet sekä kertoimet ja näyttää visuaalisesti mikä on todennäköisin tulos (tässä tapauksessa 2 – 0 kotijoukkueen eduksi). Tämän taulukon kaavoissa käytetään POISSON-funktiota.todennäköisyydet

 

Jos ryhdyt käyttämään mallia, suosittelen tutkimaan ja validoimaan mallin kaavat ennen kuin otat sen käyttöösi. Muistathan, etten vielä toissapäivänä tiennyt 1×2 pitkävedosta ja siihen liittyvistä ennustemenetelmistä yhtikäs mitään. Arvostan suuresti, jos kommentoit juttuani, mikäli löydät mallista virheitä.

Suosittelen myös lukemaan Sportsbetting-sivun ohjeen kokonaisuudessaan. Alla ote:

  • The model uses past data to predict future results. The accuracy of this method is open to debate. Does something that happened 6 months ago with different players in different weather conditions really help us understand what will happen?
  • In this scenario, the model is also based on last season’s information – players and managers come and go, so the Manchester United under David Moyes could be very different to the Manchester United under Louis van Gaal. Similarly, will Liverpool be as free-scoring without the talents of Luis Suarez? You may wish to wait for a few games to have been played in the new season before betting to ensure that things are in line with your expectations.
  • The only real factor that this approach takes into account is the result. We’ve all seen plenty of games where a team dominated a match but only won 1-0. Or even the odd situation where the dominant team lost the match via a goal on the counter attack. Match results tell us the final score, but do not tell us what actually happened during the game.
  • The model is objective, which means it does not take into account other factors. However, as we know, a lot of things can affect a game, both before and during. A model such as this does not take into account things like injuries, suspensions, fatigue or weather which could affect the predictions prior to the game. Similarly, it is believed that goal expectation is affected by factors that happen in the game, such as an away goal or a red card.
  • It is also believed the probability of draws and the probability of zero is underestimated when using Poisson Distribution to predict football games. This can however be rectified by using a method known as zero-inflation to increase the probability of no goals.

Taidanpa ryhtyä veikkaamaan?

PS. Jos ryhdyt keräämään pelidataa Exceliin systemaattisesti, sinun kannattaa tutustua Power Queryyn, jolla voi imuroida tietoa Exceliin erilaisista tietolähteistä. Tosin monet kaupalliset veikkaukseen ja vedonlyöntiin liittyvät sivustot ovat toteuttaneet sivunsa ohjelmallisesti siten, että sivuilla näkyviin taulukoihin ei pääse käsiksi Power Queryllä. Esimerkkityökirjaani latasin tiedot Wikipediasta 2013 – 14 Premier League -sivulta.
Löydät muita HExcelligent-työkaluja täältä.

52 kommenttia artikkeliin ”Pitkäveto 1 x 2 ennuste Excelissä

  1. Aivan mahtavaa! Olin tehnyt jo itselleni Exceliin tiedoston, joka laskee maalikeskiarvot ja sitä jalostaakseni etsin tietoa miten laskea koti- ja vieraspeleistä prosentit voitoille, tasureille ja tappioille. Tämähän on jo valmiiksi jalostettu versio 🙂

    Lisää dataa aiemmilta vuosilta vain kehiin.

    Tykkää

    • Mainiota Janne, että ennustemallille löytyy käyttöä! Tarkistin WordPressin statistiikasta, että työkirja on ladattu jo yli 100 kertaa eikä kukaan ole vielä raportoinut virheistä. Kaiketi toimii, mutta kerrothan, jos huomaat ongelmia.

      Tykkää

  2. Hienoa, että joku osaava on tällaisenkin tehnyt! Harmi vaan, että minulla jostain syystä ei toimi tuo taulukko. Tuossa Kaikki matsit -taulukossa kotimaalien ja vierasmaalien kohdalla lukee vaan Virhe:508. Sama Virhe:508 lukee sitten muissakin taulukoissa. Lisäksi näissä ei näy noita nuolinäppäimiä (alasvetovalikko vai mikä tuo nyt on nimeltään). Luultavasti vika on täällä päässä, kun en mikään excel -guru ole..

    Tykkää

    • Tarkennatko vielä, millä Excel-versiolla avasit työkirjan? ”Virhe:508” ei nimittäin vaikuta Excelille tyypilliseltä ilmoitukselta. Myös se, että alasvetovalikoita ei näy, kuulostaa omituiselta. Esim. Mac-versio on melkeinpä ihan eri tuote kuin pc-versio.

      Kotimaalit ja vierasmaalit sarakkeisiin voi myös itse syöttää maalien määrät sen sijaan, että syöttää Tulos-sarakkeeseen tiedot ”1-3” muodossa.

      Tykkää

  3. Hei ja kiitoksia hienosta työkalusta. Ensimmäiselle välilehdelle olet kirjoittanut, että kannattaa merkitä myös kausi (esim. 2014/15), jotta vanhat tulokset voi suodattaa tarvittaessa pois. Tavallinen suodattaminen kyllä piilottaa halutut rivit, mutta seuraavilla välilehdillä olevat laskentakaavat ottavat silti piilossa olevat tulokset huomioon. Eli tuo rajaus onnistuu?

    Tykkää

    • Kiitti Antti, hyvä huomio. Olen käyttänyt epähuomiossa sanaa suodattaa, kun olen tarkoittanut, että kauden perusteella voi karsia vanhoja tietoja eli käytännössä poistaa niitä. Suodattaminen ei tosiaan vaikuta kaavoihin.

      Tykkää

  4. No nyt kun palasin lukemaan tekstin uudelleen, niin siinä kyllä lukee, että ”…voit helposti poistaa mallista sen perusteella ennusteen kannalta liian vanhoja otteluita.”. Eli oikein olet jättänyt puhumatta suodattamisesta.

    Mutta se olisi kyllä kätevä, jos kaavat ymmärtäisivät jättää pois piilossa olevat tulokset. Pikaisella haulla interneetistä löytyi sellainen funktio kuin VÄLISUMMA. Siinä ensimmäiset 1-11 argumentit huomioivat normaaliin tapaan suodatetut arvot. 101-111 taas osaa jättää piilossa olevat laskematta. Nyt kun vielä saisi taiteiltua tuon seuraavien välilehtien kaavoihin, niin hyvä tulee!

    Tykkää

    • Jep, välisumma (subtotal) -funktio osaa laskea muutamia tilastollisia tuloksia huomioiden vain näkyvillä olevat luvut. Välisumma ei puolestaan ymmärrä sellaista ehdollisuutta, että se laskisi tietyn joukkueen tulokset. Valitettavasti välisummasta ei ole versiota, jolla voisi korvata seuraavilla välilehdillä käytetyt ehdolliset laske.jos (countif) ja summa.jos (sumif) -funktiot.

      Mutta laske.jos ja summa.jos -funktiot voi kyllä korvata funktioilla Laske.jos.joukko (countifs) ja summa.jos.joukko (sumifs), joille voi antaa useita ehtoja. Yhtenä ehtona voisi olla kausi, jolta tulokset halutaan laskea. Ja jos kauden on syöttänyt Excelin ymmärtämänä päivämääränä, funktio ymmärtäisi myös ”suurempi tai yhtä suuri kuin” -tyyppisen ehdon.

      Pitääkin tehdä mallista uudempi versio, missä käytetään edellä mainittuja funktioita!

      Nämä mainitsemani funktiot toimivat vain Excel 2007 ja sitä uudemmissa versioissa.

      Tykkää

  5. Hei
    Nuo taulukon voimaluvut ovat mielestä hieman epäloogisia. Mitä enemmän maaleja päästää suhteessa ottelumäärään sitä korkeampi on hävittyjen otteluiden keskiarvo. Loogisempaa olisi kääntää jakolasku toisin päin, niin paljon maaleja päästävillä joukkueilla pienenisi tuo keskiarvo ja se olisi suoraan vertailukelpoinen tehtyjen maalien kanssa?

    Tykkää

  6. Mielenkiintoinen taulukko. Tallensin ja aloin päivittää tätä kaudelle 2016-2017. Valioliigassa 5 joukkuetta oli tällä välin vaihtunut. Vaihdoinkin Aston Villan, Cardiff cityn, Fulhamin, New Castlen ja Norwichin tilalle: nykyiset: Bournemouth, Watford, Burnley, Leicester, Middlesbrough. (listasin jos joku tekee saman). Nyt voin vain syöttää oikeat tulokset oikeisiin kohtiin. Koska kaikkia taulukossa olevia vastaavia otteluita ei kuitenkaan tällä kaudella ole vielä ehditty pelata,

    Haluaisin tietää että miten:
    a) joko poistan pelin excelistä niin ettei se sotke taulukkoja, tai b) poistan vain tuloksen mutta jätän pelirivin paikan taulukkoon koska ennen pitkää siinä olevat joukkueet tulevat kohtaamaan. Jos vain deletoin tuloksen tulee #value -valitus joka välilehdelle.

    Tykkää

    • Hei Janne,

      Taulukoista voi poistaa pelejä ja lisätä sitä mukaa kun niitä tarvitaan. Valitse taulukon sisältä niin monelta riviltä soluja kuin haluat poistaa rivejä ja valitse pikavalikosta Delete > Delete Table Rows.

      Kun kirjoitat taulukon alapuoliselle tyhjälle riville uuden pelin, taulukon koko kasvaa automaattisesti ja kaikki kaavat kopioituvat myös uudelle riville.

      Tykkää

  7. Miten voin suurentaa (Todennäköisyydet) Kaaviota? Esim. Jos teen vaikka NBA tai NFL exelin on maalimäärät/pistemäärät huomattavasti isommat kuin jääkiekossa tai jalkapallossa. Ja johtuuko siitä että prosentit eivät näy oikein? Tässä näät kuvasta mitä tarkoitan http://imgur.com/a/LAkPE

    Tykkää

    • Moi,

      Voit lisätä esimerkiksi luvut 11-20 sekä vaaka- että pystysuuntaisesti otsikoiksi ja kopioida matriisissa olevaa kaavaa kaikkiin uusiin soluihin esim. copy-paste -komennolla tai valittuna olevan kaavasolun oikeassa alakulmassa olevasta neliöstä vetämällä. Samalla kopioituu myös alueella oleva ehdollinen muotoilu, joka näyttää solut eri sävyillä.

      Koko matriisissa on siis vain yksi kaava, jota on käytetty kaikissa soluissa.

      Sen lisäksi sinun pitää muokata solujen L3, L4 ja L5 summakaavoja. Niiden logiikka selviää sinulle, kun kaksoisnapsautat kutakin solua L3, L4 ja L5, jotta näet visuaalisesti mistä soluista summat lasketaan. L3, L4 ja L5 solujen tulostenhan pitää olla aina 100 %, joten kaavojen oikeellisuus on helppo testata.

      t. Heidi

      Tykkää

  8. Moi, latasin Excelin v2 ja korvasin Kaikki matsit -taulukkoon Liigan 2016-17 pelatut pelit. Koti ja vierasmaalit erittelevä funktio toimii oikein, mutta Maalit-taulukkoon ei tapahdu mitään laskutoimituksia -> Kaikki sarakkeet näyttävät nollaa. Joukkueet olen tänne korvannut ohjeen mukaisesti identtisessä kirjoitusasussa kuin ottelulistauksessakin on. Kaikki matsit -taulukossa korvasin mallina olleet datat Liitä -> Arvot toiminnolla.

    Ilmeisesti tässä liitä-toiminnossa tapahtuu jotain, koska jos korvaan mallina ollutta otteludataa ihan käsin kirjoittamalla niin Maalit-taulukon laskutoimitukset pysyvät hengissä. Mitähän voisin onnistua sössimään? Excel 2016 ja Windows 10 käytössä.

    Tykkää

    • Vaikea arvioida kuvailun perusteella. Maalit-taulukko edellyttää, että joukkueet on kirjoitettu täsmälleen samalla tavalla kuin miten ne esiintyvät Kaikki matsit -taulukossa, Voiko jommassa kummassa olla esimerkiksi ylimääräisiä välilyöntejä, jolloin ne eivät ole samanlaisia?

      t. Heidi

      Tykkää

    • Taulukon yläpuoliseen soluun c5 pitää syöttää erotinmerkki. Jos ei vieläkään toimi, epäilen että kopioimissasi tiedoissa on myös jotain muita merkkejä kuin pelkät luvut ja erotin. Jos kopioit tietoja nettisivuilta, mukana voi tulla erilaisia tulostumattomia ohjausmerkkejä, jotka pitää siivota soluista pois. Huomaa, että myös viivoja on olemassa erilaisia. Eli vaikka näyttävät silmämääräisesti samoilta eivät ehkä olekaan.

      Tykkää

  9. Kun käytän viivaa niin se kyllä toimii mutta kun kaksoispisteen niin ei toimi… eli haen sitä että voisi kopsata tulokset suoraan niin ettei tarvitsisi jokaista ottelua mekkailla käsin manuaalisesti näppäillen..hmm..

    Tykkää

    • Netistä kopioitaessa nuo välilyönnin näköiset eivät välttämättä ole tavallisia välilyöntejä. Ehkä parempi ettet kirjoita käsin korvattavaa merkkijonoa, vaan kopioit ja liität merkit ikkunaan.

      Tykkää

  10. Hei! Olen tykännyt tästä Excel-tiedostosta, mutta haluaisin kehittää sitä jääkiekkoon paremmin mukautuvaksi. Maalien lisäksi tulisi ennusteita laskiessa ottaa huomioon maalipaikat, laukaukset ja maalivahdin onnistuminen yms. Tämä onnistuisi näillä pienillä lisäyksillä:

    -Corsi
    -Fenwick
    -PDO

    http://www.jatkoaika.com/Artikkeli/corsi-ja-fenwick-%E2%88%92-nhln-keskeisimm%C3%A4t-tilastoanalyysit-p%C3%A4hkin%C3%A4nkuoressa/156240

    Miten nämä saisi Exceliin? Voisiko sellainen toimia, että jokaisesta pelistä syötetään maalit, corsi, fenwick ja pdo, mutta pienenä lisänä todennäköisyyslaskentaan tuotaisi nämä kolme uutta muuttujaa, jotka voisivat esimerkiksi toimia tietynlaisina kertoimina:

    Joukkue A voittaa ottelun 6-2, mutta pelaa huonosti (fenwickin, corsin ja pdo:n mukaan). Joukkue B siis häviää ottelun, mutta muut arvot ovat hyviä. Fenwick, corsi ja pdo toimisivat tällaisen rökäletappion tasapainottajina, koska tulos vääristää tulevia ennusteita. Jos nämä kolme muuttujaa saisi lisättyä funktioihin ikään kuin kertoimina, jolloin tulevissa ennusteissa huomioitaisi äskeinen ottelu esimerkiksi 5-3 tuloksena.

    Omat Excel-taitoni ovat erittäin rajoitetut, mutta minkälaisia ideoita ehdottaisit? Mistä lähtisit muokkaamaan nykyistä versiota?

    Tykkää

    • Huh, huh. Nyt meni kyllä vaikeaksi. Minulla ei ole riittävästi todennäköisyyslaskennan osaamista, jotta pystyisin auttamaan eteenpäin. Aluksi kannattaa ryhtyä syöttämään tarvittavia tietoja, jotta myöhemmin niitä voi hyödyntää, jos löydät jonkun joka pystyisi auttamaan eteenpäin.

      T. Heidi

      Tykkää

  11. Moikka. Tämä on aivan mahtava taulukko ja olenkin tätä nyt itselle säätänyt Serie A:n ennustamiseen. Yksi asia mikä olisi hieno saada tähän ja jota olen ihmetellyt, on että mitenkä tuohon esimerkiksi kaikki matsit-välilehdelle olisi mahdollista saada sarjataulukko kyseisistä peleistä sinä vuotena. Minulla on tuossa nyt vuodet 14-17 ja ensi kautta ajatellen olisi hieno, jos sarjataulukon näkisi myös suoraan excelissä. Ainahan sen näkee muista lähteistä mutta silloin kaikki informaatio olisi yhdessä paikassa, josta sen näkisi nopeasti. Pivotilla tuota jotenkin pitäisi rakentaa, mutta on täysin outo toiminto minulle eikä kyllä aukeakaan minulle.

    Tykkää

    • Moi,

      Tuo sun kysymyksesi osoittautui sen verran haastavaksi pähkinäksi, että päätin tehdä siitä esimerkin heti aamuverryttelyksi. 🙂

      Pivoteilla sarjataulukkoa ei saa tehtyä. Laskennassa pitää käyttää jopa matriisikaavoja, jotka ovat melko haastavia ymmärtää.

      Lisäksi minun piti tarkistaa mitä sarjataulukoissa yleensä on. 🙂 Viitsisitkö tarkistaa, onko tuohon oheiseen tiedostoon laatimassani sarjataulukossa kaikki tarpeellinen. Jos on, voisin julkaista sen uutena versiona blogiini.

      pitkäveto-1×2-ennustemalli-versio-3.xlsx

      Terveisin,
      Heidi

      Tykkää

      • Olipa se nopeaa toimintaa. Hyvältä näyttää! Nopeasti testaamalla tuloksien muuttaminen muuttaa sarjataulukonkin oikeaksi. Vautsi! 🙂

        Tykkää

        • Nyt itseasiassa kun päivitin omat tiedot sarjataulukkoon, niin sarjataulukosta ei toimi voitot, häviöt, tasapelit eikä yhteispisteet. Maalimäärät ja maaliero toimii, sekä pelatut pelit. Muissa lukee #ARVO!

          Liked by 1 henkilö

  12. Kun teit kaavoja, painoitko Ctrl + Shift + Enteriä kaavan hyväksymiseen? Osa kaavoista oli matriisikaavoja ja ne hyväksytään kyseisellä näppäinyhdistelmällä. Tavalliset kaavat hyväksytään tietenkin Enterillä.

    Tykkää

    • Toin myös testiksi muutaman kierroksen 2015 vuoden Serie A:ta suoraan tuohon sinun taulukkoon, kaikki muu toimii jälleen mutta nuo voitto/tappio… jne ei toimi. Ei vaikutusta ctrl+shift+enterillä.

      Tykkää

        • Muillekin uteliaille tiedoksi, että ongelma ratkesi. Jos Kaikki matsit -taulukkoon jää sellaisia tyhjiä ottelurivejä, joissa kaavat antavat virheilmoituksia, myös sarjataulukon kaavojen tulokset muuttuvat virheilmoituksiksi. Yleensä matriisikaavat eivät salli virheilmoituksia niissä matriiseissa, joihin kaavoista viitataan.

          Tykkää

    • Sun pitäisi tyhjentää siinä ensimmäisessä Kaikki matsit -laskentataulukossa olevat tulokset (jätä kaavat) ja ryhtyä syöttämään uusia. Poista ensimmäisestä taulukosta turhat tyhjäksi jääneet rivit. Lisäksi joukkueet pitää kirjoittaa Maalit-laskentataulukossa oleviin luetteloihin samassa muodossa kuin ne esiintyvät Kaikki matsit -taulukossa.

      Tykkää

Vastaa

Täytä tietosi alle tai klikkaa kuvaketta kirjautuaksesi sisään:

WordPress.com-logo

Olet kommentoimassa WordPress.com -tilin nimissä. Log Out / Muuta )

Twitter-kuva

Olet kommentoimassa Twitter -tilin nimissä. Log Out / Muuta )

Facebook-kuva

Olet kommentoimassa Facebook -tilin nimissä. Log Out / Muuta )

Google+ photo

Olet kommentoimassa Google+ -tilin nimissä. Log Out / Muuta )

Muodostetaan yhteyttä palveluun %s