Jak používat Excel jako profesionál: 18 jednoduchých tipů, triků a zkratek pro Excel

Excel se někdy zdá být příliš dobrý na to, aby to byla pravda. Jediné, co musím udělat, je zadat vzorec a téměř všechno, co jsem kdy potřeboval udělat ručně, lze provést automaticky. Potřebujete sloučit dva listy s podobnými údaji? Excel to dokáže. Potřebujete udělat jednoduchou matematiku? Excel to dokáže. Potřebujete spojit informace do více buněk? Excel to dokáže.

Jediným problémem je, že použití pro začátečníky může být obtížné. Zatímco ruční zadávání dat je snadné, může být noční můrou naučit se všechny vzorce a zkratky, které potřebujete k osvojení nástroje. Navíc, i když vzorce používáte správně, vždy existuje šance, že na ně narazíte chybové zprávy. Jednoduše řečeno, Excel může být extrémně obtížný.

Více

Neboj se. V tomto příspěvku se seznámím s nejlepšími tipy, triky a zkratkami, které můžete použít právě teď, abyste svou hru Excel posunuli na vyšší úroveň. Nejsou nutné žádné pokročilé znalosti aplikace Excel.

Co je to Excel?

Microsoft Excel je tabulkový software, který obchodníci, účetní, analytici dat a další profesionálové používají k ukládání, organizaci a sledování datových sad. Je součástí sady produktů Microsoft Office. Alternativy zahrnují Tabulky a čísla Google. Zde najdete další alternativy aplikace Excel.

Chcete-li začít s výukou aplikace Excel, podívejte se na video níže o tom, jak používat Excel pro začátečníky.

Excel se primárně používá k vytváření finančních dokumentů kvůli jeho silným výpočetním schopnostem. Tento software často najdete v účetních kancelářích a týmech, protože umožňuje účetním automaticky zobrazit součty, průměry a součty. S Excelem mohou snadno pochopit data svých firem.

Zatímco Excel je primárně známý jako účetní nástroj, odborníci v jakékoli oblasti mohou používat jeho funkce a vzorce - zejména obchodníci - protože jej lze použít ke sledování jakéhokoli typu dat. Odstraňuje nutnost trávit hodiny a hodiny počítáním buněk nebo kopírováním a vkládáním čísel výkonu. Excel má obvykle zástupce nebo rychlou opravu, která proces zrychluje.

Nejste si jisti, jak můžete ve svém týmu skutečně používat Excel? Zde je seznam dokumentů, které můžete vytvořit:

  • Výkazy zisku a ztráty: Pomocí tabulky aplikace Excel můžete sledovat prodejní aktivity společnosti a finanční zdraví.
  • Rozvahy: Rozvahy patří mezi nejběžnější typy dokumentů, které můžete vytvořit pomocí aplikace Excel. Umožňuje vám získat holistický pohled na finanční situaci společnosti.
  • Kalendář: Můžete snadno vytvořit tabulkový měsíční kalendář pro sledování událostí nebo jiných údajů citlivých na datum.

Zde je několik dokumentů, které můžete vytvořit speciálně pro obchodníky.

Toto je jen malý vzorek typů marketingových a obchodních dokumentů, které můžete vytvořit v aplikaci Excel. Vytvořili jsme rozsáhlý seznam šablon aplikace Excel právě teď můžete použít pro marketing, fakturaci, řízení projektů, rozpočet a další.

Níže si můžete také stáhnout šablony aplikace Excel pro všechny vaše marketingové potřeby.

Po stažení šablon je čas začít software používat. Pojďme nejprve vysvětlit základy.

Základy aplikace Excel

Pokud právě začínáte s Excelem, doporučujeme vám seznámit se s několika základními příkazy. Jsou to věci jako:

  • Vytvoření nové tabulky od nuly.
  • Provádění základních výpočtů, jako je sčítání, odčítání, násobení a dělení.
  • Psaní a formátování textu a nadpisů sloupců.
  • Pomocí funkcí automatického vyplňování aplikace Excel.
  • Přidávání nebo mazání jednotlivých sloupců, řádků a tabulek. Níže se podíváme na to, jak přidat věci, jako je více sloupců a řádků.
  • Udržujte viditelné názvy sloupců a řádků při procházení kolem nich v tabulce, abyste věděli, jaká data vyplňujete, když se pohybujete dále v dokumentu.

Chcete-li se podrobně ponořit do těchto základů, podívejte se na naše komplexní průvodce Microsoft Excel.

V duchu efektivnější práce a vyhýbání se zdlouhavé manuální práci je zde několik vzorců a funkcí aplikace Excel, které potřebujete znát.

Excel Vzorce

Je snadné se nechat ohromit širokou škálou Formule aplikace Excel které můžete použít k pochopení svých dat. Pokud teprve začínáte používat Excel, můžete se při provádění některých složitých funkcí spolehnout na následující vzorce - aniž byste přidali na složitosti své cesty učení.

  • Rovné znaménko: Před vytvořením libovolného vzorce budete muset napsat znaménko rovná se (=) v buňce, kde se má výsledek zobrazit.
  • Přidání: Chcete-li přidat hodnoty dvou nebo více buněk, použijte + podepsat. Příklad: = C5 + D3.
  • Odčítání: Chcete-li odečíst hodnoty dvou nebo více buněk, použijte - podepsat. Příklad: = C5-D3.
  • Násobení: Chcete-li vynásobit hodnoty dvou nebo více buněk, použijte * podepsat. Příklad: = C5 * D3.
  • Divize: Chcete-li rozdělit hodnoty dvou nebo více buněk, použijte / podepsat. Příklad: = C5 / D3.

Když vše spojíte dohromady, můžete vytvořit vzorec, který sčítá, odčítá, násobí a dělí vše v jedné buňce. Příklad: =(C5-D3)/((A5+B6)*3).

U složitějších vzorců budete muset kolem výrazů použít závorky, abyste zabránili náhodnému použití Pořadí operací PEMDAS. Pamatujte, že ve vzorcích můžete použít obyčejná čísla.

Funkce aplikace Excel

Funkce aplikace Excel automatizují některé úkoly, které byste použili v typickém vzorci. Například místo použití + Chcete-li přidat celou řadu buněk, použijte funkci SUMA. Podívejme se na několik dalších funkcí, které pomohou automatizovat výpočty a úkoly.

  • SOUČET: Funkce SUM automaticky sčítá řadu buněk nebo čísel. Chcete-li dokončit součet, zadali byste počáteční buňku a poslední buňku s dvojtečkou mezi nimi. Vypadá to takto: SUM (Cell1: Cell2). Příklad: = SUM (C5: C30).
  • PRŮMĚRNÝ: Funkce PRŮMĚR zprůměruje hodnoty řady buněk. Syntaxe je stejná jako funkce SUMA: PRŮMĚR (buňka 1: buňka 2). Příklad: = PRŮMĚR (C5: C30).
  • IF: Funkce IF umožňuje vrátit hodnoty na základě logického testu. Syntaxe je následující: IF (logical_test, value_if_true, [value_if_false]). Příklad: = IF (A2> B2, „Over Budget“, „OK“).
  • VLOOKUP: Funkce VLOOKUP vám pomůže vyhledat cokoli v řádcích vašeho listu. Syntaxe je: VLOOKUP (vyhledávací hodnota, pole tabulky, číslo sloupce, přibližná shoda (TRUE) nebo přesná shoda (FALSE)). Příklad: = VLOOKUP ([@ Attorney], tbl_Attorneys, 4, FALSE).
  • INDEX: Funkce INDEX vrací hodnotu z rozsahu. Syntaxe je následující: INDEX (pole, číslo řádku, [číslo_sloupce]).
  • MATCH: Funkce MATCH vyhledá určitou položku v rozsahu buněk a vrátí její pozici. Lze jej použít v tandemu s funkcí INDEX. Syntaxe je: MATCH (lookup_value, lookup_array, [match_type]).
  • COUNTIF: Funkce COUNTIF vrací počet buněk, které splňují určitá kritéria nebo mají určitou hodnotu. Syntaxe je: COUNTIF (rozsah, kritéria). Příklad: = COUNTIF (A2: A5, „London“).

Dobře, připraveni se dostat do hlouposti? Pojďme na to. (A všem fanouškům Harryho Pottera venku ... jste předem vítáni.)

Poznámka: GIFy a vizuály pocházejí z předchozí verze aplikace Excel. Pokud je to možné, byla kopie aktualizována, aby poskytovala pokyny pro uživatele novější i starší verze aplikace Excel.

1. Použijte kontingenční tabulky k rozpoznání a pochopení dat.

Kontingenční tabulky se používají k reorganizaci dat v tabulce. Nezmění data, která máte, ale mohou shrnout hodnoty a porovnat různé informace v tabulce podle toho, co chcete.

Pojďme se podívat na příklad. Řekněme, že se chci podívat na to, kolik lidí je v Bradavicích v každém domě. Možná si myslíte, že nemám příliš mnoho dat, ale pro delší soubory dat to přijde vhod.

K vytvoření kontingenční tabulky jdu na Data > Pivot Table. Pokud používáte nejnovější verzi aplikace Excel, přejděte na Vložit > Pivot Table. Excel automaticky naplní vaši kontingenční tabulku, ale vždy se můžete změnit v pořadí dat. Pak máte na výběr ze čtyř možností.

  • Filtr zpráv: To vám umožní podívat se pouze na určité řádky v datové sadě. Například kdybych chtěl vytvořit filtr podle domu, mohl bych se rozhodnout zahrnout pouze studenty do Nebelvíru místo všech studentů.
  • Sloupcové štítky: Toto by byly vaše záhlaví v datové sadě.
  • Řádkové štítky: Mohly by to být vaše řádky v datové sadě. Štítky řádků i sloupců mohou obsahovat data z vašich sloupců (např. Křestní jméno lze přetáhnout na štítek řádku nebo sloupce - záleží jen na tom, jak chcete data zobrazit.)
  • Hodnota: Tato část vám umožňuje podívat se na svá data odlišně. Místo toho, abyste pouze vtažili jakoukoli číselnou hodnotu, můžete se svými daty sčítat, počítat, průměrovat, max, min, počítat čísla nebo provádět několik dalších manipulací. Ve skutečnosti ve výchozím nastavení vždy, když přetáhnete pole na hodnotu, vždy se to počítá.

Vzhledem k tomu, že chci spočítat počet studentů v jednotlivých domech, přejdu k nástroji pro tvorbu kontingenčních tabulek a přetáhnu sloupec Dům na štítky řádků a hodnoty. Tím se sečte počet studentů přidružených k jednotlivým domům.Vytvoření kontingenční tabulky aplikace Excel

2. Přidejte více než jeden řádek nebo sloupec.

Při hraní s vašimi daty možná zjistíte, že neustále potřebujete přidávat další řádky a sloupce. Někdy možná budete muset přidat stovky řádků. Dělat to jeden po druhém by bylo velmi zdlouhavé. Naštěstí vždy existuje jednodušší způsob.

Chcete-li do tabulky přidat více řádků nebo sloupců, zvýrazněte stejný počet již existujících řádků nebo sloupců, které chcete přidat. Poté klikněte pravým tlačítkem a vyberte „Vložit“.

V níže uvedeném příkladu chci přidat další tři řádky. Zvýrazněním tří řádků a kliknutím na tlačítko Vložit mohu rychle a snadno přidat další tři prázdné řádky do mé tabulky.

vložte mezery do Excelu

3. Použijte filtry ke zjednodušení dat.

Když se díváte na velmi velké datové sady, obvykle se nemusíte dívat na každý řádek současně. Někdy se chcete podívat pouze na data, která odpovídají určitým kritériím.

Tam přicházejí filtry.

Filtry vám umožňují zkrátit data, abyste se mohli podívat pouze na určité řádky najednou. V aplikaci Excel lze do každého sloupce v datech přidat filtr - a odtud pak můžete vybrat, které buňky chcete zobrazit najednou.

Podívejme se na příklad níže. Přidejte filtr kliknutím na kartu Data a výběrem možnosti Filtr. Kliknutím na šipku vedle záhlaví sloupců budete moci zvolit, zda chcete, aby vaše data byla uspořádána vzestupně nebo sestupně, a také konkrétní řádky, které chcete zobrazit.

V mém příkladu Harryho Pottera řekněme, že chci vidět pouze studenty v Nebelvíru. Výběrem nebelvírského filtru ostatní řádky zmizí.

Filtry aplikace Excel v akciPro Tip: Když je zapnutý filtr, zkopírujte a vložte hodnoty do tabulky a proveďte další analýzu v jiné tabulce.

4. Odstraňte duplicitní datové body nebo sady.

Větší soubory dat mají obvykle duplicitní obsah. Můžete mít seznam více kontaktů ve společnosti a chcete vidět pouze počet společností, které máte. V takových situacích je odstranění duplikátů velmi užitečné.

Chcete-li duplikáty odebrat, zvýrazněte řádek nebo sloupec, ze kterého chcete duplikáty odebrat. Poté přejděte na kartu Data a vyberte možnost „Odebrat duplikáty“ (která je ve starší verzi aplikace Excel v podnadpisu Nástroje). Zobrazí se vyskakovací okno, které potvrdí, s jakými daty chcete pracovat. Vyberte možnost Odebrat duplikáty a můžete začít.

Odebrání duplikátů v aplikaci Excel

Tuto funkci můžete také použít k odebrání celého řádku na základě hodnoty duplicitního sloupce. Pokud tedy máte tři řádky s informacemi o Harrym Potterovi a potřebujete vidět pouze jeden, můžete vybrat celý soubor dat a poté odstranit duplikáty na základě e-mailu. Váš výsledný seznam bude mít pouze jedinečná jména bez duplikátů.

5. Transponujte řádky do sloupců.

Pokud máte v tabulce řádky s daty, můžete se rozhodnout, že skutečně chcete položky v jednom z těchto řádků transformovat do sloupců (nebo naopak). Kopírování a vložení každé jednotlivé hlavičky by zabralo spoustu času - ale to, co vám umožňuje funkce transpozice, je jednoduše přesunout data řádků do sloupců nebo naopak.

Začněte zvýrazněním sloupce, který chcete transponovat do řádků. Klikněte na něj pravým tlačítkem myši a vyberte možnost Kopírovat. Dále vyberte buňky v tabulce, kde má začít váš první řádek nebo sloupec. Pravým tlačítkem klikněte na buňku a poté vyberte „Vložit jinak“. Zobrazí se modul - dole se zobrazí možnost transponovat. Zaškrtněte toto políčko a vyberte OK. Váš sloupec bude nyní převeden na řádek nebo naopak.

Nástroj pro transpozici v aplikaci Excel

V novějších verzích aplikace Excel se místo vyskakovacího okna zobrazí rozevírací seznam.

Nástroj pro transpozici aplikace Excel v novějších verzích

6. Rozdělte textové informace mezi sloupce.

Co když chcete rozdělit informace, které jsou v jedné buňce, do dvou různých buněk? Například možná budete chtít vytáhnout něčí název společnosti prostřednictvím jeho e-mailové adresy. Nebo možná chcete pro šablony e-mailového marketingu rozdělit celé jméno někoho na jméno a příjmení.

Díky Excelu je možné obojí. Nejprve zvýrazněte sloupec, který chcete rozdělit. Dále přejděte na kartu Data a vyberte možnost „Text do sloupců“. Zobrazí se modul s dalšími informacemi.

Nejprve musíte vybrat možnost „Oddělovač“ nebo „Pevná šířka“.

  • „Oddělený“ znamená, že chcete rozdělit sloupec na základě znaků, jako jsou čárky, mezery nebo tabulátory.
  • „Pevná šířka“ znamená, že chcete vybrat přesné umístění ve všech sloupcích, u kterých chcete, aby došlo k rozdělení.

V níže uvedeném příkladu vybereme možnost „Oddělovač“, abychom mohli oddělit celé jméno od jména a příjmení.

Pak je čas vybrat oddělovače. Může to být tabulátor, středník, čárka, mezera nebo něco jiného. („Něco jiného“ může být například znak „@“ použitý v e-mailové adrese.) V našem příkladu si vybereme mezeru. Excel vám poté zobrazí náhled toho, jak budou vaše nové sloupce vypadat.

Pokud jste s náhledem spokojeni, stiskněte tlačítko „Další“. Tato stránka vám umožní vybrat Pokročilé formáty, pokud se rozhodnete. Po dokončení klikněte na „Dokončit“.

Nástroj Excel pro převod textu na sloupec

7. Použijte vzorce pro jednoduché výpočty.

Kromě provádění docela složitých výpočtů vám Excel může pomoci provádět jednoduchou aritmetiku, jako je sčítání, odčítání, násobení nebo dělení libovolných dat.

  • Chcete-li přidat, použijte znaménko +.
  • Chcete-li odečíst, použijte znaménko -.
  • K znásobení použijte znak *.
  • K rozdělení použijte znak /.

Můžete také použít závorky, abyste zajistili, že budou nejprve provedeny určité výpočty. V níže uvedeném příkladu (10 + 10 * 10) byla druhá a třetí 10 vynásobena společně před přidáním dalších 10. Pokud bychom to však udělali (10 + 10) * 10, první a druhá 10 by se přidali jako první .

Excel jednoduché vzorce v akci

8. Získejte průměr čísel ve svých buňkách.

Pokud chcete průměr ze sady čísel, můžete použít vzorec = PRŮMĚR (Cell1: Cell2). Chcete-li shrnout sloupec čísel, můžete použít vzorec = SUMA (buňka1: buňka2).

9. Použijte podmíněné formátování, aby buňky automaticky změnily barvu na základě dat.

Podmíněné formátování umožňuje změnit barvu buňky na základě informací v buňce. Například pokud chcete označit určitá čísla, která jsou nadprůměrná nebo v horních 10% dat v tabulce, můžete to udělat. Pokud chcete barevně označit společné znaky mezi různými řádky v aplikaci Excel, můžete to udělat. To vám pomůže rychle vidět informace, které jsou pro vás důležité.

Chcete-li začít, zvýrazněte skupinu buněk, ve kterých chcete použít podmíněné formátování. Poté vyberte v nabídce Domů možnost „Podmíněné formátování“ a z rozevírací nabídky vyberte svou logiku. (Pokud si přejete něco jiného, ​​můžete si také vytvořit vlastní pravidlo.) Otevře se okno, které vás vyzve k poskytnutí dalších informací o vašem pravidle formátování. Až budete hotovi, klikněte na „OK“ a výsledky by se měly automaticky zobrazit.

Podmínkové formátování aplikace Excel

10. Použijte vzorec IF Excel k automatizaci určitých funkcí aplikace Excel.

Někdy nechceme počítat, kolikrát se hodnota objeví. Místo toho chceme do buňky vložit různé informace, pokud existuje odpovídající buňka s těmito informacemi.

Například v níže uvedené situaci chci udělit deset bodů každému, kdo patří do nebelvírského domu. Místo ručního zadávání desetin vedle jména každého nebelvírského studenta mohu pomocí vzorce IF Excel říci, že pokud je student v nebelvírském, měl by získat deset bodů.

Vzorec je: IF (logical_test, value_if_true, [value_if_false])

Příklad zobrazený níže: = IF (D2 = „Nebelvír“, „10“, „0“)

Obecně by vzorec byl IF (Logický test, hodnota true, hodnota false). Pojďme se zabývat každou z těchto proměnných.

  • Logický_test: Logický test je částí příkazu „IF“. V tomto případě je logika D2 = „Nebelvír“, protože chceme zajistit, aby buňka odpovídající studentovi řekla „Nebelvír“. Ujistěte se, že jste zde uvedli Nebelvír do uvozovek.
  • Hodnota_if_True: To je to, co chceme, aby buňka zobrazila, pokud je hodnota pravdivá. V tomto případě chceme, aby buňka zobrazovala „10“, což znamená, že student získal 10 bodů. Uvozovky používejte pouze v případě, že chcete, aby výsledkem byl text místo čísla.
  • Value_if_False: To je to, co chceme, aby buňka zobrazila, pokud je hodnota nepravdivá. V tomto případě pro každého studenta, který není v Nebelvíru, chceme, aby buňka ukazovala „0“. Uvozovky používejte pouze v případě, že chcete, aby výsledkem byl text místo čísla.

Excel IF vzorec v akci

Poznámka: Ve výše uvedeném příkladu jsem každému v Nebelvíru přidělil 10 bodů. Pokud bych později chtěl sečíst celkový počet bodů, nemohl bych to udělat, protože 10 jsou v uvozovkách, což z nich dělá text a ne číslo, které může Excel sečíst.

11. Pomocí znaků dolaru udržujte vzorec jedné buňky stejný bez ohledu na to, kam se pohybuje.

Už jste někdy viděli znak dolaru ve vzorci aplikace Excel? Při použití ve vzorci nepředstavuje americký dolar; místo toho zajistí, že přesný sloupec a řádek zůstanou stejné, i když zkopírujete stejný vzorec do sousedních řádků.

Vidíte, odkaz na buňku - když například odkazujete na buňku A5 z buňky C5 - je ve výchozím nastavení relativní. V takovém případě vlastně odkazujete na buňku, která má pět sloupců vlevo (C minus A) a ve stejném řádku (5). Tomu se říká relativní vzorec. Když zkopírujete relativní vzorec z jedné buňky do druhé, upraví hodnoty ve vzorci podle toho, kam se přesunul. Někdy však chceme, aby tyto hodnoty zůstaly stejné bez ohledu na to, zda se pohybují nebo ne - a můžeme to udělat tak, že proměníme vzorec na absolutní vzorec.

Chcete-li změnit relativní vzorec (= A5 + C5) na absolutní vzorec, předcházeli bychom hodnoty řádků a sloupců znakem dolaru, například takto: (= $ A $ 5 + $ C $ 5). (Další informace najdete na stránce podpory Microsoft Office zde.)

12. Pomocí funkce VLOOKUP můžete stahovat data z jedné oblasti listu do druhé.

Už jste někdy měli dvě sady dat ve dvou různých tabulkách, které chcete spojit do jedné tabulky?

Můžete mít například seznam jmen lidí vedle jejich e-mailových adres v jedné tabulce a seznam e-mailových adres stejných lidí vedle jejich firemních jmen v druhé - ale chcete jména, e-mailové adresy a názvy společností z těchto lidí se objeví na jednom místě.

Musím takto hodně kombinovat datové sady - a když to udělám, VLOOKUP je můj vzorec.

Než však použijete vzorec, ujistěte se, že máte alespoň jeden sloupec, který se na obou místech zobrazuje shodně. Prohledejte své datové sady, abyste se ujistili, že sloupec dat, který používáte ke kombinování svých informací, je přesně stejný, včetně žádných mezer navíc.

Vzorec: = VLOOKUP (vyhledávací hodnota, pole tabulky, číslo sloupce, přibližná shoda (TRUE) nebo přesná shoda (FALSE))

Vzorec s proměnnými z našeho příkladu níže: = VLOOKUP (C2, Sheet2! A: B, 2, FALSE)

V tomto vzorci existuje několik proměnných. Toto platí, pokud chcete zkombinovat informace v listu 1 a listu 2 do listu 1.

  • Hodnota vyhledávání: Toto je stejná hodnota, kterou máte v obou tabulkách. Vyberte první hodnotu ve své první tabulce. V následujícím příkladu to znamená první e-mailovou adresu v seznamu nebo buňku 2 (C2).
  • Pole tabulky: Pole tabulky je rozsah sloupců v listu 2, ze kterého chcete vytáhnout svá data, včetně sloupce dat shodných s vaší vyhledávací hodnotou (v našem příkladu e-mailové adresy) v listu 1 a také sloupcem dat snažíte se zkopírovat do Listu 1. V našem příkladu je to „List2! A: B.“ „A“ znamená sloupec A v listu 2, což je sloupec v listu 2, kde jsou uvedena data shodná s naší vyhledávací hodnotou (e-mail) v listu 1. „B“ znamená sloupec B, který obsahuje informace, které jsou k dispozici pouze v listu 2, které chcete přeložit do listu 1.
  • Číslo sloupce: Toto řekne aplikaci Excel, ve kterém sloupci se nacházejí nová data, která chcete zkopírovat do Listu 1. V našem příkladu by to byl sloupec, ve kterém je umístěn „Dům“. „Dům“ je druhý sloupec v naší řadě sloupců ( pole tabulky), takže číslo našeho sloupce je 2. [Poznámka: Váš rozsah může být více než dva sloupce. Například pokud jsou na listu 2 tři sloupce - e-mail, věk a dům - a přesto chcete dům přenést na list 1, můžete i nadále použít VLOOKUP. Musíte jen změnit „2“ na „3“, aby stáhlo hodnotu ve třetím sloupci: = VLOOKUP (C2: Sheet2! A: C, 3, false).]
  • Přibližná shoda (PRAVDA) nebo Přesná shoda (NEPRAVDA): Použijte FALSE k zajištění, že budete stahovat pouze shody s přesnou hodnotou. Pokud použijete PRAVDA, funkce načte přibližné shody.

V níže uvedeném příkladu obsahují List 1 a List 2 seznamy popisující různé informace o stejných lidech a společným vláknem mezi nimi jsou jejich e-mailové adresy. Řekněme, že chceme kombinovat oba datové soubory, aby se všechny informace o domě z Listu 2 překládaly do Listu 1.

Funkce VLOOKUP aplikace Excel

Takže když napíšeme vzorec = VLOOKUP (C2, Sheet2! A: B, 2, FALSE), přinášíme všechna data domu do listu 1.

Mějte na paměti, že VLOOKUP bude stahovat pouze hodnoty z druhého listu, které jsou napravo od sloupce obsahujícího vaše identická data. To může vést k určitým omezením, a proto někteří lidé místo toho raději používají funkce INDEX a MATCH.

13. Pomocí vzorců INDEX a MATCH vytáhněte data z vodorovných sloupců.

Stejně jako VLOOKUP, funkce INDEX a MATCH načítají data z jiné datové sady do jednoho centrálního umístění. Zde jsou hlavní rozdíly:

  • VLOOKUP je mnohem jednodušší vzorec. Pokud pracujete s velkými datovými sadami, které by vyžadovaly tisíce vyhledávání, použití funkce INDEX a MATCH výrazně zkrátí dobu načítání v aplikaci Excel.
  • Vzorce INDEX a MATCH fungují zprava doleva, zatímco vzorce VLOOKUP fungují pouze jako vyhledávání zleva doprava. Jinými slovy, pokud potřebujete provést vyhledávání, které má vyhledávací sloupec napravo od sloupce s výsledky, budete muset tyto sloupce uspořádat, abyste mohli provést VLOOKUP. To může být při velkých souborech dat zdlouhavé a / nebo vést k chybám.

Takže pokud chci zkombinovat informace v Listu 1 a Listu 2 na List 1, ale hodnoty sloupců v Tabulkách 1 a 2 nejsou stejné, pak abych udělal VLOOKUP, musel bych přepínat kolem svých sloupců. V tomto případě bych se místo toho rozhodl udělat INDEX a MATCH.

Podívejme se na příklad. Řekněme, že List 1 obsahuje seznam jmen lidí a jejich bradavických e-mailových adres a List 2 obsahuje seznam e-mailových adres lidí a Patronus, který má každý student. (Pro fanoušky jiných než Harryho Pottera má každá čarodějnice nebo kouzelník přidruženého strážce zvířat zvaného „Patronus“.) Informace, které se nacházejí v obou listech, je sloupec obsahující e-mailové adresy, ale tento sloupec e-mailové adresy je na každém listu v různých číslech sloupců. Místo VLOOKUP bych použil vzorce INDEX a MATCH, takže bych nemusel přepínat žádné sloupce.

Jaký je tedy vzorec? Vzorec je ve skutečnosti MATCH vzorec vnořený do vzorce INDEX. Uvidíte, že jsem zde odlišil vzorec MATCH pomocí jiné barvy.

Vzorec: = INDEX (pole tabulky, MATCH vzorec)

Stává se to: = INDEX (pole tabulky, MATCH (lookup_value, lookup_array))

Vzorec s proměnnými z našeho příkladu níže: = INDEX (List2! A: A, (MATCH (List1! C: C, List2! C: C, 0)))

Zde jsou proměnné:

  • Pole tabulky: Rozsah sloupců v listu 2 obsahující nová data, která chcete přenést do listu 1. V našem příkladu znamená „A“ sloupec A, který obsahuje informace „Patronus“ pro každou osobu.
  • Hodnota vyhledávání: Toto je sloupec v listu 1, který obsahuje stejné hodnoty v obou tabulkách. V následujícím příkladu to znamená sloupec „e-mail“ na listu 1, kterým je sloupec C. Takže: List1! C: C.
  • Vyhledávací pole: Toto je sloupec v listu 2, který obsahuje stejné hodnoty v obou tabulkách. V následujícím příkladu to odkazuje na sloupec „e-mail“ v ​​listu 2, který je shodou okolností také sloupec C. Takže: List2! C: C.

Jakmile máte své proměnné rovné, zadejte vzorce INDEX a MATCH do nejvyšší buňky prázdného sloupce Patronus na listu 1, kde chcete, aby byly kombinované informace aktivní.

Funkce Excel INDEX a MATCH v akci

14. Pomocí funkce COUNTIF můžete v Excelu počítat slova nebo čísla v libovolném rozsahu buněk.

Místo ručního počítání toho, jak často se určitá hodnota nebo číslo objeví, nechte Excel za vás pracovat. Pomocí funkce COUNTIF může Excel spočítat, kolikrát se slovo nebo číslo objeví v libovolném rozsahu buněk.

Řekněme například, že chci spočítat, kolikrát se v mé datové sadě objeví slovo „Nebelvír“.

Vzorec: = COUNTIF (rozsah, kritéria)

Vzorec s proměnnými z našeho příkladu níže: = COUNTIF (D: D, „Nebelvír“)

V tomto vzorci existuje několik proměnných:

  • Rozsah: Rozsah, který chceme, aby vzorec pokrýval. V tomto případě, protože se zaměřujeme pouze na jeden sloupec, použijeme „D: D“ k označení, že první i poslední sloupec jsou oba D. Pokud bych se díval na sloupce C a D, použil bych „C: D . “
  • Kritéria: Jakékoli číslo nebo část textu, které má Excel počítat. Uvozovky používejte pouze v případě, že chcete, aby výsledkem byl text místo čísla. V našem příkladu je kritériem „Nebelvír“.

Jednoduše zadáním vzorce COUNTIF do libovolné buňky a stisknutím klávesy „Enter“ mi zobrazíte, kolikrát se v datové sadě objeví slovo „Nebelvír“.

Funkce Excel COUNTIF

15. Kombinujte buňky pomocí &.

Databáze mají tendenci dělit data, aby byla co nejpřesnější. Například namísto sloupce, který zobrazuje celé jméno osoby, může mít databáze data jako křestní jméno a potom příjmení v samostatných sloupcích. Nebo může mít polohu osoby oddělenou podle města, státu a PSČ. V aplikaci Excel můžete kombinovat buňky s různými daty do jedné buňky pomocí znaku „&“ ve vaší funkci.

Vzorec s proměnnými z našeho příkladu níže: = A2 & ”“ & B2

Pojďme si společně projít vzorec na příkladu. Předstírejte, že chceme spojit křestní jména a příjmení do celých jmen v jednom sloupci. Za tímto účelem bychom nejprve umístili kurzor do prázdné buňky, kde chceme, aby se zobrazilo celé jméno. Dále bychom zvýraznili jednu buňku, která obsahuje křestní jméno, zadejte znak „&“ a poté zvýrazněte buňku s odpovídajícím příjmením.

Ale ještě nejste hotoví - pokud vše, co zadáte, je = A2 a B2, nebude mezi křestním jménem a příjmením osoby mezera. Chcete-li přidat potřebný prostor, použijte funkci = A2 & ”“ & B2. Uvozovky kolem mezery říkají Excelu, aby vložil mezeru mezi jméno a příjmení.

Chcete-li, aby to platilo pro více řádků, jednoduše přetáhněte roh první buňky dolů, jak je znázorněno v příkladu.

Excel kombinace buněk

16. Přidejte zaškrtávací políčka.

Pokud používáte list aplikace Excel ke sledování údajů o zákaznících a chcete dohlížet na něco, co není kvantifikovatelné, můžete do sloupce vložit zaškrtávací políčka.

Pokud například používáte tabulku aplikace Excel ke správě svých prodejních vyhlídek a chcete sledovat, zda jste jim volali v posledním čtvrtletí, můžete mít „Volali jste toto čtvrtletí?“ sloupec a zaškrtněte buňky v něm, když jste zavolali příslušnému klientovi.

Zde je postup.

Zvýrazněte buňku, do které chcete přidat zaškrtávací políčka v tabulce. Poté klikněte na VÝVOJÁŘ. Poté v části FORMOVÉ OVLÁDÁNÍ zaškrtněte políčko nebo výběrový kruh zvýrazněný na obrázku níže.

Zaškrtávací políčka aplikace Excel

Jakmile se pole v buňce objeví, zkopírujte jej, zvýrazněte buňky, ve kterých chcete, aby se také zobrazilo, a poté jej vložte.

17. Hypertextový odkaz na buňku na webovou stránku.

Pokud používáte svůj list ke sledování metrik sociálních médií nebo webových stránek, může být užitečné mít referenční sloupec s odkazy, které každý řádek sleduje. Pokud přidáte adresu URL přímo do aplikace Excel, měla by být automaticky klikatelná. Pokud však musíte vytvořit hypertextový odkaz na slova, jako je název stránky nebo nadpis sledovaného příspěvku, postupujte takto.

Zvýrazněte slova, která chcete vytvořit hypertextový odkaz, a poté stiskněte klávesu Shift K. Odtud se otevře okno, které vám umožní umístit adresu URL hypertextového odkazu. Zkopírujte a vložte adresu URL do tohoto pole a stiskněte nebo klikněte na Enter.

Pokud klávesová zkratka z nějakého důvodu nefunguje, můžete to udělat také ručně zvýrazněním buňky a kliknutím Vložit > Hyperlink.

18. Přidejte rozevírací nabídky.

Někdy budete pomocí své tabulky sledovat procesy nebo jiné kvalitativní věci. Místo toho, abyste do listu opakovaně psali slova, například „Ano“, „Ne“, „Fáze zákazníka“, „Vedoucí prodeje“ nebo „Prospekt“, můžete pomocí rozbalovacích nabídek rychle označit popisné věci o svých kontaktech nebo cokoli jiného sleduji.

Zde je postup, jak do svých buněk přidat rozevírací nabídky.

Zvýrazněte buňky, ve kterých mají být rozbalovací nabídky, poté klikněte na nabídku Data v horním navigačním panelu a stiskněte Ověření.

Možnost rozevírací nabídky aplikace Excel

Odtud uvidíte otevřené pole Nastavení ověření dat. Podívejte se na možnosti Povolit, klikněte na Seznamy a vyberte Rozevírací seznam. Zkontrolujte rozbalovací tlačítko In-Cell a stiskněte OK.

Další zdroje nápovědy pro Excel

Použijte Excel k automatizaci procesů ve vašem týmu

I když nejste účetní, můžete stále používat Excel k automatizaci úkolů a procesů ve vašem týmu. Díky tipům a trikům, které jsme sdíleli v tomto příspěvku, budete jistě používat Excel v plném rozsahu a využívat software co nejvíce k rozvoji vašeho podnikání.

 

původní článek

Související příspěvky