ตาราง Pivot ในหลายช่วงข้อมูล

การกำหนดปัญหา

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

ก่อนที่เราจะเริ่ม ขอชี้แจงสองสามประเด็น เบื้องต้น ฉันเชื่อว่าข้อมูลของเราตรงตามเงื่อนไขต่อไปนี้:

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

วิธีที่ 1: สร้างตารางสำหรับ pivot โดยใช้ Power Query

เริ่มต้นจากเวอร์ชัน 2010 สำหรับ Excel มี Add-in ของ Power Query ฟรีที่สามารถรวบรวมและแปลงข้อมูลใดๆ จากนั้นให้เป็นแหล่งสำหรับสร้างตารางสาระสำคัญ การแก้ปัญหาของเราด้วยความช่วยเหลือของ Add-in นี้ไม่ใช่เรื่องยากเลย

ขั้นแรก ให้สร้างไฟล์เปล่าใหม่ใน Excel โดยการประกอบจะเกิดขึ้นในนั้น จากนั้นจะมีการสร้างตารางเดือยในไฟล์นั้น

จากนั้นบนแท็บ ข้อมูล (ถ้าคุณมี Excel 2016 หรือใหม่กว่า) หรือบนแท็บ Power Query (ถ้าคุณมี Excel 2010-2013) เลือกคำสั่ง สร้างแบบสอบถาม – จากไฟล์ – Excel (รับข้อมูล — จากไฟล์ — Excel) และระบุไฟล์ต้นทางด้วยตารางที่จะรวบรวม:

ในหน้าต่างที่ปรากฏขึ้น ให้เลือกแผ่นงานใดก็ได้ (ไม่ว่าแผ่นใด) แล้วกดปุ่มด้านล่าง เปลี่ยนแปลง (แก้ไข):

หน้าต่าง Power Query Query Editor ควรเปิดขึ้นที่ด้านบนของ Excel ที่ด้านขวาของหน้าต่างบนแผง ขอพารามิเตอร์ ลบขั้นตอนที่สร้างขึ้นโดยอัตโนมัติทั้งหมดยกเว้นขั้นตอนแรก - แหล่ง (ที่มา):

ตอนนี้เราเห็นรายการทั่วไปของชีตทั้งหมด หากไฟล์มีไซด์ชีตอื่นนอกเหนือจากแผ่นข้อมูล ในขั้นตอนนี้ หน้าที่ของเราคือเลือกเฉพาะชีตที่ต้องการโหลดข้อมูล ยกเว้นแผ่นอื่นๆ ทั้งหมดที่ใช้ตัวกรองในส่วนหัวของตาราง:

ลบคอลัมน์ทั้งหมดยกเว้นคอลัมน์ ข้อมูลโดยคลิกขวาที่ส่วนหัวของคอลัมน์แล้วเลือก ลบคอลัมน์อื่นๆ (ลบ คอลัมน์อื่นๆ):

จากนั้น คุณสามารถขยายเนื้อหาของตารางที่รวบรวมได้โดยคลิกที่ลูกศรคู่ที่ด้านบนของคอลัมน์ (ช่องทำเครื่องหมาย ใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้า คุณสามารถปิดได้):

หากคุณทำทุกอย่างถูกต้องแล้ว ณ จุดนี้คุณควรเห็นเนื้อหาของตารางทั้งหมดที่รวบรวมไว้ด้านล่าง:

มันยังคงเพิ่มแถวแรกไปที่ส่วนหัวของตารางด้วยปุ่ม ใช้บรรทัดแรกเป็นส่วนหัว (ใช้แถวแรกเป็นส่วนหัว) แถบ หน้าแรก (บ้าน) และลบส่วนหัวของตารางที่ซ้ำกันออกจากข้อมูลโดยใช้ตัวกรอง:

บันทึกทุกอย่างที่ทำด้วยคำสั่ง ปิดและโหลด - ปิดและโหลดใน... (ปิด & โหลด — ปิด & โหลดไปที่…) แถบ หน้าแรก (บ้าน)และในหน้าต่างที่เปิดขึ้น ให้เลือกตัวเลือก การเชื่อมต่อเท่านั้น (การเชื่อมต่อเท่านั้น):

ทุกอย่าง. มันยังคงอยู่เพียงเพื่อสร้างบทสรุป ในการดำเนินการนี้ ให้ไปที่แท็บ แทรก – PivotTable (แทรก — ตารางหมุน), เลือกตัวเลือก ใช้แหล่งข้อมูลภายนอก (ใช้แหล่งข้อมูลภายนอก)แล้วโดยการคลิกที่ปุ่ม เลือกการเชื่อมต่อ, คำขอของเรา การสร้างและการกำหนดค่าเพิ่มเติมของเดือยเกิดขึ้นในลักษณะมาตรฐานโดยสมบูรณ์ โดยการลากฟิลด์ที่เราต้องการลงในแถว คอลัมน์ และพื้นที่ค่า:

หากข้อมูลต้นทางมีการเปลี่ยนแปลงในอนาคตหรือมีการเพิ่มแผ่นงานร้านค้าอีกสองสามรายการก็เพียงพอที่จะอัปเดตการสืบค้นและสรุปของเราโดยใช้คำสั่ง รีเฟรชทั้งหมด แถบ ข้อมูล (ข้อมูล — รีเฟรชทั้งหมด).

วิธีที่ 2 เรารวมตารางด้วยคำสั่ง UNION SQL ในแมโคร

อีกวิธีแก้ไขปัญหาของเราแสดงโดยมาโครนี้ ซึ่งสร้างชุดข้อมูล (แคช) สำหรับตารางสาระสำคัญโดยใช้คำสั่ง ความสามัคคี ภาษาแบบสอบถาม SQL คำสั่งนี้รวมตารางจากทั้งหมดที่ระบุในอาร์เรย์ ชื่อแผ่น แผ่นงานหนังสือลงในตารางข้อมูลเดียว นั่นคือ แทนที่จะคัดลอกและวางช่วงจากแผ่นงานต่างๆ เป็นแผ่นเดียว เราทำแบบเดียวกันใน RAM ของคอมพิวเตอร์ จากนั้นมาโครจะเพิ่มแผ่นงานใหม่ด้วยชื่อที่กำหนด (variable ชื่อผลงาน) และสร้างสรุปที่สมบูรณ์ (!) ตามแคชที่รวบรวมไว้

เมื่อต้องการใช้แมโคร ให้ใช้ปุ่ม Visual Basic บนแท็บ ผู้พัฒนา (ผู้พัฒนา) หรือแป้นพิมพ์ลัด อื่น ๆ+F11. จากนั้นเราแทรกโมดูลเปล่าใหม่ผ่านเมนู แทรก – โมดูล และคัดลอกรหัสต่อไปนี้ที่นั่น:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'ชื่อชีตที่ pivot ที่ได้จะแสดง ResultSheetName' = "Pivot array of Pivot" ชื่อที่มีตารางที่มา SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'เราสร้างแคชสำหรับตารางจากชีตจาก SheetsNames ด้วย ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) สำหรับ i = LBound (ชื่อชีต) ถึง UBound(ชื่อชีต) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" ถัดไป i Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) ลงท้ายด้วย ' สร้างแผ่นงานใหม่เพื่อแสดงตาราง pivot ที่เป็นผลลัพธ์ On Error Resume Next Application.DisplayAlerts = False Worksheets (ResultSheetName) ลบ Set wsPivot = Worksheets.Add wsPivo ที ชื่อ = ResultSheetName 'แสดงสรุปแคชที่สร้างขึ้นบนชีตนี้ Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) ตั้งค่า objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache.CreatePivotTable TableDestination.")=Range"A objPivotCache = Nothing Range ("A3") เลือก End With End Sub    

มาโครที่เสร็จแล้วสามารถเรียกใช้ด้วยแป้นพิมพ์ลัด อื่น ๆ+F8 หรือปุ่มมาโครบนแท็บ ผู้พัฒนา (ผู้พัฒนา — มาโคร).

ข้อเสียของแนวทางนี้:

• ข้อมูลไม่ได้รับการอัพเดตเนื่องจากแคชไม่มีการเชื่อมต่อกับตารางต้นทาง ถ้าคุณเปลี่ยนข้อมูลต้นฉบับ คุณต้องเรียกใช้แมโครอีกครั้งและสร้างสรุปอีกครั้ง
• เมื่อเปลี่ยนจำนวนชีตจำเป็นต้องแก้ไขโค้ดแมโคร (array ชื่อแผ่น).
  

แต่ในท้ายที่สุด เราได้ตารางเดือยที่เต็มเปี่ยมอย่างแท้จริง ซึ่งสร้างขึ้นจากหลายช่วงจากชีตต่างๆ:

Voila!

หมายเหตุทางเทคนิค: หากคุณได้รับข้อผิดพลาด เช่น "ผู้ให้บริการไม่ได้ลงทะเบียน" เมื่อเรียกใช้แมโคร เป็นไปได้มากว่าคุณมี Excel เวอร์ชัน 64 บิตหรือ Office เวอร์ชันที่ไม่สมบูรณ์ได้รับการติดตั้ง (ไม่มีการเข้าถึง) ในการแก้ไขสถานการณ์ ให้แทนที่ส่วนย่อยในโค้ดแมโคร:

	 ผู้ให้บริการ=Microsoft.Jet.OLEDB.4.0;  

ไปที่:

	ผู้ให้บริการ=Microsoft.ACE.OLEDB.12.0;  

และดาวน์โหลดและติดตั้งเครื่องมือประมวลผลข้อมูลฟรีจาก Access จากเว็บไซต์ Microsoft – Microsoft Access Database Engine 2010 Redistributable

วิธีที่ 3: รวมตัวช่วยสร้าง PivotTable จาก Excel เวอร์ชันเก่า

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

ใน pivot ดังกล่าว ไม่มีส่วนหัวของคอลัมน์ในรายการเขตข้อมูล ไม่มีการตั้งค่าโครงสร้างที่ยืดหยุ่น ชุดของฟังก์ชันที่ใช้มีจำกัด และโดยทั่วไป ทั้งหมดนี้ไม่ได้คล้ายกับตารางสาระสำคัญมากนัก อาจเป็นเพราะเหตุนี้ นับตั้งแต่ปี 2007 Microsoft ได้ลบฟังก์ชันนี้ออกจากกล่องโต้ตอบมาตรฐานเมื่อสร้างรายงานตารางสาระสำคัญ ตอนนี้ฟีเจอร์นี้ใช้ได้เฉพาะผ่านปุ่มที่กำหนดเองเท่านั้น ตัวช่วยสร้าง PivotTable(ตัวช่วยสร้างตาราง Pivot)ซึ่งสามารถเพิ่มลงใน Quick Access Toolbar ได้ตามต้องการผ่าน ไฟล์ – ตัวเลือก – ปรับแต่งแถบเครื่องมือด่วน – คำสั่งทั้งหมด (ไฟล์ — ตัวเลือก — ปรับแต่งแถบเครื่องมือด่วน — คำสั่งทั้งหมด):

หลังจากคลิกปุ่มที่เพิ่มแล้ว คุณต้องเลือกตัวเลือกที่เหมาะสมในขั้นตอนแรกของวิซาร์ด:

จากนั้นในหน้าต่างถัดไป ให้เลือกแต่ละช่วงและเพิ่มในรายการทั่วไป:

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

• การสร้างรายงานด้วย PivotTables
• ตั้งค่าการคำนวณใน PivotTables
• มาโครคืออะไร ใช้งานอย่างไร คัดลอกโค้ด VBA ไปที่ใด ฯลฯ
• การรวบรวมข้อมูลจากหลายแผ่นเป็นแผ่นเดียว (โปรแกรมเสริม PLEX)