วิธีค้นหาค่าหลายค่าใน Excel: ทีละขั้นตอน

เมื่อทำงานกับชุดข้อมูลขนาดใหญ่ใน Microsoft Excel อาจเป็นเรื่องยากที่จะค้นหาค่าหลายค่าที่ตรงกับเงื่อนไขต่างๆ ฟังก์ชันในตัวบางอย่าง เช่น VLOOKUP ได้รับการออกแบบมาให้ทำงานกับค่าเดียว

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

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

ไปกันเถอะ!

สารบัญ

• วิธีใช้ VLOOKUP ที่มีหลายค่า
  • สูตร Array คืออะไร?
  • ตัวอย่าง VLOOKUP
  • Excel เวอร์ชันล่าสุด
  • รุ่นเก่ากว่า
  • การจับคู่แบบตรงทั้งหมด การจับคู่โดยประมาณ
  • เพิ่มเติมเกี่ยวกับ VLOOKUP
• วิธีใช้ INDEX และ MATCH เพื่อค้นหาค่าหลายค่า
  • วิธีใช้ INDEX และ MATCH ร่วมกันใน Excel 365
  • การทำงานกับ Excel เวอร์ชันเก่า
• วิธีใช้ฟังก์ชัน IF เพื่อหาค่าหลายค่า
  • Excel เวอร์ชันปัจจุบัน
  • Excel เวอร์ชันเก่า
• วิธีใช้ SUMPRODUCT สำหรับหลายเกณฑ์
• 3 ข้อผิดพลาดทั่วไปเกี่ยวกับฟังก์ชันการค้นหา
  • 1. ข้อผิดพลาด #N/A
  • 2. #รีฟ! ข้อผิดพลาด
  • 3. ข้อผิดพลาดแบบวงกลม
• วิธีใช้ IFERROR กับฟังก์ชันการค้นหา
• 7 เทคนิคการค้นหาขั้นสูง
  • 1. การค้นหาตำแหน่งสัมพัทธ์
  • 2. ฟังก์ชันขนาดเล็ก
  • 3. ฟังก์ชันแถว
  • 4. ฟังก์ชั่นกรอง
  • 5. คอลัมน์ตัวช่วย
  • 6. อาร์เรย์แบบไดนามิก
  • 7. แบบสอบถามพลังงาน
• ความคิดสุดท้าย

วิธีใช้ VLOOKUP ที่มีหลายค่า

ฟังก์ชันVLOOKUPมักใช้เพื่อค้นหาค่าเดียวในช่วงข้อมูล อย่างไรก็ตาม คุณยังสามารถค้นหารายการที่ตรงกันหลายรายการใน Excel ได้ด้วยสูตรการค้นหานี้

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

สูตร Array คืออะไร?

สูตรอาร์เรย์คือสูตรที่สามารถคำนวณอาร์เรย์ของข้อมูลได้ เรียกว่าสูตรอาร์เรย์เพราะสามารถส่งคืนผลลัพธ์เป็นอาร์เรย์ แทนที่จะเป็นค่าเดียว

มีหลายขั้นตอนง่ายๆ ในการสร้างสูตรอาร์เรย์:

1. เลือกช่วงของเซลล์เพื่อค้นหา

2. ใส่สูตรในแถบสูตร

3. กด Ctrl + Shift + Enter เพื่อเสร็จสิ้น

ไวยากรณ์ของสูตรอาร์เรย์คล้ายกับสูตรปกติ แต่มีเครื่องหมายปีกกา {} ล้อมรอบสูตร วงเล็บปีกการะบุว่าสูตรเป็นสูตรอาร์เรย์และจะส่งกลับอาร์เรย์ของค่า

ตัวอย่างในบทความนี้จะแสดงวิธีใช้สูตรอาร์เรย์อย่างถูกต้อง

ตัวอย่าง VLOOKUP

ตัวอย่างของเรามีห้ารายการในคอลัมน์ A ของแผ่นงาน:

• แอปเปิล

• กล้วย

• ขนมปัง

• แครอท

• เชอร์รี่

ภารกิจคือตรวจดูว่ามีผลไม้ 3 ชนิดอยู่ในรายการนี้หรือไม่ ได้แก่ แอปเปิ้ล กล้วย และเชอร์รี่

Excel เวอร์ชันล่าสุด

ไวยากรณ์ VLOOKUP ขึ้นอยู่กับเวอร์ชันของ Excel ที่คุณใช้

เวอร์ชันล่าสุด ( Excel 365หรือExcel 2021 ) รองรับอาร์เรย์แบบไดนามิก คุณลักษณะนี้ช่วยให้สูตรส่งคืนผลลัพธ์หลายรายการที่ "รั่วไหล" ลงในเซลล์ที่อยู่ติดกัน

นี่คือไวยากรณ์ (เครื่องหมายเท่ากับเริ่มต้นสูตร):

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

• lookup_value: ค่าที่คุณต้องการค้นหา

• table_array: ตารางข้อมูลทั้งหมดที่คุณต้องการค้นหา

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

• range_lookup: ไม่บังคับ ระบุว่าคุณต้องการการจับคู่แบบตรงทั้งหมดหรือแบบประมาณ

ตัวอย่างเฉพาะของเราใช้สูตรนี้:

=VLOOKUP({“แอปเปิ้ล”,”กล้วย”,”เชอร์รี่”},A1:A5,1,FALSE)

เมื่อป้อนสูตรในเซลล์ B1 ผลลัพธ์จะกระจายไปยังเซลล์ C1 และ D1 ภาพนี้แสดงตัวอย่างการทำงาน:

รุ่นเก่ากว่า

หากคุณใช้ Excel เวอร์ชันเก่าที่ไม่รองรับอาร์เรย์แบบไดนามิก (เช่น Excel 2019 หรือเก่ากว่า) คุณจะต้องใช้วิธีการที่แตกต่างออกไปเล็กน้อยกับสูตรอาร์เรย์

ทำตามขั้นตอนเหล่านี้:

1. คลิกเซลล์ที่คุณต้องการแสดงผลลัพธ์สำหรับรายการแรก (เช่น คอลัมน์ B)

2. พิมพ์สูตรต่อไปนี้โดยไม่ต้องกด Enter: =VLOOKUP({“apple”,”banana”,”cherry”}, A1:A5, 1, FALSE)

3. กด Ctrl + Shift + Enter เพื่อเปลี่ยนเป็นสูตรอาร์เรย์

4. คัดลอกเซลล์ B1 แล้ววางลงในเซลล์ด้านล่าง (หรือใช้ที่จับเติม)

เมื่อคุณใช้ Ctrl + Shift + Enter Excel จะเพิ่มวงเล็บปีกการอบๆ สูตร สิ่งนี้บ่งชี้ว่าเป็นสูตรอาร์เรย์

การจับคู่แบบตรงทั้งหมด การจับคู่โดยประมาณ

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

หากคุณต้องการจับคู่แบบตรงทั้งหมด คุณสามารถตั้งค่าอาร์กิวเมนต์ range_lookup เป็น FALSE

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

เพิ่มเติมเกี่ยวกับ VLOOKUP

หากคุณต้องการเรียนรู้เพิ่มเติมเกี่ยวกับฟังก์ชันอเนกประสงค์นี้ โปรดดูบทความเหล่านี้:

• วิธีเปรียบเทียบสองคอลัมน์ใน Excel กับ VLOOKUP

• วิธีค้นหาความแตกต่างด้วย VLOOKUP

ตอนนี้คุณตั้งค่าฟังก์ชัน VLOOKUP เรียบร้อยแล้ว มาดูฟังก์ชันอีกสองฟังก์ชันที่สามารถทำงานในลักษณะที่แตกต่างกันได้: INDEX และ MATCH

วิธีใช้ INDEX และ MATCH เพื่อค้นหาค่าหลายค่า

คุณสามารถรวมฟังก์ชัน INDEX และ MATCH เข้าด้วยกันเพื่อค้นหาค่าหลายค่าในหลายๆ แถว

ฟังก์ชัน INDEX ใน Excel จะส่งกลับค่าหรือการอ้างอิงไปยังเซลล์ภายในช่วงที่กำหนด

=INDEX(อาร์เรย์, row_num, [column_num])

• อาร์เรย์: ช่วงของเซลล์ที่จะค้นหาค่า

• row_num: หมายเลขแถวภายในอาร์เรย์ที่จะส่งคืนค่า

• column_num: (ไม่บังคับ) หมายเลขคอลัมน์ภายในอาร์เรย์ที่จะส่งคืนค่า หากละเว้น ฟังก์ชันจะส่งกลับทั้งแถว

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

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: ค่าที่จะค้นหาภายใน lookup_array

• lookup_array: ช่วงของเซลล์ที่จะค้นหา lookup_value

• match_type: (ไม่บังคับ) ประเภทของการจับคู่ที่จะดำเนินการ หากละเว้น ฟังก์ชันจะทำการจับคู่แบบตรงทั้งหมด

วิธีใช้ INDEX และ MATCH ร่วมกันใน Excel 365

หากต้องการใช้ INDEX และ MATCH ร่วมกันเพื่อค้นหาค่าหลายค่าใน Excel คุณต้องใช้สูตรอาร์เรย์

การทำงานกับข้อมูลตัวอย่างก่อนหน้านี้ นี่คือสูตรใน Excel 365:

=INDEX(A1:A5, MATCH({“แอปเปิ้ล”,”กล้วย”,”เชอร์รี่”}, A1:A5, 0))

ตัวอย่างข้างต้นแยกย่อยดังนี้:

• INDEX: ส่งกลับค่าของเซลล์ในช่วงที่ระบุตามแถวและหมายเลขคอลัมน์ที่กำหนด ในกรณีนี้ จะส่งคืนค่าจากช่วง A1:A5

• A1:A5: นี่คือช่วงตารางที่กำหนดซึ่งคุณกำลังค้นหาค่าและผลลัพธ์จะถูกส่งกลับ

• จับคู่: ค้นหารายการที่ระบุในช่วงของเซลล์และส่งกลับตำแหน่งสัมพัทธ์ของรายการนั้นในช่วง

• {“apple”,”banana”,”cherry”}: นี่คือค่าคงที่อาร์เรย์ที่มีค่าที่คุณต้องการค้นหา

• A1:A5: นี่คือช่วงที่ MATCH จะค้นหาค่าจากค่าคงที่อาร์เรย์

• 0: นี่คือประเภทการจับคู่สำหรับฟังก์ชัน MATCH ในกรณีนี้ ค่าจะเป็น 0 ซึ่งหมายความว่าคุณกำลังมองหาการจับคู่แบบตรงทั้งหมดแทนที่จะเป็นการจับคู่แบบปิด

 ภาพนี้แสดงสูตรที่ใช้งานจริง:

การทำงานกับ Excel เวอร์ชันเก่า

หากคุณใช้ไฟล์ Excel รุ่นเก่าที่ไม่รองรับอาร์เรย์แบบไดนามิก (เช่น Excel 2019 หรือรุ่นก่อนหน้า) คุณจะต้องใช้วิธีอื่น

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

ทำตามขั้นตอนเหล่านี้:

1. คลิกที่เซลล์ที่คุณต้องการผลลัพธ์สำหรับรายการแรก (เช่น เซลล์ B1)

2. พิมพ์สูตรด้านล่าง:

3. =INDEX(A1:A5, MATCH(“แอปเปิล”, A1:A5, 0))

4. กด Enter เพื่อดำเนินการตามสูตร

5. พิมพ์สูตรนี้ในเซลล์ B2: =INDEX(A1:A5, MATCH(“banana”, A1:A5, 0))

6. พิมพ์สูตรนี้ในเซลล์ B3: =INDEX(A1:A5, MATCH(“cherry”, A1:A5, 0))

รูปภาพนี้แสดงการอ้างอิงเซลล์ B3:

ฟังก์ชัน INDEX และ MATCH ไม่ใช่ฟังก์ชันเดียวที่ช่วยให้คุณค้นหาค่าได้หลายค่า ในหัวข้อถัดไป เราจะมาดูกันว่าคุณสามารถใช้ฟังก์ชัน IF เป็นทางเลือกได้อย่างไร

วิธีใช้ฟังก์ชัน IF เพื่อหาค่าหลายค่า

อีกวิธีในการค้นหาค่าหลายเซลล์ตามเกณฑ์ที่กำหนดคือการใช้ฟังก์ชัน IF กับฟังก์ชันอื่นๆ

ฟังก์ชัน IF ช่วยให้คุณทดสอบหลายเงื่อนไขและส่งคืนผลลัพธ์ที่แตกต่างกันโดยขึ้นอยู่กับผลลัพธ์ของการทดสอบเหล่านั้น

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

Excel เวอร์ชันปัจจุบัน

หากต้องการหาผลรวมของคอลัมน์ยอดขายที่มีสินค้าเป็น "Apple" หรือ "Banana" โดยใช้ฟังก์ชัน IF คุณสามารถใช้สูตรอาร์เรย์ที่มีฟังก์ชัน IF, SUM และ OR

สมมติว่าข้อมูลของคุณเริ่มต้นในเซลล์ A1 ให้ใช้สูตรต่อไปนี้:

=SUM(IF((A2:A4=”แอปเปิล”)+(A2:A4=”กล้วย”), B2:B4, 0))

ส่วน(A2:A4=”Apple”)+(A2:A4=”Banana”)สร้างอาร์เรย์ที่มีค่า 1 ถ้าเซลล์ในช่วง A2:A4 มี “Apple” หรือ “Banana” และมิฉะนั้น 0 .

คำสั่ง IF ตรวจสอบแต่ละองค์ประกอบของอาร์กิวเมนต์อาร์เรย์ หากค่าเป็น 1 (เช่น สินค้าเป็น "Apple" หรือ "Banana") ค่านั้นจะใช้ค่าที่สอดคล้องกันในคอลัมน์ Sales (ช่วง B2:B4) มิฉะนั้นจะใช้เวลา 0

ฟังก์ชัน SUM จะเพิ่มค่าจากฟังก์ชัน IF ซึ่งจะรวมมูลค่าการขายของทั้ง "Apple" และ "Banana" ได้อย่างมีประสิทธิภาพ

ภาพนี้แสดงการทำงานของสูตรในช่วงการค้นหา:

Excel เวอร์ชันเก่า

ใน Excel 2019 หรือเก่ากว่า คุณต้องใช้สูตรอาร์เรย์ ทำตามขั้นตอนเหล่านี้:

1. พิมพ์สูตรแต่ไม่ต้องกด Enter

2. กด Ctrl + Shift + Enter เพื่อให้เป็นสูตรอาร์เรย์

Excel จะเพิ่มวงเล็บปีกกา {} รอบสูตร แสดงว่าเป็นสูตรอาร์เรย์

ต่อไป เราจะดูว่าคุณสามารถใช้ SUMPRODUCT เพื่อค้นหาค่าต่างๆ ตามเกณฑ์ของคุณได้อย่างไร ไปกันเถอะ!

วิธีใช้ SUMPRODUCT สำหรับหลายเกณฑ์

ฟังก์ชัน SUMPRODUCT ยังช่วยให้คุณค้นหาค่าหลายค่าตามเกณฑ์หลายเกณฑ์

เนื่องจากไม่ต้องใช้สูตรอาร์เรย์ ไวยากรณ์จึงเหมือนกันโดยไม่คำนึงถึงเวอร์ชันของ Excel

โดยใช้ข้อมูลเดียวกันกับในตัวอย่างก่อนหน้า สูตรจะมีลักษณะดังนี้:

=SUMPRODUCT((A2:A4=”แอปเปิ้ล”)+(A2:A4=”กล้วย”), B2:B4)

ส่วน(A2:A4=”Apple”)+(A2:A4=”Banana”)สร้างอาร์เรย์ที่มีค่า 1 ถ้าเซลล์ในช่วง A2:A4 มี “Apple” หรือ “Banana” และมิฉะนั้น 0 .

ฟังก์ชัน SUMPRODUCT จะคูณองค์ประกอบของอาร์เรย์ด้วยองค์ประกอบที่สอดคล้องกันในคอลัมน์ Sales (ช่วง B2:B4) จากนั้นจึงรวมมูลค่าผลลัพธ์เข้าด้วยกันเพื่อรวมมูลค่าการขายของทั้ง “Apple” และ “Banana” อย่างมีประสิทธิภาพ

สูตรด้านล่างแสดงการทำงาน:

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

3 ข้อผิดพลาดทั่วไปเกี่ยวกับฟังก์ชันการค้นหา

ฟังก์ชันการค้นหาบางครั้งอาจส่งกลับข้อผิดพลาดที่อาจทำให้หงุดหงิดและใช้เวลานานในการแก้ไขปัญหา ข้อผิดพลาดทั่วไปสามประการที่คุณจะพบคือ:

1. #N/A ข้อผิดพลาด

2. #รีฟ! ข้อผิดพลาด

3. ข้อผิดพลาดแบบวงกลม

1. ข้อผิดพลาด #N/A

ข้อผิดพลาด #N/A เกิดขึ้นเมื่อไม่พบค่าการค้นหาในอาร์เรย์การค้นหา

มีสาเหตุหลายประการที่ทำให้เกิดข้อผิดพลาดนี้ ได้แก่:

• ค่าการค้นหาสะกดผิดหรือไม่ถูกต้อง

• อาร์เรย์การค้นหาไม่ได้เรียงลำดับจากน้อยไปหามาก

• ค่าการค้นหาไม่ได้อยู่ในชุดข้อมูล

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

2. #รีฟ! ข้อผิดพลาด

#REF! ข้อผิดพลาดเกิดขึ้นเมื่ออาร์เรย์การค้นหาหรืออาร์เรย์ส่งคืนถูกลบหรือย้าย

ข้อผิดพลาดนี้สามารถแก้ไขได้โดยการอัปเดตการอ้างอิงเซลล์ในฟังก์ชันการค้นหา

3. ข้อผิดพลาดแบบวงกลม

เมื่อคุณรวมฟังก์ชันในสูตรที่ซับซ้อน Excel อาจบอกคุณว่าคุณมีการอ้างอิงแบบวงกลม

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

วิธีใช้ IFERROR กับฟังก์ชันการค้นหา

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

ไวยากรณ์สำหรับฟังก์ชัน IFERROR เป็นดังนี้:

=IFERROR(ค่า, value_if_error)

· ค่า: ค่าหรือสูตรที่คุณต้องการประเมิน

· Value_if_error: ค่าหรือสูตรที่จะส่งคืนหากอาร์กิวเมนต์แรกส่งกลับข้อผิดพลาด

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

อย่างที่คุณเห็น ข้อผิดพลาด #N/A จะแสดงขึ้น ซึ่งอาจทำให้ผู้ใช้ Excel ที่ไม่มีประสบการณ์สับสนได้

คุณสามารถใช้ IFERROR เพื่อแสดงเซลล์ว่างหรือข้อความที่ระบุว่า "ไม่พบ" ด้วยไวยากรณ์นี้แทน:

=IFERROR(VLOOKUP(lookup_value, table_array, column_index, FALSE), “ไม่พบ”)

ในตัวอย่างนี้ หากฟังก์ชัน VLOOKUP ส่งกลับข้อผิดพลาด ฟังก์ชัน IFERROR จะส่งกลับข้อความ “ไม่พบ” แทน

ภาพนี้แสดงสูตรที่ใช้งานจริง คอลัมน์ B มีค่าที่ขาดหายไป ในขณะที่คอลัมน์ C และคอลัมน์ D พบค่าที่ตรงกัน

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

7 เทคนิคการค้นหาขั้นสูง

การค้นหาค่าหลายค่าใน Excel อาจเป็นงานที่ท้าทาย โดยเฉพาะอย่างยิ่งเมื่อต้องจัดการกับชุดข้อมูลขนาดใหญ่ คุณอาจพบปัญหาด้านประสิทธิภาพด้วยการประมวลผลที่ช้า

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

• การค้นหาตำแหน่งสัมพัทธ์

• ฟังก์ชันขนาดเล็ก

• ฟังก์ชันแถว

• ฟังก์ชั่นกรอง

• คอลัมน์ตัวช่วย

• อาร์เรย์แบบไดนามิก

• แบบสอบถามพลังงาน

1. การค้นหาตำแหน่งสัมพัทธ์

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

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

=OFFSET(เซลล์ 2, 3)

2. ฟังก์ชันขนาดเล็ก

อีกเทคนิคที่มีประโยชน์สำหรับการค้นหาค่าหลายค่าคือการใช้ฟังก์ชัน SMALL ฟังก์ชันนี้ส่งคืนค่าที่น้อยที่สุดลำดับที่ n ในช่วงของเซลล์

ด้วยการรวมฟังก์ชันนี้เข้ากับฟังก์ชันการค้นหาอื่นๆ เช่น INDEX และ MATCH คุณสามารถค้นหาค่าได้หลายค่าตามเกณฑ์เฉพาะ

ตัวอย่างเช่น สูตรต่อไปนี้ค้นหาค่าที่น้อยที่สุดเป็นอันดับสองในช่วงของเซลล์ที่ตรงตามเงื่อนไขบางอย่าง:

=INDEX(ข้อมูล, MATCH(SMALL(IF(เกณฑ์, ช่วง), 2), ช่วง, 0))

3. ฟังก์ชันแถว

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

ตัวอย่างเช่น สูตรต่อไปนี้ค้นหาค่าในตารางตามตัวระบุเฉพาะ:

=INDEX(ข้อมูล, MATCH(ตัวระบุที่ไม่ซ้ำกัน, ข้อมูล[ตัวระบุที่ไม่ซ้ำกัน], 0), column_index_number)

4. ฟังก์ชั่นกรอง

ฟังก์ชัน FILTER ไม่พร้อมใช้งานใน Excel เวอร์ชันเก่า

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

=FILTER(อาร์เรย์ รวม [if_empty])

• อาร์เรย์: ข้อมูลเฉพาะที่คุณต้องการกรอง

• รวม: อาร์กิวเมนต์ include คือเกณฑ์หรือเงื่อนไขที่คุณต้องการใช้กับอาร์เรย์

• [if_empty] (ไม่บังคับ): ค่าที่จะส่งคืนหากไม่มีแถวหรือคอลัมน์ที่ตรงกับเกณฑ์ที่ระบุในอาร์กิวเมนต์ include

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

=SUM(FILTER(B2:B4, (A2:A4=”แอปเปิ้ล”)+(A2:A4=”กล้วย”)))

รูปภาพนี้แสดงจำนวนแถวที่ตรงกันและผลรวมของค่าต่างๆ:

5. คอลัมน์ตัวช่วย

คุณสามารถใช้คอลัมน์ตัวช่วยเพื่อรวมหลายฟิลด์เข้าด้วยกันภายในฟังก์ชัน เช่น VLOOKUP

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

6. อาร์เรย์แบบไดนามิก

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

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

7. แบบสอบถามพลังงาน

Power Query เป็นเครื่องมือที่มีประสิทธิภาพใน Excel ที่สามารถใช้เพื่อส่งคืนค่าตามเกณฑ์หลายเกณฑ์

ตัวอย่างเช่น วิดีโอนี้ค้นหาข้อมูลที่ยุ่งเหยิงในสเปรดชีตและทำความสะอาด

ความคิดสุดท้าย

สรุปการดำน้ำลึกของเราในศิลปะการค้นหาค่าหลายค่าใน Excel! เมื่อคุณเชี่ยวชาญ VLOOKUP, INDEX, MATCH และสูตรอาร์เรย์แล้ว คุณจะพบว่าตัวเองรู้สึกตื่นเต้นกับชุดข้อมูลที่ซับซ้อน เช่น มีดร้อนผ่านเนย

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

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