การใช้ฟังก์ชัน VLOOKUP และตัวอย่างเฉพาะ

ฟังก์ชัน VLOOKUP ใน Excelมีประโยชน์อย่างยิ่งในการค้นหาข้อมูล ต่อไปนี้คือสิ่งที่คุณต้องรู้เกี่ยวกับการใช้ฟังก์ชัน VLOOKUP ใน Excel

คุณต้องการตรวจสอบค่าเฉพาะในสเปรดชีต Excel และค้นหาข้อมูลเกี่ยวกับค่านั้น การเรียกใช้ VLOOKUP ช่วยให้คุณประหยัดความพยายามได้มากในกรณีนี้ นี่เป็นหนึ่งในวิธีที่ดีที่สุดในการดำเนินการค้นหาแนวตั้งใน Microsoft Excel บทความนี้จะบอกความหมาย การใช้งาน และตัวอย่างประกอบของฟังก์ชัน VLOOKUP ใน Microsoft Excel

• นี่เป็นฟังก์ชันพื้นฐานที่สุดใน Excel ที่คุณต้องเข้าใจ

วิธีใช้ฟังก์ชัน VLOOKUP

• ฟังก์ชัน VLOOKUP คืออะไร?
• สูตรฟังก์ชัน VLOOKUP ใน Excel 2016, 2013, 2010, 2007, 2003
• วิดีโอสอนการใช้ฟังก์ชัน VLOOKUP
• ตัวอย่างที่ 1: ใช้ฟังก์ชัน VLOOKUP เพื่อประเมินการให้คะแนนของนักเรียนและเจ้าหน้าที่:
• ตัวอย่างที่ 2: ใช้ VLOOKUP สำหรับการตรวจจับแบบสัมบูรณ์เพื่อรับข้อมูล
• ตัวอย่างที่ 3: ใช้ VLOOKUP เพื่อแยกข้อมูล
• ตัวอย่างที่ 4: ใช้ฟังก์ชัน VLOOKUP บนแผ่นงาน Excel 2 แผ่น
• จะใช้ฟังก์ชัน VLOOKUP เพื่ออะไร?
• สิ่งที่ต้องจำเกี่ยวกับฟังก์ชัน VLOOKUP

ฟังก์ชัน VLOOKUP คืออะไร?

ฟังก์ชัน VLOOKUP เป็นฟังก์ชันค้นหาข้อมูลใน Excel ฉันคิดว่าคุณมีพื้นฐานใน Excel และสามารถใช้ฟังก์ชันพื้นฐาน เช่น SUM, AVERAGE และ TODAY ได้ ฟังก์ชันทั่วไปอย่างหนึ่งของ VLOOKUP คือฟังก์ชันข้อมูล ซึ่งหมายความว่าฟังก์ชันดังกล่าวทำงานบนตารางฐานข้อมูล หรือพูดง่ายๆ ก็คือรายการของรายการต่างๆ รายการมีความหลากหลายมาก คุณสามารถสร้างตารางเกี่ยวกับบุคลากรของบริษัท หมวดหมู่ผลิตภัณฑ์ รายชื่อลูกค้า หรือสิ่งอื่นใดได้ และด้านล่างคือรายชื่อผลิตภัณฑ์ของบริษัท A ที่จำหน่ายในตลาด:

ตารางฐานข้อมูลมักจะมีตัวระบุสำหรับแต่ละผลิตภัณฑ์ ในตารางข้อมูลด้านบน เครื่องหมายระบุพิเศษคือคอลัมน์ "รหัสรายการ" หมายเหตุ: เพื่อให้สามารถใช้ฟังก์ชัน VLOOKUP ได้ ตารางข้อมูลจะต้องมีคอลัมน์ที่มีเครื่องหมายระบุ เช่น รหัสหรือ ID และต้องเป็นคอลัมน์แรกเหมือนกับตารางด้านบน

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

ในด้านลบ ฟังก์ชัน VLOOKUP จะถูกจำกัดและมีค่าเริ่มต้นที่เป็นอันตราย ซึ่งแตกต่างจาก INDEX และ MATCH (หรือ XLOOKUP) ฟังก์ชัน VLOOKUP ต้องการตารางที่สมบูรณ์พร้อมค่าการค้นหาในคอลัมน์แรก ซึ่งทำให้การใช้ฟังก์ชัน VLOOKUP กับหลายเกณฑ์ทำได้ยาก นอกจากนี้ ลักษณะการจับคู่เริ่มต้นของ VLOOKUP ยังทำให้ได้ผลลัพธ์ที่ไม่ถูกต้องได้ง่าย แต่ไม่ต้องกังวล! กุญแจสำคัญในการใช้ฟังก์ชัน VLOOKUP ให้ประสบความสำเร็จคือการเรียนรู้พื้นฐาน

VLOOKUP มีอาร์กิวเมนต์ 4 รายการ ได้แก่ lookup_value, table_array, column_index_num และ range_lookup Lookup_value คือค่าที่จะค้นหา และ table_array คือช่วงข้อมูลแนวตั้งที่จะค้นหาภายใน คอลัมน์แรกของ table_array จะต้องมีค่าการค้นหาเพื่อค้นหา อาร์กิวเมนต์ column_index_num คือหมายเลขคอลัมน์ของค่าที่จะดึงข้อมูล โดยคอลัมน์แรกของ table_array คือคอลัมน์ 1

สุดท้าย range_lookup จะควบคุมพฤติกรรมการค้นหาคู่ที่ตรงกัน ถ้า range_lookup เป็น TRUE ฟังก์ชัน VLOOKUP จะดำเนินการจับคู่โดยประมาณ ถ้า range_lookup เป็น FALSE ฟังก์ชัน VLOOKUP จะดำเนินการจับคู่แบบตรงทั้งหมด ที่สำคัญ range_lookup เป็นทางเลือกและมีค่าเริ่มต้นเป็น TRUE ดังนั้น VLOOKUP จะดำเนินการจับคู่โดยประมาณตามค่าเริ่มต้น

สูตรของฟังก์ชัน VLOOKUP ใน Excel

ฟังก์ชัน VLOOKUP ใน Excel มีสูตรดังต่อไปนี้:

=VLOOKUP(lookup_value,table_array,col_index_num ,[range_lookup] )

ในนั้น:

• VLOOKUP:คือชื่อฟังก์ชัน
• ต้องระบุพารามิเตอร์ที่เป็นตัวหนา
• lookup_value:ค่าที่ใช้สำหรับการค้นหา
• table_array:ตารางที่มีค่าที่จะค้นหา ให้ใส่ค่าสัมบูรณ์โดยมีเครื่องหมาย $ อยู่ข้างหน้า เช่น $A$3:$E$40
• col_index_num:ลำดับของคอลัมน์ที่มีค่าการค้นหาบน table_array ตัวอย่างเช่น ในตาราง $A$3:$E$40 คอลัมน์ B มีค่าที่จะค้นหา จากนั้น col_index_num ที่นี่จะเป็น 2 ตาราง $C$3:$F$40 คอลัมน์ E มีค่าการค้นหา จากนั้น col_index_num ที่นี่คือ 3
• range_lookup: เป็นช่วงการค้นหา TRUE เทียบเท่ากับ 1 (การค้นหาแบบสัมพัทธ์) FALSE เทียบเท่ากับ 0 (การค้นหาแบบสัมบูรณ์) พารามิเตอร์นี้ไม่จำเป็นเสมอไปในสูตร

การค้นหาแบบสัมพัทธ์สามารถใช้ได้เฉพาะเมื่อมีการจัดเรียงค่าที่จะค้นหาใน table_array ตามลำดับ (จากน้อยไปมากหรือจากมากไปน้อยหรือตามตัวอักษร) ด้วยตารางดังกล่าว คุณสามารถใช้การค้นหาแบบสัมพันธ์ได้ ซึ่งคล้ายกับการใช้ฟังก์ชัน IFแบบไม่มีที่สิ้นสุด สำหรับค่าที่ไม่สามารถค้นหาหรือไม่ได้เรียงลำดับ ให้ใช้การค้นหาแบบสัมบูรณ์เพื่อค้นหาค่าที่แน่นอน

วิดีโอสอนการใช้ฟังก์ชัน VLOOKUP

ตัวอย่างที่ 1: ฟังก์ชัน VLOOKUP เพื่อประเมินการให้คะแนนของนักศึกษาและเจ้าหน้าที่

สมมติว่าคุณมีใบรับรองผลการเรียนของนักเรียนดังนี้:

และตารางระเบียบการจัดอันดับมีดังนี้

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

ใน Excel ทั้ง 2 ตารางนี้จะแสดงดังนี้:

ที่นี่ ค่าที่จะตรวจพบอยู่ในคอลัมน์ C โดยเริ่มจากบรรทัดที่ 6 ช่วงการค้นหาคือ $A$18:$B$21 ลำดับของคอลัมน์ที่มีค่าที่ตรวจพบคือ 2

ในเซลล์ D6 ให้ป้อนสูตร: =VLOOKUP($C6,$A$18:$B$21,2,1) นี่เป็นสูตรการค้นหาแบบสัมพันธ์ คุณสามารถดำเนินการค้นหาแบบสัมบูรณ์ได้หากต้องการ (หรือเนื่องจากไม่ได้เรียงลำดับอันดับ) โดยเพิ่ม 0 ลงในสูตรดังนี้: =VLOOKUP($C6 ,$A$18:$B$21,2, 0) กดปุ่มตกลง.

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

ในขณะนั้นเรามีผลการใช้ฟังก์ชัน VLOOKUP จำแนกผลการเรียนของนักเรียนดังนี้

คุณสงสัยหรือไม่ว่าทำไมต้องใช้ $ ก่อน C6? $ จะคงคอลัมน์ C ไว้คงที่ โดยเปลี่ยนเฉพาะแถวเมื่อคุณลากสูตรลงในตาราง นอกจากนี้ยังมี $A$18:$B$21 เพื่อช่วยแก้ไขตารางกฎการจัดอันดับ ทำให้ไม่มีการเปลี่ยนแปลงเมื่อคุณลากสูตร

ตัวอย่างที่ 2: ฟังก์ชัน VLOOKUP ค้นหาเพื่อรับข้อมูลโดยสิ้นเชิง

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

สมมติว่าคุณมีตารางพนักงานและบ้านเกิดของพนักงานดังนี้:

ตอนนี้คุณต้องการกรอกข้อมูลบ้านเกิดของพนักงาน ในเซลล์ D4 ให้ป้อนสูตรการค้นหาแบบสัมบูรณ์ดังนี้: =Vlookup($A4,$A$16:$C$25,2,0)

จากนั้นกด Enter คลิกสี่เหลี่ยมเล็กๆ ที่ปรากฏตรงมุมของเซลล์ D4 แล้วลากลงมาทั่วตารางเพื่อคัดลอกสูตรไปยังพนักงานคนอื่นๆ

หากต้องการกรอกข้อมูลคุณสมบัติสำหรับพนักงาน ในเซลล์ E4 ให้ป้อนสูตรการค้นหาแบบสัมบูรณ์: =VLOOKUP($A4,$A$16:$C$25,3,0)

คุณกด Enter ต่อไปแล้วเลื่อนลงเพื่อคัดลอกสูตรไปยังพนักงานที่เหลือ เราจะได้ผลลัพธ์ดังนี้:

ตัวอย่างที่ 3: ใช้ VLOOKUP เพื่อแยกข้อมูล

จากชุดข้อมูลตัวอย่างที่ 2 ต่อไป เราจะพบบ้านเกิดของพนักงาน 3 คน ได้แก่ Nguyen Hoang Anh, Tran Van Anh และ Nguyen Quang Vinh ฉันได้แตกมันออกมาเป็นตารางใหม่ F15:G18 แล้ว

ฉันจะค้นหาในตาราง A3:E13 หลังจากกรอกข้อมูลบ้านเกิดและคุณสมบัติของฉันแล้ว ตอนนี้ให้ป้อนสูตรการค้นหาแบบสัมบูรณ์ต่อไปนี้ลงในเซลล์ G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > กด Enter

คัดลอกสูตรสำหรับพนักงาน 2 คนที่เหลือ เราจะได้ผลลัพธ์ดังนี้:

โปรดทราบว่าในตัวอย่างนี้ ค่าการตรวจจับอยู่ในคอลัมน์ B ดังนั้นพื้นที่การตรวจจับจึงคำนวณจากคอลัมน์ B ไม่ใช่จากคอลัมน์ A

ตัวอย่างที่ 4: ใช้ฟังก์ชัน VLOOKUP บนแผ่นงาน Excel 2 แผ่น

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

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

หากต้องการค้นหาข้อมูลเกี่ยวกับ "คุณสมบัติ" ของพนักงาน ให้ป้อนสูตร: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) ในเซลล์ C4 ของชีต QTM1

ในนั้น:

• B4 คือคอลัมน์ที่มีค่าที่ใช้ในการตรวจจับ
• คิวทีเอ็ม! คือชื่อของชีตที่มีตารางที่มีค่าที่จะตรวจพบ หลังชื่อชีต ให้เพิ่ม !
• $B$3:$E$13 คือตารางที่มีค่าการค้นหาและแผ่นงานที่มีตาราง (QTM)
• 4 คือหมายเลขซีเรียลของคอลัมน์ "คุณสมบัติ" ซึ่งคำนวณจากคอลัมน์ "ชื่อเต็ม" บนชีต QTM
• 0 คือการตรวจจับแบบสัมบูรณ์

กด Enter จากนั้นคัดลอกสูตรสำหรับพนักงานที่เหลือทั้งหมดในตาราง เราจะได้ผลลัพธ์ดังนี้

หากต้องการค้นหาข้อมูล "ตำแหน่ง" ของพนักงาน ในเซลล์ D4 ของชีต QTM1 ให้ป้อนสูตร: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0) ให้กด Enter

เมื่อคัดลอกสูตรสำหรับพนักงานที่เหลือ เราจะได้สิ่งต่อไปนี้:

จะใช้ฟังก์ชัน VLOOKUP เพื่ออะไร?

ก่อนอื่น เราต้องค้นหาคำตอบที่ถูกต้องสำหรับคำถาม "เราใช้ฟังก์ชัน VLOOKUP เพื่ออะไร"

ฟังก์ชัน VLOOKUP ใช้เพื่อรองรับการค้นหาข้อมูลในเขตข้อมูลหรือรายการตามตัวระบุที่มีอยู่

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

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

สิ่งที่ต้องจำเกี่ยวกับฟังก์ชัน VLOOKUP

ต่อไปนี้คือรายการสิ่งสำคัญที่ต้องจำเกี่ยวกับฟังก์ชัน VLOOKUP ใน Excel:

• เมื่อละเว้น range_lookup VLOOKUP จะอนุญาตการจับคู่ที่ไม่ตรงทั้งหมด แต่จะยังคงใช้การจับคู่ที่ตรงทั้งหมด หากมี
• ข้อจำกัดที่ใหญ่ที่สุดของฟังก์ชันคือ ฟังก์ชันจะใช้ค่าทางด้านขวาเสมอ ฟังก์ชันจะดึงข้อมูลจากคอลัมน์ทางด้านขวาของคอลัมน์แรกในตาราง
• หากคอลัมน์การค้นหามีค่าซ้ำกัน ฟังก์ชัน VLOOKUP จะจับคู่เฉพาะค่าแรกเท่านั้น
• ฟังก์ชันไม่คำนึงถึงขนาดตัวพิมพ์
• สมมติว่ามีสูตร VLOOKUP อยู่ในเวิร์กชีต ในกรณีนั้นสูตรอาจพังหากคุณแทรกคอลัมน์ลงในตารางเนื่องจากค่าดัชนีคอลัมน์เป็นแบบฮาร์ดโค้ดซึ่งจะไม่เปลี่ยนแปลงโดยอัตโนมัติเมื่อแทรกหรือลบคอลัมน์
• VLOOKUP อนุญาตให้ใช้อักขระตัวแทน เช่น เครื่องหมายดอกจัน (*) หรือเครื่องหมายคำถาม (?)
• สมมติว่าในตารางที่คุณกำลังทำงานกับฟังก์ชันที่มีตัวเลขที่ป้อนเป็นข้อความ หากคุณเพียงได้รับตัวเลขเป็นข้อความจากคอลัมน์ในตารางก็ไม่สำคัญ แต่ถ้าคอลัมน์แรกของตารางมีตัวเลขที่ป้อนเป็นข้อความ ระบบจะ#N/A! จะแสดงหากค่าการค้นหาไม่ใช่ข้อความด้วย
• ข้อผิดพลาด#ไม่มี! เกิดขึ้นหากฟังก์ชัน VLOOKUP ไม่พบรายการที่ตรงกันกับ lookup_value ที่ให้มา
• เกิดข้อผิดพลาด#REF! เกิดขึ้นหาก:
  • อาร์กิวเมนต์ col_index_num มากกว่าจำนวนคอลัมน์ใน table_array ที่ให้มา
  • สูตรพยายามอ้างอิงเซลล์ที่ไม่มีอยู่
• ข้อผิดพลาด#VALUE! เกิดขึ้นหาก:
  • อาร์กิวเมนต์ col_index_num น้อยกว่า 1 หรือไม่ได้รับการยอมรับว่าเป็นค่าตัวเลข
  • อาร์กิวเมนต์ range_lookup ไม่ได้รับการยอมรับว่าเป็นค่าตรรกะค่าใดค่าหนึ่ง TRUE หรือ FALSE

ความแตกต่างที่สำคัญระหว่างฟังก์ชัน VLOOKUP และ XLOOKUP ใน Excel

ย -ใช่

ยังไม่มีข้อความ

ดูเพิ่มเติม:

• วิธีใช้ฟังก์ชัน IF ใน Excel
• วิธีใช้ฟังก์ชัน AVERAGEIF ใน Excel

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

ข้อผิดพลาดทั่วไปเมื่อใช้ฟังก์ชัน VLOOKUP ใน Excel

ค่าการค้นหาอยู่ในคอลัมน์ที่ไม่ถูกต้อง

ข้อผิดพลาดทั่วไปประการหนึ่งที่คุณจะพบเมื่อใช้สูตร VLOOKUP คือฟังก์ชันจะส่งกลับเฉพาะค่า #N/A ข้อผิดพลาดนี้เกิดขึ้นเมื่อไม่พบค่าการค้นหาที่คุณขอให้ VLOOKUP ค้นหา

ในบางกรณี คุณสามารถใช้ฟังก์ชัน XLOOKUP แทน VLOOKUP ได้ อย่างไรก็ตาม ข้อผิดพลาดนี้สามารถแก้ไขได้อย่างง่ายดายในฟังก์ชัน VLOOKUP เท่านั้น

วิธีแก้ไขปัญหา:

สาเหตุที่ VLOOKUP ไม่พบค่าที่ต้องการอาจเป็นเพราะค่าไม่อยู่ในตาราง อย่างไรก็ตาม หากส่งกลับค่า #N/A ทั้งหมด ค่าการค้นหาจะเป็นข้อความ เป็นไปได้มากว่าสามารถอ่านจากซ้ายไปขวาเท่านั้น

เมื่อสร้างตารางใน Excel สำหรับ VLOOKUP ตรวจสอบให้แน่ใจว่าได้วางค่าการค้นหาทางด้านซ้ายของค่าที่คุณต้องการส่งกลับ วิธีที่ง่ายที่สุดในการทำเช่นนี้คือวางไว้ในคอลัมน์แรกของตาราง

ตอนนี้สูตรจะส่งกลับค่า:

โดยรวมแล้ว VLOOKUP เป็นวิธีที่ยอดเยี่ยมในการค้นหาข้อมูลได้เร็วขึ้นใน Microsoft Excel แม้ว่าแบบสอบถาม VLOOKUP จะทำงานในแนวตั้งบนคอลัมน์และมีข้อจำกัดอื่นๆ บางประการ แต่ Microsoft จะอัปเดตฟีเจอร์การค้นหาของ Excel อย่างต่อเนื่องเพื่อขยายการบังคับใช้ ตัวอย่างเช่น HLOOKUP, XLOOKUP... สามารถช่วยคุณค้นหาข้อมูลในทิศทางต่างๆ ได้ LuckyTemplates.com จะยังคงให้ข้อมูลแก่คุณเกี่ยวกับวิธีใช้ฟังก์ชันนี้ในบทความต่อไปนี้