วิธีใช้ 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)ดังนี้:
- ชนิด=VLOOKUP(
- เลือกฟิลด์ School(School field)ซึ่งในตัวอย่างนี้คือI2 พิมพ์เครื่องหมายจุลภาค
- เลือกช่วงของเซลล์ทั้งหมดที่มีข้อมูลที่คุณต้องการค้นหา พิมพ์เครื่องหมายจุลภาค
เมื่อคุณเลือกช่วง คุณสามารถเริ่มจากคอลัมน์ที่คุณใช้เพื่อค้นหา (ในกรณีนี้คือคอลัมน์ชื่อโรงเรียน(school name column) ) จากนั้นเลือกคอลัมน์และแถวอื่นๆ ทั้งหมดที่มีข้อมูล
หมายเหตุ(Note) : ฟังก์ชัน VLOOKUP(VLOOKUP function)ในExcelสามารถค้นหาผ่านเซลล์ทางด้านขวาของคอลัมน์ค้นหา(search column)เท่านั้น ในตัวอย่างนี้คอลัมน์ชื่อโรงเรียน(school name column)ต้องอยู่ทางด้านซ้ายของข้อมูลที่คุณกำลังค้นหา
- ถัดไป ในการดึงคะแนนการอ่าน(Reading score)คุณจะต้องเลือกคอลัมน์ที่ 3 จากคอลัมน์ซ้ายสุดที่เลือก ดังนั้น พิมพ์3แล้วพิมพ์เครื่องหมายจุลภาคอื่น
- สุดท้าย พิมพ์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)
Related posts
เพิ่ม Regression Trendline แนวตรงไปยัง Excel Scatter Plot
วิธีทำ Histogram ใน Excel
วิธีที่จะปลอดภัย Password Protect Excel File
วิธีสร้าง Flowchart ใน Word and Excel
วิธีการ Create Labels ใน Word จาก Excel Spreadsheet
วิธีเพิ่มความคิดเห็นในเซลล์แผ่นงาน Excel
วิธีติดตามการเปลี่ยนแปลงใน Excel
ใช้ฟังก์ชันสรุปเพื่อสรุปข้อมูลใน Excel
วิธีการสร้าง Drop Down List ใน Excel
เชื่อมโยงเซลล์ระหว่างชีตและเวิร์กบุ๊กใน Excel
วิธีเปิด Excel หลายอินสแตนซ์
วิธีสร้างกราฟหรือแผนภูมิอย่างง่ายใน Excel
ใช้หน้าต่างดูของ Excel เพื่อตรวจสอบเซลล์ที่สำคัญในสมุดงาน
วิธีเพิ่มและพิมพ์รูปภาพพื้นหลังของ Excel
วิธีเปรียบเทียบไฟล์ Excel สองไฟล์และเน้นความแตกต่าง
40 แป้นพิมพ์ลัด Microsoft Excel ที่ดีที่สุด
ใช้ Excel เพื่อหาอัตราดอกเบี้ยที่แท้จริงจากอัตราดอกเบี้ยที่กำหนด
วิธีลบ แตก หรือทำลายรหัสผ่าน Excel XLS ที่ถูกลืม
วิธีการบันทึก Macro ใน Excel
วิธีเพิ่มแถบข้อผิดพลาดใน Excel