ฟังก์ชัน IFERROR ใน Excel สูตรและการใช้งาน
ค้นพบฟังก์ชัน IFERROR ใน Excel เพื่อจัดการข้อผิดพลาดอย่างมีประสิทธิภาพและเรียนรู้วิธีใช้งานเบื้องต้นที่สำคัญ
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 เพื่อทำให้ฟังก์ชันการค้นหาเป็นแบบอัตโนมัติ หวังว่าบทความนี้จะเป็นประโยชน์กับคุณ
ค้นพบฟังก์ชัน IFERROR ใน Excel เพื่อจัดการข้อผิดพลาดอย่างมีประสิทธิภาพและเรียนรู้วิธีใช้งานเบื้องต้นที่สำคัญ
ฟังก์ชัน SUBTOTAL ใน Excel คืออะไร? สูตร SUBTOTAL ใน Excel คืออะไร มาหาคำตอบกับ LuckyTemplates.com กันเถอะ!
การอ้างอิงเซลล์ใน Excel มีสองประเภท: แบบสัมพัทธ์และแบบสัมบูรณ์ ซึ่งมีความสำคัญต่อการจัดการข้อมูลในสเปรดชีต
ฟังก์ชัน SUM ใน Excel เป็นเครื่องมือที่มีประโยชน์ในการคำนวณผลรวมใน Excel มาดูกันว่าเราสามารถใช้ฟังก์ชันนี้อย่างไรให้มีประสิทธิภาพสูงสุด
เรียนรู้การใช้ฟังก์ชัน Min และ Max ใน Excel เพื่อค้นหาค่าที่น้อยที่สุดและค่าที่ใหญ่ที่สุดในตารางข้อมูลอย่างมีประสิทธิภาพ
เรียนรู้เกี่ยวกับฟังก์ชัน AVERAGEIFS ใน Excel เพื่อคำนวณค่าเฉลี่ยจากหลายเงื่อนไข ข้อมูลเชิงลึกและตัวอย่างเพื่อให้คุณเข้าใจได้ง่ายขึ้น
ฟังก์ชันการนับใน Excel คืออะไร? วิธีการใช้ฟังก์ชันนับใน Excel? มาหาคำตอบกับ LuckyTemplates.com กันเถอะ!
เรียนรู้วิธีใช้ฟังก์ชัน RANK ใน Excel เพื่อจัดอันดับข้อมูลอย่างมีประสิทธิภาพ สามารถจัดอันดับจากน้อยไปมากหรือจากมากไปน้อยได้ตามต้องการ
เรียนรู้วิธีใช้ฟังก์ชัน HLOOKUP ใน Excel อย่างมีประสิทธิภาพและเคล็ดลับการใช้งานที่คุณไม่ควรพลาด!
ฟังก์ชัน SORT จะปล่อยให้ตารางต้นฉบับไม่เสียหาย และสร้างสำเนาที่เรียงลำดับแล้วในสเปรดชีตแทน