01 จาก 03
ค้นหาข้อมูลที่ใกล้เคียงกับข้อมูลโดยใช้ VLOOKUP ของ Excel
วิธีการทำงาน VLOOKUP Function
ฟังก์ชัน VLOOKUP ของ Excel ซึ่งหมายถึงการ ค้นหาในแนวตั้ง สามารถใช้เพื่อค้นหาข้อมูลเฉพาะที่อยู่ในตารางข้อมูลหรือฐานข้อมูล
VLOOKUP จะส่งกลับข้อมูลเพียงอย่างเดียวเป็นข้อมูลที่ส่งออก วิธีการนี้ไม่เป็น:
- คุณระบุชื่อหรือ lookup_value ที่บอก VLOOKUP ซึ่งแถวหรือ ระเบียน ของตารางข้อมูลเพื่อค้นหาข้อมูลที่ต้องการ
- คุณระบุหมายเลข คอลัมน์ หรือที่เรียกว่า col_index_num - ของข้อมูลที่คุณต้องการ
- ฟังก์ชันจะ ค้นหา lookup_value ในคอลัมน์แรกของตารางข้อมูล
- VLOOKUP จะค้นหาและส่งกลับข้อมูลที่คุณต้องการจากฟิลด์อื่นของระเบียนเดียวกันโดยใช้หมายเลขคอลัมน์ที่ให้มา
การเรียงลำดับข้อมูลก่อน
แม้ว่าจะไม่จำเป็นเสมอไป แต่ก็มักจะดีที่สุดในการ จัดเรียงช่วงของข้อมูล ที่ VLOOKUP กำลังค้นหาโดย เรียงจากน้อยไปหาหนึ่ง โดยใช้คอลัมน์แรกของช่วงสำหรับคีย์เรียงลำดับ
ถ้าไม่มีการจัดเรียงข้อมูล VLOOKUP อาจส่งคืนผลลัพธ์ที่ไม่ถูกต้อง
ไวยากรณ์ของฟังก์ชัน VLOOKUP และอาร์กิวเมนต์
ไวยากรณ์ ของฟังก์ชันหมายถึงเค้าโครงของฟังก์ชันและรวมถึงชื่อฟังก์ชันวงเล็บและ อาร์กิวเมนต์
ไวยากรณ์สำหรับฟังก์ชัน VLOOKUP คือ:
= VLOOKUP (lookup_value, table_array, col_index_num, range_lookup)
การค้นหา _value - (ต้องระบุ) ค่าที่จะค้นหาเช่นปริมาณที่ขายในภาพด้านบน
table_array - (จำเป็น) นี่คือตารางข้อมูลที่ VLOOKUP ค้นหาเพื่อค้นหาข้อมูลที่คุณต้องการ
- table_array ต้องมีอย่างน้อยสองคอลัมน์ของข้อมูล
- คอลัมน์แรกประกอบด้วย lookup_value โดยปกติ
col_index_num - (ต้องระบุ) หมายเลขคอลัมน์ของค่าที่คุณต้องการพบ
- การเรียงลำดับจะขึ้นต้นด้วยคอลัมน์ search_key เป็นคอลัมน์ที่ 1
- ถ้า col_index_num ถูกตั้งค่าเป็นจำนวนมากกว่าจำนวนคอลัมน์ที่เลือกในอาร์กิวเมนต์ table_array #REF! ข้อผิดพลาดจะถูกส่งกลับโดยฟังก์ชัน
range_lookup - (ระบุหรือไม่ก็ได้) ระบุว่ามีการเรียงลำดับตามลำดับจากน้อยไปมากหรือไม่
- ข้อมูลในคอลัมน์แรกใช้เป็นคีย์จัดเรียง
- ค่าบูลีน - TRUE หรือ FALSE เป็นค่าที่ยอมรับได้เท่านั้น
- หากข้ามไปค่าเริ่มต้นจะถูกกำหนดเป็น TRUE
- หากตั้งค่าเป็น TRUE หรือถูกละเว้นและคอลัมน์แรกของช่วงจะไม่เรียงตามลำดับจากน้อยไปอาจมีผลลัพธ์ที่ไม่ถูกต้อง
- หากตั้งค่าเป็น TRUE หรือละเว้นและไม่พบการจับคู่ที่ตรงกันสำหรับการ ค้นหา _value การจับคู่ที่ใกล้ที่สุดที่มีขนาดหรือค่าน้อยกว่าจะถูกใช้เป็น search_key
- หากตั้งค่าเป็น FALSE VLOOKUP จะยอมรับการจับคู่แบบตรงทั้งหมดสำหรับการ ค้นหา _value เท่านั้น หากมีค่าการจับคู่หลายค่าที่ตรงกันจะถูกส่งคืน
- หากตั้งค่าเป็น FALSE และไม่พบค่าที่ตรงกันสำหรับ search_key ข้อผิดพลาด # N / A จะถูกส่งกลับโดยฟังก์ชัน
ตัวอย่าง: หาอัตราส่วนลดสำหรับจำนวนที่ซื้อ
ตัวอย่างในภาพด้านบนใช้ฟังก์ชัน VLOOKUP เพื่อหาอัตราคิดลดที่แตกต่างกันไปขึ้นอยู่กับปริมาณของสินค้าที่ซื้อ
ตัวอย่างแสดงให้เห็นว่าส่วนลดสำหรับการซื้อสินค้า 19 รายการคือ 2% เนื่องจากคอลัมน์ Quantity มีช่วงของค่า เป็นผลให้ VLOOKUP ไม่สามารถหาคู่ที่ตรงกันได้ ต้องมีการจับคู่โดยประมาณเพื่อให้ได้อัตราคิดลดที่ถูกต้อง
เพื่อหาผลลัพธ์ที่ใกล้เคียงกัน:
- จัดเรียงข้อมูลใน table_array โดย เรียงจากน้อยไปหามาก
- ตั้งอาร์กิวเมนต์ range_lookup เป็น TRUE
ในตัวอย่างสูตรต่อไปนี้ที่มีฟังก์ชัน VLOOKUP ใช้เพื่อหาส่วนลดสำหรับปริมาณสินค้าที่ซื้อ
= VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE)
แม้ว่าสูตรนี้จะถูกพิมพ์ลงในเซลล์แผ่นงานตัวเลือกอื่นที่ใช้กับขั้นตอนด้านล่างคือการใช้กล่องโต้ตอบของฟังก์ชันเพื่อป้อนอาร์กิวเมนต์
- การใช้กล่องโต้ตอบมักทำให้การป้อนอาร์กิวเมนต์ของฟังก์ชันทำได้ง่ายขึ้น
การเปิดกล่องโต้ตอบ VLOOKUP
ขั้นตอนที่ใช้ในการป้อนฟังก์ชัน VLOOKUP ที่แสดงในภาพด้านบนลงในเซลล์ B2 คือ:
- คลิกที่เซลล์ B2 เพื่อสร้าง เซลล์ที่ใช้งานอยู่ - ตำแหน่งที่แสดงผลของฟังก์ชัน VLOOKUP
- คลิกแท็บ สูตร
- เลือกการ ค้นหาและการอ้างอิง จาก Ribbon เพื่อเปิดรายการแบบเลื่อนลงของฟังก์ชัน
- คลิกที่ VLOOKUP ในรายการเพื่อเรียกกล่องโต้ตอบของฟังก์ชั่น
02 จาก 03
การป้อนอาร์กิวเมนต์ของฟังก์ชัน Excel VLOOKUP
ชี้ไปที่การอ้างอิงเซลล์
อาร์กิวเมนต์สำหรับฟังก์ชัน VLOOKUP ถูกป้อนลงในบรรทัดแยกจากกล่องโต้ตอบดังที่แสดงในภาพด้านบน
การอ้างอิงเซลล์ที่จะใช้เป็นอาร์กิวเมนต์สามารถพิมพ์ลงในบรรทัดที่ถูกต้องหรือทำตามขั้นตอนด้านล่างชี้ซึ่งเกี่ยวข้องกับการเน้นช่วงที่ต้องการของเซลล์ด้วยตัวชี้เมาส์สามารถใช้เพื่อป้อนข้อมูลลงในกล่องโต้ตอบ .
ข้อดีของการใช้การชี้ ได้แก่ :
- มันเร็วกว่าการพิมพ์;
- มีข้อผิดพลาดน้อยลงเมื่อป้อนข้อมูลอ้างอิงของเซลล์ที่ถูกต้อง
ใช้การอ้างอิงเซลล์สัมพัทธ์และสัมบูรณ์ด้วยอาร์กิวเมนต์
ไม่ใช่เรื่องแปลกที่จะใช้ VLOOKUP หลายชุดเพื่อส่งคืนข้อมูลที่แตกต่างจากตารางข้อมูลเดียวกัน เพื่อให้ง่ายต่อการทำเช่นนี้ VLOOKUP มักจะสามารถคัดลอกจากเซลล์หนึ่งไปยังอีกเซลล์หนึ่งได้ เมื่อฟังก์ชันถูกคัดลอกไปยังเซลล์อื่น ๆ ต้องระมัดระวังเพื่อให้มั่นใจว่าข้อมูลอ้างอิงที่เป็นผลลัพธ์ถูกต้องเนื่องจากตำแหน่งใหม่ของฟังก์ชัน
ในรูปด้านบนเครื่องหมายดอลลาร์ ( $ ) ล้อมรอบการอ้างอิงเซลล์สำหรับอาร์กิวเมนต์ table_array ระบุว่าเป็น ข้อมูลอ้างอิงของเซลล์สัมบูรณ์ ซึ่งหมายความว่าจะไม่มีการเปลี่ยนแปลงหากฟังก์ชันถูกคัดลอกไปยังเซลล์อื่น นี่เป็นที่พึงประสงค์เนื่องจาก VLOOKUP หลายชุดจะอ้างถึงข้อมูลตารางเดียวกันเป็นแหล่งข้อมูล
การอ้างอิงเซลล์ที่ใช้สำหรับ lookup_value ไม่มีการล้อมรอบด้วยเครื่องหมายดอลลาร์ซึ่งจะทำให้มีการอ้างอิงเซลล์สัมพัทธ์ การอ้างอิงเซลล์สัมพัทธ์จะเปลี่ยนแปลงเมื่อคัดลอกเพื่อสะท้อนตำแหน่งใหม่ของพวกเขาเทียบกับตำแหน่งของข้อมูลที่พวกเขาอ้างถึง
การป้อนอาร์กิวเมนต์ของฟังก์ชัน
- คลิกที่การค้นหาค่า _value บรรทัดในการ VLOOKUP กล่องโต้ตอบ
- คลิกที่เซลล์ C2 ในแผ่นงานเพื่อป้อนข้อมูลอ้างอิงเซลล์นี้เป็นอาร์กิวเมนต์ search_key
- คลิกที่บรรทัด Table_array ของกล่องโต้ตอบ
- เน้นเซลล์ C5 ถึง D8 ในแผ่นงานเพื่อป้อน ช่วง นี้เป็นอาร์กิวเมนต์ Table_array - ไม่รวมส่วนหัวของตาราง
- กดแป้น F4 บนแป้นพิมพ์เพื่อเปลี่ยนช่วงเป็นข้อมูลอ้างอิงของเซลล์ที่แน่นอน
- คลิกที่ Col_index_num บรรทัดของกล่องโต้ตอบ
- พิมพ์ 2 ในบรรทัดนี้เป็นอาร์กิวเมนต์ Col_index_num เนื่องจากอัตราคิดลดอยู่ในคอลัมน์ 2 ของอาร์กิวเมนต์ Table_array
- คลิกที่ Range_lookup บรรทัดของกล่องโต้ตอบ
- พิมพ์คำ True เป็นอาร์กิวเมนต์ Range_lookup
- กดปุ่ม Enter บนแป้นพิมพ์เพื่อปิดกล่องโต้ตอบและกลับไปที่เวิร์กชีท
- คำตอบ 2% (อัตราคิดลดสำหรับปริมาณที่ซื้อ) ควรปรากฏในเซลล์ D2 ของแผ่นงาน
- เมื่อคุณคลิกที่เซลล์ D2 ฟังก์ชันที่สมบูรณ์ = VLOOKUP (C2, $ C $ 5: $ D $ 8,2, TRUE) ปรากฏขึ้นใน แถบสูตร เหนือแผ่นงาน
ทำไม VLOOKUP คืนผลลัพธ์ 2%
- ในตัวอย่างคอลัมน์ Quantity จะไม่มีการจับคู่แบบตรงทั้งหมดสำหรับค่า search_key ของ 19
- เนื่องจากอาร์กิวเมนต์ is_sorted ถูกตั้งค่าเป็น TRUE VLOOKUP จะหาค่าที่ใกล้เคียงกับค่า search_key
- ค่าที่ใกล้ที่สุดในขนาดที่เล็กกว่าค่า search_key ที่ 19 เท่ากับ 11
- VLOOKUP จึงหาเปอร์เซ็นต์ส่วนลดในแถวที่มี 11 และผลตอบแทนที่ได้รับจะเป็นอัตราคิดลดที่ 2%
03 จาก 03
Excel VLOOKUP ไม่ทำงาน: # N / A และ #REF ข้อผิดพลาด
VLOOKUP ข้อความแสดงข้อผิดพลาด
ข้อความแสดงข้อผิดพลาดต่อไปนี้เกี่ยวข้องกับ VLOOKUP
A # N / A ("ค่าไม่สามารถใช้ได้") ข้อผิดพลาดจะแสดงขึ้นถ้า:
- การ ค้นหา _value ไม่พบในคอลัมน์แรกของอาร์กิวเมนต์ ช่วง
- อาร์กิวเมนต์ Table_array ไม่ถูกต้อง ตัวอย่างเช่นอาร์กิวเมนต์อาจรวมถึงคอลัมน์ว่างที่ด้านซ้ายของช่วง
- อาร์กิวเมนต์ Range_lookup ถูกตั้งค่าเป็น FALSE และไม่พบอาร์กิวเมนต์ที่ตรงกันสำหรับอาร์กิวเมนต์ search_key ในคอลัมน์แรกของ ช่วง
- อาร์กิวเมนต์ Range_lookup ถูกตั้งค่าเป็น TRUE และค่าทั้งหมดในคอลัมน์แรกของ ช่วง มีขนาดใหญ่กว่า search_key
#REF! ("อ้างอิงจากช่วง") ข้อผิดพลาดจะแสดงขึ้นถ้า:
- อาร์กิวเมนต์ Col_index_num มีค่ามากกว่าจำนวนคอลัมน์ใน Table_array