เชี่ยวชาญ Excel: 3 วิธีที่พิสูจน์แล้วในการค้นหาข้อมูลข้ามหลายแผ่นงาน

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

ดาวน์โหลดสมุดงานฝึกหัด

แนะนำให้ดาวน์โหลดไฟล์ Excel และฝึกปฏิบัติตามบทความนี้

3 วิธีในการค้นหาข้ามหลายแผ่นงานใน Excel

สมมติว่า ร้านหนังสือขายหนังสือทั้งออนไลน์และในร้าน พวกเขามีรายการหนังสือ 2 รายการ ที่แสดงหนังสือที่พร้อมขายออนไลน์และหนังสือที่พร้อมขายในร้าน

ในบทช่วยสอนนี้ เราจะสอนว่าคุณสามารถรวมรายการหนังสือ 2 รายการนี้และสร้างรายการหนังสือใหม่ที่สมบูรณ์ได้อย่างไร โดยใช้ 3 วิธีที่แตกต่างกัน

1. ค้นหาข้ามหลายแผ่นงานใน Excel ด้วย IFERROR

เพื่อสร้างรายการหนังสือที่สมบูรณ์ซึ่งมีหนังสือที่พร้อมขายทั้งออนไลน์และในร้าน เราต้องรวมข้อมูลทั้งจากแผ่นงาน "Store" และ "Online"

ตอนนี้ทำตามขั้นตอนด้านล่างเพื่อเรียนรู้วิธีการค้นหา:

🔗 ขั้นตอน:

❶ ก่อนอื่น เลือก เซลล์ C5 ▶ เพื่อเก็บผลลัพธ์ของสูตร

❷ หลังจากนั้น พิมพ์ สูตรต่อไปนี้:

❸ จากนั้นกดปุ่ม ENTER

❹ ตอนนี้ลากไอคอน Fill Handle ไปจนถึงท้ายคอลัมน์ Book Name

เสร็จแล้ว

💡 เพื่อทำให้คอลัมน์ Author สมบูรณ์ ให้ใช้สูตร

␥ การแยกวิเคราะห์สูตร

📌 Syntax: IFERROR(VLOOKUP(…), IFERROR(VLOOKUP(…), …, "Not found"))

• B5 ▶ เลือก ID ซึ่งจะทำงานเป็นคีย์ค้นหา
• Store!$B$5:$D$9 ▶ การค้นหาดำเนินการภายในช่วงจาก B5 ถึง D9 ในแผ่นงาน Store
• Online!$B$5:$D$9 ▶ การค้นหาดำเนินการภายในช่วงจาก B5 ถึง D9 ในแผ่นงาน Online
• 2 ▶ ระบุคอลัมน์ Book Name เพื่อดึงชื่อหนังสือ
• FALSE ▶ อาร์กิวเมนต์นี้ใช้สำหรับการจับคู่ที่แน่นอนขณะค้นหา
• =IFERROR(VLOOKUP(B5,Store!$B$5:$D$9,2, FALSE), IFERROR(VLOOKUP(B5,Online!$B$5:$D$9, 2, FALSE), "Not found")) ▶ ส่งคืนชื่อหนังสือพร้อมหมายเลข ID เลข 96

อ่านเพิ่มเติม: ค้นหาและคืนค่าหลายค่ารวมเป็นเซลล์เดียวใน Excel

2. Excel Vlookup หลายแผ่นงานด้วย INDIRECT

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

🔗 ขั้นตอน:

❶ ก่อนอื่น เลือก เซลล์ C5 ▶ เพื่อเก็บผลลัพธ์ของสูตร

❷ หลังจากนั้น พิมพ์ สูตร

❸ จากนั้นกดปุ่ม ENTER

❹ ตอนนี้ลากไอคอน Fill Handle ไปจนถึงท้ายคอลัมน์ Book Name

เสร็จแล้ว

💡 เพื่อทำให้คอลัมน์ Author สมบูรณ์ ให้ใช้สูตร

␥ การแยกวิเคราะห์สูตร

📌 Syntax: VLOOKUP(lookup_value, INDIRECT("'"&INDEX(Lookup_sheets, MATCH(TRUE, --(COUNTIF(INDIRECT("'"& Lookup_sheets & "'!lookup_range"), lookup_value)>0), 0)) & "'!table_array"), col_index_num, FALSE)

• Lookup_value ▶ $B5 ▶ คีย์ค้นหาที่เราดำเนินการค้นหาตาม
• Lookup_sheets ▶ $F$5:$F$6 ▶ ที่อยู่เซลล์ของรายการแผ่นงานที่เรามักค้นหาข้อมูล
• Lookup_range ▶ $B5:$B9 ▶ ช่วงที่ค่าค้นหาของเราอยู่
• Table_array ▶ $B$5:$D$9 ▶ ช่วงของตารางข้อมูลทั้งหมด
• Column_index_number ▶ 2 ▶ หมายเลขคอลัมน์ที่เราดึงข้อมูลที่ต้องการออกมา

อ่านเพิ่มเติม: VBA INDEX MATCH ตามเกณฑ์หลายอย่างใน Excel (3 วิธี)

3. ค้นหาข้ามหลายแผ่นงานใน Excel โดยใช้ฟังก์ชัน IF 巢狀

มีอีกวิธีหนึ่งในการค้นหาข้ามหลายแผ่นงานใน Excel นั่นคือการใช้ ฟังก์ชัน IF 巢狀 ร่วมกับ ISNA และ VLOOKUP

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

อย่างไรก็ตาม ทำตามขั้นตอนด้านล่างเพื่อเรียนรู้ว่าสูตรทำงานอย่างไร ที่นี่คุณไป:

🔗 ขั้นตอน:

❶ ก่อนอื่น เลือก เซลล์ C5 ▶ เพื่อเก็บผลลัพธ์ของสูตร

❷ หลังจากนั้น พิมพ์ สูตร

❸ จากนั้นกดปุ่ม ENTER

❹ ตอนนี้ลากไอคอน Fill Handle ไปจนถึงท้ายคอลัมน์ Book Name

เสร็จแล้ว

💡 เพื่อทำให้คอลัมน์ Author สมบูรณ์ ให้ใช้สูตร

␥ การแยกวิเคราะห์สูตร

📌 Syntax: IF(ISNA(VLOOKUP(lookup_value,table_array,col_index_number,0)),value_if_true,value_if_false)

• Lookup_value ▶ $B5 ▶ คีย์ค้นหาที่เราดำเนินการค้นหาตาม
• Table_array ▶ $B$5:$D$9 ▶ ช่วงของตารางข้อมูลทั้งหมด
• Column_index_number ▶ 2 ▶ หมายเลขคอลัมน์ที่เราดึงข้อมูลที่ต้องการออกมา
• ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)) ▶ ค้นหาการจับคู่ข้ามของ ID (ที่อ้างอิงโดย $B5) ภายในช่วง $B$5:$D$9.
• IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) ▶ หาก ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)) เป็นจริง แล้วชื่อหนังสือที่ตรงกันจะถูกดึงออกมาโดยใช้ VLOOKUP($B5,Online!$B$5:$D$9,3,0).
• หากส่วน IF(ISNA(VLOOKUP($B5,Store!$B$5:$D$9,3,0)),VLOOKUP($B5,Online!$B$5:$D$9,3,0) กลายเป็นเท็จ เราจะเข้าสู่ IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)).
• IF(ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)),VLOOKUP($B5,Store!$B$5:$D$9,3,0)) ▶ หาก ISNA(VLOOKUP($B5,Online!$B$5:$D$9,3,0)) กลายเป็นจริง แล้วเราจะดึงชื่อหนังสือโดยใช้ VLOOKUP($B5,Store!$B$5:$D$9,3,0)).

อ่านเพิ่มเติม: VLOOKUP พร้อมเกณฑ์หลายอย่างรวมถึงช่วงวันที่ใน Excel (2 วิธี)

สิ่งที่ควรจดจำ

📌 ค่าค้นหา ควรอยู่ใน คอลัมน์แรก ภายใน table array เสมอ

📌 ใช้ Ctrl + Shift + Enter พร้อมกันเพื่อเสร็จสิ้น สูตรอาร์เรย์

📌 ระวังเรื่อง ไวยากรณ์ ของ ฟังก์ชัน

📌 ใส่ช่วง ข้อมูล อย่างระมัดระวังลงใน สูตร

สรุป

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

บทความเพิ่มเติมที่น่าสนใจ

• Vlookup พร้อมเกณฑ์หลายอย่างโดยไม่มีคอลัมน์ช่วยใน Excel (5 วิธี)
• วิธีใช้ INDEX MATCH พร้อมเกณฑ์หลายอย่างสำหรับช่วงวันที่
• INDEX-MATCH พร้อมเกณฑ์หลายอย่างสำหรับข้อความบางส่วนใน Excel (2 วิธี)
• XLOOKUP พร้อมเกณฑ์หลายอย่างใน Excel (4 วิธีง่าย)