Excel 2010 Pivot хүснэгтүүдийг хэрхэн тохируулах

by Тим Амьдрал

01-ийн 15

Эцсийн үр дүн

Энэ алхам нь энэ алхамын төгсгөлийн үр дүн юм - Зурганд дээр дарж бүтэн хэмжээний хувилбарыг харна уу.

Microsoft Excel болон дээд түвшний бизнесийн тагнуулын (BI) платформуудын хооронд олон жилийн туршид завсарлага гарсан. Microsoft Excel 2010 Pivot Хүснэгтийн сайжруулалтууд нь BI төрлийн хэд хэдэн онцлогуудтай хамт аж ахуйн нэгжийн BI-д зориулсан өрсөлдөгч болсон. Excel-ээс уламжлалт байдлаар бие даасан дүн шинжилгээ хийхэд ашиглагддаг бөгөөд хүн бүр эцсийн тайлангаа гаргадаг стандарт хэрэгсэл юм. Мэргэжлийн бизнесийн тагнуул нь SAS, Бизнесийн объект, САП-т дуртай байдаг уламжлалтай.

Microsoft Excel 2010 (Excel 2010 Pivot хүснэгтээр) SQL Server 2008 R2, SharePoint 2010 болон Microsoft Excel 2010-ийн "PowerPivot" add-on нь бизнесийн өндөр түвшний тагнуул, тайлагнах шийдлийг бий болгосон.

Энэ заавар нь энгийн SQL query-ийг ашиглан Excel 2010 PivotTable-тэй SQL Server 2008 R2 мэдээллийн санд шууд шулуун хувилбартай хамаатай. Би Excel-ийг 2010 онд шинэ үзэл баримтлалд зориулж Слизерүүд дээр ашиглаж байна. Би ойрын ирээдүйд PowerPivot for Excel 2010 дээр Data Analysis Expressions (DAX) ашиглан илүү нарийн төвөгтэй BI арга техникийг ашиглах болно. Microsoft Excel 2010-ийн хамгийн сүүлийн хувилбар нь таны хэрэглэгчийн нийгэмлэгийн бодит үнэ цэнийг бий болгодог.

15 нь

Пивот хүснэгт оруулах

Курсороо өөрийн байрлалыг хүснэгтийн оронд байршуулж оруулна уу Тэнхлэгийн хүснэгт.

Та Pivot Хүснэгтийг шинэ буюу одоо байгаа Excel ажлын дэвтэр дээр оруулж болно. Та курсорыг дээд талаас нь хэд хэдэн эгнээ доош байршуулахыг хүсч болно. Энэ нь ажлын хуудас эсвэл хэвлэхэд хамаатай тохиолдолд толгой эсвэл компаний мэдээлэл авах зай гаргах болно.

Шинэ эсвэл одоо байгаа Excel 2010 workbook нээгээд Pivot Table-ийн зүүн дээд буланд байхыг хүсч буй нүдэн дээр дар.
Insert таб дээр дарж PivotTable дээр дарна уу. PivotTable-ыг сонгоно уу. Энэ нь Create PivotTable харилцах цонхыг эхлүүлэх болно.

15 of 15

SQL Server (эсвэл бусад өгөгдлийн сан) руу Pivot хүснэгтийг холбох

SQL хайлтыг үүсгээд SQL Server руу холбохын тулд холболтын өгөгдлийн мөрийг Excel хүснэгтэд оруулах.

Excel 2010 нь бүх томоохон RDBMS (Relational Database Management System) өгөгдлүүдээс өгөгдлийг авах боломжтой. SQL сервер жолооч анхдагч байдлаар холболт хийх боломжтой байх ёстой. Гэхдээ бүх мэдээллийн баазын программ хангамж нь ODBC (Open Database Connectivity) драйверуудыг холболт хийх боломжийг танд олгоно. ODBC жолооч татаж авах хэрэгтэй бол вэбсайтаа шалгаарай.

Энэ гарын авлагын хувьд SQL Server 2008 R2 (SQL Express үнэгүй хувилбар) -тай холбогдож байна.

A - PivotTable хэлбэрийг үүсгэх нь SQL сервертэй холболт үүсгэх эхний хэлбэр юм. "Гадаад дата эх сурвалж ашиглах" гэснийг сонгоод Connection товчийг дарна уу. Шинэ ажлын хуудас үүсгэхийг хүсэхгүй байгаа бол Пивот Хүснэгт байрлуулах байршлыг орхи.
B - Одоо байгаа холболтын хэлбэр нь одоогийн байгаа ажлын дэвтэр дэхь холболтууд, таны компьютер болон таны холбогдсон сүлжээн дэх ямар нэг холбоосыг жагсаав. Одоо байгаа холболтууд нь тодорхой өгөгдлийн эх үүсвэрт хандахын тулд шаардлагатай холболтын мэдээллийг агуулсан текст файл юм. Бидний хувьд бид шинэ өгөгдлийн эх үүсвэр үүсгэх болно. Дэлгэрэнгүйг Browse for More товч дээр дарна уу.
C - Шинэ Эх сурвалж товч дээр дарж Data Connection Wizard ажиллуулна.
D - Microsoft SQL Server-г сонгоод Next товчлуурыг дарна уу.
E - Серверийн нэрийг оруулаад итгэмжлэл дээр нэвтрэх. Тохирох баталгааны аргыг сонгоно уу. Хэрэв та аль аргыг ашиглахаа мэдэхгүй байгаа бол мэдээллийн сангийн администраторт хандана уу.
- Windows баталгаажуулалтыг ашиглах: Энэ арга нь SQL Server мэдээллийн баазад хандахын тулд таны сүлжээний нэвтрэх эрхийг ашигладаг.
- Дараах хэрэглэгчийн нэр, нууц үгийг ашиглана: Энэ аргыг SQL Server нь дан ганц мэдээллийн сан руу дангаар нь хэрэглэн тохируулагдсан үед ашиглагддаг.
F - Энэ шатанд бид хүснэгтийн байршлыг сонгох болно. Бид хүснэгтийн оронд SQL-г өөрийн хүссэн өгөгдлөө Excel-ийн ажлын дэвтэртээ оруулах болно.
- Холбогдох мэдээллийн санг сонгоно уу. Энэ жишээнд бид Microsoft-ийн өгсөн AdventureWorks дээжийн мэдээллийн сантай холбогдож байна. Холболтыг тусгай хүснэгтээс шалгаад эхний хүснэгтийг сонгоно уу. Бид энэ хүснэгтийн датаг авахгүй байхыг санаарай.
- Шилдэгийг дараад Finish дээр дараад шидийг буцааж өгнө. Бид өөрсдийн захиалсан SQL хайлтын хувьд placeholder хүснэгтийг swap хийх болно.

Та Create PivotTable маягт (A) руу буцах болно. OK дарна уу.

04-р сарын 15

Pivot Table нь SQL хүснэгтэнд түр хугацаанд холбогддог

PivotTable нь SQL Server-тэй хамт байрлана.

Энэ үед та байршлын хүснэгтэд холбогдсон бөгөөд танд PivotTable хоосон байна. Та зүүн талд харж болно PivotTable байна, баруун талд боломжтой байгаа талбар жагсаалт байна.

05-р сарын 15

Нээлттэй холболтын шинж чанарууд

Нээлттэй холболтын шинж чанаруудын хэлбэр.

PivotTable-ийн өгөгдлийг сонгохоос өмнө бид SQL query-тэй холболтыг өөрчлөх шаардлагатай. Та Options цэсэн дээр байгаа эсэхийг шалгаад Data Data дээрээс Өөрчлөлтийн Мэдээллийн эх үүсвэр дээр дарж уншина уу. Холболтын шинж чанарыг сонгох.

Энэ нь Connection Properties хэлбэрийг үүсгэдэг. Тодорхойлолт дээр дарна уу. Энэ нь SQL Server руу одоогийн холболтын холболтын мэдээллийг харуулж байна. Энэ нь холболтын файлыг иш татсан боловч өгөгдөл нь хүснэгтэд агуулагдсан байна.

15-ын 15

Холболтын шинж чанарыг шинэчлэх замаар хайлт хийнэ

Хүснэгтийг SQL query рүү солино.

Хүснэгтээс Command Type -ийг SQL рүү шилжүүлэн одоо байгаа Command Text-г SQL Query ашиглан дарж бичих. AdventureWorks дээжийн өгөгдлийн сангаас үүсгэсэн хайлт энд байна:

SELECT Sales.SalesOrderHeader.SalesOrderID,
Борлуулалт.SalesOrderHeader.OrderDate,
Борлуулалт.SalesOrderHeader.ShipDate,
Борлуулалт.SalesOrderHeader.Status,
Борлуулалт.SalesOrderHeader.SubTotal,
Борлуулалт.SalesOrderHeader.TaxAmt,
Борлуулалт.SalesOrderHeader.Freight,
Борлуулалт.SalesOrderHeader.TotalDue,
Борлуулалт.SalesOrderDetail.SalesOrderDetailID,
Борлуулалт.SalesOrderDetail.OrderQty,
Борлуулалт.SalesOrderDetail.UnitPrice,
Борлуулалт.SalesOrderDetail.LineTotal,
Бүтээгдэхүүн. Нэргүй,
Sales.vIndividualCustomer.StateProvinceName, Sales.vIndividualCustomer.CountryRegionName,
Sales.Customer.CustomerType,
Үйлдвэрлэл.Product.ListPrice,
Үйлдвэрлэл.ProductProductLine,
Үйлдвэрлэл.ProductSubcategory.Name AS ProductCategory
FROM Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ON
Борлуулалт.SalesOrderDetail.SalesOrderID = борлуулалт.SalesOrderHeader.SalesOrderID
INNER JOIN Production. Бүтээгдэхүүний ON SALESSalesOrderDetail.ProductID =
Үйлдвэрлэл.Product.ProductID INNER JOIN Sales.Customer ON
Борлуулалт.SalesOrderHeader.CustomerID = Борлуулалт.Харилцагч.ХарилцагчБОЛТ
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN
Sales.vIndividualCustomer ON Sales.Customer.CustomerID =
Sales.vIndividualCustomer.CustomerID INNER JOIN
PRODUCT Production.Subcategory ON Production.ProductProductSubcategoryID =
Үйлдвэрлэл.ProductSubcategory.ProductSubcategoryID

OK дарна уу.

Холболтын анхааруулга авах

Connection Анхааруулга болгохын тулд Yes дээр дарна уу.

Та Microsoft Excel Анхааруулга цонхыг хүлээн авах болно. Учир нь бид холболтын мэдээллийг өөрчилсөн юм. Бид анх холболтыг үүсгэсний дараа энэ мэдээллийг гадаадад хадгалсан .ODC файл (ODBC Data Connection). Ажлын байран дахь өгөгдөл нь .ODC файлыг Step тушаалын 6-р мөрөнд SQL командын төрөл рүү тушаалын төрлөөс өөрчлөх хүртэл өөрчлөгдсөн байна. Анхааруулга нь өгөгдөл нь синк хийгдээгүй бөгөөд ажлын хуудасны гаднах файлд лавлагаа авах болно. Энэ бол зүгээр юм. Тиймээ дарна уу.

15-с 15

SQL Server-тэй холбоотой Pivot Хүснэгт

PivotTable нь танд өгөгдөл нэмэхэд бэлэн байна.

Энэ нь Excel 2010-ийн ажлын номыг хоосон PivotTable ашиглан буцааж авах болно. Та боломжит талбарууд нь одоо өөр бөгөөд SQL query доторх талбартай тохирохыг харж болно. Одоо бид PivotTable-д талбаруудыг нэмэх боломжтой.

09-р сарын 15

Талбарыг Pivot Хүснэгт рүү нэмэх

PivotTable руу талбар нэмэх.

PivotTable Талбарын жагсаалтад БүтээгдэхүүнЧайлбарыг Row Row талбарт, OrderDate to Column Шошго талбар, TotalDue to Value талбай руу чирнэ үү. Зураг нь үр дүнг харуулж байна. Өгөгдсөн талбар нь он сар өдөр тусдаа огноотой тул PivotTable нь онцгой өдөр бүрийн баганыг үүсгэсэн. Аз болоход, Excel 2010 нь болзооны талбаруудыг зохион байгуулахад туслах зарим функцуудтай.

10 - ын 15

Өгсөн талбаруудад бүлэглэх бүлэг нэмэх

Өгөгдсөн талбарт бүлэглэл нэмэх.

Группын функц нь биднийг жил, сар, улирлаар зохион байгуулах боломжийг олгодог. Энэ нь өгөгдлийг нэгтгэн, хэрэглэгчтэй харилцахад хялбар болгоно. Өдөр тутмын баганы толгойн дээр баруун товшиж, Бүлгийн маягтыг үүсгэсэн Бүлгийг сонгоно уу.

Бүлэглэхийг сонгоно уу

Огноо талбарт зориулж зүйлүүдийг бүлэглэх.

Өгөгдөлд хамаарах өгөгдлөөс хамааран маягт арай өөр байх болно. Excel 2010 нь он сар өдөр, тоонууд, сонгосон текст өгөгдлүүдийг бүлэглэх боломжийг танд олгоно. Бид энэ зааварчилгаанд OrderDate-г бүлэглэж байгаа тул маягт нь огноог бүлэглэхтэй холбоотой сонголтуудыг харуулах болно.

Сараар хэдэн жил дарж, OK дарна уу.

15 хүний 12

Пивот хүснэгтийг олон жил, сараар бүлэглэсэн

Огноо талбаруудыг жил ба сараар бүлэглэнэ.

Дээрх зургаар харж болно. Өгөгдлийг эхний ба дараа нь сараар бүлэглэнэ. Бүр тус бүрийг нэмэх, хасах тэмдэг бүхий өгөгдлийг харахыг хүсч байгаагаас шалтгаалан өргөжүүлэх, задлах боломжтой.

Энэ үед PivotTable маш ашигтай. Талбар бүрийг шүүж болох боловч асуудал нь шүүлтүүрийн өнөөгийн төлөвийн хувьд харааны заавар биш юм. Мөн үзэмжийг өөрчлөх хэд хэдэн товчлуур шаардагдана.

15-15

Слайдер оруулах (Excel-д шинэ онд)

PivotTable руу Slicers нэмнэ үү.

Сликерууд нь 2010 оны Excel-д шинээр орсон. Слицерүүд нь одоо байгаа талбайн шүүлтүүрүүдийг харуулахтай адил бөгөөд Filter хийхийг хүсч байгаа зүйл нь одоогийн PivotTable харагдацгүй байгаа тохиолдолд Report Filters үүсгэдэг. Слицерийн тухай энэ гайхалтай зүйл нь хэрэглэгч PivotTable дахь өгөгдлийг харах, шүүлтүүрийн өнөөгийн төлөвийг харуулах үзүүлэлтүүдийг хангахад маш хялбар болно.

Slicers-ийг оруулахын тулд Options товч дээр дарж, Sort, Filter хэсэг дэх Insert Slicer дээр дарна уу. Insert Slicers хэлбэрийг нээх Слайдерыг сонгоно уу. Боломжтой байгаа талбаруудын аль нэгийг шалга. Бидний жишээнд Би жил, CountryRegionName болон ProductCategory нэмсэн. Та хүссэн газраа Slicers байршуулах хэрэгтэй болж магадгүй. Анхдагч байдлаар, бүх утгуудыг сонгосон бөгөөд ямар ч шүүлтүүр хэрэглээгүй гэсэн үг юм.

Pivot Хүснэгт Хэрэглэгчдэд ээлтэй Slicers ашиглан

Сификерууд нь хэрэглэгчдэд PivotTables-ийг шүүнэ.

Слайдер сонгосон бүх өгөгдлийг харуулж чадна. Энэ нь PivotTable-ийн одоогийн байршилд ямар өгөгдөл байгаа талаар хэрэглэгч тодорхой юм.

15 нь 15

Ачаалал Pivot Хүснэгтийн Slicers-ийн утгыг сонгоно уу

Слицерийн хослолыг сонгохын тулд өгөгдлийг харах боломжтой.

Үнийн янз бүрийн хослол дээр дарж, PivotTable-ийн өөрчлөлт хэрхэн харагдана. Та Microsoft-г ердийн Slicers дээр дарж ашиглаж болно. Хэрэв та Control + Click дээр дарж олон утгыг сонгох эсвэл Shift + Click дээр дарж утгыг сонгох боломжтой. Слицер бүр сонгосон утгуудыг харуулдаг бөгөөд энэ нь PivotTable-ийн төлөв нь шүүлтүүрийн хувьд юу болохыг үнэхээр ойлгомжтой болгодог. Хэрэв та хүсвэл Slicers-ийн хэв маягийг өөрчлөх боломжтой бөгөөд Options товч дээр Slicer хэсгийг доош дарж уншаарай.

Slicers-ийн танилцуулга нь PivotTables-ийн хэрэглээг сайжруулж, Excel-ийг 2010 бизнесийн мэргэжлийн тагнуулын хэрэгсэл болгоход илүү ойртсон. PivotTables нь Excel 2010-д бага зэрэг сайжирсан бөгөөд шинэ PowerPivot-тэй хослуулсан нь өндөр гүйцэтгэлтэй орчинд ажиллах чадварыг бий болгодог.