Prawdopodobieństwo wygrania w finale Euro 2016
DLA UŻYTKOWNIKÓW
Rozwiązanie w pliku automatycznie generuje drabinkę meczy (odrzuca drużyny, które przegrały w poprzednich fazach rozgrywek)
oraz na podstawie zadanego prawdopodobieństwa meczy (arkusz Prawdopodobieństwo) liczy prawdopodobieństwo wygrania meczu finałowego.
Rozwiązanie można używać po każdej fazie analizując prawdopodobieństwo (kol. L arkusz Mecze), można zmieniać prawdopodobieństwo w arkuszu prawdopodobieństwo,
w którym wszystkie prawdopodobieństwa zostały wprowadzone w wysokości 50%. Są one do wprowadzania przez użytkowników.
Przykład:
Prawdopodobieństwo wygrania meczu Anglii z Islandią wynosi 50% (tak jak wszystkie zamieszczone w rozwiązaniu prawdopodobieństwa).
Oznacza to, że prawdopodobieństwo wygrania finału przez Islandię wynosi 6,25% (komórka L35 w arkuszu Mecze).
Jeżeli prawdopodobieństwo wygrania meczu Anglii z Islandią zostanie zmienione na 60% (komórka U24 w arkuszu Prawdopodobieństwo),
to prawdopodobieństwo wygrania finału przez Islandię spadnie do 5% (komórka L37 w arkuszu Mecze).
Po wprowadzeniu wyniku meczu Anglii z Islandią (do komórek E34 i E35) prawdopodobieństwo to zmieni się (w komórce L37 arkusza Mecze),
ponieważ jedna z tych drużyn zakwalifikuje się do dalszej fazy rozgrywek.
plik do pobrania: euro2016z.xlsx wersja MS Excel wersje wcześniejsze
Jeśli podoba Ci się logika i zgrabność rozwiązania, to zapraszamy na warsztaty, na których nie uczymy MS Excel, tylko projektowania budowy rozwiązań, które są czytelne, bezpieczne i elastyczne.
Wcześniej zapraszamy do zapoznania się z informacjami na stronach www.4tg.pl
KOLEJNE KROKI BUDOWY ROZWIĄZANIA
Poniżej zostały wprowadzone wzory (i niektóre zawartości tekstowe komórek rozwiązania), które tworzą model.
Ważna jest czytelność i elastyczność rozwiązania. Dlatego struktury danych i algorytmy zostały zaprojektowane w tym rozwiązaniu tak,
aby od strony użytkownika, jak i projektanta takie właśnie były.
ARKUSZ: Mecze
* Wprowadzić nagłówki w wierszach początkowych
* D11: 1/8 FINAŁU
* A13: =A11+1
* A14: =A13+1
* B13: =DZIES.NA.DWÓJK(A13-1;4) (uwaga! we wcześniejszych wersjach MS Excel funkcja nazywa się DEC2BIN)
* B14 (skopiowane z B13)
* formatowanie E13:E14 - tło na zielony kolor do wprowadzania wyniku
* F13: =E13>E14
* F14: =E14>E13
* G13: =E13
* H13: =JEŻELI(F13;LEWY(B13;DŁ(B13)-1);"")
* H14 (skopiowane z H13)
* I13: =JEŻELI(G13;D13;"")
* I14 (skopiowane z I13)
* zakres komórek A13:I15 skopiować do komórek A13:I36
* D38: ĆWIERĆFINAŁ
* zakres komórek A13:I15 skopiować do komórek A40:I42
* poprawić B40 na: =DZIES.NA.DWÓJK(A13-1;3)
* C40: =JEŻELI.BŁĄD(PODAJ.POZYCJĘ(B40;H:H;0);"")
* D40: =JEŻELI.BŁĄD(INDEKS(D:D;C40);"")
* zakres komórek B40:D40 skopiować do komórek B41:D41
* zakres komórek A40:I42 skopiować do komórek A40:I51
* D53: PÓŁFINAŁ
* zakres komórek A40:I42 skopiować do komórek A55:I57
* poprawić B55 na: =DZIES.NA.DWÓJK(A13-1;2)
* komórkę B55 skopiować do komórki B56
* zakres komórek A55:I57 skopiować do komórek A55:I60
* D62: FINAŁ
* zakres komórek A55:I57 skopiować do komórek A64:I66
* poprawić B64 na: =DZIES.NA.DWÓJK(A13-1;1)
* komórkę B64 skopiować do komórki B65
* formatowanie D13:D14 - tło na zielony kolor do wprowadzania drużyny
* zakres komórek D13:D15 skopiować do komórek D13:D36
* wpisanie nazw drużyn do wybranych komórek zakresu D13:D35 (tło komórek na zielono)
ARKUSZ Prawdopodobieństwo
* A10: =1+A9
* B10: =PODAJ.POZYCJĘ(A10;Mecze!A:A;0)
* C10: =INDEKS(Mecze!D:D;B10)
* zakres komórek A10:C10 skopiować do komórek A10:C25
* F7:=1+E7
* F8: =INDEKS($C10:$C25;F7)
* zakres komórek F7:F8 skopiować do komórek F7:U8
do komórek nad główną przekątną tak powstałej macierzy wpisać prawdopodobieństwa wygrania meczy (zmienić tło komórek na zielono)
* A34: =A10
* C34: =C10
* zakres komórek A34:C34 skopiować do komórek A34:C49
* F34: =JEŻELI($A34=F$7;"";JEŻELI($A34
* A58: =A10
* C58: =C10
* D58: =LICZ.JEŻELI(Mecze!I:I;C58)
* zakres komórek A58:D58 skopiować do komórek A58:D73
* F56: =INDEKS($D58:$D73;F7)
* komórkę F56 skopiować do zakresu komórek F56:U56
* F58: =LUB($D58;F$56)
* komórkę F58 skopiować do zakresu komórek F58:U73
* A82: =A10
* C82: =C10
* D82: =D58
* zakres komórek A82:D82 skopiować do komórek A82:D97
*F82: =JEŻELI($A82=F$7;"";JEŻELI($D82;0;JEŻELI(F$56;1;F34)))
* komórkę F82 skopiować do zakresu komórek F82:U97
* W82: =A82
* komórkę W82 skopiować do zakresu komórek W82:W97
* formatowanie warunkowe:
- zaznaczyć zakres F10:U25,
- wybrać z menu formatowanie warunkowe | użyj formuły do określenia komórek, które należy sformatować
- w pozycji: formatuj wartości, dla których następująca formuła jest prawdziwa: =F58
- wybrać formatuj (ustawić tło komórek – białe; można również ustawić białą czcionkę)
(formatowanie warunkowe jest po to, aby po każdej fazie meczy można było łatwiej zmieniać tylko potrzebne dane – prawdopodobieństwa).
ARKUSZ Obliczenia
Wpisanie tytułów kolumn w wierszach 7:11
B14: =1+B4
C14: =BITXOR(B14-1;1)+1 (uwaga! we wcześniejszych wersjach nie ma funkcji BITXOR, trzeba wpisać dane ręcznie)
F14: =PODAJ.POZYCJĘ(B14;Prawdopodobieństwo!$W:$W;0)
G14: =PODAJ.POZYCJĘ(C14; Prawdopodobieństwo!$7:$7;0)
H14: =INDEKS(Prawdopodobieństwo!$1:$1048576;F14;G14)
* pokolorowanie tła komórki H14 na jasno szaro
* zakres komórek B14:H14 skopiować do komórek J14:P15
* poprawić K14 na: =BITXOR(B14-1;2)+1
* poprawić K15 na: =BITXOR(B14-1;3)+1
* L14: =PODAJ.POZYCJĘ(K14;$B:$B;0)
* M14: =INDEKS(H:H;L14)
* pokolorowanie tła komórki M14 na ciemniejszy szary
* zakres komórek L14:M14 skopiować do komórek L14:M15
* R14: =(1-SUMA(M14:M22)+SUMA.ILOCZYNÓW(P14:P22;M14:M22))*H14
* pokolorowanie tła komórki R14 na jasno żółty
* zakres komórek J14:R15 skopiować do komórek T14:AB15
* zakres komórek T15:AB15 skopiować do komórek T15:AB17
* poprawić U14 na: =BITXOR(B14-1;3)+1
* poprawić U15 na: =BITXOR(B14-1;4)+1
* poprawić U16 na: =BITXOR(B14-1;5)+1
* poprawić U17 na: =BITXOR(B14-1;6)+1
* zakres komórek T14:AB15 skopiować do komórek AD14:AL15
* zakres komórek AD15:AL15 skopiować do komórek AD15:AL21
* poprawić AE14 na: =BITXOR(B14-1;7)+1
* poprawić AE15 na: =BITXOR(B14-1;8)+1
* poprawić AE16 na: =BITXOR(B14-1;9)+1
* poprawić AE17 na: =BITXOR(B14-1;10)+1
* poprawić AE18 na: =BITXOR(B14-1;11)+1
* poprawić AE19 na: =BITXOR(B14-1;12)+1
* poprawić AE20 na: =BITXOR(B14-1;13)+1
* poprawić AE21 na: =BITXOR(B14-1;14)+1
* zakres komórek A13:AM22 skopiować do komórek A13:AM172
ARKUSZ Mecze
* K13: =PODAJ.POZYCJĘ(A13;Obliczenia!B:B;0)
* L13: =INDEKS(Obliczenia!AL:AL;K13)
* zakres komórek K13:L13 skopiować do komórek K13:L14
* zakres komórek K13:L15 skopiować do komórek K13:L36
Uczymy, jak postępować przy projektowaniu i budowie rozwiązań, aby rozwiązanie było określonej jakości,
a jednocześnie, aby nie trzeba było poświęcić zbyt dużo czasu przy jego tworzeniu.
Używanie nieefektywnych narzędzi MS Excel i nieelastycznych sposobów rozwiązań powoduje,
że z rozwiązaniami są kłopoty (wolno działają, trudno je zmodyfikować) - wtedy trudno sobie wyobrazić,
że za pomocą MS Excel można zrealizować poważniejsze rozwiązanie wymaganej jakości - tak MS Excel jest postrzegany wśród znacznej części osób.
Pracujemy, aby to zmienić. Chcemy, aby MS Excel + VB for Excel + SQL współpracujący z MS Excel to były podstawowe narzędzia analityków i zarządzających.
Jedno środowisko pracy - dobrze zanane, prawidłowo wykorzystywane.
Uczymy innej idei pracy, niż to proponuje MS Microsoft nie tylko w używaniu MS Excel, ale również Visual Basic for Excel.
Na VB for Excel uczymy przede wszystkim semantyki, rozumienia, dlaczego trzeba stosować jedne rozwiązania, a inne nie.
Na warsztatach często oduczamy stosowania wielu niepotrzebnych elementów, zakłócających czytelność i prostotę rozwiązania.
Reguła: im bardziej proste rozwiązanie, tym łatwiej je modyfikować, poprawiać, jest bardzo ważna.
Dlatego staramy się oceniać rozwiązania oraz uczyć ich oceny.