คุณเคยมีสเปรดชีตขนาดใหญ่ที่มีข้อมูลใน Excel และต้องการวิธีง่ายๆ ในการกรองและดึงข้อมูลเฉพาะหรือไม่ ถ้าคุณเรียนรู้วิธีใช้ VLOOKUP ใน Excel คุณสามารถค้นหาได้ด้วยฟังก์ชัน Excel อันทรงพลังเพียงฟังก์ชันเดียว
ฟังก์ชัน VLOOKUP ใน Excel สร้างความหวาดกลัวให้กับผู้คนจำนวนมาก เนื่องจากมีพารามิเตอร์จำนวนมากและมีหลายวิธีในการใช้งาน ในบทความนี้ คุณจะได้เรียนรู้วิธีต่างๆ ในการใช้ VLOOKUP ใน Excel และสาเหตุที่ฟังก์ชันนี้มีประสิทธิภาพมาก
พารามิเตอร์ VLOOKUP ใน Excel
เมื่อคุณเริ่มพิมพ์ =VLOOKUP( ในเซลล์ใดก็ได้ใน Excel คุณจะเห็นป๊อปอัปแสดงพารามิเตอร์ฟังก์ชันที่มีอยู่ทั้งหมด
มาตรวจสอบพารามิเตอร์แต่ละตัวและความหมายของมันกัน
- lookup_value :ค่าที่คุณกำลังมองหาจากสเปรดชีต
- table_array :ช่วงของเซลล์ในชีตที่คุณต้องการค้นหาผ่าน
- col_index_num :คอลัมน์ที่คุณต้องการดึงผลลัพธ์ของคุณ
- [range_lookup] :โหมดจับคู่ (TRUE =ค่าประมาณ, FALSE =ตรงทั้งหมด)
พารามิเตอร์ทั้งสี่นี้ช่วยให้คุณทำการค้นหาข้อมูลภายในชุดข้อมูลขนาดใหญ่ได้หลากหลายและมีประโยชน์มากมาย
ตัวอย่าง VLOOKUP Excel อย่างง่าย
VLOOKUP ไม่ใช่ฟังก์ชันพื้นฐานของ Excel อย่างที่คุณอาจเคยเรียนรู้มา ดังนั้นเรามาดูตัวอย่างง่ายๆ เพื่อเริ่มต้นกัน
สำหรับตัวอย่างต่อไปนี้ เราจะใช้สเปรดชีตขนาดใหญ่ของคะแนน SAT สำหรับโรงเรียนในสหรัฐอเมริกา สเปรดชีตนี้มีโรงเรียนมากกว่า 450 แห่ง พร้อมด้วยคะแนน SAT ของแต่ละคนสำหรับการอ่าน คณิตศาสตร์ และการเขียน โหลดไปติดตามกันได้เลยคร้าบบบ มีการเชื่อมต่อภายนอกที่ดึงข้อมูล ดังนั้นคุณจะได้รับคำเตือนเมื่อเปิดไฟล์ แต่ปลอดภัย
จะใช้เวลามากในการค้นหาชุดข้อมูลขนาดใหญ่เพื่อค้นหาโรงเรียนที่คุณสนใจ
คุณสามารถสร้างฟอร์มอย่างง่ายในเซลล์ว่างที่ด้านข้างของตารางแทนได้ ในการค้นหานี้ เพียงสร้างหนึ่งฟิลด์สำหรับโรงเรียน และอีกสามฟิลด์สำหรับการอ่าน คณิตศาสตร์ และคะแนนการเขียน
ถัดไป คุณจะต้องใช้ฟังก์ชัน VLOOKUP ใน Excel เพื่อให้ทั้งสามฟิลด์นี้ทำงานได้ ใน การอ่าน ให้สร้างฟังก์ชัน VLOOKUP ดังนี้:
- พิมพ์ =VLOOKUP(
- เลือกฟิลด์ School ซึ่งในตัวอย่างนี้คือ I2 . พิมพ์เครื่องหมายจุลภาค
- เลือกช่วงของเซลล์ทั้งหมดที่มีข้อมูลที่คุณต้องการค้นหา พิมพ์เครื่องหมายจุลภาค
เมื่อคุณเลือกช่วง คุณสามารถเริ่มจากคอลัมน์ที่คุณใช้เพื่อค้นหา (ในกรณีนี้คือคอลัมน์ชื่อโรงเรียน) จากนั้นเลือกคอลัมน์และแถวอื่นๆ ทั้งหมดที่มีข้อมูล
หมายเหตุ :ฟังก์ชัน VLOOKUP ใน Excel สามารถค้นหาผ่านเซลล์ทางด้านขวาของคอลัมน์ค้นหาเท่านั้น ในตัวอย่างนี้ คอลัมน์ชื่อโรงเรียนต้องอยู่ทางด้านซ้ายของข้อมูลที่คุณกำลังค้นหา
- ถัดไป ในการดึงคะแนนการอ่าน คุณจะต้องเลือกคอลัมน์ที่ 3 จากคอลัมน์ซ้ายสุดที่เลือก ดังนั้น พิมพ์ 3 แล้วพิมพ์เครื่องหมายจุลภาคอื่น
- สุดท้าย พิมพ์ FALSE สำหรับการจับคู่แบบตรงทั้งหมด และปิดฟังก์ชันด้วย ) .
ฟังก์ชัน VLOOKUP สุดท้ายของคุณควรมีลักษณะดังนี้:
=VLOOKUP(I2,B2:G461,3,FALSE)
เมื่อคุณกด Enter เป็นครั้งแรกและสิ้นสุดฟังก์ชัน คุณจะสังเกตเห็นว่าช่องการอ่านจะมี #N/A .
เนื่องจากฟิลด์ School ว่างเปล่าและไม่มีฟังก์ชัน VLOOKUP ให้ค้นหา อย่างไรก็ตาม หากคุณป้อนชื่อโรงเรียนมัธยมที่ต้องการค้นหา คุณจะเห็นผลลัพธ์ที่ถูกต้องจากแถวนั้นสำหรับคะแนนการอ่าน
วิธีจัดการกับ VLOOKUP ที่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่
คุณอาจสังเกตเห็นว่าถ้าคุณไม่พิมพ์ชื่อโรงเรียนในกรณีเดียวกับที่ระบุไว้ในชุดข้อมูล คุณจะไม่เห็นผลลัพธ์ใดๆ
เนื่องจากฟังก์ชัน VLOOKUP คำนึงถึงขนาดตัวพิมพ์ สิ่งนี้อาจสร้างความรำคาญ โดยเฉพาะอย่างยิ่งสำหรับชุดข้อมูลขนาดใหญ่มากซึ่งคอลัมน์ที่คุณกำลังค้นหาไม่สอดคล้องกับวิธีการพิมพ์ตัวพิมพ์ใหญ่ของสิ่งต่างๆ
เพื่อหลีกเลี่ยงปัญหานี้ คุณสามารถบังคับสิ่งที่คุณกำลังค้นหาให้เปลี่ยนเป็นตัวพิมพ์เล็กก่อนที่จะค้นหาผลลัพธ์ ในการดำเนินการนี้ ให้สร้างคอลัมน์ใหม่ถัดจากคอลัมน์ที่คุณกำลังค้นหา พิมพ์ฟังก์ชัน:
=TRIM(LOWER(B2))
การดำเนินการนี้จะเป็นตัวพิมพ์เล็กชื่อโรงเรียนและลบอักขระที่ไม่เกี่ยวข้อง (ช่องว่าง) ที่อาจอยู่ทางด้านซ้ายหรือด้านขวาของชื่อ
กดปุ่ม Shift ค้างไว้แล้ววางเคอร์เซอร์ของเมาส์ไว้เหนือมุมล่างขวาของเซลล์แรกจนกว่าจะเปลี่ยนเป็นเส้นแนวนอนสองเส้น ดับเบิลคลิกเมาส์เพื่อเติมทั้งคอลัมน์โดยอัตโนมัติ
สุดท้าย เนื่องจาก VLOOKUP จะพยายามใช้สูตรแทนข้อความในเซลล์เหล่านี้ คุณจึงต้องแปลงค่าทั้งหมดให้เป็นค่าเท่านั้น ในการดำเนินการนี้ ให้คัดลอกทั้งคอลัมน์ คลิกขวาในเซลล์แรก แล้ววางค่าเท่านั้น
เมื่อข้อมูลทั้งหมดของคุณได้รับการล้างในคอลัมน์ใหม่นี้แล้ว ให้ปรับเปลี่ยนฟังก์ชัน VLOOKUP ใน Excel เล็กน้อยเพื่อใช้คอลัมน์ใหม่นี้แทนคอลัมน์ก่อนหน้าโดยเริ่มช่วงการค้นหาที่ C2 แทนที่จะเป็น B2
=VLOOKUP(I2,C2:G461,3,FALSE)
ตอนนี้ คุณจะสังเกตเห็นว่าหากคุณพิมพ์คำค้นหาด้วยตัวพิมพ์เล็กเสมอ คุณจะได้ผลการค้นหาที่ดีเสมอ
นี่เป็นเคล็ดลับ Excel ที่มีประโยชน์ในการเอาชนะความจริงที่ว่า VLOOKUP คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่
VLOOKUP การจับคู่โดยประมาณ
แม้ว่าตัวอย่างการค้นหาการจับคู่แบบตรงทั้งหมดที่อธิบายไว้ในส่วนแรกของบทความนี้จะค่อนข้างตรงไปตรงมา แต่การจับคู่โดยประมาณนั้นซับซ้อนกว่าเล็กน้อย
การจับคู่โดยประมาณจะใช้ดีที่สุดในการค้นหาช่วงตัวเลข ในการดำเนินการนี้อย่างถูกต้อง จะต้องจัดเรียงช่วงการค้นหาอย่างเหมาะสม ตัวอย่างที่ดีที่สุดคือฟังก์ชัน VLOOKUP เพื่อค้นหาเกรดตัวอักษรที่ตรงกับเกรดที่เป็นตัวเลข
หากครูมีรายการเกรดการบ้านของนักเรียนตลอดทั้งปีพร้อมคอลัมน์เฉลี่ยสุดท้าย คงจะดีถ้าเกรดตัวอักษรที่ตรงกับเกรดสุดท้ายนั้นปรากฏขึ้นโดยอัตโนมัติ
เป็นไปได้ด้วยฟังก์ชัน VLOOKUP สิ่งที่ต้องมีคือตารางค้นหาทางด้านขวาซึ่งมีเกรดตัวอักษรที่เหมาะสมสำหรับแต่ละช่วงคะแนนที่เป็นตัวเลข
ขณะนี้ เมื่อใช้ฟังก์ชัน VLOOKUP และการจับคู่โดยประมาณ คุณจะพบเกรดตัวอักษรที่เหมาะสมกับช่วงตัวเลขที่ถูกต้อง
ในฟังก์ชัน VLOOKUP นี้:
- lookup_value :F2 เกรดเฉลี่ยสุดท้าย
- table_array :I2:J8 ช่วงการค้นหาเกรดตัวอักษร
- index_column :2 คอลัมน์ที่สองในตารางค้นหา
- [range_lookup] :TRUE ค่าที่ตรงกันโดยประมาณ
เมื่อคุณใช้ฟังก์ชัน VLOOKUP ใน G2 เสร็จแล้วและกด Enter คุณสามารถกรอกข้อมูลในเซลล์ที่เหลือโดยใช้วิธีการเดียวกันกับที่อธิบายไว้ในส่วนสุดท้าย คุณจะเห็นเกรดตัวอักษรทั้งหมดถูกกรอกอย่างถูกต้อง
โปรดทราบว่าฟังก์ชัน VLOOKUP ใน Excel จะค้นหาจากด้านล่างสุดของช่วงเกรดด้วยคะแนนตัวอักษรที่กำหนดไปที่ด้านบนของช่วงคะแนนตัวอักษรถัดไป
ดังนั้น “C” จะต้องเป็นตัวอักษรที่กำหนดให้กับช่วงล่าง (75) และ B ถูกกำหนดไว้ที่ด้านล่าง (ขั้นต่ำ) ของช่วงตัวอักษรของตัวเอง VLOOKUP จะ "ค้นหา" ผลลัพธ์สำหรับ 60 (D) เป็นค่าโดยประมาณที่ใกล้เคียงที่สุดสำหรับค่าใดๆ ระหว่าง 60 ถึง 75
VLOOKUP ใน Excel เป็นฟังก์ชันที่ทรงพลังมากซึ่งมีให้ใช้งานมาเป็นเวลานาน นอกจากนี้ยังเป็นประโยชน์สำหรับการค้นหาค่าที่ตรงกันทุกที่ในเวิร์กบุ๊ก Excel
อย่างไรก็ตาม โปรดทราบว่าผู้ใช้ Microsoft ที่มีการสมัครใช้งาน Office 365 แบบรายเดือนจะสามารถเข้าถึงฟังก์ชัน XLOOKUP ที่ใหม่กว่าได้ ฟังก์ชันนี้มีพารามิเตอร์และความยืดหยุ่นเพิ่มเติม ผู้ใช้ที่สมัครใช้บริการรายครึ่งปีจะต้องรอให้การอัปเดตเปิดตัวในเดือนกรกฎาคม 2020
