EURO 2016 w świetle Excela – nie tylko dla finansistów
Jako że jesteśmy w trakcie mistrzostw Europy w piłce nożnej, proponujemy dziś nie lada gratkę dla wszystkich kibiców-finansistów i nie tylko – zajmiemy się najważniejszą z rzeczy mniej ważnych, czyli piłką nożną właśnie, a dokładniej połączeniem jej z zacięciem excelowym.
Oto co powstało z tego właśnie połączenia – przedstawiamy narzędzie służące do samodzielnego prognozowania, symulacji, tudzież uzupełniania wyników poszczególnych meczy aż do samego finału.
Samo narzędzie wygląda tak, jak na rysunku 1.
Jedną z opcji jest umożliwienie samemu Excelowi typowania wyników za pomocą formuły =LOS(), którą należy wpisać do każdego meczu, a dokładniej np. =LOS()*5, chcąc uniknąć niskobramkowych rezultatów, jako że formuła LOS zwraca nam losowe wyniki z przedziału 0-1. Oczywiście jeśli wyniki nas nie zadowolą, możemy kazać mu typować jeszcze raz, poprzez odświeżenie pliku klawiszem F9.
W jednej z takich symulacji, nasza drużyna osiąga nawet finał, co prawda przegrany z drużyną Hiszpanii, ale myślę, że i tak każdy wziąłby taki scenariusz w ciemno (rysunek 2).
A teraz trochę „kuchni” – na przykładzie jednej z grup zobaczymy jak taki plik jest skonstruowany. Do każdego wyniku dopisywany jest rezultat wg oznaczeń bukmacherskich, gdzie 1 odpowiada za zwycięstwo gospodarza, w tym przypadku nominalnego rzecz jasna, X – to remis, 2 – zwycięstwo drużyny rozstawionej jako gość (kolumna N na rysunku 3).
Formuła zbudowana na te potrzeby, to:
JEŻELI(LUB(I3="";K3="");"";JEŻELI(I3>K3;1;JEŻELI(I3=K3;"X";JEŻELI(I3<K3;2;""))))
Następnie dochodzimy do wyliczenia punktów, ilości zwycięstw, remisów i porażek oraz bramek strzelonych i straconych.
Punkty wyliczane są standardowo – 3 pkt. za zwycięstwo i 1 za remis (rysunek 4).
Ilość zwycięstw, remisów i porażek wyliczana jest przy użyciu formuł LICZ.WARUNKI (rysunek 5):
Z: =LICZ.WARUNKI($H:$H;$Q4;$N:$N;1)+LICZ.WARUNKI($L:$L;$Q4;$N:$N;2)
R: =LICZ.WARUNKI($H:$H;$Q4;$N:$N;"X")+LICZ.WARUNKI($L:$L;$Q4;$N:$N;"X")
P: =LICZ.WARUNKI($H:$H;$Q4;$N:$N;2)+LICZ.WARUNKI($L:$L;$Q4;$N:$N;1)
Ilość bramek strzelonych i straconych to z kolei wynik formuł SUMA.WARUNKÓW (rysunek 6):
G+: =SUMA.WARUNKÓW($I:$I;$H:$H;$Q4)+SUMA.WARUNKÓW($K:$K;$L:$L;$Q4)
G-: =SUMA.WARUNKÓW($K:$K;$H:$H;$Q4)+SUMA.WARUNKÓW($I:$I;$L:$L;$Q4)
Kolejność drużyn ustalana jest z wykorzystaniem formuły POZYCJA. W związku z większą ilością drużyn w tegorocznym turnieju, potrzebna też była dodatkowa tabela drużyn z trzecich miejsc, jako że do dalszej części rozgrywek przechodzą wszystkie drużyny z pierwszych i drugich miejsc oraz 4 najlepsze drużyny z trzecich miejsc (rysunek 7).
Oczywiście w dalszej części turnieju, w przypadku remisów możemy mieć karne, co też niniejszy model bierze pod uwagę (rysunek 8). Od razu nasuwa się wniosek, iż lepiej zająć wyższe miejsce niż trzecie i uniknąć Francji w 1/8 finału.
Tyle teorii. Zapraszamy do wspólnej zabawy z załączonym plikiem.
