Ako nakonfigurovať kontingenčné tabuľky programu Excel 2010

by Tim Perdue

01 z 15

Konečný výsledok

Toto je konečný výsledok tutoriálu krok za krokom - Kliknutím na obrázok sa zobrazí verzia v plnej veľkosti.

Medzi platformami Microsoft Excel a platformami najvyššej úrovne obchodnej inteligencie (BI) existuje mnoho rokov. Vylepšenia Pivot Table Microsoft Excel 2010 spolu s niekoľkými ďalšími funkciami BI z neho urobili skutočného konkurenta pre podnikové BI. Program Excel sa tradične používa na samostatnú analýzu a štandardný nástroj, do ktorého každý exportuje svoje konečné správy. Profesionálna obchodná inteligencia je tradične vyhradená pre spoločnosti SAS, Business Objects a SAP.

Microsoft Excel 2010 (s pivotnou tabuľkou Excel 2010) spolu s SQL Server 2008 R2, SharePoint 2010 a bezplatným doplnkom Microsoft PowerPoint 2010 "PowerPivot" vyústil do špičkového riešenia v oblasti obchodnej inteligencie a reportingu.

Tento výukový program pokrýva scenár s priamym pokrokom s kontingenčnou tabuľkou Excel 2010 pripojenou k databáze SQL Server 2008 R2 pomocou jednoduchého dotazu SQL. Používam tiež nástroje Slicers pre vizuálne filtrovanie, ktoré sú nové v programe Excel 2010. V blízkej budúcnosti budem pokrývať zložitejšie techniky BI pomocou výrazov Data Analysis Expressions (DAX) v aplikácii PowerPivot for Excel 2010. Táto najnovšia verzia programu Microsoft Excel 2010 môže poskytnúť skutočnú hodnotu pre vašu komunitu používateľov.

02 z 15

Vložiť kontingenčnú tabuľku

Umiestnite kurzor presne tam, kde chcete svoju kontingenčnú tabuľku, a kliknite na položku Vložiť | Kontingenčná tabuľka.

Môžete vložiť kontingenčnú tabuľku do nového alebo existujúceho zošita programu Excel. Možno budete chcieť zvážiť umiestnenie kurzora nadol niekoľko riadkov zhora. To vám poskytne priestor na hlavičku alebo informácie o spoločnosti v prípade, že zdieľate pracovný hárok alebo ho vytlačte.

Otvorte nový alebo existujúci zošita programu Excel 2010 a kliknite na bunku, kde chcete byť v ľavom hornom rohu kontingenčnej tabuľky.
Kliknite na kartu Vložiť a kliknite na rozbaľovaciu tabuľku v tabuľke. Zvoľte kontingenčnú tabuľku. Spustí sa dialógový formulár Vytvoriť kontingenčný tabuľku.

03 z 15

Pripojiť kontingenčnú tabuľku na SQL Server (alebo inú databázu)

Vytvorte svoj dotaz SQL a potom sa pripojte k serveru SQL, aby ste vložili reťazec údajov o pripojení do tabuľky programu Excel.

Program Excel 2010 dokáže načítať dáta zo všetkých hlavných poskytovateľov RDBMS (System Relational Management System) . Ovládače SQL Server by mali byť k dispozícii pre pripojenie ako predvolené. Ale všetky hlavné databázový softvér robia ODBC (Open Database Connectivity) ovládače, aby vám umožnili pripojenie. Skontrolujte ich webové stránky, ak potrebujete stiahnuť ovládače ODBC.

V prípade tohto tutoriálu sa pripájam k SQL Server 2008 R2 (bezplatná verzia SQL Express).

A - Vytvoriť kontingenčný formulár je prvý formulár pri vytváraní pripojenia k serveru SQL Server. Vyberte možnosť Použiť externý zdroj údajov a kliknite na tlačidlo Vybrať spojenie. Nechajte umiestnenie miesta, kde bude umiestnená kontingenčná tabuľka, pokiaľ nechcete vytvoriť nový pracovný hárok a umiestnite ho tam.
B - Formulácia Existujúce pripojenia zobrazuje všetky pripojenia v aktuálnom zošite, v počítači a sieti, ku ktorej ste práve pripojený. Existujúce pripojenia sú naozaj len textové súbory s informáciami o pripojení potrebnými na prístup k určitému zdroju údajov. V našom prípade budeme vytvárať nový zdroj údajov. Kliknite na tlačidlo Prehľadať viac.
C - Kliknutím na tlačidlo Nový zdroj sa spustí Sprievodca dátovým pripojením.
D - Vyberte si Microsoft SQL Server a kliknite na Ďalej.
E - zadajte meno servera a prihlasovacie poverenia. Vyberte vhodnú metódu overovania. Ak si nie ste istí, ktorú metódu chcete použiť, obráťte sa na správcu databázy.
- Použitie overenia pravosti systému Windows: Táto metóda používa vaše prihlasovacie meno pre prístup do databáz SQL Server.
- Použite nasledujúce používateľské meno a heslo: Táto metóda sa používa, keď bol SQL Server nakonfigurovaný so samostatnými používateľmi na prístup k databázam.
F - V tomto kroku si vyberieme stôl ako zástupný symbol. Budeme nahradiť tabuľku vlastným SQL, ktorý poskytne presne dáta, ktoré chceme v našom zošite.
- Vyberte databázu, ku ktorej sa pripájate. V tomto príklade sa pripájame k ukážkovej databáze AdventureWorks poskytovanej spoločnosťou Microsoft. Začiarknite políčko Pripojiť ku konkrétnej tabuľke a vyberte prvú tabuľku. Nezabudnite, že z tejto tabuľky nebudeme získavať údaje.
- Kliknite na tlačidlo Dokončiť, čím sa sprievodca zatvorí a vráti sa do zošita. Pre našu vlastnú SQL dotaz vymeniame tabuľku s náhradnými znakmi.

Budete vrátení do formulára Vytvoriť kontingenčný tabuľku (A). Kliknite na tlačidlo OK.

04 z 15

Kontingenčná tabuľka dočasne pripojená k tabuľke SQL

Kontingenčná tabuľka je pripojená k serveru SQL Server s tabuľkou s umiestnením.

V tomto okamihu ste pripojili k zástupnému stolu a máte prázdnu kontingenčnú tabuľku. Môžete vidieť, že na ľavej strane bude kontingenčná tabuľka a na pravej strane je zoznam dostupných polí.

05 z 15

Otvoriť vlastnosti pripojenia

Otvoriť formulár Vlastnosti pripojenia.

Skôr než začneme vybrať údaje pre kontingenčnú tabuľku, musíme zmeniť pripojenie k dotazu SQL. Skontrolujte, či ste na karte Možnosti a kliknite na položku Zmeniť zdroj údajov v sekcii Údaje. Vyberte Vlastnosti pripojenia.

Toto vyvolá formulár Vlastnosti pripojenia. Kliknite na kartu Definícia. Zobrazí sa vám informácie o pripojení pre aktuálne pripojenie k serveru SQL Server. Zatiaľ čo odkazuje na súbor s pripojením, údaje sú skutočne vložené do tabuľky.

06 z 15

Aktualizovať vlastnosti pripojenia pomocou dotazu

Zmena tabuľky na dotaz SQL.

Zmeňte typ príkazu z tabuľky na SQL a prepíšte existujúci príkazový text pomocou SQL dotazu. Tu je dopyt, ktorý som vytvoril z ukážkovej databázy 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 Produkcia.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.Produkt.ProduktSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

Kliknite na tlačidlo OK.

07 z 15

Upozornenie na prijímanie pripojenia

Kliknite na Áno, aby ste upozornili na pripojenie.

Zobrazí sa dialógové okno Upozornenie programu Microsoft Excel. Dôvodom je zmena informácií o pripojení. Keď sme pôvodne vytvorili spojenie, uložili informácie do externého súboru .ODC (ODBC Data Connection). Údaje v zošite boli rovnaké ako súbor .ODC, kým sme sa v kroku č. 6 nezmenili z typu príkazu tabuľky na typ príkazu SQL. Upozornenie vám povie, že údaje už nie sú synchronizované a odkaz na externý súbor v zošni sa odstráni. Toto je v poriadku. Kliknite na tlačidlo Áno.

08 z 15

Kontingenčná tabuľka pripojená k serveru SQL s dotazom

Kontingenčná tabuľka je pripravená na pridanie údajov.

To sa vráti do zošita programu Excel 2010 s prázdnou kontingenčnou tabuľkou. Môžete vidieť, že dostupné polia sú teraz odlišné a zodpovedajú polia v dotaze SQL. Teraz môžeme začať pridávať polia do kontingenčnej tabuľky.

09 z 15

Pridať polia do kontingenčnej tabuľky

Pridajte polia do kontingenčnej tabuľky.

V zozname kontingenčných tabuliek presuňte oblasť ProductCategory do oblasti Row Labels, oblasť OrderDate to Labels a TotalDue to Values. Obrázok zobrazuje výsledky. Ako vidíte, pole dátumu má jednotlivé dátumy, takže kontingenčná tabuľka vytvorila stĺpec pre každý jedinečný dátum. Našťastie Excel 2010 má niekoľko funkcií, ktoré nám pomôžu usporiadať dátumové polia.

10 z 15

Pridať zoskupenie pre polia dátumu

Pridajte pole Zoskupenia pre dátum.

Funkcia zoskupovania nám umožňuje usporiadať dáta do rokov, mesiacov, štvrťrokov a pod. Pomôže to zhromažďovať údaje a uľahčiť používateľovi interakciu s ním. Kliknite pravým tlačidlom myši na jednu z hlavičiek stĺpcov dátumu a vyberte položku Skupina, ktorá vyvolá formulár zoskupenia.

11 z 15

Zvoľte možnosť Zoskupenie podľa hodnôt

Vyberte položky zoskupenia pre pole dátumu.

V závislosti od druhu údajov, ktoré zoskupujete, bude formulár vyzerať trochu inak. Program Excel 2010 umožňuje zoskupiť dátumy, čísla a vybrané textové údaje. Skupinu OrderDate zoskupíme do tejto príručky, takže vo formulári sa zobrazia možnosti týkajúce sa zoskupení dátumov.

Kliknite na mesiace a roky a kliknite na tlačidlo OK.

12 z 15

Kontingenčná tabuľka zoskupená podľa rokov a mesiacov

Polia dátumu sú zoskupené podľa rokov a mesiacov.

Ako vidíte na obrázku vyššie, údaje sú zoskupené podľa prvého roka a potom po mesiaci. Každý z nich má znamienko plus a mínus, ktoré umožňuje rozbaliť a zbaliť v závislosti od toho, ako chcete zobraziť údaje.

V tomto bode je kontingenčná tabuľka veľmi užitočná. Každé z polí môže byť filtrované, ale problémom je, že neexistuje vizuálna stopa, pokiaľ ide o aktuálny stav filtrov. Tiež niekoľko kliknutí zmení zobrazenie.

13 z 15

Vložiť slicer (nové v programe Excel 2010)

Pridať Slicers do kontingenčnej tabuľky.

Slicery sú v programe Excel 2010 nové. Slicers sú v podstate ekvivalentné s vizuálnymi nastaveniami filtrov existujúcich polí a vytváraním filtrov Report v prípade, že položka, ktorú chcete filtrovať, nie je v aktuálnom zobrazení kontingenčnej tabuľky. Táto pekná vec týkajúca sa Slicerov je, že používateľovi sa stáva veľmi jednoduché zmeniť zobrazenie údajov v kontingenčnom tabuľke a poskytnúť vizuálne indikátory, pokiaľ ide o aktuálny stav filtrov.

Ak chcete vložiť nástroj Slicers, kliknite na kartu Možnosti a kliknite na položku Insert Slicer zo sekcie Sort & Filter. Zvoľte možnosť Vložit slicer, ktorý otvorí formulár Insert Slicers. Skontrolujte toľko polí, ktoré chcete mať k dispozícii. V našom príklade som pridala Years, CountryRegionName a ProductCategory. možno budete musieť umiestniť Slicers tam, kde ich chcete. Štandardne sú vybraté všetky hodnoty, čo znamená, že neboli použité žiadne filtre.

14 z 15

Kontingenčná tabuľka s užívateľsky prívetivými slicermi

Slicers uľahčujú používateľom filtrovanie kontingenčných tabuliek.

Ako vidíte, Slicers zobrazia všetky údaje ako vybraté. Pre užívateľa je veľmi jasné, aké sú údaje v aktuálnom zobrazení kontingenčnej tabuľky.

15 z 15

Vyberte hodnoty zo Slicerov Ktoré kontingenčné tabuľky aktualizácií

Vyberte kombinácie nástrojov na krájanie na zmenu zobrazenia údajov.

Kliknite na rôzne kombinácie hodnôt a zistite, ako sa zmení pohľad na kontingenčnú tabuľku. Môžete použiť typické kliknutie v systéme Microsoft Slicers, čo znamená, že ak môžete použiť Control + Click, vyberte viac hodnôt alebo Shift + Click, ak chcete vybrať rozsah hodnôt. Každý nástroj Slicer zobrazuje vybrané hodnoty, čo z neho jasne ukazuje, aký je stav kontingenčnej tabuľky z hľadiska filtrov. Ak chcete, kliknite na rozbaľovaciu ponuku Rýchle štýly v sekcii Slicer na karte Možnosti.

Zavedenie produktu Slicers skutočne zlepšilo použiteľnosť kontingenčných tabuliek a presunulo program Excel 2010 oveľa bližšie k tomu, aby bol profesionálnym nástrojom obchodnej inteligencie. Kontingenčné tabuľky sa v programe Excel 2010 výrazne zlepšili a pri kombinácii s novým PowerPivotom sa vytvára veľmi vysoko výkonné analytické prostredie.