นับเซลล์ของข้อมูลด้วยฟังก์ชัน SUMPRODUCT ของ Excel

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

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

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

01 จาก 04

SUMPRODUCT กับ COUNTIF และ COUNTIFS

ตั้งแต่ Excel 2007 โปรแกรมจะมีฟังก์ชัน COUNTIF และ COUNTIFS ซึ่งจะช่วยให้คุณนับเซลล์ที่ตรงกับเกณฑ์ที่ตั้งไว้อย่างน้อยหนึ่งชุด

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

02 จาก 04

SUMPRODUCT Function ไวยากรณ์และอาร์กิวเมนต์เพื่อนับเซลล์

ไวยากรณ์ ของฟังก์ชันหมายถึงเค้าโครงของฟังก์ชันและประกอบด้วยชื่อฟังก์ชันวงเล็บเครื่องหมายจุลภาคและ อาร์กิวเมนต์

เพื่อให้ฟังก์ชันสามารถนับเซลล์ได้มากกว่าที่จะทำตามวัตถุประสงค์มาตรฐานไวยากรณ์ที่ไม่ได้มาตรฐานต่อไปนี้จะต้องใช้กับ SUMPRODUCT:

= SUMPRODUCT ([condition1] * [condition2])

คำอธิบายเกี่ยวกับวิธีการทำงานของไวยากรณ์นี้อยู่ด้านล่างตัวอย่างต่อไปนี้

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

ดังที่แสดงในตัวอย่างในภาพด้านบน SUMPRODUCT ใช้เพื่อหาจำนวนเซลล์ทั้งหมดในช่วงข้อมูล A2 ถึง B6 ที่มีข้อมูลระหว่างค่า 25 ถึง 75

03 จาก 04

การป้อนฟังก์ชัน SUMPRODUCT

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

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

ในภาพด้านบนขั้นตอนต่อไปนี้ถูกใช้เพื่อป้อน SUMPRODUCT ลงในเซลล์ B7:

1. คลิกที่เซลล์ B7 ในแผ่นงาน - ตำแหน่งที่จะแสดงผลการทำงาน
2. พิมพ์สูตรต่อไปนี้ลงในเซลล์ E6 ของแผ่นงาน:

   = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75))

3. คำตอบที่ 5 ควรปรากฏในเซลล์ B7 เนื่องจากมีค่าเพียงห้าค่าในช่วง - 40, 45, 50, 55 และ 60 - ซึ่งอยู่ระหว่าง 25 ถึง 75
4. เมื่อคุณคลิกที่เซลล์ B7 สูตรที่เสร็จสมบูรณ์ = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75)) ปรากฏใน แถบสูตร เหนือแผ่นงาน

04 จาก 04

การทำลายฟังก์ชัน SUMPRODUCT

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

สำหรับวัตถุประสงค์ในการคำนวณ Excel จะกำหนดค่าเป็น 1 สำหรับองค์ประกอบอาร์เรย์เหล่านี้ซึ่งเป็น TRUE และค่า 0 สำหรับองค์ประกอบอาร์เรย์ที่เป็น FALSE

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

• มี 1 x 1 ส่งคืนค่า 1
• มี 1 x 0 ส่งกลับค่าเป็น 0

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

หรือคิดอย่างนี้ ...

อีกวิธีหนึ่งในการคิดถึงสิ่งที่ SUMPRODUCT ทำคือการคิดเลขคูณเป็นเงื่อนไข AND

เมื่อคำนึงถึงเงื่อนไขนี้เท่านั้นเมื่อมีการตอบสนองทั้งสองเงื่อนไข - ตัวเลขที่มากกว่า 25 และ น้อยกว่า 75 - ค่า TRUE (ซึ่งเท่ากับหนึ่งจำ) จะถูกส่งกลับ

จากนั้นฟังก์ชันจะสรุปค่าที่แท้จริงทั้งหมดที่จะได้รับจาก 5