Návod na analýzy dat prostřednictvím MS EXCEL

PODPORA SESTAVENÍ VÝROČNÍ ZPRÁVY O HOSPODAŘENÍ VYSOKÉ ŠKOLY S POMOCÍ MS EXCEL NAD DATY SW IFIS

Úvod

Tento dokument je jedním z výstupů úkolu SW podpory při sestavení výroční zprávy o hospodaření vysoké školy, realizovaného v rámci projektu CRP 2017 (hlavní řešitel ČVUT, řešitel dílčího úkolu JU).
Úkolem bylo najít nástroj a ověřit jeho použitelnost při zpracování ad-hoc analýz a rozborů vybraných účetních ukazatelů v hospodaření vysoké školy ve sledovaném roce a ve srovnání s roky předcházejícími. Zdrojovou databází je účetní systém iFIS na databázi Oracle.
Z předchozích výstupů projektů CRP bylo převzato doporučení na zpracování takových dat prostředky MS EXCEL (kontingenční tabulky) nad daty importovanými z databáze Oracle a to prostřednictvím rozšíření MS EXCEL – POWER QUERY (PWQ). Stručný návod na implementaci doplňku PWQ a připojení k databázi Oracle je předmětem samostatného dokumentu (CRP 2016).
Tento dokument je stručným návodem na připojení MS EXCEL k databázi iFIS prostřednictvím speciálně vytvořených pohledů ročních obratů (view) s příkladem využití těchto pohledů v kontingenčních tabulkách. Dokument obsahuje také několik užitečných rad při úpravách a nastavení kontingenčních tabulek. Neslouží však jako návod na samotné vytvoření kontingenční tabulky.

Příprava na straně databáze Oracle

Pro verzi iFIS 11.14. byly vyvinuty nové pohledy na roční obraty analytických účtů (VEKROBAU) a roční obraty typů účtů (VEKROBTU). Tyto pohledy budou součástí distribuce standardu IFIS od roku 2017. Pohledy obsahují data bez omezení přístupových práv osob ke zdrojům financování. Přístupy na tyto DB pohledy povoluje administrátor pomocí běžných nástrojů správce aplikace.

Přístup do databáze

• Uživatel, který přistupuje přes PWQ do databáze musí mít nastavené role (např. DEFAULT_FAM1 pro FIS_01) aby mohl číst z View. Toto se nastaví při vytváření uživatele. Není podmínkou přístup do aplikace iFis.

Struktura DB pohledů pro export do XLS

Obr. 1 Struktura DB pohledů pro export do XLS

Příprava na straně databáze MS EXCEL

• V MS Excel záložka "Data"
• Klikneme na "Data" (pokud máme MS Office 2016) toto tlačítko nahrazuje Power Query, které je již součástí – bez instalace samostatného doplňku MS Power Query jako v předchozích verzích MS Office.
• Poté klikneme na "Nový dotaz" - v rozbalovacím menu se nabídne možnost vybrat připojení "Z databáze" – "Z databáze Oracle"
• Po odkliknutí se zobrazí dialogové okno, do kterého napíšeme název databáze, kam se budeme přihlašovat.

Obr. 2 Příprava na straně databáze MS EXCEL

Obr. 3 Příprava na straně databáze MS EXCEL

Obr. 4 Příprava na straně databáze MS EXCEL

• Následuje dialogové okno přihlášení – POZOR!!! Nabídne to přihlášení Windows či Databáze – dáváme databáze – naše jméno a heslo do reálu, a pak už se rovnou dostáváme na výběr z jednotlivých Views.
• Tímto se dostáváme do editoru dotazů, kde už si můžeme filtrovat, měnit datové typy aj. a načíst potřebná data do MS Excel.

Obr. 5 Příprava na straně databáze MS EXCEL

Obr. 6 Příprava na straně databáze MS EXCEL

Vzorové příklady kontingenčních tabulek

Obr. 7 Vzorové příklady kontingenčních tabulek

Obr. 8 Vzorové příklady kontingenčních tabulek

Obr. 9 Vzorové příklady kontingenčních tabulek

Možnosti kontingenční tabulky

• Pravé tlačítko na kontingenční tabulce
• "Možnosti kontingenční tabulky"
• V záložce "Rozložení a formát" lze nastavit hodnotu pro chybové hodnoty a prázdné buňky
• V záložce "Součty a filtry" lze nastavit zobrazení celkových součtů pro řádky a sloupce

Obr. 10 Možnosti kontingenční tabulky

Obr. 11 Možnosti kontingenční tabulky

Obr. 12 Možnosti kontingenční tabulky

Zobrazení seznamu polí

• Pravé tlačítko na kontingenční tabulce
• "Zobrazit seznam polí"

Obr. 13 Zobrazení seznamu polí

Zobrazení souhrnu řádků

• V seznamu polí, levé tlačítko na konkrétní pole
• "Nastavení pole"
• V oblasti "Souhrny" zvolit "Vlastní" a vybrat operaci

Obr. 14 Zobrazení souhrnu řádků

Obr. 15 Zobrazení souhrnu řádků

Obr. 16 Zobrazení souhrnu řádků

Nastavení polí hodnot

• V seznamu polí, levé tlačítko na konkrétní pole hodnot
• "Nastavení polí hodnot"
• V záložce "Souhrn dat" lze nastavit operaci, která se bude provádět s daty
• V záložce "Zobrazit hodnoty jako" lze nastavit operace, které porovnávají data s dalšími hodnotami
• Tlačítko "Formát čísla" umožnuje nastavit zobrazení hodnoty v buňce

Obr. 17 Nastavení polí hodnot

Obr. 18 Nastavení polí hodnot

Obr. 19 Nastavení polí hodnot

Obr. 20 Nastavení polí hodnot

Návrh kontingenční tabulky

• v záložce "Návrh" lze nastavit barvu a styl kontingenční tabulky (např. pruhované řádky, zobrazení celkových součtů)

Obr. 21 Nastavení polí hodnot