Excel pro pokročilé uživatele. Co by měl vědět pokročilý uživatel Excelu

Věřte, že čím více budete o Excelu vědět, čím lépe pochopíte jeho funkce, tím efektivnější bude vaše práce. Mezitím vám řekneme o pěti nejdůležitějších nástrojích Excelu.

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

Máte tedy tabulku, kde jsou pole „Množství“ a „Datum“ stále prázdná. Manažer Vasilij vyplní tabulku a vy trochu pochybujete o jeho poctivosti. Jak můžeme zajistit, aby Vasilij nemohl provádět změny v tabulce zpětně?

1) Najeďte myší na buňku „Datum“ a vyberte „Data“.

2) Klikněte na položku „Kontrola dat“. Vyskočí znamení.

3) V položce „Typ dat“ klikněte na „Jiné“.

4) Najděte sloupec „Vzorec“ a zadejte tam: =A2=DNES().

5) Najděte položku „Ignorovat prázdné buňky“ a zrušte její zaškrtnutí.

6) Klepněte na OK.

Stejný postup lze opakovat se sloupcem „Množství“ a v zásadě s jakýmkoli jiným sloupcem.

2. Vytvoření zcela skrytého listu

Řekněme, že na jednom excelovém dokumentu pracuje několik lidí najednou. Řekněme, že chcete skrýt konkrétní list. Pokud to uděláte jednoduchým způsobem, uživatel stále uvidí název skrytého listu. Nabízíme vám super tajnou možnost:

1) Napište ALT+F11, vlevo vyskočí podlouhlé okno.

2) V horní části okna označte, který list chcete skrýt.

3) V dolní části okna najděte vlastnost Visible a nastavte ji na režim xlSheetVeryHidden.

Vše! Nyní budete o tomto listu vědět pouze vy.

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

Řekněme, že načítáte seznam produktů do ceníku. Jak se vyhnout opakování? Zde je příklad uspořádání ceníku pro deset buněk; Pokud chcete, můžete si jich vytvořit libovolný počet.

1) Vyberte buňky, na které se bude vztahovat zákaz duplicit. Například takto: A1:A10.

2) Klikněte na záložku „Data“ a vyberte „Kontrola dat“.

3) V rozevíracím seznamu „Typ dat“ na kartě „Parametry“ vyberte „Jiné“.

4) Zadejte =COUNTIF($A$1:$A$10,A1)<=1 в графу «Формула».

5) Přejděte na kartu „Chybová zpráva“ a zadejte text, který se zobrazí při každém záběru.

6) Klepněte na OK.

4. Kontingenční tabulka

Řekněme, že je pro vás obvyklé vytvářet tabulky, které ukazují, kolik produktů určitý manažer prodal. Často se stává, že stůl roste a práce s ním je obtížná. Služba „kontingenční tabulky“ existuje pro usnadnění práce s přerostlým dokumentem. Jak je vytvořit?

1) Klikněte na "Vložit". Je zde položka „Pivot Table“, klikněte na ni.

2) Zobrazí se okno. Klepněte na tlačítko OK.

3) Zobrazí se okno, které vám umožní vytvořit kontingenční tabulku s údaji, které vás zajímají: například nejproduktivnější manažer nebo nejoblíbenější produkt.

5. Selektivní sčítání

Řekněme, že máte tabulku, která ukazuje, že různí zákazníci si od vás koupili různé produkty za různé částky. Chcete znát celkovou částku, za kterou od vás klient jménem VASILIY koupil Vařené solené vepřové maso.

Excel není nejpřívětivější program na světě. Běžný uživatel využívá pouze 5 % jeho schopností a má jen malou představu, jaké poklady skrývají jeho hlubiny. H&F si přečetlo rady excelovského guru a naučilo se porovnávat ceníky, skrývat tajné informace před zvědavýma očima a vytvářet 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 s kurzem a klikněte na tlačítko „Importovat“.

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

Klikněte pravým tlačítkem na kteroukoli z těchto buněk a z nabídky vyberte „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 myši na štítek 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 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 se vyjasní.

Nejprve zadejte (=SUM 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).

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

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 (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ém 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í seřazena, 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 je ve výsledném sloupci uvedeno #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 pomocí dvou různých vzorců nalezen výpočet diskontovaného toku v každém období.

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).

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í ú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ší.

Problém, kterému všichni čelíme, není nedostatek dat; naopak – jde o obrovské množství dat! To je důvod, proč doporučuji používat skvělou funkci Excel - Kontingenční tabulky shrnout a analyzovat vaše data.

Prohlédnout si sílu kontingenčních tabulek je stejně jednoduché jako porovnání původních dat s verzí kontingenční tabulky; Po několika kliknutích jsem byl schopen vidět průměrnou cenu položky podle státu.

Jako finanční profesionál jsem geneticky nakloněn milování tabulek. Ale také jsem zjistil, že používám tabulky k organizaci své kreativní práce a práce na volné noze. Bez ohledu na to, k čemu tabulky používáte, kontingenční tabulka vám může pomoci lépe pochopit vaše data.

Tuto lekci postavíme podle našeho, pro lepší práci s daty. Ukážu vám pět mých oblíbených osvědčených postupů kontingenční tabulky.

V tomto tutoriálu budu používat ukázková data poskytnutá společností Microsoft na této stránce. Tato data použijte k opětovnému vytvoření příkladů nebo k testování funkcí, které předvádím.

Jak používat pokročilé techniky kontingenčních tabulek v Excelu (krátké video)

Rád učím pomocí screencastů, které vám dávají možnost sledovat, jak krok za krokem používám funkce. Podívejte se na krátké video níže, které pokrývá pět mých oblíbených pokročilých funkcí kontingenční tabulky Excel:

5 Pokročilých triků s kontingenčními tabulkami v Excelu

Pokračujte ve čtení a zjistěte, jak používat každou z těchto pěti funkcí v lekci níže, včetně: Průřezy, Časová osa, Zobrazení tabulky, Vypočítaná pole a Vybrané kontingenční tabulky. Začněme.

1. Sekce

Plátky- Nástroj typu point-and-click pro upřesnění dat obsažených v kontingenční tabulce Excelu. Vložte výřez a můžete snadno změnit data obsažená v kontingenční tabulce.

V tomto příkladu jsem vložil řez pro typ Položka. Poté, co kliknu na Batoh, kontingenční tabulka zobrazuje pouze tento parametr v tabulce.

Mnohokrát vytvářím sestavy dashboardu, které budou používat ostatní. Přidání průřezů může koncovému uživateli pomoci upravit sestavu podle svých představ.

Chcete-li přidat průřez, klikněte do kontingenční tabulky a vyhledejte kartu Analýza na pásu karet Excelu.

Zaškrtnutím více políček zahrnete tyto sloupce, z nichž každý bude řezem.

Držet Ctrl na klávesnici vyberte více položek průřezu, které budou zahrnovat více výběrů ze sloupce jako data kontingenční tabulky.

2. Časová osa

Časové osy Jedná se o speciální typ průřezu, který se používá k úpravě dat zahrnutých jako součást dat kontingenční tabulky. Pokud vaše data obsahují data, opravdu musíte vyzkoušet časovou osu jako způsob, jak vybrat data z konkrétních časových období.

Přejděte do sekce Analyzovat > Vložit časovou osu pro přidání časové osy, speciálního typu průřezu, který spravuje data obsažená v pokročilé kontingenční tabulce Excelu na základě data.

Tip: Pokud vám tato funkce nefunguje, ujistěte se, že vaše zdrojová data mají datum ve formátu v tabulce jako datum.

Chcete-li přidat časovou osu, vyberte kontingenční tabulku (klikněte do ní) a poté klikněte na tlačítko Analyzovat > Vložit časovou osu na pásu karet Excel. Ve vyskakovacím okně vyberte sloupec data (nebo více sloupců) a klikněte OK k vytvoření časové osy.

Kliknutím a přetažením v okně Časová osa vyberte konkrétní časové období pro kontingenční tabulku.

Po vložení časové osy můžete kliknout a posouvat úchyty na časové ose, abyste změnili, co je součástí kontingenční tabulky.

Způsob fungování časové osy můžete změnit kliknutím na rozevírací seznam v pravém dolním rohu. Časovou osu můžete změnit, aby se například místo konkrétních dat zobrazovala čtvrtletní nebo roční data.

3. Tabulkový pohled

Standardní zobrazení kontingenčních tabulek v Excelu vypadá jako vodopád; Jak přetahujete více polí do řádků, Excel vytváří více „vrstev“ v datech.

Problém je v tom, že je obtížné vměstnat vzorce do kontingenčních tabulek ve standardním zobrazení. Pokud máte data v kontingenční tabulce, ale chcete je zobrazit jako běžnou tabulku, musíte pro kontingenční tabulku použít zobrazení tabulky.

Vybrat Návrhář > Rozvržení sestavy > Zobrazit ve formě tabulky pracovat s kontingenční tabulkou jako s běžnou tabulkou.

Proč byste měli používat zobrazení tabulky? Převedení dat kontingenční tabulky do klasického tabulkového stylu vám umožní snadněji zadávat datové vzorce nebo je vkládat do samostatné sestavy.

Použijte zobrazení tabulky, aby vaše kontingenční tabulka vypadala více jako standardní řádky a sloupce.

Většinu času raději používám tabulkové zobrazení Excelu. Vypadá spíše jako standardní tabulka a je snazší v ní psát vzorce a pracovat s daty. Mohl bych také vzít tento pohled a vložit jej na novou kartu.

4. Vypočítaná pole

Vypočítaná pole představují způsob, jak přidat sloupec do kontingenční tabulky, který není ve zdrojových datech. Standardní matematické operace můžete použít k vytvoření zcela nových polí, se kterými budete pracovat. Vezměte dva existující sloupce a pomocí matematiky vytvořte úplně nové.

Řekněme, že máme údaje o prodeji v tabulce. U každého zboží máme počet prodaných kusů a prodejní cenu. Toto je ideální čas použít počítané pole k výpočtu součtu objednávky.

Chcete-li začít pracovat s vypočítanými poli, začněte kliknutím do kontingenční tabulky a poté vyhledejte na pásu karet Analýza. Klikněte na nabídku Pole, prvky a množiny a poté vyberte Vypočítané pole.

Použití Analýza > Pole, členové a sady > Vypočítané pole vložit vypočítané pole do kontingenční tabulky.

V novém vyskakovacím okně začněte zadáním názvu vypočítaného pole. v mém případě to zavolám Celková objednávka. Celková hodnota objednávky je množství vynásobené cenou každé jednotky. Poté jsem poklepal na první pole názvu (množství) v seznamu polí v tomto okně.

Pro výpočet celkových nákladů na objednávku jsem vynásobil stávající pole jednotkové ceny ( Jednotková cena) v poli množství ( Množství).

Po přidání názvu tohoto pole přidám znaménko násobení * a poté dvakrát klikněte na celkové množství (množství). Pojďme dál a klikneme OK.

Nyní Excel aktualizoval mou rozšířenou kontingenční tabulku a přidal nové počítané pole. V seznamu polí také uvidíte seznam kontingenčních tabulek, takže je můžete přetáhnout kamkoli do sestavy, kdykoli je budete potřebovat.

Pokud nechcete používat dvousloupcovou aritmetiku, můžete do vypočítaného pole zadat také vlastní aritmetické hodnoty. Pokud bych například chtěl ke každé objednávce jednoduše přidat 5% daň z obratu, mohl bych do vypočítaného pole napsat následující:

Vynásobil jsem celkovou cenu objednávky 1,05, abych vypočítal náklady včetně daně z obratu; můžete použít číselné hodnoty spolu s existujícími poli.

V zásadě mohou vypočítaná pole obsahovat libovolné standardní matematické operátory, jako je sčítání, odčítání, násobení a dělení. Tato vypočítaná pole použijte, pokud nechcete aktualizovat původní data.

Přejít na Vložit > vyzkoušet tuto funkci.

Tato funkce je tak snadno použitelná, že je samozřejmostí. Můžete jej použít k rychlému vytváření pokročilých kontingenčních tabulek v Excelu. Stačí zvýraznit svá data a přejít na kartu Vložit na pásu karet Excel a vyberte Doporučené kontingenční tabulky.

Vyskakovací okno obsahuje mnoho možností pro vytvoření kontingenční tabulky ze zdrojových dat. Kliknutím na miniatury na levé straně tohoto okna zobrazíte doporučené možnosti kontingenční tabulky poskytované aplikací Excel.

Funkce doporučení kontingenční tabulky nabízí mnoho možností, jak analyzovat data jedním kliknutím.

I když se jedná o pokročilou funkci, kterou si málokdo uvědomuje, je to také skvělý nástroj pro spouštění kontingenčních tabulek. Nic vám nebrání upravit kontingenční tabulku tím, že si pole nahradíte sami, ale je to dobrý výchozí bod.

Také se mi tato funkce líbí jako nástroj pro průzkum dat. Pokud nevím, co hledám, když začnu zkoumat data, doporučené kontingenční tabulky Excel jsou často prozíravější než já!

Opakujte a pokračujte v učení (s ještě více lekcemi Excelu)

Tato pokročilá lekce Excelu vám pomohla ponořit se hlouběji do kontingenčních tabulek, jedné z mých oblíbených funkcí pro analýzu a zobrazení tabulky Excel. Kontingenční tabulky používám k nalezení významu ve velkých souborech dat, abych se mohl lépe rozhodovat a přijímat opatření.

Tyto lekce posunou vaše dovednosti s Excelem a kontingenčními tabulkami na další úroveň. Podívejte se na ně:

• ExcelZoo má skvělý přehled o technikách kontingenčních tabulek ve svém článku 10 lekcí pro zvládnutí kontingenčních tabulek (v angličtině).
• My v Envato Tuts+ jsme pokryli kontingenční tabulky výukovým programem pro začátečníky.
• Pro snazší úvod do aplikace Microsoft Excel si prohlédněte naši sérii výukových programů.

Co ještě chcete vědět o kontingenčních tabulkách? Dejte mi vědět své nápady nebo dotazy v komentářích pod tímto návodem.

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 do řad odborníků 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 výpočet součtů.

Ř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 dosadí odtud 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“ (2) na kartě „Vložit“ 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í parametrů zde přítomných 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 čitelný 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ě“. Vyplnit". 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 Excel 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í.

Podmíněné formátování vám 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í.

Vestavěný kancelářský produkt Microsoft Excel je vhodný pro vytváření databází a udržování provozních záznamů. Programové doplňky poskytují uživateli možnost pro pokročilé uživatele automatizovat práci a eliminovat chyby pomocí maker.

V této recenzi se podíváme na užitečné funkce Excelu, které pokročilí uživatelé využívají k řešení různých problémů. Naučíme se pracovat s databází v Excelu. Pojďme se naučit používat makra v praxi. Zvážíme také využití sdílení dokumentů pro společnou (víceuživatelskou) práci.

Jak pracovat s databází v Excelu

Databáze (DB) je tabulka s určitým souborem informací (databáze klientů, skladové zásoby, účtování o příjmech a výdajích atd.). Tato forma prezentace je vhodná pro třídění podle parametrů, rychlé vyhledávání, počítání hodnot podle určitých kritérií atd.

Vytvořme si například databázi v Excelu.

Informace zadané ručně. Poté jsme zvýraznili rozsah dat a naformátovali jej „jako tabulku“. Nejprve bylo možné nastavit rozsah pro databázi („Vložit“ - „Tabulka“). A pak zadejte data.



Najděte v databázi potřebné informace

Vyberte Hlavní menu – záložka „Úpravy“ – „Najít“ (dalekohled). Nebo stiskněte kombinaci kláves Shift + F5 nebo Ctrl + F. Do vyhledávacího pole zadejte požadovanou hodnotu. Pomocí tohoto nástroje můžete nahradit jeden název hodnoty v celé databázi jiným.

Seřaďme podobné hodnoty v databázi

Naše databáze je sestavena na principu „chytré tabulky“ – v pravém dolním rohu každého prvku záhlaví je šipka. Lze jej použít k řazení hodnot.

Zobrazme si zboží, které je na skladě č. 3. Klikněte na šipku v rohu názvu „Sklad“. Vyberte požadovanou hodnotu z rozevíracího seznamu. Po kliknutí na OK máme přístup k informacím o skladu č. 3. A to je vše.

Pojďme zjistit, které produkty stojí méně než 100 rublů. Klikněte na šipku vedle „Cena“. Vyberte „Číselné filtry“ - „Menší než nebo rovno“.

Nastavte parametry řazení.

Po kliknutí na OK:

Poznámka. Pomocí vlastního automatického filtru můžete současně nastavit několik podmínek pro řazení dat v databázi.

Pojďme najít mezivýsledky

Spočítejme si celkovou cenu zboží na skladě č.3.

Pomocí automatického filtru zobrazíme informace o tomto skladu (viz výše).

Do sloupce „Náklady“ zadejte vzorec: =INTERMEDIATE TOTAL(9;E4:E41), kde 9 je číslo funkce (v našem příkladu SUM), E4:E41 je rozsah hodnot.

Všimněte si šipky vedle výsledku vzorce:

Lze jej použít ke změně funkce SUM.

V čem je krása této metody: pokud změníme sklad, získáme novou celkovou hodnotu (na základě nového sortimentu). Vzorec zůstává stejný – jen jsme změnili parametry automatického filtru.

Jak pracovat s makry v Excelu

Makra jsou navržena tak, aby automatizovala rutinní práci. Jedná se o pokyny, které sdělují postup k dosažení konkrétního cíle.

Mnoho maker je veřejně dostupných. Můžete je zkopírovat a vložit do sešitu (pokud pokyny splňují zamýšlený účel). Podívejme se na jednoduchý příklad, jak si makro zaznamenat sami.

Vytvořme pokyn, který automaticky vypočítá cenu zákaznické objednávky:

1. Zkopírujeme tabulku na nový list.
2. Odeberme údaje o množství. Dbejte ale na to, aby tyto buňky měly číselný formát bez desetinných míst (protože zboží je možné objednávat jednotlivě, nikoli v jednotkách hmotnosti).
3. Hodnota „Cena“ musí být ve formátu měny.
4. Odeberme údaje o nákladech. Do sloupce zadáme vzorec: cena * množství. A budeme se množit.
5. Ve spodní části tabulky je „Celkem“ (kolik jednotek zboží bylo objednáno a za jakou cenu). Ještě nižší - „Celkem“.

Tabulka měla následující podobu:

Nyní naučme Microsoft Excel provádět určitý algoritmus.

Jak pracovat v Excelu současně pro více lidí

Aby k databázi v Excelu mělo přístup více uživatelů, musí být otevřena. Pro verze 2007-2010: „Recenze“ - „Přístup ke knize“.

Poznámka. Pro starou verzi 2003: „Služba“ - „Přístup ke knize“.

Ale! Pokud 2 nebo více uživatelů při přístupu k dokumentu změnilo hodnoty stejné buňky, dojde ke konfliktu přístupu.

Nebo budou informace dříve zadané jedním uživatelem odstraněny, když další uživatel zadá novou hodnotu do stejné buňky.

Program nejen zvyšuje produktivitu uživatelů, ale také sleduje jejich chyby!

Bohužel v režimu pro více hráčů existují určitá omezení. Například:

• listy nelze smazat;
• buňky nelze kombinovat nebo oddělovat;
• vytvářet a upravovat makra;
• omezená práce s XML daty (import, mazání map, převod buněk na prvky atd.).

Obecně je ale tento režim velmi atraktivní pro velké a rutinní práce, které se snáze a rychleji dokončují společně. Sdílený přístup výrazně zvyšuje produktivitu zaměstnanců v kanceláři.

V příkladu: sdílení souborů - podrobněji je popsán proces připojení a práce ve víceuživatelském režimu.