วิธีทำให้ Vlookup เป็นแบบอัตโนมัติโดยใช้ Excel VBA

Vlookupเป็นฟังก์ชันที่สำคัญใน Excel และได้กลายเป็นส่วนสำคัญของการประมวลผลข้อมูล มีฟังก์ชันมากมายที่คุณสามารถรวมเข้ากับฐานข้อมูลที่ครอบคลุมได้

อย่างไรก็ตาม ถ้าคุณต้องการทำให้ฟังก์ชันนี้เป็นแบบอัตโนมัติ คุณสามารถทำได้ โดยเฉพาะอย่างยิ่งถ้าคุณรู้วิธีใช้ภาษาการทำงานอัตโนมัติที่มีอยู่ใน Excel และ VBA ต่อไปนี้คือวิธีที่คุณสามารถทำให้ฟังก์ชัน vlookup ใน Excel VBA เป็นแบบอัตโนมัติ ได้

สูตรฟังก์ชัน Vlookup ใน Excel

=vlookup(lookup_value, lookup_array, column_index, exact_match/partial_match)

ความหมายของข้อโต้แย้งในสูตร:

• lookup_value: ค่าพื้นฐานที่คุณต้องการค้นหาในอาร์เรย์
• lookup_array: ข้อมูลต้นฉบับ lookup_value จะถูกนำมาใช้เป็นข้อมูลอ้างอิง
• column_index: คอลัมน์ส่งคืนค่า
• exact_match/partial_match: ใช้ 0 หรือ 1 เพื่อระบุประเภทการรวมกัน

ฟังก์ชัน VBA Vlookup ใน Excel

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

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

ภาพประกอบ

ชุดข้อมูลนี้มีสองส่วน: ตารางการค้นหาและจุดข้อมูลเป้าหมาย ตารางค้นหาประกอบด้วย 3 คอลัมน์ โดยมีข้อมูลอยู่ใน 2 ช่อง หมวดหมู่ย่อยและชื่อผลิตภัณฑ์:

ตารางเป้าหมายมีคอลัมน์ที่เหมาะสม ไม่มีโฟลเดอร์ย่อยหรือข้อมูลชื่อผลิตภัณฑ์ โปรดทราบว่าค่าใน คอลัมน์ รหัสคำสั่งซื้อจะปรากฏในคอลัมน์เดียวกันในตารางค้นหาด้วย:

1. ทำการตั้งค่าที่จำเป็น

เริ่มต้นด้วยการเปิดตัวแก้ไขโค้ด (กดAlt + F11หรือไปที่ แท็บ นักพัฒนา ) ในสมุดงาน Excel ใหม่ และเพิ่มโมดูลเพื่อเริ่มการเขียนโค้ด ในโปรแกรมแก้ไขโค้ด ให้เพิ่มบรรทัดต่อไปนี้ที่ด้านบนของหน้าต่างการเขียนโปรแกรมเพื่อสร้างรูทีนย่อย:

Sub vlookup_fn1()End Sub

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

2. ประกาศตัวแปรและสร้างขอบเขตอ้างอิง

ขั้นแรก คุณต้องประกาศประเภทข้อมูลของตัวแปรโดยใช้คำสั่ง Dim:

Dim i as integer, lastrow as long

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

ในตัวอย่างนี้ ตัวแปร Lastrowมีค่าของแถวที่เติมล่าสุดในช่วงการค้นหา โดยเฉพาะ 17

lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row

ในตัวอย่างข้างต้น ตารางปลายทางจะขยายจาก A2:C17 และตารางต้นฉบับจะขยายจาก F2:H17 สูตร vlookup จะวนซ้ำแต่ละค่าในคอลัมน์ A ค้นหาในตารางต้นฉบับ และวางรายการที่รวมกันในคอลัมน์ B และ C

อย่างไรก็ตาม ก่อนที่จะเข้าสู่ลูปคุณต้องเก็บค่าช่วงไว้ในตัวแปรใหม่ ( my_range ) ดังนี้

my_range = Sheets("Sheet2").Range("F3:H" & lastrow)

คุณต้องระบุการอ้างอิงเซลล์เริ่มต้น (F3) และการอ้างอิงคอลัมน์สิ้นสุด (H) อย่างชัดเจน VBA จะเพิ่มค่าแถวโดยอัตโนมัติเพื่อทำให้อาร์เรย์การค้นหาสมบูรณ์

3. เขียนวงวนเพื่อวนไปตามค่าการค้นหาแต่ละค่า

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

i = 2

คุณสามารถใช้do- While loop เพื่อประมวลผลแต่ละแถว โดยเริ่มต้นที่แถวที่ 2 และสิ้นสุดที่แถวที่ 17 เช่นเดียวกับโค้ดอื่นๆ ลักษณะนี้มีความยืดหยุ่น การวนซ้ำจะทำงานจนกว่าเงื่อนไขจะเป็นเท็จ ใช้เงื่อนไขเพื่อทดสอบค่าที่ไม่ว่างในเซลล์แรกของแถวปัจจุบัน

Do While len(Sheets("Sheet2").Cells(i, 1).value) <> 0

ภายในลูป คุณสามารถเรียกใช้ฟังก์ชัน Vlookup เพื่อเติมเซลล์ได้:

Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False)Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False)

คำสั่งเหล่านี้ตั้งค่าของเซลล์ในแถวปัจจุบันในคอลัมน์ 2 และ 3 ตามลำดับ ใช้วัตถุ WorksheetFunction เพื่อเรียกใช้ฟังก์ชัน Vlookup โดยส่งค่าที่เกี่ยวข้องจากคอลัมน์ 1 เพื่อค้นหา นอกจากนี้ยังส่งผ่านช่วงที่คุณกำหนดไว้ในตอนแรกและดัชนีของคอลัมน์ที่เกี่ยวข้องเพื่อดึงค่าสุดท้าย

รหัส vlookup พร้อมแล้ว แต่คุณยังต้องเพิ่มตัวแปรแถวในแต่ละครั้งในลูป:

i = i + 1

แต่ละครั้งที่ลูปทำงาน มันจะเพิ่มค่าi ขึ้น1 โปรดจำไว้ว่าค่าแถวเริ่มต้นคือ 2 การเพิ่มขึ้นจะเกิดขึ้นทุกครั้งที่มีการวนซ้ำ ใช้ คีย์เวิร์ดloopเพื่อตั้งค่าจุดสิ้นสุดของบล็อกโค้ดนี้

เมื่อรันโค้ดทั้งหมด มันจะเติมข้อมูลทั้งสองคอลัมน์พร้อมผลลัพธ์ โดยขึ้นอยู่กับค่าในตารางต้นฉบับ

ด้านล่างนี้เป็นรหัสอ้างอิงทั้งหมด:

 Sub vlookup_fn1() Dim i As Integer lastrow = Sheets("Sheet2").Range("F3").End(xlDown).Row my_range = Sheets("Sheet2").Range("F3:H" & lastrow) i = 2 Do While Len(Sheets("Sheet2").Cells(i, 1).Value) <> 0 On Error Resume Next Sheets("Sheet2").Cells(i, 2).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 2, False) Sheets("Sheet2").Cells(i, 3).Value = _ Application.WorksheetFunction.VLookup(Sheets("Sheet2") _ .Cells(i, 1).Value, my_range, 3, False) i = i + 1 LoopEnd Sub

4. สร้างปุ่มเพื่อเรียกใช้โค้ด

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

ในกล่องโต้ตอบ ให้เลือกชื่อของรูทีนย่อย และคลิกตกลง

เมื่อคุณต้องการรันโค้ดให้คลิกปุ่มเพื่อดูว่ากรอกข้อมูลอย่างไรทันที

ด้านบนคือวิธีใช้ Excel VBA เพื่อทำให้ฟังก์ชันการค้นหาเป็นแบบอัตโนมัติ หวังว่าบทความนี้จะเป็นประโยชน์กับคุณ