ผลรวมเซลล์ที่ตรงตามเกณฑ์หลายเกณฑ์ด้วย Excel SUMPRODUCT

2024 ผู้เขียน: Abigail Brown | [email protected]. แก้ไขล่าสุด: 2023-12-17 07:03

01 จาก 02

ผลรวมเซลล์ที่อยู่ระหว่างสองค่า

ฟังก์ชัน SUMPRODUCT ใน Excel เป็นฟังก์ชันอเนกประสงค์ที่จะให้ผลลัพธ์ที่แตกต่างกันขึ้นอยู่กับวิธีการป้อนอาร์กิวเมนต์ของฟังก์ชัน

ตามปกติ SUMPRODUCT จะคูณองค์ประกอบของอาร์เรย์อย่างน้อยหนึ่งอาร์เรย์เพื่อให้ได้ผลิตภัณฑ์ จากนั้นจึงบวกหรือรวมผลิตภัณฑ์เข้าด้วยกัน

โดยการปรับไวยากรณ์ของฟังก์ชัน สามารถใช้เพื่อรวมเฉพาะข้อมูลในเซลล์ที่ตรงตามเกณฑ์เฉพาะ

ตั้งแต่ Excel 2007 โปรแกรมมีสองฟังก์ชัน - SUMIF และ SUMIFS - ซึ่งจะรวมข้อมูลในเซลล์ที่ตรงตามเกณฑ์ที่กำหนดตั้งแต่หนึ่งเกณฑ์ขึ้นไป

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

SUMPRODUCT ฟังก์ชัน Syntax to Sum Cells

ไวยากรณ์ที่ใช้ในการรับ SUMPRODUCT เพื่อรวมข้อมูลในเซลล์ที่ตรงตามเงื่อนไขเฉพาะคือ:

=SUMPRODUCT([condition1][condition2][array])

condition1, condition2 - เงื่อนไขที่ต้องปฏิบัติตามก่อนที่ฟังก์ชันจะค้นหาผลคูณของอาร์เรย์

array - ช่วงเซลล์ที่อยู่ติดกัน

ตัวอย่าง: การรวมข้อมูลในเซลล์ที่ตรงตามเงื่อนไขหลายข้อ

ตัวอย่างในภาพด้านบนเพิ่มข้อมูลในเซลล์ในช่วง D1 ถึง E6 ที่อยู่ระหว่าง 25 ถึง 75

การเข้าสู่ฟังก์ชัน SUMPRODUCT

เนื่องจากตัวอย่างนี้ใช้ฟังก์ชัน SUMPRODUCT ในรูปแบบที่ผิดปกติ จึงไม่สามารถใช้กล่องโต้ตอบของฟังก์ชันเพื่อเข้าสู่ฟังก์ชันและอาร์กิวเมนต์ของฟังก์ชันได้ แต่จะต้องพิมพ์ฟังก์ชันลงในเซลล์เวิร์กชีตด้วยตนเอง

1. คลิกที่เซลล์ B7 ในเวิร์กชีตเพื่อทำให้เป็นเซลล์ที่ใช้งานอยู่
2. ป้อนสูตรต่อไปนี้ลงในเซลล์ B7: =SUMPRODUCT(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
3. คำตอบ 250 ควรปรากฏในเซลล์ B7
4. คำตอบมาถึงแล้วโดยการเพิ่มตัวเลขห้าตัวในช่วง (40, 45, 50, 55 และ 60) ที่อยู่ระหว่าง 25 ถึง 75 รวมเป็น 250

ทำลายสูตร SUMPRODUCT

เมื่อใช้เงื่อนไขสำหรับอาร์กิวเมนต์ SUMPRODUCT จะประเมินแต่ละองค์ประกอบอาร์เรย์กับเงื่อนไขและส่งกลับค่าบูลีน (TRUE หรือ FALSE)

สำหรับวัตถุประสงค์ในการคำนวณ Excel กำหนดค่า 1 สำหรับองค์ประกอบอาร์เรย์ที่เป็น TRUE (ตรงตามเงื่อนไข) และค่า 0สำหรับองค์ประกอบอาร์เรย์ที่เป็น FALSE (ไม่ตรงตามเงื่อนไข)

ตัวอย่างเช่น หมายเลข 40:

• เป็น TRUE สำหรับเงื่อนไขแรก ดังนั้นค่า 1 ถูกกำหนดในอาร์เรย์แรก;
• เป็น TRUE สำหรับเงื่อนไขที่สอง ดังนั้นค่า 1 ถูกกำหนดในอาร์เรย์ที่สอง

หมายเลข 15:

• เป็น FALSE สำหรับเงื่อนไขแรก ดังนั้นค่า 0 ถูกกำหนดในอาร์เรย์แรก;
• เป็น TRUE สำหรับเงื่อนไขที่สอง ดังนั้นค่า 1 ถูกกำหนดในอาร์เรย์ที่สอง

จำนวนและศูนย์ที่สอดคล้องกันในแต่ละอาร์เรย์จะถูกคูณเข้าด้วยกัน:

• สำหรับหมายเลข 40 - เรามี 1 x 1 คืนค่า 1;
• สำหรับหมายเลข 15 - เรามี 0 x 1 ส่งคืนค่า 0

การคูณหนึ่งและศูนย์ด้วยช่วง

เลขศูนย์เหล่านี้คูณด้วยตัวเลขในช่วง A2: B6

ทำเพื่อให้เราได้ตัวเลขที่จะสรุปโดยฟังก์ชัน

สิ่งนี้ใช้ได้เพราะ:

• 1 คูณตัวเลขใด ๆ เท่ากับจำนวนเดิม
• 0 คูณจำนวนใด ๆ เท่ากับ 0

เราลงเอยด้วย:

• 140=40

  015=0

  022=0

  145=45150=50

  155=55

  025=0

  075=0

  160=60

  0100=0

สรุปผล

SUMPRODUCT จากนั้นสรุปผลลัพธ์ข้างต้นเพื่อค้นหาคำตอบ

40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250