Как да конфигурирате таблици за завъртане на Excel 2010

by Тим Пердуи

01 от 15

Краен резултат

Това е крайният резултат от този урок стъпка по стъпка - Кликнете върху изображението, за да видите версия в пълен размер.

Имаше голяма разлика между Microsoft Excel и платформите от най-високо ниво за бизнес разузнаване (BI) в продължение на много години. Подобренията в Pivot Table на Microsoft Excel 2010, заедно с няколко други BI функции, го превърнаха в истински конкурент за бизнес BI. Програмата Excel традиционно се използва за самостоятелен анализ и стандартното средство, с което всеки изнася своите окончателни отчети. Професионалното бизнес разузнаване традиционно е запазено за хора като SAS, Business Objects и SAP.

Microsoft Excel 2010 (заедно с Excel 2010 Pivot Table) заедно със SQL Server 2008 R2, SharePoint 2010 и безплатната добавка за Microsoft Excel 2010 "PowerPivot" доведе до висококачествено решение за бизнес разузнаване и отчитане.

Този урок обхваща справедлив сценарий с Excel 2010 PivotTable, свързан към база данни на SQL Server 2008 R2, използвайки проста SQL заявка. Също така използвам Slicers за визуално филтриране, което е ново в Excel 2010. Ще използвам по-сложни BI техники с помощта на Data Analysis Expressions (DAX) в PowerPivot за Excel 2010 в близко бъдеще. Това най-новото издание на Microsoft Excel 2010 може да осигури реална стойност за вашата потребителска общност.

02 от 15

Вмъкване на конфигурационната таблица

Поставете курсора точно там, където искате вашата конфигурация и кликнете върху Insert | Окончателна таблица.

Можете да вмъкнете Pivot таблица в нова или съществуваща работна книга на Excel. Може да искате да обмислите позиционирането на курсора надолу по няколко реда отгоре. Това ще ви даде място за заглавна информация или информация за фирмата, в случай че споделите работния лист или го отпечатате.

Отворете нова или съществуваща работна книга на Excel 2010 и кликнете върху клетката, където искате да бъде в горния ляв ъгъл на конфигурационната таблица.
Кликнете върху раздела Вмъкване и кликнете върху падащото меню в секцията Таблици. Изберете PivotTable. Това ще стартира диалоговия формуляр Създаване на обобщена таблица.

03 от 15

Свързване на таблицата за завършване към SQL Server (или друга база данни)

Създайте своя SQL заявка и след това се свържете със SQL Server, за да вградите низа за данни за връзка в електронната таблица на Excel.

Excel 2010 може да извлича данни от всички основни доставчици на RDBMS (Релационна база данни) . Драйверите на SQL Server трябва да са достъпни по подразбиране за връзката. Но всеки основен софтуер за бази данни прави ODBC (Open Database Connectivity) драйвери, за да ви позволи да осъществите връзката. Проверете техния уеб сайт, ако трябва да изтеглите ODBC драйвери.

В случай на този урок, аз се свързвам с SQL Server 2008 R2 (SQL Express безплатна версия).

A - формулярът Create PivotTable е първият формуляр за създаване на връзка към SQL Server. Изберете "Използване на външен източник на данни" и кликнете върху бутона Избиране на връзка. Оставете местоположението на мястото, където ще бъде поставена общата таблица, освен ако не искате да създадете нов работен лист и да го поставите там.
B - Формулярът "Съществуващи връзки" показва всички връзки в текущата работна книга, на вашия компютър и мрежата, към която сте свързани. Съществуващите връзки са наистина само текстови файлове с информацията за връзка, необходима за достъп до определен източник на данни. В нашия случай ще създадем нов източник на данни. Кликнете върху бутона Преглед на повече.
C - Щракнете върху бутона "Нов източник", за да стартирате Помощника за свързване на данни.
D - Изберете Microsoft SQL Server и щракнете върху Напред.
E - Въведете името на сървъра и данните за вход. Изберете подходящия метод за удостоверяване. Ако не сте сигурни кой метод да използвате, свържете се с администратора на базата данни.
- Използване на удостоверяване в Windows: Този метод използва вашите мрежови данни за достъп до SQL Server бази данни.
- Използвайте следното потребителско име и парола: Този метод се използва, когато SQL Server е конфигуриран със самостоятелни потребители за достъп до бази данни.
F - В тази стъпка ще изберем таблица като заместител. Ще заменим таблицата с персонализиран SQL, който ще предостави точно данните, които искаме, в нашата работна книга в Excel.
- Изберете базата данни, към която ще се свързвате. В този пример се свързваме с примерната база данни на AdventureWorks, предоставена от Microsoft. Проверете връзката Свързване към конкретна таблица и изберете първата таблица. Не забравяйте, че няма да извличаме данни от тази таблица.
- Кликнете върху Завършване, което ще затвори съветника и ще ви върне в работната книга. Ще сменим таблицата за наслажденията за нашата персонализирана SQL заявка.

Ще бъдете върнати в формуляра Create PivotTable (A). Кликнете върху OK.

04 от 15

Pivot Table временно свързан с SQL таблица

PivotTable е свързан към SQL Server с таблицата с резервни копия.

В този момент сте свързани с таблицата със заместващи символи и имате празна тактова таблица. Можете да видите отляво, че ще бъде натисната и отдясно има списък с наличните полета.

05 от 15

Отворете свойствата на връзката

Отворете формуляра Свойства на връзката

Преди да започнем да избираме данни за общата таблица, трябва да променим връзката с SQL заявката. Уверете се, че сте в раздела Опции и кликнете върху падащото меню Промяна на източника на данни от секцията Данни. Изберете "Свързване".

Това извежда формуляра за свойства на връзката. Кликнете върху раздела Дефиниция. Това ви показва информацията за връзката за текущата връзка със SQL Server. Докато препраща към файл с връзка, данните всъщност се вграждат в електронната таблица.

06 от 15

Актуализиране на свойствата на връзката с заявка

Промяна на таблицата в SQL заявката.

Променете типа на командата от таблицата към SQL и заменете съществуващия команден текст с вашето SQL запитване. Ето заявката, която създадох от примерната база данни на 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,
Производство.Продукт
От Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Производство.Продукт на продажбите.SalesOrderDetail.ProductID =
Производство.Продукт.ПродуктID ВЪТРЕШНО СЪБИРАНЕ Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID AND
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Продажби. Индивидуален потребител на продажби
Продажби. Индивидуален потребител
Производство.ПродуктПродукти по продукция.Продукт.Продукт
Production.ProductSubcategory.ProductSubcategoryID

Кликнете върху OK.

07 от 15

Внимание за получаване на връзка

Кликнете върху Да, за да се свържете с предупреждение.

Ще получите диалогов прозорец за предупреждение в Microsoft Excel. Това е така, защото смените информацията за връзката. Когато първоначално създадохме връзката, тя запази информацията в външен .ODC файл (ODBC Data Connection). Данните в работната книга бяха същите като файла .ODC, докато не променим от типа на командата на таблицата до SQL командата в стъпка # 6. Предупреждението ви казва, че данните вече не са синхронизирани, а препратката към външния файл в работната книга ще бъде премахната. Това е добре. Кликнете върху Да.

08 от 15

Обща таблица, свързана със SQL Server с заявка

PivotTable е готов за добавяне на данни.

Това се връща към работната книга на Excel 2010 с празен контекст. Можете да видите, че наличните полета сега са различни и съответстват на полетата в SQL заявката. Сега можем да започнем да добавяме полета към общата таблица.

09 от 15

Добавяне на полета към обобщаващата таблица

Добавете полета към обобщена таблица.

В списъка на полетата на плъзгащите таблици плъзнете областта на продуктовата категория в етикетите на ред, областта на поръчките за колоните и зоната TotalDue to Values. Изображението показва резултатите. Както можете да видите, полето за дата има отделни дати, така че PivotTable е създала колона за всяка уникална дата. За щастие Excel 2010 има някои вградени функции, които да ни помогнат да организираме полетата за дата.

10 от 15

Добавяне на групиране за полетата за данни

Добавяне на групи за поле за дата.

Функцията "Групиране" ни позволява да организираме дати в години, месеци, квартали и т.н. Това ще помогне да обобщим данните и да улесним потребителя да взаимодейства с тях. Кликнете с десния бутон върху една от заглавията на колоните за дата и изберете Група, която извежда формуляра за групиране.

11 от 15

Изберете Групиране по стойности

Изберете елементите за групиране за полето за дата.

В зависимост от вида на данните, които групирате, формулярът ще изглежда малко по-различен. Excel 2010 ви позволява да групирате дати, номера и избрани текстови данни. Групираме OrderDate в този урок, така че в формуляра ще се показват опции, свързани с групирането по дати.

Кликнете върху Месеци и години и кликнете върху OK.

12 от 15

Обща таблица, групирана по години и месеци

Полетата за дата са групирани по години и месеци.

Както можете да видите на изображението по-горе, данните се групират по първа година и след това по месец. Всеки от тях има знак плюс и минус, който ви позволява да разширявате и свивате в зависимост от това как искате да видите данните.

В този момент общата таблица е доста полезна. Всяко от полетата може да бъде филтрирано, но проблемът е, че няма визуална представа за текущото състояние на филтрите. Също така е необходимо няколко щракване, за да промените изгледа.

13 от 15

Вмъкване на Slicer (ново в Excel 2010)

Добавете Slicers към обобщена таблица.

Slicers са нови в Excel 2010. Slicers са основно еквивалент на визуално настройване на филтри на съществуващите полета и създаване на отчет Филтри в случай, че елемент, който искате да филтрирате не е в текущия панелен изглед. Това хубаво нещо за Slicers е, че е много лесно за потребителя да промени изгледа на данните в PivotTable, както и да осигури визуални индикатори за текущото състояние на филтрите.

За да вмъкнете Slicers, кликнете върху раздела Options и кликнете върху Insert Slicer от раздела Sort & Filter. Изберете Insert Slicer, който отваря формуляра Insert Slicers. Проверете колкото се може повече полета, колкото искате. В нашия пример добавих Years, CountryRegionName и ProductCategory. може да се наложи да поставите Slicers където искате. По подразбиране са избрани всички стойности, което означава, че не са били приложени филтри.

14 от 15

Ориентировъчна маса с удобни за ползване срязващи устройства

Слизерите улесняват потребителите да филтрират контамирани таблици.

Както можете да видите, Slicers показва всички избрани данни. За потребителя е много ясно какви са данните в текущия изглед на обобщената таблица.

15 от 15

Изберете стойности от Slicers Кои таблици за обновяване обобщават

Изберете комбинации от Slicers, за да промените изгледа на данните.

Кликнете върху различни комбинации от стойности и вижте как се променя изгледа на общата таблица. Можете да използвате типичното кликване на Microsoft в Slicers, което означава, че ако можете да използвате Control + Кликнете, за да изберете няколко стойности или Shift + Click, за да изберете диапазон от стойности. Всеки Slicer показва избраните стойности, което прави наистина очевидно какво е състоянието на PivotTable по отношение на филтри. Можете да промените стиловете на Slicers, ако искате, като кликнете върху падащото меню Quick Styles в раздела Slicer в раздела Options.

Въвеждането на Slicers наистина подобри използваемостта на конфигурационните таблици и премести Excel 2010 много по-близо до това да бъде професионален инструмент за бизнес разузнаване. Обобщените таблици се подобриха доста в Excel 2010 и когато се комбинират с новия PowerPivot, създават много висока аналитична среда за ефективност.