ใช้ Excel หรือ Google ชีตนานพอ และคุณจะพบกับคำว่า VLOOKUP มันคืออะไรและมันทำอะไร? ในคู่มือนี้ เราจะพูดถึงสิ่งที่ VLOOKUP ทำ วิธีใช้งานเพื่อให้งานของคุณง่ายขึ้น และให้คำแนะนำเกี่ยวกับวิธีการใช้ประโยชน์สูงสุดใน Excel และ Google ชีต
นอกจากนี้เรายังจัดการกับคำถามต่างๆ เกี่ยวกับ VLOOKUP และข้อผิดพลาดทั่วไปที่คุณอาจพบเมื่อพยายามใช้คำสั่ง
VLOOKUP คืออะไร
VLOOKUP – ย่อมาจาก “vertical lookup” – เป็นฟังก์ชันที่มีต้นกำเนิดใน Microsoft Excel ซึ่งช่วยให้คุณสามารถค้นหาค่าเฉพาะในคอลัมน์ จากนั้นใช้ข้อมูลนั้นเพื่อดึงค่าต่างๆ ขึ้นมาในแถวเดียวกัน
ตัวอย่างเช่น คุณสามารถมีสามคอลัมน์ที่มีป้ายกำกับว่า "ชื่อ" "หมายเลข" และ "ที่อยู่" และคอลัมน์เหล่านี้สามารถกรอกข้อมูลได้ เมื่อใช้ VLOOKUP คุณจะสามารถค้นหาชื่อเฉพาะในคอลัมน์ "ชื่อ" จากนั้นใช้ข้อมูลนั้นเพื่อแสดงหมายเลขหรือที่อยู่ที่สอดคล้องกันซึ่งสามารถพบได้ในแถวเดียวกับชื่อนั้น อย่างไรก็ตาม โปรดทราบว่า VLOOKUP ไม่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่
แม้ว่าจะดูไม่มีประโยชน์อย่างยิ่งเมื่อใช้ในกลุ่มข้อมูลขนาดเล็กเหมือนในตัวอย่างข้างต้น แต่มีประโยชน์มากเมื่อคุณมีข้อมูลจำนวนมากในแผ่นงานของคุณ และคุณต้องการใช้ค่าบางอย่างในพื้นที่อื่นๆ
คุณสามารถสร้างรายการข้อมูลหลักได้ในแผ่นเดียว จากนั้นใช้ VLOOKUP ในชีตที่ต่อจากนี้เพื่อดึงข้อมูลจากรายการหลัก ด้วยวิธีนี้ คุณจะต้องอัปเดตแผ่นงานเดียวเท่านั้น และค่าต่างๆ จะตามมาโดยอัตโนมัติในส่วนที่เหลือ
พูดง่ายๆ ก็คือ สตริง VLOOKUP มีดังต่อไปนี้:
=VLOOKUP(
the value you want to lookup,
the range of cells you want to look for it in,
the column number of the value you want to display,
whether you want an exact or approximate match
) วิธีใช้ VLOOKUP ใน Excel และ Google ชีต
เมื่อมองแวบแรก สตริง VLOOKUP อาจดูน่ากลัว แต่จริงๆ แล้วง่ายกว่าที่เห็นมาก เนื่องจากคำแนะนำเชิงลึกเหล่านี้จะแสดงขึ้น
- คุณต้องมีตารางข้อมูลเพื่อดึงออกมา เราใช้แนวคิดเดียวกันกับตัวอย่างที่ให้ไว้ก่อนหน้านี้และเพิ่มข้อมูลในสามคอลัมน์ ได้แก่ ชื่อ ที่อยู่ และหมายเลขโทรศัพท์
- เราต้องการดึงหมายเลขโทรศัพท์ที่เกี่ยวข้องของชื่อใดก็ตามในช่องใดฟิลด์หนึ่ง ในกรณีนี้ เราต้องการดึงหมายเลขโทรศัพท์ของ “Iris Watson”
- เริ่มสตริงโดยดับเบิลคลิกเซลล์ว่างแล้วพิมพ์
=VLOOKUP(. คุณจะเห็นว่าค่าแรกที่ต้องการคือ "lookup_value" ซึ่งเป็นข้อมูลที่คุณจะใช้ในการค้นหาหมายเลขโทรศัพท์
- เนื่องจากเราได้เพิ่มชื่อ “Iris Watson” ลงในเซลล์ก่อนหน้า VLOOKUP แล้ว เซลล์นั้นจึงเป็นสิ่งที่คุณจะใช้เป็น lookup_value สตริงของคุณควรมีลักษณะดังนี้:
=VLOOKUP(e12,
- ถัดไปคือ table_array นี่คือตารางข้อมูลทั้งหมดที่คุณกำลังดึงข้อมูล เพียงเน้นทั้งตารางที่มีข้อมูลของคุณอยู่ ในขั้นตอนนี้ สตริงของคุณควรมีลักษณะดังนี้:
=VLOOKUP(e12,A1:C5,
- ค่าที่สามในสตริงของคุณคือ “col_index_number” หรือหมายเลขดัชนีคอลัมน์ นี่คือหมายเลขของคอลัมน์ที่คุณพยายามดึงข้อมูล ในอาร์เรย์ตารางที่เราเลือก "ชื่อ" จะถือเป็น 1 "ที่อยู่" คือ 2 และ "หมายเลขโทรศัพท์" คือ 3 เนื่องจากเรากำลังพยายาม VLOOKUP หมายเลขโทรศัพท์ สตริงของเราจึงมีลักษณะดังนี้:
=VLOOKUP(e12,A1:C5,3,
- สุดท้ายนี้ ส่วนสุดท้ายของสตริงคือคุณต้องการการจับคู่แบบตรงทั้งหมดหรือการจับคู่โดยประมาณสำหรับค่าที่คุณกำลังค้นหาอยู่หรือไม่ คุณจะต้องพิมพ์ TRUE สำหรับค่าโดยประมาณและ FALSE สำหรับค่าที่แน่นอน ในกรณีของเราเราไปกับคนหลัง สตริงสุดท้ายของเราคือ
=VLOOKUP(E12,A1:C5,3,FALSE)
- อย่างที่คุณเห็น VLOOKUP ของเราดึงหมายเลขโทรศัพท์ที่เกี่ยวข้องสำหรับ “Iris Watson” สำเร็จแล้ว
หมายเหตุ :คู่มือนี้ใช้ Microsoft Excel เพื่อดำเนินการ VLOOKUP แต่คุณสามารถใช้วิธีการเดียวกันกับ Google ชีตได้
วิธีการกรองข้อมูลโดยใช้หลายเกณฑ์
VLOOKUP ได้รับการออกแบบมาให้ดึงค่าเพียงค่าเดียว และยังมีฟังก์ชันอื่นๆ สำหรับการค้นหาแหล่งข้อมูลหลายแห่ง แต่ไม่ได้หมายความว่าคุณจะใช้ VLOOKUP เพื่อค้นหาเกณฑ์หลายเกณฑ์ไม่ได้ เมื่อใช้คอลัมน์ตัวช่วย คุณสามารถสร้างตัวระบุที่ไม่ซ้ำที่เก็บข้อมูลจากหลายเซลล์ จากนั้นปรับแต่ง VLOOKUP เพื่อค้นหาตัวระบุที่ไม่ซ้ำเหล่านี้แทน
ซึ่งมีประโยชน์ในกรณีที่คุณมีหลายเซลล์ที่มีค่าเท่ากัน สมมติว่าตารางของเรามีหลายเซลล์ที่ชื่อ "Iris Watson" ภายใต้สถานการณ์ปกติ VLOOKUP จะดึง “Iris Watson” ตัวแรกที่พบในรายการขึ้นมาเท่านั้น แต่คุณอาจมองหาอันอื่น
เมื่อใช้คอลัมน์ตัวช่วย คุณจะมีตัวระบุเฉพาะที่จะช่วยให้ VLOOKUP แยกความแตกต่างระหว่าง “Iris Watsons” ต่างๆ ในชีตของคุณได้
สำหรับตัวอย่างนี้ เราใช้ VLOOKUP เพื่อแสดงที่อยู่แทนหมายเลขโทรศัพท์ นอกจากนี้เรายังเพิ่ม "Iris Watson" อันที่สองลงในแผ่นงานด้วยที่อยู่และหมายเลขโทรศัพท์อื่น
- สิ่งแรกที่เราทำคือสร้างคอลัมน์ตัวช่วยที่รวมเซลล์ "ชื่อ" และ "หมายเลขโทรศัพท์" เข้าด้วยกันเพื่อสร้างตัวระบุที่ไม่ซ้ำกัน เราใช้ฟังก์ชัน CONCATENATE ซึ่งรวมสตริงจากเซลล์ต่างๆ เข้าด้วยกัน สตริงของคุณควรมีลักษณะดังนี้:
=CONCATENATE(B2," | ",D2)เราได้เพิ่มไปป์"|"สัญลักษณ์และช่องว่างระหว่างชื่อและหมายเลขโทรศัพท์เพื่อให้เข้าใจง่ายขึ้น หากคุณต้องการเรียนรู้เพิ่มเติมเกี่ยวกับฟังก์ชันนี้ โปรดดูบทความเกี่ยวกับวิธีใช้ CONCATENATE
- เมื่อคุณเชื่อมต่อสำเร็จแล้ว ให้คลิกที่มุมล่างขวาของเซลล์ที่ไฮไลต์ แล้วลากลงไปที่เซลล์อื่นในคอลัมน์ตัวช่วย นั่นก็จะใช้การต่อกันแบบเดียวกันกับพวกเขาเช่นกัน
- คุณต้องมี 2 ช่องสำหรับ VLOOKUP เราได้เพิ่มช่องค้นหา "ชื่อ" และ "หมายเลข" รวมถึงช่อง "ที่อยู่" ที่จะแสดงผล VLOOKUP
- สิ่งที่เราต้องการทำคือให้ VLOOKUP รวมข้อมูลที่ระบุในช่องค้นหา "ชื่อ" และ "หมายเลข" เพื่อให้เข้ากับรูปแบบเดียวกับที่เราใช้ในคอลัมน์ผู้ช่วย ด้วยวิธีนี้ VLOOKUP จะสามารถมองเห็นตัวระบุที่ไม่ซ้ำกันในคอลัมน์ตัวช่วยและดึงที่อยู่ที่เกี่ยวข้องขึ้นมาได้
นี่คือลักษณะสตริงของเราในตอนแรก:=VLOOKUP(F9&" | "&F10,
F9 และ F10 คือช่องค้นหา "ชื่อ" และ "ที่อยู่" ของเราตามลำดับ และสัญลักษณ์ &ทำงานเหมือนการต่อกันที่รวมทั้งสองฟิลด์เข้าด้วยกัน “ | “ ส่วนหนึ่งของสตริงเป็นตัวแบ่งเดียวกับที่เราใช้ในการต่อกันในคอลัมน์ตัวช่วยของเรา
- ตอนนี้คุณต้องทำตามสูตร VLOOKUP ที่เหลือเท่านั้น คุณจะต้องเลือกอาร์เรย์ของตาราง ป้อนหมายเลขดัชนีคอลัมน์ และตั้งค่าให้ตรงกันทั้งหมด นี่คือสตริงสิ้นสุดของเรา:
=VLOOKUP(F9&" | "&F10,A1:D6,3,FALSE)
- คุณจะเห็นว่าการป้อนเพียงชื่อหรือที่อยู่ในช่องค้นหาไม่เพียงพอ ต้องกรอกทั้งสองอย่างจึงจะสำเร็จในการค้นหา ด้านล่างนี้ คุณสามารถดูผลลัพธ์ VLOOKUP ที่แตกต่างกันสองรายการ เนื่องจากเรามี "Iris Watsons" ที่แตกต่างกันสองรายการ คุณจะได้ผลลัพธ์ที่แตกต่างกันไปตามหมายเลขโทรศัพท์ที่คุณป้อนในช่องค้นหาที่สอง
VLOOKUP ดีกว่า INDEX-MATCH หรือไม่
คำถามนี้ได้รับการถกเถียงกันอย่างหนักตั้งแต่สมัยก่อนๆ ของ Microsoft Excel ก่อนที่เราจะตอบคำถามนี้ สิ่งสำคัญคือเราต้องเข้าใจว่า INDEX-MATCH คืออะไร INDEX และ MATCH เป็นฟังก์ชันสองอย่างที่แยกจากกันซึ่งมักจะรวมกันเพื่อสร้างระบบการค้นหาที่หลากหลายกว่า VLOOKUP
ฟังก์ชัน MATCH จะใช้เมื่อคุณต้องการค้นหาหมายเลขตำแหน่งของค่าใดค่าหนึ่งภายในช่วงของเซลล์ ในทางกลับกัน INDEX ใช้รูปแบบที่เป็นไปได้สองรูปแบบในการแสดงค่าจากอาร์เรย์ตารางหรือช่วงของเซลล์ เมื่อรวมทั้งสองอย่างเข้าด้วยกัน เราสามารถใช้ฟังก์ชัน MATCH เพื่อกำหนดหมายเลขตำแหน่งของข้อมูลที่ให้ จากนั้นให้ INDEX ใช้หมายเลขตำแหน่งเหล่านั้นเพื่อส่งกลับค่า
อันไหนดีกว่ากันว่าใครใช้ฟังก์ชันนี้ VLOOKUP เป็นมิตรกับมือใหม่และผู้ใช้ Excel หรือ Google ชีตระดับกลางมากกว่ามาก เนื่องจากการตั้งค่าและใช้งานนั้นง่ายกว่ามาก อย่างไรก็ตาม INDEX-MATCH นั้นยืดหยุ่นกว่ามากและสามารถใช้ได้ในสถานการณ์ที่หลากหลายขึ้น
ในตอนท้ายของวัน ผู้คนจำนวนมากรู้วิธีใช้ VLOOKUP เมื่อเทียบกับ INDEX-MATCH ดังนั้นตัวเลือกแรกจึงน่าจะเป็นตัวเลือกที่ดีกว่าหากผู้ใช้ที่ไม่ขั้นสูงจำนวนมากเข้าถึงชีต แต่ถ้าแผ่นงานมีไว้สำหรับผู้เชี่ยวชาญ INDEX-MATCH ก็น่าจะเป็นทางไป
สิ่งที่ควรทำและไม่ควรทำทั่วไปเมื่อใช้ VLOOKUP
การทำผิดพลาดเล็กน้อยไม่ใช่เรื่องน่าอายเมื่อคุณเพิ่งเริ่มใช้ฟังก์ชัน VLOOKUP ทหารผ่านศึกของ Excel ส่วนใหญ่เคยอยู่ที่นั่นมาก่อนในชีวิต ต่อไปนี้คือสิ่งที่ควรคำนึงถึงเมื่อลองใช้ VLOOKUP
1. ทำ ตรวจสอบให้แน่ใจว่า Lookup_Value เป็นคอลัมน์แรกในตารางของคุณ
ฟังก์ชัน VLOOKUP ภายใต้สมมติฐานว่า lookup_value ของคุณอยู่ในคอลัมน์แรกในอาร์เรย์ตารางของคุณ สามารถแสดงข้อมูลได้ก็ต่อเมื่อค่านั้นอยู่ในเซลล์ที่ต่อจากแถวเดียวกัน วาง lookup_value ไว้ที่อื่นที่ไม่ใช่จุดเริ่มต้น แล้วฟังก์ชันจะล้มเหลว
2. อย่าลืมใช้ FALSE สำหรับการจับคู่แบบตรงทั้งหมด
ส่วนสุดท้ายของสตริง VLOOKUP ช่วยให้คุณกำหนดได้ว่าค่านั้นเป็น FALSE สำหรับการจับคู่แบบตรงทั้งหมด หรือ TRUE สำหรับการจับคู่บางส่วน ผู้ใช้หลายคนใช้ TRUE ผิดพลาด ซึ่งอาจนำไปสู่ผลลัพธ์ที่ไม่ถูกต้อง หรือลืมตั้งค่าเลย
3. ตรวจสอบหมายเลขดัชนีคอลัมน์อีกครั้ง
VLOOKUP จะแสดงอะไรขึ้นอยู่กับคุณตั้งค่า “col_index_num” ที่ถูกต้องเมื่อพิมพ์สตริง “col_index_num” หรือหมายเลขดัชนีคอลัมน์คือค่าตัวเลขที่กำหนดไว้สำหรับแต่ละคอลัมน์ในอาร์เรย์ตารางของคุณ ค่าของคอลัมน์แรกถือเป็น 1 คอลัมน์ที่สอง 2 เป็นต้น ป้อนค่า “col_index_num” ที่ไม่ถูกต้องในสตริงของคุณ และฟังก์ชันจะแสดงผลลัพธ์ที่แตกต่างไปจากเดิมอย่างสิ้นเชิง
4. ใช้ F4 เมื่อคัดลอกสูตรไปยังเซลล์อื่น
สิ่งหนึ่งที่มีประโยชน์มากที่สุดเกี่ยวกับ VLOOKUP คือคุณสามารถลากสูตรลงไปเพื่อคัดลอกไปยังเซลล์ต่างๆ ปัญหาคือ ค่าที่ระบุในสตริงฟังก์ชันจะเลื่อนลงมาเช่นกัน ทำลายสูตรทั้งหมด เพื่อป้องกันสิ่งนี้ ให้วางเคอร์เซอร์บนค่าในสูตรของคุณแล้วกด F4 กุญแจ. ซึ่งจะเปลี่ยนเป็นค่าสัมบูรณ์ที่จะไม่เปลี่ยนเมื่อคัดลอกสูตร
ข้อผิดพลาด VLOOKUP ปกติและวิธีแก้ไข
ข้อผิดพลาดที่พบบ่อยที่สุดที่คุณจะพบเมื่อใช้ VLOOKUP คือข้อผิดพลาด “#NA” แม้ว่าควรสังเกตว่ามีเหตุผลหลายประการที่ข้อผิดพลาดนี้จะปรากฏขึ้น
1. Lookup_value ของคุณไม่อยู่ในคอลัมน์แรกของอาร์เรย์ตารางของคุณ
หนึ่งในข้อจำกัดที่ใหญ่ที่สุดของ VLOOKUP คือสามารถค้นหาค่าในคอลัมน์แรกสุดของอาร์เรย์ตารางของคุณเท่านั้น หากไม่มี lookup_value ของคุณ จะทำให้เกิดข้อผิดพลาด #NA ในการแก้ไขปัญหานี้ คุณสามารถปรับสูตรของคุณเพื่ออ้างอิงคอลัมน์อื่น หรือย้ายคอลัมน์ของคุณเพื่อให้ lookup_value อยู่ในตำแหน่งที่ถูกต้อง
2. VLOOKUP ไม่พบรายการที่ตรงกันทั้งหมด
ค่าสุดท้ายในสูตร VLOOKUP ของคุณคืออาร์กิวเมนต์ range_lookup ที่คุณตั้งค่าเป็น TRUE สำหรับค่าที่ตรงกันโดยประมาณ หรือ FALSE สำหรับค่าที่ตรงกันทั้งหมด หากคุณตั้งค่าอาร์กิวเมนต์นี้เป็น FALSE และ VLOOKUP ไม่พบการจับคู่แบบตรงทั้งหมด คุณจะจบลงด้วยข้อผิดพลาด #NA
หากคุณแน่ใจอย่างยิ่งว่า lookup_value ของคุณควรมีข้อมูลตรงกัน คุณจะต้องตรวจสอบข้อมูลในอาร์เรย์ของตารางอีกครั้งเพื่อให้แน่ใจว่าทุกอย่างได้รับการจัดรูปแบบอย่างเหมาะสมและไม่มีช่องว่างที่ไม่ต้องการ อักขระที่ไม่พิมพ์ออกมายังสามารถป้องกันไม่ให้ VLOOKUP ค้นหารายการได้อย่างถูกต้อง
3. ตัวเลขทศนิยมที่มากเกินไป
โดยพื้นฐานแล้ว ตัวเลขทศนิยมคือตัวเลขที่มีจุดทศนิยม ด้วย VLOOKUP หากคุณมีตัวเลขที่มีตัวเลขหลังจุดทศนิยมมากเกินไป คุณจะลงเอยด้วยข้อผิดพลาด #NA การแก้ไขปัญหานี้ค่อนข้างง่าย:ปัดเศษตัวเลขของคุณเป็นทศนิยมสูงสุดห้าตำแหน่ง และควรใช้งานได้ คุณสามารถทำได้โดยใช้ ROUND ฟังก์ชัน
คำถามที่พบบ่อย
1. จะเกิดอะไรขึ้นเมื่อคุณเว้นอาร์กิวเมนต์ range_lookup ว่างไว้
อาร์กิวเมนต์ที่สี่ในสตริง VLOOKUP ซึ่งควรตั้งค่าเป็น "TRUE" หรือ "FALSE" จะถือว่าไม่บังคับ การตั้งค่าเป็น "TRUE" หมายความว่า VLOOKUP ของคุณจะค้นหาการจับคู่โดยประมาณ ในขณะที่ "FALSE" กำหนดให้ค่าตรงกันทั้งหมด ประเด็นก็คือ ถ้าคุณเว้นอาร์กิวเมนต์นี้ว่างไว้ VLOOKUP จะตั้งค่าเริ่มต้นเป็น "TRUE" ซึ่งอาจทำให้ผลลัพธ์ที่คุณต้องการยุ่งเหยิง
2. อะไรคือทางเลือกอื่นในการใช้ VLOOKUP?
ทางเลือกที่ดีที่สุดในการใช้ฟังก์ชัน VLOOKUP คือคำสั่งผสม INDEX-MATCH อย่างไรก็ตาม พึงระลึกไว้เสมอว่าการรวมกันของฟังก์ชันนี้เรียนรู้ได้ยากขึ้นเล็กน้อย เนื่องจากคุณต้องเชี่ยวชาญฟังก์ชันสองอย่างที่แตกต่างกัน และตรวจสอบให้แน่ใจว่าฟังก์ชันเหล่านี้ทำงานร่วมกันอย่างเหมาะสม
3. คุณสามารถค้นหาค่าตามแถวแทนที่จะเป็นคอลัมน์ได้ไหม
ใช่. คุณสามารถทำได้ด้วย HLOOKUP หรือ "การค้นหาแนวนอน" ใน Excel และ Google ชีต ฟังก์ชันนี้ช่วยให้คุณค้นหาค่าจากแถวใดแถวหนึ่ง จากนั้นแสดงค่าจากแถวอื่นแต่อยู่ในคอลัมน์เดียวกัน
หากคุณต้องการจำกัดการค้นหาไว้ที่แถวหรือคอลัมน์เดียว คุณสามารถใช้ฟังก์ชัน LOOKUP ได้
