ข้อมูลแผนที่ เป็นส่วนสำคัญของ Excel . ดังนั้น การรู้สองวิธีที่สะดวกในการข้อมูลแผนที่ สามารถประหยัดเวลาได้มากและปรับปรุงเวิร์กโฟลว์ เมื่อคำนึงถึงสิ่งนี้ บทความนี้จะแสดงให้เห็น 4 วิธีที่เป็นประโยชน์ในการแมปข้อมูล ใน Excel VLOOKUP . นอกจากนี้ เรายังจะหารือเกี่ยวกับวิธีการรับค่า Nth ที่เกิดขึ้น และซ่อนข้อผิดพลาดโดยใช้ VLOOKUP ฟังก์ชัน
คุณสามารถดาวน์โหลดสมุดแบบฝึกหัดได้จากลิงค์ด้านล่าง
4 วิธีในการจับคู่ข้อมูลโดยใช้ VLOOKUP ใน Excel
ในบทความนี้ เราจะรวม VLOOKUP ฟังก์ชันด้วย MATCH , COUNTIF , ทางอ้อม และ ฟังก์ชัน IF ไปยังข้อมูลแผนที่ . ดังนั้นอย่ารอช้า มาดำดิ่งกัน!
ที่นี่เราใช้ Microsoft Excel 365 คุณสามารถใช้เวอร์ชันอื่นได้ตามสะดวก
วิธีที่-1 :การใช้ฟังก์ชัน VLOOKUP เพื่อแมปข้อมูลใน Excel
มาเริ่มกันด้วยวิธีที่ชัดเจนที่สุดคือการใช้ฟังก์ชัน VLOOKUP เพื่อแมปข้อมูลใน Excel เริ่มกันเลย
พิจารณาชุดข้อมูลที่แสดงใน B4:D14 เซลล์. ที่นี่ ชุดข้อมูลแสดงรายการของพนักงาน ID s ชื่อ . ของพวกเขา และ แผนก ที่ทำงาน
ขั้นตอน :
- ในตอนเริ่มต้น ให้ไปที่ G5 เซลล์แล้วป้อนนิพจน์ด้านล่าง
=VLOOKUP(G4,B5:D14,3,FALSE)
ในสูตรนี้ G4 เซลล์หมายถึง ID 1008 และ B5:D14 ช่วงของเซลล์แสดงถึง ID , ชื่อ , และ แผนก คอลัมน์
รายละเอียดสูตร:
- VLOOKUP(G4,B5:D14,3,FALSE) → ค้นหาค่าในคอลัมน์ซ้ายสุดของตาราง แล้วส่งกลับค่าในแถวเดียวกันจากคอลัมน์ที่คุณระบุ ที่นี่ G4 ( lookup_value อาร์กิวเมนต์) ถูกแมปจาก B5:D14 (table_array อาร์กิวเมนต์) อาร์เรย์ ต่อไป 3 (col_index_num อาร์กิวเมนต์) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย FALSE (range_lookup อาร์กิวเมนต์) หมายถึง การจับคู่แบบตรงทั้งหมด ของค่าการค้นหา
- ผลลัพธ์ → การตลาด
สุดท้าย ผลลัพธ์ควรมีลักษณะเหมือนภาพด้านล่าง
วิธีที่ 2 :การทำแผนที่ข้อมูลด้วยฟังก์ชัน VLOOKUP และ MATCH (VLOOKUP สองทาง)
ในวิธีที่ 2 เราจะรวม VLOOKUP และ MATCH ทำหน้าที่จับคู่ค่าที่จุดตัดของแถวและคอลัมน์เฉพาะ สิ่งนี้เรียกอีกอย่างว่า VLOOKUP สองทาง . มาดูการทำงานกัน
สมมติว่าเรามี รายการขาย ชุดข้อมูลที่แสดงใน B4:E12 เซลล์. เรามีรายการรายการ และจำนวน หน่วยที่ขาย ใน มกราคม , กุมภาพันธ์ และ มีนาคม .
ขั้นตอน :
- เริ่มแรก เลือก รายการ และ เดือน ตัวอย่างเช่น เราได้เลือก โทรทัศน์ และ มีนาคม ตามลำดับ
- ต่อไป ไปที่ H6 เซลล์และป้อนสูตรที่กำหนดด้านล่าง
=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)
ที่นี่ H4 และ H5 เซลล์อ้างถึง รายการ และ เดือน ตามลำดับ ในขณะที่ B5:E5 แสดงถึงส่วนหัวของคอลัมน์
รายละเอียดสูตร:
- MATCH(H5, B5:E5, 0) → ส่งกลับตำแหน่งสัมพัทธ์ของรายการในอาร์เรย์ที่ตรงกับค่าที่กำหนด ที่นี่ H5 คือ lookup_value อาร์กิวเมนต์ที่อ้างถึง เดือนมีนาคม . กำลังติดตาม B5:E5 แสดงถึง lookup_array อาร์กิวเมนต์จากตำแหน่งที่จับคู่ค่า ช่วงนี้ 0 เป็นตัวเลือก match_type อาร์กิวเมนต์ซึ่งระบุ ตรงทั้งหมด เกณฑ์
- เอาต์พุต → 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 อาร์กิวเมนต์) หมายถึง การจับคู่แบบตรงทั้งหมด ของค่าการค้นหา
- เอาต์พุต → 243
สุดท้าย ผลลัพธ์ของคุณควรมีลักษณะตามภาพด้านล่าง
วิธีที่-3 :การใช้ฟังก์ชัน VLOOKUP และ COUNTIF เพื่อทำแผนที่ข้อมูล
อีกวิธีในการข้อมูลแผนที่ ใน Excel คือการรวม ฟังก์ชัน COUNIF ภายใน VLOOKUP การทำงาน. ง่ายและสะดวก เพียงทำตามขั้นตอนเหล่านี้
ลองพิจารณา หนังสือขายดี ชุดข้อมูลที่แสดงใน B4:C11 เซลล์. ในชุดข้อมูลนี้ เรามีชื่อ สินค้าขายดี และ ราคา คือ USD ตามลำดับ
ขั้นตอน :
- เพื่อเริ่มต้น ให้ย้ายไปที่ F5 เซลล์แล้วป้อนนิพจน์ด้านล่าง
=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)
ในนิพจน์นี้ B5:C9 ช่วงของเซลล์แสดงถึงหนังสือขายดี และ ราคา คอลัมน์ ในทางตรงกันข้าม F4 เซลล์หมายถึง สินค้าขายดี (ในที่นี้คือ House of Wisdom )
รายละเอียดสูตร:
- COUNTIF(B5:B9,F4) → นับจำนวนเซลล์ภายในช่วงที่ตรงตามเงื่อนไขที่กำหนด ที่นี่ B5:B9 คือ ช่วง อาร์กิวเมนต์ที่อ้างถึง หนังสือขายดี . กำลังติดตาม F4 แสดงถึง เกณฑ์ อาร์กิวเมนต์ที่ส่งคืนการนับของค่าที่ตรงกัน
- ผลลัพธ์ → 1
- VLOOKUP(F4,B5:C9,2,TRUE) → ที่นี่ F4 ( lookup_value อาร์กิวเมนต์) ถูกแมปจาก B5:C9 (table_array อาร์กิวเมนต์) อาร์เรย์ ต่อไป 2 (col_index_num อาร์กิวเมนต์) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย จริง (range_lookup อาร์กิวเมนต์) หมายถึง การจับคู่โดยประมาณ ของค่าการค้นหา
- เอาต์พุต → 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 โดยการรวม ทางอ้อม และ VLOOKUP ฟังก์ชั่น. ทีนี้มาดูขั้นตอนกัน
สมมติว่าเรามี รายการของชำ ชุดข้อมูลที่แสดงใน B4:I10 เซลล์. ชุดข้อมูลแสดง ราคา ของรายการเดียวกัน ใน 3 เมืองต่างๆ ใน สหรัฐอเมริกา .
ขั้นตอน :
- ขั้นแรก ไปที่ B5 เซลล์>> คลิกปุ่ม ข้อมูล แท็บ>> จากนั้นกดปุ่ม การตรวจสอบข้อมูล เมนูแบบเลื่อนลง
ซึ่งจะเปิดการตรวจสอบความถูกต้องของข้อมูล กล่องโต้ตอบ
- ประการที่สอง ใน อนุญาต แบบเลื่อนลง เลือก รายการ ตัวเลือก>> ใน แหล่งที่มา ฟิลด์ เลือก B6:B10 เซลล์>> คลิกปุ่ม ตกลง ปุ่ม.
- ประการที่สาม เลือก B6:C10 เซลล์>> ไปที่ สูตร แท็บ>> ดับเบิลคลิกที่ กำหนดชื่อ ตัวเลือก
ซึ่งจะเป็นการเปิด แก้ไขชื่อ ตัวช่วยสร้าง
- ที่นี่ ป้อนชื่อที่เหมาะสม (ในกรณีนี้คือ Boston ) สำหรับช่วงข้อมูล>> คลิก ตกลง ปุ่ม.
ในทำนองเดียวกัน ให้กำหนด ช่วงที่มีชื่อ สำหรับ แอตแลนตา .
ในทำนองเดียวกัน ให้กำหนด ช่วงที่มีชื่อ สำหรับ เดนเวอร์ .
- ประการที่สี่ ไปที่ C14 เซลล์>> บน แท็บข้อมูล และคลิก การตรวจสอบข้อมูล ปุ่ม.
- ในทำนองเดียวกัน เลือก รายการ ตัวเลือก>> ป้อน ช่วงที่มีชื่อ ตามที่แสดงในภาพหน้าจอด้านล่าง
ตอนนี้ เลือก รายการ และ ที่ตั้ง จากเมนูแบบเลื่อนลง ตัวอย่างเช่น เราได้เลือก มะเขือเทศ และ แอตแลนตา ตามลำดับ
- หลังจากนั้น ใน D14 ชนิดเซลล์ในนิพจน์ต่อไปนี้
=VLOOKUP(B14,INDIRECT(C14),2,FALSE)
ที่นี่ B14 และ C14 เซลล์ชี้ไปที่ รายการ และ ที่ตั้ง ตามลำดับ
รายละเอียดสูตร:
- ทางอ้อม (C14) → ส่งคืนการอ้างอิงที่ระบุโดยสตริงข้อความ ที่นี่ C14 คือ ref_text อาร์กิวเมนต์ที่อ้างถึง ช่วงที่มีชื่อ บอสตัน .
- VLOOKUP(B14,INDIRECT(C14),2,FALSE) → ที่นี่ B14 ( lookup_value อาร์กิวเมนต์) ถูกแมปจาก ช่วงที่มีชื่อ ทางอ้อม(C14) นั่นคือ table_array การโต้แย้ง. ต่อไป 2 (col_index_num อาร์กิวเมนต์) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย FALSE (range_lookup อาร์กิวเมนต์) หมายถึง การจับคู่แบบตรงทั้งหมด ของค่าการค้นหา
- ผลลัพธ์ → $1.2
ต่อจากนี้ หลังจากทำตามขั้นตอนข้างต้นแล้ว ผลลัพธ์ควรมีลักษณะดังภาพด้านล่าง
นอกจากนี้ คุณอาจใช้วิธีนี้ในกรณีที่คุณมีข้อมูลในแผ่นงานหลายแผ่น และคุณต้องการแผนที่ จากแผ่นงานเฉพาะตาม การค้นหา ค่า
การใช้ฟังก์ชัน VLOOKUP เพื่อรับ Nth Occurrence
สมมติว่าคุณมี ขายเครื่องเขียน รายการตามที่แสดงใน B4:D13 เซลล์ด้านล่าง ตอนนี้ คุณต้องการทราบรายการที่ 2 หรือ 3 ที่ซื้อโดยลูกค้ารายเดียวกัน เพื่อแก้ปัญหานี้ เราสามารถใช้ COUNTIF และ VLOOKUP ฟังก์ชั่นเพื่อให้ได้ผลลัพธ์ มาดูขั้นตอนกันเลยดีกว่า
ขั้นตอน :
- ขั้นแรก แทรกคอลัมน์ใน คอลัมน์ B และเปลี่ยนชื่อส่วนหัวเป็น คอลัมน์ตัวช่วย>> ใน B5 เซลล์ ป้อนสูตรที่กำหนดด้านล่าง
=C5&COUNTIF($C$5:C5, C5)
📄 หมายเหตุ: โปรดตรวจสอบให้แน่ใจว่าได้แทรกคอลัมน์ Helper ที่ด้านซ้ายสุดของชุดข้อมูล เนื่องจากโดยค่าเริ่มต้น VLOOKUP ฟังก์ชั่นมองจากซ้ายไปขวา
ที่นี่ C5 เซลล์หมายถึง ชื่อ จอห์น และ COUNTIF ฟังก์ชั่นนับการเกิดของ John จากช่วงที่กำหนด $C$5:C5 . สุดท้าย เครื่องหมาย (&) โอเปอเรเตอร์รวมข้อความและตัวเลข
- ถัดไป ป้อน ชื่อ และ อินสแตนซ์ ตัวอย่างเช่น นี่คือ Julie , และ 3>> ไปที่ H6 เซลล์และพิมพ์นิพจน์ด้านล่าง
=VLOOKUP(H4&H5, B5:E13, 3, FALSE)
ในสูตรนี้ H4 และ H5 เซลล์ระบุ ชื่อ และ อินสแตนซ์ .
หลังจากทำตามขั้นตอนข้างต้นแล้ว ผลลัพธ์ควรมีลักษณะเหมือนภาพหน้าจอที่แสดงด้านล่าง
การใช้ฟังก์ชัน VLOOKUP และ IF เพื่อซ่อนข้อผิดพลาด #N/A
คุณสามารถใช้ ISNA และ VLOOKUP ฟังก์ชันใน ฟังก์ชัน IF เพื่อซ่อน #N/A ข้อผิดพลาดเมื่อ ค่าการค้นหาที่ไม่ถูกต้อง จะได้รับ ตอนนี้ให้ฉันสาธิตกระบวนการด้านล่าง
ขั้นตอน :
- ในตอนเริ่มต้น ให้ไปที่ G5 เซลล์แล้วป้อนนิพจน์ด้านล่าง
=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))
ในสูตรนี้ G4 เซลล์หมายถึง ID 1008 และ B5:D14 ช่วงของเซลล์แสดงถึง ID , ชื่อ , และ แผนก คอลัมน์
รายละเอียดสูตร:
- ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → ตรวจสอบว่าค่าเป็น #NA และส่งคืน TRUE หรือ เท็จ . ที่นี่ G4 ( lookup_value อาร์กิวเมนต์) ถูกแมปจาก B5:D14 (table_array อาร์กิวเมนต์) อาร์เรย์ ต่อไป 3 (col_index_num อาร์กิวเมนต์) แทนหมายเลขคอลัมน์ของค่าการค้นหา สุดท้าย FALSE (range_lookup อาร์กิวเมนต์) หมายถึง การจับคู่แบบตรงทั้งหมด ของค่าการค้นหา
- ผลลัพธ์ → เท็จ
- IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), “”,VLOOKUP(G4, B5:D14,3,FALSE)) → กลายเป็น
- IF(FALSE, “”,VLOOKUP(G4, B5:D14,3,FALSE)) → ตรวจสอบว่าตรงตามเงื่อนไขและคืนค่าหนึ่งค่าหาก TRUE และค่าอื่นถ้า ที่นี่ เท็จ คือ logical_test อาร์กิวเมนต์ที่พร้อมท์ IF ฟังก์ชันส่งกลับค่าจาก VLOOKUP ฟังก์ชัน (value_if_true อาร์กิวเมนต์) มิฉะนั้นจะส่งกลับค่าว่าง “” (value_if_false อาร์กิวเมนต์)
- ผลลัพธ์ → HR
ในที่สุดคุณควรได้ผลลัพธ์ที่แสดงในภาพด้านล่าง
นอกจากนี้ หากเราป้อน ID . ที่ไม่ถูกต้อง หมายเลข (ID 1012 ) สูตรจะส่งกลับค่าว่างแทน #N/A ข้อผิดพลาดที่แสดงในภาพหน้าจอด้านล่าง
ภาคปฏิบัติ
เราได้จัดเตรียมการปฏิบัติ ส่วนทางด้านขวาของแต่ละแผ่นเพื่อให้คุณสามารถฝึกฝนตัวเองได้ โปรดดำเนินการด้วยตนเอง
บทสรุป
ฉันหวังว่าวิธีการทั้งหมดในวิธีการแมปข้อมูลใน Excel โดยใช้ VLOOKUP จะแจ้งให้คุณนำไปใช้ในสเปรดชีต Excel ของคุณได้อย่างมีประสิทธิภาพมากขึ้น หากคุณมีคำถามหรือข้อเสนอแนะโปรดแจ้งให้เราทราบในส่วนความคิดเห็น หรือคุณสามารถอ่านบทความอื่นๆ ของเราเกี่ยวกับฟังก์ชัน Excel ได้ในเว็บไซต์นี้
