การดึงข้อมูลในช่วงเวลาที่จำเป็นเป็นปัจจัยสำคัญของการใช้สเปรดชีต Excel ก็ไม่ใช่ข้อยกเว้น คุณเก็บข้อมูลในรูปแบบตารางใน Excel เพื่อแยกข้อมูลเหล่านั้น วันนี้เราจะมาแสดงวิธีการดึงข้อมูลจากตารางตามเกณฑ์หลายเกณฑ์
ก่อนอื่น เรามาทำความรู้จักกับชุดข้อมูลที่เป็นฐานของตัวอย่างกันก่อน
ที่นี่เรามีตารางที่มีรายชื่อภาพยนตร์ที่มีประเภทและนักแสดงนำพร้อมกับปีที่ออกฉาย เมื่อใช้ชุดข้อมูลนี้ เราจะดึงข้อมูลตามเกณฑ์หลายเกณฑ์
โปรดทราบว่านี่เป็นตารางพื้นฐานเพื่อให้ทุกอย่างเรียบง่าย ในสถานการณ์จริง คุณอาจพบชุดข้อมูลขนาดใหญ่และซับซ้อน
สมุดแบบฝึกหัด
คุณสามารถดาวน์โหลดสมุดแบบฝึกหัดได้จากลิงค์ต่อไปนี้
ดึงข้อมูลจากตารางตามเกณฑ์หลายเกณฑ์
ตัวอย่างเช่น เราจะระบุประเภทและชื่อนักแสดงเป็นเกณฑ์ และเราจะแยกชื่อภาพยนตร์ตามเกณฑ์เหล่านี้
1. ส่งกลับค่าเดียว
ในส่วนนี้ เราจะคืนค่าเป็นค่าเดียว ตามเกณฑ์จะมีการดึงข้อมูลเพียงค่าเดียวเท่านั้น ไปสำรวจกัน
ม. สูตรอาร์เรย์ INDEX-MATCH
เราสามารถใช้ INDEX . ร่วมกันได้ และ MATCH ฟังก์ชั่น. ดัชนี ส่งคืนค่าที่ตำแหน่งที่กำหนดในช่วง MATCH ระบุตำแหน่งของค่าการค้นหาในช่วง
หากต้องการทราบฟังก์ชันเหล่านี้ ให้ตรวจสอบบทความเหล่านี้: INDEX, MATCH.
มาตั้งค่าเกณฑ์กัน ในขณะนี้ Thriller เป็น Genre และ Hugh Jackman ที่ นักแสดง สนาม
สูตรจะเป็นดังนี้
=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 สำหรับชื่อนักแสดง
มันพบ 1 ในอาร์เรย์ของผลลัพธ์การคูณ และส่งคืนหมายเลขแถวและ INDEX ฟังก์ชั่นส่งคืนชื่อภาพยนตร์
เป็นสูตรอาร์เรย์ เราจึงต้องใช้ CTRL + SHIFT + ENTER เพื่อดำเนินการ
เปลี่ยนค่าเกณฑ์แล้วคุณจะพบค่าที่อัปเดต
อ่านเพิ่มเติม: วิธีดึงข้อมูลเฉพาะออกจากเซลล์ใน Excel (ตัวอย่าง 3 ตัวอย่าง)
ครั้งที่สอง. 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 ฟังก์ชันทำหน้าที่ดึงข้อมูลโดยที่คนในวงช่วยตรวจจับหมายเลขแถว
ภายใน INDEX, เราได้ตรวจสอบค่าเกณฑ์แล้ว ที่นี่การดำเนินการทางตรรกะทั้งสองคูณภายใน INDEX และทำหน้าที่เป็นข้อมูลอ้างอิงอาร์เรย์
เพียง ENTER จะดำเนินการตามสูตร
ปรับเปลี่ยนค่าเกณฑ์ได้ตามสบาย คุณจะพบค่าที่อัปเดต
อ่านเพิ่มเติม: วิธีการดึงข้อมูลจาก Excel ตามเกณฑ์ (5 วิธี)
III. ชุดค่าผสม INDEX-MATCH-IF
ในส่วนก่อนหน้านี้ เราได้ตรวจสอบเงื่อนไขและคูณเงื่อนไขเหล่านี้เพื่อบังคับให้ทำร่วมกัน เราสามารถออกจากการคูณโดยใช้ IF ฟังก์ชัน
ถ้า รันการทดสอบเชิงตรรกะและส่งกลับค่าบูลีน (TRUE หรือ เท็จ ) ผลลัพธ์. หากต้องการทราบเกี่ยวกับฟังก์ชัน โปรดไปที่บทความ IF นี้
สูตรของเราจะเป็นดังนี้
=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))
เรามีเงื่อนไขสองข้อที่ตรงกัน นั่นคือเหตุผลที่สอง IF . พวกเขากำลังทำงานเป็น IF . ที่ซ้อนกัน (หนึ่งภายในอีก) ด้านนอก IF ฟังก์ชั่นตรวจสอบเงื่อนไขก่อน (โดยไม่คำนึงถึงลำดับ) และเงื่อนไขที่สอง (ภายใน IF) คือ if_true_value สำหรับครั้งแรก IF .
คุณต้องใช้ CTRL+SHIFT+ENTER เพื่อดำเนินการตามสูตร
เปลี่ยนค่าเกณฑ์แล้วคุณจะพบค่าที่อัปเดต
อ่านเพิ่มเติม: วิธีการดึงข้อมูลจากแผ่นงาน Excel (6 วิธีที่มีประสิทธิภาพ)
IV. ฟังก์ชันการค้นหา
เราสามารถใช้ ค้นหา เพื่อทำหน้าที่ในการดึงข้อมูลตามเกณฑ์
ค้นหา ฟังก์ชันทำการค้นหาที่ตรงกันในช่วงและส่งกลับค่าที่สอดคล้องกัน สำหรับข้อมูลเพิ่มเติม โปรดไปที่ฝ่ายสนับสนุนของ Microsoft เว็บไซต์
มาสำรวจสูตรกัน
=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))
ที่นี่เราได้ตั้งค่า 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 เพื่อดำเนินการ
เปลี่ยนค่าเกณฑ์เพื่อดูว่าสูตรทำงานได้อย่างสมบูรณ์หรือไม่สำหรับค่าอื่นๆ
โปรดทราบว่าเราใช้ 2 เป็น lookup_value . ตัวเลขนี้สามารถเป็นตัวเลขใดก็ได้โดยเริ่มจาก 1
อ่านเพิ่มเติม: วิธีการดึงข้อมูลจากรูปภาพไปยัง Excel (ด้วยขั้นตอนด่วน)
การอ่านที่คล้ายกัน
- โค้ด VBA เพื่อแปลงไฟล์ข้อความเป็น Excel (7 วิธี)
- Excel VBA:ดึงข้อมูลจากเว็บไซต์โดยอัตโนมัติ (2 วิธี)
- วิธีการนำเข้าข้อมูลจากเว็บไซต์ที่ปลอดภัยไปยัง Excel (ด้วยขั้นตอนด่วน)
- ถ่ายโอนข้อมูลจากแผ่นงาน Excel หนึ่งไปยังอีกแผ่นหนึ่งโดยอัตโนมัติด้วย VLOOKUP
- วิธีการแปลง Excel เป็นไฟล์ข้อความด้วยตัวคั่นไพพ์ (2 วิธี)
2. ส่งคืนค่าหลายค่า
ม. ชุดค่าผสมดัชนี-เล็ก
สามารถใช้ชุดฟังก์ชันต่างๆ ร่วมกันเพื่อดึงข้อมูลหลายรายการตามเกณฑ์ได้ หนึ่งในชุดค่าผสมคือ INDEX – เล็ก การรวมกัน
เล็ก ฟังก์ชันส่งกลับค่าตามตำแหน่งในรายการที่จัดอันดับตามค่า หากต้องการทราบข้อมูลเพิ่มเติม ให้ตรวจสอบ เล็ก . นี้ บทความ
นอกจากสองสิ่งนี้แล้ว เราจะต้องมีฟังก์ชันตัวช่วยบางอย่าง 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),"")
ที่นี่ทุกฟังก์ชั่นมีจุดประสงค์ ดัชนี ฟังก์ชันส่งคืนค่าจากอาร์เรย์ B2:B17 และขนาดใหญ่ เล็ก ส่วนระบุหมายเลขแถวที่จะดึงข้อมูล
ถ้า ภายใน เล็ก ตรวจสอบว่าเกณฑ์ตรงกันหรือไม่ เรามีเงื่อนไขสองข้อที่ตรงกัน นั่นคือเหตุผลที่เราคูณการดำเนินการเชิงตรรกะทั้งสองเพื่อตรวจสอบเกณฑ์ และ ROW ฟังก์ชันจะวนซ้ำในเซลล์ของคอลัมน์
จากนั้น แถว . ด้านนอก หมายถึงค่าที่ k สำหรับ เล็ก การทำงาน. ฟังก์ชันเหล่านี้ร่วมกันส่งคืนหมายเลขแถวและ INDEX ส่งกลับผลลัพธ์
IFERROR เพื่อจัดการกับข้อผิดพลาดที่อาจเกิดขึ้นจากสูตร เราได้ตั้งค่าไว้ในลักษณะที่ข้อผิดพลาดจะทำให้เซลล์ว่าง
ลากลงมาจะได้ค่าที่ตรงตามเกณฑ์
อ่านเพิ่มเติม: วิธีดึงข้อมูลออกจากรายการโดยใช้สูตร Excel (5 วิธี)
ครั้งที่สอง. ชุดค่าผสม INDEX-AGGREGATE
รวม ฟังก์ชันใน Excel ช่วยให้เราสามารถทำงานต่างๆ ฟังก์ชันเดียวสำหรับการดำเนินการหลายอย่าง เราสามารถใช้ฟังก์ชันนี้เพื่อคืนค่าหลายค่าตามเกณฑ์หลายเกณฑ์
มาทำความรู้จักกับฟังก์ชันกันสักหน่อย AGGREGATE ฟังก์ชันส่งคืนการคำนวณรวม เช่น AVERAGE, COUNT, MAX เป็นต้น
ไวยากรณ์สำหรับ AGGREGATE ฟังก์ชันมีดังนี้:
AGGREGATE(function_number,behavior_options, range) function_number: ตัวเลขนี้เป็นตัวกำหนดว่าควรทำการคำนวณใด
พฤติกรรม_ตัวเลือก: ตั้งค่านี้โดยใช้ตัวเลข ตัวเลขนี้แสดงถึงลักษณะการทำงานของฟังก์ชัน
ช่วง: ช่วงที่คุณต้องการรวม
รวม ฟังก์ชั่นทำงานหลายอย่าง ดังนั้นจำนวนของฟังก์ชั่นจึงถูกกำหนดไว้ล่วงหน้า เรากำลังแสดงรายการหมายเลขฟังก์ชันที่ใช้บ่อยบางรายการ
| ฟังก์ชัน | Function_number |
|---|---|
| ค่าเฉลี่ย | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| SUM | 9 |
| LARGE | 14 |
| เล็ก | 15 |
หากต้องการทราบข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชัน โปรดไปที่ฝ่ายสนับสนุนของ Microsoft เว็บไซต์
ทีนี้มาดูสูตรกัน จะเป็นการรวม INDEX และ รวม .
=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),"")
ที่นี่เราใช้ 15 เป็น function_number ใน รวม . จากตารางด้านบน คุณจะเห็น 15 เรียกร้องให้ เล็ก การทำงานของฟังก์ชัน คุณสามารถดูนอกเหนือจากการใช้ AGGREGATE (และหมายเลขฟังก์ชันและตัวเลือกการทำงาน) สูตรจะเหมือนกับ INDEX . ก่อนหน้า –เล็ก สูตร
กลไกเหมือนกัน INDEX เก็บอาร์เรย์ที่คืนค่าตามการจับคู่ที่พบใน AGGREGATE ส่วนของสูตร
6 สำหรับตัวเลือกพฤติกรรม ซึ่งหมายถึง ละเว้นค่าความผิดพลาด .
ลากลงมาจะได้ค่าที่ตรงตามเกณฑ์
อย่าลืมใช้ CTRL+SHIFT+ENTER เพื่อดำเนินการตามสูตร
อ่านเพิ่มเติม: ส่งกลับค่าหลายค่าใน Excel ตามเกณฑ์เดียว (3 ตัวเลือก)
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)),"")
ภายใน MATCH ฟังก์ชัน เราให้ 0 เป็น lookup_array และสำหรับ lookup_range เราได้ใช้ IF ส่วนที่มี COUNTIF .
ที่นี่ COUNTIF ฟังก์ชันไม่รวมค่าใดๆ ที่ดึงออกมาแล้ว และสอง IF ฟังก์ชั่นตรวจสอบสองเงื่อนไข We have added these functions so that they together form the lookup_range .
The MATCH portion returns the value as long as 0 is found. The value here works as the row number for INDEX .
Drag it down, you will get all the values that match the criteria.
อ่านเพิ่มเติม: How to Extract Data from Cell in Excel (5 Methods)
IV. FILTER Function
If you are using Excel 365, then you can perform the task with a single built-in function called FILTER .
The FILTER function filters a range of data based on given criteria and extracts matching records. To know about the function, visit this article:FILTER.
Let’s explore the formula
=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))
B4:B19 is the array that is to be filtered. Then we have provided the condition, based on what we will extract values. Since we need to check two criteria, we have multiplied them.
Here you will not need to drag down the formula, at one go this will provide all the values and fulfill the list.
อ่านเพิ่มเติม: Extract Filtered Data in Excel to Another Sheet (4 Methods)
บทสรุป
That’s all for today. We have listed several methods to extract data from table based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other approaches that we have missed here.
อ่านเพิ่มเติม
- How to Extract Data from Excel to Word (4 Ways)
- 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
- How to Pull Data From Another Sheet Based on Criteria in Excel
- Excel Macro:Extract Data from Multiple Excel Files (4 Methods)
- How to Extract Year from Date in Excel (3 Ways)
