EXCEL: pokročilá kontrola a předvyplňování dat

Návody platí minimálně pro Excel 2010.

Uzamknutí listu/buněk

Revize / Zamknout list. Lze zvolit Povolit uživatelům úpravy oblastí - vybrat, co upravovat lze.

Kontrola dat

Označit buňku - Data / Ověření dat - vybrat:

• Povolit - a zvolit co je třeba
• Zprávu při zadávání - zobrazí se rovnou při označení buňky
• Chybové hlášení - zobrazí se při pokusu o zadání hodnoty, která není povolena; lze mít tři stavy:
  • Stop - nenechá zadat chybnou hodnotu
  • Varování - lze volit ANO, NE, tedy může nechat zadat chybnou hodnotu
  • Informace - jen informační hláška s OK

Vymazání validace dat - tlačítko Vymazat vše v dialogu Ověření dat.

Krok 1 - povolená hodnota

Krok 2 - hláška při zadávání

Krok 3 - chybová hlášky 

Krok 4 - ukázka hlášky při zadávání

Krok 5 - ukázka chybové hlášky

​Předvyplňování dat

Rozvírací seznam

Označit buňku, Data / Ověření dat - vybrat Povolit "Seznam" a vybrat buňky, ze kterých se seznam má vytvářet.

Excel 2010

https://support.office.com/en-sg/article/Remove-a-dropdown-list-01b38366-f5bb-43f8-9df9-8a707b9502f0

Nastavení stylu odkazů

Excel 2010 - styl odkazů

Vyplňování řádku/sloupce dle jiného

Funkce: svyhledat a vvyhledat (vlookup, hlookup).

Příklad:

=VVYHLEDAT(B2;F2:G5;2)

- dle hodnoty v buňce B2 vyhledá v tabulce F2G5 hodnotu v prvním řádku. Druhý související řádek zobrazí do editované buňky.

Excel 2010

Propojení více tabulek: EXCELentní triky a návody: SVYHLEDAT / VLOOKUP - funkce pro propojování více tabulek, http://excel-navod.fotopulos.net/svyhledat.html.

Odebrání jména https://support.office.com/en-us/article/Define-and-use-names-in-formulas-2d9abce7-42cf-4a21-a6b4-f02786f690b7?ui=en-US&rs=en-US&ad=US#bmdelete_a_name .

Vyplňování tabulky dle jiné 

Reference : http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=119:vlookup-multiple-values-return-multiple-corresponding-values-for-one-lookup-value&catid=77&Itemid=473

Mějme tabulku, ve které máme duplicitní klíče (jablka, citrony) a různé ceny. Chceme, aby když jinde vyplníme třeba "jablka", zobrazily se všechny ceny jablek z první tabulky.

Můžeme to udělat pomocí vzorce:

=IFERROR(INDEX($E$12:$E$18; SMALL(KDYŽ($A$13=$D$12:$D$18; ŘÁDEK($D$12:$D$18)-ŘÁDEK($D$12)+1); ŘÁDEK(1:1)));"")

• IFERROR - jen potlačuje chybový výstup, protože pokud máme méně záznamů než řádků vzorce, logicky k němu dojde, protože Excel nemá čím řádky vyplnit
• INDEX - vrací index řádků s cenami
• SMALL - vybírá řádky, které se zobrazí, pro bližší pochopení viz reference výše

Schematicky zapsáno:

=IFERROR(INDEX(__CO_UKAZAT__; SMALL(KDYŽ(__VYBRANO__=__VYBRAT_Z_CEHO__; ŘÁDEK(__VYBRAT_Z_CEHO__)-ŘÁDEK(__VYBRAT_Z_CEHO_PRVNI_RADEK__)+1); ŘÁDEK(1:1)));"")

Vzorec se musí vložit a odentrovat pomocí Ctrl+Shift+Enter (tj. jako array formula), Jinak vrátí chybu #HODNOTA. Složené závorky, které v něm vidíte (na obrázku) se přidají automaticky právě pomocí Ctrl+Shift+Enter. Poté stačí vzorec zkopírovat do dalších řádků.

Příklad použití