กำหนดตาราง Pivot 2010 ของ Excel

มีช่องว่างระหว่าง Microsoft Excel และแพลตฟอร์ม Business Intelligence (BI) เป็นเวลาหลายปี การปรับปรุงตาราง Pivot ของ Microsoft Excel 2010 พร้อมกับคุณสมบัติ BI อื่น ๆ ทำให้คู่แข่งของจริงสำหรับ BI ขององค์กร Excel ได้รับการใช้สำหรับการวิเคราะห์แบบสแตนด์อโลนและเป็นเครื่องมือมาตรฐานที่ทุกคนส่งออกรายงานขั้นสุดท้ายของตนไปแล้ว ระบบธุรกิจอัจฉริยะระดับมืออาชีพได้รับการสงวนไว้สำหรับ SAS, Business Objects และ SAP แล้ว

01 จาก 15

ผลการแข่งขันรอบชิงชนะเลิศ

Microsoft Excel 2010 (ด้วยตาราง Pivot 2010 ของ Excel) พร้อมกับ SQL Server 2008 R2, SharePoint 2010 และ Add-on "PowerPivot" ฟรีของ Microsoft Excel 2010 ได้นำเสนอโซลูชันทางธุรกิจและการรายงานทางการเงินระดับไฮเอนด์

บทแนะนำนี้ครอบคลุมสถานการณ์สมมติที่ตรงไปตรงมากับ Excel 2010 PivotTable ที่เชื่อมต่อกับฐานข้อมูล SQL Server 2008 R2 โดยใช้แบบสอบถาม SQL แบบง่ายๆ

อ่านต่อด้านล่าง

02 จาก 15

แทรกตาราง Pivot

คุณสามารถแทรกตาราง Pivot ในสมุดงาน Excel ใหม่หรือที่มีอยู่ คุณอาจต้องการพิจารณาตำแหน่งเคอร์เซอร์ของคุณลงมาสองสามแถวจากด้านบน ซึ่งจะทำให้คุณมีช่องว่างสำหรับส่วนหัวหรือข้อมูล บริษัท ในกรณีที่คุณแชร์แผ่นงานหรือพิมพ์ออกมา

• เปิดสมุดงาน Excel 2010 ใหม่หรือที่มีอยู่และคลิกที่เซลล์ที่คุณต้องการให้มุมซ้ายบนของตาราง Pivot เป็น
• คลิกที่แท็บแทรกและคลิกที่ PivotTable แบบหล่นลงในส่วนตาราง เลือก PivotTable นี้จะเปิดการสร้างตารางโต้ตอบ PivotTable

อ่านต่อด้านล่าง

03 จาก 15

เชื่อมต่อตาราง Pivot กับ SQL Server

Excel 2010 สามารถเรียกค้นข้อมูลจากผู้ให้บริการ RDBMS (Relational Database Management System) รายใหญ่ ๆ โปรแกรมควบคุมเซิร์ฟเวอร์ SQL ควรพร้อมใช้งานสำหรับการเชื่อมต่อโดยค่าเริ่มต้น ตรวจสอบเว็บไซต์ของพวกเขาถ้าคุณต้องการดาวน์โหลดไดรเวอร์ ODBC

ในกรณีของบทแนะนำนี้เรากำลังเชื่อมต่อกับ SQL Server 2008 R2 (SQL Express เวอร์ชันฟรี)

1. เปิดแบบฟอร์ม PivotTable สร้าง เลือก "ใช้แหล่งข้อมูลภายนอก" และคลิกที่ปุ่มเลือกการเชื่อมต่อ ปล่อยตำแหน่งของตำแหน่งที่จะวางตาราง Pivot ไว้
2. เปิดแบบฟอร์มการเชื่อมต่อที่มีอยู่ คลิกปุ่มเรียกดูเพิ่มเติม
3. คลิกที่ปุ่ม New Source จะเปิดตัว Data Connection Wizard
4. เลือก Microsoft SQL Server และคลิก Next
5. ป้อนชื่อเซิร์ฟเวอร์และข้อมูลรับรองการเข้าสู่ระบบ เลือกวิธีการรับรองความถูกต้องที่เหมาะสม:
   1. ใช้ Windows Authentication: วิธีนี้ใช้การเข้าสู่ระบบเครือข่ายของคุณเพื่อเข้าถึงฐานข้อมูล SQL Server
   2. ใช้ชื่อผู้ใช้และรหัสผ่านต่อไปนี้: วิธีนี้จะใช้เมื่อ SQL Server ได้รับการกำหนดค่าให้กับผู้ใช้แบบสแตนด์อโลนเพื่อเข้าถึงฐานข้อมูล
6. แทนที่ตารางด้วย SQL ที่กำหนดเองซึ่งจะให้ข้อมูลที่เราต้องการในสมุดงาน Excel ของเรา:
   1. เลือกฐานข้อมูลที่คุณจะเชื่อมต่อ ในตัวอย่างนี้เรากำลังเชื่อมต่อกับฐานข้อมูลตัวอย่างของ AdventureWorks ที่จัดเตรียมโดย Microsoft ตรวจสอบ Connect กับตารางที่ต้องการและเลือกตารางแรก โปรดทราบว่าเราจะไม่เรียกข้อมูลจากตารางนี้
   2. คลิกเสร็จสิ้นซึ่งจะเป็นการปิดตัวช่วยสร้างและนำคุณกลับไปที่เวิร์กบุค เราจะเปลี่ยนตารางตัวยึดสำหรับการสอบถาม SQL ที่กำหนดเองของเรา

คุณจะถูกส่งกลับไปที่แบบฟอร์ม PivotTable สร้าง (A) คลิกตกลง

04 จาก 15

ตาราง Pivot ชั่วคราวเชื่อมต่อกับ SQL ตาราง

ณ จุดนี้คุณได้เชื่อมต่อกับตารางตัวยึดและคุณมี PivotTable ว่างเปล่า คุณสามารถเห็นด้านซ้ายที่ PivotTable จะเป็นและด้านขวามีรายการของฟิลด์ที่พร้อมใช้งาน

อ่านต่อด้านล่าง

05 จาก 15

เปิดคุณสมบัติการเชื่อมต่อ

ตรวจสอบว่าคุณอยู่ในแท็บตัวเลือกและคลิกที่เปลี่ยนแหล่งข้อมูลจากเมนูข้อมูลลง เลือกคุณสมบัติการเชื่อมต่อ

ซึ่งจะแสดงรูปแบบการเชื่อมต่อคุณสมบัติ คลิกแท็บนิยาม นี้จะแสดงข้อมูลการเชื่อมต่อสำหรับการเชื่อมต่อปัจจุบันกับ SQL Server แม้ว่าจะอ้างถึงไฟล์การเชื่อมต่อข้อมูลจะฝังอยู่ในสเปรดชีตจริงๆ

06 จาก 15

อัพเดตคุณสมบัติการเชื่อมต่อด้วย Query

เปลี่ยนชนิดคำสั่งจากตารางเป็น 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 ผลิตภัณฑ์หมวดหมู่จาก Sales.SalesOrderDetail INNER JOIN Sales.SalesOrderHeader ONSales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Production.Product ON Sales.SalesOrderDetail.ProductID =Production.Product.ProductID INNER JOIN Sales.Customer ONSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID ANDSales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID INNER JOIN เข้าร่วมSales.vIndividualCustomer ON Sales.Customer.CustomerID =รายละเอียด Sales.vIndividualCustomer.CustomerID INNER JOINการผลิต Product.ProductSubcategoryID =Production.ProductSubcategory.ProductSubcategoryID

คลิกตกลง

อ่านต่อด้านล่าง

07 จาก 15

รับคำเตือนการเชื่อมต่อ

คุณจะได้รับกล่องโต้ตอบ Microsoft Excel Warning เนื่องจากเราเปลี่ยนข้อมูลการเชื่อมต่อ เมื่อเราสร้างการเชื่อมต่อครั้งแรกระบบจะบันทึกข้อมูลไว้ในไฟล์ .ODC ภายนอก (ODBC Data Connection) ข้อมูลในสมุดงานเหมือนกับแฟ้ม. odc จนกว่าเราจะเปลี่ยนจากชนิดคำสั่งตารางเป็นชนิดคำสั่ง SQL ในขั้นตอนที่ 6 คำเตือนจะแจ้งให้คุณทราบว่าข้อมูลไม่มีการซิงค์และการอ้างอิงไปยังไฟล์ภายนอกในสมุดงานจะถูกลบออก นี้เป็นไร คลิกใช่

08 จาก 15

ตาราง Pivot เชื่อมต่อกับ SQL Server ด้วย Query

ซึ่งจะกลับไปที่สมุดงาน Excel 2010 ด้วย PivotTable ที่ว่างเปล่า คุณสามารถดูได้ว่าฟิลด์ที่พร้อมใช้งานมีความแตกต่างกันไปแล้วและตรงกับฟิลด์ในแบบสอบถาม SQL ตอนนี้เราสามารถเริ่มต้นการเพิ่มฟิลด์ลงใน PivotTable

อ่านต่อด้านล่าง

09 จาก 15

เพิ่มฟิลด์ลงในตาราง Pivot

ในรายการเขตข้อมูล PivotTable ให้ลาก ProductCategory ไปที่ Row Labels area, OrderDate ไปยัง Column Labels area และ TotalDue to Values ​​area ตามที่คุณเห็นฟิลด์วันที่มีข้อมูลแต่ละวันดังนั้นตารางสาระสำคัญได้สร้างคอลัมน์สำหรับแต่ละวันที่ไม่ซ้ำกัน Excel 2010 มีฟังก์ชันภายในที่จะช่วยให้เราจัดระเบียบฟิลด์วันที่

10 จาก 15

เพิ่มการจัดกลุ่มสำหรับฟิลด์วันที่

ฟังก์ชันการจัดกลุ่มช่วยให้เราสามารถจัดระเบียบวันที่เป็นปีเดือนไตรมาส ฯลฯ ซึ่งจะช่วยสรุปข้อมูลและทำให้ผู้ใช้โต้ตอบกับมันได้ง่ายขึ้น คลิกขวาที่หนึ่งในส่วนหัวของคอลัมน์วันที่และเลือกกลุ่มซึ่งจะแสดงฟอร์มการจัดกลุ่ม

อ่านต่อด้านล่าง

11 จาก 15

เลือกการจัดกลุ่มตามค่า

รูปแบบจะมีลักษณะแตกต่างกันเล็กน้อยขึ้นอยู่กับชนิดของข้อมูลที่คุณจัดกลุ่ม Excel 2010 ช่วยให้คุณจัดกลุ่มวันที่ตัวเลขและข้อมูลข้อความที่เลือกได้ เรากำลังจัดกลุ่ม OrderDate ไว้ในแบบฝึกหัดนี้เพื่อให้แบบฟอร์มแสดงตัวเลือกเกี่ยวกับการจัดกลุ่มวันที่

คลิกเดือนและปีแล้วคลิกตกลง

12 จาก 15

ตาราง Pivot ที่จัดกลุ่มตามปีและเดือน

ข้อมูลจะถูกจัดกลุ่มตามปีแรกและต่อเดือน แต่ละอันมีเครื่องหมายบวกและลบซึ่งช่วยให้คุณสามารถขยายและยุบขึ้นอยู่กับว่าคุณต้องการดูข้อมูลอย่างไร

ณ จุดนี้ PivotTable มีประโยชน์มาก แต่ละฟิลด์สามารถกรองได้ แต่ปัญหาคือไม่มีร่องรอยของสถานะปัจจุบันของตัวกรอง การเปลี่ยนมุมมองจะใช้เวลาหลายคลิก

13 จาก 15

แทรกเครื่องตัด (ใหม่ใน Excel 2010)

เครื่องตัดแบ่งเป็นผลิตภัณฑ์ใหม่ใน Excel 2010. ตัวแบ่งส่วนมีค่าเท่ากับตัวกรองการตั้งค่าสายตาของเขตข้อมูลที่มีอยู่และสร้างตัวกรองรายงานในกรณีที่รายการที่คุณต้องการกรองไม่อยู่ในมุมมอง PivotTable ปัจจุบัน สิ่งที่ดีเกี่ยวกับ Slicers เป็นเรื่องง่ายสำหรับผู้ใช้ในการเปลี่ยนมุมมองของข้อมูลใน PivotTable รวมทั้งการให้ตัวบ่งชี้ที่แสดงถึงสถานะปัจจุบันของตัวกรอง

ในการแทรก Slicers คลิกที่แท็บ Options และคลิกที่ Insert Slicer จากส่วน Sort & Filter เลือก Insert Slicer ซึ่งจะเปิดแบบ Insert Slicers เลือกฟิลด์ต่างๆตามที่ต้องการ

14 จาก 15

ตาราง Pivot พร้อมเครื่องใช้ที่เป็นมิตรกับผู้ใช้

ดังที่คุณเห็นตัวแบ่งส่วนจะแสดงข้อมูลทั้งหมดตามที่เลือก ผู้ใช้เห็นได้ชัดว่าข้อมูลใดอยู่ในมุมมองปัจจุบันของ PivotTable

15 จาก 15

เลือกค่าจากตัวแบ่งส่วนซึ่งอัปเดตตาราง Pivot

คลิกที่การรวมกันของค่าต่างๆและดูว่ามุมมองของ PivotTable มีการเปลี่ยนแปลงอย่างไร คุณสามารถใช้การคลิก Microsoft ทั่วไปในเครื่องตัดข้อความซึ่งหมายความว่าหากคุณสามารถใช้ Control + คลิกเพื่อเลือกค่าต่างๆหรือ Shift + คลิกเพื่อเลือกช่วงของค่า

เครื่องตัดแต่ละเครื่องจะแสดงค่าที่เลือกไว้ซึ่งจะทำให้เห็นได้ชัดว่าสถานะของ PivotTable เป็นตัวกรองอย่างไร คุณสามารถเปลี่ยนสไตล์ของตัวแบ่งส่วนได้หากต้องการโดยคลิกที่แบบเลื่อนลง Quick Styles ในส่วนเครื่องตัดส่วนของแท็บ Options