Jak konfigurovat kontingenční tabulky aplikace Excel 2010

01 z 15

Konečný výsledek

To je konečný výsledek tohoto kroku krok za krokem - Klikněte na obrázek pro zobrazení plnohodnotné verze.

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

Umístěte kurzor přesně tam, kde chcete svou kontingenční tabulku a klikněte na Insert | Kontingenční tabulka.

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.

03 z 15

Připojit kontingenční tabulku k serveru SQL (nebo jiné databázi)

Vytvořte dotaz SQL a poté se připojte k serveru SQL, abyste vložili datový řetězec připojení do tabulky aplikace Excel.

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).

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

Kontingenční tabulka je připojena k serveru SQL Server s tabulkou zástupných symbolů.

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í

Otevřete formulář 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 tabulku dotazu SQL.

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í

Klepnutím na Ano na 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

Kontingenční tabulka je připravena přidat data.

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

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

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

Vyberte pole seskupení pro pole Datum.

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ů

Políčka Datum jsou seskupena 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)

Přidat slicery do kontingenční tabulky.

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

Slicery usnadňují uživatelům filtrování kontingenčních tabulek.
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í

Vyberte kombinaci slicerů, chcete-li změnit zobrazení dat.

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í.