วิธีใช้ VLOOKUP ใน Excel

คุณเคยมีสเปรดชีตขนาดใหญ่ที่มีข้อมูลในExcelและต้องการวิธีง่ายๆ ในการกรองและดึงข้อมูลเฉพาะหรือไม่? ถ้าคุณเรียนรู้วิธีใช้VLOOKUPในExcelคุณสามารถค้นหาได้ด้วยฟังก์ชัน Excel อันทรงพลังเพียงฟังก์ชัน(Excel function)เดียว

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

พารามิเตอร์ VLOOKUP ใน Excel(VLOOKUP Parameters In Excel)

เมื่อคุณเริ่มพิมพ์=VLOOKUP(ลงในเซลล์ใดๆ ในExcelคุณจะเห็นป๊อปอัปแสดงพารามิเตอร์ของฟังก์ชันที่มีอยู่ทั้งหมด

เรามาตรวจสอบพารามิเตอร์เหล่านี้กันและความหมายของมันกัน

  • lookup_value : ค่าที่คุณกำลังมองหาจากสเปรดชีต
  • table_array : ช่วงของเซลล์ในชีตที่คุณต้องการค้นหาผ่าน
  • col_index_num : คอลัมน์ที่คุณต้องการดึงผลลัพธ์จาก
  • [range_lookup] : โหมดจับคู่(Match mode) ( TRUE = approximate , FALSE = exact ทั้งหมด )

พารามิเตอร์ทั้งสี่นี้ช่วยให้คุณทำการค้นหาข้อมูลภายในชุดข้อมูลขนาดใหญ่ที่แตกต่างกันและมีประโยชน์มากมาย

ตัวอย่าง VLOOKUP Excel อย่างง่าย(A Simple VLOOKUP Excel Example)

VLOOKUP ไม่ใช่ฟังก์ชัน(VLOOKUP isn)พื้นฐานของExcelอย่างที่คุณอาจเคยเรียนรู้มา ดังนั้นเรามาดูตัวอย่างง่ายๆ เพื่อเริ่มต้นกัน

สำหรับตัวอย่างต่อไปนี้ เราจะใช้สเปรดชีตขนาดใหญ่ของคะแนนSATสำหรับโรงเรียนใน(States)สหรัฐอเมริกา (United) สเปรดชีตนี้มีโรงเรียนมากกว่า 450 แห่ง พร้อมด้วย คะแนน SAT ของแต่ละคน สำหรับการอ่าน คณิตศาสตร์ และการเขียน โหลด(Feel)มาติดตามกันได้เลยคร้าบบบ มีการเชื่อมต่อภายนอกที่ดึงข้อมูล ดังนั้นคุณจะได้รับคำเตือนเมื่อเปิดไฟล์ แต่ก็ปลอดภัย

จะใช้เวลามากในการค้นหาชุดข้อมูลขนาดใหญ่เพื่อค้นหาโรงเรียนที่คุณสนใจ

แต่คุณสามารถสร้างฟอร์มอย่างง่ายในเซลล์ว่างที่ด้านข้างของตารางได้ ในการค้นหานี้ เพียงสร้างหนึ่งฟิลด์สำหรับSchoolและอีกสามฟิลด์สำหรับการอ่าน คณิตศาสตร์ และคะแนนการเขียน

ถัดไป คุณจะต้องใช้ฟังก์ชัน VLOOKUP(VLOOKUP function)ในExcelเพื่อให้ทั้งสามฟิลด์นี้ทำงานได้ ในฟิลด์Reading ให้สร้าง (Reading)ฟังก์ชัน VLOOKUP(VLOOKUP function)ดังนี้:

  1. ชนิด=VLOOKUP(
  2. เลือกฟิลด์ School(School field)ซึ่งในตัวอย่างนี้คือI2 พิมพ์เครื่องหมายจุลภาค
  3. เลือกช่วงของเซลล์ทั้งหมดที่มีข้อมูลที่คุณต้องการค้นหา พิมพ์เครื่องหมายจุลภาค

เมื่อคุณเลือกช่วง คุณสามารถเริ่มจากคอลัมน์ที่คุณใช้เพื่อค้นหา (ในกรณีนี้คือคอลัมน์ชื่อโรงเรียน(school name column) ) จากนั้นเลือกคอลัมน์และแถวอื่นๆ ทั้งหมดที่มีข้อมูล

หมายเหตุ(Note) : ฟังก์ชัน VLOOKUP(VLOOKUP function)ในExcelสามารถค้นหาผ่านเซลล์ทางด้านขวาของคอลัมน์ค้นหา(search column)เท่านั้น ในตัวอย่างนี้คอลัมน์ชื่อโรงเรียน(school name column)ต้องอยู่ทางด้านซ้ายของข้อมูลที่คุณกำลังค้นหา

  1. ถัดไป ในการดึงคะแนนการอ่าน(Reading score)คุณจะต้องเลือกคอลัมน์ที่ 3 จากคอลัมน์ซ้ายสุดที่เลือก ดังนั้น พิมพ์3แล้วพิมพ์เครื่องหมายจุลภาคอื่น
  2. สุดท้าย พิมพ์FALSEสำหรับการจับคู่แบบตรงทั้งหมด และปิดฟังก์ชันด้วยa )

ฟังก์ชัน VLOOKUP(VLOOKUP function)สุดท้ายของคุณควรมีลักษณะดังนี้:

=VLOOKUP(I2,B2:G461,3,FALSE)

เมื่อคุณกดEnter ครั้งแรก และสิ้นสุดฟังก์ชัน คุณจะสังเกตเห็นว่าช่องการอ่าน(Reading field)จะมี#N/A A

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

วิธีจัดการกับ VLOOKUP ที่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่(How To Deal With VLOOKUP Being Case- Sensitive)

คุณอาจสังเกตเห็นว่าถ้าคุณไม่พิมพ์ชื่อโรงเรียนในกรณีเดียวกับที่ระบุไว้ในชุดข้อมูล คุณจะไม่เห็นผลลัพธ์ใดๆ

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

เพื่อหลีกเลี่ยงปัญหานี้ คุณสามารถบังคับสิ่งที่คุณกำลังค้นหาให้เปลี่ยนเป็นตัวพิมพ์เล็กก่อนที่จะค้นหาผลลัพธ์ ในการดำเนินการนี้ ให้สร้างคอลัมน์ใหม่ถัดจากคอลัมน์ที่คุณกำลังค้นหา พิมพ์ฟังก์ชัน:

=TRIM(LOWER(B2))

การดำเนินการนี้จะเป็นตัวพิมพ์เล็กชื่อโรงเรียน(school name)และลบอักขระที่ไม่เกี่ยวข้อง (ช่องว่าง) ที่อาจอยู่ทางด้านซ้ายหรือด้านขวาของชื่อ

กดปุ่ม Shift ค้างไว้แล้ววาง(Shift key and place)เคอร์เซอร์(mouse cursor)ของ เมาส์ไว้ เหนือมุมล่างขวาของเซลล์แรกจนกว่าจะเปลี่ยนเป็นเส้นแนวนอนสองเส้น ดับเบิล(Double)คลิกเมาส์เพื่อเติมทั้งคอลัมน์โดยอัตโนมัติ

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

เมื่อข้อมูลทั้งหมดของคุณได้รับการล้างในคอลัมน์ใหม่นี้แล้ว ให้ปรับเปลี่ยนฟังก์ชัน VLOOKUP(VLOOKUP function)ในExcel เล็กน้อย เพื่อใช้คอลัมน์ใหม่นี้แทนคอลัมน์ก่อนหน้าโดยเริ่มช่วงการค้นหา(the lookup range)ที่ C2 แทนที่จะเป็น B2

=VLOOKUP(I2,C2:G461,3,FALSE)

ตอนนี้ คุณจะสังเกตเห็นว่าหากคุณพิมพ์คำค้นหาด้วยตัวพิมพ์เล็กเสมอ คุณจะได้ผลการค้นหา(search result) ที่ดี เสมอ

นี่เป็นเคล็ดลับ Excel ที่มีประโยชน์(handy Excel tip)ในการเอาชนะข้อเท็จจริงที่ว่าVLOOKUPจะคำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่

VLOOKUP การแข่งขันโดยประมาณ

แม้ว่า ตัวอย่างการค้นหาการ(LOOKUP example)จับคู่แบบตรง ทั้งหมดที่ อธิบายไว้ในส่วนแรกของบทความนี้ค่อนข้างตรงไปตรงมา แต่การจับคู่โดยประมาณนั้นซับซ้อนกว่าเล็กน้อย

ค่าที่ตรงกันโดยประมาณจะใช้ดีที่สุดในการค้นหาช่วงตัวเลข ในการดำเนินการนี้อย่างถูกต้องจะต้องจัดเรียงช่วงการค้นหา อย่างเหมาะสม (search range)ตัวอย่างที่ดีที่สุดคือฟังก์ชัน VLOOKUP(VLOOKUP function)เพื่อค้นหาเกรดตัวอักษร(letter grade)ที่ตรงกับ เกรด ที่ เป็น ตัวเลข(number grade)

หากครูมีรายการ เกรดการ บ้านของนักเรียน(student homework) จำนวน มากตลอดทั้งปีโดยมีคอลัมน์เฉลี่ย(averaged column) สุดท้าย คงจะดีถ้าเกรดตัวอักษรที่ตรงกับเกรดสุดท้ายนั้นปรากฏขึ้นโดยอัตโนมัติ

เป็นไป ได้ด้วยฟังก์ชัน VLOOKUP (VLOOKUP function)ทั้งหมดที่จำเป็นคือตารางค้นหา(lookup table)ทางด้านขวาซึ่งมีเกรดตัวอักษร ที่เหมาะสมสำหรับแต่ละ (letter grade)ช่วงคะแนนที่(score range)เป็นตัวเลข

ขณะนี้ เมื่อใช้ฟังก์ชัน VLOOKUP(VLOOKUP function)และการจับคู่โดยประมาณ คุณสามารถค้นหาเกรดตัวอักษรที่เหมาะสมกับช่วงตัวเลขที่ถูกต้องได้

ในฟังก์ชัน VLOOKUP นี้:

  • lookup_value : F2 เกรดเฉลี่ยสุดท้าย
  • table_array : I2:J8 ช่วงการค้นหาเกรดตัวอักษร(letter grade lookup range)
  • index_column : 2 คอลัมน์ที่สองในตารางค้นหา(lookup table)
  • [range_lookup] : TRUE ค่าที่ตรงกันโดยประมาณ

เมื่อคุณใช้ฟังก์ชัน VLOOKUP(VLOOKUP function)ในG2 เสร็จแล้วกด Enter(G2 and press Enter)คุณสามารถกรอกข้อมูลในเซลล์ที่เหลือโดยใช้วิธีการเดียวกันกับที่อธิบายไว้ในส่วนสุดท้าย คุณจะเห็นเกรดตัวอักษรทั้งหมดถูกกรอกอย่างถูกต้อง

โปรดทราบว่าฟังก์ชัน VLOOKUP(VLOOKUP function)ในExcelจะค้นหาจากด้านล่างสุดของช่วงเกรด(grade range)ด้วยคะแนนตัวอักษร(letter score) ที่กำหนด ไปที่ด้านบนของช่วงคะแนนตัวอักษร(letter score) ถัด ไป

ดังนั้น “C” จะต้องเป็นตัวอักษรที่กำหนดให้กับช่วงล่าง (75) และ B ถูกกำหนดไว้ที่ด้านล่าง (ขั้นต่ำ) ของช่วงตัวอักษร(letter range) ของตัว เอง VLOOKUPจะ "ค้นหา" ผลลัพธ์สำหรับ 60 (D) เป็นค่าโดยประมาณที่ใกล้เคียงที่สุดสำหรับค่าใดๆ ระหว่าง 60 ถึง 75

VLOOKUPในExcelเป็นฟังก์ชันที่ทรงพลังมากซึ่งมีให้ใช้งานมาเป็นเวลานาน นอกจากนี้ยังเป็นประโยชน์สำหรับการค้นหาค่าที่ตรงกันทุกที่ใน เวิร์กบุ๊ กExcel(Excel workbook)

อย่างไรก็ตาม โปรดทราบว่า ผู้ใช้ Microsoftที่มีการ สมัครใช้งาน Office 365 แบบรายเดือน จะสามารถเข้าถึงฟังก์ชัน XLOOKUP ที่ใหม่กว่าได้ ฟังก์ชันนี้มีพารามิเตอร์และความยืดหยุ่นเพิ่มเติม ผู้ใช้ที่สมัครรับข้อมูลรายครึ่งปีจะต้องรอให้การอัปเดตเปิดตัวในเดือนกรกฎาคม 2020(July 2020)



About the author

ฉันเป็นมืออาชีพด้านการรีวิวซอฟต์แวร์ที่มีประสบการณ์มากกว่า 10 ปี ฉันได้เขียนและตรวจสอบซอฟต์แวร์ประเภทต่างๆ มากมาย รวมถึงแต่ไม่จำกัดเพียง Microsoft Office (Office 2007, 2010, 2013), แอป Android และเครือข่ายไร้สาย ทักษะของฉันอยู่ที่การจัดเตรียมการทบทวนโปรแกรม/แอปพลิเคชันโดยละเอียดและมีวัตถุประสงค์เพื่อให้ผู้อื่นใช้เป็นเอกสารอ้างอิงหรือสำหรับงานของตนเอง ฉันยังเป็นผู้เชี่ยวชาญเกี่ยวกับผลิตภัณฑ์ MS office และมีคำแนะนำเกี่ยวกับวิธีการใช้งานอย่างมีประสิทธิภาพและประสิทธิผล



Related posts