14 Esenciální funkce Microsoft Excel pro analýzu dat

1. ZŘETIT

=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é. Chcete-li použít tuto funkci, postupujte podle syntaxe níže:

=CONCATENATE(Cell 1, Cell 2)

Buňka 1 a buňka 2 odkazují na buňky s hodnotami, které chcete sloučit. Je také užitečné pro vytváření parametrů sledování marketingových kampaní, budování dotazů API, přidání textu do formátu číselného a několika dalších věcí.

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í tagů titulků nebo popisů s omezeným počtem znaků. Syntaxe je níže:

=LEN(Cell)

Buňka ve výše uvedené syntaxi může mít hodnotu, jako je A1, B2 atd. Tato funkce může být také užitečná při objevování rozdílů mezi různými jedinečnými identifikátory, které jsou často zdlouhavé a ne ve správném pořadí.

3. Vlookup

=VLOOKUP dokáže porovnat data z tabulky se vstupní hodnotou. Funkce nabízí dva režimy shody - Přesná a Přibližná - řízené rozsahem vyhledávání. Pokud rozsah nastavíte na FALSE, bude hledat přesnou shodu, ale pokud nastavíte hodnotu PRAVDA, bude hledat přibližnou shodu. Níže je syntaxe:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Prvky této funkce jsou rozděleny takto:

• Lookup_value : Je to prvek, který musíte hledat.
• Table_array: Rozsah buněk obsahujících data, která chcete prohledávat.
• col_index_num: Číslo sloupce v poli table_array obsahující hodnotu, kterou chcete vrátit.
• [Range_lookup] : Volitelný argument specifikuje, jak funkce najde vyhledávání_Value. True pro přibližné zápasy, FALS pro přesné shody.

4. Index/shoda

Stejně jako funkce Vlookup, funkce indexu a shody jsou užitečné pro vyhledávání konkrétních dat na základě vstupní hodnoty.

Při společném použití mohou INDEX a MATCH překonat omezení funkce VLOOKUP při 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 jednorozměrném poli. To vrátí souřadnice dat jako číslo. Níže je syntaxe:

=INDEX(column of the data you want to return, MATCH(common data point you're trying to match, column of the other data source that has the common data point, 0))

Ve výše uvedeném příkladu jsem chtěl v lednu vyhledat počet zhlédnutí. K tomu jsem použil vzorec:

 =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3)

Zde,

• A2:C13je sloupec dat, který má vzorec vrátit.
• Jan je hodnota, kterou chci porovnat.
• A2: A13 je sloupec, ve kterém vzorec najde jan .
• 0 znamená, že chci, aby vzorec našel přesnou shodu pro hodnotu.

Chcete-li najít přibližnou shodu, musíte 0 nahradit 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ě. Všimněte si, že pokud nepoužijete 0, 1 nebo -1, vzorec použije ve výchozím nastavení 1.

5. Minifs/Maxifs

= Minifs a=maxifs jsou podobné funkcím=min a=max, kromě toho, že vám umožňují vzít minimum/maximální sadu hodnot a porovnat je podle konkrétních kritérií. V podstatě tedy tato funkce hledá minimální/maximální hodnoty a odpovídá je vstupních kritériích. Zde je syntaxe:

=MINIFS(min_range, criteria_range1, criteria1,…)

=MAXIFS(max_range, criteria_range1, criteria1,…)

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

Dostal jsem výsledek 27. Tady,

• 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í).
• M je kritérium.

Podobně jsem použil níže uvedený vzorec pro maximální skóre a dostal jsem výsledek 100.

=MAXIFS(C2:C10, B2:B10, "M")

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 musí mít kritéria a rozsah součtů stejný rozsah velikosti. Níže je syntaxe:

=AVERAGEIFS(average_range, criteria_range1, criteria1,…)

Ve výše uvedeném příkladu jsem chtěl najít průměrné skóre na základě pohlaví studenta. K tomu jsem použil níže uvedený vzorec a dostal 56,8. Zde,

• C2: C10 je rozsah, ve kterém bude vzorec hledat průměr.
• B2:B10 je rozsah kritérií.
• M je kritérium.

=AVERAGEIFS(C2:C10, B2:B10, "M") 

7. COUNTIFS

Pokud chcete spočítat instance, kde sada dat splňuje konkrétní kritéria, musíte použít funkci=počítání. Tato funkce umožňuje přidat do dotazu neomezená kritéria, což z ní činí nejjednodušší způsob, jak najít počet na základě vstupních kritérií. Zde je syntaxe:

=COUNTIFS(criteria_range1, criteria1,…)

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 níže uvedený vzorec:

=COUNTIFS(B2:B10, "M", C2:C10, ">=40")

Zde,

• B2: B10 je rozsah, ve kterém bude vzorec hledat první kritéria (pohlaví).
• m je prvním kritériem.
• C2: C10 je rozsah, ve kterém bude vzorec hledat druhé kritérium (značky).
• >=40 je druhé kritérium.

8. SOUČETNÍ PRODUKT

Funkce=sumProduct vám pomůže znásobit rozsah nebo pole dohromady a poté vrací součet produktů. Je to všestranná funkce a lze ji použít k počítání a součtu pole, jako jsou počítání nebo sumify, ale s přidanou flexibilitou. Můžete také použít jiné funkce v rámci SUMPRODUCT k dalšímu rozšíření jeho funkčnosti. Zde je syntaxe:

=SUMPRODUCT(array1, [array2], [array3],…)

V tomto příkladu jsem chtěl najít součet všech prodaných produktů. Použil jsem vzorec:

=SUMPRODUCT(B2:B8, C2:C8) 

Zde,

• B2:B8 je první pole (množství prodaných produktů).
• C2: C8 je druhé pole (cena každého produktu). Vzorec poté vynásobí množství každého produktu prodaného jeho cenou a vše přidává, aby dosáhl celkového prodeje.

9. Oříznutí

Funkce =TRIM je užitečná zejména při práci se sadou dat s několika mezerami nebo nežádoucími znaky. Tato funkce vám umožňuje snadno odstranit tyto mezery nebo znaky z vašich dat a získat přesné výsledky při používání dalších funkcí. Zde je syntaxe:

=TRIM(text)

V tomto příkladu jsem chtěl odstranit všechny další mezery mezi slovy myší a pad v a7 . K tomu jsem použil vzorec:

 =TRIM(A7)

10. Najít/vyhledat

Zaokrouhlením věcí jsou funkce vyhledávání/vyhledávání, které vám pomohou izolovat konkrétní text v datové sadě. Obě funkce jsou podobné v tom, co dělají, s výjimkou jednoho hlavního rozdílu-funkce=Find Function retuse pouze shodné případy. Mezitím Funkce=vyhledávání nemá taková omezení. Zde je syntaxe pro nalezení a vyhledávání:

=FIND(find_text, within_text, [start_num])

=SEARCH(find_text, within_text, [start_num])

V tomto příkladu jsem chtěl zjistit, kolikrát se Gui objevil v rámci Guiding Tech, tak jsem použil níže uvedený vzorec, který přinesl výsledek 1.

=FIND(A2, B2)

Pokud bych místo toho chtěl zjistit, kolikrát se gui objevil v Guiding Tech, musel bych použít vzorec =SEARCH, protože nerozlišuje velká a malá písmena. Tyto funkce jsou užitečné zejména při hledání anomálií nebo jedinečných identifikátorů.

11. Text

Funkce=text transformuje čísla na text. To umožňuje je zobrazit v konkrétním formátu. Tuto funkci můžete použít k zajištění jasnosti a konzistence. Tady je syntaxe:

=TEXT(value, "format_text")

Ve výše uvedeném příkladu používám níže uvedený vzorec k převodu hodnoty v G1 na slova.

=TEXT(G1, "mmmm d, yyyy")

12. IFERROR

Funkce =IFERROR je obslužná rutina chyb, která vám umožňuje určit, co má program Excel zobrazit, když dojde k chybě. Pomáhá to vyhnout se mnoha # chybovým znaménkům na vašich listech. Zde je syntaxe:

=IFERROR(value, value_if_error)

Ve výše uvedeném příkladu použijeme níže uvedený vzorec k zobrazení zprávy, když dojde k chybě.

=IFERROR(A1/B1, "Division by zero error")

13. Filtr

Funkce =FILTER extrahuje konkrétní datové sady na základě jednoho nebo více kritérií. Výsledky můžete dynamicky filtrovat, aniž byste museli provádět změny v datech. Můžete tak zkoumat a analyzovat různé podmnožiny vašich dat. Zde je syntaxe:

=FILTER(array, include, [if_empty])

Příklad níže můžeme použít k zobrazení sloupce b hodnoty rovnající se Sales .

=FILTER(A1:C10, B1:B10 = "Sales")

14. Sřídnutí

Funkce=třídění organizuje data ve vzestupném nebo sestupném pořadí. Tady je syntaxe:

=SORT(array, sort_col1, sort_order1, [sort_col2, sort_order2], ...)

V níže uvedeném příkladu třídíme data v rozsahu A1: C10 , 3 odkazuje na třetí sloupec v rozsahu a používáme -1 Chcete -li třídit v sestupném pořadí.

=SORT(A1:C10, 3, -1)

Při používání těchto funkcí můžete také najít některé zkratky Excel.