INDEX และ MATCH กับหลายอาร์เรย์
สมมติว่าคุณมีตารางหลายตารางที่มีคำอธิบายภาพเหมือนกันดังที่แสดงด้านล่าง การค้นหาค่าที่ตรงกับเกณฑ์การให้จากตารางเหล่านี้อาจเป็นงานที่ยากสำหรับคุณ ในบทช่วยสอนนี้ เราจะพูดถึงวิธีค้นหาค่าจากอาร์เรย์ ช่วง หรือกลุ่มต่างๆ โดยจับคู่เกณฑ์เฉพาะกับ ดัชนี, MATCH และ เลือก ฟังก์ชั่น
จะค้นหาค่าในหลายอาร์เรย์ได้อย่างไร?
หากต้องการทราบ หัวหน้ากลุ่มต่าง ๆ ที่อยู่ในแผนกต่าง ๆคุณสามารถใช้ฟังก์ชัน CHOOSE เพื่อกำหนดเป้าหมายตารางเพื่อส่งคืนชื่อผู้นำได้ก่อน ฟังก์ชัน MATCH จะค้นหาตำแหน่งของผู้นำในตารางที่เขา/เธออยู่ สุดท้าย ฟังก์ชัน INDEX จะดึงข้อมูลผู้นำตามข้อมูลตำแหน่ง บวกกับคอลัมน์เฉพาะที่มีรายชื่อผู้นำอยู่
ไวยากรณ์ทั่วไป
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: ตัวเลข CHOOSE ใช้เพื่อระบุอาร์เรย์จากรายการ อาร์เรย์1,อาร์เรย์2,... เพื่อส่งคืนผลลัพธ์จาก
- อาร์เรย์1,อาร์เรย์2,…: อาร์เรย์ที่จะส่งคืนผลลัพธ์ ในที่นี้หมายถึงสามตาราง
- lookup_value: ค่าของสูตรผสมที่ใช้เพื่อค้นหาตำแหน่งของผู้นำที่สอดคล้องกัน นี่หมายถึงกลุ่มที่กำหนด
- lookup_array: ช่วงของเซลล์ที่ lookup_value อยู่ในรายการ ในที่นี้หมายถึงช่วงของกลุ่ม หมายเหตุ: คุณสามารถใช้ช่วงกลุ่มจากแผนกใดก็ได้ เนื่องจากทั้งหมดเหมือนกันและเราเพียงแค่ต้องได้รับหมายเลขตำแหน่ง
- คอลัมน์_num: คอลัมน์ที่คุณระบุซึ่งคุณต้องการดึงข้อมูล
หากต้องการทราบ หัวหน้ากลุ่ม D สังกัดกรม Aโปรดคัดลอกหรือป้อนสูตรด้านล่างในเซลล์ G5 แล้วกด เข้าสู่ เพื่อให้ได้ผลลัพธ์:
=ดัชนี(เลือก(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),จับคู่(F5,$ B $ 5: $ B $ 8,0)2)
√ หมายเหตุ: เครื่องหมายดอลลาร์ ($) ด้านบนระบุการอ้างอิงแบบสัมบูรณ์ ซึ่งหมายความว่าชื่อและช่วงของคลาสในสูตรจะไม่เปลี่ยนแปลงเมื่อคุณย้ายหรือคัดลอกสูตรไปยังเซลล์อื่น หลังจากที่คุณป้อนสูตรแล้ว ให้ลากที่จับเติมลงไปเพื่อใช้สูตรกับเซลล์ด้านล่าง แล้วเปลี่ยน array_num ตาม
คำอธิบายของสูตร
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): ฟังก์ชัน CHOOSE ส่งกลับค่า 1st array จากสามอาร์เรย์ที่ระบุไว้ในสูตร มันจะกลับมา $B$5:$C$8, กล่าวคือ, ช่วงข้อมูลของแผนกA.
- ตรง(F5,$B$5:$B$8,0): Match_type 0 บังคับให้ฟังก์ชัน MATCH คืนตำแหน่งการแข่งขันนัดแรกของ กลุ่ม D, ค่าในเซลล์ F5, ในอาร์เรย์ $ B $ 5: $ B $ 8ซึ่งเป็น 4.
- ดัชนี(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),ตรง(F5,$B$5:$B$8,0),2) = ดัชนี($B$5:$C$8,4,2): ฟังก์ชัน INDEX ดึงค่าที่จุดตัดของ 4แถวที่และ 2คอลัมน์ที่ nd ของช่วง $B$5:$C$8ซึ่งเป็น เอมิลี่.
เพื่อหลีกเลี่ยงการเปลี่ยนแปลง array_num ในสูตรทุกครั้งที่คุณคัดลอก คุณสามารถใช้คอลัมน์ตัวช่วย คอลัมน์ D สูตรจะเป็นดังนี้:
=ดัชนี(เลือก(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),จับคู่(F5,$ B $ 5: $ B $ 8,0)2)
√ หมายเหตุ: ตัวเลข 1, 2, 3 ในคอลัมน์ตัวช่วยระบุ array1, array2, array3 ภายในฟังก์ชัน CHOOSE
ฟังก์ชันที่เกี่ยวข้อง
ฟังก์ชัน Excel INDEX ส่งคืนค่าที่แสดงตามตำแหน่งที่กำหนดจากช่วงหรืออาร์เรย์
ฟังก์ชัน Excel MATCH จะค้นหาค่าที่ระบุในช่วงของเซลล์ และส่งกลับตำแหน่งสัมพัทธ์ของค่า
ฟังก์ชัน CHOOSE ส่งคืนค่าจากรายการอาร์กิวเมนต์ค่าตามหมายเลขดัชนีที่กำหนด ตัวอย่างเช่น CHOOSE(3”Apple””Peach””Orange”) จะส่งกลับ Orange หมายเลขดัชนีคือ 3 และ Orange คือค่าที่สามตามหลังหมายเลขดัชนีในฟังก์ชัน
สูตรที่เกี่ยวข้อง
ค้นหาค่าจากแผ่นงานหรือสมุดงานอื่น
ถ้าคุณรู้วิธีใช้ฟังก์ชัน VLOOKUP เพื่อค้นหาค่าในเวิร์กชีตค่า vlookup จากเวิร์กชีตหรือเวิร์กบุ๊กอื่นจะไม่เป็นปัญหาสำหรับคุณ
Vlookup พร้อมชื่อแผ่นงาน dymanic
ในหลาย ๆ กรณีคุณอาจต้องรวบรวมข้อมูลในแผ่นงานหลายแผ่นเพื่อสรุป ด้วยการผสมผสานระหว่างฟังก์ชัน VLOOKUP และฟังก์ชัน INDIRECT คุณสามารถสร้างสูตรเพื่อค้นหาค่าเฉพาะในแผ่นงานด้วยชื่อแผ่นงานแบบไดนามิก
ค้นหาหลายเกณฑ์ด้วย INDEX และ MATCH
เมื่อต้องรับมือกับฐานข้อมูลขนาดใหญ่ในสเปรดชีต Excel ที่มีหลายคอลัมน์และคำอธิบายแถว การค้นหาสิ่งที่ตรงตามเกณฑ์หลายเกณฑ์มักเป็นเรื่องยากเสมอ ในกรณีนี้ คุณสามารถใช้สูตรอาร์เรย์ที่มีฟังก์ชัน INDEX และ MATCH ได้
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox