Як наладзіць Excel 2010 зводныя табліцы

by цім Перд'ю

01 з 15

канчатковы вынік

Гэта канчатковы вынік гэтага крок за крокам падручнік - Націсніце на малюнак, каб убачыць поўнапамерную версію.

Там быў разрыў паміж верхнімі ўзроўню бізнес-аналітыкі (BI) платформаў на працягу многіх гадоў Microsoft Excel і. Ўдасканалення 2010 зводнай табліцы Microsoft Excel разам з некалькімі іншых BI-функцый зрабілі яго рэальным канкурэнтам для карпаратыўных BI. Excel традыцыйна выкарыстоўваецца для аўтаномнага аналізу і стандартнага інструмента, што кожны экспартуе свае заключныя даклады ст. Прафесійны бізнес-аналітыка традыцыйна была зарэзерваваная для такога SAS, Business Objects і SAP.

Microsoft Excel 2010 (з Excel 2010 зводнай табліцай) разам з SQL Server 2008 R2, SharePoint 2010 і свабодным Microsoft Excel 2010 надбудовы «PowerPivot» прывяла да высокай мэты бізнес-аналітыцы і справаздачнасці рашэння.

Гэты падручнік ахоплівае прама наперад сцэнар з 2010 зводнай табліцай Excel, падлучанай да базе дадзеных SQL Server 2008 R2 з дапамогай простага запыту SQL. Я таксама выкарыстоўваю разакі для візуальнай фільтрацыі, якая з'яўляецца новай у Excel 2010. Я распавяду больш складаныя метады BI з дапамогай аналізу дадзеных выразаў (DAX) у PowerPivot для Excel 2010 у найбліжэйшай будучыні. У апошняй версіі Microsoft Excel 2010 можа забяспечыць рэальную каштоўнасць для вашага супольнасці карыстальнікаў.

02 з 15

Уставіць зводную табліцу

Усталюйце курсор менавіта там, дзе вы хочаце, каб ваша зводную табліцу і націсніце Insert | Зводная табліца.

Вы можаце ўставіць зводную табліцу ў новай або існуючай кнізе Excel. Вы можаце разгледзець пытанне аб пазіцыянаванні курсора ўніз некалькі радкоў зверху. Гэта дасць вам прастору для інфармацыі загалоўка або кампаніі ў выпадку, калі вы падзяляеце ліст ці раздрукаваць яго.

Адкрыйце новую або існуючую кнігу Excel 2010 і націсніце на вочка, дзе вы хочаце, каб левы верхні кут зводнай табліцы будзе.
Націсніце на ўкладку Устаўка і націсніце на зводную табліцу выпадаючыя ў раздзеле Tables. Выберыце зводную табліцу. Гэта запусціць Стварэнне зводнай табліцы дыялогавай формы.

03 з 15

Падключэнне зводнай табліцы ў SQL Server (ці іншую базу дадзеных)

Стварыце свой зварот SQL, а затым падлучыцца да SQL Server, каб ўставіць радок дадзеных злучэння ў электронную табліцу Excel.

Excel 2010 можа здабываць дадзеныя з усіх асноўных СКБД (сістэма кіравання рэляцыйнымі базамі дадзеных) пастаўшчыкоў. SQL драйвера сервераў павінны быць даступныя для падлучэння па змаўчанні. Але ўсе асноўныя праграмнага забеспячэння базы дадзеных робяць ODBC (Open Database Connectivity) кіроўцы, каб дазволіць вам зрабіць злучэнне. Праверце свой вэб-сайт, калі вам трэба спампаваць драйвер ODBC.

У выпадку гэтага ўроку, я падключаю да SQL Server 2008 R2 (SQL Express бясплатнай версіі).

A - Стварэнне зводнай табліцы формы з'яўляецца першай формай, у стварэнні падлучэння да SQL Server. Выберыце «Выкарыстаць знешні крыніца дадзеных» і націсніце на кнопку Выбраць Connection. Пакіньце месцазнаходжанне, дзе Зводная табліца будзе размешчана, калі вы не хочаце, каб стварыць новую табліцу і змясціць яго туды.
B - Існуючыя злучэння формы пералічаныя ўсе злучэння ў бягучай кнізе, на вашым кампутары і сеткі, у цяперашні час падлучаныя. Існуючыя падлучэння сапраўды толькі тэкставыя файлы з інфармацыяй злучэння, неабходнай для доступу да пэўнага крыніцы дадзеных. У нашым выпадку, мы збіраемся стварыць новы крыніца дадзеных. Націсніце на за дадатковую кнопку.
C - Націсніце на кнопку New Source запусціць майстры падлучэння дадзеных.
D - Выберыце Microsoft SQL Server і націсніце кнопку Далей.
E - Калі ласка, увядзіце імя сервера і ўваход ва ўліковых дадзеных. Абярыце неабходны метад аўтэнтыфікацыі. Калі вы не ведаеце, які метад выкарыстаць, звярніцеся да адміністратара базы дадзеных.
- Выкарыстанне праверкі сапраўднасці Windows: Гэты метад выкарыстоўвае сеткавую уліковы запіс для доступу да баз дадзеных SQL Server.
- Выкарыстоўвайце наступнае імя карыстальніка і пароль: Гэты метад выкарыстоўваецца , калі SQL Server быў наладжаны з аўтаномнымі карыстальнікамі для доступу да баз дадзеных.
F - У гэтым кроку мы будзем выбіраць табліцу ў якасці запаўняльніка. Мы будзем замяняць табліцу з карыстацкай SQL, які будзе забяспечваць менавіта дадзеныя, якія мы хочам у нашай кнізе Excel.
- Выберыце базу дадзеных, якую вы будзеце падключаць к. У гэтым прыкладзе мы падключаемся да базы дадзеных AdventureWorks, прадстаўленай Microsoft. Праверце злучэнне да канкрэтнай табліцы і выбраць першую табліцу. Памятаеце, што мы не збіраемся вымання дадзеных з гэтай табліцы.
- Націсніце кнопку Гатова, якая будзе зачыняць акно майстры і вярнуць вас да кнігі. Мы будзем абменьваць з табліцы запаўняльніка для нашага карыстацкага SQL запыту.

Вы будзеце вернутыя да формы Стварэнне зводнай табліцы (A). Націсніце кнопку ОК.

04 з 15

Зводная табліца Часова Падлучэнне да SQL табліцы

PivotTable падключаецца да SQL Server з табліцай запаўняльнікам.

На дадзены момант, вы падключылі да стала шаблоннага і ў вас ёсць пустая зводная табліца. Вы можаце ўбачыць на левым былі PivotTable будзе і справа ёсць спіс даступных палёў.

05 з 15

Адкрыць Уласцівасці падлучэння

Open Connection Properties формы.

Перад тым, як пачаць выбар дадзеных для зводнай табліцы, неабходна змяніць падлучэнне да запыту SQL. Пераканайцеся, што вы знаходзіцеся на ўкладцы Параметры і націсніце Змяніць крыніца дадзеных выпадальны з секцыі дадзеных. Выберыце Уласцівасці падключэння.

Узнікае злучэнне Уласцівасці формы. Націсніце на ўкладцы Вызначэнне. Гэта паказвае інфармацыю аб злучэнні для бягучага падлучэння да SQL Server. У той час як ён спасылаецца на файл падлучэння, дадзеныя фактычна ўбудаваны ў электронную табліцу.

06 з 15

Ўласцівасці абнаўлення злучэння З Query

Змена табліцы SQL запыту.

Змена тыпу каманднага радка з табліцы ў SQL і перапісаць існуючы тэкст каманды з вашым SQL Query. Вось запыт я стварыў з ўзору базы дадзеных AdventureWorks:

ВЫБАР 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 А.С. ProductCategory
АД Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =
Production.Product.ProductID INNER JOIN Sales.Customer ON
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID І
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
Production.ProductSubcategory ON Production.Product.ProductSubcategoryID =
Production.ProductSubcategory.ProductSubcategoryID

Націсніце кнопку ОК.

07 з 15

Атрыманне злучэння Папярэджанні

Націсніце Так, каб Папярэджаньне падключэння.

Вы атрымаеце дыялогавае акно Папярэджаньне Microsoft Excel. Гэта адбываецца таму, што мы змянілі інфармацыю аб злучэнні. Калі мы першапачаткова стварылі злучэнне, ён захаваў інфармацыю ў вонкавым ODC-файле (Падлучэнне дадзеных ODBC). Дадзеныя, прыведзеныя ў кнізе была такой жа, як файл ODC-пакуль мы не змяніліся з каманднага тыпу табліцы для тыпу каманды SQL за крок # 6. Папярэджанне не кажа вам пра тое, што дадзеныя больш не сінхранізавана і спасылка на знешні файл у кнізе будуць выдаленыя. Гэта нармальна. Націсніце кнопку Так.

08 з 15

Зводная табліца Падлучэнне да SQL Server З дапамогай Query

Зводная табліца гатовая для дадання дадзеных.

Гэта бярэ назад у 2010 кнігі Excel з пустой зводнай табліцай. Вы можаце бачыць, што даступныя поля цяпер розныя і адпавядаюць палях ў запыце SQL. Цяпер мы можам прыступіць да дадання палёў у зводнай табліцы.

09 з 15

Даданне палёў зводнай табліцы

Даданне палёў у зводную табліцу.

У спісе палёў зводнай табліцы, перацягнуць ProductCategory ў Роу вобласць этыкеткі, OrderDate ў Калоннай вобласці Этыкеткі і TotalDue ў галіне значэнняў. Выява паказвае вынікі. Як вы можаце бачыць, поле даты мае асобныя даты, таму PivotTable стварыў слупок для кожнай унікальнай даты. На шчасце, Excel 2010 мае некалькі ўбудаваных функцый, каб дапамагчы нам арганізаваць поля даты.

10 з 15

Дадаць Групоўку для полай даты

Дадаць Групоўкі для поля даты.

Функцыя Групоўка дазваляе арганізаваць даты ў гады, месяцы, кварталы і г.д. Гэта дапаможа абагульніць дадзеныя і зрабіць яго больш лёгкім для карыстальніка, каб ўзаемадзейнічаць з ім. Пстрыкніце правай кнопкай мышы на адным з загалоўкаў слупкоў даты і выбраць групу, якая выхоўвае форму групаванне.

11 з 15

Выберыце Групоўка па значэннях

Выберыце групоўку элементаў для поля даты.

У залежнасці ад тыпу дадзеных, якія вы групаванне, форма будзе выглядаць крыху інакш. Excel 2010 дазваляе даты групы, лічбаў і выбраных тэкставых дадзеных. Мы групоўка OrderDate ў гэтым падручніку, так што форма будзе паказваць варыянты, якія тычацца даты груповак.

Націсніце на месяцы і гады, і націсніце кнопку ОК.

12 з 15

Зводная табліца Згрупаваныя па гадах і месяцах

Поля даты згрупаваныя па гадах і месяцах.

Як вы можаце бачыць на малюнку вышэй, дадзеныя згрупаваны па гадах, а затым па месяцах. Кожны з іх мае плюс і мінус знак, які дазваляе згарнуць або разгарнуць у залежнасці ад таго, як вы хочаце бачыць дадзеныя.

На дадзены момант, PivotTable вельмі карысна. Кожнае з палёў можа быць адфільтраваныя, але праблема значыць не візуальны ключ да разумення бягучага стану фільтраў. Акрамя таго, ён займае некалькі націсніце, каб змяніць выгляд.

13 з 15

Ўстаўце Slicer (Новае ў Excel 2010)

Дадаць у зводную табліцу зрэзаў.

Зрэзы з'яўляюцца новымі ў Excel 2010. зрэзы ў асноўным эквівалент візуальна ўсталяваны фільтры існуючых палёў і стварэння справаздач фільтраў у тым выпадку, калі дэталь вы хочаце фільтраваць не ў бягучай зводнай табліцы. Гэта добрая рэч зрэзаў гэта становіцца вельмі лёгкім для карыстальніка, каб змяніць прадстаўленне даных у зводнай табліцы, а таксама прадастаўленне візуальных індыкатараў, як да бягучага стану фільтраў.

Каб ўставіць зрэзы, націсніце на ўкладку Параметры і націсніце Уставіць Слайсер з падзелу Сартаванне і фільтр. Выберыце Уставіць зрэз, які адкрывае форму ўстаўкі Слайсер. Праверце, як шмат палёў, як вы хочаце мець у сваім распараджэнні. У нашым прыкладзе, я дадаў гадоў CountryRegionName і ProductCategory. Вы, магчыма, прыйдзецца размясціць зрэзы, дзе вы хочаце іх. Па змаўчанні ўсе значэння выбіраюцца, што азначае фільтры не былі ўжытыя.

14 з 15

Зводная табліца з зручнымі зрэзамі

Зрэзы робяць яго больш зручным для карыстальнікаў, каб адфільтраваць PivotTables.

Як вы можаце бачыць, ломтерезки паказаць усе дадзеныя, абраныя. Гэта вельмі зразумела карыстачу, якія менавіта дадзеныя ў бягучым выглядзе зводнай табліцы.

15 з 15

Выберыце значэння з Слайсер, якія абнаўлення зводнай табліцы

Выбар камбінацыі зрэзаў, каб змяніць прадстаўленне даных.

Націсніце на розныя камбінацыі значэнняў і ўбачыць, як выгляд зводнай табліцы змен. Вы можаце выкарыстоўваць тыповыя пстрычкі Microsoft у зрэзах гэта азначае, што калі вы можаце выкарыстоўваць Control + пстрычка для выбару некалькі значэнняў або Shift + Націсніце, каб выбраць дыяпазон значэнняў. Кожны Slicer адлюстроўвае выбраныя значэння, што робіць яго сапраўды відавочна, што стан зводнай табліцы ў тэрмінах фільтраў. Вы можаце змяніць стылі зрэзамі, калі вы хочаце, націсніце на кнопку Quick Styles выпадаючыя ў раздзеле Slicer на ўкладцы Параметры.

Ўвядзенне зрэзамі сапраўды палепшылі зручнасць выкарыстання зводных табліц і пераехаў Excel 2010 значна бліжэй да прафесійных інструментам бізнес-аналітыкі. PivotTables палепшылася зусім трохі ў Excel 2010 і ў спалучэнні з новым PowerPivot стварае вельмі высокую прадукцыйнасць аналітычнай асяроддзя.