วิธีเปรียบเทียบสองคอลัมน์ใน Excel โดยใช้ VLOOKUP

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

VLOOKUP เป็นฟังก์ชันที่มีประสิทธิภาพที่ช่วยให้คุณสามารถค้นหาข้อมูลที่ตรงกันระหว่างสองคอลัมน์ ฟังก์ชันนี้ช่วยให้คุณค้นหาค่าในคอลัมน์หนึ่งซึ่งปรากฏในอีกคอลัมน์หนึ่งได้

บทความนี้แสดงวิธีเปรียบเทียบสองคอลัมน์โดยใช้ VLOOKUP เพื่อให้คุณสามารถวิเคราะห์ข้อมูลได้อย่างมีประสิทธิภาพ

สารบัญ

• พื้นฐานของฟังก์ชัน Vlookup
  • ฟังก์ชัน Vlookup
• การเตรียมข้อมูลของคุณสำหรับ VLOOKUP
  • การจัดรูปแบบข้อมูล
• วิธีใช้ VLOOKUP เพื่อเปรียบเทียบสองคอลัมน์
  • การจัดการข้อผิดพลาดอื่นๆ
• ทางเลือกแทน VLOOKUP สำหรับการเปรียบเทียบคอลัมน์
  • 1. การใช้ดัชนีและฟังก์ชันจับคู่
  • 2. การใช้ XLOOKUP ใน Excel
• ห้าเคล็ดลับในการปรับปรุงประสิทธิภาพ VLOOKUP
  • 1. จำกัดช่วงการค้นหาของคุณ
  • 2. ใช้การอ้างอิงแบบสัมบูรณ์
  • 3. จัดเรียงข้อมูลของคุณ
  • 4. ใช้ VLOOKUP สองครั้ง
  • 5. ใช้การจัดรูปแบบตามเงื่อนไข
• การใช้งานขั้นสูงสำหรับ VLOOKUP
• คำพูดสุดท้ายของเรา

พื้นฐานของฟังก์ชัน Vlookup

สมมติว่าคุณมีสเปรดชีตที่มีรายการสองรายการในคอลัมน์ A และคอลัมน์ B คุณต้องการค้นหารายการในรายการ 1 ที่ปรากฏในรายการ 2 ด้วย

คุณสามารถจินตนาการได้ว่าการทำงานด้วยตนเองผ่านรายการสินค้าหลายพันรายการจะใช้เวลามาก โชคดีที่Excelนำ VLOOKUP มาช่วย!

คำว่า VLOOKUP ย่อมาจาก Vertical Lookup ฟังก์ชันจะเปรียบเทียบสองคอลัมน์ ค้นหาข้อมูลที่ตรงกัน และส่งกลับค่าที่เกี่ยวข้อง

ฟังก์ชัน Vlookup

นี่คือไวยากรณ์พื้นฐานของฟังก์ชัน VLOOKUP:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

นี่คือรายละเอียดขององค์ประกอบ:

• lookup_value : ค่าที่คุณต้องการค้นหาในคอลัมน์แรกของ table_array

• table_array : ช่วงของเซลล์ที่มีข้อมูลที่คุณต้องการค้นหา

• col_index_num : หมายเลขคอลัมน์ใน table_array ที่คุณต้องการคืนค่า

• range_lookup : ไม่บังคับ อาจเป็น TRUE (การจับคู่โดยประมาณ) หรือ FALSE (การจับคู่แบบตรงทั้งหมด) ค่าเริ่มต้นคือ TRUE

การเตรียมข้อมูลของคุณสำหรับ VLOOKUP

ก่อนที่จะใช้ VLOOKUP เพื่อเปรียบเทียบสองคอลัมน์ใน Excelคุณต้องเตรียมข้อมูลของคุณ

สร้างสองคอลัมน์แยกกันในเวิร์กชีตของคุณซึ่งคุณต้องการเปรียบเทียบค่าต่างๆ บทความนี้ใช้คอลัมน์ A และคอลัมน์ B สำหรับตัวอย่างของเรา

นี่คือตัวอย่างข้อมูลที่ใช้ในบทความนี้:

นี่คือตารางตัวอย่างพร้อมส่วนหัวที่จะเพิ่มในส่วนที่ชื่อว่า “การเตรียมข้อมูลของคุณสำหรับ VLOOKUP”:

รายการที่ 1 รายการที่ 2
รายการที่ 1 รายการที่ 3
รายการที่ 2
รายการที่ 4 รายการที่ 3 รายการที่ 1
รายการที่ 4 รายการที่ 5

การจัดรูปแบบข้อมูล

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

ข้อผิดพลาดในการจับคู่อาจเกิดขึ้นในผลลัพธ์สุดท้ายหากการจัดรูปแบบไม่สอดคล้องกัน

นอกจากนี้ คุณควรลบค่าที่ซ้ำกันหรือเซลล์ว่างออกเพื่อลดความเสี่ยงของข้อผิดพลาด

หากต้องการลบค่าที่ซ้ำกัน:

1. เลือกคอลัมน์

2. ไปที่แท็บข้อมูลในริบบิ้นด้านบน

3. คลิกที่ Remove Duplicates ในส่วน Data Tools

ปุ่มอาจมองเห็นได้ยากเล็กน้อย ภาพนี้จะช่วยให้คุณ:

ในการลบเซลล์ว่าง:

1. เลือกคอลัมน์

2. ไปที่แท็บหน้าแรกในริบบิ้นด้านบน

3. ขยายเมนู "ค้นหาและเลือก"

4. เลือก "ไปที่รายการพิเศษ"

5. เลือก "ว่าง" จากตัวเลือก

6. คลิกที่ "ลบ" ในส่วนเซลล์

วิธีใช้ VLOOKUP เพื่อเปรียบเทียบสองคอลัมน์

เมื่อเตรียมข้อมูลแล้ว คุณสามารถเขียนสูตร VLOOKUP เพื่อเปรียบเทียบสองคอลัมน์ใน Excel และระบุข้อมูลที่ตรงกันได้ ทำตามขั้นตอนเหล่านี้:

1. เลือกเซลล์ในคอลัมน์ใหม่ที่คุณต้องการแสดงผลการเปรียบเทียบ (เช่น เซลล์ C2)

2. พิมพ์สูตรต่อไปนี้: =VLOOKUP(A2, B:B, 1, FALSE))

3. กด Enter เพื่อใช้สูตร

4. ในกรณีที่ค่าตรงกัน ค่าจะแสดงในแถวเดียวกันของคอลัมน์ผลลัพธ์ (เช่น คอลัมน์ C)

5. ลากสูตรลงจาก C2 เพื่อคัดลอกไปยังเซลล์จำนวนมากเท่าที่คุณต้องการ

หากคุณกำลังใช้ข้อมูลตัวอย่างของเรา ให้ใส่ผลการเปรียบเทียบของคุณในคอลัมน์ที่สาม สูตรจะค้นหารายการที่ตรงกันสามรายการจากสี่รายการในรายการที่ 1

โปรดสังเกตว่ารายการที่ 2 แสดงเป็น #N/A Excel กำลังบอกเราว่ารายการที่ตรงกันใช้ไม่ได้ นั่นคือไม่พบ

ถูกต้องและเป็นข้อมูลที่เป็นประโยชน์เกี่ยวกับค่าที่ขาดหายไป อย่างไรก็ตาม ผู้ใช้ Excel บางคนอาจคิดว่ามีปัญหาเกี่ยวกับข้อมูลหรือข้อผิดพลาดของฟังก์ชัน

เป็นแนวปฏิบัติที่ดีในการแสดงข้อบ่งชี้อื่นว่าไม่พบรายการที่ตรงกัน นั่นอาจเป็นเพียงแค่ความว่างเปล่า

ในการทำเช่นนั้น ให้รวมฟังก์ชัน VLOOKUP กับฟังก์ชัน IFNA ดังนี้:

=IFNA(VLOOKUP(A1, B:B, 1, FALSE), “”)

ฟังก์ชัน IFNA ตรวจพบข้อผิดพลาด #N/A และแทนที่เอาต์พุตด้วยช่องว่าง (“”) คุณยังสามารถใช้ฟังก์ชัน ISNA หรือมีตรรกะเพิ่มเติมด้วยฟังก์ชัน IF

การจัดการข้อผิดพลาดอื่นๆ

บางครั้ง VLOOKUP อาจทำให้เกิดข้อผิดพลาดอื่นๆ ในผลการเปรียบเทียบของคุณ #REF ! ข้อผิดพลาดใน Excel เป็นปัญหาที่แท้จริงกับข้อมูลของคุณ

มักเกิดขึ้นเมื่อช่วงที่คุณระบุไม่ถูกต้อง ตัวอย่างของเราอ้างอิงทั้งคอลัมน์ แต่คุณสามารถใช้ช่วงเซลล์แนวตั้งได้เช่นกัน

ตรวจสอบให้แน่ใจว่าช่วงการค้นหาที่คุณอ้างถึงครอบคลุมค่าทั้งหมดที่คุณต้องการเปรียบเทียบ

ทางเลือกแทน VLOOKUP สำหรับการเปรียบเทียบคอลัมน์

มีฟังก์ชันการค้นหาทางเลือกหลักสองฟังก์ชันเมื่อเปรียบเทียบสองคอลัมน์ใน Excel เพื่อค้นหารายการที่ตรงกัน

1. การใช้ดัชนีและฟังก์ชันจับคู่

แทนที่จะใช้ VLOOKUP คุณสามารถเปรียบเทียบสองคอลัมน์ใน Excel ได้โดยการรวมฟังก์ชัน INDEX และ MATCH

วิธีนี้เป็นวิธีที่ยืดหยุ่นมากขึ้นในการค้นหาข้อมูล และมีประโยชน์อย่างยิ่งเมื่อทำงานกับคอลัมน์ที่ไม่ติดกันหรือเมื่อดัชนีคอลัมน์อาจเปลี่ยนแปลง

นี่คือไวยากรณ์ที่จะใส่ในคอลัมน์ผลลัพธ์:

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Return_range : ช่วงของเซลล์ที่มีข้อมูลที่คุณต้องการส่งคืน

• lookup_value : ค่าที่คุณต้องการค้นหาภายในช่วงการค้นหา

• lookup_range : ช่วงของเซลล์ที่คุณต้องการค้นหาค่าการค้นหา

ใช้ข้อมูลเดียวกันในตัวอย่างก่อนหน้านี้ เราแทนที่สูตร VLOOKUP ดังนี้:

=(ดัชนี(B:B, ตรงกัน(A2, B:B, 0)))

ฟังก์ชัน MATCH จะส่งกลับตำแหน่งสัมพัทธ์ของค่าการค้นหาภายในช่วงการค้นหา และฟังก์ชัน INDEX จะส่งกลับค่าที่สอดคล้องกันจากช่วงที่ส่งกลับ

ผลลัพธ์จากข้อมูลเดียวกันจะเหมือนกับ VLOOKUP ที่เราใช้ก่อนหน้านี้ รูปนี้แสดงฟังก์ชั่นการใช้งาน:

คุณยังสามารถแทนที่ข้อผิดพลาด #N/A ด้วยข้อความที่กำหนดเอง นี่คือตัวอย่างการใช้ฟังก์ชัน IFERROR

=IFERROR(INDEX(B:B, MATCH(A2, B:B, 0)), “ไม่พบ”)

2. การใช้ XLOOKUP ใน Excel

สำหรับผู้ใช้ที่มี Excel 365 หรือ Excel 2019 XLOOKUP คือการทดสอบเชิงตรรกะทางเลือกสำหรับ VLOOKUP สำหรับการค้นหาค่าทั่วไปในสองคอลัมน์

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

ไวยากรณ์ของ XLOOKUP คือ:

=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

• lookup_value : ค่าที่คุณต้องการค้นหาภายในช่วงการค้นหา

• lookup_range : ช่วงของเซลล์ที่คุณต้องการค้นหาค่าการค้นหา

• return_range : ช่วงของเซลล์ที่มีข้อมูลที่คุณต้องการส่งคืน

• เพิ่มค่าความผิดพลาดที่กำหนดเอง โหมดการจับคู่ และพารามิเตอร์โหมดการค้นหาตามต้องการ

XLOOKUP จะค้นหาค่าการค้นหาภายในช่วงการค้นหาและส่งกลับค่าที่สอดคล้องกันจากช่วงส่งคืน

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

นี่คือตัวอย่างที่มีสองรายการในคอลัมน์แรกและคอลัมน์ที่สามของสเปรดชีต:

สูตร XLOOKUP มีลักษณะดังนี้:

=XLOOKUP(A2,C:C,C:C,””)

รูปภาพนี้แสดงผลลัพธ์พร้อมการเปรียบเทียบในคอลัมน์ D ค่าแรกมีอยู่แต่ค่าที่สองหายไป

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

ฉันจะแสดงวิธีใช้ XLOOKUP เพื่อเปรียบเทียบระหว่างแถวเป็นโบนัส หากคุณมีสองรายการที่อยู่ในแถวที่ 1 และแถวที่ 2 การใช้สูตรที่ง่ายที่สุดจะมีลักษณะดังนี้:

=XLOOKUP(B1, 2:2, 2:2, “”)

รูปนี้แสดงผลสองแถว

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

ห้าเคล็ดลับในการปรับปรุงประสิทธิภาพ VLOOKUP

การปรับฟังก์ชัน VLOOKUP ให้เหมาะสมจะช่วยให้คุณไม่ต้องเสียเวลารอนาน และปรับปรุงการตอบสนองโดยรวมของเวิร์กชีต Excel ของคุณ

ต่อไปนี้คือเคล็ดลับและคำแนะนำ 6 ข้อที่คุณสามารถนำไปใช้เพื่อปรับปรุงประสิทธิภาพของ VLOOKUP ใน Excel

1. จำกัดช่วงการค้นหาของคุณ

ฉันใช้ทั้งคอลัมน์ในตัวอย่างเพื่อทำให้ง่าย หากคุณกำลังทำงานกับข้อมูลจำนวนมาก คุณควรหลีกเลี่ยงการทำเช่นนั้น การใช้ทั้งคอลัมน์อาจทำให้กระบวนการคำนวณของ Excel ช้าลง

ให้ลองใช้ช่วงที่จำเป็นสำหรับข้อมูลของคุณแทน (เช่น A1:A100) ซึ่งจะลดจำนวนเซลล์ที่ฟังก์ชัน VLOOKUP ของคุณต้องประเมิน

2. ใช้การอ้างอิงแบบสัมบูรณ์

เมื่อระบุช่วงข้อมูล (เช่น เซลล์ตั้งแต่ B2 ถึง B5) ให้ใช้การอ้างอิงแบบสัมบูรณ์ วิธีนี้ช่วยให้แน่ใจว่าสูตรนั้นสอดคล้องกันและมีข้อมูลอยู่เมื่อคุณคัดลอกข้ามเซลล์หลายๆ เซลล์

นี่คือตัวอย่าง:

=VLOOKUP(A2, $B$2:$B$5, 1, เท็จ)

3. จัดเรียงข้อมูลของคุณ

หากคุณทราบว่าข้อมูลในคอลัมน์การค้นหาเรียงจากน้อยไปมาก คุณสามารถใช้ VLOOKUP กับ TRUE หรือ 1 สำหรับอาร์กิวเมนต์ 'range_lookup'

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

4. ใช้ VLOOKUP สองครั้ง

คุณสามารถใช้สองฟังก์ชัน VLOOKUP เพื่อเร่งกระบวนการค้นหาใน Excel

VLOOKUP แรกจะพิจารณาว่ามีค่าการค้นหาหรือไม่ โดยการตั้งค่า 'col_index_num' เป็น 1 และ 'range_lookup' เป็น TRUE

หากคืนค่าเป็น TRUE VLOOKUP ที่สองจะดึงค่าที่ต้องการโดยตั้งค่า 'range_lookup' เป็น TRUE

5. ใช้การจัดรูปแบบตามเงื่อนไข

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

คุณจะพบเมนูการจัดรูปแบบตามเงื่อนไขในกลุ่มลักษณะบนแท็บหน้าแรก

การใช้งานขั้นสูงสำหรับ VLOOKUP

การทดสอบ T-test ตัวอย่างที่จับคู่ใช้เพื่อเปรียบเทียบค่าเฉลี่ยของตัวอย่างที่เกี่ยวข้องกันสองตัวอย่าง วิดีโอนี้แสดงการใช้งานใน Excel

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

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

คำพูดสุดท้ายของเรา

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

คุณยังเห็นตัวอย่างฟังก์ชันการค้นหาและการอ้างอิงอื่นๆ เช่น XLOOKUP, INDEX และ MATCH การเพิ่มองค์ประกอบเหล่านี้ในทักษะของคุณจะทำให้ความสามารถในการวิเคราะห์ข้อมูลของคุณแข็งแกร่งยิ่งขึ้น

หมั่นฝึกฝนและปรับปรุงทักษะ VLOOKUP ของคุณ แล้วคุณจะกลายเป็นผู้เชี่ยวชาญในการเปรียบเทียบคอลัมน์ใน Excel ในไม่ช้า ประหยัดเวลาและเพิ่มประสิทธิภาพการทำงานของคุณ