เมื่อเราต้องจัดการกับหลายแผ่น Excel บางครั้งเราต้องคัดลอกข้อมูลจากแผ่นคำนวณหนึ่งไปอีกแผ่นหนึ่งพร้อมเงื่อนไขบางอย่างเพื่อความอ่านที่ดีขึ้น การใช้ VBA เป็นวิธีที่มีประสิทธิภาพ ที่รวดเร็วที่สุด และปลอดภัยที่สุดในการดำเนินการใดๆ ใน Excel ในบทความนี้ เราจะแสดงวิธี คัดลอกข้อมูลไปอีกแผ่นด้วย Advanced Filter ใน Excel ด้วย VBA macro
ดาวน์โหลดสมุดทำงาน (Download Workbook)
คุณสามารถดาวน์โหลดสมุดทำงาน Excel ฝึกปฏิบัติฟรีได้จากที่นี่
3 วิธี ใช้ VBA คัดลอกข้อมูลไปอีกแผ่นพร้อม Advanced Filter ใน Excel
ดูชุดข้อมูลต่อไปนี้ มีแผ่นงาน Excel ชื่อ Original แผ่นนี้ประกอบด้วยข้อมูลบางส่วนจากช่วง B4 ถึง E12 ช่วงนั้นยังมีค่าซ้ำ ช่วง G4 ถึง H5 เก็บ เกณฑ์ (criteria) สิ่งที่เราต้องการคือ เราจะคัดลอกข้อมูลจากช่วง B5:E12 เมื่อ John จากคอลัมน์ Name (ชื่อ) มี Marks (คะแนน) น้อยกว่า 80 (เกณฑ์ใน เซลล์ G4:H5) และวางข้อมูลเฉพาะนั้นไปแผ่นอื่นด้วย Advanced Filter ใน Excel ด้วย 3 วิธีที่แตกต่างกัน
เราจะดูวิธี แทรกแมโครแบบฮาร์ดโค้ดเพื่อคัดลอกข้อมูล, วิธี advanced filter โดยการเลือก (advance filter by selection) และวิธี โอนข้อมูลจากแผ่นหนึ่งไปอีกแผ่นโดยการบันทึกแมโคร (recording the macro) และชุดข้อมูลข้างต้นจะเป็นตัวอย่างสำหรับรันทุกวิธีที่กล่าวมา
1. แทรกโค้ด VBA คัดลอกข้อมูลไปอีกแผ่นพร้อม Advanced Filter ใน Excel
ที่นี่ คุณจะได้เรียนรู้ โค้ด VBA เพื่อ คัดลอกข้อมูลเฉพาะจากแผ่น Original เมื่อคะแนนของ John น้อยกว่า 80 ไปอีก แผ่นชื่อ Target ด้วย advanced filter
ขั้นตอน:
- ก่อนอื่น กด Alt + F11 บนคีย์บอร์ด หรือไปที่แท็บ Developer -> Visual Basic เพื่อเปิด Visual Basic Editor
- ต่อไป ในหน้าต่างโค้ดที่ป๊อปอัพ จากแถบเมนู คลิก Insert -> Module
- ก็อปปี้โค้ดต่อไปนี้และวางในหน้าต่างโค้ด
Sub AdvancedFilterCode()
Dim iRange As Range
Dim iCriteria As Range
'set the range to filter and the criteria range
Set iRange = Sheets("Original").Range("B4:E12")
Set iCriteria = Sheets("Original").Range("G4:H5")
'copy the filtered data to the destination
iRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=iCriteria, CopyToRange:=Sheets("Target").Range("B4:E4"), Unique:=True
End Subโค้ดของคุณพร้อมรันแล้ว
- กด F5 บนคีย์บอร์ด หรือจากแถบเมนูเลือก Run -> Run Sub/UserForm คุณยังสามารถคลิกไอคอน Run เล็กๆ ในแถบเมนูย่อยเพื่อรันแมโครได้
หลังจากรันโค้ดแล้ว ดูภาพด้านล่างเพื่อดูผลลัพธ์
ผลลัพธ์ คือ ข้อมูลที่ คะแนนของ John น้อยกว่า 80 เท่านั้น ถูก คัดลอก ใน แผ่น Target จากแผ่น Original ด้วย Advanced Filter ของ VBA
อ่านเพิ่มเติม: วิธีใช้ Advanced Filter คัดลอกข้อมูลไปแผ่นงานอื่นใน Excel
2. ใช้ VBA Macro กรองข้อมูลโดยการเลือกของผู้ใช้ (User-Defined Selection)
ตอนนี้ คุณจะได้เรียนรู้วิธี กรองข้อมูลเฉพาะจากแผ่น Original เมื่อ คะแนนของ John น้อยกว่า 80 ไปอีก แผ่นชื่อ Destination จาก ช่วงที่ผู้ใช้เลือก (user-selected range) ด้วย advanced filter ของ VBA ใน Excel
ขั้นตอน:
- เปิด Visual Basic Editor จากแท็บ Developer และ Insert Module ในหน้าต่างโค้ด เช่นเดียวกับก่อนหน้า
- ในหน้าต่างโค้ด ก็อปปี้โค้ดต่อไปนี้และวาง
Sub AdvancedFilterBySelection()
Dim iTrgt As String
Dim iRange As Range
Dim iCriteria As Range
Dim iDestination As Range
On Error Resume Next
iTrgt = ActiveWindow.RangeSelection.Address
Set iRange = Application.InputBox("Select Range to Filter", "Excel", iTrgt, , , , , 8)
If iRange Is Nothing Then Exit Sub
Set iCriteria = Application.InputBox("Select Criteria Range", "Excel", "", , , , , 8)
If iCriteria Is Nothing Then Exit Sub
Set iDestination = Application.InputBox("Select Destination Range", "Excel", "", , , , , 8)
If iDestination Is Nothing Then Exit Sub
iRange.AdvancedFilter xlFilterCopy, iCriteria, iDestination, False
iDestination.Worksheet.Activate
iDestination.Worksheet.Columns.AutoFit
End Subโค้ดของคุณพร้อมรันแล้ว
- Run (รัน) แมโคร
- กล่องป๊อปอัพจะปรากฏ เลือกช่วง ที่คุณต้องการ กรอง (ในกรณีนี้ คือช่วง B4 ถึง E12)
- กด OK
- กล่องป๊อปอัพอีกอันจะปรากฏ ครั้งนี้คุณต้อง เลือกช่วงเกณฑ์ ที่คุณเก็บไว้ในชุดข้อมูล (ช่วงเกณฑ์จาก เซลล์ G4 ถึง H5 สำหรับชุดข้อมูลของเรา)
- กด OK อีกครั้ง
- ครั้งสุดท้าย อีกกล่องป๊อปอัพจะปรากฏ และตอนนี้ คุณต้อง เลือกช่วงปลายทาง (destination range) ที่คุณต้องการ เก็บข้อมูลที่คัดลอก ในกรณีนี้ คือ เซลล์ B2 ใน แผ่น Destination
- กด OK
ดูผลลัพธ์จากภาพด้านล่าง
สุดท้าย ข้อมูลที่ คะแนนของ John น้อยกว่า 80 เท่านั้น ถูก คัดลอก ใน แผ่น Destination จากแผ่น Original ด้วย Advanced Filter ของ VBA
เนื้อหาที่เกี่ยวข้อง: Excel Advanced Filter Not Working (2 เหตุผล & วิธีแก้)
การอ่านที่คล้ายคลึงกัน
- วิธีใช้ Advanced Filter หาก Criteria Range มีข้อความใน Excel
- Dynamic Advanced Filter Excel (VBA & Macro)
- Advanced Filter พร้อม Criteria Range ใน Excel (18 การประยุกต์ใช้)
- Advanced Filter พร้อมหลายเกณฑ์ใน Excel (15 ตัวอย่างที่เหมาะสม)
- ตัวอย่าง Excel VBA: ใช้ Advanced Filter พร้อมเกณฑ์ (6 เกณฑ์)
3. ใช้ VBA Macro คัดลอกข้อมูลไปอีกแผ่นโดยการบันทึกแมโคร (Macro Recording) ใน Excel
ในส่วนนี้ คุณจะได้รู้วิธีแก้ปัญหาเดียวกัน คือ ดึงข้อมูลเฉพาะจากแผ่น Original เมื่อคะแนนของ John น้อยกว่า 80 ไปอีก แผ่นชื่อ Filtered โดย การบันทึกแมโคร (Macro recording) ของ VBA ใน Excel
ขั้นตอน:
- เปิด แผ่นงานใหม่ (ในกรณีนี้ คือแผ่น Filtered)
- ในแผ่นนั้น เก็บเฉพาะแถวหัวข้อ (header row) ของชุดข้อมูลเดิม
- ไปที่แผ่น Original จะเห็นเครื่องหมาย แมโครเล็กๆ ที่มุมซ้ายล่างของแผ่น คลิกเครื่องหมายนั้น เพื่อเริ่มบันทึกแมโคร
- หน้าต่าง Record Macro ป๊อปอัพ จะปรากฏ ให้ ชื่อแมโคร (Macro name) ที่ต้องการ เราตั้ง AdvancedFilter เป็น ชื่อแมโคร
- เลือกที่ต้องการ เก็บแมโคร เราต้องการเก็บในสมุดทำงานที่มีอยู่ จึงเลือก This Workbook
- คลิก OK
- กลับไปที่แผ่น Original จะสังเกตว่าแมโครที่เริ่มต้นบันทึกอยู่
- ไปที่ แผ่นงาน ที่จะ เก็บข้อมูลที่คัดลอก (เช่น แผ่น Filtered)
- มีเซลล์ที่ใช้งานอยู่ (active cell) ในแผ่นนั้น และไปที่ Data -> Advanced
- กล่อง Advanced Filter ป๊อปอัพ จะปรากฏ
- ขั้นแรก ติ๊กถูก ตัวเลือก Copy to another location จาก Action
- ในกล่องข้อความข้าง List range ไปที่แผ่น Original และ เลือกช่วง ที่จะกรอง (สำหรับชุดข้อมูลของเรา ช่วงคือ B4:E12)
- ในกล่องข้อความข้าง Criteria range เลือก ช่วงเกณฑ์ (คะแนนของ John น้อยกว่า 80) ที่เก็บใน แผ่น Original (สำหรับชุดข้อมูลของเรา ช่วงคือ G4:H5)
- ในกล่องข้อความข้าง Copy to ไปที่แผ่น Filtered แผ่นที่ต้องการ เก็บข้อมูลที่คัดลอก และ เลือกช่วงหัวข้อ (header range) (สำหรับชุดข้อมูลของเรา ช่วงคือ B4:E4)
- สุดท้าย คลิก OK
ดูผลลัพธ์จากกระบวนการทั้งหมดจากภาพด้านล่าง เฉพาะข้อมูล ที่ คะแนนของ John น้อยกว่า 80 ถูก คัดลอก ใน แผ่น Filtered จากแผ่น Original ด้วย การบันทึกแมโคร
- ตอนนี้ คลิกเครื่องหมายแมโคร ที่มุมซ้ายล่างของแผ่น เพื่อหยุดการบันทึกแมโคร ตอนนี้คุณมีแมโครที่บันทึกไว้ ซึ่งจะดำเนินการขั้นตอนที่อธิบายข้างต้นทุกครั้งที่คุณรัน
แต่วิธีนี้มี ข้อเสีย หากคุณเพิ่มข้อมูลใหม่ในแผ่น Original แผ่น Filtered จะ ไม่ได้รับการอัปเดต แม้ข้อมูลจะตรงตามเกณฑ์
เราต้องการให้แผ่น Filtered ใหม่ของเราถูกอัปเดตอัตโนมัติโดยการรันโค้ดเมื่อเพิ่มข้อมูลใหม่ในแผ่น Original เพื่อทำเช่นนั้น เราต้องปรับโค้ดเล็กน้อย
ทุกขั้นตอนเพื่อให้ได้สิ่งที่เราต้องการ แสดงด้านล่าง
ขั้นตอน:
- ก่อนอื่น จากแท็บ เลือก View -> Macros -> View Macros
- หน้าต่าง Macro ป๊อปอัพ จะปรากฏ เลือก ชื่อแมโคร (Macro name) ที่คุณเพิ่ง สร้างโดยการบันทึก (AdvancedFilter สำหรับกรณีของเรา)
- คลิก Edit (แก้ไข)
- โค้ดเบื้องหลังแมโครที่บันทึกจะปรากฏในหน้าต่างโค้ด (ดูภาพด้านล่าง)
- ลบส่วนที่ทำเครื่องหมายสีน้ำเงิน (แสดงในภาพด้านล่าง) ออกจากโค้ด
- ปรับโค้ด ตามภาพต่อไปนี้
- ตอนนี้ โค้ดที่อัปเดตแล้ว จะเป็น:
Sub AdvancedFilter()
Sheets("Original").Range("B4").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Sheets("Original").Range("G4:H5"), CopyToRange:=Sheets("Filtered").Range("B4:E4"), Unique:=False
End Sub- Save (บันทึก) โค้ดนี้
- กลับไปที่แผ่น Original และ เพิ่มข้อมูลใหม่ ที่ตรงตามเกณฑ์ ตัวอย่างเช่น เพิ่มแถวอีกแถวของข้อมูล John ที่ คะแนนคือ 76 ซึ่งตรงตามเกณฑ์ คะแนนน้อยกว่า 80
- จากนั้น Run (รัน) โค้ด และดูภาพต่อไปนี้สำหรับผลลัพธ์
- มี แถวที่คัดลอกใหม่ ใน แผ่น Filtered ของข้อมูล John ด้วย คะแนน 76 ที่ตรงตามเกณฑ์ (Marks<80)
อ่านเพิ่มเติม: วิธีใช้ Advanced Filter สำหรับบันทึกที่ไม่ซ้ำ (Unique Records Only) ใน Excel
สรุป
สรุปได้ว่า บทความนี้แสดง 3 วิธีที่แตกต่างกันในการ คัดลอกข้อมูลไปอีกแผ่นด้วย advanced filter ใน Excel ด้วย VBA macro หวังว่าบทความนี้จะเป็นประโยชน์มาก รู้สึกสบายๆ ถามคำถามเกี่ยวกับหัวข้อนี้ได้เลย
บทความที่เกี่ยวข้อง
- วิธีใช้ Advanced Filter ตัดเซลล์ว่างใน Excel (3 เทคนิคง่าย)
- Excel VBA: Advanced Filter พร้อมเกณฑ์หลายอย่างในช่วง (5 วิธี)
- วิธีใช้ Advanced Filter สำหรับบันทึกที่ไม่ซ้ำ (Unique Records Only) ใน Excel
- ใช้ Advanced Filter คัดลอกไปตำแหน่งอื่นใน Excel
- Excel Advanced Filter: ใช้ "Does Not Contain" (2 วิธี)
- ใช้ Advanced Filter ตามเกณฑ์หลายอย่างในคอลัมน์เดียวใน Excel
