SVYHLEDAT: Jak ověřit, že je daná hodnota v seznamu?

Účetní mi připravila do excelovského souboru seznam faktur, ve kterých se objevila položka "jablka". Do jiného sloupce mi připravila seznam faktur, kde se prodaly banány. Potřeboval bych zjistit, která čísla faktur jablek se zároveň objevují mezi fakturami banánů (znamená to, že na faktuře jsou obě položky).

Řešení je snadnější, než se může na první pohled zdát. Využijeme k tomu funkci listu SVYHLEDAT. Pomocí té budeme hledat jednotlivé řádky "jablek" v tabulce/ve sloupci "banánů". Platí tedy, že budeme vyhledátat jeden řádek, v druhé tabulce a budeme vracet první sloupec (ten jediný). Pokud nám vrátí stejnou hodnotu jako je vyhledávaná hodnota, znamená to, že se faktura v druhém sloupci nachází. Pokud vrátí chybu, faktura tam není.

Více bude patrné z obrázku níže.

Obdobně by šlo situaci řešit i naopak. Stáhněte si testovací soubor a vyzkoušejte sami. :-)

Zamykání buněk – 2. díl: nastavení pokročilých práv

Minule jsme se podívali na obecné principy zamykání buněk. V tomto díle si ukážeme, jak jít ještě dál. Použijeme stejnou tabulku a podobný příklad.

Milan, který vlastní síť obchodů s ovocem a zeleninou, plánuje prodeje. Chce nyní jednu tabulku zaslat všem svým prodejcům, aby vyplnili předpokládané prodeje. Potřebuje ale tabulku upravit tak, aby každý mohl upravovat pouze "svoje" hodnoty – tedy hodnoty ve svém sloucpi a nikde jinde.

Každý prodejce bude mít své vlastní uživatelské jméno a heslo, pomocí kterého se přihlásí "do tabulky". Na základě toho se mu pak odemkne pouze příslušná oblast, kterou bude moci upravit.

Začneme tak, že si oblast každého prodejce pojmenujeme podle jména prodejce, viz obrázek níže. Oblasti tedy budou pojmenované Libor, Lucie, Olga atd.

V dalším kroku vytvoříme na druhém listu tabulku uživatelů a jejich hesel. Kromě všech prodejců přidáme taky administrátorský účet (účet Milana), kterému se vždy odemkne celý sešit.

Nyní už stačí jen zamknout sešit, nastavit heslo (v příkladu je nastaveno heslo zelenina, které je také použité v makru níže). Poté je třeba přidat do editoru maker (spusťte přes klávesovou zkratku Alt+F11) makro, které se bude starat o přihlašování uživatelů.

Stáhněte si tento soubor.

Pro první přihlášení použijte uživatele administrátor a heslo admin.

Zamykání buněk – 1. díl: základy

Excel umožňuje poměrně snadno nastavit, které buňky mohou uživatelé upravovat a měnit. Slouží k tomu zamykání buněk a listů. Problematiku si opět ukážeme na praktickém příkladu, kdy navážeme na jednu z předchozích situací (Hromadné vkládání a úpravy hodnot). 

Příklad

Pan Milan má síť obchodů s ovocem a zeleninou. Plánuje prodeje jednotlivým prodejcům na následující měsíc. Aby Milan nemusel veškerou práci dělat sám, chce pověřit svou asistentku Janu, aby mu část tabulky vyplnila sama. Potřebuje jí ale omezit přístup tak, aby mohla pouze vyplňovat hodnoty tam, kam má a nesmazala tak některé důležité vzorce.

Jana bude smět editovat pouze zelené buňky.

základní tabulka

Postup

Pojďme si nejprve říct něco k teorii.

Ač se to bude zdát nelogické, ve výchozím nastavení jsou všechny buňky v Excelu zamčené. Jen díky tomu, že není zamčený celý list (případně sešit) je možné je upravovat. Až v okamžiku zamčení listu nebude možnost uzamčené buňky editovat.

Budeme tedy postupovat tak, že všechny buňky, u kterých chceme možnost úpravy ponechat odemkneme a následně zamkneme list. Nejprve tedy označíme zelené buňky, klepeneme na ně pravým tlačítkem myši a vybereme příkaz Formát buněk. Na kartě Zámek pak odškrneme volbu Uzamknout. Tím zůstanou buňky po zamčení listu odemčené.

Na kartě Revize zvolíme Zamknout list. V nově otevřeném okně odškrtneme příkaz Vybrat uzamčené buňky. Pak už uživatel nebude moci vybrat (a tudíž ani upravovat) buňky, které jsme předtím neodemkli. Doporučuji nastavit heslo pro odemčení listu (jinak zkušený uživvatel kdykoli list odemkne a tím si "otevře" i uzamčené buňky.

Na první pohled není výsledek patrný, při bližším prozkoumání zjistíme, že neodemčené buňky skutečně není možné vybrat. Takhle upravenou tabulku může autor někomu poslat k vyplnění beze strachu, že editor něco pokazí či změní výpočty.

V příštím díle se podíváme na tabulku ještě podrobněji. Ukážeme si, jak je tabulku možné najednou poslat všem lidem, kteří se na ní podílejí a jak nastavit uživatelům práva tak, aby každý mohl upravovat pouze svůj konkrétní sloupec s daty.

Jak zobrazit dva excelovské soubory vedle sebe?

Mám otevřené dva excelovské soubory a chci je zobrazit na jedné obrazovce. Nechci pořád přeskakovat z jednoho souboru do druhého.

Zobrazení dvou souborů na jedné obrazovce vedle sebe je docela banální úkol, který má jednoduché řešení.

Jakmile máte otevřené oba soubory, přejděte na kartu Zobrazení, zvolte položku Uspořádat vše a vyberte Svisle.

Oba soubory jsou nyní zobrazené vedle sebe.

Možnosti roztažení buněk

Většina z vás asi bude znát funkci "roztažení buňky". Ta umožňuje chytit libovolnou buňku za její pravý dolní roh, roztáhnout ji do větší oblasti a Excel pak automaticky vyplní zbylé buňky. ,

Tuto funkci můžete využít například na vyplnění dnů v týdnu (do jedné buňky vepíšete pondělí, roztáhnete a Excel doplní do zbylých buněk názvy dnů v týdnu). Obdobně to funguje v případě měsíců nebo případě čísel, kdy zapíšete 1, 2 a Excel tak bude pokračovat v daném trendu.

Roztažení buněk ale umí mnohem víc, než jen tupě kopírovat buňky.

Dny a měsíce

Vezměme si příklad, kdy v buňce A1 máme uvedené datum 1. 1. 2015. Pokud roztáhneme buňku standardním způsobem, vloží se jednotlivé dny.

Co když ale potřebujeme vyplnit pouze pracovní dny? Nebo pouze dané měsíce? Buňku chytneme za pravý dolní roh při držení pravého tlačítka myši a roztáhneme do požadované oblasti. Z vyvolané kontextové nabídky vybereme například Vyplnit pracovní dny a je hotovo.

Formátování

Další možností je vyplňování bez formátování. Dejme tomu, že máme připravenou tabulku, která už obsahuje nějaké formáty buněk. Do jednoho sloupce jsme nyní napsali vzorec, který potřebujeme roztáhnout do všech řádků. Chceme zachovat formátování.

Nejprve se podívejme, co by se stalo, kdybychom vzorec roztáhli standardním způsobem.

Pokud ale využijeme roztažení přes pravé tlačítko myši a volbu Vyplnit bez formátování dojde k vyplnění buněk vzorcem, aniž by se nám formátování rozhodilo.

Hromadné vkládání a úpravy hodnot

Čas od času se v Excelu setkáme s tím, že potřebujeme hromadně upravovat více buněk najednou. Jak situaci řešit si ukážeme na praktických příkladech.

Hromadné vkládání hodnot

Pan Milan má svoji síť obchodů s ovocem a zeleninou. U svých prodejců v jednoduché tabulce plánuje, kolik kilo jednotlivých potravin mají prodat v daném měsíci. Ví ale, že u některých potravin bude plánovat stejnou hodnotu pro všechny prodejce. Například u jablek a hrušek chce všem zadat 150 kg.

• Nejjednodušší variantou samozřejmě je vepsání hodnoty 150 do jedné buňky a pak ji rozkopírovat dále.
• Lepší varianta je ale vybrat celou oblast, kam chceme buňky vložit, zapsat hodnotu 150 a změnu potvrdit klávesovou zkratkou Ctrl+Enter.

Milan si dále uvědomil, že Petrovi naplánoval prodat 28 kilo mandarinek, ale stejný počet chce nyní zadat i pro mrkve, okurky, pomeranče a pórky.

• Označíme oblast "od mandarinek po pórky", čili oblast kam chceme buňku rozkopírovat, včetně původní buňky a stiskneme klávesovou zkratku Ctrl+D.
• Podobný postup platí při kopírování buněk doprava (čili například zkopírovat hodnotu u Olgy také pro Páju a Petra ve stejném řádku). Jen použijeme klávesovou zkratku Ctrl+R.

• Mimochodem, klávesové zkratky Ctrl+D a Ctrl+R také slouží k bleskovému zkopírování buňky, která se nachází nad nebo vlevo od vybrané buňky.

Hromadná úprava hodnot

Milan je zmatkař. Poté, co tabulku celou naplnil daty, si vzpomněl, že v dubnu mívá vyšší prodeje než ve zbytku roku. Teď potřebuje všechny hodnoty v tabulce navýšit o 20 %.

• Trik spočívá v tom, že všechny hodnoty vynásobíme číslem 1,2 (dostaneme se tak na 120 % původních hodnot). Použijeme jednu pomocnou buňku, do které zapíšeme hodnotu 1,2. Buňku zkopírujeme, vybereme celou oblast s daty, klepneme pravým tlačítkem myši a vybereme příkaz Vložit jinak. V nově otevřeném okně pak zaškrneme, že chceme vložit pouze hodnoty (aby se nerozhodily formáty buněk) a ve spodní části vybereme operaci násobení. Po odklepnutí tlačítkem OK se všechny hodnoty zvýší.
• Stejným způsobem lze i dělit, přičítat nebo odečítat hodnoty.

Aby Milan nemusel veškerou práci dělat sám, chce pověřit svou asistentku, aby mu část tabulky vyplnila sama. Potřebuje jí ale omezit přístup tak, aby mohla pouze vyplňovat hodnoty tam, kam má.

O tom ale až příště, ve dvoudílném seriálu o zamykání buněk.