10 základních funkcí aplikace Microsoft Excel pro analýzu dat

1. CONCATENATE

=CONCATENATE je jednou z nejdůležitějších funkcí pro analýzu dat, protože umožňuje kombinovat text, čísla, data atd. z více buněk do jedné. Funkce je zvláště užitečná pro kombinování dat z různých buněk do jedné buňky. Hodí se například pro vytváření parametrů sledování pro marketingové kampaně, vytváření dotazů API, přidávání textu do formátu čísel a několik dalších věcí.

Ve výše uvedeném příkladu jsem chtěl měsíc a tržby společně v jednom sloupci. K tomu jsem použil vzorec =CONCATENATE(A2, B2) v buňce C2, abych jako výsledek dostal Jan $ 700.

Vzorec: =CONCATENATE(buňky, které chcete zkombinovat)

2. LEN

=LEN je další užitečná funkce pro analýzu dat, která v podstatě vydává počet znaků v dané buňce. Funkce je použitelná především při vytváření značek titulků nebo popisů, které mají omezený počet znaků. Může být také užitečné, když se snažíte zjistit rozdíly mezi různými jedinečnými identifikátory, které jsou často poměrně zdlouhavé a nejsou ve správném pořadí.

Ve výše uvedeném příkladu jsem chtěl spočítat čísla pro počet zobrazení, která jsem získával každý měsíc. K tomu jsem použil vzorec =LEN(C2) v buňce D2, abych získal 5 jako výsledek.

Vzorec: =DÉLKA(buňka)

3. SVYHLEDAT

=VLOOKUP je pravděpodobně jednou z nejznámějších funkcí pro každého, kdo je obeznámen s analýzou dat. Můžete jej použít ke spárování dat z tabulky se vstupní hodnotou. Funkce nabízí dva režimy shody — přesné a přibližné, které je řízeno rozsahem vyhledávání. Pokud nastavíte rozsah na FALSE, bude hledat přesnou shodu, ale pokud nastavíte hodnotu TRUE, bude hledat přibližnou shodu.

Ve výše uvedeném příkladu jsem chtěl vyhledat počet zobrazení v konkrétním měsíci. K tomu jsem použil vzorec =VLOOKUP("Červ", A2:C13, 3) v buňce G4 a jako výsledek jsem dostal 74992. Zde je "Jun" vyhledávací hodnotou, A2:C13 je pole tabulky, ve kterém hledám "Červen" a 3 je číslo sloupce, ve kterém vzorec najde odpovídající zobrazení pro červen.

Jedinou nevýhodou použití této funkce je, že pracuje pouze s daty, která byla uspořádána do sloupců, odtud název — vertikální vyhledávání. Pokud tedy máte data uspořádaná do řádků, musíte to nejprve udělat transponovat řádky do sloupců.

Vzorec: =SVYHLEDAT(lookup_value, table_array, col_index_num, [range_lookup])

4. INDEX/MATCH

Podobně jako funkce VLOOKUP jsou funkce INDEX a MATCH užitečné pro vyhledávání konkrétních dat na základě vstupní hodnoty. Když se INDEX a MATCH použijí společně, mohou překonat omezení funkce VLOOKUP spočívající v poskytování nesprávných výsledků (pokud si nedáte pozor). Když tedy tyto dvě funkce zkombinujete, mohou určit odkaz na data a vyhledat hodnotu v jediném poli dimenzí. To vrátí souřadnice dat jako číslo.

Ve výše uvedeném příkladu jsem chtěl vyhledat počet zobrazení v lednu. K tomu jsem použil vzorec =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). Zde je A2:C13 sloupec dat, který chci, aby vzorec vrátil, "Jan" je hodnota, kterou chci porovnat, A2:A13 je sloupec, ve kterém vzorec najde "Jan" a 0 znamená, že chci, aby vzorec našel přesnou shodu pro hodnota.

Pokud chcete najít přibližnou shodu, budete muset nahradit 0 1 nebo -1. Takže 1 najde největší hodnotu menší nebo rovnou vyhledávací hodnotě a -1 najde nejmenší hodnotu menší nebo rovnou vyhledávací hodnotě. Pamatujte, že pokud nepoužijete 0, 1 nebo -1, vzorec použije 1, by.

Nyní, pokud nechcete pevně kódovat název měsíce, můžete jej nahradit číslem buňky. Můžeme tedy nahradit "Jan" ve výše uvedeném vzorci F3 nebo A2, abychom získali stejný výsledek.

Vzorec: =INDEX(sloupec dat, která chcete vrátit, MATCH(společný datový bod, který se pokoušíte porovnat, sloupec jiného zdroje dat, který má společný datový bod, 0))

5. MINIFS/MAXIFS

=MINIFS a =MAXIFS jsou velmi podobné funkcím =MIN a =MAX, s výjimkou toho, že vám umožňují vzít minimální/maximální sadu hodnot a také je porovnat podle určitých kritérií. Funkce tedy v podstatě hledá minimální/maximální hodnoty a porovnává je se vstupními kritérii.

Ve výše uvedeném příkladu jsem chtěl najít minimální skóre na základě pohlaví studenta. K tomu jsem použil vzorec =MINIFS (C2:C10, B2:B10, "M") a dostal jsem výsledek 27. Zde C2:C10 je sloupec, ve kterém bude vzorec hledat skóre, B2:B10 je sloupec, ve kterém bude vzorec hledat kritéria (pohlaví) a "M" je kritéria.

Podobně pro maximální skóre jsem použil vzorec =MAXIFS(C2:C10, B2:B10, "M") a dostal jsem výsledek 100.

Vzorec pro MINIFS: =MINIFS(minimální_rozsah, rozsah_kritérií1, kritéria1,...)

Vzorec pro MAXIFS: =MAXIFS(max_range, kritéria_rozsah1, kritéria1,...)

6. PRŮMĚRNÉ

Funkce =AVERAGEIFS vám umožňuje najít průměr pro konkrétní soubor dat na základě jednoho nebo více kritérií. Při používání této funkce byste měli mít na paměti, že každé kritérium a průměrný rozsah se mohou lišit. Ve funkci =AVERAGEIF však rozsah kritérií i rozsah součtů musí mít stejný rozsah velikosti. Všimli jste si rozdílu v jednotném a množném čísle mezi těmito funkcemi? No a tam je třeba být opatrný.

V tomto příkladu jsem chtěl najít průměrné skóre na základě pohlaví studentů. K tomu jsem použil vzorec =AVERAGEIFS(C2:C10, B2:B10, "M") a jako výsledek jsem dostal 56,8. Zde je C2:C10 rozsah, ve kterém bude vzorec hledat průměr, B2:B10 je rozsah kritérií a "M" je kritérium.

Vzorec: =AVERAGEIFS(průměrný_rozsah, rozsah_kritérií1, kritéria1,...)

7. COUNTIFS

Nyní, pokud chcete spočítat počet případů, kdy soubor dat splňuje určitá kritéria, budete muset použít funkci =COUNTIFS. Tato funkce vám umožňuje přidat neomezená kritéria k vašemu dotazu, a tím je nejjednodušší způsob, jak najít počet na základě vstupních kritérií.

V tomto příkladu jsem chtěl zjistit počet studentů nebo studentek, kteří získali známky pro úspěšnost (tj. >=40). K tomu jsem použil vzorec =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). Zde je B2:B10 rozsah, ve kterém bude vzorec hledat první kritérium (pohlaví), "M" je první kritérium, C2:C10 je rozsah, ve kterém bude vzorec hledat druhé kritérium (známky), a ">=40" je druhé kritéria.

Vzorec: =COUNTIFS(rozsah_kritérií1, kritéria1,...)

8. SUMPRODUCT

Funkce =SUMPRODUCT vám pomůže násobit rozsahy nebo pole dohromady a poté vrátí součet součinů. Je to docela všestranná funkce a lze ji použít k počítání a sčítání polí jako COUNTIFS nebo SUMIFS, ale s přidanou flexibilitou. K dalšímu rozšíření jeho funkčnosti můžete využít i další funkce v rámci SUMPRODUCT.

V tomto příkladu jsem chtěl najít součet všech prodaných produktů. K tomu jsem použil vzorec =SUMPRODUCT(B2:B8, C2:C8). Zde je B2:B8 první pole (množství prodaných produktů) a C2:C8 je druhé pole (cena každého produktu). Vzorec pak vynásobí množství každého prodaného produktu jeho cenou a poté vše sečte, aby se dosáhlo celkového prodeje.

Vzorec: =SUMPRODUKT(pole1, [pole2], [pole3],...)

9. TRIM

Funkce =TRIM je zvláště užitečná, když pracujete se sadou dat, která obsahuje několik mezer nebo nežádoucích znaků. Tato funkce vám umožňuje snadno odstranit tyto mezery nebo znaky z vašich dat, což vám umožní získat přesné výsledky při používání jiných funkcí.

V tomto příkladu jsem chtěl odstranit všechny přebytečné mezery mezi slovy Myš a podložka v A7. K tomu jsem použil vzorec =TRIM(A7).

Vzorec jednoduše odstranil nadbytečné mezery a poskytl výslednou podložku pod myš s jedinou mezerou.

Vzorec: =TRIM(text)

10. NAJÍT/HLEDAT

Zaokrouhlení jsou funkce FIND/SEARCH, které vám pomohou izolovat konkrétní text v rámci datové sady. Obě funkce jsou v tom, co dělají, docela podobné, až na jeden zásadní rozdíl — funkce =FIND vrací pouze shody rozlišující malá a velká písmena. Mezitím funkce =SEARCH žádná taková omezení nemá. Tyto funkce jsou užitečné zejména při hledání anomálií nebo jedinečných identifikátorů.

V tomto příkladu jsem chtěl zjistit, kolikrát se 'Gui' objevilo v Guiding Tech, pro které jsem použil vzorec =FIND(A2, B2), který přinesl výsledek 1. Pokud bych chtěl zjistit, kolikrát se v Guiding Tech objevilo „gui“, musel bych použít vzorec =SEARCH, protože nerozlišuje velká a malá písmena.

Vzorec pro hledání: =NAJÍT(najít_text, v_textu, [počáteční_číslo])

Vzorec pro vyhledávání: =HLEDAT(najít_text, v_textu, [počáteční_číslo])