Úvod do práce s Excelem pro novináře

Příklad datasetu faktur Ministerstva pro místní rozvoj

V rámci tohoto cvičení si vyzkoušíme, jak dataset získat, vypořádat se se zrádným kódováním textu, seznámit se s obsahem několika tisíců řádků vydolovat z nich zajímavé informace za pomoci filtrů a funkcí / výpočtů.

Získání dat

Stáhněte si soubor s fakturami ve formátu csv:

na webu data.mmr.cz -> Seznam uhrazených faktur MMR v roce 2019 -> klikněte na Faktury_2019.csv -> zelené tlačítko Stáhnout

Pro připomenutí:

.csv (comma separated values)
= textový soubor, ve kterém jsou hodnoty ve sloupcích odděleny čárkou (nebo středníkem atp.) a zaznamenány po řádcích, obsahuje pouze holý text bez
jakéhokoliv formátování, Excel s ním automaticky pracuje jako s tabulkou

.xlsx
= excelovský soubor, který může obsahovat jeden nebo více listů s tabulkami, často obsahuje text i formátování (může se promítnou do velikosti souboru), podoba tabulky může být přizpůsobena tisku na A4 (např. do reportů a výkazů, které mají být čitelné pro lidi a ne pro počítače), proto je zapotřebí strávit více času jejím čištěním

POZOR na omezení počtu řádků a sloupců zastaralého formátu xls!

Než se pustíme do práce

Nikdy bychom neměli čistit a upravovat původní soubor pro případ, že se k němu chceme vrátit a něco dohledat (nebo ukázat, z čeho jsme vycházeli). Nejvhodnější je soubor v původní podobě uložit a vytvořit si jeho kopii, s níž budeme dále pracovat.

Nezapomeňte si často ukládat mezikroky (ctrl + s / cmd + s).

Otevření souboru a první úpravy

Otevřete na svém počítači stažený soubor v programu Excel. Co vidíte?

V lepším případě přehlednou a dokonale čitelnou tabulku. V horším případě se může objevit něco takového:

Změna kódování textu

Může to být nastavením Excelu nebo častěji kódováním (tzv. text encoding) staženého souboru. Řešení je docela snadné. Otevřeme si buď nový list (+ dole na liště) nebo úplně nový prázdný sešit (workbook). V horní liště vybereme sekci Data a vlevo u možnosti Načíst data klikneme na možnost Z textu/CSV. Vybereme stažený soubor a v dalším kroku změníme Původ souboru na 65001: Unicode (UTF-8).

V náhledu se přesvědčíme, že jsou znaky v pořádku a klikneme na Načíst.

Pryč s formátováním

Naneštěstí se nám tabulka většinou načte v ještě ošklivější formátované podobě. Než se dostaneme dál, barevného formátování se snadno zbavíme.

Celou tabulku si označíme — buď kliknutím na trojúhelníček v levém horním rohu tabulky nebo stisknutím ctrl + a / cmd + a — a v horní liště vybereme sekci Domů, v jejíž pravé části klikneme na fialový symbol gumy a z možností vybereme Vymazat formáty.

Změna formátu buněk

Bez formátování už tabulka vypadá lépe, ale možná jste si všimli, že ve sloupcích Datum příchodu a Datum úhrady jsou najednou místo běžných dat (např. 21/10/2019) jen zdánlivě náhodná čísla. Abychom to napravili, označíme si oba sloupce a nahoře (opět v sekci Domů) uprostřed změníme Obecný formát textu na Datum (krátké). Voilà — svět je zase v pořádku!

Data, seznamte se!

Konečně přišel čas na to, seznámit se s obsahem našeho datasetu. Při zběžném pohledu zjistíme, že dataset má 13 sloupců (A-M) a 8292 řádků. Ve sloupcích jsou informace o fakturách (rok, různá označení dokladu, fakturující osoba a její IČ, částka, data příchodu a úhrady, předmět fakturace a název členění rozpočtu) a v řádcích jednotlivé položky.

Vzpomínáte si na principy “tidy data”? Přesně takhle mají data vypadat. :)

Filtrování

Chceme-li si udělat lepší přehled o tom, jaké hodnoty se v jednotlivých sloupcích vyskytují, můžeme kliknout na malou šipku vedle názvu daného sloupce a podívat se na seznam hodnot v rámci filtru. Největší smysl to dává u kategorií (spíš než u číselných hodnot nebo jednotlivých předmětů).

Pokud si chceme zobrazit jen řádky, které odpovídají námi vybrané kategorii, vyfiltrujeme si je kliknutím ve stejném seznamu. Například si můžeme ve sloupci Název členění rozpočtu vybrat jen Věcné dary a následně je ve sloupci Částka (opět kliknutím na šipku vedle názvu sloupce) seřadit od největšího po nejmenší nebo naopak. Můžeme se podívat, jaké věcné dary se proplácely a kdy — nepřekvapivě většina z nich v období před Vánoci.

Klidně si tímto způsobem proklikejte i další oblasti. Určitě se v nich skrývá spousta zajímavostí.

Výběr otázky na základě dostupných dat

Osobně mě ale zaujala jedna konkrétní, a to Náhrady obcím za pohřby zesnulých, o které se nemá kdo postarat. Do této kategorie patří platby za sociální pohřby a mě by zajímalo, kolik takový sociální pohřeb v různých městech stojí.

Ve filtru si proto odkliknu Věcné dary a naopak vyberu výše zmíněnou kategorii. No a teď to teprve bude zajímavé! :)

POZOR! na používání funkcí (vč. sčítání, odčítání, změn) u vyfiltrovaných výsledků

Při použití filtru se ostatní řádky tabulky pouze skryjí a agregační operace (např. součet hodnot sloupce) probíhají na celém datasetu, ne jen na vyfiltrované části. Pokud chceme s vybranou částí dat dál pracovat na úrovni buněk, můžeme si výsledky filtru vyjmout a přesunout. Pokud nás zajímají součty, změny nebo rozdíly po kategoriích, řešením je kontingenční tabulka (o té si řekneme víc později).

Analýza dat

Co s vyfiltrovanými daty

V posledním sloupci jsme si filtrem vybrali Náhrady obcím za pohřby zesnulých, o které se nemá kdo postarat, před sebou vidíme jen řádky, které odpovídají této kategorii. Ostatní nevidíme, ale pořád tu jsou. Proto je potřeba vybrané řádky vzít a přesunout jinam.

Opět si označíme celou tabulku (trojúhelník v levém horním rohu). Potom v sekci Domů klikneme vpravo na možnost Najít a vybrat a potom Přejít na… Vlevo dole vybereme Jinak…, potom z nabídnutých možností Pouze viditelné buňky a konečně klikneme na OK. Náš výběr se tím změní jen na viditelné buňky. Teď už můžeme zkopírovat obsah buněk pomocí ctrl + c (cmd + c) a vložit je do nového prázdného listu tím, že klikneme na první buňku (A1) a stiskneme ctrl + v (cmd + v). Konečně máme výběr z datasetu, se kterým si můžeme dělat, co chceme!

Nejvyšší a nejnižší hodnoty

Na novém listu s vyfiltrovanými hodnotami si zase označíme celou tabulku (určitě už víte jak!) a v sekci Domů na horní liště vybereme Seřadit a filtrovat. Tím se nám vedle názvů sloupců objeví šipky a můžeme nejen dál filtrovat, ale také řadit data podle hodnot v daném sloupci. Podíváme se proto na nejvyšší a nejnižší hodnoty ve sloupci Částka.

Co si o nich myslíte? Proč jsou asi tak vysoké nebo nízké?

Přepočet výdajů na jednoho zesnulého

Jak jste si nejspíš všimli, ve sloupci Předmět fakturace jsou vypsána jména zesnulých, za jejichž pohřeb každé město fakturuje výdaje. V některých případech je jich na řádku hned několik, v jiných je jeden jeden. Abychom měli lepší představu o výdajích za jeden pohřeb a nepoukazovali na vysoké hodnoty bezdůvodně, spočítáme si počet zesnulých, jejichž pohřby město fakturuje a přepočítáme celkovou částku na jednoho.

Přidání nového sloupce

Nejdříve vedle sloupce Předmět fakturace přidáme prázdný sloupec tím, že pravým tlačítkem klikneme na sloupec vpravo od něj (M) a vybereme Vložit buňky. Nově vytvořený sloupec v prvním řádku nazveme Počet zesnulých.

Určení (přibližného) počtu zesnulých

Počet zesnulých spočítáme na základě počtu čárek, které oddělují jednotlivá jména ve sloupci Předmět fakturace. Do první prázdné buňky pod názvem nového sloupce napíšeme následující funkci: =DÉLKA(PROČISTIT(K2))-DÉLKA(DOSADIT(PROČISTIT(K2);”,”;””))+1

Přesvědčíme se, že adresa buňky (v tomto případě K2) odpovídá správné buňce, a že výsledek odpovídá počtu jmen v prvním řádku. Pokud ano, dvakrát klikneme na černý křížek, který se objeví, když přejedeme přes pravý dolní roh nově vyplněné buňky. Tímto se stejná funkce aplikuje na celý sloupec a vždy použije odpovídající buňku na daném řádku.

Přepočet částky na jednoho zesnulého

Vedle nového sloupce označeného jako Počet zesnulých přidáme další prázdný sloupec, který nazveme Částka za zesnulého. Podobně jako v předchozím kroku vypíšeme do první prázdné buňky pod názvem nového sloupce funkci, tentokrát už jednodušší: =G2/L2

(A ujistíme se, že adresy buněk odpovídají prvním hodnotám ve sloupci Částka a Počet zesnulých.) Výsledkem bude celková částka vydělená počtem zesnulých. Stejným způsobem opět aplikujeme funkci na celý sloupec.

Průměr, medián, modus a rozložení hodnot

Abychom se toho o hodnotách dozvěděli víc, spočítáme si průměr, medián a modus pro naposledy vytvořený sloupec. Stačí kliknou na prázdnou buňku mimo naše data, napsat = a vybrat příslušnou funkci. Do závorek (i podle nápovědy) patří rozsah buněk, pro které chceme hodnotu spočítat, tedy stačí kliknout na náš vybraný sloupec a přidat závorku na konec.

Zjistíme, že nejčastější hodnota je 4663, medián je 8898,33 a průměr 9211,63.

A co dál?

Když už máme konečně výsledky správně, je vidět, že hodnoty jsou rozloženy rovnoměrně a nejvyšší částka dosahuje cca 20 000 Kč. To znamená, že se můžeme pustit do analýzy nákladů na sociální pohřby v jednotlivých městech a můžeme je začít porovnávat.

Tento návod je součástí výuky předmětu Digitální žurnalistika a datová analýza (JJM300) v rámci magisterského programu Žurnalistiky na FSV UK.