Excel 2010 жиынтық кестелерін қалай теңшеу керек

by Тим Перду

01-тен 15-ке дейін

Қорытынды нәтиже

Бұл Step by Step үйретісінің соңғы нәтижесі - Толық өлшемді нұсқаны көру үшін суретті басыңыз.

Көптеген жылдар бойы Microsoft Excel және жоғары деңгейлі іскери интеллект (BI) платформаларының арасындағы айырмашылық бар. Microsoft Excel 2010 Pivot кестесінің жақсартулары басқа BI мүмкіндіктерінің екеуі де оны кәсіпорын BI үшін нақты бәсекелеске айналдырды. Excel дәстүрлі түрде дербес талдау және стандартты құрал үшін пайдаланылды, олардың әрқайсысы соңғы есептерді экспорттайды. Кәсіби бизнес интеллект дәстүрлі түрде SAS, Business Objects және SAP-ді ұнатады.

Microsoft Excel 2010 (Excel 2010 Pivot кестесімен) және SQL Server 2008 R2, SharePoint 2010 және Microsoft Excel 2010 тегін «PowerPivot» қосымшасы жоғары бизнес ақыл-кеңес және есеп беру шешіміне әкелді.

Бұл оқулық SQL SQL Server 2008 R2 дерекқорына қосылған қарапайым SQL сұрауымен бірге қосылған Excel 2010 жиынтық кестесімен тікелей алға сценарийді қамтиды. Сондай-ақ, Excel 2010 бағдарламасында жаңа көрнекі сүзгілеу үшін Шектегіштерді пайдаланамын. Жақын болашақта Excel 2010 үшін PowerPivot бағдарламасында Деректерді талдау нұсқаларын (DAX) пайдаланып, аса күрделі BI әдістерін қамтитын болады. Microsoft Excel 2010 соңғы нұсқасы пайдаланушы қауымдастығына нақты мән бере алады.

02-тен 15-ке дейін

Жиынтық кестені кірістіру

Курсорды өзіңіздің қалаған үстеліңізді қай жерге қойғыңыз келсе, сол жерге қойыңыз да, Кірістіру | түймесін басыңыз Жиынтық кесте.

Сіз жаңа немесе бар Excel жұмыс кітабында жиынтық кестені кірістіре аласыз. Меңзеріңізді үстіңгі жағынан бірнеше жолға қоюға болады. Егер сіз жұмыс парағын бөліссеңіз немесе басып шығарсаңыз, бұл тақырып немесе компания туралы ақпарат береді.

Жаңа немесе бар Excel 2010 жұмыс кітабын ашып, жиынтық кестенің жоғарғы сол жақ бұрышын қалайтын ұяшықты басыңыз.
Кірістіру қойындысын нұқыңыз және Кестелер бөлімінде жиынтық кестені ашыңыз. Жиынтық кестені таңдаңыз. Бұл Жиынтық кесте жасау тілқатысу пішінін іске қосады.

03-тен 15-ке дейін

SQL серверіне (немесе басқа дерекқорға) жиынтық кестені қосу

SQL сұрауыңызды жасаңыз және қосылым деректерінің жолын Excel электрондық кестесіне ендіру үшін SQL серверіне қосылыңыз.

Excel 2010 барлық негізгі RDBMS (Relational Database Management System) провайдерлерінен деректерді шығара алады. Әдепкі бойынша қосылу үшін SQL Server драйверлері қол жетімді болуы керек. Бірақ барлық негізгі дерекқор бағдарламалық жасақтамасы ODBC (Open Database Connectivity) драйверлерін қосылуға мүмкіндік береді. ODBC драйверлерін жүктеу қажет болса, олардың веб-сайттарын тексеріңіз.

Бұл оқулық жағдайында, мен SQL Server 2008 R2 (SQL Express тегін нұсқасына) қосыламын.

A - Жиынтық кесте жасау пішіні - SQL Server байланысын жасаудың бірінші пішіні. «Сыртқы деректер көзін пайдалану» тармағын таңдап, «Қосылымды таңдау» түймешігін басыңыз. Жаңа жұмыс парағын жасағыңыз келмейінше, Жиынтық кесте орналастырылатын орынды қалдырыңыз.
B - Бар қосылымдар пішіні ағымдағы жұмыс кітабындағы, компьютеріңізде және қазіргі уақытта қосылған желідегі барлық қосылымдарды тізеді. Қолданыстағы байланыстар шын мәнінде нақты деректер көзіне кіру үшін қажетті байланыс ақпараты бар мәтіндік файлдар болып табылады. Біздің жағдайда жаңа деректер көзін жасаймыз. More for the Review батырмасын басыңыз.
C - Жаңа көз батырмасын басу Деректерді қосу шеберін іске қосады.
D - Microsoft SQL серверін таңдап, Келесі түймешігін басыңыз.
E - Сервер атын енгізіңіз және тіркелгі деректеріне кіріңіз. Тиісті аутентификация әдісін таңдаңыз. Егер қандай әдіс қолданылатынына сенімді болмасаңыз, дерекқор әкімшісіне хабарласыңыз.
- Windows түпнұсқалық растамасын пайдалану: Бұл әдіс SQL Server дерекқорларына кіру үшін желінің логинін қолданады.
- Келесі пайдаланушы атын және құпия сөзді қолданыңыз: Бұл әдіс SQL Server дерекқорларға кіру үшін оқшау пайдаланушылармен теңшелген кезде пайдаланылады.
F - Бұл қадамда кестені толтырғыш ретінде таңдап аламыз. Кестені біздің Excel жұмыс кітабында қалаған деректерді дәл қамтамасыз ететін SQL-мен ауыстырамыз.
- Қосылатын дерекқорды таңдаңыз. Бұл мысалда біз Microsoft ұсынған AdventureWorks үлгі дерекқорына қосыламыз. Белгілі бір кестеге қосылуды тексеріп, бірінші кестені таңдаңыз. Есіңізде болсын, біз осы кестеден деректерді алуды жоспарламаймыз.
- Шеберді жабатын және жұмыс кітабына қайтып келетін Дайын түймешігін басыңыз. Біз реттелетін SQL сұрауына арналған толтырғыш кестесін ауыстырамыз.

Сіз Жиынтық кесте жасау нысанын (A) қайтарасыз. OK түймешігін басыңыз.

04 15

SQL кестесіне уақытша қосылған жиынтық кесте

Жиынтық кесте толтырғыш кестесімен SQL Server-ға қосылған.

Осы сәтте сіз толтырғыш үстеліне қосылдыңыз және сізде бос жиынтық кесте бар. Сол жақта көруге болады, Жиынтық кесте болады, ал оң жағында бос орындар бар.

05 of 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,
Product.ProductSubcategory.Name AS 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 =
Өнім. ӨнімСубкатегория. ӨнімСубкатегорияID

OK түймешігін басыңыз.

07-тен 15-ке дейін

Қосылу туралы ескертуді алыңыз

Қосылу туралы ескерту үшін Иә түймесін басыңыз.

Сіз Microsoft Excel ескерту тілқатысу терезесін аласыз. Себебі байланыс туралы ақпаратты өзгерттік. Бастапқыда қосылымды жасаған кезде ол ақпаратты сыртқы .ODC файлында (ODBC деректер байланысы) сақтады. Жұмыс кітабындағы деректер кесте пәрмені түрінен SQL 6-қадамға ауыстырылғанға дейін .ODC файлымен бірдей болды. Ескерту сізге деректердің синхрондалмағанын және жұмыс кітабындағы сыртқы файлға сілтеме жойылатынын білдіреді. Бұл жақсы. Иә түймешігін басыңыз.

08 15

Сұрау салынған SQL серверіне қосылған жиынтық кесте

Жиынтық кесте деректерді қосу үшін дайын.

Бұл Excel 2010 жұмыс кітабына бос Жиынтық кестемен бірге оралады. Қол жетімді өрістер енді әр түрлі және SQL сұрауындағы өрістерге сәйкес келетінін көре аласыз. Енді жиынтық кестеге өрістерді қосуды бастауға болады.

09-дан 15-ке дейін

Өрістерді жиынтық кестеге қосу

Жиынтық кестеге өрістерді қосу.

Жиынтық кесте өрістерінің тізімінде ProductCategory-ды жол белгілері аймағына, OrderDate-ды баған белгілері аймағына және TotalDue мәндер аумағына апарыңыз. Суретте нәтижелер көрсетіледі. Көріп отырғаныңыздай, күн өрісінде жеке күндер бар, сондықтан жиынтық кесте әр бірегей күн үшін баған жасаған. Бақытымызға орай, Excel 2010 бағдарламасы күні өрістерін ұйымдастыруға көмектесетін кейбір функцияларды қамтиды.

10-тен 15-ке дейін

Күндер өрістері үшін топтауды қосыңыз

Күндер өрісі үшін топтауды қосыңыз.

Топтау функциясы жылдарды, айларды, тоқсандарды т.б. ұйымдастыруды ұйымдастыруға мүмкіндік береді. Бұл деректерді жинақтауға көмектеседі және пайдаланушының онымен өзара әрекеттесуін жеңілдетеді. Күндік баған тақырыбының біреуін тінтуірдің оң жақ түймешігімен нұқыңыз және Grouping пішінін шығаратын Топты таңдаңыз.

11-тен 15-ке дейін

Мәндер бойынша топтауды таңдаңыз

Күндер өрісі үшін элементтерді топтауды таңдаған.

Сіз топтастыратын деректер түріне байланысты, пішін аздап басқаша көрінеді. Excel 2010 күндерді, сандарды және таңдалған мәтін деректерін топтауға мүмкіндік береді. Біз осы оқу құралында OrderDate тобын топтастырдық, сондықтан пішін күні топтамаларына қатысты опцияларды көрсетеді.

Айлар мен Жылдарды басыңыз және OK түймешігін басыңыз.

12-тен 15-ке дейін

Жылдар мен айлар бойынша топталған жиынтық кесте

Күн өрістері жылдар мен айлар бойынша топтастырылады.

Жоғарыдағы суретте көрсетілгендей, деректер бірінші және кейін аймен топтастырылады. Олардың әрқайсысында плюс және минус белгісі бар, ол сіз деректерді қалай көргіңіз келетініне қарай кеңейтуге және тастауға мүмкіндік береді.

Бұл кезде жиынтық кесте өте пайдалы. Әр өрістерді сүзуге болады, бірақ мәселе фильтрлердің ағымдық күйіне қатысты көрнекі түйме болмайды. Сондай-ақ, көріністі өзгерту үшін бірнеше рет нұқу қажет.

13-тен 15-ке дейін

Шектегішті кірістіру (Excel 2010 ішінде жаңа)

Шектегіштерді жиынтық кестеге қосыңыз.

Шектегіштер Excel 2010 бағдарламасында жаңа болып табылады. Шектегіштер негізінен бар өрістерді көрнекі орнату сүзгілерінің эквиваленті болып табылады және сүзгілегісі келетін элементтің ағымдағы жиынтық кесте көрінісінде болмаған жағдайда, есеп сүзгілерін жасайды. Шектегіштер туралы бұл жақсы нәрсе, пайдаланушыға Жиынтық кестедегі деректердің көрінісін өзгертуге, сондай-ақ сүзгілердің ағымдық күйіне қатысты визуалды көрсеткіштерді беруге өте оңай болады.

Шектегіштерді қосу үшін, Параметрлер қойындысын нұқыңыз және Сұрыптау және сүзу бөлімінен Шектегішті кірістіру түймешігін басыңыз. Шектегіштерді кірістіру пәрменін ашады. Қол жетімді болғыңыз келетін өрістердің көптігін тексеріңіз. Біздің мысалда мен Year, CountryRegionName және ProductCategory қосылдым. Сіз Шектегіштерді өзіңіз қалаған жерде орналастыруыңыз керек болуы мүмкін. Әдепкі бойынша, барлық мәндер таңдалады, бұл сүзгілер қолданылмаған.

14-тен 15-ке дейін

Пайдаланушы достығы шектегіштерімен жиынтық кесте

Шектегіштер пайдаланушыларға жиынтық кестелерді сүзуге жеңілдетеді.

Көріп отырғандай, Шектеуіштер барлық деректерді таңдалған ретінде көрсетеді. Жиынтық кестенің ағымдық көрінісінде қандай деректердің нақты пайдаланушысы үшін өте анық.

15-тен 15-ке дейін

Айналмалы кестені жаңартатын шектегіштерден мәндерді таңдаңыз

Деректер көрінісін өзгерту үшін Шектегіштердің тіркесімдерін таңдаңыз.

Мәндердің түрлі комбинацияларын нұқыңыз және Жиынтық кестенің көрінісі қалай өзгеретінін қараңыз. Сіз Шектегіштерге қарапайым Microsoft-ды қолдана аласыз, егер сіз бірнеше мәндерді таңдау үшін Control + Click немесе мәндер ауқымын таңдау үшін Shift + Түймешігін пайдалана аласыз. Әрбір Шектегіш таңдалған мәндерді көрсетеді, бұл жиынтық кестенің күйінің сүзгілерде қандай екенін нақты көрсететін етеді. Параметрлер қойындысының Шектегіш бөлімінде Жылдам мәнерлер түймешігін басу арқылы Шектегіштердің мәнерлерін өзгертуге болады.

Шектегіштерді енгізу шынымен PivotTables-дың ыңғайлылығын жақсартты және Excel 2010-ды кәсіптік бизнес-барлау құралы болу үшін әлдеқайда жақындастырды. Excel 2010 жиынтық кестелері сәл жақсарды және жаңа PowerPivot-мен біріктірілген кезде өте жоғары өнімділік аналитикалық орта жасайды.