วิธีการกำหนดค่า Excel Pivot ตาราง 2010

01 จาก 15

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

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

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 แบบง่ายๆ ฉันยังใช้เครื่องตัดแบ่งเพื่อกรองภาพซึ่งเป็นของใหม่ใน Excel 2010 ฉันจะอธิบายเกี่ยวกับเทคนิค BI ที่ซับซ้อนมากขึ้นโดยใช้ Expression Analysis Express (DAX) ใน PowerPivot for Excel 2010 ในอนาคตอันใกล้นี้ นี้รุ่นล่าสุดของ Microsoft Excel 2010 สามารถให้ค่าจริงสำหรับชุมชนผู้ใช้ของคุณ

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 (Open Database Connectivity) เพื่อให้คุณสามารถเชื่อมต่อได้ ตรวจสอบเว็บไซต์ของพวกเขาถ้าคุณต้องการดาวน์โหลดไดรเวอร์ ODBC

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

• A - ฟอร์ม PivotTable สร้างเป็นฟอร์มแรกในการสร้างการเชื่อมต่อกับ SQL Server เลือก "ใช้แหล่งข้อมูลภายนอก" และคลิกที่ปุ่มเลือกการเชื่อมต่อ เว้นตำแหน่งที่จะวาง Pivot Table เว้นแต่คุณต้องการสร้างแผ่นงานใหม่และวางไว้ที่นั่น
• B - แบบฟอร์มการเชื่อมต่อที่มีอยู่แสดงการเชื่อมต่อใด ๆ ในสมุดงานปัจจุบันในคอมพิวเตอร์ของคุณและเครือข่ายที่คุณกำลังเชื่อมต่ออยู่ การเชื่อมต่อที่มีอยู่เป็นเพียงแฟ้มข้อความที่มีข้อมูลการเชื่อมต่อที่จำเป็นในการเข้าถึงแหล่งข้อมูลเฉพาะเท่านั้น ในกรณีของเราเราจะสร้างแหล่งข้อมูลใหม่ คลิกปุ่มเรียกดูเพิ่มเติม
• C - คลิกที่ปุ่ม New Source จะเปิดตัว Data Connection Wizard
• D - เลือก Microsoft SQL Server และคลิก Next
• E - ใส่ชื่อเซิร์ฟเวอร์และเข้าสู่ระบบข้อมูลประจำตัว เลือกวิธีการรับรองความถูกต้องที่เหมาะสม ถ้าคุณไม่แน่ใจว่าจะใช้วิธีใดโปรดติดต่อผู้ดูแลระบบฐานข้อมูลของคุณ
  • ใช้การรับรองความถูกต้องของ Windows: วิธีนี้ใช้การเข้าสู่ระบบเครือข่ายของคุณเพื่อเข้าถึงฐานข้อมูล SQL Server
  • ใช้ชื่อผู้ใช้และรหัสผ่านต่อไปนี้: วิธีนี้ใช้เมื่อ SQL Server ได้รับการกำหนดค่ากับผู้ใช้แบบสแตนด์อโลนในการเข้าถึงฐานข้อมูล
• F - ในขั้นตอนนี้เราจะเลือกตารางเป็นตัวยึดตำแหน่ง เราจะแทนที่ตารางด้วย SQL ที่กำหนดเองซึ่งจะให้ข้อมูลที่เราต้องการในสมุดงาน Excel ของเรา
  • เลือกฐานข้อมูลที่คุณจะเชื่อมต่อ ในตัวอย่างนี้เรากำลังเชื่อมต่อกับฐานข้อมูลตัวอย่างของ AdventureWorks ที่จัดเตรียมโดย Microsoft ตรวจสอบ Connect กับตารางที่ต้องการและเลือกตารางแรก โปรดทราบว่าเราจะไม่เรียกข้อมูลจากตารางนี้
  • คลิกเสร็จสิ้นซึ่งจะเป็นการปิดตัวช่วยสร้างและนำคุณกลับไปที่เวิร์กบุค เราจะเปลี่ยนตารางตัวยึดสำหรับการสอบถาม SQL ที่กำหนดเองของเรา

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

04 จาก 15

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

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

05 จาก 15

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

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

ซึ่งจะแสดงรูปแบบการเชื่อมต่อคุณสมบัติ คลิกแท็บนิยาม นี้จะแสดงข้อมูลการเชื่อมต่อสำหรับการเชื่อมต่อปัจจุบันกับ 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 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 AND
Sales.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 เนื่องจากเราเปลี่ยนข้อมูลการเชื่อมต่อ เมื่อเราสร้างการเชื่อมต่อครั้งแรกระบบจะบันทึกข้อมูลไว้ในไฟล์ .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 เลือกฟิลด์ต่างๆตามที่ต้องการ ในตัวอย่างของเราฉันได้เพิ่ม Year, CountryRegionName และ ProductCategory คุณอาจต้องวางเครื่องหั่นย่อยไว้ในที่ที่คุณต้องการ โดยค่าเริ่มต้นค่าทั้งหมดจะถูกเลือกซึ่งหมายความว่าไม่ได้ใช้ตัวกรองใด ๆ

14 จาก 15

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

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

15 จาก 15

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

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

การแนะนำ Slicers ช่วยปรับปรุงการใช้งาน PivotTables ได้ดียิ่งขึ้นและทำให้ Excel 2010 ใกล้เคียงกับการเป็นเครื่องมือทางธุรกิจระดับมืออาชีพ PivotTables มีการปรับปรุงค่อนข้างน้อยใน Excel 2010 และเมื่อรวมกับ PowerPivot ใหม่สร้างสภาพแวดล้อมการวิเคราะห์ที่มีประสิทธิภาพสูงมาก