Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

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

ก่อนอื่น มารู้จักชุดข้อมูลที่เป็นพื้นฐานของตัวอย่าง

ที่นี่เรามีตารางที่มีรายการหนังพร้อมแนวหนังและนักแสดงนำ พร้อมปีฉาย ใช้ชุดข้อมูลนี้ เราจะดึงข้อมูลตามเกณฑ์หลายอย่าง

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

สมุดงานฝึกฝน

คุณสามารถดาวน์โหลดสมุดงานฝึกฝนจากลิงก์ต่อไปนี้

ดึงข้อมูลจากตารางตามเกณฑ์หลายอย่าง

ที่นี่ ตัวอย่างเราจะให้แนวหนังและชื่อนักแสดงเป็นเกณฑ์ และจากเกณฑ์เหล่านี้ เราจะดึงชื่อหนัง

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

1. คืนค่าค่าเดียว (Return Single Value)

ในส่วนนี้ เราจะคืนค่าค่าเดียว ตามเกณฑ์ จะดึงค่าเดียวเท่านั้น มาสำรวจกัน

I. สูตรอาร์เรย์ INDEX-MATCH

เราสามารถใช้การรวมของฟังก์ชัน INDEX และ MATCH INDEX คืนค่าที่ตำแหน่งที่กำหนดในช่วง MATCH ค้นหาตำแหน่งของค่าค้นหาในช่วง

เพื่อรู้จักฟังก์ชันเหล่านี้ ดูบทความ: INDEX, MATCH

ตั้งค่าเกณฑ์ สำหรับตอนนี้ Thriller เป็น Genre และ Hugh Jackman ที่ช่อง Actor

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

สูตรจะเป็นดังนี้

=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0))

B4:B19 คืออาร์เรย์ที่เราจะดึงค่า และฟังก์ชัน MATCH กำหนดหมายเลขแถวที่จะดึง

คุณเห็นเราให้ 1 เป็น lookup_value ภายใน MATCH และ lookup_array สร้างจากการคูณตรรกะการจับคู่เกณฑ์

ผ่าน $H$4=$C$4:$C$19 เราตรวจสอบแนวหนัง และ $H$5=$D$4:$D$19 สำหรับชื่อนักแสดง

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

มันหา 1 ในอาร์เรย์ของผลคูณ และคืนหมายเลขแถว และฟังก์ชัน INDEX คืนชื่อหนัง

เป็นสูตรอาร์เรย์ จึงต้องใช้ CTRL + SHIFT + ENTER เพื่อรัน

เปลี่ยนค่าเกณฑ์ และคุณจะพบค่าที่ปรับปรุงแล้ว

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

อ่านเพิ่มเติม: วิธีดึงข้อมูลเฉพาะจากเซลล์ใน Excel (3 ตัวอย่าง)

II. สูตรไม่ใช่อาร์เรย์ INDEX-MATCH

เราสามารถสร้างสูตรไม่ใช่อาร์เรย์โดยรวม INDEX และ MATCH

มาดูสูตรก่อน

=INDEX($B$4:$B$19,MATCH(1,INDEX(($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0,1),0))

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

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

ภายใน INDEX ตัวใน เราตรวจสอบค่าเกณฑ์ ตรรกะสองอย่างคูณกันภายใน INDEX และทำหน้าที่เป็นอ้างอิงอาร์เรย์

ENTER เพียงอย่างเดียว จะรันสูตรได้

เปลี่ยนค่าเกณฑ์ได้อย่างอิสระ คุณจะพบค่าที่ปรับปรุงแล้ว

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

อ่านเพิ่มเติม: วิธีดึงข้อมูลจาก Excel ตามเกณฑ์ (5 วิธี)

III. การรวม INDEX-MATCH-IF

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

IF รันการทดสอบตรรกะและคืนค่าบูลีน (TRUE หรือ FALSE) เพื่อรู้จักฟังก์ชัน อ่านบทความ IF

สูตรของเราจะเป็นดังนี้

=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

ที่นี่เรามีเงื่อนไขสองอย่าง ต้องจับคู่ นั่นคือเหตุที่มี IF สองตัว พวกมันทำงานเป็น IF 巢 (หนึ่งอยู่ในอีกหนึ่ง) IF ตัวนอกตรวจสอบเงื่อนไขแรก (ไม่สนใจลำดับ) และเงื่อนไขที่สอง (IF ตัวใน) เป็น if_true_value สำหรับ IF ตัวแรก

คุณต้องใช้ CTRL+SHIFT+ENTER เพื่อรันสูตร

เปลี่ยนค่าเกณฑ์ และคุณจะพบค่าที่ปรับปรุงแล้ว

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

อ่านเพิ่มเติม: วิธีดึงข้อมูลจากชีต Excel (6 วิธีที่มีประสิทธิภาพ)

IV. ฟังก์ชัน LOOKUP

เราสามารถใช้ฟังก์ชัน LOOKUP เพื่อทำภารกิจดึงข้อมูลตามเกณฑ์

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

มาสำรวจสูตร

=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

ที่นี่เราตั้งค่า 2 เป็น lookup_value และการดำเนินการตรรกะสองอย่างในรูปแบบหาร 1 ด้วยพวกเขา คือ lookup_vector

ที่นี่ มันหาร 1 ด้วยอาร์เรย์ค่า TRUE/FALSE ($C$4:$C$19=$H$4) และจากนั้นหารด้วยอีกอาร์เรย์ TRUE/FALSE ($D$4:$D$19=$H$5) จะคืนค่า 1 หรือข้อผิดพลาด #DIV/0!

lookup_value บอกสูตรให้จับคู่กับค่าตัวเลขในช่วง เมื่อเจอการจับคู่ ค่าจะถูกดึงจากอาร์เรย์ B4:B19

ไม่ต้องกด CTRL + SHIFT + ENTER เพื่อรัน

เปลี่ยนค่าเกณฑ์ เพื่อดูว่าสูตรทำงานสมบูรณ์สำหรับค่าอื่นหรือไม่

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

โปรดสังเกต เราใช้ 2 เป็น lookup_value สิ่งนี้สามารถเป็นตัวเลขใดๆ เริ่มจาก 1

อ่านเพิ่มเติม: วิธีดึงข้อมูลจากภาพเข้า Excel (พร้อมขั้นตอนเร็ว)

การอ่านที่คล้ายกัน

VBA Code to Convert Text File to Excel (7 Methods)
Excel VBA: Pull Data Automatically from a Website (2 Methods)
How to Import Data from Secure Website to Excel (With Quick Steps)
Transfer Data from One Excel Worksheet to Another Automatically with VLOOKUP
How to Convert Excel to Text File with Pipe Delimiter (2 Ways)

2. คืนค่าหลายค่า (Return Multiple Values)

I. การรวม INDEX-SMALL

การรวมฟังก์ชันต่างๆ สามารถใช้สำหรับดึงข้อมูลหลายค่าตามเกณฑ์ หนึ่งในนั้นคือการรวม INDEX – SMALL

ฟังก์ชัน SMALL คืนค่าตามตำแหน่งในรายการที่จัดอันดับตามค่า เพื่อรู้เพิ่มเติม ดูบทความ SMALL

พร้อมกับสองฟังก์ชันนี้ เราต้องการฟังก์ชันช่วยเหลืออีก 몇 ตัว คือ IF, ROW และ IFERROR ดูบทความเพิ่มเติม: IF, ROW, IFERROR

สูตรของเราจะเป็นดังนี้

=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

ที่นี่ ทุกฟังก์ชันมีวัตถุประสงค์ ฟังก์ชัน INDEX คืนค่าจากอาร์เรย์ B2:B17 และส่วน SMALL ใหญ่ๆ ให้หมายเลขแถวที่จะดึง

IF ภายใน SMALL ตรวจสอบว่าเกณฑ์ตรงหรือไม่ เรามีเงื่อนไขสองอย่าง จึงคูณการดำเนินการตรรกะทั้งสองเพื่อตรวจสอบเกณฑ์ และฟังก์ชัน ROW ทำซ้ำผ่านเซลล์ของคอลัมน์

จากนั้น ROW ตัวนอก บ่งชี้ค่า k-th สำหรับฟังก์ชัน SMALL ร่วมกัน ฟังก์ชันเหล่านี้คืนหมายเลขแถว และ INDEX คืนผลลัพธ์

IFERROR เพื่อจัดการข้อผิดพลาดที่อาจเกิดจากสูตร เราตั้งค่าให้เซลล์ว่างสำหรับข้อผิดพลาด

ลากลง คุณจะได้ค่าทั้งหมดที่ตรงกับเกณฑ์

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

อ่านเพิ่มเติม: วิธีดึงข้อมูลจากรายการด้วยสูตร Excel (5 วิธี)

II. การรวม INDEX-AGGREGATE

ฟังก์ชัน AGGREGATE ใน Excel ช่วยให้เราทำภารกิจต่างๆ ได้ หนึ่งฟังก์ชันสำหรับหลายการดำเนินการ เราสามารถใช้ฟังก์ชันนี้คืนค่าหลายค่าตามเกณฑ์หลายอย่าง

มารู้จักฟังก์ชันนิดหน่อย ฟังก์ชัน AGGREGATE คืนการคำนวณรวม เช่น AVERAGE, COUNT, MAX เป็นต้น

ไวยากรณ์สำหรับฟังก์ชัน AGGREGATE มีดังนี้:

AGGREGATE(function_number,behavior_options, range)

function_number: หมายเลขนี้ระบุว่าควรคำนวณอะไร

behavior_options: ตั้งค่าด้วยตัวเลข หมายเลขนี้แสดงว่าฟังก์ชันจะทำงานอย่างไร

range: ช่วงที่ต้องการรวม

ฟังก์ชัน AGGREGATE ทำหลายงาน ดังนั้นฟังก์ชันจำนวนมากถูกกำหนดไว้ภายใน เราแสดงหมายเลขฟังก์ชันที่ใช้บ่อย

Function	Function_number
AVERAGE	1
COUNT	2
COUNTA	3
MAX	4
MIN	5
PRODUCT	6
SUM	9
LARGE	14
SMALL	15

เพื่อรู้เพิ่มเติม เยี่ยมชมไซต์ Microsoft Support

มาดูสูตร จะเป็นการรวมของ INDEX และ AGGREGATE

=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

ที่นี่เราใช้ 15 เป็น function_number ใน AGGREGATE จากตารางข้างต้น คุณเห็น 15 เรียกใช้การดำเนินการของฟังก์ชัน SMALL คุณเห็นนอกจากใช้ AGGREGATE (และหมายเลขฟังก์ชันและตัวเลือกพฤติกรรม) สูตรเหมือนกับสูตร INDEX–SMALL ก่อนหน้านี้ทุกประการ

กลไกเหมือนกัน INDEX เก็บอาร์เรย์ที่คืนค่าตามการจับคู่ที่พบในส่วน AGGREGATE ของสูตร

6 สำหรับตัวเลือกพฤติกรรม ซึ่งหมายถึง ignore error values

ลากลง คุณจะได้ค่าทั้งหมดที่ตรงกับเกณฑ์

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

จำได้ว่า ต้องใช้ CTRL+SHIFT+ENTER เพื่อรันสูตร

อ่านเพิ่มเติม: Return Multiple Values in Excel Based on Single Criteria (3 Options)

III. การรวม INDEX-MATCH-COUNTIF

สำหรับคืนค่าหลายค่าตามเกณฑ์หลายอย่าง เราสามารถใช้การรวมของ INDEX, MATCH, และ COUNTIF

COUNTIF นับเซลล์ในช่วงที่ตรงตามเงื่อนไขเดียว เพื่อรู้เพิ่มเติม อ่านบทความ: COUNTIF

สูตรของเราจะเป็นดังนี้

=IFERROR(INDEX($B$4:$B$19,MATCH(0,COUNTIF(H5:$H$5,$B$4:$B$19)+IF($C$4:$C$19<>$H$4,1,0)+IF($D$4:$D$19<>$H$5,1,0),0)),"")

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

ภายในฟังก์ชัน MATCH เราให้ 0 เป็น lookup_array และสำหรับ lookup_range เราใช้ส่วน IF ที่มี COUNTIF

ที่นี่ ฟังก์ชัน COUNTIF แยกค่าที่ดึงมาแล้ว และสอง IF ตรวจสอบเงื่อนไขสองอย่าง เราบวกฟังก์ชันเหล่านี้เข้าด้วยกัน จึงสร้าง lookup_range

ส่วน MATCH คืนค่าตราบใดที่พบ 0 ค่าที่นี่ทำหน้าที่เป็นหมายเลขแถวสำหรับ INDEX

ลากลง คุณจะได้ค่าทั้งหมดที่ตรงกับเกณฑ์

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

อ่านเพิ่มเติม: วิธีดึงข้อมูลจากเซลล์ใน Excel (5 วิธี)

IV. ฟังก์ชัน FILTER

หากคุณใช้ Excel 365 คุณสามารถทำภารกิจนี้ด้วยฟังก์ชันสร้างในตัวเดียวชื่อ FILTER

ฟังก์ชัน FILTER กรองช่วงข้อมูลตามเกณฑ์ที่กำหนดและดึงระเบียนที่ตรงกัน เพื่อรู้จักฟังก์ชัน อ่านบทความ: FILTER

มาสำรวจสูตร

=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))

Excel Mastery: ดึงข้อมูลจากตารางด้วยเกณฑ์หลายอย่างได้อย่างมีประสิทธิภาพ

B4:B19 คืออาร์เรย์ที่จะกรอง จากนั้นเราให้เงื่อนไข ตามที่เราจะดึงค่า เนื่องจากเราต้องตรวจสอบสองเกณฑ์ เราจึงคูณพวกเขา

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

อ่านเพิ่มเติม: Extract Filtered Data in Excel to Another Sheet (4 Methods)

สรุป

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

การอ่านเพิ่มเติม

วิธีดึงข้อมูลจาก Excel ไป Word (4 วิธี)
Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
How to Pull Data from Multiple Worksheets in Excel VBA
Transfer Data from One Excel Worksheet to Another Automatically
วิธีดึงข้อมูลจากชีตอื่นตามเกณฑ์ใน Excel
Excel Macro: Extract Data from Multiple Excel Files (4 Methods)
วิธีดึงปีจากวันที่ใน Excel (3 วิธี)