3. Príhody krokodíla Karola s tabuľkami vzorové riešenie

Vypracované Vzorové riešenie v Google Sheets môžete nájsť na adrese

docs.google.com/spreadsheets/d/1W9_NBYzKE8w6IZ-3EZLGSYTpUNPWz0m0ZGOKI7zRtkA Klikaním na bunky sa dozviete použité vzorce. V tomto texte si riešenia slovne vysvetlíme, prípadne uvedieme iné spôsoby riešenia.

a) Veľká násobilka

Môžeme vidieť, že hodnota bunky v našej tabuľke je daná násobkom čísla riadku a stĺpca, v ktorom sa nachádza. Pre prvú bunku, teda B4 musíme vynásobiť hodnotu buniek B3 a A4. Keď toto urobíme pomocou vzorca B4=B3*A4 a z bunky B4 ho potiahneme nadol, zistíme, že nerobí to, čo by sme chceli. Problém je, že potiahnutím vzorca z bunky B4 do bunky B5 sa zmení vzorec na B5=B4*A5 namiesto toho, aby sa zmenil na B5=B3*A5.

Riešením tohto problému je “uzamknutie” čísla riadku tejto bunky. Tým docielime, že sa nebude po potiahnutí vzorca smerom nadol meniť označenie bunky B3. Uzamykáme pridaním znaku dolára $ pred to, čo chceme zamknúť. V našom prípade zamykáme číslo riadku bunky B3, teda náš vzorec v bunke B4 bude =B$3*A4 a jeho potiahnutím už dostaneme v stĺpci správne výsledky. Podobne pri naťahovaní doprava potrebujeme uzamknúť písmeno stĺpca, takže konečný vzorec pre bunku B4 bude =B$3*$A4 a úlohu vyriešime natiahnutím tohto vzorca do ostatných buniek.

b) Trojuholník

Jeden spôsob je vyriešiť úlohu pomocou príkazu IF. Chceme zapísať, že ak súčet čísel v bunkách nad mojou bunkou a naľavo od mojej bunky je jedna, tak v mojej bunke bude jedna, inak tam bude nula. Napríklad pre bunku C5 bude vzorec vyzerať =IF(C4+B5=1, 1, 0).

Vieme to však spraviť aj trocha elegantnejšie pomocou funkcie MOD(X, Y). Táto funkcia nám vráti zvyšok, ktorý dáva číslo \(X\) po delení \(Y\), teda napríklad MOD(17, 10) vráti zvyšok, ktorý dá 17 po delení 10, čo je 7.

V našom krokodílom príklade budeme počítať zvyšok po delení dvoma. Využijeme, že MOD(0, 2) je 0 aj MOD(2,2) je 0 a MOD(1,2) je 1. Pre bunku C5 teda dostaneme vzorec =MOD(C4+B5, 2).

Natiahneme vzorec do celého bojového pola a dostaneme rozostavenie vojakov. Otázkou ešte ostáva, ako zvýrazniť na bojovom poli miesta, kde sa nachádzajú krokodíly. Použijeme na to podmienené formátovanie. To nájdeme v Google Sheets v kolónke Format pod názvom Conditional Formatting. Označíme oblasť, ktorú chceme formátovať a klikneme na Add another rule, teda pridať Pravidlo formátovania. Tu nastavíme podmienku, ktorá bude vyzerať takto: Format cells -> "if Is equal to 1". Nakoniec zvolíme, typ formátovania, teda zmenu farby bunky. V Exceli označíme formátovanú oblasť a použijeme nástroj podmienené formátovanie, kde nastavíme Formátovať bunky obsahujúce hodnotu rovnú 1 a nastavíme typ formátovania, teda zelenú farbu bunky. V OpenOffice použijeme Podmienené formátovanie z položky Formát a postupujeme rovnako ako pri Exceli.

Obrazec, ktorý sme dostali je nazývaný aj Sierpinského trojuholník. Pekné obrázky tohto tvaru nájdete aj na Wikipédii.

So Sierpinského trojuholníkom súvisí aj Pascalov trojuholník, objekt známy pre svoje zaujímavé matematické vlastnosti. sk.wikipedia.org/wiki/Pascalov_trojuholník

c) Peniaze

Táto úloha má celkom priamočiare riešenie. Pokúsime sa zaradom odpovedať na otázky zo zadania.

Hodnotu vreckového za rok zistíme ako súčet vreckového za jednotlivé dni roka, B3=SUM(B24:B388). Podobne zistíme aj hodnotu celkového nákupu za rok, B4=SUM(C24:C388). Celkový zisk vypočítame ako celkové vreckové mínus celkový nákup, obidve hodnoty už poznáme, B5=B3-B4.

Na zistenie, ako dlho bol cez rok Karol v mínuse budeme potrebovať pomocný výpočet. Ten bude vyzerať tak, že pre každý deň si vypočítame aktuálny stav účtu. Pre prvý deň je to vreckové mínus nákup teda =B24-C24, túto hodnotu si uložíme do bunky E24. Pre druhý a všetky ostatné dni to bude, stav účtu deň predtým plus vreckové mínus nákup, napríklad pre druhý deň je tento vzorec E25=E24+B25-C25, tento vzorec potiahneme smerom nadol a v políčkach E24 až E388 budeme mať stav účtu pre jednotlivé dni. Teraz, keď už vieme rýchlo pre každý deň povedať, či bol v tento deň Karol v mínuse, spočítame počet týchto dní. Použijeme na to ďalší pomocný výpočet a v bunkách F24 až F388 si označíme 1 každý deň, kedy bol Karol v mínuse – vzorec, ktorý prenesieme z bunky F24 bude =IF(E24<0,1,0). Počet jednotiek v tomto stĺpci je rovný súčtu tohto stĺpca. Riešením je teda vzorec B6=SUM(F24:F388).

Iným spôsobom ako spočítať počet záporných čísel v stĺpci E je použiť priamo na takéto účely určenú funkciu: B6=COUNTIF(E24:E388, "<0").

Pre vypočítanie priemerného stavu účtu potrebujeme vedieť stavy účtu pre jednotlivé dni. Tie už máme vypočítané, preto použijeme funkciu AVERAGE(), ktorá vypočíta aritmetický priemer zadaných buniek. K výsledku nám už stačí len použiť vzorec B8=AVERAGE(E24:E388).

Ak chceme zistiť priemerné výdavky v pondelky, potrebujeme zistiť, ktoré dni sú pondelky. V stĺpci G chceme mať čísla 1 až 7 určujúce dni v týždni. Jednotka bude pondelok, dvojka utorok a tak ďalej. Pozrieme do kalendára a vidíme, že prvého januára 2015 bol štvrtok. Takže v G24 bude číslo 4. V ostatných bunkách bude vzorec tvaru =MOD(X, 7)+1. Tento vzorec nám vráti \(X+1\), ak \(X\) je menej ako 7 a jednotku, ak \(X\) je 7. Do bunky G25 teda dáme =MOD(G24, 7)+1 a do zvyšných buniek skopírujeme natiahnutím.

Následne si do stĺpca H dáme jednotky do tých riadkov, ktoré zodpovedajú pondelkom a nuly všade inde: H24=IF(G24=1, 1, 0). Do stĺpca I si potom dáme ku každému pondelku výdavky v tento deň a nulu ku všetkým ostatným dňom (I24=IF(H24=1, C24, 0), alebo I24=H24 * C24). Priemerné výdaje v pondelky potom vypočítame ako súčet pondelkových výdajov vydelený počtom pondelkov, teda B7=SUM(I24:I388)/SUM(H24:H388).

Skúsme odpovedať na poslednú otázku, koľko peňazí Karol v jednotlivých mesiacoch ušetril. Na riešenie znova použijeme pomocný výpočet, tentokrát si budeme pre každý deň pamätať stav účtu v tento deň s tým rozdielom, že každý mesiac bude účet štartovať odznova. Tým docielime, že dostaneme pre každý deň stav účtu vzhľadom na daný mesiac. Na toto riešenie použijeme ešte jeden pomocný stĺpec J, v ktorom si budeme ku každému dňu pamätať mesiac, v ktorom sa nachádza. Na to použijeme funkciu MONTH(). Napríklad, MONTH(A100) teda MONTH("18/03/2015") bude 3. Pre prvý deň – políčko K24 – bude vzorec vyzerať takto: =IF(J24=J23, K23, 0)+B24-C24, teda pripočítame vreckové mínus nákup ku predošlej hodnote. Túto predošlú hodnotu budeme v prvý deň každého mesiaca považovať za nulovú.

Iným spôsobom riešenia bolo ručne si pre každý mesiac zistiť jeho prvý a posledný riadok v tabuľke a následne pomocou funkcie SUM spočítať odpovede.

d) Výsledkovka

Našou prvou úlohou je zistiť počet bodov pre každého súťažiaceho, pričom si musíme dať pozor, aby sa úloha za najviac a úloha za najmenej bodov rátali iba za polovicu. Toto pre prvú bunku (teda I3) jednoducho zapíšeme vzorcom =SUM(D3:H3)-MIN(D3:H3)/2-MAX(D3:H3)/2. Keď máme pre každého súťažiaceho vypočítaný jeho bodový zisk, je potrebné, aby sme výsledkovú listinu zoradili podľa tohto počtu bodov.

Označíme teda celú tabuľku, od políčka B3 až po I213. Potom použijeme funkciu zoradenia podľa hodnoty nejakej bunky, v našom prípade to bude počet bodov. Táto funkcia pracuje tak, že zoradí tabuľku podľa nami zvoleného stĺpca. V Exceli nájdeme túto funkciu pod záložkou Dáta s názvom Zoradiť. Tu nastavíme “zoradiť podľa” počet bodov “od najvačšieho po najmenší”.

Keď už máme súťažiacich zoradených podľa bodového zisku, určíme každému poradové číslo. Vytvoríme si v pomocnom stĺpci od K3 po K213 čísla 1 až 211. Potom poradie súťažiaceho bude buď príslušná hodnota v stĺpci K, ak mal menej bodov ako súťažiaci pred ním, alebo to bude rovnaká hodnota ako mal súťažiaci pred ním. Teda napríklad do A3 dáme vzorec =IF(I3=I2, A2, K3), ktorý môžeme natiahnuť do ostatných riadkov.

Nakoniec maximálne počty za jednotlivé úlohy zvýrazníme rovnakým spôsobom ako jednotky v podúlohe (b).

e) Zvieratá

Na vyriešenie tejto úlohy bolo základom úspechu dobre odsimulovať celý proces a nikde sa nepomýliť.

Najjednoduchším riešením je napísať vzorec pre počty zvierat po konci druhého roku a tento vzorec potom použiť aj pre iné dni. V našom prípade je počet krokodílov \(k=C6\), počet mamutov \(m=B6\), počet holubov \(h=D6\) a aktuálny rok je \(i=A6\).

Keď si prečítame zadanie, vidíme, že počet mamutov na druhý rok \(m_2\) je daný vzťahom \(m_2 = m + 2 - h/200000 - k/2000 - i/500\). Dosadením hodnôt buniek za premenné \(k\), \(m\), \(h\) dostaneme vzorec pre bunku B7, teda počet mamutov na druhý rok =B6+2-(D6/200000)-(C6/2000)-(A6/500). Podobne počet holubov zapíšeme vzorcom D7=D6+5*SQRT(D6)-3*C6. Napokon, počet krokodílov v druhom roku je daný vzorcom C7=C6*0.99-B6/20+D6/10000.

Tieto 3 vzorce môžeme jednoducho ťahať smerom nadol, pokým neklesne počet mamutov pod nulu. Pre vykreslenie grafu sme si tabuľku skopírovali aj so správnymi konštantami, celú tabuľku sme označili a vybrali sme vhodný typ grafu.

f) Jedlo

V úlohe sa pýtame, koľko najviac holubov vie Karol zjesť, keď skončí úplne na juhovýchode. My však zistíme pre každé políčko námestia, koľko najviac holubov by Karol mohol zjesť, keby sa mal dostať iba po toto políčko. Zistíme teda oveľa viac ako úloha žiada, no napriek tomu to bude jednoduchšie. Dôvodom je to, že tieto čiastkové odpovede spolu úzko súvisia.

Označíme si maximálny počet holubov, ktoré môže Karol zjesť, ak skončí na políčku \((i,j)\) (t. j. na políčku v \(i\)-tom riadku a \(j\)-tom stĺpci) ako \(holuby(i,j)\). Ako sa mohol Karol dostať na políčko \((i,j)\)? Buď tam prišiel zo severu alebo zo západu, čiže z políčka \((i-1,j)\) alebo z políčka \((i,j-1)\). Takže maximálny počet holubov, ktorý mohol zjesť pred tým, ako sa dostal na políčko \((i,j)\) bude väčšie z čísel \(holuby(i-1, j)\) a \(holuby(i, j-1)\). Navyše mohol nejakého holuba zjesť na políčku \((i,j)\). \[holuby(i,j)=\max(holuby(i-1,j),holuby(i,j-1))+\text{počet holubov na }(i,j)\]

Pre riešenie úlohy si teda vytvoríme novú tabuľku od bunky B41 napravo a dolu. Do buniek dáme vzorec, ktorý spočíta \(holub(i,j)\) pre dané políčko. Presnejšie, do bunky B41 napíšeme =MAX(B40, A41)+IF(B7="H", 1, 0) a natiahneme do zvyšných buniek. V bunke W72 sa nám potom zjaví odpoveď na úlohu. Úžasné, však?

 

Posledný problém, s ktorým sme sa museli popasovať je vykreslenie najlepšej Karolovej trasy. Tú budeme vyrábať odzadu. Budeme sa snažiť, aby na políčkach trasy bola jednotka a na ostatných nula. Vieme, že sa Karol niekedy musel dostať do cieľa, takže na úplne juhovýchodné políčko dáme jednotku.

Teraz potrebujeme zistiť, či je na toto políčko lepšie prísť zo severu alebo zo západu. Ak je väčší počet holubov na západe, tak je lepšie prísť zo západu, ak je viac holubov na severe, je lepšie prísť zo severu. V prípade rovnakých počtov je jedno odkiaľ sme prišli, ale nemôžeme prísť naraz z oboch smerov. Preto si určíme, že v prípade remízy chceme prísť zo severu.

To bola celá myšlienka, zvyšok roboty je zapísať to do tabuľky. Sú dva možné dôvody, prečo by v políčku \((i,j)\) mala byť jednotka. Prvý dôvod je, keď z daného políčka budeme v ceste pokračovať na juh, teda keď v \((i+1, j)\) je jednotka a zároveň \(holuby(i,j)\geq holuby(i+1,j-1)\). Druhý dôvod je, keď z daného políčka budeme pokračovať na východ. Jeho formálny zápis bude podobný, len máme \(>\) namiesto \(\geq\). Navyše, iba jeden z týchto dôvodov sa môže uplatniť, pretože nemôže byť jednotka aj na políčku \((i+1, j)\) aj na \((i, j+1)\). Takže v tabuľke to môžeme zapísať ako súčet dvoch IFov. Takto vyzerá vzorec v políčku AW72: =IF(W72>=V73, AW73, 0)+IF(W72>X71, AX72, 0), do ostatných políčok stačí vzorec natiahnuť z tohto.

Záverečné slovo

Videli sme, že tabuľkové kalkulátory sú silné nástroje. Ale to sme videli len malý zlomok ich schopností. Dá sa v nich spočítať oveľa viac, teoreticky sa v nich dá spočítať všetko, čo sa dá vôbec spočítať ;). Existuje dokonca aj počítačová hra naprogramovaná v takomto kalkulátore. Ale netreba robiť všetko v tabuľkách. Primárny cieľ tabuliek je spracovávanie dát.

Pokiaľ potrebujete naprogramovať aplikáciu, alebo nejaký zložitejší výpočet, je lepšie siahnuť po nejakom praktickejšom programovacom jazyku ako je C++, Python alebo Java. Ak sa raz naučíte dobre pracovať s týmito jazykmi, budete schopní robiť úžasné veci, o ktorých sa vám možno ani nesnívalo.