Tajnička v Exceli s použitím funkcií CONCATENATE, IF a COUNTIF.

Potrebujete vytvoriť v Exceli tajničku s automatickým vyhodnotením správnosti? Ste na správnom mieste. Dnes si ukážeme jeden z jednoduchších spôsobov vytvorenia tajničky v Exceli, pričom použijeme textovú funkciu CONCATENATE (resp. &), logickú funkciu IF na vyhodnotenie správnosti riešenia a funkcie COUNTIF a COUNTA pri určení počtu správnych odpovedí.

Najskôr ukážka vypracovanej tajničky, aby ste vedeli, do čoho idete 😉

Poďme si popísať jednotlivé časti:

V stĺpci A sú v jednotlivých bunkách prostredníctvom komentárov vložené pokyny na vypĺňanie tajničky. Napr. v bunke A2 je vložený komentár „Najmenšia jednotka informácie.“ Samozrejme si musíme pokyny aj riešenia vopred pripraviť, aby všetko sedelo tak, ako má 😀

Komentár vložíme nasledovným spôsobom:

  • klikneme pravým tlačidlom myši na bunku a zvolíme voľbu Vložiť komentár.
Ako vložiť komentár v Exceli

V oblasti C2:T11 sa nachádza samotná tajnička. V podstate si ju najskôr správne vyplníme (vytvoríme) a následne podľa potreby naformátujeme – orámovanie, podfarbenie, zarovnanie… Následne môžeme samozrejme hodnoty vymazať, tie bude neskôr dopĺňať samotný riešiteľ tajničky. Aby pôsobila tajnička ešte „krajšie“, môžeme vypnúť zobrazovanie mriežky v hárku.

Ako vypnúť zobrazenie mriežky v Exceli

Ako skryť mriežku v Exceli

V oblasti V2:V11 pomocou vhodného vzorca zobrazíme slovo, ktoré vpísal užívateľ do tajničky v danom riadku. Použiť môžeme funkciu CONCATENATE, prípadne od verzie 2019 aj funkciu CONCAT alebo symbol &. Aby sme mohli tajničku využívať aj s obmenami slov, pri vypisovaní slova budeme počítať so všetkými stĺpcami tajničky, teda v prípade prvého vzorca od bunky C2 až po bunku T2.

Do bunky V2 vložíme vzorec a následne ho skopírujeme do celej oblasti V2:V11. Použiť môžeme napr. vzorce:

  • =CONCATENATE(C2;D2;E2;F2;G2;H2;I2;J2;K2;L2;M2;N2;O2;P2;Q2;R2;S2;T2)
  • alebo =C2&D2&E2&F2&G2&H2&I2&J2&K2&L2&M2&N2&O2&P2&Q2&R2&S2&T2
  • alebo od verzie Excel 2019 aj =CONCATE(C2:T2)

Je jasné, že použitie funkcie CONCAT je najrýchlejšie, ale ak máme staršie verzie Excelu, musíme použiť niektorú z prvých dvoch možností.

V oblasti W2:W11 vyhodnocujeme správnosť odpovedí. Využijeme pritom funkciu IF, pričom na porovnanie použijeme hodnoty v stĺpci V a na porovnanie správne hodnoty, ktoré sa skrývajú v skrytom stĺpci AA. Nebojte sa, na záver hárok zabezpečíme, aby riešiteľ správne odpovede nevidel.

Použijeme logickú funkciu IF:

  • do bunky W2 vložíme vzorec =IF(V2=AA2;“správne“;“nesprávne“) a následne skopírujeme do oblasti W2:W11
  • stĺpec AA skryjeme.
Ako skryť stĺpce v Exceli

V bunke W12 sa nachádza vyhodnotenie počtu správnych odpovedí. Využijeme pritom funkciu COUNTIF a & na spojenie údajov. Číslo 10 predstavuje počet riadkov tajničky a pokojne by sme mohli namiesto čísla 10 využiť funkciu COUNTA:

  • =COUNTIF(W2:W11;“správne“)&“/“&COUNTA(W2:W11)
  • alebo =COUNTIF(W2:W11;“správne“)&“/10″

Ako zabezpečiť hárok v Exceli

Najskôr označíme pomocou CTRL+A všetky bunky, klikneme na vyznačenú oblasť pravým tlačidlom myši, zvolíme Formátovať bunky a na karte Ochrana zamkneme všetky bunky a skryjeme vzorce.

Ako zamknúť bunky a skryť vzorce

Teraz si označíme bunky, do ktorých môže užívateľ vpisovať údaje a zrušíme pri nich zamknutie buniek. Ide o oblasť C2:T11. Postup rovnaký ako pred chvíľou iba zrušíme zamknutie buniek.

Odomknutie buniek

A teraz už len zabezpečíme hárok. Klikneme pravým tlačidlom myši na uško hárka, zvolíme Zabezpečiť hárok, povolíme iba výber neuzamknutých buniek, zadáme a potvrdíme heslo a je to hotovo 😀

Zabezpečenie hárka v Exceli

A teraz je možné už len riešiť tajničku. V súboroch na stiahnutie nájdete nezabezpečenú tajničku, ale aj zabezpečenú. Použil som heslo asdf 😀

Zabezpečenie hárka vieme zrušiť opäť pomocou pravého tlačidla myši a zadaním správneho hesla.

Súbory na stiahnutie:

close

Exceluj s Excelom!

1-krát do týždňa priamo do vášho emailu tipy a triky, ktoré vám pomôžu napredovať, pracovať rýchlo a efektívne!

Zásady spracúvania osobných údajov