Man in a black suit talking near a laptop with another man

เรียนรู้วิธีสร้างตารางสาระสำคัญใน Excel โดยอัตโนมัติด้วย VBA

ตาราง Pivot ใน Excel เป็นส่วนสำคัญในการทำให้ข้อมูลเข้าใจและเข้าใจได้ง่ายขึ้น ตารางสาระสำคัญสามารถย่อและรวมข้อมูลลงในโครงสร้างที่มีความหมายได้ มีการใช้กันอย่างแพร่หลายโดยผู้ใช้ MS Excel ในอุตสาหกรรมข้อมูล


คุณรู้หรือไม่ว่าคุณสามารถสร้างอัตโนมัติและสร้างตารางเดือยใน Excel ได้ด้วยคลิกเดียว? MS Excel ทำงานร่วมกับ VBA ได้เป็นอย่างดี และกลายเป็นเครื่องมือที่ยอดเยี่ยมสำหรับการทำงานซ้ำๆ โดยอัตโนมัติ

ต่อไปนี้คือวิธีการทำให้ตารางสาระสำคัญเป็นแบบอัตโนมัติโดยใช้มาโครใน MS Excel VBA


ใช้ชุดข้อมูลการฝึก

คุณสามารถดาวน์โหลดและใช้ตุ๊กตา ชุดข้อมูลจาก Tableau เพื่อปฏิบัติตามสคริปต์ VBA ในคู่มือนี้ โค้ด VBA จะทำงานร่วมกับชุดข้อมูลอื่นๆ โดยมีการแก้ไขพื้นฐานบางอย่าง ก่อนเริ่มต้น ตรวจสอบให้แน่ใจว่าได้เปิดใช้งานมาโครในเวิร์กบุ๊ก Excel ของคุณ

มีคอลัมน์พื้นฐานสองสามคอลัมน์ที่คุณสามารถใช้ได้ภายในตารางสาระสำคัญ เพื่อให้เข้าใจถึงความแตกต่างของตารางและโครงสร้างขั้นสุดท้าย คุณสามารถสร้างตารางเดือยพื้นฐานด้วยตนเองโดยใช้องค์ประกอบต่อไปนี้:

  • กรอง: ภาค
  • ชั้นเรียน: หมวดหมู่ย่อย
  • คอลัมน์: สภาพ
  • ค่า: ฝ่ายขาย

แกนสุดท้ายควรมีลักษณะดังนี้:

อย่างไรก็ตาม คุณสามารถให้ VBA ทำสิ่งนี้โดยอัตโนมัติแทนการตั้งค่าด้วยตนเอง

วิธีสร้างตารางเดือยใน Excel โดยอัตโนมัติ

ในการทำให้ตารางสาระสำคัญเป็นอัตโนมัติโดยใช้ VBA ให้เปิดไฟล์ Excel ใหม่และเปลี่ยนชื่อแผ่นงานดังนี้:

  • แผ่นแรก: แม่นยำ
  • แผ่นที่สอง: ข้อมูล

ดิ แม่นยำ แผ่นงานมีสคริปต์มาโครในขณะที่ไฟล์ . มี ข้อมูล แผ่นงานประกอบด้วยข้อมูลของคุณ ในแผ่นงานมาโคร คุณสามารถแทรกรูปร่างที่คุณต้องการและกำหนดมาโครให้กับรูปร่างได้ คลิกขวาที่รูปร่าง แล้วคลิกกำหนดมาโคร

ในกล่องโต้ตอบถัดไป ให้คลิกชื่อแมโครของคุณแล้วคลิกตกลง ขั้นตอนนี้ตั้งค่ามาโครสำหรับรูปร่าง

1. เปิดตัวแก้ไขมาร์กอัป Excel VBA

กด Alt + F11 เพื่อเปิดตัวแก้ไขโค้ด เมื่ออยู่ในตัวแก้ไขโค้ด ให้คลิกขวาที่ชื่อไฟล์ ตามด้วย การแทรก และ ความเหงา. สิ่งสำคัญคือต้องจำไว้ว่าคุณจะเขียนโค้ด VBA ทั้งหมดของคุณภายในโมดูลก่อนที่จะดำเนินการ

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

sub pivot_demo()

ชื่อหน่วยลงท้ายด้วย . จบซับซึ่งเป็นคำสั่งสิ้นสุดของหน่วย:

End Sub

2. การประกาศตัวแปร

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

Dim PSheet As Worksheet, DSheet As Worksheet
Dim PvtCache As PivotCache
Dim PvtTable As PivotTable
Dim PvtRange As Range
Dim Last_Row As Long, Last_Col As Long
Dim sht1 as Variant

คุณจะใช้ตัวแปรเหล่านี้สำหรับสิ่งต่อไปนี้:

  • แผ่น PS: แผ่นงานปลายทาง ซึ่ง VBA จะสร้างแกน
  • แผ่นงาน: แผ่นข้อมูล.
  • PvtCache: ฮับแคชประกอบด้วยฮับ
  • ตาราง Pvt: วัตถุตารางเดือย
  • ช่วง Pvt: ช่วงข้อมูลสำหรับแกน
  • Last_Row และ Last_Col: แถวและคอลัมน์ที่เติมข้อมูลล่าสุดภายในแผ่นข้อมูล (DSheet)
  • Sht1: ตัวแปรนี้เป็นตัวแปร

3. ยกเลิกคำเตือนและข้อความ

ข้อผิดพลาด คำเตือน และข้อความที่ไม่จำเป็นทำให้โค้ด VBA ช้าลง การระงับข้อความดังกล่าวจะทำให้กระบวนการเร็วขึ้นอย่างมาก

ใช้รหัสต่อไปนี้:

On Error Resume Next

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

ที่ไหน:

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

เมื่อเปิดรหัสนี้ จะบล็อกการแจ้งเตือน คำเตือน และข้อความใดๆ ที่ Excel อาจแสดง คุณสามารถปิดพารามิเตอร์ DisplayAlerts และ ScreenUpdating ได้โดยตั้งค่าเป็น Bloomer.

ในตอนท้ายของโค้ด คุณสามารถเรียกใช้อีกครั้งโดยตั้งค่าเป็น จริง.

4. ลบเดือยชีตที่มีอยู่

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

ในคู่มือนี้ มาสร้าง pivot sheet ใหม่เพื่อเก็บ pivot table

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

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

นี่คือวิธีที่คุณสามารถทำได้:

For Each sht1 In ActiveWorkbook.Worksheets
If sht1.Name = "Pivot" Then
sht1.Delete
End If
Next sht1

Worksheets.Add.Name = "Pivot"

5. กำหนดแหล่งข้อมูลและเดือยชีต

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

Set PSheet = Worksheets("Pivot")
Set DSheet = Worksheets("Data")

6. เลือกแถวและคอลัมน์ที่ใช้ล่าสุด

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

Last_Row = DSheet.Cells(Rows.Count, 1).End(xlUp).Row
Last_Col = DSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set PvtRange = DSheet.Cells(1, 1).Resize(Last_Row, Last_Col)

ที่ไหน:

  • แถวสุดท้าย: ตัวแปรเก็บหมายเลขแถวที่เติมล่าสุด เช่น 9995
  • Last_Col: ตัวแปรสำหรับเก็บหมายเลขคอลัมน์ที่เติมล่าสุด เช่น 21
  • ช่วง Pvt: PvtRange ระบุช่วงข้อมูลทั้งหมดของแกน

7. สร้าง Pivot Cache และ Pivot Table

เดือยแคชประกอบด้วยตารางเดือย ดังนั้น คุณต้องสร้างแคชก่อนสร้างตารางสาระสำคัญ คุณต้องใช้การอ้างอิงไวยากรณ์ของ VBA เพื่อสร้าง pivot cache ภายใน pivot sheet

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

Set PvtCache = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PvtRange).CreatePivotTable(TableDestination:=PSheet.Cells(2, 2), TableName:="MUODemoTable")
Set PvtTable = PvtCache.CreatePivotTable(TableDestination:=PSheet.Cells(1, 1), TableName:="MUODemoTable")

ที่ไหน:

  • สมุดงานที่ใช้งานอยู่: เวิร์กบุ๊กปัจจุบันที่คุณมีแผ่นข้อมูลและเดือย
  • การก่อสร้าง: ไวยากรณ์เริ่มต้นสำหรับการสร้างเดือยแคช
  • ประเภทแหล่งที่มา: เนื่องจากคุณมีข้อมูลอยู่ในสมุดงาน คุณสามารถกำหนดเป็น ฐานข้อมูล xl. ตัวเลือกอื่นๆ ได้แก่ xl uniteและ xl ภายนอกหรือ xlPivotTable.
  • แหล่งข้อมูล: คุณสามารถอ้างอิงช่วง pivot ก่อนหน้าเป็นข้อมูลต้นทางได้
  • CreatePivotTable: คำสั่งเริ่มต้นเพื่อสร้างตารางเดือย
  • กำหนดการ คุณต้องระบุการอ้างอิงแผ่นงานและเซลล์ที่คุณต้องการสร้างแกน
  • ชื่อตาราง: เลือกชื่อตารางสาระสำคัญ
  • CreatePivotTable: คำสั่งเริ่มต้นเพื่อสร้างตารางสาระสำคัญภายในแคชเดือย

8. แทรกแถว คอลัมน์ ตัวกรอง และค่า

เมื่อพร้อมใช้ตารางเดือย คุณจะต้องเริ่มเพิ่มพารามิเตอร์ภายในตัวกรอง แถว คอลัมน์ และค่าการจัดกลุ่ม คุณสามารถใช้ VBA pivotfields คำสั่งเริ่มประกาศรายละเอียด

ในการเพิ่มค่าตัวกรอง:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Region")
.Orientation = xlPageField
End With

ในการเพิ่มค่าแถว:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sub-Category")
.Orientation = xlRowField
End With

ในการเพิ่มค่าคอลัมน์:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("State")
.Orientation = xlColumnField
End With

ในการเพิ่มค่าการจัดกลุ่ม:

With ActiveSheet.PivotTables("MUODemoTable").PivotFields("Sales")
.Orientation = xlDataField
.Function = xlSum
End With

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

  • xlPageField: เพื่อเพิ่มตัวกรอง
  • xlRowField: เพื่อเพิ่มแถว
  • xlRowField: เพื่อเพิ่มคอลัมน์

สุดท้าย คุณสามารถใช้ไฟล์ xlDataField คำสั่งคำนวณการจัดกลุ่มค่า คุณสามารถใช้ฟังก์ชันการรวมอื่นๆ เช่น xlSum, xlAverage, xlCount, xlMax, xlMin และ xlProduct

9. เรียกใช้โค้ด Excel VBA เพื่อสร้างจุดหมุนอัตโนมัติ

สุดท้ายเมื่อโปรแกรมทั้งหมดพร้อม ก็สามารถเรียกใช้ได้โดยกด F5 หรือคลิก เกม ปุ่ม. เมื่อคุณกลับไปที่ pivot sheet ในเวิร์กบุ๊กของคุณ คุณจะเห็นว่าตารางสาระสำคัญใหม่ของคุณพร้อมสำหรับการตรวจทาน

หากต้องการดูการใช้งานโค้ดคำสั่งแบบทีละบรรทัด ให้ไปที่ตัวแก้ไขโค้ดแล้วกด F8 หลายครั้ง. ด้วยวิธีนี้ คุณสามารถดูวิธีการทำงานของโค้ดแต่ละบรรทัดและวิธีที่ VBA สร้าง pivot โดยอัตโนมัติ

เรียนรู้วิธีตั้งโปรแกรม Pivot Table โดยอัตโนมัติ

จุดหมุนไม่ได้จำกัดอยู่แค่ใน MS Excel ภาษาการเขียนโปรแกรมเช่น Python ช่วยให้คุณสร้างฮับที่ปรับให้เหมาะสมด้วยโค้ดเพียงไม่กี่บรรทัด

การเพิ่มประสิทธิภาพข้อมูลไม่สามารถง่ายกว่านี้ คุณสามารถเลือกคำสั่งของคุณได้อย่างมีประสิทธิภาพใน Python และบรรลุโครงสร้างสำคัญที่คล้ายกับ Excel ได้อย่างง่ายดาย

#เรยนรวธสรางตารางสาระสำคญใน #Excel #โดยอตโนมตดวย #VBA

Leave a Comment

Your email address will not be published. Required fields are marked *