ExcelTutor

ExcelTutor http://www.exceltutor.cz

ExcelTutor je unikátní elektronický lexikon řešení praktických problémů z různých oblastí nasazení tabulkového procesoru Excel. Autor: Jiří Číhař (za pomoci dotazů stovek reálných uživatelů tabulkového procesoru Excel)

Zakoupením e-publikace ExcelTutor získáte: Návody na řešení několika desítek problémů z praxe uživatele Excelu. Ve srovnání s jinými publikacemi řešení směřující bezprostředně k cíli. Postupy, které jinde nenaleznete. Řešení dostupnými prostředky bez nutnosti programování. Metody univerzálně použitelné ve všech v současné době používaných verzích - Excel97, Excel2000, ExcelXP, Excel2003, Excel2007.

Více informací o této publikaci získáte na webu http://www.exceltutor.cz 1

ExcelTutor http://www.exceltutor.cz Pracuji ve zdravotnickém zařízení, kde mám na starosti personální agendu. Vytvořila jsem v Excelu přehled služeb zdravotnického personálu. Vše funguje správně až na jednu důležitou věc – pokud chci spočítat skutečně odpracovanou dobu na nějakém zákroku a odečítám tedy počáteční čas od konečného, dostanu výsledek jen v případě, když čas nepřekročil půlnoc. Jak mám postupovat, abych zjistila vždy skutečně odpracovanou dobu? Odečítání času dává očekávaný výsledek pouze za předpokladu, že doba mezi počátkem a koncem nepřekračuje půlnoc. V opačném případě Excel vrací pouze znaky #########.

obr. 1 – odečítání času dává očekávaný výsledek pouze za předpokladu, že doba mezi počátkem a koncem nepřekračuje půlnoc

Důvodem je skutečnost, že čas je interně ukládán ve tvaru desetinného čísla představujícího uplynulou část 24 hodin. Tedy jakýkoliv časový údaj je vyjádřen desetinným číslem menším než 1. Například poledne je vyjádřeno číslem 0,5, 06:00 číslem 0,25, 18:00 číslem 0,75 atd.

obr. 2 – předchozí časové údaje vyjádřené jako desetinné číslo změnou formátu buněk

Pokud tedy odečítáme čas před půlnocí (např. 19:54 (tedy 0,83)) od času po půlnoci (např. 16:59 (tedy 0,71)) dostaneme záporný výsledek a Excel neumí pracovat se záporným časem.

Univerzální vzorec, který nám poskytne skutečný rozdíl časů: =(A2>B2) + B2–A2 Alternativní vzorec pro výpočet rozdílu časů: =MOD(B2-A2;1) obr. 3 – vzorce, které umožňují univerzální odečítání časů

2

ExcelTutor http://www.exceltutor.cz Vytvořil jsem přehled odpracované doby. Obsahuje dva sloupce pro počátek a konec práce a dále počátek a konec noční směny. Potřeboval bych určit, kolik času z odpracované doby připadá právě na práci v noční směně.

obr. 1 – určení doby odpracované v noční směně

Vzorec je sice komplikovaný, ale poskytuje nám přesně informaci o odpracované době v intervalu, jehož počátek definujeme v buňkách B2 a C2 jako počátek a konec noční směny. =C5-KDYŽ(B5>=A5;MAX(0;MIN(B5;$B$2)-MAX(A5;$C$2));MAX(0;$B$2MAX(A5;$C$2)) +MAX(0;MIN(B5;$B$2)-$C$2))

3

ExcelTutor http://www.exceltutor.cz Při zadávání vzorců se mi často stává nemilá věc – Excel vzorec nevypočítá, ale ponechá jej v zapsané podobě. Nepodařilo se mi nalézt způsob, jak se s tímto problémem vypořádat. Někdy musím tabulku nebo list zcela znovu přepracovat. Zmíněné "svéhlavosti" se Excel dopouští tehdy, když chápe vzorec jako textový zápis. Důvodů může být více, podstatné je ukázat si jednoduché způsoby nápravy. obr. 1 – Excel někdy nevrací výsledek vzorce

V žádném případě není třeba vytvářet listy znova a dokonce většinou ani nebude nutné vzorce přepisovat. Nežádoucí chování je způsobeno nevhodným formátováním buňky – konkrétně se jedná o typ "text" místo správného typu "obecný".

Nicméně bohužel neplatí jednoduchá úvaha vedoucí ke změně formátu. Pokud formát změníme, vzorec zůstane stále nevyhodnocený. Nevhodný formát chybu způsobuje, jeho změnou se však náprava neuskuteční. obr. 2 – nežádoucí chování je způsobeno formátem "text"

Postup nápravy je následující: - vybereme buňku obsahující vzorec nebo celou oblast buněk - aktivujeme dialogové okno Nahradit (Ctrl+H nebo Úpravy > Nahradit…) - do vstupního pole "Najít" zapíšeme znak "=" (rovnítko, bez uvozovek) - do vstupního pole "Nahradit čím:" zapíšeme znak "=" (rovnítko, bez uvozovek) - nahrazení potvrdíme stiskem tlačítka "Nahradit" nebo "Nahradit vše"

obr. 3 – "vyžádané" vyhodnocení vzorce

4

ExcelTutor http://www.exceltutor.cz Provozuji malou živnost. Téměř veškerou agendu mám vyřešenu v Excelu – pokladní deník, seznam kontaktů, skladové hospodářství. Nyní se pokouším vytvořit v Excelu nabídkový list a ceník pro zboží, které vyrábím a prodávám. Rád bych se vás zeptal, jestli je možné v Excelu nějakým způsobem zařídit, aby buňka, která obsahuje text, byla až do svého kraje vyplněna nějakým znakem, například tečkami. Můj požadavek není nesmyslný, potřeboval bych ve svém ceníku vyplnit jednotlivé řádky vodícími tečkami pro lepší orientaci zákazníků. Nejdříve jsem je ručně zapisoval, pak mi někdo poradil použít funkci OPAKOVAT(), pomocí které se vše zjednodušilo. Takto zadané vodící tečky ale nereagují na rozšiřování sloupce, takže se může stát, že buňka obsahuje text, pak do poloviny tečky a pak prázdný prostor. Můžete mi poradit?

Požadované řešení by mělo být snadné a mělo by umožňovat při změně šířky sloupce automatické doplnění teček nebo jejich odebrání podle potřeby. Je třeba psát makro? Vůbec ne, stačí vytvořit vhodný formát pro zobrazení obsahu buňky.

obr. 1 – vodící tečky usnadňují orientaci v ceníku

Pozn. Toto řešení je základem pro ceník obsahující více než 25 000 položek firmy MARO – http://www.maro.cz

Na obrázku je zobrazeno alternativní řešení, kdy vodícími tečkami vyplníme pomocný sloupec vložený mezi název zboží a jeho cenu. obr. 2 – alternativní řešení

5


obr. 4 – automatický zápis teček

Nastavený formát se postará o to, aby buňky byly tečkami vyplněny bez ohledu na zvolenou šířku sloupce. Musíme ale ještě do každé buňky zapsat znak "." (tečka), který Excel pak bude opakovat.

obr. 3 – vyplnění celého obsahu buňky

Pokud by nám postačovalo řešení pomocí vloženého sloupce, naformátujeme jeho buňky tak, aby po zapsání jediné tečky do buněk sloupce Excel automaticky buňky těmito tečkami vyplnil. K tomu nám poslouží formát zarovnání "vyplnit vodorovně".

Nemusíme samozřejmě do každé buňky vstupovat jednotlivě – existuje mnohem rychlejší postup. Vyznačíme oblast buněk, zapíšeme POUZE JEDINOU TEČKU a potom místo stisku klávesy Enter stiskneme současně dvojici Ctrl+Enter. Tímto způsobem Excel zapisuje zadané znaky do všech buněk vybrané oblasti. Požadované řešení je založeno na použití formátu, jehož syntaxe je

@*. Zápis formátu můžeme samozřejmě i modifikovat, pokud bychom dali přednost vodícím čarám, zapíšeme například obr. 5 – vodící tečky pomocí formátu "@*."

@*-

6


formát

@*-

formát

@" "*-

tento formát způsobí, že vodící znaky se nevyskytují bezprostředně za obsahem buňky

obr. 6 – vodící tečky pomocí formátu "@*." – formát musíme zapsat sami, Excel tento formát neposkytuje

formát

*.@

vodící tečky budou doplněny před obsah buňky Buňky ve sloupci "B" mají nastaven formát @*. Buňky ve sloupci "C" mají nastaven formát obr. 7 –vizuální spojení sousedních sloupců vodícími tečkami

*.# ##0,00 *. – způsobí vyplnění buněk tečkami zleva # ##0,00 – buňky mají nastaven číselný formát s oddělováním tisíců a dvěmi desetinnými místy. Můžeme tedy i nadále podle potřeby provádět s obsahem buněk matematické operace

7

ExcelTutor http://www.exceltutor.cz Sloupec A obsahuje číselné hodnoty, které potřebuji sečíst. Je třeba však sčítat jen některé hodnoty – konkrétně pouze ty, které jsou minimálně rovny číslu 30. Vím, že je třeba zvolit funkci SUMIF, umím ji i používat pro jednoduché podmínky, ale nevím, jak zapsat do kritéria vztah "být větší" a "být menší". Pokud zapíšu vzorec =SUMIF(A2:A9;>=30), Excel hlásí, že napsaný vzorec obsahuje chybu.

obr. 1 – funkce SUMIF akceptuje kritérium ve tvaru textového řetězce nebo číselné hodnoty – matematické operátory však nelze zapisovat přímo =SUMIF(A1:A9;">=" & 30;A1:A9 )

Matematické operátory zapíšeme ve formě řetězce – ohraničíme je uvozovkami a limitní hodnotu připojíme pomocí znaku ampersand (& ).

obr. 2 – matematické operátory zapisujeme ve formě řetězce

Limitní hodnotu (zde číslo 30) můžeme samozřejmě zapsat i pomocí odkazu na buňku, která hodnotu obsahuje, například =SUMIF(A1:A9;">=" & E5;A1:A9 )

Další variantou zápisu, která však není příliš flexibilní z hlediska možných úprav, je zápis celé podmínky (operátorů i limitní hodnoty) ve formě řetězce =SUMIF(A1:A9;">=30";A1:A9 )

8

ExcelTutor http://www.exceltutor.cz Sloupec A obsahuje položky sortimentu - některé z nich se mohou i vícekrát opakovat. Sloupec B obsahuje pořizovací ceny těchto položek. Potřebuji vzorec, který by "prohledal" sloupec A, nalezl určité položky a sečetl jejich pořizovací ceny. Zajímají mě však jen ceny v určitém intervalu. Jde mi tedy o možnost výběru dat pomocí funkce SUMIF ve spojení s několika výběrovými podmínkami.

obr. 1 – logická brána : sčítám pořizovací ceny za položku "KEČUP" za podmínky, že tyto ceny jsou vyšší než 18Kč nebo nižší než 22Kč.

Pozn.Funkce VELKÁ() v první podmínce zajistí, že při porovnávání položek nebude hrát roli velikost písmen. Tento "trik" je velmi užitečný a měli bychom si ho osvojit. Funkce SUMIF() je vhodná pro řešení úloh podmíněného součtu v případě jedné podmínky. Pokud musíme brát v úvahu podmínek více, měli bychom zvážit použití funkce SOUČIN.SKALÁRNÍ() nebo řešení pomocí logické brány.

obr. 2 – řešení úlohy podmíněného součtu pomocí funkce SOUČIN.SKALÁRNÍ()

9

Řešení na obrázku 2 jsou odlišná pouze z hlediska metody převodu pravdivostních hodnot na čísla 0 a 1. V prvním případě je tento převod "vynucen" operací násobení, ve druhém případě operací negace negace – funkce SOUČIN.SKALÁRNÍ() již sama ze své definice násobí vložené podmínky (není tedy nezbytné násobení podmínek zadávat).

ExcelTutor http://www.exceltutor.cz Vím, že pořadí číselných hodnot lze určit pomocí funkce RANK. Potřeboval bych ale nalézt obdobnou funkci nebo kombinaci funkcí, která by dokázala totéž pro soubor nečíselných hodnot. Pracuji se seznamy, které nemohu z důvodů další úpravy seřadit, ale potřebuji určit pořadí každého prvku v seznamu.

Řešením je funkce COUNTIF, která vrací počet buněk z oblasti, které splňují zadané kritérium. Funkci použijeme v následující variantě COUNTIF(;"<="&)

obr. 1 – určení pořadí textových hodnot

Z hlediska použití češtiny je pozitivní, že funkce COUNTIF pracuje ve svých argumentech i s diakritikou.

10

ExcelTutor http://www.exceltutor.cz Udržuji přehled prodejců a provizí, které jsme jim dosud vyplatili. Potřebovala bych zjistit pomocí nějaké funkce nejvyšší částku provize, která byla každému z nich v minulosti vyplacena a VYZNAČIT PŘÍMO DO SEZNAMU, kdy k této události došlo. Navíc bych potřebovala, aby v případě, že nejvyšší hodnota byla vyplacena několikrát, řešení vyznačilo všechny tyto případy.

obr. 1 – přehled vyplacených provizí

11

ExcelTutor http://www.exceltutor.cz {=KDYŽ(B2=MAX(KDYŽ(A$1:A $13=A2; $B$1: $B$13));"Osobní Max";"")} Vnitřní podmínka MAX(KDYŽ(A$1:A$13=A2; $B$1: $B$13)) vyhledá maximum výše provize pro prodejce, jehož jméno je zapsáno v buňce A2. Vnější podmínka KDYŽ(B2=…));"Osobní Max";"")} vyhodnotí, zda výše provize zapsaná v buňce B2 odpovídá maximální hodnotě zjištěné vnitřní podmínkou.

obr. 2 – identifikace osobního maxima vyplacené provize

12

Vzorec, pomocí něhož identifikujeme osobní maximum vyplacené.provize, je maticový. Vzhledem k tomu, že se jedná o maticový vzorec, musíme ho po zapsání do příslušné buňky potvrdit stiskem trojice kláves Ctrl + Shift + Enter. Složené závorky ohraničující vzorec nezapisujeme, Excel je doplní sám.

ExcelTutor http://www.exceltutor.cz Velmi často vytvářím ve svých sešitech různé typy vzorců. U kolegy jsem viděl "trik", kdy zadává odkaz na buňku kliknutím myši a pak stiskem nějaké klávesy změní tento odkaz z relativního na absolutní. Nevíte, jak této změny dosáhnout? F4 – opakovaným stiskem této klávesy měníte cyklicky odkaz (relativní, smíšený s ukotvením řádku, smíšený s ukotvením sloupce, relativní) na vyznačenou oblast vzorce.

Pokud náš vzorec obsahuje absolutní nebo smíšené odkazy, není nutné zapisovat znak $ přímo z klávesnice. Vyznačíme v řádku vzorců příslušnou část vzorce a opakovaným stiskem klávesy F4 cyklicky měníme odkazy na absolutní, smíšený a relativní. .

obr. 1 – změna relativních odkazů na absolutní

Vyznačíme v řádku vzorců příslušnou část vzorce a opakovaným stiskem klávesy F4 cyklicky měníme odkazy na absolutní, smíšený a relativní.

obr. 2 – změna se realizuje stiskem F4

Změnu potvrdíme kliknutím na ikonu "fajfky" (Nike ;)) nebo stiskem klávesy Enter.

obr. 3 – změnu potvrdíme kliknutím na ikonu"Nike" nebo stiskem klávesy Enter

13

ExcelTutor http://www.exceltutor.cz Je možné vytvořit přehled záhlaví sloupců - například ve sloupci "A"? Potřeboval bych pro lepší orientaci v rozsáhlé tabulce mít k dispozici takovýto přehled názvů sloupců, neumím ho ale jednoduše vytvořit.

obr. 1 – transpozice záhlaví sloupců pomocí vzorce

Jeden za způsobů je využití vzorce kombinujícího funkci ODKAZ() a funkci NEPŘÍMÝ.ODKAZ(). Funkce ODKAZ() vytváří při kopírování ve směru sloupce A text ($B$1, $C$1, $D$1…), který je dále předán ve formě argumentu funkci NEPŘÍMÝ.ODKAZ(). Ta vyhodnotí argument jako platný odkaz na konkrétní buńku a vrátí její obsah.

obr. 2 - pomocí Úpravy > Vložit jinak a volby Transponovat převedeme oblast

Nejrychlejší postup je však pomocí zkopírování oblasti záhlaví sloupců B1:J1 (například Ctrl+C) a potom transponováním této oblasti pomocí příkazu Úpravy > Vložit jinak.

14

ExcelTutor http://www.exceltutor.cz Mám zapsaný rozsáhlý přehled kontaktních údajů (cca 100 položek) v nevyhovujícím formátu. Lze se nějak vyhnout ručnímu přepsání těchto dat do nové podoby?

obr. 1 – současná nevyhovující podoba seznamu obr. 2 – struktura, kterou potřebuji

Do volné dvojice sloupců (například "F:G") zapíšeme následující text Jméno Společnost Adresa Telefon

%A2 %B2 %C2 %D2

obr. 3 – příprava na převod seznamu

Vybereme tuto pomocnou oblast VČETNĚ PRÁZDNÉHO ŘÁDKU. Vzhledem k tomu, že budeme kopírovat buňky přes rozsáhlý počet řádků (100 položek x 4 údaje = 400 řádků) usnadní nám práci změna měřítka zobrazení na nižší hodnotu. obr. 4 – zkopírujeme buňky včetně prázdného řádku

Při kopírování sledujeme indikátor, který nám podává informaci o právě převáděné hodnotě. %A10 – znamená, že kopíruje údaj ze sloupce A řádku 10 – tedy jméno z desáté adresy. Jestliže máme 100 adres, kopírování můžeme ukončit při zobrazení údaje %D100 obr. 5 – indikátor %A10 nám ukazuje, že právě převádíme desátou adresu našeho seznamu

15


obr. 6 – převod pomocných textů na platné odkazy

Pomocí Úpravy > Nahradit změníme pomocné texty na platné odkazy (změníme znak "%" za znak "=")

obr. 7 – výsledná podoba převodu seznamu

16

ExcelTutor http://www.exceltutor.cz Často používám v pracovních listech funkci SUMA(). Vkládání provádím buď pomocí průzkumníka funkcí, nebo ručním zápisem. Neexistuje nějaká rychlejší cesta pro zápis této funkce?

obr. 2 – po vložení funkce bychom se měli přesvědčit o rozsahu oblasti, kterou funkce zpracovává

obr. 1 – funkci SUMA() lze rychle vložit pomocí ikony AutoSum ve standardním panelu nástrojů

Můžeme využít vizuálního zobrazení odkazů – aktivujeme buňku se vzorcem a přejdeme do editačního režimu (stiskneme klávesu F2 nebo klikneme do řádku vzorců)

17


obr. 3 – zrychlené zadávání funkce SUMA() do více buněk

obr. 4 - zrychlené zadávání funkce SUMA() do více buněk

Pokud vyznačíme větší oblast dat včetně prázdných buněk, do nichž chceme zapsat funkci SUMA(), …

…Excel zapíše funkci v jednom kroku včetně příslušných odkazů.

Alternativní způsob zápisu funkce SUMA() je pomocí stisku kombinace Alt + "=" ( Alt a klávesa se značkou rovnítka).

18

ExcelTutor http://www.exceltutor.cz Často tisknu velmi rozsáhlé soubory, které při tisku zabírají desítky stran. Stává se mi, že po neúspěšném nastavování různých variant okrajů a ručních konců stránek bych potřebovala začít od začátku "s čistým stolem". Odstranit tedy všechny ruční konce a zalomení stránek. Je to možné nějakým rychlejším způsobem než opětovným odebíráním jednotlivých zalomení stránek?

Excel umožňuje nastavit zobrazení listu "z pohledu tiskárny", kdy není podstatný obsah a formátování buněk, ale rozložení dat na tiskové stránce.

Toto zobrazení aktivujeme pomocí Zobrazit > Konce stránek

obr. 1 – zobrazení listu "z pohledu tiskárny"

V tomto režimu můžeme jednoduchým způsobem manipulovat s tiskovými konci stránek – umístíme kurzor nad silnou modrou čáru představující zalomení stránky, stiskneme levé tlačítko myši a přesunutím kurzoru posouváme i tiskový konec stránky.

obr. 2 - zobrazení listu v režimu "Konce stránek"

19

ExcelTutor http://www.exceltutor.cz Pokud potřebujeme vytvořit další konec stránky, umístíme kurzor do příslušné buňky, stiskneme PRAVÉ tlačítko myši a z kontextového menu vybereme položku "Vložit konec stránky". Obdobným postupem se dostaneme k položce "Obnovit všechny konce stránky", s jejíž pomocí odstraníme všechna uživatelsky zadaná zalomení stránky zůstanou pouze fyzické okraje datové oblasti označené hranicí mezi daty a "šedou zónou" a přerušovanou modrou čárou.

obr. 3 – vložení dalšího ručního konce stránky

obr. 5 – "vyčistění" ručních zalomení

obr. 4 - odstranění všech uživatelem zadaných konců stránky

20

ExcelTutor http://www.exceltutor.cz Při tisku excelovských sešitů bych v některých případech potřeboval začít číslování stránek od jiné hodnoty než 1. Například když tisknu pouze vybrané oblasti listů a chci je seřadit do přílohy dokumentace v jiném pořadí. V nastavení tisku jsem ale žádnou možnost pro změnu číslování nenalezl. Soubor > Vzhled stránky > karta Záhlaví a zápatí > Vlastní zápatí

obr. 1 – nastavení zápatí při tisku

Zadáme kliknutím na ikonu řídící kód pro tiskárnu &[Stránka] a doplníme znaménko "+" spolu s číslem představujícím posun číslování. obr. 2 – nastavení číslování od jiné hodnoty než 1

Pokud například požadujeme, aby Excel čísloval stránky od hodnoty 11, zadáme &[Stránka]+10

21

ExcelTutor http://www.exceltutor.cz Vím, že požadavek, který mám, je zřejmě neřešitelný, přesto se zkusím zeptat, zda je možné v Excelu vytisknout na jednu stránku oblasti z různých listů sešitu a přitom zachovat možnost aktualizace dat? Pracujeme s mnoha výkazy a reporty, z nichž bychom potřebovali vytvořit jakýsi jednostránkový přehled nejdůležitějších informací. V současné době snímáme pomocí PrintScreen jednotlivé listy, v grafickém editoru "vystřihneme" požadované oblasti a ty pak sjednotíme v prázdném excelovském listě. Problém je však v tom, že vždy dojde těsně před odevzdáním tohoto přehledu k aktualizaci některých dat a my musíme vše v časové tísni provádět znova. Že často vznikají chyby z přehlédnutí, nemusím ani zdůrazňovat.

Řešení existuje a to dokonce bez použití programování ve VBA kódu. Postup je založen na spojení několika funkcionalit a ve své podstatě je velice jednoduchý.

obr. 1 – část rozsáhlého přehledu, z něhož chceme aktivně propojit a tisknout důležité oblasti

Požadujeme spojit - z první tabulky sloupec "A" a poslední sloupec "G" - z druhé tabulky sloupec "C" a sloupec "D"

22

ExcelTutor http://www.exceltutor.cz Vybereme 1. oblast určenou pro sjednocení A PŘI SOUČASNÉM STISKU KLÁVESY SHIFT klikneme na nabídku "Úpravy". Zadáme příkaz "Kopírovat obrázek… " a v následujícím dialogovém okně potvrdíme Excelem navržené parametry.

obr. 2 – Excelem navržené parametry ponecháme

obr. 2 – vybereme 1. oblast (A2:A17)

obr. 4 – grafická podoba oblasti dat

obr. 3 – aktivujeme libovolnou buňku mimo oblast dat, držíme stisknutou klávesu SHIFT a z nabídky Úpravy zvolíme příkaz Vložit obrázek

23

Aktivujeme libovolnou buňku mimo oblast dat, držíme stisknutou klávesu SHIFT a z nabídky Úpravy zvolíme příkaz Vložit obrázek. V této chvíli je vložený obrázek pouze statický – jedná se v podstatě o obdobu příkazu PrintScreen s tím rozdílem, že nesnímáme celou obrazovku, ale pouze aktivní oblast pracovního listu.

ExcelTutor http://www.exceltutor.cz V následujícím kroku změníme statické chování obrázku na dynamické propojení s původní oblastí dat. 1) obrázek kliknutím aktivujeme – jeho hranice jsou vyznačeny úchyty pro změnu velikosti 2) do řádku vzorců zapíšeme znak "=" (rovnítko), jako bychom chtěli vkládat vzorec 3) pomocí myši vyznačíme oblast dat – Excel zapíše adresu této oblasti do řádku vzorců 4) potvrdíme propojení obrázku s oblastí dat stiskem klávesy Enter nebo kliknutím na ikonu Zadat ( "zelené zaškrtávátko") v řádku vzorců

obr. 5 – změna statického obrázku na obrázek dynamický s propojením na zdrojová data

Nyní máme nastavené dynamické, aktivní propojení mezi zdrojem dat a zobrazením této oblasti. Pokud dojde ke změně dat, změně formátovacích pravidel apod., grafická kopie bude reagovat na tuto změnu. Dokonce můžeme obrázek zvětšovat nebo zmenšovat, případně měnit adresu odkazované oblasti takovým způsobem, že do obrázku zahrneme více sloupců nebo řádků.

obr. 6 – dynamické, aktivní propojení mezi zdrojem dat a grafickou kopií této oblasti

24


obr. 8 – pomocí panelu nástrojů "Kreslení" jednotlivé obrazy seskupíme

obr. 7 – vytvoříme jednotlivé dynamicky propojené obrazy zdrojových dat

Seskupení obrázků je vhodné v případě jejich přesouvání nebo změně velikosti – skupina se pak chová jednotně.

Opakováním výše uvedeného postupu vytvoříme tolik dynamicky propojených oblastí, kolik je pro tvorbu našeho souhrnu potřeba

25


obr. 9 - příprava pro tisk

Nyní připravíme sloučenou oblast pro tisk - aktivujeme oblast obsahující vytvořené dynamické obrazy zdrojových dat z různých oblastí pracovního sešitu a v dialogovém okně pro tisk (Ctrl+P) nastavíme pomocí volby "Výběr" tisk pouze této oblasti. Zobrazením náhledu stránky před tiskem se můžeme přesvědčit, že naše řešení je opravdu zcela profesionální a funkční.

Výše uvedený postup má značné množství variant: -

-

26

můžeme propojovat nejen různé oblasti jednoho listu, ale dokonce i oblasti z různých listů jednoho sešitu nebo se odkazovat do zcela jiného sešitu můžeme dynamicky propojovat nejenom oblasti dat, ale i například kontingenční tabulky nebo grafy


Použitím dynamicky propojených oblastí lze vytvářet velmi působivé vizuální výstupy, a to zcela bez nutnosti programování v kódu VBA. Například pomocí současného zmenšení měřítka zobrazení listu a zvětšení dynamicky propojené důležité oblasti dat v obrázku ZDŮRAZNIT významné informace.

obr. 11 – pomocí dynamických zobrazení oblastí dat lze vizuálně zvýrazňovat důležité oblasti

27

ExcelTutor http://www.exceltutor.cz Audituji účetní záznamy v naší společnosti. Záznamy z hlavní knihy, které jsem obdržel z účetního oddělení, obsahují číslo účtu pouze pro první záznam. Vzhledem k tomu, že hlavní kniha má rozsah mnoho desítek stran, po vytištění většina stránek postrádá toto číslo účtu a já musím pracně dohledávat jeho výskyt na předcházejících stránkách. Tato podoba přehledu vůbec neumožňuje filtrování nebo třídění jednotlivých záznamů, což je základním předpokladem mojí práce. Celý postup je tak zatížen i velkou pravděpodobností vzniku chyby nebo přehlédnutí důležité informace. Pokud číslo účtu kopíruji "ručně" ve směru sloupce, vzniká riziko, že omylem přepíši při tomto kopírování další hodnotu. Můžete mi poradit nějaký postup doplnění hodnot, který by byl funkční, rychlý a s nízkou pravděpodobností vzniku chyby?

obr. 2 – požadovaný vzhled vhodný pro třídění, filtrování a vyhledávání záznamů

obr. 1 – současný vzhled

28

ExcelTutor http://www.exceltutor.cz Pokud by se jednalo o seznam, který není příliš rozsáhlý, můžeme zkopírovat hodnoty do prázdných buněk následujícím postupem: -

-

obr. 3 – automatické zkopírování vzorce BEZ RIZIKA PŘEPSÁNÍ DALŠÍ HODNOTY

-

aktivujeme první prázdnou buňku a zapíšeme do ní odkaz na předchozí buňku obsahující hodnotu kurzor umístíme nad pravý dolní roh buňky (nad malý černý čtverec v ohraničení buňky) vzhled kurzoru se změní na černý kříž dvakrát rychle klikneme levým tlačítkem myši – Excel automaticky zkopíruje vzorec do dalších buněk a to buď až k další vyplněné buňce, nebo až k první prázdné buňce V LEVÉM sloupci

Pokud kopírujeme vzorce ve sloupci A, Excel se může zachovat různorodě – -

protože levý sloupec neexistuje, ke kopírování nedojde NEBO

obr. 4 – zkopírované hodnoty

-

kopírování se řídí obsahem pravého sloupce

Pokud ke zkopírování nedojde, můžeme vložit před sloupec "A" nový, pomocný sloupec, vyplnit jej libovolnou hodnotou nebo textem, zkopírovat vzorec výše popsaným způsobem a pak pomocný sloupec odstranit.

29

ExcelTutor http://www.exceltutor.cz Protože seznam, který potřebujeme upravit, je velmi rozsáhlý, byl by předchozí postup velmi zdlouhavý. Ukážeme si univerzální způsob doplnění prázdných hodnot, který je plně automatický a lze jej použít i pro soubory zahrnující tisíce záznamů.

Označíme v našem seznamu oblast sloupce "A", otevřeme dialogové okno "Přejít na… " (F5 nebo Ctrl+G), klikneme na tlačítko Jinak…

Zvolíme variantu "Prázdné buňky" a potvrdíme tlačítkem OK obr. 5 – automatický výběr prázdných buněk

Pro další postup je důležité zachovat výběr, který Excel provedl – nesmíme aktivovat žádnou buňku pracovního listu. Klikneme do řádku vzorců, zapíšeme znak rovnítka "=" a kliknutím v našem případě na buňku A2 (nebo zapsáním této adresy) nastavíme odkaz na první vyplněnou buňku. Důležité je, aby tento odkaz byl zapsán v relativní adresaci – tedy bez znaku dolar ($). Nyní zkopírujeme tento odkaz do dalších prázdných buněk – stiskneme Ctrl+Enter. obr. 6 – zapíšeme odkaz na předchozí buňku

30

(Excel umí vložit hodnotu nebo odkaz nebo vzorec do více buněk najednou (pokud jsou vybrány do bloku) – místo klávesy Enter musíme ale stisknout dvojici kláves Ctrl+Enter.)

ExcelTutor http://www.exceltutor.cz Nyní Excel v jediném kroku doplnil požadované hodnoty do všech prázdných buněk sloupce obsahujícího čísla účtů.

obr. 7 – vzorce ve vyplněné oblasti obr. 6 – vložení odkazu do všech prázdných buněk najednou – BEZ NUTNOSTI KOPÍROVAT!

Protože jsme prvotní odkaz zadali v relativní adresaci, Excel tento odkaz doplnil do jednotlivých prázdných buněk v aktualizované podobě. Nyní bychom mohli považovat změnu vzhledu seznamu za dokončenou. Protože ale požadovaný výsledek obsahuje i prázdné řádky oddělující jednotlivé sekce hlavní knihy, musíme ještě řešení doplnit.

obr. 8 – prázdné řádky oddělující jednotlivé sekce, které ještě musíme doplnit

31

ExcelTutor http://www.exceltutor.cz Pomocí vzorce =KDYŽ(A(B2="";C2="";D2= "");"";A2) v pomocném sloupci otestujeme, zda má být vyplněno číslo účtu nebo zda má v seznamu zůstat prázdný řádek Část vzorce A(B2="";C2="";D2="")

obr. 8 – vzorcem v pomocném sloupci otestujeme, zda má být vyplněno číslo účtu nebo zda má v seznamu zůstat prázdný řádek

Vrací hodnotu PRAVDA, pokud buňky ve sloupcích "B:D" jsou prázdné. V tomto případě nezapisujeme číslo účtu, ale ponecháme buňku prázdnou, protože je součástí řádku oddělujícího jednotlivé sekce. Zkopírujeme buňky v pomocném sloupci "F", aktivujeme buňku "A2" a vložíme hodnoty (POUZE HODNOTY) – pomocí Úpravy > Vložit jinak… > volba Hodnoty.

obr. 9 – zkopírujeme buňky z pomocného sloupce "F" do sloupce "A"

32

ExcelTutor http://www.exceltutor.cz Odstraníme pomocný sloupec "F" a můžeme začít pracovat s přehledem záznamů hlavní knihy ve tvaru, který na rozdíl od původní podoby umožňuje vyhledávání a další zpracování záznamů například pomocí filtrování nebo třídění.

obr. 10 – výsledná podoba přehledu vhodná pro další zpracování (např. filtrování, třídění nebo vyhledávání)

33

ExcelTutor http://www.exceltutor.cz Pracuji ve stavební firmě, proto velmi často používám plošné a objemové jednotky, jakož i geometrické stupně. Pokud zapíšu do buňky v Excelu plošnou hodnotu představující výměru (například 15 m2), Excel ji chápe jako text a neumí provádět s takovými buňkami matematické operace. Zatím to řeším tak, že odděluji číselné hodnoty a vyjádření jednotek do různých sloupců. Tento způsob funguje, ale určitě není ideální. Mohl bych nějakým způsobem zapsat do buňky hodnotu i s jednotkou tak, aby Excel s tímto údajem dokázal pracovat jako s číslem? Řešení je velmi snadné a je založeno na vytvoření uživatelského formátu pro zobrazení čísla v buňce. Do buňky tedy budeme zapisovat pouze číselné hodnoty a Excel tak i nadále bude moci provádět podle potřeby nad těmito čísly matematické operace. Formát způsobí, že Excel bude hodnoty zobrazovat společně s požadovanou jednotkou.

obr. 1 – řešení pomocí formátu zobrazí potřebné jednotky dokonce i v součtu zobrazeném ve stavovém řádku

obr. 2 – k číselnému formátu, který je pro zobrazení našich hodnot nejvhodnější, doplníme text " m2"

Použitý formát může být samozřejmě i jiný než "General". Zvolíme ten, který nejvíce vyhovuje požadavkům na zobrazované hodnoty (počet desetinných míst, oddělování tisíců atd.). Můžeme tedy zapsat například formát # ##0 " m2" nebo # ##0,00 " m2"

34

ExcelTutor http://www.exceltutor.cz Popsané řešení má ale stále jeden nedostatek – jak zadáme do textového okna pro zápis formátu horní index?

Pomůže nám například mapa znaků. Pokud ji v Excelu nenalezneme, můžeme si ji zobrazit pomocí Wordu – příkaz "Symbol" nabídky "Vložit" nebo přes ikonu Start v levém dolním rohu Windows (Start > Programy > Příslušenství > Systémové nástroje > Mapa znaků) obr. 3 – mapa znaků nám pomůže při získání indexů

Znak zkopírujeme do schránky, pak zobrazíme v Excelu dialogové okno pro nastavení formátu a znak pomocí Ctrl+V vložíme kam potřebujeme.

Stejným způsobem můžeme vytvořit i formáty pro zobrazení objemových jednotek (20 cm³ , 56,7 dm³) nebo stupňů (11°C ).

obr. 4 – uživatelský formát pro zobrazení Celsiových stupňů

35

ExcelTutor http://www.exceltutor.cz Často pracuji s excelovskými soubory, které vytvořil někdo jiný. Znamená to, že se musím obvykle sám seznámit se strukturou sešitu a pochopit výpočetní logiku. První věcí, kterou dělám, je identifikace všech vzorců. Moje otázka směřuje právě k této činnosti – mohl byste mi poradit postup, jak nalézt vzorce obsažené v sešitu a žádný nevynechat? Dobrý den, rád bych se zeptal, zda je možné nějakým způsobem rozpoznat všechny vzorce a odkazy uložené v listě. Stává se mi, že při úpravách excelovských souborů omylem nevratně smažu důležitý vzorec, protože jsem se domníval, že buňka je prázdná nebo obsahuje již nepotřebnou konstantu. Postup č. 1 Vybereme buď jedinou buňku listu nebo celou oblast, která nás zajímá a pak pomocí F5 (nebo Ctrl+G) > Jinak…> volba Vzorce vybereme a tedy i zobrazíme všechny buňky obsahující vzorce.

obr. 1 – jak identifikovat vzorce ve zdánlivě homogenní oblasti?

obr. 3 – Excel aktivuje všechny buňky obsahující vzorce nebo odkazy

Nevýhodou tohoto řešení je, že jakmile aktivujeme některou buňku, tento výběr zmizí. Tímto postupem ale velmi rychle zjistíme strukturu listu – místa, kde se vzorce vyskytují, jejich poměrné zastoupení a podobně.

obr. 2 – využijeme volbu Vzorce dialogového okna "Přejít na"

36

ExcelTutor http://www.exceltutor.cz Postup č. 2 Pomocí Nástroje > Možnosti > karta Zobrazení > volba Vzorce zobrazíme v aktivním listu místo výsledných hodnot vzorců jejich zápis.

obr. 4 – změna zobrazení vzorců

Postup č. 3 Nahradíme znak "=", kterým zadáváme vzorce nebo odkazy pomocným znakem (Ctrl+H nebo Úpravy > Nahradit) Zvolíme takový znak, který se v listě nevyskytuje – např. # nebo %. obr. 5 – nahradíme znak "=" pomocným znakem

Excel "převede" vzorce a odkazy na prostý text a my nyní takové buňky velmi snadno poznáme. Nevýhodou ale je, že buňky obsahující vzorce nyní nevracejí hodnoty – vidíme sice zápis vzorce, ale nemáme k dispozici aktuální hodnotu, kterou vzorec vrací. Pokud chceme zjistit tuto hodnotu, musíme zpětně nahradit pomocný znak znakem "=". obr. 5 – vzorce a odkazy jsou "převedeny" na prostý text

37

Výhodou proti předchozímu postupu je nesporně to, že můžeme mezi těmito dvěma typy zobrazení převádět pouze vzorce, které nás zajímají.

ExcelTutor http://www.exceltutor.cz Postup č. 4 Následující postup je založený na vyhledávání buněk, které obsahují znak"=". Pomocí Ctrl+F nebo Úpravy > Najít…

obr. 6 – pomocí vyhledávání znaku "=" můžeme identifikovat vzorce a odkazy

aktivujeme dialogové okno pro vyhledávání. Důležitá je správná volba kritérií vyhledávání: -

Oblast = vzorce NE - pouze celé buňky

Pokud bychom pro vyhledávání zadali kritérium Oblast = hodnoty

obr. 7 – buňka B16 obsahuje vzorec, buňka B17 text

Excel by vrátil při vyhledávání buňku B17 ale nenalezl by buňku B16!

Málo známou vlastností vyhledávání v Excelu je schopnost vyhledávat buňky i po skrytí dialogového okna "najít".

obr. 8 – vyhledávat další vzorce můžeme i po uzavření dialogového okna

(Excel 2002 již umožňuje zobrazení všech buněk vyhovujících zadaným kritériím najednou)

Pokud stiskneme po skrytí dialogového okna vyhledávání kombinaci kláves Shift+F4, Excel aktivuje další buňku odpovídající zadanému kritériu – v našem případě další buňku obsahující vzorec nebo odkaz. Pokud stiskneme kombinaci kláves Ctrl+Shift+F4, Excel bude při vyhledávání postupovat od právě aktivní buňky k začátku listu.

38

ExcelTutor http://www.exceltutor.cz Postup č. 5 Poslední způsob využívá mezi uživateli velmi málo známé XLM funkce. Jedná se o funkce, na kterých bylo postaveno programování prostředí Excelu v jeho verzi 95 a které zůstali z důvodu kompatibility zachovány i v novějších verzích. Jejich schopnosti se nepodařilo beze zbytku převést ani do VBA editoru ani do současné platformy NET a proto existují situace, kdy je jejich použití jediným způsobem řešení. Většina uživatelů o nich vůbec neví – jednak proto, že nejsou obsaženy v přehledu funkcí a jednak proto, že je zmiňují ve svých knihách nebo přednáškách většinou pouze "pamětníci". V našem řešení budou XLM funkce tvořit základ pojmenovaných funkcí. Pojmenované funkce a vzorce jsou velmi užitečné – jejich jediný rozdíl proti standardním funkcím a vzorcům je ten, že nemusí být vloženy do konkrétní buňky. Jinak jejich způsob zápisu i chování jsou shodné – vyhodnocují výraz a vrací hodnotu. V našem případě to bude hodnota PRAVDA nebo NEPRAVDA, podle toho, zda buňka bude obsahovat funkci (nebo vzorec či odkaz) nebo konstantu. Využijeme XLM funkci, která se jmenuje O.POLÍČKU() . Tato funkce může nabývat několik desítek významů, které jsou odlišeny argumentem. O.POLÍČKU() s argumentem 48 vrací hodnotu PRAVDA, pokud buňka, na kterou se funkce odkazuje, obsahuje funkci, vzorec nebo odkaz. XLM funkce jsou mimořádně užitečné. Jejich výklad, popisy argumentů a příklady funkčních řešení jsou však stále vzácnější. Jsou zejména mladší generací uživatelů a programátorů nahrazovány kódem VBA, což nemusí být nutně vždy efektivnější řešení. V následujících příkladech si ukážeme několik dalších možností jejich využití v postupech známých z denní praxe uživatele Excelu.

39

ExcelTutor http://www.exceltutor.cz Řešení pomocí XLM funkcí je ideální – v kombinaci s podmíněným formátováním můžeme vizuálně vyznačit buňky obsahující vzorce, přičemž: - vzorce zůstávají funkční - původní formátování buněk zůstane zachováno - v listu můžeme pracovat zcela bez omezení obr. 9 – řešení pomocí kombinace XLM funkcí a podmíněného formátování

Musíme přesně dodržet postup znázorněný na obrázku č. 10:

obr. 10 – vytvoření pojmenovaného vzorce

40

1) aktivujeme buňku A1 – nezáleží na tom, zda obsahuje hodnotu nebo zda je součástí oblasti dat 2) pomocí Vložit > Název > Definovat zobrazíme dialogové okno pro zadávání pojmenovaných oblastí 3) zvolíme nějaký vhodný název dle naší představivosti – například "jefunkce" 4) do textového pole, kam obvykle zapisujeme odkaz na pojmenovanou oblast, zapíšeme vzorec =O.POLÍČKU(48;A1) 5) potvrdíme tlačítkem "Přidat"


obr. 11 – nastavíme způsob zobrazení vzorců pomocí podmíněného formátování

Pomocí podmíněného formátování Formát > Podmíněné formátování… nastavíme požadovaný způsob zobrazení buněk obsahující vzorce – viz obrázek č. 11. V levém textovém poli okna Podmíněné formátování změníme standardní volbu "Hodnota buňky" za variantu "Vzorec" a do pravého textového pole zapíšeme rozhodovací výraz

obr. 12 – výsledné zobrazení vzorců

=jefunkce Nastavíme formátovací pravidlo a potvrdíme pomocí tlačítka OK. Existuje i alternativní způsob zápisu pojmenované funkce pro identifikaci vzorce v buňce: =O.POLÍČKU(48;NEPŘÍMÝ.OD KAZ("RC";0)) V tomto případě není nutné, aby před zápisem vzorce byla aktivní buňka "A1". Vložená funkce NEPŘÍMÝ.ODKAZ() vytváří odkaz na všechny buňky oblasti.

obr. 13 – alternativní způsob zápisu

41

ExcelTutor http://www.exceltutor.cz Rád bych vytvořil nabídkový list produktů naší firmy. Základem je seznam položek vedený v Excelu. Chtěl bych ale v záhlaví této nabídky tisknout na každé stránce logo firmy. Musím si pořídit novou verzi Excelu, nebo mohu zadat logo do záhlaví i v Excelu 97, který stále používám?

Excel od své verze 2002 (XP) umožňuje vkládat grafiku přímo do záhlaví nebo zápatí stránky. Starší verze (97 a 2000), které stále představují většinu implementací Excelu v českém prostředí, tuto možnost nemají. Pomocí jednoduchého triku je ale možné toto omezení obejít a umožnit vložení grafiky – například firemního loga – do záhlaví stránek. obr. 1 – firemní logo vložíme do prvního nebo několika prvních řádků listu

Obrázek představující logo vložíme do prvních řádků pracovního listu.

Řešení založíme na možnosti nastavit opakování tisku řádku nebo více řádku v horní části listu. Soubor > Vzhled stránky > karta List Aktivujeme textové pole "Nahoře opakovat řádky" a pak vybereme v pracovním listu řádky 1-3.

obr. 2 – nastavíme opakování řádků 1, 2, 3

42

ExcelTutor http://www.exceltutor.cz Tímto způsobem můžeme vkládat libovolný grafický formát do horní části všech vytištěných stránek.

Příklad je použit z reálné nabídky firmy MARO, s.r.o. http://www.maro.cz

obr. 3 – logo je na všech stránkách nabídky vytištěno v horní části

43

ExcelTutor http://www.exceltutor.cz V kurzu pro pokročilé uživatele Excelu nám byla prezentována možnost nastavení automatických formátů tabulek. Často ji nyní ve své práci využívám, mám však jednu výhradu. Pokud si nejsem jistá, jak novou tabulku naformátovat, zkouším několik variant, než naleznu tu, která mi vyhovuje. Pokaždé musím "proklikávat" přes příkazy panelu nabídek a navíc i rolovat v přehledu formátů. Ptala jsem se lektora i pracovníků IT v naší firmě, zda je možné tuto činnost zjednodušit, odpověď zněla "ne". Je to pravda? Pravda to naštěstí není. Nicméně platí, že postup, který popíšu, nebývá zmiňován ani v učebnicích Excelu pro pokročilé uživatele. Výsledkem však je přesně to, co hledáte – automatická změna formátování vybrané oblasti doslova na stisk klávesy, bez potřeby proklikávat se jednotlivými volbami a příkazy. Nejdříve si řekněme, že Excel je vybaven řadou funkcionalit, které jsou oku běžného uživatele skryty. Jedním z takovýchto zdrojů jsou příkazy, které Excel při instalaci nezavádí. Právě jeden z těchto příkazů využijeme a doplníme panel nástrojů Standardní novou ikonou Automatický formát. -

klikneme PRAVÝM tlačítkem myši kdekoliv v nějakém panelu nástrojů v horní části pracovního okna (nebo aktivujeme Zobrazit > Panely nástrojů)

obr.1 – přehled panelu nástrojů

-

klikneme na příkaz Vlastní… a v dialogovém okně aktivujeme kartu Příkazy

44


obr. 2 – karta Příkazy dialogového okna Vlastní

-

v levém rozbalovacím seznamu aktivujeme volbu Formát a v pravém okně rolováním dolů nalezneme ikonu Automatický formát…

obr. 3 – ikona Automatický formát se nalézá ve spodní části seznamu

Nyní tuto ikonu umístíme do panelu nástrojů Standardní (nebo do jakéhokoliv jiného, záleží pouze na naší volbě) v horní části pracovního okna Excelu. Přenesení provedeme tak, že: - umístíme kurzor nad ikonu - stiskneme levé tlačítko myši (kurzor změní svoji standardní podobu) - se stále stisknutým levým tlačítkem přeneseme pohybem myši ikonu na vhodné místo

45

ExcelTutor http://www.exceltutor.cz -

levé tlačítko uvolníme pokud nejsme spokojeni s umístěním ikony, jednoduše ji znovu uchopíme a přemístíme jinam. Pokud bychom ikonu chtěli odstranit, uchopíme ji v panelu nástrojů, přemístíme nad pracovní list (do oblasti buněk) a uvolníme tlačítko myši.

obr. 4 – umístění tlačítka / ikony do aktivních panelů nástrojů

Nyní zpět k dotazu. Máme-li nainstalovanou ikonu Automatický formát, můžeme ji použít k postupnému nastavování formátovacích pravidel vybrané oblasti.

obr. 4 – ikona při stisku klávesy Shift umožní postupné změny formátování

46

ExcelTutor http://www.exceltutor.cz Pokud podržíme stisknutou klávesu Shift a zároveň klikáme na ikonu Automatický formát, bude se formátování pracovní oblasti postupně měnit. Jakmile při těchto změnách dojdeme k poslední variantě, formátování se již měnit nebude. "Nastartovat" nový cyklus lze opět tak, že oblast zformátujeme jakýmkoliv formátem (tedy například prvním) z nabídky dostupné pomocí Formát > Automatický formát… a celý postup opakujeme.

47

ExcelTutor

Recommend Documents