เชี่ยวชาญการอ้างอิงตารางใน Excel VBA – 20 เทคนิคที่พิสูจน์แล้ว

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

อ้างอิงตารางด้วยคุณสมบัติ ListObject

ขณะเขียนโค้ด VBA ListObjects ใช้เพื่ออ้างอิงตาราง Excel สามารถสร้างและลบตาราง Excel ด้วย VBA LISTOBJECTS การใช้ตาราง Excel อาจท้าทายสำหรับมือใหม่ และผู้ใช้ระดับกลางบางส่วน เนื่องจากบทความนี้พูดถึงการอ้างอิงตาราง จึงควรเข้าใจตารางใน Excel ให้ดีเมื่อเขียน VBA

ตัวอย่าง: เพื่อเลือกตารางทั้งหมดในแผ่นงาน Excel ต้องเขียนโค้ด VBA ดังนี้

ActiveSheet.ListObjects("Table_ref_1").Range.Select

บรรทัดนี้จะเลือกตาราง (“Table_ref_1” คือชื่อตารางในชุดข้อมูลของเรา)

20 ตัวอย่างที่เหมาะสมสำหรับการใช้การอ้างอิงตารางใน Excel ด้วยโค้ด VBA

ส่วนต่อไปจะใช้ตัวอย่าง 20 รายการที่มีประสิทธิภาพและเทคนิคในการอ้างอิงตารางใน Excel ต้องใช้โค้ด VBA นี่คือภาษาโปรแกรมเชิงเหตุการณ์ของ Microsoft ที่เรียกว่า Visual Basic for Applications (VBA) เพื่อใช้ฟีเจอร์นี้ ต้องมีแท็บ Developer แสดงบนริบบอน คลิกที่นี่เพื่อดูวิธี แสดงแท็บ Developer บนริบบอน ส่วนนี้ให้รายละเอียดมาก ควรเรียนรู้และนำไปใช้เพื่อพัฒนาความคิดและความรู้ Excel เราใช้ Microsoft Office 365 แต่สามารถใช้เวอร์ชันอื่นได้ตามสะดวก

1. เลือกตารางทั้งหมด

นี่คือวิธีเลือกตารางทั้งหมด ก่อนอื่น เพื่ออ้างอิงตารางที่สร้างไว้ใน Excel ต้องประกาศตารางเป็น ListObject ก่อน แล้วอ้างอิงด้วยชื่อที่มีใน Excel สำหรับการสาธิต ใช้ชุดข้อมูลดังนี้

ขั้นตอน:

📌 Steps:

• VBA มีหน้าต่างแยกต่างหาก ให้เปิดหน้าต่าง VBA ไปที่แท็บ Developer บนริบบอน เลือก Visual Basic จากกลุ่ม Code

• โมดูล VBA เก็บโค้ดใน Visual Basic Editor นามสกุล .bcf สร้างหรือแก้ไขได้ง่ายผ่านหน้าต่าง VBA editor ไปที่แท็บ Insert บน VBA editor คลิก Module จากเมนูแบบเลื่อนลง

• จะได้โมดูลใหม่ เลือกโมดูลนั้น แล้วเขียนโค้ดดังนี้

Sub Select_whole_Table() ActiveSheet.ListObjects("Table_ref_1").Range.Select End Sub

• บันทึกโค้ด
• กด F5 หรือจากแถบเมนูเลือก Run -> Run Sub/UserForm หรือคลิกไอคอน Play ขนาดเล็กบนแถบเมนูย่อย

• จะเลือกตารางทั้งหมดตามภาพ

2. คัดลอกตารางทั้งหมด

วิธีนี้แสดงการคัดลอกตารางทั้งหมด ใช้ชุดข้อมูลดังนี้

ขั้นตอนการคัดลอกตารางทั้งหมด:

📌 Steps:

• เปิดหน้าต่าง VBA: แท็บ Developer -> Visual Basic จากกลุ่ม Code

• สร้างโมดูล: แท็บ Insert -> Module

• เขียนโค้ดในโมดูล:

Sub copy_whole_Table() ActiveSheet.ListObjects("Table_ref_18").Range.Copy End Sub

• บันทึกโค้ด
• กด F5 หรือ Run -> Run Sub/UserForm หรือคลิกไอคอน Play

• จะคัดลอกตารางทั้งหมดตามภาพ

3. อ้างอิงคอลัมน์ของตาราง

สาธิตการอ้างอิงคอลัมน์ของตาราง ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub ReferenceEntireColumn() ActiveSheet.ListObjects("Table_ref").ListColumns("Seller").Range.Select End Sub

• บันทึกและรัน (F5)

• จะเลือกคอลัมน์ทั้งหมดของตารางตามภาพ

4. ดึงค่าจากเซลล์ในตาราง

สาธิตการดึงค่าจากเซลล์ในตาราง ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub get_Value() Dim Table1 As ListObject Set Table1 = ActiveSheet.ListObjects("Table_ref_2") Set Value = Table1.DataBodyRange.Columns(1).Find("Sam", LookAt:=xlWhole) MsgBox Value End Sub

• บันทึกและรัน (F5)

• จะดึงค่าจากเซลล์ในตารางตามภาพ

5. เพิ่มแถวใหม่

สาธิตเพิ่มแถวใหม่ในตารางที่มีอยู่ ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Entering_row() Dim Table1 As ListObject Set Table1 = ActiveSheet.ListObjects("Table_ref_3") Table1.ListRows.Add End Sub

• บันทึกและรัน (F5)

• จะเพิ่มแถวใหม่ในตารางตามภาพ

6. เพิ่มคอลัมน์ใหม่

สาธิตเพิ่มคอลัมน์ใหม่ในตารางที่มีอยู่ ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Entering_Column() Dim Table1 As ListObject Set Table1 = ActiveSheet.ListObjects("Table_ref_4") Table1.ListColumns.Add End Sub

• บันทึกและรัน (F5)

• จะเพิ่มคอลัมน์ใหม่ในตารางตามภาพ

7. กรองตาราง (Filtering a Table)

สาธิตการกรองตารางใน Excel ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Select_AutoFiltered_VisibleRows_NewSheet() ActiveSheet.ListObjects("Table_25").Range.AutoFilter _ Field:=2, Criteria1:="=Texas" End Sub

• บันทึกและรัน (F5)

• จะกรองตารางตามข้อความ “Texas” ตามภาพ

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

สาธิตลบแถวและคอลัมน์จากตาราง ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub delete_row_column() ActiveSheet.ListObjects("Table_ref_9").ListRows(2).Delete ActiveSheet.ListObjects("Table_ref_9").ListColumns(2).Delete End Sub

• บันทึกและรัน (F5)

• จะลบแถวและคอลัมน์จากตารางตามภาพ

9. เลือกข้อมูลจากตารางโดยไม่รวมหัวคอลัมน์

สาธิตเลือกข้อมูลจากตารางโดยไม่รวมหัวคอลัมน์ ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub ReferenceEntireColumnWithoutHeader() ActiveSheet.ListObjects("Table_ref_6").ListColumns("Seller").DataBodyRange.Select End Sub

• บันทึกและรัน (F5)

• จะเลือกข้อมูลโดยไม่รวมหัวคอลัมน์ตามภาพ

10. เลือกหัวคอลัมน์

สาธิตเลือกหัวคอลัมน์ ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Selecting_Column_Header() ActiveSheet.ListObjects("Table_ref_7").HeaderRowRange(4).Select End Sub

• บันทึกและรัน (F5)

• จะเลือกหัวคอลัมน์ตามภาพ

11. เลือกแถวทั้งหมด

สาธิตเลือกแถวทั้งหมด ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Selecting_row_entire() ActiveSheet.ListObjects("Table_ref_8").ListRows(3).Range.Select End Sub

• บันทึกและรัน (F5)

• จะเลือกแถวทั้งหมดตามภาพ

12. ปรับขนาดช่วงของตาราง (Resize Range)

สาธิตปรับขนาดช่วงของตาราง ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub resize() Dim Table1 As ListObject Set Table1 = ActiveSheet.ListObjects("Table_ref_5") Dim Rng As Range Set Rng = Range("B4:C8") Table1.Resize Rng End Sub

• บันทึกและรัน (F5)

• จะปรับขนาดช่วงของตารางตามภาพ

13. เปลี่ยนสไตล์ตาราง (Changing Table Style)

สาธิตเปลี่ยนสไตล์ตาราง ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub ChangingTable1_Style() ActiveSheet.ListObjects("Table_ref_12").TableStyle = "TableStyleLight15" End Sub

• บันทึกและรัน (F5)

• จะเปลี่ยนสไตล์ตารางตามภาพ

14. ตั้งค่าสไตล์ตารางเริ่มต้น (Set Default Table Style)

สาธิตตั้งค่าสไตล์ตารางเริ่มต้น ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Setting_Default_Table1_Style() ActiveWorkbook.DefaultTableStyle = "TableStyleMedium2" End Sub

• บันทึกและรัน (F5)

• จะตั้งค่าสไตล์ตารางเริ่มต้นตามภาพ

15. วนลูปผ่านทุกเซลล์ในตาราง

ตัวอย่างนี้แสดงการวนลูปผ่านทุกเซลล์ในตาราง ใช้ ListRows.Count สำหรับวนลูปแต่ละแถว ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Loopingthrough() Dim Table1 As ListObject Set Table1 = ActiveSheet.ListObjects("Table_ref_14") For i = 1 To Table1.ListRows.Count Table1.ListRows(i).Range.Select Next i End Sub

• บันทึกและรัน (F5)

• จะวนลูปผ่านทุกเซลล์ในตารางตามภาพ

16. ทำให้หัวตารางมองเห็นได้ (Make Table Header Visible)

สาธิตทำให้หัวตารางมองเห็นได้ ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Table_header_visible() ActiveSheet.ListObjects("Table_ref_15").ShowHeaders = True End Sub

• บันทึกและรัน (F5)

• จะทำให้หัวตารางมองเห็นได้ตามภาพ

17. นับแถวและคอลัมน์

สาธิตนับแถวและคอลัมน์จากตาราง ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub counting_rows_columns() rowNumber = ActiveSheet.ListObjects("Table_ref_16").ListRows.Count columnNumber = ActiveSheet.ListObjects("Table_ref_16").ListColumns.Count MsgBox "Number of Rows: " & rowNumber & vbCrLf & _ "Number of Columns: " & columnNumber End Sub

• บันทึกและรัน (F5)

• จะนับแถวและคอลัมน์จากตารางตามภาพ

18. ตรวจสอบว่ามีตารางในแผ่นงานหรือไม่

สาธิตตรวจสอบว่ามีตารางในแผ่นงานหรือไม่ ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Checking_If1_Table1_Exists() Dim ws1 As Worksheet Dim tbl1 As ListObject Dim tbl1Name As String Dim tbl1Exists As Boolean tbl1Name = "Table_ref_17" For Each ws1 In ActiveWorkbook.Worksheets For Each tbl1 In ws1.ListObjects If tbl1.Name = tb1lName Then tb1lExists = True End If Next tbl1 Next ws1 If tbl1Exists = True Then MsgBox "Table " & tb1lName & " Doesn’t Exists." Else MsgBox "Table " & tbl1Name & " Exists." End If End Sub

• บันทึกและรัน (F5)

• จะตรวจสอบว่ามีตารางในแผ่นงานตามภาพ

19. แปลงตารางเป็นช่วงปกติ (Convert Table to Normal Range)

ตัวอย่างแปลงตารางเป็นช่วงปกติ ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub Converting_Table1_To_NormalRange() ActiveSheet.ListObjects("Table_ref_11").Unlist End Sub

• บันทึกและรัน (F5)

• จะแปลงตารางเป็นช่วงปกติตามภาพ

20. ลบตาราง (Delete a Table)

สาธิตลบตาราง ใช้ชุดข้อมูล:

📌 Steps:

• เปิด VBA: Developer -> Visual Basic

• สร้างโมดูล: Insert -> Module

• เขียนโค้ด:

Sub delete_table_entire() Dim Table1 As ListObject Set Table1 = ActiveSheet.ListObjects("Table_ref_10") Table1.Delete End Sub

• บันทึกและรัน (F5)

• จะลบตารางตามภาพ

สรุป

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

อย่าลืมเยี่ยมชมเว็บไซต์ Exceldemy.com สำหรับปัญหาและวิธีแก้เกี่ยวกับ Excel ต่างๆ เรียนรู้วิธีใหม่ๆ และเติบโตต่อไป!

อ่านเพิ่มเติม…

การเปลี่ยนตัวพิมพ์เล็ก-ใหญ่ด้วย Worksheet Functions และ Excel VBA

[8 Tips] How to Make Excel Tables Look Good!