บทช่วยสอน Excel VLOOKUP สำหรับผู้เริ่มต้น
ชาร์ล็อตต์มิลเลอร์
⚡ สรุปอย่างชาญฉลาด
คู่มือการใช้งานฟังก์ชัน VLOOKUP ใน Excel อธิบายวิธีการใช้ฟังก์ชันค้นหาแนวตั้ง (VLOOKUP) ในการค้นหาค่าในคอลัมน์แรกของตารางและส่งคืนค่าที่ตรงกันจากคอลัมน์อื่น คู่มือนี้ครอบคลุมถึงไวยากรณ์ การค้นหาค่าที่ตรงกันทุกประการและค่าที่ใกล้เคียง การค้นหาข้ามชีต ข้อผิดพลาดที่พบบ่อย และฟังก์ชัน XLOOKUP ซึ่งเป็นทางเลือกที่ทันสมัยกว่า
VLOOKUP คืออะไร?
VLOOKUP (ตัว V ย่อมาจาก Vertical) เป็นฟังก์ชันในตัวของ Excel ที่สร้างความสัมพันธ์ระหว่างคอลัมน์ในสเปรดชีต ฟังก์ชันนี้ช่วยให้คุณสามารถค้นหาค่าในคอลัมน์หนึ่งและส่งคืนค่าที่สอดคล้องกันจากอีกคอลัมน์หนึ่งในแถวเดียวกันได้
ไวยากรณ์และอาร์กิวเมนต์ของฟังก์ชัน VLOOKUP
ก่อนที่จะใช้ฟังก์ชัน VLOOKUP เราควรทำความเข้าใจโครงสร้างของสูตรก่อน ฟังก์ชันนี้รับอาร์กิวเมนต์สี่ตัวและมีรูปแบบที่สม่ำเสมอใน Excel ทุกเวอร์ชัน
- lookup_value — ค่าที่คุณต้องการค้นหา (การอ้างอิงเซลล์หรือค่าคงที่)
- table_array — ช่วงของเซลล์ที่ประกอบด้วยคอลัมน์ค้นหาและคอลัมน์ส่งคืน
- col_index_num — หมายเลขคอลัมน์ใน table_array ที่จะใช้ส่งค่ากลับ (1 คือคอลัมน์ซ้ายสุด)
- range_lookup — ค่า FALSE สำหรับการจับคู่ที่ตรงกันทุกประการ ค่า TRUE (หรือละเว้น) สำหรับการจับคู่โดยประมาณบนข้อมูลที่เรียงลำดับแล้ว
สำคัญ: ค่าที่ต้องการค้นหาต้องอยู่ในคอลัมน์ซ้ายสุดของตาราง table_array และฟังก์ชัน VLOOKUP จะค้นหาจากซ้ายไปขวาเท่านั้น
การใช้งาน VLOOKUP
เมื่อคุณต้องการค้นหาข้อมูลเฉพาะในสเปรดชีตขนาดใหญ่ หรือต้องการดึงค่าประเภทเดียวกันซ้ำๆ ฟังก์ชัน VLOOKUP จะช่วยประหยัดเวลาได้อย่างมากเมื่อเทียบกับการกรองข้อมูลด้วยตนเอง
พิจารณา a ตารางเงินเดือนของบริษัท ทีมงานด้านการเงินเป็นผู้ดูแล โดยเริ่มต้นจากข้อมูลที่ทราบแล้ว เช่น ดัชนี และใช้ฟังก์ชัน VLOOKUP เพื่อดึงค่าที่ไม่ทราบออกมา
ตัวอย่างเช่น คุณทราบชื่อพนักงานอยู่แล้ว:
และคุณต้องการดูข้อมูลเงินเดือนของพนักงาน:
ไฟล์สเปรดชีต Excel สำหรับกรณีข้างต้น:
เพื่อหาเงินเดือนพนักงานที่ไม่ทราบค่า เราต้องป้อนข้อมูลพนักงานเข้าไป Code ซึ่งมีอยู่แล้ว
โดยใช้ฟังก์ชัน VLOOKUP จะได้ค่าเงินเดือนของพนักงานคนนั้น Code ปรากฏขึ้นโดยอัตโนมัติ
วิธีใช้ฟังก์ชัน VLOOKUP ใน Excel
ทำตามขั้นตอนต่อไปนี้เพื่อใช้ฟังก์ชัน VLOOKUP ใน Excel:
ขั้นตอนที่ 1) ไปยังเซลล์เป้าหมาย
คลิกที่เซลล์ที่คุณต้องการให้แสดงเงินเดือนของพนักงานที่เลือกไว้ — ในตัวอย่างนี้คือเซลล์ H3
ขั้นตอนที่ 2) ป้อนฟังก์ชัน VLOOKUP =VLOOKUP()
พิมพ์ฟังก์ชันลงในเซลล์ เริ่มต้นด้วยเครื่องหมายเท่ากับ (ซึ่งบอก Excel ว่าตามด้วยสูตร) แล้วตามด้วยคำหลัก VLOOKUP: =VLOOKUP().
วงเล็บจะบรรจุชุดของอาร์กิวเมนต์ (ข้อมูลที่ฟังก์ชันต้องการ)
ฟังก์ชัน VLOOKUP ต้องการอาร์กิวเมนต์สี่ตัว:
ขั้นตอนที่ 3) อาร์กิวเมนต์แรก — ค่าที่ต้องการค้นหา
อาร์กิวเมนต์แรกคือการอ้างอิงเซลล์สำหรับค่าที่คุณต้องการค้นหา ในกรณีนี้คือ พนักงาน Code คือค่าที่ใช้ค้นหา ดังนั้นอาร์กิวเมนต์แรกคือ H2 ซึ่งเป็นเซลล์ที่ Excel ควรจับคู่เนื้อหาด้วย
ขั้นตอนที่ 4) อาร์กิวเมนต์ที่สอง — อาร์เรย์ตาราง
หมายถึงกลุ่มของค่าที่จะค้นหา ซึ่งใน Excel เรียกว่า กลุ่มของค่าที่จะค้นหา (search block) อาร์เรย์ตาราง หรือตารางค้นหา ในตัวอย่างของเรา ตารางค้นหาจะทำงาน จาก B2 ถึง E25.
หมายเหตุ: คอลัมน์ที่ใช้ค้นหาจะต้องเป็นคอลัมน์ซ้ายสุดของอาร์เรย์ตารางของคุณ
ขั้นตอนที่ 5) อาร์กิวเมนต์ที่สาม — col_index_num
คำสั่งนี้บอกฟังก์ชัน VLOOKUP ว่าคอลัมน์ใดในอาร์เรย์ของตารางเก็บค่าที่ส่งคืน เนื่องจากเงินเดือนของพนักงานอยู่ในคอลัมน์ที่สี่ ดังนั้นดัชนีคอลัมน์จึงเป็น 4
ขั้นตอนที่ 6) ข้อโต้แย้งที่สี่ — ตรงกันเป๊ะหรือใกล้เคียง
อาร์กิวเมนต์สุดท้ายคือแฟล็กการค้นหาช่วง มันควบคุมว่าฟังก์ชัน VLOOKUP จะคืนค่าที่ตรงกันเป๊ะหรือค่าโดยประมาณ ในที่นี้เราต้องการค่าที่ตรงกันเป๊ะ (FALSE)
- FALSE — ตรงกันทุกประการ
- TRUE — ตรงกันโดยประมาณ
ขั้นตอนที่ 7) กด Enter
กด Enter เพื่อกรอกสูตรให้สมบูรณ์ คุณจะเห็นข้อผิดพลาดในตอนแรกเนื่องจากไม่มีพนักงาน Code ยังไม่ได้ลงทะเบียนในครึ่งปีหลัง
เมื่อคุณป้อนข้อมูลพนักงานที่ถูกต้องแล้ว Code ในเซลล์ H2 จะแสดงเงินเดือนของพนักงานที่เกี่ยวข้อง
โดยสรุป สูตรนี้บอก Excel ว่าค่าที่ทราบแล้วนั้นอยู่ในคอลัมน์ซ้ายสุดของข้อมูล (พนักงาน) Codeจากนั้นฟังก์ชัน VLOOKUP จะสแกนตารางและส่งคืนค่าในคอลัมน์ที่สี่ของแถวที่ตรงกัน ซึ่งก็คือเงินเดือนของพนักงาน
ตัวอย่างนี้กล่าวถึงการจับคู่ที่ตรงกันทุกประการ (คำหลัก FALSE) ส่วนถัดไปจะอธิบายการจับคู่โดยประมาณ
VLOOKUP สำหรับการจับคู่โดยประมาณ (คำหลัก TRUE เป็นพารามิเตอร์สุดท้าย)
ลองพิจารณาสถานการณ์ที่ตารางคำนวณส่วนลดสำหรับลูกค้าที่ไม่ได้ซื้อสินค้าจำนวนหลักสิบหรือหลักร้อยชิ้นพอดี
ดังแสดงในตารางด้านล่าง บริษัทแห่งหนึ่งให้ส่วนลดสำหรับสินค้าจำนวนตั้งแต่ 1 ถึง 10,000 ชิ้น:
ลูกค้าส่วนใหญ่ไม่ได้ซื้อสินค้าจำนวน 100 หรือ 1,000 ชิ้นเป๊ะๆ โหมดการค้นหาแบบประมาณค่าจะช่วยให้ฟังก์ชัน VLOOKUP ค้นหาค่าที่ใกล้เคียงที่สุดจากค่าที่น้อยกว่า แทนที่จะยืนยันตัวเลขที่แน่นอน ขั้นตอน:
ขั้นตอน 1) คลิกที่เซลล์ที่จะใช้ฟังก์ชัน VLOOKUP — เซลล์อ้างอิง I2
ขั้นตอน 2) ป้อนสูตร `=VLOOKUP()` ลงในเซลล์ แล้วใส่ค่าตัวแปรภายในวงเล็บ
ขั้นตอนที่ 3) ข้อโต้แย้งที่ 1: ป้อนรหัสอ้างอิงเซลล์ที่ต้องการนำไปเปรียบเทียบกับค่าในตารางค้นหา
ขั้นตอนที่ 4) ข้อโต้แย้งที่ 2: เลือกตารางค้นหา — ในที่นี้คือคอลัมน์จำนวนและส่วนลด
ขั้นตอนที่ 5) ข้อโต้แย้งที่ 3: ป้อนดัชนีคอลัมน์ในตารางค้นหาที่ต้องการค้นหาค่าที่ตรงกัน
ขั้นตอนที่ 6) ข้อโต้แย้งที่ 4: ตั้งค่าอาร์กิวเมนต์สุดท้ายเป็น TRUE สำหรับการจับคู่โดยประมาณ
ขั้นตอน 7) กด Enter สูตรจะถูกนำไปใช้กับเซลล์นั้นแล้ว เมื่อคุณพิมพ์จำนวนใดๆ Excel จะแสดงช่วงส่วนลดตามค่าที่ใกล้เคียงที่สุด
หมายเหตุ: หากคุณเว้นช่องอาร์กิวเมนต์ที่สี่ว่างไว้ Excel จะตั้งค่าเริ่มต้นเป็น TRUE (การจับคู่โดยประมาณ) สำหรับการจับคู่โดยประมาณ คอลัมน์ที่ใช้ค้นหาจะต้องเรียงลำดับจากน้อยไปมาก
ฟังก์ชัน Vlookup ใช้ระหว่าง 2 แผ่นงานที่แตกต่างกันในสมุดงานเดียวกัน
ทีนี้ลองพิจารณาเวิร์กบุ๊กที่มีสองแผ่น แผ่นที่ 1 แสดงรายชื่อพนักงาน Codeชื่อ และตำแหน่ง; แผ่นที่ 2 แสดงรายชื่อพนักงาน Code และเงินเดือนพนักงาน
แผ่นที่ 1:
แผ่นที่ 2:
วัตถุประสงค์คือการรวบรวมข้อมูลทั้งหมดไว้ในแผ่นงานที่ 1 ดังแสดงด้านล่าง:
ฟังก์ชัน VLOOKUP สามารถรวบรวมข้อมูลได้ เช่น ข้อมูลพนักงาน Codeชื่อและเงินเดือนจะปรากฏอยู่ด้วยกันในเอกสารแผ่นเดียวกัน
เราเริ่มต้นที่แผ่นงานที่ 2 เพราะมีข้อมูลสำคัญสองอย่าง คือ คอลัมน์เงินเดือนพนักงาน และคอลัมน์อื่นๆ ดัชนีคอลัมน์คือ 2.
เราต้องการหาอัตราเงินเดือนที่เหมาะสมกับพนักงานแต่ละคน Code.
ข้อมูลจะอยู่ในช่วงตั้งแต่เซลล์ A2 ถึง B25 ซึ่งเป็นลำดับชั้นของตารางของเรา
ขั้นตอน 1) สลับไปที่แผ่นงานที่ 1 แล้วป้อนหัวข้อตามที่แสดง
ขั้นตอน 2) คลิกที่เซลล์ถัดจาก "เงินเดือนพนักงาน" (เซลล์ F3) ซึ่งเป็นตำแหน่งที่จะใส่สูตร VLOOKUP
ป้อนฟังก์ชัน VLOOKUP: =VLOOKUP()
ขั้นตอนที่ 3) ข้อโต้แย้งที่ 1: กด F2 — ช่องที่มีข้อมูลพนักงานอยู่ Code เพื่อให้ตรงกับข้อมูลในตารางค้นหา
ขั้นตอนที่ 4) ข้อโต้แย้งที่ 2: ตารางค้นหาจะอยู่ในชีตอื่น ดังนั้นให้ระบุชื่อชีตเพื่ออ้างอิง: แผ่นที่ 2!A2:B25.
ขั้นตอนที่ 5) ข้อโต้แย้งที่ 3: ป้อนดัชนีคอลัมน์ภายในตารางค้นหาที่เก็บค่าส่งคืน
ขั้นตอนที่ 6) ข้อโต้แย้งที่ 4: ใช้ค่า FALSE สำหรับการจับคู่ที่ตรงกันทุกประการ เนื่องจากเราต้องการเงินเดือนที่ตรงกับพนักงานแต่ละคนอย่างแม่นยำ Code.
ขั้นตอน 7) กด Enter เมื่อคุณป้อนข้อมูลพนักงาน Codeโดยเซลล์นี้จะแสดงเงินเดือนที่ดึงมาจากชีต 2
ข้อผิดพลาดทั่วไปของการใช้ฟังก์ชัน VLOOKUP และวิธีแก้ไข
แม้แต่ผู้ใช้ที่มีประสบการณ์ก็ยังเจอปัญหาข้อผิดพลาดในการใช้ VLOOKUP ข้อผิดพลาดที่พบบ่อยที่สุดและวิธีแก้ไขอย่างรวดเร็วมีดังนี้:
- # N / A — ฟังก์ชัน VLOOKUP หาค่าที่ต้องการค้นหาไม่พบ โปรดตรวจสอบช่องว่างเพิ่มเติม ชนิดข้อมูลที่ไม่ตรงกัน (เช่น ตัวเลขถูกจัดเก็บเป็นข้อความ) หรือตรวจสอบว่าค่าดังกล่าวมีอยู่ในคอลัมน์แรกของตาราง table_array จริงหรือไม่
- #REF! — col_index_num มีค่ามากกว่าจำนวนคอลัมน์ใน table_array ให้ลดค่าดัชนีคอลัมน์หรือขยายช่วงค่าดัชนี
- #VALUE! — ค่า col_index_num น้อยกว่า 1 หรือค่าอาร์กิวเมนต์ไม่ถูกต้อง โปรดตรวจสอบไวยากรณ์ของสูตร
- ผลลัพธ์ไม่ถูกต้อง — หากอาร์กิวเมนต์ที่สี่เป็น TRUE หรือละเว้น แต่คอลัมน์ที่ใช้ค้นหายังไม่ได้เรียงลำดับ ให้เปลี่ยนเป็น FALSE หรือเรียงลำดับคอลัมน์จากน้อยไปมาก
- ข้อมูลอ้างอิงที่ถูกล็อก — เมื่อคัดลอกสูตรลงมา ให้ใช้การอ้างอิงแบบสัมบูรณ์ (ตัวอย่างเช่น $B$2:$E$25) เพื่อป้องกันไม่ให้ตารางอาร์เรย์คลาดเคลื่อน
VLOOKUP กับ XLOOKUP: ควรใช้ตัวไหนดี?
Microsoft ได้มีการนำ XLOOKUP มาใช้ใน Microsoft ฟังก์ชัน 365 และ Excel 2021 เป็นฟังก์ชันทดแทน VLOOKUP ที่ทันสมัยกว่า โดยได้แก้ไขข้อจำกัดหลายประการของ VLOOKUP และปัจจุบันเป็นตัวเลือกที่แนะนำในเวอร์ชันที่รองรับ
| คุณสมบัติ (Feature) | วีลุคอัพ | เอ็กซ์ลุคอัพ |
|---|---|---|
| ค้นหาทิศทาง | จากซ้ายไปขวาเท่านั้น | ทิศทางใดก็ได้ (ซ้าย ขวา บน ล่าง) |
| ประเภทการจับคู่เริ่มต้น | โดยประมาณ (จริง) | แน่นอน |
| การจัดการกรณีไม่พบ | การส่งคืนสินค้า #N/A | อาร์กิวเมนต์ if_not_found ในตัว |
| ดัชนีคอลัมน์ | หมายเลขที่กำหนดไว้ตายตัว | อ้างอิงช่วงคอลัมน์ส่งคืน |
| ความพร้อมที่จะให้บริการ | ทุกเวอร์ชันของ Excel | Microsoft 365, Excel 2021, Excel สำหรับเว็บ |
เมื่อใดควรเลือกใช้ VLOOKUP: ไฟล์เวิร์กบุ๊กนี้ต้องสามารถใช้งานได้ใน Excel 2019 หรือเวอร์ชันก่อนหน้า มิฉะนั้นคุณจะต้องใช้สูตรเดิมที่มีอยู่ ควรเลือกใช้ XLOOKUP เมื่อใด: คุณกำลังสร้างเวิร์กบุ๊กใหม่ใน Excel เวอร์ชันใหม่ และต้องการฟังก์ชันค้นหาทางซ้าย การจัดการข้อผิดพลาดที่สะอาดกว่า และการจับคู่ที่ตรงกันทุกประการเป็นค่าเริ่มต้น เรียนรู้เพิ่มเติมเกี่ยวกับฟังก์ชันค้นหาได้ใน... บทเรียน Excel ชุด.
สรุป
สถานการณ์ทั้งสามข้างต้นอธิบายวิธีการทำงานของ VLOOKUP สำหรับการค้นหาแบบตรงกันทุกประการ การค้นหาแบบใกล้เคียง และการอ้างอิงข้ามชีต ฝึกฝนกับชุดข้อมูลของคุณเองเพื่อเพิ่มความชำนาญ VLOOKUP ยังคงเป็นคุณสมบัติที่สำคัญใน... MS-Excel สำหรับการจัดการข้อมูลอย่างมีประสิทธิภาพ XLOOKUP ได้ขยายชุดเครื่องมือดังกล่าวใน Excel เวอร์ชันใหม่
