3 bláznivé Excel vzorce, které dělají úžasné věci

Excel vzorce mohou dělat téměř cokoli. V tomto článku se dozvíte, jak výkonné formáty Microsoft Excel a podmíněné formátování mohou být, se třemi užitečnými příklady.

Kopání do aplikace Microsoft Excel

Pokryli jsme celou řadu různých způsobů, jak lépe využít Excel, jako je například použití k vytvoření vlastní šablony kalendáře nebo použití jako nástroje pro správu projektů..

Mnoho síly leží za vzorci a pravidly Excelu, která můžete psát, aby se s daty a informacemi zacházelo automaticky, bez ohledu na to, jaká data vložíte do tabulky.

Pojďme se podívat, jak můžete používat vzorce a další nástroje pro lepší využití aplikace Microsoft Excel.

Podmíněné formátování pomocí vzorců Excelu

Jedním z nástrojů, které lidé dostatečně často nepoužívají, je podmíněné formátování. Pokud hledáte podrobnější informace o podmíněném formátování v aplikaci Microsoft Excel, podívejte se na článek Sandy o formátování dat v aplikaci Microsoft Excel s podmíněným formátováním..

Pomocí vzorců, pravidel nebo pouze několika opravdu jednoduchých nastavení aplikace Excel můžete tabulku transformovat do automatizovaného řídicího panelu.

Chcete-li se dostat k podmíněnému formátování, stačí kliknout na ikonu Domov 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ýraznění, barvení nebo stínování buněk na základě dat v této buňce..

Toto je pravděpodobně nejběžnější použití podmíněného formátování - například změnění buňky na červenou pomocí vzorců méně než nebo více. Další informace o tom, jak používat příkazy IF v Excelu.

Jedním z méně používaných nástrojů podmíněného formátování je Sada ikon možnost, která nabízí velkou sadu ikon, které můžete použít k přeměně datové buňky Excelu na ikonu displeje řídicího panelu.

Když kliknete na Spravovat pravidla, vezme tě na Správce podmíněných formátovacích pravidel.

V závislosti na datech, která jste vybrali před výběrem sady ikon, se v okně Správce zobrazí buňka označená s právě vybranou sadou ikon..

Když kliknete na Upravit pravidlo, uvidíte dialog, kde se magie odehrává.

Zde můžete vytvořit logický vzorec a rovnice, které zobrazí požadovanou ikonu na hlavním panelu.

Tento příklad řídicího panelu zobrazí čas strávený různými úkoly v porovnání s rozpočtovaným časem. Pokud překročíte polovinu rozpočtu, zobrazí se žluté světlo. Pokud jste úplně nad rozpočet, zbarví se červeně.

Jak vidíte, tento dashboard ukazuje, že časové rozpočtování není úspěšné.

Téměř polovina času je věnována více než rozpočtovaným částkám.

Čas na zaostření a lepší správu času!

1. Pomocí funkce VLookup

Pokud chcete používat pokročilejší funkce aplikace Microsoft Excel, pak je tu další pro vás.

Pravděpodobně jste obeznámeni s funkcí VLookup, která vám umožní prohledat seznam konkrétní položky v jednom sloupci a vrátit 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 napravo, ale co když budou přepnuty?

V níže uvedeném příkladu, co když chci najít úkol, který jsem provedl dne 25.6.2018 z následujících údajů?

V tomto případě prohledáváte hodnoty vpravo a chcete vrátit odpovídající hodnotu vlevo - oproti tomu, jak normálně funguje VLookup..

Pokud čtete pro-uživatelská fóra Microsoft Excel, najdete mnoho lidí, kteří tvrdí, že to není možné pomocí VLookup, a že k tomu musíte použít kombinaci funkcí Index a Match. To není úplně pravda.

Můžete získat VLookup tak, aby fungoval tak, že do něj vnoříte funkci VYBRAT. V tomto případě by vzorec Excel vypadal takto:

"= VLOOKUP (DATUM (2018,6,25), VYBRAT (1,2, E2: E8, A2: A8), 2,0)"

Tato funkce znamená, že chcete najít datum 25/25/2013 ve vyhledávacím seznamu a vrátit odpovídající hodnotu z indexu sloupců..

V tomto případě si všimnete, že index sloupců je “2”, ale jak vidíte, sloupec v tabulce výše je ve skutečnosti 1, správně?

To je pravda, ale to, co děláte s “VYBRAT” funkce manipuluje s oběma poli.

Přiřazujete reference “index” čísla do rozsahu dat - přiřazení dat indexu číslo 1 a úkolům indexu číslo 2.

Takže, když píšete “2” ve funkci VLookup vlastně odkazujete na Index číslo 2 ve funkci VYBRAT. Super, dobře?

Takže nyní VLookup používá datum sloupec a vrací data z Úkol sloupec, i když je úkol vlevo.

Teď, když znáte tento malý úbor, jen si představte, co jiného můžete udělat!

Pokud se pokoušíte provádět další pokročilé úkoly vyhledávání dat, nezapomeňte si prohlédnout úplný článek Danna o hledání dat v Excelu pomocí vyhledávacích funkcí..

2. Vnořená receptura pro analýzu řetězců

Tady je další šílený vzorec pro Excel.

Mohou nastat případy, kdy importujete data do aplikace Microsoft Excel z vnějšího zdroje skládajícího se z řetězce oddělených dat.

Jakmile data uvedete, chcete je analyzovat do jednotlivých komponent. Zde je příklad informace o jméně, adrese a telefonním čísle ohraničeném “;” charakter.

Zde je návod, jak můžete tyto informace analyzovat pomocí vzorce Excel (zjistěte, zda můžete mentálně sledovat toto šílenství):

Pro první pole, pro extrahování položky zcela vlevo (jméno osoby), byste jednoduše použili funkci LEFT ve vzorci.

"= LEFT (A2, FIND ("; ", A2,1) -1)"

Jak funguje tato logika:

• Hledá textový řetězec z A2
• Najde “;” oddělovač symbol
• Odečte jeden za správné umístění konce této části řetězce
• Uchopí k tomuto bodu text zcela vlevo

V tomto případě je text zcela vlevo “Ryane”. Mise splněna.

3. Vnořená formule v Excelu

Ale co ostatní oddíly?

Mohou existovat snazší způsoby, jak to udělat, ale protože se snažíme vytvořit nejbláznivější vzorec vnořeného Excelu (který ve skutečnosti funguje), budeme používat jedinečný přístup.

Chcete-li extrahovat části napravo, musíte vnořit více funkcí PRAVÉ, abyste uchopili část textu až do té první “;” symbol a znovu na něm proveďte funkci VLEVO. Tady je to, co vypadá jako pro extrahování části adresy z ulice.

"= LEFT ((VPRAVO (A2, LEN (A2) -FIND ("; ", A2))), FIND ("; ", (RIGHT (A2, LEN (A2) -FIND ("; ", A2))) ), 1) -1) "

Vypadá to bláznivě, ale není těžké se spojit. Vše, co jsem udělal, bylo převzít tuto funkci:

PRAVÝ (A2, LEN (A2) -FIND (";", A2))

A vložil jej na každé místo ve funkci VLEVO nad tím, kde je “A2”.

Tím se správně extrahuje druhá část řetězce.

Každá následující část řetězce potřebuje další vytvořené hnízdo. Takže teď už jen blázen “ŽE JO” rovnice, kterou jste vytvořili pro poslední sekci, a pak ji předejte do nového RIGHT vzorce s předchozím RIGHT vzorcem vloženým do sebe, kdekoli uvidíte “A2”. Jak to vypadá?.

(RIGHT ((RIGHT (A2, LEN (A2) -FIND (";", A2))), LEN ((RIGHT (A2, LEN (A2) -FIND (";", A2)))) - FIND ( ";", (VPRAVO (A2, LEN (A2) -FIND (";", A2)))))))))

Pak si vezmete, že vzorce, a umístěte jej do původní LEVÉ vzorce, kdekoli existuje “A2”.

Konečný vzorec ohýbání mysli vypadá takto:

"= VLEVO ((VPRAVO ((PRÁVO (A2, LEN (A2)) -FIND ("; ", A2))), LEN ((VPRAVO (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 se 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 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, aby dlouhé hnízda, které skutečně fungují.

Ano, splňuje to požadavek “ší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 údaji a poté pod Data vyberte položku nabídky Text do sloupců.

Tím se otevře okno, kde můžete řetězec rozdělit libovolným oddělovačem.

Za pár kliknutí můžete udělat totéž, co ten bláznivý vzorec výše ... ale v čem je zábava?

Bláznění do vzorců Microsoft Excel

Takže to máte. Výše uvedené vzorce ukazují, jak může člověk získat při vytváření vzorců aplikace Microsoft Excel k provedení určitých úkolů.

Někdy tyto Excel formule nejsou vlastně nejjednodušší (nebo nejlepší) způsob, jak dosáhnout věci. Většina programátorů vám řekne, aby to bylo jednoduché, a to platí stejně jako u vzorců Excelu.

Pokud si opravdu přejete, aby s používáním Excelu opravdu záleželo, budete si chtít přečíst v naší příručce pro začátečníky k používání aplikace Microsoft Excel Průvodce pro začátečníky k aplikaci Microsoft Excel Průvodce pro začátečníky k aplikaci Microsoft Excel Pomocí této průvodce pro začátečníky můžete začít používat Microsoft Excel. Základní tipy pro tabulky vám pomohou začít se učit Excel sami. . Má vše, co potřebujete ke zvýšení produktivity pomocí Excelu. Po tom, ujistěte se, že konzultovat naše základní funkce Excel cheat sheet Základní Microsoft Excel vzorce a funkce Cheat Sheet Základní Microsoft Excel vzorce a funkce Cheat Sheet Stáhněte si tento Excel vzorce cheat, aby zástupce prostřednictvím světového oblíbeného tabulkového procesoru. .