Home-theater-designers
Vzorce aplikace Microsoft Excel dokážou téměř cokoli. V tomto článku se pomocí tří užitečných příkladů dozvíte, jak mocné mohou být vzorce aplikace Microsoft Excel a podmíněné formátování.
Seznámení s aplikací Microsoft Excel
Pokryli jsme řadu různých způsobů, jak lépe využívat Excel, jako například kde najdete skvělé šablony aplikace Excel ke stažení , a jak používat Excel jako nástroj pro správu projektů .
Velká část výkonu aplikace Excel se skrývá za vzorci a pravidly aplikace Excel, které vám pomohou automaticky manipulovat s daty a informacemi, bez ohledu na to, jaká data vložíte do tabulky.
Podívejme se, jak můžete pomocí vzorců a dalších nástrojů lépe používat Microsoft Excel.
Podmíněné formátování pomocí vzorců aplikace Excel
Jedním z nástrojů, které lidé nepoužívají dostatečně často, je podmíněné formátování. Pomocí vzorců, pravidel nebo jen několika opravdu jednoduchých nastavení aplikace Excel můžete transformovat tabulku na automatizovaný řídicí panel.
K podmíněnému formátování se dostanete kliknutím na Domov kartu a klikněte na Podmíněné formátování ikona panelu nástrojů.
V části Podmíněné formátování existuje mnoho možností. Většina z nich přesahuje rámec tohoto konkrétního článku, ale většina z nich se týká zvýrazňování, barvení nebo stínování buněk na základě údajů v této buňce.
Toto je pravděpodobně nejběžnější použití podmíněného formátování-například změna barvy buňky na červenou pomocí vzorců, které jsou menší než nebo větší než. Další informace o tom, jak používat příkazy IF v aplikaci Excel.
Jedním z méně používaných nástrojů podmíněného formátování je Sady ikon možnost, která nabízí skvělou sadu ikon, které můžete použít k přeměně datové buňky aplikace Excel na ikonu zobrazení na palubní desce.
Když kliknete na Spravovat pravidla , zavede vás do Správce pravidel podmíněného formátování .
jak stáhnout všechny fotografie z cloudu samsung
V závislosti na datech, která jste vybrali před výběrem sady ikon, se vám v okně Správce zobrazí buňka s právě vybranou sadou ikon.
Když kliknete na Upravit pravidlo , uvidíte dialog, kde se kouzlo děje.
Zde můžete vytvořit logický vzorec a rovnice, které zobrazí požadovanou ikonu řídicího panelu.
Tento příklad řídicího panelu bude zobrazovat čas strávený na různých úkolech versus rozpočtovaný čas. Pokud překročíte polovinu rozpočtu, zobrazí se žluté světlo. Pokud jste úplně přes rozpočet, zčervená.
Jak vidíte, tento řídicí panel ukazuje, že časové rozpočtování není úspěšné.
Téměř polovina času je věnována rozpočtovaným částkám.
Čas se znovu soustředit a lépe řídit svůj čas!
1. Použití funkce VLookup
Pokud chcete používat pokročilejší funkce aplikace Microsoft Excel, zde je pár, které můžete vyzkoušet.
Pravděpodobně jste obeznámeni s funkcí VLookup, která vám umožňuje prohledávat seznam pro konkrétní položku v jednom sloupci a vracet data z jiného sloupce ve stejném řádku jako tato položka.
Funkce bohužel vyžaduje, aby položka, kterou hledáte v seznamu, byla v levém sloupci a data, která hledáte, byla vpravo, ale co když jsou přepnuty?
V níže uvedeném příkladu, co když chci z následujících údajů najít Úkol, který jsem provedl 25. 6. 2018?
V tomto případě prohledáváte hodnoty napravo a chcete vrátit odpovídající hodnotu vlevo.
Pokud čtete fóra pro uživatele aplikace Microsoft Excel, najdete mnoho lidí, kteří říkají, že to není možné s VLookup. K tomu musíte použít kombinaci funkcí Index a Match. To není tak úplně pravda.
VLookup můžete nechat fungovat tak, že do něj vnoříte funkci CHOOSE. V tomto případě bude vzorec aplikace Excel vypadat takto:
'=VLOOKUP(DATE(2018,6,25),CHOOSE({1,2},E2:E8,A2:A8),2,0)'Tato funkce znamená, že chcete ve vyhledávacím seznamu najít datum 25. 6. 2013 a poté vrátit odpovídající hodnotu z indexu sloupců.
V tomto případě si všimnete, že index sloupce je '2', ale jak vidíte, sloupec v tabulce výše je ve skutečnosti 1, že?
To je pravda, ale co děláš s ' VYBRAT Funkce manipuluje se dvěma poli.
Rozsahům dat přiřazujete referenční „indexová“ čísla - data přiřazujete indexu číslo 1 a úkoly indexu číslo 2.
Když tedy ve funkci VLookup napíšete '2', ve skutečnosti odkazujete na index číslo 2 ve funkci CHOOSE. Super, že?
VLookup nyní používá datum sloupec a vrací data z Úkol sloupec, přestože Úkol je vlevo.
Hraješ hry gamecube
Nyní, když znáte tento malý citát, si představte, co jiného můžete dělat!
Pokud se pokoušíte provádět další pokročilé úlohy vyhledávání dat, podívejte se na tento článek o vyhledávání dat v aplikaci Excel pomocí vyhledávacích funkcí .
2. Vnořený vzorec k analýze řetězců
Ještě jeden bláznivý vzorec Excelu pro vás! Mohou nastat případy, kdy buď importujete data do aplikace Microsoft Excel z externího zdroje sestávajícího z řetězce dat s oddělovači.
Jakmile přinesete data, chcete je analyzovat do jednotlivých komponent. Zde je příklad jména, adresy a telefonního čísla odděleného znakem ';' charakter.
Zde je návod, jak můžete tyto informace analyzovat pomocí vzorce v Excelu (zjistěte, zda je můžete duševně sledovat spolu s tímto šílenstvím):
Pro první pole byste pro extrahování položky zcela vlevo (jméno osoby) jednoduše použili ve vzorci funkci DOLEVA.
'=LEFT(A2,FIND(';',A2,1)-1)'Tato logika funguje takto:
- Hledá textový řetězec z A2
- Najde ';' symbol oddělovače
- Odečte jeden pro správné umístění konce této části řetězce
- Zachytí text úplně vlevo k tomuto bodu
V tomto případě je text zcela vlevo „Ryan“. Mise splněna.
3. Vnořený vzorec v Excelu
Ale co ostatní sekce?
Mohou to být jednodušší způsoby, ale protože se chceme pokusit vytvořit nejbláznivější vzorec vnořeného Excelu (který skutečně funguje), použijeme jedinečný přístup.
Chcete -li extrahovat části napravo, musíte vnořit více PRAVÝCH funkcí, abyste chytili část textu až do té první ';' symbol a znovu na něm proveďte funkci DOLEVA. Takto to vypadá při extrahování části adresy s číslem ulice.
'=LEFT((RIGHT(A2,LEN(A2)-FIND(';',A2))),FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))),1)-1)'Vypadá to šíleně, ale není těžké dát se dohromady. Jediné, co jsem udělal, je převzít tuto funkci:
RIGHT(A2,LEN(A2)-FIND(';',A2))A vložil ho do každého místa v VLEVO, ODJET funkce výše tam, kde je 'A2'.
To správně extrahuje druhou část řetězce.
můžete vrátit peníze za hry na ps4
Každá další část řetězce potřebuje další vytvořené hnízdo. Nyní vše, co musíte udělat, je vzít si ' ŽE JO 'rovnice, kterou jste vytvořili v poslední sekci, a vložte ji do nového PRÁVNÉHO vzorce s předchozím PRAVÝM vzorcem, do kterého je vloženo, kde vidíte' A2 '. Jak to vypadá?
(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2))))))Potom musíte vzít TENTO vzorec a umístit jej do původního levého vzorce všude tam, kde je 'A2'.
Konečný vzorec ohýbání mysli vypadá takto:
'=LEFT((RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),FIND(';',(RIGHT((RIGHT(A2,LEN(A2)-FIND(';',A2))),LEN((RIGHT(A2,LEN(A2)-FIND(';',A2))))-FIND(';',(RIGHT(A2,LEN(A2)-FIND(';',A2)))))),1)-1)'Tento vzorec správně extrahuje 'Portland, ME 04076' z původního řetězce.
Chcete -li extrahovat další část, opakujte výše uvedený postup znovu.
Vaše vzorce v Excelu mohou být opravdu smyčkové, ale vše, co děláte, je stříhání a vkládání dlouhých vzorců do sebe a vytváření dlouhých hnízd, která stále fungují.
Ano, toto splňuje požadavek na „šílené“. Ale buďme upřímní, existuje mnohem jednodušší způsob, jak dosáhnout stejné věci pomocí jedné funkce.
Stačí vybrat sloupec s oddělovanými daty a poté pod Data položku nabídky, vyberte Text do sloupců .
Zobrazí se okno, ve kterém můžete řetězec rozdělit podle libovolného oddělovače. Jednoduše zadejte ' ; “a uvidíte, že se podle toho změní náhled vybraných dat.
Na pár kliknutí můžete udělat totéž jako ten šílený vzorec výše ... ale kde je v tom zábava?
Zbláznit se pomocí vzorců aplikace Microsoft Excel
Tak tady to máte. Výše uvedené vzorce dokazují, jak nadprůměrně se člověk může dostat při vytváření vzorců aplikace Microsoft Excel k plnění určitých úkolů.
Někdy tyto vzorce aplikace Excel nejsou ve skutečnosti nejjednodušší (nebo nejlepší) způsob, jak dosáhnout věcí. Většina programátorů vám řekne, abyste to zjednodušili, a to platí stejně pro vzorce aplikace Excel, jako pro cokoli jiného.
Pokud to s používáním Excelu opravdu chcete vážně, přečtěte si naši příručku pro začátečníky k používání Microsoft Excelu. Má vše, co potřebujete k posílení produktivity pomocí Excelu. Poté se podívejte na další cheat list našich základních funkcí Excelu, kde najdete další pokyny.
Fotografický kredit: kues/Depositphotos
Podíl Podíl tweet E-mailem 7 nejlepších finančních funkcí v aplikaci ExcelAť už jste účetní nebo finanční profesionál, měli byste znát tyto vzorce v Excelu.
Číst dále Související témata- Produktivita
- Tabulkové tipy
- Microsoft Excel
- Microsoft Office 2016
- Tipy pro Microsoft Office
- Microsoft Office 2019
Ryan má bakalářský titul z elektrotechniky. Pracoval 13 let v automatizačním inženýrství, 5 let v IT a nyní je Apps Engineer. Bývalý vedoucí redaktor MakeUseOf hovořil na národních konferencích o vizualizaci dat a vystupoval v národní televizi a rozhlase.
Více od Ryana DubehoPřihlaste se k odběru našeho zpravodaje
Připojte se k našemu zpravodaji a získejte technické tipy, recenze, bezplatné elektronické knihy a exkluzivní nabídky!
Kliknutím sem se přihlásíte k odběru