หัดเล่นๆให้เป็นเซียน Excel เขียน Blog โดย แมวใหญ่ Bigcat9: SUMIF

การหาผลรวมแบบมีเงื่อนไขนั้น ใน Excel มีฟังก์ชั่นหนึ่งเรียกว่า SUMIF()
SUMIF นั้นมาจาก IF แล้วจึง SUM
ลองมาดูตัวอย่าง IF ก่อน แล้ว SUM
ต้องการหายอดขายรวมของรถยี่ห้อต่างๆ ที่มียอดขายมากกว่า 150 คันขึ้นไป

หากมองว่าใช้ IF เพื่อหาคำตอบออกมา จะได้ว่าเพิ่มคอลัมน์ขึ้นมา 1 คอลัมน์เพื่อสร้างเงื่อนไข IF แล้วหาผลรวม(SUM) แบบนี้ก็ทำได้

หากจะใช้ฟังก์ชั่น SUMIF() ซึ่งมี ไวยากรณ์ดังนี้
=SUMIF(range,criteria,sum_range)
=SUMIF(ข้อมูลหรือชุดข้อมูลที่ให้อ่านค่า ,เงื่อนไข ,ผลรวมของชุดข้อมูล)
จะได้ว่า..

มาดู SUMIF แบบเงื่อนไขเดียวอีกซักตัวอย่าง
โจทย์ ให้หาผลรวมของค่าที่มากกว่า 30

สามารถทำได้หลายๆวิธี เช่น

1.ใช้ IF ธรรมดา กำหนดเงื่อนไข แล้วจึง SUM หาผลลัพท์
2.ใช้ SUMIF
3. ใช้ ARRAY

- - - - - - - - - - -- - -
SUMIF เงื่อนไขเดียวอีกซักตัวอย่าง
ให้หาผลรวมของจำนวนและยอดเงินจากยอดขายของสินค้าแต่ละชนิดในแต่ละเดือน

- - - - - - - - - - - - - -- -

การหาผลรวมที่มากกว่า 1 เงื่อนไข
โดยปกตินั้น SUMIFไม่สามารถหาผลรวมที่มากกว่า 1 เงื่อนไขได้ แต่ก็ยังมีวิธีอื่นๆที่สามารถช่วยได้ดังนี้ ตัวอย่างโจทย์ ให้หาผลรวมของค่าที่มากกว่า 20 และน้อยกว่า 50

มีวิธีหาผลลัพท์ได้หลายๆวิธีเช่น
1. ใช้ IF ธรรมดากำหนดเงื่อนไขก่อน แบบกระจายสูตรออกมา แล้วจึง SUM
ใช้ IF บังคับเงื่อนไขแรก ถ้ามากกว่า 20 ให้เป็น ตัวมันเอง ถ้าไม่ใช่ให้เป็น 0

IF บังคับเงื่อนไขที่ 2 ถ้าน้อยกว่า 50 ให้เป็น 1 ถ้าไม่ใช่ให้เป็น 0
แล้วนำทั้ง 2 กรณีมา คูณกัน
- - - - - - - - - - - -
2. ใช้ IF ธรรมดาแต่รวบสูตรเข้ามาหากัน แล้วจึง SUM
มาจากข้อ 1. โดยรวบสูตรเข้ามา
- - - - - - - - - - - -
3. ใช้ ARRAY จากวิธีที่ 2 ใช้ IF ธรรมดาแต่รวบสูตรเข้ามา แล้วทำเป็น Array

4. โจทย์บังคับ 2 เงื่อนไข อยู่แล้ว จะใช้ IF(AND() ก็ได้

- - - - - - - - - - - -- - -
อีกตัวอย่าง การหาผลรวม ที่มากกว่า 1 เงื่อนไข
โจทย์ต้องการให้หายอดขาย ของรถ HONDA ที่มากว่า 15 คัน

วิธีที่ 1 ใช้ IF ธรรมดา กำหนดเงื่อนไขโดยกระจายสูตรออกมา
เงื่อนไขแรก กำหนดว่า ถ้าเป็น HONDA ให้เป็น 1 ถ้าไม่ใช่ให้เป็น 0
เงื่อนไขที่ 2 กำหนดว่า ถ้ามากกว่า 15 ให้เป็นตัวมันเอง ถ้าไม่ใช่ให้เป็น 0
จากนั้น นำทั้ง 2 เงื่อนไขมาคูณกัน
- - - - - - - - - - - -

วิธีที่ 2. ใช้ IF ธรรมดาแบบวิธีที่1 แต่รวบสูตรเข้ามาให้กระชับ
- - - - - - - - - - - -
วิธีที่ 3.โจทย์บังคับทั้ง 2 กรณีอยู่แล้ว ใช้ IF(AND()
บังคับว่า ถ้าเป็น HONDA และ มากกว่า 15 ให้เป็นตัวมันเอง ถ้าไม่ใช่ให้เป็น 0

- - - - - - - - - - - -
วิธีที่ 4. ใช้ Array โดยใช้ IF ธรรมดา วิธีที่ 2 แล้ว SUM ทำเป็น Array
- - - - - - - - - - - -
โจทย์ให้หาผลรวม ถ้าคอลัมน์ B เป็น TOYOTA และ คอลัมน์ C มากกว่า 10

วิธีที่ 1. ใช้ IF ธรรมดากำหนดเงื่อนไข แรก ถ้าเป็น TOYOTA ให้เป็น 1 ถ้าไม่ใช่ให้เป็น 0
เงื่อนไขที่ 2 ถ้ามากกว่า 10 ให้เป็นตัวมันเอง ถ้าไม่ใช่ให้เป็น 0
จากนั้น นำทั้ง 2 กรณี มาคูณกัน .
- - - - - - - - - - - -
วิธีที่ 2. โจทย์บังคับทั้ง 2 เงื่อนไขอยู่แล้ว ใช้ IF(AND()
บังคับว่า ถ้า คอลัมน์B เป็น TOYOTA และ คอลัมน์ C มากกว่า 10 ให้เป็น คอลัมน์ C ถ้าไม่ใช่ให้เป็น 0
- - - - - - - - - - - -

วิธีที่ 3. ทำเป็น Array โดยมาจากวิธีที่ 1 ใช้ IF ธรรมดา แล้ว SUM แต่ทำเป็น Array
- - - - - - - - - - - - - - - -

ตัวอย่างต่อไป
โจทย์ให้หายอดขาย ของรถยี่ห้อ BMW ที่เป็นสีดำ

วิธีที่ 1. ใช้ IFธรรมดาบังคับ เงื่อนไขแรก ถ้าเป็น BMW ให้เป็น 1 ถ้าไม่ใช่ให้เป็น 0 คูณกับเงื่อนไขหลังโดยบังคับเงื่อนไขหลัง ถ้าเป็น สีดำ (BLACK) ให้เป็น จำนวนที่ขาย ถ้าไม่ใช่ให้เป็น 0
- - - - - - - - - - - -

วิธีที่ 2.เนื่องจากโจทย์บังคับให้ต้องเป็นทั้ง 2 กรณี คือ BMW และ BLACK จึงใช้ IF(AND() หาคำตอบ โดย ถ้า B4 เป็น BMW และ C4 เป็น BLACK ให้เป็น จำนวนที่ขาย ถ้าไม่ใช่ให้เป็น 0
- - - - - - - - - - - -
วิธีที่ 3. ใช้ Array ช่วยโดยหลักการจากวิธีที่ 1. แต่บังคับทั้งชุดข้อมูล คือ B4:B12 และ C4:C12 และ D4:D12 ที่เซลล์ใดๆ พิมพ์ =SUM(IF(B4:B12="BMW",1,0)*IF(C4:C12="BLACK",1,D4:D12,0)) จากนั้น บังคับกด Ctrl + Shift +Enter พร้อมกัน...ติดรูป

{=SUM(IF(B4:B12="BMW",1,0)*IF(C4:C12="BLACK",1,D4:D12,0))}

ตัวอย่าง
เงื่อนไขให้หาผลรวมของจำนวนรถ ที่เป็นยี่ห้อ BMW หรือ AUDI

ทำได้หลายๆวิธี

1. ใช้ IF ธรรมดา โดยการ เพิ่มคอลัมน์ขึ้นมา 2 คอลัมน์และนำผลลัพท์แต่ละคอลัมน์มาบวกกัน การเพิ่มคอลัมน์จะช่วยลดความยุ่งยากไปได้มากมาย และยังตรวจสอบได้ง่าย