การแมปข้อมูล (Mapping Data) เป็นส่วนสำคัญของ Excel ดังนั้น การรู้จักวิธีที่สะดวกสำหรับ การแมปข้อมูล จะช่วยประหยัดเวลามากและปรับปรุงเวิร์กโฟลว์ ด้วยความคิดนี้ บทความนี้สาธิต 4 วิธีที่มีประโยชน์ในการ แมปข้อมูล ใน Excel ด้วย VLOOKUP นอกจากนี้ เราจะพูดถึงวิธีการหาค่า Nth occurrence และการซ่อนข้อผิดพลาดโดยใช้ฟังก์ชัน VLOOKUP
คุณสามารถดาวน์โหลดสมุดงานฝึกฝนได้จากลิงก์ด้านล่าง
4 วิธีในการแมปข้อมูลโดยใช้ VLOOKUP ใน Excel
ในบทความนี้ เราจะรวมฟังก์ชัน VLOOKUP กับ MATCH, COUNTIF, INDIRECT, และ IF functions เพื่อ แมปข้อมูล มาเริ่มกันเลยดีกว่า!
ที่นี่เราใช้ Microsoft Excel 365 คุณอาจใช้เวอร์ชันอื่นตามสะดวก
วิธีที่ 1: ใช้ฟังก์ชัน VLOOKUP เพื่อแมปข้อมูลใน Excel
เริ่มต้นด้วยวิธีที่ชัดเจนที่สุด คือ การใช้ ฟังก์ชัน VLOOKUP เพื่อแมปข้อมูลใน Excel มาเริ่มกันเลย
พิจารณาชุดข้อมูลที่แสดงในเซลล์ B4:D14 ชุดข้อมูลนี้แสดงรายการ ID พนักงาน, ชื่อ และ แผนก ที่พวกเขาทำงาน
ขั้นตอน:
- ในตอนแรก ไปที่เซลล์ G5 และป้อนนิพจน์ด้านล่าง
=VLOOKUP(G4,B5:D14,3,FALSE)
ในสูตรนี้ เซลล์ G4 หมายถึง ID 1008 และช่วงเซลล์ B5:D14 แทนคอลัมน์ ID, Name, และ Department
การแยกวิเคราะห์สูตร:
- VLOOKUP(G4,B5:D14,3,FALSE) → ค้นหาค่าในคอลัมน์ซ้ายสุดของตาราง แล้วส่งคืนค่าในแถวเดียวกันจากคอลัมน์ที่คุณระบุ ที่นี่ G4 (อาร์กิวเมนต์ lookup_value) ถูกแมปจากอาร์เรย์ B5:D14 (อาร์กิวเมนต์ table_array) ต่อมา 3 (อาร์กิวเมนต์ col_index_num) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย FALSE (อาร์กิวเมนต์ range_lookup) หมายถึง Exact match ของค่าการค้นหา
- ผลลัพธ์ → Marketing
สุดท้าย ผลลัพธ์ควรจะเหมือนภาพด้านล่าง
วิธีที่ 2: แมปข้อมูลด้วยฟังก์ชัน VLOOKUP และ MATCH (Two-way VLOOKUP)
ในวิธีที่สอง เราจะรวมฟังก์ชัน VLOOKUP และ MATCH เพื่อแมปค่าที่จุดตัดของแถวและคอลัมน์เฉพาะ นี่ cònเรียกว่า Two-way VLOOKUP มาดูการทำงานกัน
สมมติเรามีชุดข้อมูล Sales List ในเซลล์ B4:E12 ที่นี่เรามีรายการ Items และจำนวน Units Sold ใน January, February, และ March
ขั้นตอน:
- เริ่มต้น เลือก Item และ Month เช่น เราเลือก Television และ March ตามลำดับ
- ไปที่เซลล์ H6 และป้อนสูตรด้านล่าง
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
ที่นี่ เซลล์ H4 และ H5 หมายถึง Item และ Month ตามลำดับ ในขณะที่ B5:E5 แทนส่วนหัวคอลัมน์
การแยกวิเคราะห์สูตร:
- MATCH(H5, B5:E5, 0) → ส่งคืนตำแหน่งสัมพัทธ์ของรายการในอาร์เรย์ที่ตรงกับค่าที่กำหนด ที่นี่ H5 เป็นอาร์กิวเมนต์ lookup_value ซึ่งหมายถึง เดือน มีนาคม ต่อมา B5:E5 แทนอาร์กิวเมนต์ lookup_array ที่ค่าถูกแมปจากที่นี่ สุดท้าย 0 เป็นอาร์กิวเมนต์ match_type ที่เป็นตัวเลือก ซึ่งระบุเกณฑ์ Exact match
- ผลลัพธ์ → 4
- VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) → กลายเป็น
- VLOOKUP(H4, B6:E10, 4, FALSE) → ที่นี่ H4 (อาร์กิวเมนต์ lookup_value) ถูกแมปจากอาร์เรย์ B6:E10 (อาร์กิวเมนต์ table_array) ต่อมา 4 (อาร์กิวเมนต์ col_index_num) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย FALSE (อาร์กิวเมนต์ range_lookup) หมายถึง Exact match ของค่าการค้นหา
- ผลลัพธ์ → 243
สุดท้าย ผลลัพธ์ของคุณควรจะเหมือนภาพด้านล่าง
วิธีที่ 3: ใช้ VLOOKUP และ COUNTIF เพื่อแมปข้อมูล
อีกวิธีหนึ่งในการ แมปข้อมูล ใน Excel คือการรวม ฟังก์ชัน COUNTIF ภายในฟังก์ชัน VLOOKUP มันง่ายและสะดวก เพียงทำตามขั้นตอนเหล่านี้
พิจารณาชุดข้อมูล Best Selling Books ในเซลล์ B4:C11 ในชุดข้อมูลนี้ เรามีชื่อ Best Seller และ Price ใน USD
ขั้นตอน:
- เริ่มต้น ไปที่เซลล์ F5 และป้อนนิพจน์ด้านล่าง
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
ในนิพจน์นี้ ช่วงเซลล์ B5:C9 แทนคอลัมน์ Best Seller Book และ Price ในขณะที่เซลล์ F4 หมายถึง Best Seller (ที่นี่คือ House of Wisdom)
การแยกวิเคราะห์สูตร:
- COUNTIF(B5:B9,F4) → นับจำนวนเซลล์ในช่วงที่ตรงตามเงื่อนไขที่กำหนด ที่นี่ B5:B9 เป็นอาร์กิวเมนต์ range ที่หมายถึง Best Seller Books ต่อมา F4 แทนอาร์กิวเมนต์ criteria ซึ่งส่งคืนจำนวนของค่าที่ตรงกัน
- ผลลัพธ์ → 1
- VLOOKUP(F4,B5:C9,2,TRUE) → ที่นี่ F4 (อาร์กิวเมนต์ lookup_value) ถูกแมปจากอาร์เรย์ B5:C9 (อาร์กิวเมนต์ table_array) ต่อมา 2 (อาร์กิวเมนต์ col_index_num) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย TRUE (อาร์กิวเมนต์ range_lookup) หมายถึง Approximate match ของค่าการค้นหา
- ผลลัพธ์ → 25
- IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) → กลายเป็น
- IF(1,25,0) → ตรวจสอบว่าเงื่อนไขเป็นจริงและส่งคืนค่าหนึ่งถ้า TRUE และอีกค่าหนึ่งถ้า FALSE ที่นี่ 1 เป็นอาร์กิวเมนต์ logical_test ซึ่งทำให้ฟังก์ชัน IF ส่งคืน 25 (อาร์กิวเมนต์ value_if_true) มิฉะนั้นจะส่งคืน 0 (อาร์กิวเมนต์ value_if_false)
- ผลลัพธ์ → $25
ผลลัพธ์ควรจะเหมือนภาพหน้าจอด้านล่าง
วิธีที่ 4: แมปข้อมูลโดยใช้ VLOOKUP และ INDIRECT ใน Excel
คุณยังสามารถ แมปข้อมูล ใน Excel ได้โดยการรวมฟังก์ชัน INDIRECT และ VLOOKUP มาดูขั้นตอนกัน
สมมติเรามีชุดข้อมูล Grocery List ในเซลล์ B4:I10 ชุดข้อมูลแสดง Price ของ Items เดียวกันใน 3 เมืองทั่ว US
ขั้นตอน:
- ก่อน tutto ไปที่เซลล์ B5 >> คลิกแท็บ Data >> กด Data Validation ดรอปดาวน์
จะเปิดกล่องโต้ตอบ Data Validation
- ที่สอง ในดรอปดาวน์ Allow เลือก List >> ในฟิลด์ Source เลือกเซลล์ B6:B10 >> คลิกปุ่ม OK
- ที่สาม เลือกเซลล์ B6:C10 >> ไปที่แท็บ Formulas >>ดับเบิลคลิก Define Name
จะเปิดวิซาร์ด Edit Name
- ที่นี่ ให้ป้อนชื่อที่เหมาะสม (ในกรณีนี้ Boston) สำหรับช่วงข้อมูล >> คลิกปุ่ม OK
ด้วยวิธีเดียวกัน ให้กำหนด Named Range สำหรับ Atlanta
เช่นกัน กำหนด Named Range สำหรับ Denver
- ที่สี่ ไปที่เซลล์ C14 >> ในแท็บ Data คลิกปุ่ม Data Validation
- เช่นเดียวกัน เลือกตัวเลือก List >> ป้อน Named Ranges ตามที่แสดงในภาพหน้าจอด้านล่าง
ตอนนี้ เลือก Item และ Location จากดรอปดาวน์ เช่น เราเลือก Tomato และ Atlanta ตามลำดับ
- หลังจากนั้น ในเซลล์ D14 ให้พิมพ์นิพจน์ต่อไปนี้
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
ที่นี่ เซลล์ B14 และ C14 ชี้ไปที่ Item และ Location ตามลำดับ
การแยกวิเคราะห์สูตร:
- INDIRECT(C14) → ส่งคืนอ้างอิงที่ระบุโดยสตริงข้อความ ที่นี่ C14 เป็นอาร์กิวเมนต์ ref_text ที่หมายถึง Named Range Boston
- VLOOKUP(B14,INDIRECT(C14),2,FALSE) → ที่นี่ B14 (อาร์กิวเมนต์ lookup_value) ถูกแมปจาก Named Range, INDIRECT(C14) ซึ่งเป็นอาร์กิวเมนต์ table_array ต่อมา 2 (อาร์กิวเมนต์ col_index_num) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย FALSE (อาร์กิวเมนต์ range_lookup) หมายถึง Exact match ของค่าการค้นหา
- ผลลัพธ์ → $1.2
หลังจากทำขั้นตอนข้างต้นเสร็จ ผลลัพธ์ควรจะเหมือนภาพด้านล่าง
นอกจากนี้ คุณอาจใช้วิธีนี้ในกรณีที่คุณมีข้อมูลใน หลายแผ่นงาน และต้องการ แมป จากแผ่นงานเฉพาะตามค่า lookup
การใช้ฟังก์ชัน VLOOKUP เพื่อหาค่า Nth Occurrence
สมมติคุณมีรายการ Stationery Sales ตามที่แสดงในเซลล์ B4:D13 ด้านล่าง ตอนนี้คุณต้องการทราบรายการที่ 2 หรือ 3 ที่ลูกค้าคนเดียวกันซื้อ ในการแก้ปัญหานี้ เราสามารถใช้ฟังก์ชัน COUNTIF และ VLOOKUP เพื่อได้ผลลัพธ์ มาดูรายละเอียดกระบวนการกัน
ขั้นตอน:
- ก่อน tutto แทรกคอลัมน์ใน Column B และเปลี่ยนชื่อส่วนหัวเป็น Helper Column >> ในเซลล์ B5 ป้อนสูตรด้านล่าง
=C5&COUNTIF($C$5:C5, C5)
📄 หมายเหตุ: โปรดแน่ใจว่าแทรก Helper Column ด้านซ้ายมสุดของชุดข้อมูล เนื่องจากโดยค่าเริ่มต้น ฟังก์ชัน VLOOKUP ค้นหาจากซ้ายไปขวา
ที่นี่ เซลล์ C5 หมายถึง Name John และฟังก์ชัน COUNTIF นับจำนวนครั้งของ John จากช่วงที่กำหนด $C$5:C5 สุดท้าย ตัวดำเนินการ Ampersand (&) รวมข้อความและตัวเลข
- ต่อไป ป้อน Name และ Instance เช่น ที่นี่คือ Julie และ 3 >> ไปที่เซลล์ H6 และพิมพ์นิพจน์ด้านล่าง
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
ในสูตรนี้ เซลล์ H4 และ H5 บ่งชี้ Name และ Instance
หลังจากทำขั้นตอนข้างต้นเสร็จ ผลลัพธ์ควรจะเหมือนภาพหน้าจอด้านล่าง
การใช้ VLOOKUP และ IF เพื่อซ่อนข้อผิดพลาด #N/A
คุณสามารถใช้ฟังก์ชัน ISNA และ VLOOKUP ใน ฟังก์ชัน IF เพื่อซ่อนข้อผิดพลาด #N/A เมื่อมีการให้ค่า lookup value ที่ไม่ถูกต้อง มาสาธิตกระบวนการด้านล่าง
ขั้นตอน:
- ในตอนแรก ไปที่เซลล์ G5 และป้อนนิพจน์ด้านล่าง
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
ในสูตรนี้ เซลล์ G4 หมายถึง ID 1008 และช่วงเซลล์ B5:D14 แทนคอลัมน์ ID, Name, และ Department
การแยกวิเคราะห์สูตร:
- ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → ตรวจสอบว่าค่าเป็น #NA และส่งคืน TRUE หรือ FALSE ที่นี่ G4 (อาร์กิวเมนต์ lookup_value) ถูกแมปจากอาร์เรย์ B5:D14 (อาร์กิวเมนต์ table_array) ต่อมา 3 (อาร์กิวเมนต์ col_index_num) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย FALSE (อาร์กิวเมนต์ range_lookup) หมายถึง Exact match ของค่าการค้นหา
- ผลลัพธ์ → FALSE
- IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE)) → กลายเป็น
- IF(FALSE, "",VLOOKUP(G4, B5:D14,3,FALSE)) → ตรวจสอบว่าเงื่อนไขเป็นจริงและส่งคืนค่าหนึ่งถ้า TRUE และอีกค่าหนึ่งถ้า ที่นี่ FALSE เป็นอาร์กิวเมนต์ logical_test ซึ่งทำให้ฟังก์ชัน IF ส่งคืนค่าจากฟังก์ชัน VLOOKUP (อาร์กิวเมนต์ value_if_true) มิฉะนั้นจะส่งคืนค่าว่าง "" (อาร์กิวเมนต์ value_if_false)
- ผลลัพธ์ → HR
ในที่สุด คุณควรได้ผลลัพธ์ตามภาพด้านล่าง
นอกจากนี้ หากเราป้อนหมายเลข ID ที่ไม่ถูกต้อง (ID 1012) สูตรจะส่งคืนค่าวางแทนข้อผิดพลาด #N/A ซึ่งแสดงในภาพหน้าจอด้านล่าง
ส่วนฝึกฝน
เราได้เตรียมส่วน Practice ไว้ด้านขวาของแต่ละแผ่น เพื่อให้คุณฝึกฝนได้ด้วยตัวเอง โปรดทำด้วยตัวเองแน่ๆ
สรุป
หวังว่าวิธีทั้งหมดเกี่ยวกับ วิธีแมปข้อมูลใน Excel โดยใช้ VLOOKUP จะกระตุ้นให้คุณนำไปใช้ในสเปรดชีต Excel ของคุณอย่างมีประสิทธิภาพมากขึ้น หากคุณมีคำถามหรือข้อเสนอแนะ โปรดแจ้งให้ทราบในส่วนความคิดเห็น หรือคุณสามารถดูบทความอื่นๆ ที่เกี่ยวข้องกับฟังก์ชัน Excel บนเว็บไซต์นี้ได้
