Nakonfigurujte kontingenční tabulky aplikace Excel 2010

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 podniky jako SAS, Business Objects a SAP.

01 z 15

Konečný výsledek

Microsoft Excel 2010 (s pivotní tabulkou Excel 2010) spolu s SQL Server 2008 R2, SharePoint 2010 a bezplatným doplňkem Microsoft PowerPoint 2010 "PowerPivot" vyústil v řešení špičkové obchodní inteligence a reportingu.

Tento tutoriál pokrývá jednoduchý scénář s kontingenční tabulkou aplikace Excel 2010 připojenou k databázi SQL Server 2008 R2 pomocí jednoduchého dotazu SQL.

Pokračujte ve čtení níže

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.

Pokračujte ve čtení níže

03 z 15

Připojit tabulku kontingenčních tabulek k serveru SQL

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í. Podívejte se na jejich webové stránky, pokud potřebujete stáhnout ovladače ODBC.

V případě tohoto tutoriálu se připojujeme k SQL Server 2008 R2 (verze zdarma pro SQL Express).

1. Otevřete formulář Vytvořit kontingenční tabulku. 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.
2. Otevřete formulář Existing Connections. Klikněte na tlačítko Procházet další.
3. Klepnutím na tlačítko Nový zdroj spustíte Průvodce datovým připojením.
4. Vyberte Microsoft SQL Server a klepněte na tlačítko Další.
5. Zadejte název serveru a přihlašovací údaje. Zvolte vhodnou metodu autentizace:
   1. 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.
   2. Použijte následující uživatelské jméno a heslo: Tato metoda se používá při konfiguraci serveru SQL se samostatnými uživateli pro přístup k databázím.
6. Nahraďte tabulku vlastními SQL, které poskytují přesně požadovaná data v našem sešitu aplikace Excel:
   1. 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.
   2. 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 vlevo, kde bude kontingenční tabulka, a vpravo je seznam dostupných polí.

Pokračujte ve čtení níže

05 z 15

Otevřete vlastnosti připojení

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ý jsme vytvořili 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 ProductCategoryZ Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Produkce.Produkt ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOINSales.vIndividualCustomer ON Sales.Customer.CustomerID =Sales.vIndividualCustomer.CustomerID INNER JOINProduction.ProductSubcategory ON Výroba.Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryID

Klepněte na tlačítko OK.

Pokračujte ve čtení níže

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.

Pokračujte ve čtení níže

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. Jak je vidět, pole data má jednotlivé data, takže kontingenční tabulka vytvořila sloupec pro každé jedinečné datum. Program Excel 2010 má některé vestavěné funkce, které nám pomáhají organizovat data pole.

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ář seskupení.

Pokračujte ve čtení níže

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ů

Údaje jsou seskupeny podle prvního roku a poté měsíce. 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ů. Pro změnu pohledu 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.

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á jednotka Slicer zobrazuje vybrané hodnoty, což činí skutečně 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ů.