Excel a Google Sheets jako základní statistické nástroje pro analýzu bez R a Pythonu

Kdy postačí Excel/Sheets jako „statistický light“ nástroj

Excel a Google Sheets jsou v mnoha kurzech a projektech okamžitě dostupné, intuitivní a dostatečně výkonné pro širokou škálu statistických úloh. Nenahradí plně R či Python při práci s velkými daty a pokročilém modelování, ale pro 80 % běžných potřeb (čištění, popisná statistika, testování hypotéz, jednoduchá regrese, vizualizace, reporting) jsou výbornou volbou. Tento článek ukazuje, jak je používat systematicky, replikovatelně a bez „klikací magie“.

Standard práce: od klikacích kroků k replikovatelným vzorcům

• Preferujte vzorce před manuálními zásahy: místo ručních filtrů použijte FILTER, UNIQUE, SORT (Sheets) nebo dynamická pole (UNIQUE(), SORT(), FILTER() v novějších verzích Excelu).
• Oddělte vstup, zpracování a výstup: listy Raw (vstup), Tidy (čištění), Analysis (výpočty), Charts (grafy).
• Pojmenované oblasti a tabulky: v Excelu převeďte data na „Table“ (Ctrl+T) a používejte strukturované odkazy (Table1[Sales]). V Sheets používejte pojmenované oblasti.
• Dokumentace v buňce: komentář u klíčových buněk (Ctrl+Alt+M v Sheets; Shift+F2 v Excelu) a legenda k proměnným.

Import a „tidy“ princip: základ pro spolehlivé výpočty

• Import CSV bez ztráty typů: u dat s daty kontrolujte region a formát; v Excelu použijte Data → From Text/CSV a nastavte Data Type Detection = Do not detect.
• Jeden sloupec = jedna proměnná, jeden řádek = jedna jednotka pozorování: vyhněte se slučování záhlaví a spojeným buňkám.
• Kontrola chybějících hodnot: COUNTBLANK(range), v Sheets rovněž COUNTIF(range,""). Označujte NA konzistentně (raději prázdná buňka než text „N/A“).
• Typy proměnných: kategorie jako text, metrické veličiny jako čísla; používejte Data Validation pro kategorie.

Rychlý audit dat: anomálie, duplicity, nesoulad typů

• Duplicity: Conditional Formatting → Duplicate Values nebo UNIQUE()/COUNTIF() pro detekci.
• Rozsahy a extrémy: MIN(), MAX(), PERCENTILE.EXC(); zvýrazněte hodnoty mimo očekávaný interval.
• Základní normalizační kontrola: z-skóre: =(x-AVERAGE(range))/STDEV.S(range); hodnoty |z| > 3 vyžadují pozornost.

Popisná statistika: jádro každého reportu

• Střední tendence: AVERAGE(), MEDIAN(), MODE.SNGL().
• Variabilita: STDEV.S() (výběr), VAR.S(), QUARTILE.EXC(), IQR = Q3-Q1.
• Agregace podle skupin: v Excelu PivotTable, v Sheets Pivot table; nebo funkce SUMIFS(), AVERAGEIFS(), COUNTIFS().
• Procenta a míra chyb: podíly s COUNTIFS() a konfidenční intervaly: CONFIDENCE.T(alpha, STDEV.S(range), COUNT(range)).

Vizualizace: standardy, které stačí na A-čkový graf

• Histogram a KDE „light“: FREQUENCY(data, bins) nebo vestavěný Histogram (Excel: Insert → Statistic Chart), v Sheets Chart type: Histogram.
• Boxplot: Excel má Box & Whisker; v Sheets použijte Box plot (v nastaveních karty).
• Rozptylové grafy s trendovou čarou: v Excelu Add Trendline, zobrazte R² a rovnici; v Sheets Series → Trendline.
• Dobrá praxe: jednotky na osách, jemná mřížka, stručný nadpis s pointou („Výnos roste ~1,2 %/měsíc“), bez 3D efektů.

Testování hypotéz bez maker: co lze udělat „z krabice“

• Srovnání průměrů (t-test): T.TEST(array1, array2, tails, type) (Excel/Sheets). type: 1 = párový, 2 = dvouvýběr stejná variance, 3 = dvouvýběr rozdílná variance.
• Rozptyly (F-test, Levene light): v Excelu existuje F-test v Data Analysis Toolpak; alternativně porovnejte VAR.S() a interpretujte opatrně.
• Podíly (z-test podílu): vypočítejte manuálně z statistiku; p-hodnotu získáte pomocí NORM.S.DIST(z, TRUE) nebo 1-NORM.S.DIST(z, TRUE).
• Neparametrické „light“: pro srovnání mediánů použijte robustní metriky (medián, IQR) a vizuální kontrolu; formální Wilcoxon/Mann-Whitney testy jsou v Toolpaku (Excel) nebo pomocí doplňků v Sheets.
• Kontingenční tabulky a chí-kvadrát: vytvořte mřížku počtů (COUNTIFS()), poté CHISQ.TEST(observed, expected) (Excel/Sheets).

Korelace, kovariance a jednoduchá lineární regrese

• Korelace: CORREL(x, y) nebo PEARSON(x, y).
• Kovariance: COVARIANCE.S(x, y) (výběr).
• Regrese – koeficienty bez klikání: LINEST(y_range, x_range, TRUE, TRUE) (jako dynamické pole v Excelu; v Sheets vrací matici). Získáte koeficienty, směrodatné chyby, R², F-statistiku.
• Predikce: FORECAST.LINEAR(x0, y, x) nebo TREND() pro více bodů.
• Diagnostika reziduí: rezidua =y - (b0 + b1*x), zkontrolujte jejich histogram a rozptyl.

ANOVA a vícečetná srovnání: kde pomáhá Toolpak

V Excelu Data → Data Analysis → ANOVA (jednofaktorová; repeated measures – částečná podpora) s reportem p-hodnot. Post-hoc testy (Tukey) řešte manuálně nebo doplňkem; v Sheets použijte alternativy Analysis ToolPak (add-ons) nebo rozdíl průměrů s Bonferroni korekcí.

Práce se „skupinováním“: Pivot a jeho vzorcové ekvivalenty

• Pivot Table: nejrychlejší souhrny podle kategorií; zapněte „Show Values As“ (procenta z celku, rozdíly).
• Vzorcové ekvivalenty: SUMIFS(), AVERAGEIFS(), COUNTIFS() pro replikovatelné reporty a propojení do grafů bez manuálních aktualizací.
• Power Query (Excel): ETL „light“ pro kombinaci listů, slučování souborů, rozdělování sloupců a doplňování; vše s krokovým sledem, který je reprodukovatelný.

Čištění a transformace: robustní stavební bloky

• Textové operace: TRIM(), CLEAN(), UPPER()/LOWER(), TEXTSPLIT() (Excel), v Sheets SPLIT().
• Podmínky: IF(), IFS(), SWITCH(); označte anomálie pro pozdější filtrování.
• Propojení tabulek: Excel XLOOKUP() (nebo INDEX/MATCH), Sheets VLOOKUP()/XLOOKUP() (pokud dostupné), INDEX(MATCH()) pro přesnost.
• Filtrování do nového rozsahu: FILTER(data, podmínka), řetězené se SORT() a UNIQUE() pro „tidy views“.

Časové řady: vyhlazování, posuny a sezónnost

• Posuny: OFFSET() nebo INDEX() pro výpočty „rolling“ metrik (např. 7-denní průměr: AVERAGE(INDEX(y, ROW()-6):INDEX(y, ROW())) ve sloupci s daty).
• Vyhlazování: FORECAST.ETS() (Excel) pro sezónní složky; v Sheets FORECAST (lineární) a manuální sezónní indexy.
• Vizualizace trendu a sezón: kombinujte sloupce (sezóna) a čáru (trend).

Intervaly spolehlivosti a velikosti efektu

• CI pro průměr: CONFIDENCE.T(alpha, STDEV.S(range), COUNT(range)); poté CI = AVERAGE(range) ± výsledek.
• Cohenovo d (light): d = (AVERAGE(x)-AVERAGE(y))/STDEV.P(pooled), kde pooled vypočítáte ze STDEV.S() a velikostí vzorků.
• R² a interpretace: z LINEST() nebo z trendové čáry v grafu.

Bootstrap „bez programování“

• Náhodný výběr s opakováním: v Excelu použijte pomocný sloupec s RAND(), přiřaďte pořadová čísla a „táhněte“ s opakováním přes INDEX() a náhodné indexy; v Sheets podobně s RAND() a RANDBETWEEN().
• Bootstrap průměru: vytvořte matici re-samplovaných průměrů, vypočítejte percentilové CI přes PERCENTILE.INC().

Výkon a limity: kdy Excel/Sheets nestačí

• Velikost dat: listy zpomalují při desítkách až stovkách tisíc řádků; použijte Power Query/Power Pivot (Excel) nebo přejděte na R/Python/SQL.
• Reprodukovatelnost: komplexní analýzy s mnoha kliky jsou hůře auditovatelné než skripty; maximalizujte vzorce, minimalizujte manuální kroky.
• Pokročilé metody: vícerozměrné modely, regularizace, nelineární modely – sáhněte po R/Pythonu.

Solver a optimalizace „light“

Excel Solver zvládne lineární/nehlinární optimalizace (alokace, plánování). Definujte cílovou buňku (min/max), měnící se buňky a omezení. V Sheets použijte doplňky (např. Linear Optimization).

Standardy reportingu: od tabulek k příběhu

• Tabulky výsledků: z Pivotu nebo z …IFS() funkcí; popisy sloupců, jednotky, vzorek (n), poznámky k metodě.
• Graf + věta s interpretací: každý graf doprovázejte větou „Co to znamená?“ a „Jaká je velikost efektu nebo nejistota?“.
• „One-pager“: pro vedení připravte Dashboard: klíčové KPI, filtry (slicery v Excelu), aktualizace jedním kliknutím (Refresh).

Kontrolní seznam kvality před odevzdáním

• Je datový model tidy (žádné sloučené buňky, jednoznačná záhlaví)?
• Jsou klíčové metriky počítány vzorci (nikoli ručně)?
• Je jasná cesta od „Raw“ k „Analysis“ (kroky Power Query, vzorce, komentáře)?
• Mají grafy správné osy, jednotky, čitelnou legendu a popis pointy?
• Jsou vypočteny nejistoty (CI, SE) a uvedeny limity/metodické poznámky?

Mini-kuchařka: často používané vzorce podle úlohy

• Podíl kategorie: COUNTIFS(cat,"A")/COUNTA(cat)
• Vážený průměr: SUMPRODUCT(values, weights)/SUM(weights)
• Percentil a IQR: PERCENTILE.EXC(x,0.25), PERCENTILE.EXC(x,0.75), rozdíl = IQR
• Flag odlehlých hodnot (Tukey): OR(x<Q1-1.5*IQR, x>Q3+1.5*IQR)
• Standardizace: