01 z 15
Konečný výsledek
Existuje mezera mezi Microsoft Excel a platformy špičkové obchodní inteligence (BI) po mnoho let. Vylepšení Pivot Table Microsoft Excel 2010 spolu s několika dalšími funkcemi BI ji činí skutečným konkurentem pro podnikové BI. Program Excel byl tradičně použit pro samostatnou analýzu a standardní nástroj, do něhož každý exportuje své závěrečné zprávy. Profesionální podniková inteligence je tradičně vyhrazena pro společnosti SAS, Business Objects a SAP.
Microsoft Excel 2010 (s pivotní tabulkou Excel 2010) společně s SQL Server 2008 R2, SharePoint 2010 a bezplatným doplňkem Microsoft PowerPoint 2010 "PowerPivot" vyústil ve špičkové podnikové zpravodajské a reportovací řešení.
Tento tutoriál pokrývá přímý scénář s kontingenční tabulkou aplikace Excel 2010 připojenou k databázi SQL Server 2008 R2 pomocí jednoduchého dotazu SQL. Také používám nástroje Slicers pro vizuální filtrování, které jsou nové v aplikaci Excel 2010. V blízké budoucnosti budu pokrývat složitější technologie BI pomocí výrazů Data Analysis Expressions (DAX) v programu PowerPivot pro aplikaci Excel 2010. Tato nejnovější verze aplikace Microsoft Excel 2010 může poskytnout skutečnou hodnotu uživatelské komunitě.
02 z 15
Vložit kontingenční tabulku
Můžete vložit kontingenční tabulku do nového nebo existujícího sešitu aplikace Excel. Možná budete chtít zvážit umístění kurzoru dolů o několik řádků shora. Tím získáte místo pro hlavičku nebo informace o společnosti, pokud sdílíte pracovní list nebo jej vytisknete.
- Otevřete nový nebo existující sešit aplikace Excel 2010 a klepněte na buňku, kde chcete být v levém horním rohu kontingenčního tabulky.
- Klikněte na kartu Vložit a v rozbalovací nabídce Tabulky klikněte na rozbalovací kontingenční tabulku. Zvolte kontingenční tabulku. Tím spustíte dialogové okno Vytvořit kontingenční tabulku.
03 z 15
Připojit kontingenční tabulku k serveru SQL (nebo jiné databázi)
Aplikace Excel 2010 dokáže načíst data ze všech hlavních poskytovatelů RDBMS (Relational Database Management System) . Ovladače SQL Server by měly být k dispozici pro připojení ve výchozím nastavení. Ale všechny hlavní databázové programy dělají ovladače ODBC (Open Database Connectivity), které vám umožní provést připojení. Podívejte se na jejich webové stránky, pokud potřebujete stáhnout ovladače ODBC.
V případě tohoto tutoriálu se připojuji k SQL Server 2008 R2 (verze zdarma pro SQL Express).
- A - Vytvoření kontingenčního formuláře je první formulář při vytváření připojení k serveru SQL Server. Zvolte možnost Použití externího zdroje dat a klikněte na tlačítko Vybrat spojení. Nechte místo, kde bude umístěn kontingenční tabulka, pokud nechcete vytvořit nový list a umístěte jej tam.
- B - Formulář existujícího připojení zobrazuje všechna připojení v aktuálním sešitu, v počítači a v síti, ke které jste aktuálně připojeni. Existující připojení jsou opravdu jen textové soubory s informacemi o připojení potřebnými pro přístup k určitému zdroji dat. V našem případě budeme vytvářet nový zdroj dat. Klikněte na tlačítko Procházet další.
- C - Klepnutím na tlačítko Nový zdroj spustíte Průvodce datovým připojením.
- D - Vyberte Microsoft SQL Server a klepněte na tlačítko Další.
- E - Zadejte název serveru a přihlaste se k pověření. Zvolte vhodnou metodu ověřování. Pokud si nejste jisti, kterou metodu použijete, obraťte se na správce databáze.
- Použití ověřování systému Windows: Tato metoda používá přihlašování k síti k přístupu k databázím serveru SQL.
- Použít následující uživatelské jméno a heslo: Tato metoda se používá, pokud byl SQL Server nakonfigurován se samostatnými uživateli pro přístup k databázím.
- F - V tomto kroku zvolíme tabulku jako zástupný symbol. Chystáme se nahradit tabulku vlastním SQL, který poskytne přesně data, která chceme, v našem sešitu aplikace Excel.
- Vyberte databázi, ke které se budete připojovat. V tomto příkladu se připojujeme k ukázkové databázi AdventureWorks poskytované společností Microsoft. Zkontrolujte připojení ke konkrétní tabulce a vyberte první tabulku. Nezapomeňte, že z této tabulky nebudeme získávat data.
- Klepněte na tlačítko Dokončit, čímž zavřete průvodce a vrátíte se do sešitu. Vyměníme zástupnou tabulku pro náš vlastní dotaz SQL.
Budete vráceni do formuláře Vytvořit kontingenční tabulku (A). Klepněte na tlačítko OK.
04 z 15
Kontingenční tabulka je dočasně připojena k tabulce SQL
V tomto okamžiku jste připojeni k zástupné tabulce a máte prázdnou kontingenční tabulku. Můžete vidět, že vlevo bude kontingenční tabulka a vpravo je seznam dostupných polí.
05 z 15
Otevřete vlastnosti připojení
Než začneme volit data pro kontingenční tabulku, musíme změnit připojení k dotazu SQL. Ujistěte se, že jste na kartě Možnosti a klikněte na položku Změnit zdroj dat v části Údaje. Zvolte vlastnosti připojení.
Toto vyvolá formulář Connection Properties. Klikněte na kartu Definice. Zobrazí se informace o připojení k aktuálnímu připojení k serveru SQL Server. Zatímco odkazuje na soubor s připojením, data jsou skutečně vložena do tabulky.
06 z 15
Aktualizace vlastností připojení pomocí dotazu
Změňte typ příkazu z tabulky na SQL a přepište existující příkazový text pomocí dotazu SQL. Zde je dotaz, který jsem vytvořil z ukázkové databáze AdventureWorks:
SELECT Sales.SalesOrderHeader.SalesOrderID,
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.ShipDate,
Sales.SalesOrderHeader.Status,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt,
Sales.SalesOrderHeader.Freight,
Sales.SalesOrderHeader.TotalDue,
Sales.SalesOrderDetail.SalesOrderDetailID,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice,
Sales.SalesOrderDetail.LineTotal,
Production.Product.Name,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Production.Product.ListPrice,
Production.Product.ProductLine,
Production.ProductSubcategory.Name AS ProductCategory
Z Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Produkce.Produkt ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Production.ProductSubcategory ON Výroba.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID
Klepněte na tlačítko OK.
07 z 15
Přijmout upozornění na připojení
Zobrazí se dialogové okno Varování aplikace Microsoft Excel. Je to proto, že jsme změnili informace o připojení. Když jsme původně vytvořili připojení, uloží informace do externího souboru .ODC (ODBC Data Connection). Data v sešitu byla stejná jako soubor .ODC, dokud jsme se nezměnili z typu příkazu tabulky na typ příkazu SQL v kroku # 6. Varování vám říká, že data již nejsou synchronizována a odkaz na externí soubor v sešitu bude odstraněn. Toto je v pořádku. Klepněte na tlačítko Ano.
08 z 15
Kontingenční tabulka připojená k serveru SQL s dotazem
To se vrátí do sešitu aplikace Excel 2010 s prázdnou kontingenční tabulkou. Vidíte, že dostupná pole jsou nyní odlišná a odpovídají polím v dotazu SQL. Nyní můžeme začít psát pole do kontingenční tabulky.
09 z 15
Přidání polí do kontingenční tabulky
V seznamu polí kontingenčního tabulátoru přetáhněte oblast ProductCategory do oblasti Row Labels, pole OrderDate to Label Column a oblast TotalDue to Values. Obrázek zobrazuje výsledky. Jak je vidět, pole data má jednotlivé data, takže kontingenční tabulka vytvořila sloupec pro každé jedinečné datum. Naštěstí má aplikace Excel 2010 některé funkce, které nám pomohou zorganizovat data.
10 z 15
Přidat pole pro datová pole
Funkce seskupování nám umožňuje uspořádat data do let, měsíců, čtvrtletí apod. To pomůže shrnout data a usnadnit uživateli interakci s nimi. Klepněte pravým tlačítkem myši na jednu ze záhlaví sloupců data a vyberte možnost Skupina, která vyvolá formulář Seskupování.
11 z 15
Zvolte možnost Seskupování podle hodnot
V závislosti na druhu dat, které seskupujete, bude formulář vypadat trochu jinak. Aplikace Excel 2010 umožňuje seskupit data, čísla a vybraná textová data. V tomto výukovém programu seskupujeme OrderDate, takže formulář zobrazí možnosti týkající se seskupení dat.
Klikněte na měsíce a roky a klikněte na tlačítko OK.
12 z 15
Kontingenční tabulka seskupená podle let a měsíců
Jak je vidět na obrázku výše, data jsou seskupena podle prvního roku a potom měsíčně. Každý z nich má znaménko plus a mínus, které umožňuje rozbalit a sbalit podle toho, jak chcete zobrazit data.
V tomto okamžiku je kontingenční tabulka velmi užitečná. Každé z polí může být filtrováno, ale problém je, že neexistuje vizuální stopa o aktuálním stavu filtrů. Ke změně zobrazení také trvá několik kliknutí.
13 z 15
Vložte slicer (nový v aplikaci Excel 2010)
Slicery jsou v aplikaci Excel 2010 nové. Slicery jsou v podstatě ekvivalentní vizuálnímu nastavení filtrů existujících polí a vytváření filtrů reportů v případě, že položka, kterou chcete filtrovat, není v aktuálním pohledu na kontingenční tabulku. Tato pěkná věc o aplikaci Slicers je pro uživatele velmi snadné měnit zobrazení dat v kontingenční tabulce a také vizuální indikátory, které se týkají aktuálního stavu filtrů.
Chcete-li vložit Slicery, klikněte na kartu Možnosti a klikněte na Vložit slicer ze sekce Seřadit a filtr. Zvolte možnost Vložit slicer, který otevře formulář Insert Slicers. Zkontrolujte, kolik polí chcete mít k dispozici. V našem příkladu jsem přidal Years, CountryRegionName a ProductCategory. budete možná muset umístit Slicery tam, kde je chcete. Ve výchozím nastavení jsou vybrány všechny hodnoty, což znamená, že nebyly použity žádné filtry.
14 z 15
Kontingenční tabulka s uživatelsky přívětivými slicery
Jak vidíte, Slicery zobrazují všechna vybraná data. Uživatelům je velmi jasné, jaké údaje jsou v aktuálním pohledu na kontingenční tabulku.15 z 15
Zvolte hodnoty ze Slicerů, které tabulky kontingencí aktualizací
Klepněte na různé kombinace hodnot a zjistěte, jak se změní zobrazení kontingenčního tabulky. Můžete použít typické klepnutí na tlačítko Microsoft v nástroji Slicers, což znamená, že pokud můžete použít ovládací prvek + klepnutím vyberte více hodnot nebo klávesou Shift + Klepnutím vyberte rozsah hodnot. Každý Slicer zobrazuje vybrané hodnoty, což činí zřejmé, jaký je stav kontingenční tabulky z hlediska filtrů. Kliknutím na rozbalovací pole Rychlé styly v sekci Slicer na kartě Možnosti můžete změnit styly slicerů.
Zavedení nástroje Slicers skutečně zlepšilo použitelnost kontingenčních tabulek a přesunul program Excel 2010 mnohem blíže k profesionálním nástrojům pro podnikovou inteligenci. Kontingenční tabulky se v aplikaci Excel 2010 poměrně zlepšily a při kombinaci s novým modulem PowerPivot se vytváří vysoce výkonné analytické prostředí.