แบ่งตารางเป็นแผ่น

Microsoft Excel มีเครื่องมือมากมายสำหรับการรวบรวมข้อมูลจากหลายตาราง (จากชีตต่างๆ หรือจากไฟล์ต่างๆ): ลิงก์โดยตรง, ฟังก์ชัน ทางอ้อม (ทางอ้อม), ส่วนเสริม Power Query และ Power Pivot เป็นต้น จากด้านนี้ของสิ่งกีดขวาง ทุกอย่างดูดี

แต่ถ้าคุณประสบปัญหาผกผัน - การกระจายข้อมูลจากตารางหนึ่งไปยังแผ่นงานอื่น - ทุกอย่างจะน่าเศร้ากว่ามาก น่าเสียดายที่ไม่มีเครื่องมือในตัวสำหรับการแยกข้อมูลดังกล่าวในคลังแสงของ Excel ดังนั้น คุณจะต้องใช้แมโครใน Visual Basic หรือใช้ตัวบันทึกแมโคร + การรวม Power Query กับ "การปรับแต่งไฟล์" เล็กน้อยหลังจากนั้น

มาดูกันว่าจะนำไปปฏิบัติได้อย่างไร

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

เรามีข้อมูลเบื้องต้นเช่นตารางที่มีขนาดมากกว่า 5000 แถวสำหรับการขาย:

ภารกิจ: เพื่อแจกจ่ายข้อมูลจากตารางนี้ตามเมืองบนแผ่นงานแยกต่างหากของหนังสือเล่มนี้ เหล่านั้น. ที่ทางออกคุณจะต้องได้รับเฉพาะแถวเหล่านั้นจากตารางที่มีการขายในเมืองที่เกี่ยวข้องในแต่ละแผ่น:

เตรียมการ

เพื่อไม่ให้มาโครโค้ดซับซ้อนและทำให้เข้าใจง่ายที่สุด ให้ทำตามขั้นตอนเตรียมการสองสามขั้นตอน

ครั้งแรกที่ สร้างตารางค้นหาแยกต่างหากโดยที่คอลัมน์เดียวจะแสดงรายชื่อเมืองทั้งหมดที่คุณต้องการสร้างชีตแยกกัน แน่นอน ไดเร็กทอรีนี้อาจไม่มีเมืองทั้งหมดที่มีอยู่ในข้อมูลต้นทาง แต่มีเพียงเมืองที่เราต้องการรายงานเท่านั้น วิธีที่ง่ายที่สุดในการสร้างตารางดังกล่าวคือการใช้คำสั่ง ข้อมูล – ลบรายการที่ซ้ำกัน (ข้อมูล — ลบรายการที่ซ้ำกัน) สำหรับการคัดลอกคอลัมน์ เมือง หรือฟังก์ชั่น ยูนิค (มีเอกลักษณ์) – หากคุณมี Excel 365 เวอร์ชันล่าสุด

เนื่องจากแผ่นงานใหม่ใน Excel ถูกสร้างโดยค่าเริ่มต้นก่อน (ทางด้านซ้ายของ) แผ่นงานปัจจุบัน (ก่อนหน้า) จึงเหมาะสมที่จะจัดเรียงเมืองในไดเรกทอรีนี้โดยเรียงลำดับจากมากไปน้อย (จาก Z ถึง A) – จากนั้นหลังจากสร้างแล้ว เมือง แผ่นงานจะถูกจัดเรียงตามตัวอักษร

ประการที่สอง пแปลงทั้งสองตารางเป็นไดนามิก (“ฉลาด”) เพื่อให้ทำงานกับพวกเขาได้ง่ายขึ้น เราใช้คำสั่ง หน้าแรก – จัดรูปแบบเป็นตาราง (หน้าแรก — รูปแบบเป็นตาราง) หรือแป้นพิมพ์ลัด Ctrl+T. บนแท็บที่ปรากฏขึ้น นวกรรมิก (ออกแบบ) มาเรียกพวกเขาว่า tablโปรดาจิ и ตารางเมืองตามลำดับ:

วิธีที่ 1 มาโครสำหรับการหารด้วยแผ่นงาน

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

ตัวแยกย่อย() สำหรับแต่ละเซลล์ในช่วง("таблГорода") ช่วง("таблПродажи")).ฟิลด์ตัวกรองอัตโนมัติ:=3, เกณฑ์1:=เซลล์.ช่วงค่า("таблПродажи[#All]") เซลล์พิเศษ(xlCellTypeVisible).คัดลอก Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit เซลล์ถัดไป เวิร์กชีต ("Данные") .ShowAllData End Sub	  

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

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

วิธีที่ 2 สร้างแบบสอบถามหลายรายการใน Power Query

วิธีการก่อนหน้านี้สำหรับความกะทัดรัดและความเรียบง่ายทั้งหมดนั้นมีข้อเสียอย่างมาก - แผ่นงานที่สร้างโดยมาโครจะไม่อัปเดตเมื่อมีการเปลี่ยนแปลงในตารางการขายดั้งเดิม หากจำเป็นต้องอัปเดตทันที คุณจะต้องใช้ชุด VBA + Power Query หรือสร้างโดยใช้แมโครที่ไม่ใช่แค่ชีตที่มีข้อมูลคงที่ แต่อัปเดตคิวรี Power Query

มาโครในกรณีนี้คล้ายกับก่อนหน้านี้บางส่วน (ยังมีวงจร สำหรับแต่ละ … ต่อไป เพื่อวนซ้ำในเมืองต่างๆ ในไดเร็กทอรี) แต่ภายในลูปจะไม่มีการกรองและคัดลอกอีกต่อไป แต่สร้างคิวรี Power Query และอัปโหลดผลลัพธ์ไปยังแผ่นงานใหม่:

Sub Splitter2() สำหรับแต่ละเซลล์ในช่วง ("ตารางเมือง") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes (ที่มา , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""แถวที่ใช้ตัวกรอง"" = Table.Se " & _ "lectRows(#""เปลี่ยนประเภท"" แต่ละอัน ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "ใน " & Chr(13) & "" & Chr(10) & " #""แถวที่ใช้ตัวกรอง""" ActiveWorkbook.Worksheets.Add ด้วย ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd SQL .CommandText = Array ("เลือก *FROM [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value เซลล์ถัดไป End Sub  

หลังจากเปิดตัว เราจะเห็นชีตเดียวกันตามเมือง แต่คิวรี Power Query ที่สร้างไว้แล้วจะสร้างขึ้นมา:

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

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