Domov › Nastavení › Excel užitečné tipy. Tipy pro Excel. Přidání nových tlačítek na panel nástrojů Rychlý přístup

Excel užitečné tipy. Tipy pro Excel. Přidání nových tlačítek na panel nástrojů Rychlý přístup

je program, který potřebují především účetní a ekonomové. Koneckonců, můžete vytvářet tabulky (sestavy), provádět výpočty libovolné složitosti a vytvářet diagramy. To vše se navíc dá zvládnout bez větších obtíží a neuvěřitelných znalostí.

Program je velký stůl, do kterého můžete zadávat data, tedy tisknout slova a čísla. Pomocí funkcí tohoto programu můžete také provádět různé manipulace s čísly (sčítání, odečítání, násobení, dělení a mnoho dalšího).
Mnoho lidí si myslí, že jsou to jen tabulky. To znamená, že jsou přesvědčeni, že všechny tabulky v počítači jsou kompilovány pouze v tomto programu. Není to tak úplně pravda.
Ano, opravdu je to stůl. Tento program je ale potřeba především pro výpočty. Pokud potřebujete nejen nakreslit tabulku se slovy a čísly, ale také provést některé akce s čísly (sčítání, násobení, výpočet procent atd.), musíte program použít.
Pokud porovnáte program s programem Microsoft Word pak je to samozřejmě složitější. A je lepší začít pracovat v tomto programu poté, co zvládnete Word. Důkladné prostudování zabere hodně času.
Při práci používejte místo myši klávesové zkratky. Pomocí klávesových zkratek můžete otevírat a zavírat dokumenty a listy, procházet dokumentem, provádět různé akce přes buňky, provádět výpočty atd. Použití klávesových zkratek usnadní a zrychlí práci s programem.

ŠIPKY
Posuňte list o jednu buňku nahoru, dolů, doleva nebo doprava.
Klávesová zkratka CTRL+ŠIPKA přesune na hranici aktuální oblasti dat listu.
Klávesová zkratka SHIFT+ŠIPKA rozšířit vybranou oblast buněk o jednu buňku.
Klávesová zkratka CTRL+SHIFT+ŠIPKA rozšíří výběr buněk na poslední neprázdnou buňku ve stejném řádku nebo sloupci jako aktivní buňka, nebo pokud je další buňka prázdná, rozšíří výběr na další neprázdnou buňku.
Pomocí kláves ŠIPKA VLEVO a ŠIPKA VPRAVO Když je pás karet vybrán, můžete vybrat karty vlevo nebo vpravo. Pokud je vybrána nebo otevřena podnabídka, můžete pomocí těchto tlačítek přejít z hlavní nabídky do podnabídky a zpět. Když je vybrána karta pásu karet, tyto klávesy vám pomohou procházet tlačítky karet.
Pomocí kláves ŠIPKA DOLŮ a ŠIPKA NAHORU na otevřít menu nebo podmenu můžete přejít na předchozí nebo další tým. Když je vybrána karta pásu, tyto klávesy se posouvají nahoru nebo dolů ve skupině karet.
V dialogovém okně se klávesy se šipkami přesunou na další nebo předchozí možnost ve vybraném rozevíracím seznamu nebo skupině možností.
Klíč ŠIPKA DOLŮ nebo klávesovou zkratkou ALT+ŠIPKA DOLŮ Otevře vybraný rozevírací seznam.

BACKSPACE
Odebere jeden znak zleva v řádku vzorců. Také odstraní obsah aktivní buňky. V režimu úprav buňky odstraní znak nalevo od textového kurzoru.

DEL
Odstraní obsah buňky (data a vzorce) ve vybrané buňce bez ovlivnění formátování buňky nebo komentářů V režimu úprav buněk odstraní znak napravo od místa vložení.
Klíč KONEC zapne přechod do koncového režimu. V tomto režimu se můžete stisknutím klávesy se šipkou přesunout na další neprázdnou buňku ve stejném sloupci nebo řádku, která se stane aktivní buňkou. Pokud jsou buňky prázdné, klikněte klávesy END a klávesy se šipkami přesunou fokus na poslední buňku ve sloupci nebo řádku. Pokud se navíc na obrazovce zobrazí nabídka nebo podnabídka, vyberte poslední příkaz Jídelní lístek.
Klávesová zkratka CTRL+END se přesune do poslední buňky na listu, která se nachází v nejnižším použitelném řádku použitelného sloupce nejvíce vpravo. Pokud je kurzor v řádku vzorců, CTRL+END přesune kurzor na konec textu.
Klávesová zkratka CTRL+SHIFT+END Rozšíří vybranou oblast buněk na poslední použitou buňku listu (pravý dolní roh). Pokud je kurzor v řádku vzorců, klávesová zkratka CTRL+SHIFT+END způsobí, že veškerý text na řádku vzorců bude vybrán od pozice kurzoru až po konec řádku (toto nemá vliv na výšku řádku vzorců).

ENTER
Dokončí zadávání hodnoty do buňky v řádku vzorců a vybere buňku níže (výchozí).
Ve formuláři pro zadávání dat se přesune do prvního pole dalšího záznamu.
Otevře vybranou nabídku (stiskněte F10 pro aktivaci lišty nabídek) nebo provede vybraný příkaz.
V dialogovém okně provede akci přiřazenou výchozímu tlačítku vybranému v dialogovém okně (toto tlačítko je zvýrazněno tlustým okrajem, často tlačítkem OK).
Klávesová zkratka ALT+ENTER začíná nový řádek v aktuální buňce.
Klávesová zkratka CTRL+ENTER vyplní vybrané buňky aktuální hodnotou.
Klávesová zkratka SHIFT+ENTER dokončí vstup do buňky a přesune textový kurzor do buňky výše.

ESC
Zruší vstup v buňce nebo řádku vzorců.
Zavře nabídku nebo podnabídku, dialogové okno nebo okno se zprávou.
Navíc se zavírá Režim celé obrazovky, pokud se použije, návrat k normální mód, která zobrazuje pás karet a stavový řádek.

DOMOV
Přesune se na začátek řádku nebo listu.
Když je povoleno SCROLL LOCK přejde do buňky vlevo horní roh okno.
Také pokud je na obrazovce zobrazena nabídka nebo podnabídka, vybere první příkaz z nabídky.
Klávesová zkratka CTRL+HOME Přeskočí na buňku na začátku listu.
Klávesová zkratka CTRL+SHIFT+HOME rozšíří vybraný rozsah buněk na začátek listu.

STRÁNKA DOLŮ
Přesune o jednu obrazovku dolů na listu.
Klávesová zkratka ALT+PAGE DOWN posune na listu o jednu obrazovku doprava.
Klávesová zkratka CTRL+PAGE DOWN přesune na další list knihy.
Klávesová zkratka CTRL+SHIFT+PAGE DOWN vede k výběru aktuálního a dalšího listu sešitu.

STRÁNKA NAHORU
Posune na listu o jednu obrazovku nahoru.
Klávesová zkratka ALT+PAGE+UP Posune na listu o jednu obrazovku doleva.
Klávesová zkratka CTRL+PAGE+UP přechody na předchozí list knihy.
Klávesová zkratka CTRL+SHIFT+PAGE+UP vede k výběru aktuálního a předchozího listu sešitu.

PROSTOR
V dialogovém okně klikněte na vybrané tlačítko nebo zaškrtněte a zrušte zaškrtnutí políčka.
Klávesová zkratka CTRL+MEZERNÍK vybere sloupec listu.
Klávesová zkratka SHIFT+MEZERNÍK vybere řádek listu.
Klávesová zkratka CTRL+SHIFT+MEZERNÍK vybere celý list.
Pokud list obsahuje data, klávesová zkratka CTRL+SHIFT+MEZERNÍK zvýrazní aktuální oblast. Stiskněte znovu CTRL+SHIFT+MEZERNÍK vybere aktuální oblast a její celkové řádky. Při třetím stisknutí CTRL+SHIFT+MEZERNÍK Vybere se celý list.
Pokud je vybrán objekt, klávesová zkratka CTRL+SHIFT+MEZERNÍK vybere všechny objekty listu.
Klávesová zkratka ALT+MEZERNÍK Zobrazí nabídku Ovládání oken.

TAB
Posune jednu buňku doprava.
Naviguje mezi nechráněnými buňkami na chráněném listu.
Naviguje na na další parametr nebo skupina možností v dialogovém okně.
SHIFT+TAB Přesune se na předchozí buňku listu nebo předchozí možnost v dialogovém okně.
Klávesová zkratka CTRL+TAB přesune na další kartu dialogového okna.

CTRL+SHIFT+TAB přejde na předchozí kartu dialogového okna.

Rychlé číslování sloupců a řádků v
Sloupce a řádky v tabulkách se musí poměrně často číslovat. Nejjednodušší a rychlý způsob k dosažení takového číslování - vložte původní číslo do počáteční buňky, například „1“, umístěte myš do pravého dolního rohu buňky (kurzor bude připomínat znaménko „plus“) a stiskněte klávesa Ctrl táhněte myší doprava (při číslování sloupců) nebo dolů (v případě číslování řádků). Ale to je potřeba mít na paměti tato metoda nefunguje vždy - vše záleží na konkrétní situaci V tomto případě můžete použít vzorec =ROW() při číslování řádků nebo =COLUMN() při číslování sloupců - efekt bude stejný.

Tisk záhlaví tabulky na každé stránce
Ve výchozím nastavení se záhlaví sloupců zobrazuje pouze na první stránce. Chcete-li je vytisknout na všechny stránky, v nabídce Soubor vyberte položku Vzhled stránky > List a ve skupině Tisk na každou stránku v poli Přes čáry zadejte řádek s popisky sloupců - nadpisy se objeví na všech stránkách dokumentu.

Vyplňte vypočítaný sloupec automaticky
Představte si například typickou prodejní tabulku, která neustále přidává nové produktové řádky a obsahuje jeden nebo více vypočítaných sloupců. Problém je, že při přidávání nových řádků je třeba zkopírovat buňky se vzorci ve vypočítaných sloupcích, což není vždy vhodné - je to lepší tento proces automatizovat. Chcete-li to provést, stačí zkopírovat vzorec do celého sloupce, ale pak do prázdných řádků v odpovídajících buňkách tohoto sloupce zobrazí se nulové nebo chybové zprávy. Tato situace může být pro uživatele znervózňující, proto je lepší tyto buňky dočasně skrýt, aby se zobrazily pouze po vyplnění příslušných řádků. Použijte podmíněné formátování (příkaz Formát > Podmíněné formátování) a nastavte písmo buňky na bílý formát. například, pokud je obsah buňky nulový.

Li mluvíme o tom pouze o nulách, pak to můžete udělat ještě jednodušeji vypnutím zobrazování nulových hodnot pomocí příkazu Nástroje->Možnosti->Zobrazit - zrušte zaškrtnutí políčka Nulové hodnoty v sekci Možnosti okna.

Spuštění kalkulačky Windows z
Pokud při práci v Excelu neustále potřebujete Windows kalkulačka, pak ji není vůbec nutné pokaždé spouštět výběrem příkazu Start > Programy > Příslušenství > Kalkulačka. Excel poskytuje možnost umístit tlačítko kalkulačky na panel nástrojů. Chcete-li to provést, otevřete okno Nastavení pomocí příkazu Nástroje > Nastavení, přejděte na kartu Příkazy a v seznamu Kategorie vyberte Nástroje. Poté vyhledejte ikonu kalkulačky v seznamu příkazů posunutím seznamu příkazů dolů a přetáhněte ikonu na panel nástrojů. Nyní ke spuštění kalkulačky stačí kliknout na toto tlačítko.

Množství pro skupiny buněk v
Předpokládejme, že máte informace o tržbách, dopravě atd. např. na měsíc. Mezisoučty musíte najít na základě údajů v jednom ze sloupců pro každý den zvlášť. Chcete-li to provést, musíte akumulovat částku po řádcích, dokud datum zůstane stejné, a když se změní, měli byste přistoupit k výpočtu nové částky. K vyřešení takového problému se zpravidla uchýlí k vytvoření makra. Existuje však jednodušší způsob - použijte funkci IF (log_expression, value_if_true, value_if_false), určete rovnost data jako logický výraz. Poté musí být součet akumulován, když je podmínka pravdivá, a v opačném případě musí být znovu vytvořen.

Excel není nejpřívětivější program na světě. Běžný uživatel využívá pouze 5 % svých schopností a netuší, jaké poklady skrývají jeho hlubiny. H&F si přečetlo rady excelovského guru a naučilo se porovnávat ceníky a skrývat se tajné informace před zvědavýma očima a vytvářejte analytické zprávy několika kliknutími. (OK, někdy je těchto kliknutí 15.)

Dovozní směnné kurzy

V Excelu si můžete nastavit neustále aktualizovaný směnný kurz.

Z nabídky vyberte záložku "Data".

Klikněte na tlačítko "Z webu".

V zobrazeném okně zadejte do řádku „Adresa“ http://www.cbr.ru a stiskněte Enter.

Po načtení stránky se na tabulkách, které může Excel importovat, objeví černé a žluté šipky. Kliknutím na tuto šipku označíte tabulku pro import (obrázek 1).

Označte tabulku se směnným kurzem a klikněte na tlačítko „Importovat“.

Sazba se objeví v buňkách na vašem listu.

Klikněte na kteroukoli z těchto buněk klikněte pravým tlačítkem myši myši a z nabídky vyberte příkaz „Vlastnosti rozsahu“ (obrázek 2).

V okně, které se zobrazí, vyberte frekvenci aktualizace rychlosti a klikněte na OK.

Super tajný list

Řekněme, že chcete skrýt některé listy v Excelu před ostatními uživateli pracujícími na sešitu. Pokud to uděláte klasickým způsobem- klikněte pravým tlačítkem na štítek listu a klikněte na „Skrýt“ (obrázek 1), poté na název skrytý list bude stále viditelné pro jinou osobu. Aby byl zcela neviditelný, musíte udělat toto:

Stiskněte ALT+F11.

Vlevo se objeví podlouhlé okno (obrázek 2).

V horní části okna vyberte číslo listu, který chcete skrýt.

- Dole na samém konci seznamu najděte vlastnost „Visible“ a nastavte ji na „xlSheetVeryHidden“ (obrázek 3). Nyní o tomto listu nebude vědět nikdo kromě vás.

Zákaz zpětných změn

Máme tabulku (obrázek 1) s prázdnými poli „Datum“ a „Množství“. Manažer Vasya dnes uvede, kolik mrkve prodal za den. Jak mu mohu v budoucnu zabránit v provádění zpětných změn této tabulky?

Umístěte kurzor na buňku s datem a z nabídky vyberte „Data“.

Klikněte na tlačítko "Kontrola dat". Objeví se tabulka.

V rozevíracím seznamu „Typ dat“ vyberte „Jiné“.

Do sloupce „Vzorec“ zapíšeme =A2=DNES().

Zrušte zaškrtnutí políčka "Ignorovat" prázdné buňky“ (obrázek 2).

Klikněte na tlačítko „OK“. Nyní, pokud chce osoba zadat jiné datum, zobrazí se varovná zpráva (obrázek 3).

Můžete také zabránit změně čísel ve sloupci „Množství“. Umístěte kurzor na buňku s množstvím a opakujte algoritmus akcí.

Zákaz zadávání duplikátů

Chcete do ceníku zadat seznam produktů, aby se neopakovaly. Můžete si nastavit zákaz takového opakování. Příklad ukazuje vzorec pro sloupec o 10 buňkách, ale samozřejmě jich může být libovolný počet.

Vybereme buňky A1:A10, které budou podléhat zákazu.

Na záložce „Data“ klikněte na tlačítko „Kontrola dat“.

Na záložce „Parametry“ v rozevíracím seznamu „Typ dat“ vyberte možnost „Jiné“ (obrázek 1).

Do sloupce „Vzorec“ zadejte =COUNTIF($A$1:$A$10,A1)<=1.

Ve stejném okně přejděte na kartu „Chybová zpráva“ a zadejte text, který se zobrazí, když se pokusíte zadat duplikáty (obrázek 2).

Klikněte na "OK".

Selektivní sčítání

Zde je tabulka, ze které můžete vidět, že různí zákazníci od vás za určité částky zakoupili různé produkty několikrát. Chcete znát celkovou částku, za kterou od vás zákazník jménem ANTON koupil bostonské krabí maso.

Do buňky G4 zadáte jméno zákazníka ANTON.

V buňce G5 - název produktu Boston Crab Meat.

Přejděte do buňky G7, kde spočítáte součet, a napište pro něj vzorec (=SUM((C3:C21=G4)*(B3:B21=G5)*D3:D21)). Zpočátku vás děsí svou hlasitostí, ale pokud budete psát postupně, jeho význam bude jasný.

Nejprve zadejte (=SOUČET a otevřete závorky, které budou obsahovat tři faktory.

První multiplikátor (C3:C21=G4) hledá zmínky o ANTONu v zadaném seznamu klientů.

Druhý multiplikátor (B3:B21=G5) dělá totéž pro Boston Crab Meat.

Třetí faktor D3:D21 je zodpovědný za sloupec nákladů, za kterým uzavřeme závorky.

Kontingenční tabulka

Máte tabulku (obrázek 1), která uvádí, jaký produkt konkrétní manažer prodal kterému zákazníkovi a za kolik. Když roste, je výběr jednotlivých dat z něj velmi obtížný. Chcete například zjistit, kolik mrkve se prodalo nebo který manažer dokončil nejvíce objednávek. K vyřešení takových problémů má Excel kontingenční tabulky. K jeho vytvoření potřebujete:

Na kartě „Vložit“ klikněte na tlačítko „Kontingenční tabulka“.

V okně, které se objeví, klikněte na „OK“ (obrázek 2).

Zobrazí se okno, ve kterém můžete vytvořit novou tabulku pouze s údaji, které vás zajímají (obrázek 3).

Prodejní doklad

Pro výpočet celkové částky objednávky můžete postupovat jako obvykle: přidat sloupec, ve kterém je třeba vynásobit cenu a množství, a poté vypočítat částku v tomto sloupci (obrázek 1). Pokud se přestanete bát vzorečků, můžete to udělat elegantněji.

Vyberte buňku C7.

Zadejte =SUM(.

Vyberte rozsah B2:B5.

Zadejte hvězdičku, která je v Excelu znakem násobení.

Vyberte rozsah C2:C5 a zavřete držák (obrázek 2).

Namísto Enteru je při psaní vzorců v Excelu potřeba zadat Ctrl + Shift + Enter.

Porovnání cen

Toto je příklad pro pokročilé uživatele Excelu. Řekněme, že máte dva ceníky a chcete porovnat jejich ceny. Na 1. a 2. obrázku máme ceníky ze 4. května a 11. května 2010. Některé zboží v nich neodpovídá – zde je návod, jak zjistit, o jaké zboží se jedná.

V knize vytvoříme další list a zkopírujeme do něj seznamy zboží z prvního i druhého ceníku (obrázek 3).

Chcete-li se zbavit duplicitních produktů, vyberte celý seznam produktů včetně jeho názvu.

V nabídce vyberte „Data“ - „Filtr“ - „Pokročilý filtr“ (obrázek 4).

V okně, které se objeví, označte tři věci: a) zkopírujte výsledek na jiné místo; b) umístěte výsledek do rozsahu - vyberte místo, kam chcete výsledek zapsat, v příkladu je to buňka D4; c) zaškrtněte políčko „Pouze jedinečné záznamy“ (obrázek 5).

Klikněte na tlačítko „OK“ a počínaje buňkou D4 získáme seznam bez duplikátů (obrázek 6).

Původní seznam produktů mažeme.

Do srovnávacího sloupce zadejte vzorec =D5-C5, který vypočte rozdíl (obrázek 7).

Zbývá pouze automaticky načíst hodnoty z ceníků do sloupců „4. května“ a „11. května“. K tomu použijeme funkci: =VLOOKUP(hledaná_hodnota; tabulka; číslo_sloupce; vyhledávání_intervalu).

- „Hledaná_hodnota“ je řádek, který budeme hledat v tabulce ceníku. Nejjednodušší je hledat produkty podle názvu (obrázek 8).

- „Tabulka“ je pole dat, ve kterých budeme hledat hodnotu, kterou potřebujeme. Mělo by odkazovat na tabulku obsahující ceník ze 4. (obrázek 9).

- „Číslo_sloupce“ je pořadové číslo sloupce v rozsahu, který jsme zadali pro vyhledávání dat. Pro vyhledávání jsme definovali tabulku se dvěma sloupci. Cena je obsažena ve druhém z nich (obrázek 10).

Time-lapse_viewing. Pokud je tabulka, ve které hledáte hodnotu, seřazena vzestupně nebo sestupně, musíte nastavit hodnotu TRUE, pokud není, napište FALSE.

Stáhněte vzorec dolů a nezapomeňte zamknout rozsahy. Chcete-li to provést, umístěte před písmeno sloupce a číslo řádku znak dolaru (to lze provést zvýrazněním požadovaného rozsahu a stisknutím klávesy F4).

Výsledný sloupec odráží rozdíl v cenách u těch položek, které jsou v obou cenících. Pokud se ve výsledném sloupci promítne #N/A, znamená to, že uvedený produkt je dostupný pouze v jednom z ceníků, a proto nelze rozdíl vypočítat.

Oceňování investic

V Excelu můžete vypočítat čistou současnou hodnotu (NPV), tedy součet diskontovaných hodnot toku plateb k datu. V příkladu je hodnota NPV vypočtena na základě jednoho investičního období a čtyř období příjmu (řádek 3 „Peněžní toky“).

Vzorec v buňce B6 vypočítá NPV pomocí finanční funkce: =NPV($B$4,$C$3:$E$3)+B3 (obrázek 1).

V pátém řádku je výpočet diskontovaného toku v každém období nalezen pomocí dvou různých vzorců.

V buňce C5 je výsledek získán díky vzorci =C3/((1+$B$4)^C2) (obrázek 2).

V buňce C6 je stejný výsledek získán pomocí vzorce (=SUM(B3:E3/((1+$B$4)^B2:E2))) (obrázek 3).

Porovnání investičních nabídek

V Excelu můžete porovnat, který ze dvou investičních návrhů je výnosnější. Chcete-li to provést, musíte do dvou sloupců zapsat požadovaný objem investic a výši jejich fázového výnosu a také samostatně uvést diskontní sazbu investice v procentech. Pomocí těchto údajů můžete vypočítat čistou současnou hodnotu (NPV).

Pomocí úplně stejného vzorce se vypočítá čistá současná hodnota jiného investičního projektu.

Nyní je lze porovnávat: kdo má více NPV, tím je projekt ziskovější.

ANTHONY DOMANICO. 11 triků pro pokročilé uživatele Excelu. PCWorld.

Od kontingenčních tabulek po Power View vám znalost těchto funkcí pomůže zařadit se mezi odborníky na tabulkové procesory.

Uživatelé Microsoft Excel jsou rozděleni do dvou kategorií: první si nějakým způsobem poradí s malými tabulkami, zatímco ti, kteří patří do druhé, udivují své kolegy složitými grafy, výkonnou analýzou dat a kouzlem efektivního používání vzorců a maker. Jedenáct technik, na které se v tomto článku podíváme, vám pomůže stát se plnohodnotným členem druhé skupiny.

Funkce SVYHLEDAT vám pomůže shromáždit data roztroušená na různých listech nebo uložená v různých sešitech aplikace Excel a umístit je na jedno místo pro vytváření sestav a počítání výsledků.

Řekněme, že manipulujete s produkty prodávanými v maloobchodě. Každé položce je obvykle přiděleno jedinečné inventární číslo, které lze použít jako odkaz na „VPR“. Vzorec „SVYHLEDAT“ hledá odpovídající identifikátor na jiném listu a odtud dosadí popis produktu, jeho cenu, stav zásob a další údaje na zadané místo v sešitu.

Funkce „VLOOKUP“ pomáhá najít informace ve velkých tabulkách obsahujících např. seznam dostupného sortimentu.

Do vzorce vložte funkci „VLOOKUP“ a v jejím prvním argumentu uveďte požadovanou hodnotu, pomocí které je spojení vytvořeno (1). Druhým argumentem je rozsah buněk, ze kterého se mají vzorky odebrat (2), třetím je číslo sloupce, ze kterého budou data porovnána, a čtvrtým argumentem je FALSE, pokud chcete přesnou shodu, nebo PRAVDA, pokud chcete nejbližší. přiblížení ( 4).

Vytváření grafů

Chcete-li vytvořit graf, zadejte data do Excelu se záhlavími sloupců (1), vyberte Grafy na kartě Vložit (2) a zadejte požadovaný typ grafu. Excel 2013 má kartu Doporučené grafy (3), která obsahuje typy, které odpovídají zadaným datům. Po určení obecné povahy grafu Excel otevře kartu „Návrh“, kde jej lze přesněji nakonfigurovat. Obrovské množství zde přítomných parametrů vám umožňuje dát diagramu vzhled, který potřebujete.

Excel 2013 obsahuje kartu Doporučené grafy, která zobrazuje typy grafů, které odpovídají zadaným datům.

Funkce "IF" a "IFERROR"

„IF“ a „IFERROR“ patří mezi nejoblíbenější funkce v Excelu. Funkce KDYŽ umožňuje definovat podmíněný vzorec, který při splnění podmínky vypočítá jednu hodnotu a při nesplnění jinou hodnotu. Například studentům, kteří ve zkoušce získali 80 bodů nebo více (známky jsou uvedeny ve sloupci C), lze přiřadit atribut „Prospěl“ a těm, kteří získali 79 bodů nebo méně, lze přiřadit atribut „Neprospěl“.

Funkce IFERROR je speciálním případem obecnější funkce IF. Vrátí konkrétní hodnotu (nebo prázdnou hodnotu), pokud během výpočtu vzorce došlo k chybě. Například při provádění funkce SVYHLEDAT na jiném listu nebo tabulce může funkce IFERROR vrátit prázdnou hodnotu v případech, kdy funkce SVYHLEDAT nenalezne požadovaný parametr určený prvním argumentem.

Funkce KDYŽ vypočítá výsledek v závislosti na zadané podmínce.

Kontingenční tabulka

Kontingenční tabulka je v podstatě souhrnná tabulka, která umožňuje spočítat počet položek a vypočítat průměr, součet a další funkce na základě uživatelsky definovaných referenčních bodů. Excel 2013 také zavedl doporučené kontingenční tabulky, což usnadňuje vytváření tabulek, které zobrazují potřebná data.

Chcete-li například vypočítat GPA studentů na základě jejich věku, přesuňte pole „Věk“ do části Řádky (1) a pole hodnocení do části „Hodnoty“ (2). V nabídce hodnot vyberte „Možnosti pole hodnot“ a jako operaci vyberte „Průměr“ (3). Stejným způsobem můžete vypočítat výsledky pro další kategorie, například spočítat počet lidí, kteří zkoušku složili a neuspěli v závislosti na pohlaví.

Kontingenční tabulka je nástroj pro provádění různých souhrnných výpočtů na tabulce podle vybraných referenčních bodů.

Kontingenční graf

Kontingenční grafy mají funkce kontingenčních tabulek i tradičních grafů Excel. Kontingenční graf umožňuje rychle a snadno vytvořit snadno srozumitelnou vizuální reprezentaci komplexních datových sad. Kontingenční grafy podporují mnoho funkcí tradičních grafů, včetně řad, kategorií a dalších. Možnost přidávat interaktivní filtry vám umožňuje manipulovat s vybranými podmnožinami dat.

Excel 2013 zavádí doporučené kontingenční grafy. Otevřete kartu Vložit, přejděte do části Grafy a vyberte Doporučené grafy. Najetím myši na vybranou možnost uvidíte, jak bude vypadat. Chcete-li vytvořit kontingenční graf ručně, klikněte na tlačítko Kontingenční graf na kartě Vložit.

Kontingenční grafy vám pomohou získat snadno srozumitelný pohled na komplexní data.

Okamžité plnění

Snad nejlepší nová funkce v Excelu 2013, Flash Fill, umožňuje efektivně řešit každodenní problémy spojené s rychlým přenosem potřebných bloků informací ze sousedních buněk. V minulosti musel uživatel při práci se sloupcem zastoupeným ve formátu Příjmení, Jméno z něj ručně extrahovat jména nebo najít nějaká velmi složitá řešení.

Nyní předpokládejme, že stejný sloupec s příjmením a jmény je přítomen v Excelu 2013. Jednoduše zadejte jméno první osoby do buňky nejblíže vpravo (1) a na kartě "Domů" vyberte "Vyplnit" a " Okamžité plnění". Excel automaticky extrahuje všechny ostatní názvy a vyplní jimi buňky napravo od těch původních.

„Okamžitá výplň“ umožňuje extrahovat potřebné bloky informací a naplnit jimi sousední buňky.

Rychlá analýza

Nový nástroj pro rychlou analýzu Excelu 2013 vám pomůže rychle vytvářet grafy z jednoduchých sad dat. Po výběru dat se v pravém dolním rohu vybrané oblasti objeví charakteristická ikona (1). Kliknutím na něj přejdete do nabídky „Rychlá analýza“ (2).

Nabídka obsahuje nástroje Formátování, Grafy, Součty, Tabulky a Sparklines. Kliknutím na tyto nástroje zobrazíte funkce, které podporují.

Rychlá analýza urychluje práci s jednoduchými datovými sadami.

Power View

Power View je interaktivní nástroj pro průzkum a vizualizaci dat určený k extrahování a analýze velkých objemů dat z externích zdrojů. V Excelu 2013 pro přístup k funkci Power View přejděte na kartu Vložit (1) a klikněte na tlačítko Zprávy (2).

Sestavy vytvořené pomocí Power View jsou připraveny k prezentaci a podporují čtení a zobrazení na celou obrazovku. Můžete dokonce exportovat jejich interaktivní verzi do PowerPointu. Výukové programy obchodní analýzy dostupné na webu společnosti Microsoft vám pomohou stát se v této oblasti okamžitě odborníkem.

Režim Power View umožňuje vytvářet interaktivní sestavy připravené k prezentaci.

Podmíněné formátování

Pokročilé funkce podmíněného formátování Excelu usnadňují a urychlují zvýraznění potřebných dat. Odpovídající ovládací prvek se nachází na kartě Domů. Vyberte rozsah buněk, které chcete formátovat, a klikněte na tlačítko Podmíněné formátování (2). Podnabídka „Pravidla pro zvýraznění buněk“ (3) uvádí nejčastěji se vyskytující podmínky formátování.

Funkce podmíněného formátování umožňuje zvýraznit požadované oblasti dat s minimálním úsilím.

Transponování sloupců do řádků a naopak

Někdy je potřeba prohodit řádky a sloupce v tabulce. Chcete-li to provést, zkopírujte požadovanou oblast do schránky, klepněte pravým tlačítkem myši na levou horní buňku oblasti, do které vkládáte, a z kontextové nabídky vyberte možnost „Vložit jinak“. V okně, které se objeví na obrazovce, zaškrtněte políčko „Transpose“ a klikněte na OK. Excel udělá zbytek za vás.

Funkce Vložit jinak umožňuje transponovat sloupce a řádky.

Důležité kombinace kláves

Zde uvedené klávesové zkratky jsou užitečné zejména pro rychlou navigaci v excelových tabulkách a provádění běžných operací.

Zde je několik jednoduchých způsobů, které výrazně zlepší vaše používání tohoto základního programu. Po propuštění Excel 2010, Microsoft přidal nesčetné množství , ale nejsou okamžitě patrné. "Tak jednoduché!" vás zve k vyzkoušení technik, které vám zaručeně pomohou ve vaší práci.

20 lifehacků při práci s Excelem

Nyní můžete vybrat všechny buňky jedním kliknutím. Stačí najít kouzelné tlačítko v rohu listu Excelu. A samozřejmě nezapomeňte na tradiční metodu - kombinaci kláves Ctrl + A.
Chcete-li otevřít několik souborů současně, musíte vybrat soubory, které hledáte, a stisknout Enter. Úspora času je zřejmá.

Mezi otevřenými sešity v Excelu můžete snadno procházet pomocí kombinace kláves Ctrl + Tab.
Panel rychlého přístupu obsahuje tři standardní tlačítka, jejich počet snadno změníte na to, které potřebujete. Stačí přejít do nabídky „Soubor“ ⇒ „Možnosti“ ⇒ „Panel nástrojů Rychlý přístup“ a vybrat libovolná tlačítka.

Pokud potřebujete do tabulky přidat diagonální čáru pro speciální rozdělení, stačí kliknout na obvyklou ikonu ohraničení na hlavní stránce Excelu a vybrat „Další ohraničení“.

Když nastane situace, kdy potřebujete vložit několik prázdných řádků, udělejte toto: vyberte požadovaný počet řádků nebo sloupců a klikněte na „Vložit“. Poté stačí vybrat místo, kam chcete buňky přesunout, a máte hotovo.
Pokud potřebujete přesunout jakékoli informace (buňku, řádek, sloupec) v Excelu, vyberte je a najeďte myší na okraj. Poté přesuňte informace tam, kde jsou potřeba. Pokud potřebujete zkopírovat informace, proveďte stejnou operaci, ale se stisknutou klávesou Ctrl.

Nyní je neuvěřitelně snadné odstranit prázdné buňky, které tak často překážejí vaší práci. Můžete se jich zbavit všechny najednou, stačí vybrat požadovaný sloupec a přejít na kartu „Data“ a kliknout na „Filtrovat“. Nad každým sloupcem se objeví šipka dolů. Kliknutím na něj se dostanete do nabídky pro odstranění prázdných polí.

Hledání něčeho se stalo mnohem pohodlnějším. Stisknutím kombinace kláves Ctrl + F najdete v tabulce libovolná data, která potřebujete. Co když se také naučíte používat symboly „?“ a „*“, můžete výrazně rozšířit možnosti vyhledávání. Otazník je jeden neznámý symbol a hvězdička je několik. Pokud přesně nevíte, jaký dotaz zadat, tato metoda určitě pomůže. Pokud potřebujete najít otazník nebo hvězdičku a nechcete, aby Excel místo toho hledal neznámý znak, vložte před ně „~“.
Neopakující se informace jsou snadno zvýrazněny jedinečnými položkami. Chcete-li to provést, vyberte požadovaný sloupec a klikněte na „Upřesnit“ vlevo od položky „Filtr“. Nyní zaškrtněte políčko a vyberte zdrojový rozsah (odkud kopírovat) a také rozsah, do kterého chcete umístit výsledek.

Vytvoření výběru je s novými funkcemi Excelu hračka. Přejděte na položku nabídky „Data“ ⇒ „Ověření dat“ a vyberte podmínku, která určí vzorek. Pokud zadáte informace, které tuto podmínku nesplňují, uživatel obdrží zprávu, že informace jsou nesprávné.

Pohodlné navigace je dosaženo pouhým stisknutím Ctrl + šipka. Díky této klávesové zkratce se snadno dostanete do krajních bodů dokumentu. Například Ctrl + ⇓ umístí kurzor do spodní části listu.
Chcete-li transponovat informace ze sloupce do sloupce, zkopírujte rozsah buněk, které chcete transponovat. Poté klikněte pravým tlačítkem myši na požadované místo a vyberte vložit speciální. Jak vidíte, udělat to již není problém.
V Excelu můžete dokonce skrýt informace! Vyberte požadovaný rozsah buněk, klikněte na „Formátovat“ ⇒ „Skrýt nebo Zobrazit“ a vyberte požadovanou akci. Exotická funkce, že?

Text z více buněk je zcela přirozeně spojen do jedné. Chcete-li to provést, vyberte buňku, do které chcete umístit spojený text, a klikněte na „=“. Poté vyberte buňky a před každou vložte znak „&“, ze kterého převezmete text.
Pouzdro písmen se mění podle vašeho přání. Pokud potřebujete, aby všechna písmena v textu byla velká nebo malá, můžete použít jednu z funkcí speciálně pro to navržených: „VELKÁ“ – všechna písmena jsou velká, „LOWERMAN“ – malá. "PROPNACH" - první písmeno každého slova je velké. Užijte si to pro své zdraví!

Chcete-li na začátku čísla ponechat nuly, stačí před číslo umístit apostrof „‘.
Nyní má váš oblíbený program slova autocorrect – podobně jako autocorrect v chytrých telefonech. Složitá slova již nejsou problémem a lze je nahradit i zkratkami.
Sledujte různé informace v pravém dolním rohu okna. A kliknutím pravým tlačítkem myši tam můžete odstranit nepotřebné čáry a přidat potřebné čáry.
Chcete-li list přejmenovat, jednoduše na něj dvakrát klikněte levým tlačítkem myši a zadejte nový název. Snadné jako koláč!

Ať vám vaše práce přináší více radosti a je jednodušší. Líbil se vám tento informativní článek? Pošlete to svým blízkým!

Toto je skutečná kreativní laboratoř! Tým skutečně stejně smýšlejících lidí, každý odborník ve svém oboru, sjednocený společným cílem: pomáhat lidem. Vytváříme materiály, které opravdu stojí za sdílení, a naši milovaní čtenáři nám slouží jako nevyčerpatelný zdroj inspirace!

Excel není uživatelsky nejpřívětivější program na světě, ale je velmi užitečný.

V kontaktu s

Spolužáci

Běžný uživatel využívá pouze 5 % jeho schopností a má jen malou představu, jaké poklady skrývají jeho hlubiny. Použitím Tipy pro Excel-guru, můžete se naučit porovnávat ceníky, skrývat tajné informace před zvědavýma očima a sestavovat analytické zprávy několika kliknutími.

1. Super tajný list

Řekněme, že chcete skrýt některé listy v Excelu před ostatními uživateli pracujícími na sešitu. Pokud to uděláte klasickým způsobem - klikněte pravým tlačítkem myši na záložku listu a klikněte na „Skrýt“ (obrázek 1), název skrytého listu bude stále viditelný pro jinou osobu.

Aby byl zcela neviditelný, musíte postupovat takto: - Stiskněte ALT+F11 - Vlevo se zobrazí podlouhlé okno - V horní části okna vyberte číslo listu, které chcete skrýt dole, na samém konci seznamu, najít Viditelná vlastnost a udělejte z něj xlSheetVeryHidden.

Nyní o tomto listu nebude vědět nikdo kromě vás.

2. Žádné zpětné změny

Máme tabulku s prázdnými poli „Datum“ a „Množství“. Manažer Vasya dnes uvede, kolik mrkve prodal za den. Jak mu mohu v budoucnu zabránit v provádění zpětných změn této tabulky?

Umístěte kurzor na buňku s datem a z nabídky vyberte „Data“.
.- Klikněte na tlačítko „Kontrola dat“. Objeví se tabulka.
- V rozevíracím seznamu „Typ dat“ vyberte „Jiné“.
- Do sloupce „Vzorec“ zapíšeme =A2=DNES().
- Zrušte zaškrtnutí políčka „Ignorovat prázdné buňky“.
- Klikněte na tlačítko „OK“. Nyní, pokud chce osoba zadat jiné datum, zobrazí se varovná zpráva.
- Můžete také zabránit změně čísel ve sloupci „Množství“.

Umístěte kurzor na buňku s množstvím a opakujte algoritmus akcí.

3. Zákaz zadávání duplikátů

Chcete do ceníku zadat seznam produktů, aby se neopakovaly. Můžete si nastavit zákaz takového opakování.

Příklad ukazuje vzorec pro sloupec o 10 buňkách, ale samozřejmě jich může být libovolný počet.

Vybereme buňky A1:A10, které budou podléhat zákazu.
- Na záložce „Data“ klikněte na tlačítko „Kontrola dat“.
- Na záložce „Parametry“ v rozevíracím seznamu „Typ dat“ vyberte možnost „Jiné“.
- Do sloupce „Vzorec“ zadejte =COUNT IF($A$1:$A$10;A1)<=1.
- Ve stejném okně přejděte na kartu „Chybová zpráva“ a zadejte text, který se zobrazí, když se pokusíte zadat duplikáty. - Klikněte na „OK“.

4. Selektivní sčítání

Zde je tabulka, ze které můžete vidět, že různí zákazníci od vás za určité částky zakoupili různé produkty několikrát.

Chcete znát celkovou částku, za kterou od vás zákazník jménem ANTON koupil bostonské krabí maso.

Do buňky G4 zadáte jméno zákazníka ANTON.
- V buňce G5 - název produktu Boston Crab Meat
.- Přejděte do buňky G7, kde vypočítáte částku, a napište pro ni vzorec (=SUM((C3:C21=G4)*(B3:B21=G5)*D3:D21)).

Zpočátku vás děsí svou hlasitostí, ale pokud budete psát postupně, jeho význam bude jasný.

Nejprve zadejte (=SOUČET a otevřete závorky, které budou obsahovat tři faktory.
- První multiplikátor (C3:C21=G4) hledá zmínky o ANTONu v zadaném seznamu klientů.
- Druhý multiplikátor (B3:B21=G5) dělá totéž s Boston Crab Meat.
- Třetí multiplikátor D3:D21 je zodpovědný za sloupec nákladů, za kterým uzavřeme závorky.

5. Kontingenční tabulka

Máte tabulku, která ukazuje, jaký produkt konkrétní manažer prodal kterému zákazníkovi a za kolik.

Když roste, je výběr jednotlivých dat z něj velmi obtížný. Chcete například zjistit, kolik mrkve se prodalo nebo který manažer dokončil nejvíce objednávek.

K vyřešení takových problémů má Excel kontingenční tabulky.

K vytvoření takové tabulky potřebujete:

Na kartě „Vložit“ klikněte na tlačítko „Kontingenční tabulka“.
- V okně, které se objeví, klikněte na „OK“.
- Zobrazí se okno, ve kterém můžete vytvořit novou tabulku pouze s údaji, které vás zajímají.

6. Prodejní doklad

Pokud se přestanete bát vzorečků, můžete to udělat elegantněji.

Vyberte buňku C7.
- Zadejte =SUM(.
- Vyberte rozsah B2:B5.
- Zadejte hvězdičku, což je v Excelu znak násobení.
- Vyberte rozsah C2:C5 a zavřete držák (obrázek 2).
- Místo Enter je při psaní vzorců v Excelu potřeba zadat Ctrl + Shift + Enter.

7. Srovnání cen

Toto je příklad pro pokročilé uživatele Excelu. Řekněme, že máte dva ceníky a chcete porovnat jejich ceny. Na 1. a 2. obrázku máme ceníky ze 4. května a 11. května 2010.

Některé zboží v nich neodpovídá – zde je návod, jak zjistit, o jaké zboží se jedná.

V knize vytvoříme další list a zkopírujeme do něj seznamy zboží z prvního i druhého ceníku.
- Chcete-li se zbavit duplicitních produktů, vyberte celý seznam produktů včetně jeho názvu.
- V nabídce vyberte „Data“ - „Filtr“ - „Pokročilý filtr“.
- V okně, které se objeví, poznamenejte tři věci:
a) zkopírujte výsledek na jiné místo;
b) umístěte výsledek do rozsahu - vyberte místo, kam chcete výsledek zapsat, v příkladu je to buňka D4;
c) zaškrtněte políčko „Pouze jedinečné záznamy“.

Klikněte na tlačítko „OK“ a počínaje buňkou D4 získáme seznam bez duplikátů.
- Smazat původní seznam produktů.
- Přidání sloupců pro načítání ceníkových hodnot za 4. a 11. května a srovnávacího sloupce.
- Do srovnávacího sloupce zadejte vzorec =D5-C5, který vypočte rozdíl.
- Zbývá pouze automaticky načíst hodnoty z ceníků do sloupců „4. května“ a „11. května“. K tomu použijeme funkci: =VLOOKUP(hledaná_hodnota; tabulka; číslo_sloupce; vyhledávání_intervalu)

.- „Hledaná_hodnota“ je řádek, který budeme hledat v cenové tabulce. Nejjednodušší způsob vyhledávání produktů je podle jejich názvu.
- „Tabulka“ je pole dat, ve kterých budeme hledat hodnotu, kterou potřebujeme. Mělo by odkazovat na tabulku obsahující ceník ze 4.
- „Číslo_sloupce“ je pořadové číslo sloupce v rozsahu, který jsme zadali pro vyhledávání dat. Pro vyhledávání jsme definovali tabulku se dvěma sloupci. Cena je obsažena v druhém z nich.
- Interval_viewing. Pokud je tabulka, ve které hledáte hodnotu, seřazena vzestupně nebo sestupně, musíte nastavit hodnotu TRUE, pokud není, napište FALSE.
- Stáhněte vzorec dolů, nezapomeňte zajistit rozsahy. Chcete-li to provést, umístěte před písmeno sloupce a číslo řádku znak dolaru (to lze provést zvýrazněním požadovaného rozsahu a stisknutím klávesy F4).
- Výsledný sloupec odráží rozdíl v cenách u těch položek, které jsou v obou cenících.

Pokud se ve výsledném sloupci promítne #N/A, znamená to, že uvedený produkt je dostupný pouze v jednom z ceníků, a proto nelze rozdíl vypočítat.

8. Hodnocení investic

V Excelu můžete vypočítat čistou současnou hodnotu (NPV), tedy součet diskontovaných hodnot toku plateb k datu.

V příkladu je hodnota NPV vypočtena na základě jednoho investičního období a čtyř období příjmu (řádek 3 „Peněžní toky“).

Vzorec v buňce B6 vypočítá NPV pomocí finanční funkce: =NPV($B$4,$C$3:$E$3)+B3.
- V pátém řádku se nachází výpočet diskontovaného toku v každém období pomocí dvou různých vzorců.
- V buňce C5 je výsledek získán díky vzorci =C3/((1+$B$4)^C2) (obrázek 2).
- V buňce C6 je stejný výsledek získán pomocí vzorce (=SUM(B3:E3/((1+$B$4)^B2:E2))).

9. Porovnání investičních návrhů

V Excelu můžete porovnat, který ze dvou investičních návrhů je výnosnější.

Chcete-li to provést, musíte do dvou sloupců zapsat požadovaný objem investic a výši jejich fázového výnosu a také samostatně uvést diskontní sazbu investice v procentech.

Pomocí těchto údajů můžete vypočítat čistou současnou hodnotu (NPV).

Do volné buňky musíte zadat vzorec =npv(b3/12,A8:A12)+A7, kde b3 je diskontní sazba, 12 je počet měsíců v roce, A8:A12 je sloupec s číslicemi pro fázovanou návratnost investice je A7 požadovaná výše investice.
- Pomocí přesně stejného vzorce se vypočítá čistá současná hodnota jiného investičního projektu - Nyní je lze porovnat: kdo má více NPV, tím je projekt ziskovější.

Oblíbené v kategorii: