Hledání
Novinky
Návštěvy
Dnes: 33
Celkem: 4561

Nové funkce mezi vyhledávacími

  • Ukázka databáze
    Ukázka databáze
  • Jednoduché použití SeskupitPodle
    Jednoduché použití SeskupitPodle
  • SeskupitPodle při použití sort_order
    SeskupitPodle při použití sort_order
  • SeskupitPodle při použití filter_array
    SeskupitPodle při použití filter_array
  • SeskupitPodle s funkcí Průměr
    SeskupitPodle s funkcí Průměr
  • Ukázka KontPodle
    Ukázka KontPodle
  • Výpočet procenta pro jednoho prodejce
    Výpočet procenta pro jednoho prodejce
  • SeskupitPodle a Procento
    SeskupitPodle a Procento

Při zpracování většího množství dat se často používání kvůli přehledu a konečným výsledkům kontingenční tabulky, Ale může nastat situace, kdy se vám z vašich dat nechce vytvářet kontingenční tabulka. Co pak? Objevila se zde možnost použít nové zajímavé vyhledávací funkce. Jedná se o funkce SeskupitPodle (GroupBy), KontPodle (PivotBy) a Procento (PercentOf). S čím vám mohou pomoci?

 

 SeskupitPodle (GroupBy)

 Funkce SeskupitPodle totiž umožňuje vytvořit souhrn dat jen pomocí vzorce. Podporuje seskupení podél jednoho pole a souhrn přidružených hodnot. Umí řadit a filtrovat data na základě zadaných polí. Možná to zní složitě, ale na příkladě bude za chvíli vidět, o co se jedná.

Nejprve ale něco o syntaxi této funkce:

=SESKUPITPODLE(row_fields; values; function; field_headers; total_depth; sort_order; filter_array; field_relationship),

kde argument row_fields je oblast, matice, pole, podle kterého se mají data seskupovat. Argument values jsou pole nebo oblast dat, která se mají být shrnuta (agregována). V dalším argumentu function se zadává funkce (Suma, Procento, Průměr, Median, Počet, Počet2, Min, Max, Součin, Arraytotext, Concat,...), která má být k agregaci použita, přičemž lze použít i vektor Lambda (pak výstup bude mít více agregací). 

Argument field_headers bude číslo, které určuje, zda bude u výsledných hodnot záhlaví. Možné hodnoty jsou 0: nebude, 1: ano, ale nezobrazuje se, 2: ne, ale vygeneruje se jen text (Řádkové pole 1 Hodnota 1) a 3: ano a zobrazí se.

Argument total_depth zase určuje, zda záhlaví řádků mají obsahovat součty. Možné hodnoty jsou: 0: bez součtů, 1: celkové součty a -1: celkové součty v horní části tabulky. Při použití více sloupců zde může být ještě 2 nebo -2.

Argument sort_order je číslo, které říká, podle kterého a jak budou řádky ve výsledku seřazeny. Pokud je číslo záporné, řádky se budou řadit sestupně jinak vzestupně.

Další argument filter_array potřebuje pole, které obsahuje logické hodnoty pravda a nepravda. Pokud se na toto pole odkáže tímto argumentem, pak hodnoty, kde je v tomto poli "pravda", tak hodnota bude do souhrnu zařazena, když bude v poli "nepravda", tak se hodnota nezahrne.

A poslední argument field_relationship určuje pole relace, když je pro row_fields zadáno více sloupců. Možné hodnoty jsou 0: hierarchie (výchozí hodnota) nebo 1: tabulka. Přičemž mezisoučty se nepodporují, protože spoléhají na data, která mají hierarchii.

Pokud se podíváte Zkuste se podívat na obrázky 1 až 5, kde je ukázáno využití funkce s použitím jednotlivých argumentů na ukázkové databázi. Najdete zde jednoduché seskupení se součtem či průměrem, použití opačného seřazení nebo odfiltrování některých řádků ze souhrnu.

 

 KontPodle (PivotBy)

Jedná se o funkci, která umožňuje seskupit, vytvářet souhrn, řadit a filtrovat data na základě zadaných polí řádků a sloupců. Tato funkce podporuje seskupení podél dvou polí a shrnutí přidružených hodnot. To předchozí funkce SeskupitPodle takto neumí. Výsledek může sice vypadat jako kontingenční tabulka, ale s funkcí kontingenční tabulky přímo nesouvisí.

Syntaxe funkce je:
=PIVOTBY(row_fields; col_fields; values; function; field_headers; row_total_depth, row_sort_order; col_total_depth; col_sort_order; filter_array; relative_to),

kde argument row_fields je sloupcové pole (oblast), které obsahuje hodnoty, které se používají k seskupení řádků a generování záhlaví řádků, přičemž může obsahovat i více sloupců. druhý argument col_fields je také sloupcově orientované pole, které se použijí k seskupení sloupců a generování záhlaví sloupců. Další povinný argument values je také sloupcově orientované pole, které se má seskupovat. A do argumentu function se zadává funkce (Suma, Procento, Průměr, Median, Počet, Počet2, Min, Max, Součin, Arraytotext, Concat,...), která má být k agregaci použita, přičemž lze použít i vektor Lambda (pak výstup bude mít více agregací).

Argument field_headers je číslo, které určuje, zda bude u výsledných hodnot záhlaví. Možné hodnoty jsou 0: nebude, 1: ano, ale nezobrazuje se, 2: ne, ale vygeneruje se jen text (Řádkové pole 1 Hodnota 1) a 3: ano a zobrazí se.

Argument row_total_depth zase určuje, zda záhlaví řádků mají obsahovat součty. Možné hodnoty jsou: 0: bez součtů, 1: celkové součty a -1: celkové součty v horní části tabulky. Při použití více sloupců zde může být ještě 2 nebo -2 (případně vyšší). Podobně je to i s argumentem col_total_depth, který určuje, zda záhlaví řádků mají obsahovat součty. 

Další argument row_sort_order je číslo, které určuje, jak se mají sloupce řadit. Čísla odpovídají sloupcům v row_fields následovaných sloupci v hodnotách. Pokud je číslo záporné, řádky se seřadí sestupně nebo v opačném pořadí. Podobně je to také u argumentu col_sort_order. To znamená číslo, které označuje, jak se mají sloupce řadit. 

Dále argument filter_array potřebuje pole, které obsahuje logické hodnoty pravda a nepravda. Pokud se na toto pole odkáže tímto argumentem, pak hodnoty, kde je v tomto poli "pravda", tak hodnota bude do souhrnu zařazena, když bude v poli "nepravda", tak se hodnota nezahrne. 

A poslední argument relative_to se využije při použití agregační funkce, která vyžaduje dva argumenty, relative_to určuje, které hodnoty jsou zadány do druhého argumentu agregační funkce. 

 

Procento (PercentOf)

Funkce Procento umí vypočítat procento, které určitá skupina tvoří z celkové datové sady. Pravidla zápisu je: =PROCENTO(data_subset;data_all),

kde argument data_subset jsou hodnoty v dané skupině dat a argument data_all jsou hodnoty, které tvoří celou skupinu dat.

Výpočet je ukázán na šestém obrázku.

Tato funkce je však užitečná i v rámci funkcí SeskupitPodle a KontPodle. Na posledním obrázku vidíte využití ve funkci SeskupitPodle.