ฟังก์ชัน SUMIFS วิธีใช้ฟังก์ชันเพื่อรวมเงื่อนไขต่างๆ ใน ​​Excel

ฟังก์ชัน Sumifs ใน Excelมีประโยชน์อย่างยิ่งเมื่อคุณต้องรวมเงื่อนไขต่างๆ เข้าด้วยกัน ด้าน ล่างนี้เป็นรายละเอียดเกี่ยวกับวิธีใช้ฟังก์ชัน Sumifs ใน Microsoft Excel

ฟังก์ชัน SUMIFS ใน Excel เป็นหนึ่งในฟังก์ชันพื้นฐานของ Excelซึ่งเป็นฟังก์ชันการคำนวณที่ใช้กันทั่วไปใน Excel ในการคำนวณผลรวมใน Excel เราจะใช้ฟังก์ชัน SUMหากเราต้องการเพิ่มเงื่อนไขบางอย่างให้กับฟังก์ชันผลรวม เราจะใช้ฟังก์ชัน SUMIF ในกรณีที่ตารางข้อมูลต้องการผลรวมที่มีเงื่อนไขหลายข้อ เราต้องใช้ฟังก์ชัน SUMIFS บทความด้านล่างนี้จะแนะนำวิธีใช้ฟังก์ชัน SUMIFS ใน Excel

• MS Excel - บทที่ 5: สูตรและฟังก์ชันของ Excel
• วิธีรวมฟังก์ชัน Sumif และฟังก์ชัน Vlookup ใน Excel
• 3 วิธีในการคำนวณผลรวมใน Excel
• วิธีแก้ไขข้อผิดพลาดของฟังก์ชัน SUM ที่ไม่เพิ่มใน Excel

สารบัญของบทความ

• ไวยากรณ์ของฟังก์ชัน SUMIFS ใน Excel
• หมายเหตุเมื่อใช้ฟังก์ชัน SUMIFS ใน Excel
• คำแนะนำในการใช้ฟังก์ชัน SUMIFS ใน Excel
  • ใช้ไวด์การ์ด
  • ใช้ Named Ranges กับฟังก์ชัน SUMIFS
  • ตัวอย่างฟังก์ชัน SUMIFS ใน Excel
• ข้อจำกัดของฟังก์ชัน SUMIFS

ไวยากรณ์ของฟังก์ชัน SUMIFS ใน Excel

ไวยากรณ์ของฟังก์ชัน SUMIFS ใน Excel คือ=SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,...)

ในนั้น:

• Sum_range: คือเซลล์ที่ต้องสรุปในตารางข้อมูล ค่าว่างและค่าข้อความจะถูกละเว้น พารามิเตอร์ที่จำเป็น
• Criteria_range1: ช่วงที่ต้องทดสอบโดยใช้เกณฑ์เกณฑ์ 1 เป็นค่าที่ต้องการ
• เกณฑ์1: เงื่อนไขที่ใช้กับเกณฑ์_ช่วง1 อาจเป็นตัวเลข นิพจน์ การอ้างอิงเซลล์เพื่อกำหนดว่าเซลล์ใดในเกณฑ์_ช่วง1 ที่จะนำมารวมกัน รวมถึงค่าที่ต้องการด้วย
• Criteria_range2, เกณฑ์2,...: ช่วงและเงื่อนไขเพิ่มเติมที่ไม่บังคับ, สูงสุด 127 คู่ของเกณฑ์_ช่วง, เกณฑ์

หมายเหตุเมื่อใช้ฟังก์ชัน SUMIFS ใน Excel

• สำหรับแต่ละเซลล์ในช่วง ช่วง sum_range จะถูกรวมเมื่อตรงตามเงื่อนไขที่เกี่ยวข้องทั้งหมดซึ่งพิจารณาว่าเป็นจริงสำหรับเซลล์นั้น
• เซลล์ในช่วง sum_range ที่มี TRUE ถือเป็น 1 เซลล์ที่มี FALSE ถือเป็น 0
• เกณฑ์_ช่วงแต่ละเกณฑ์จะต้องมีขนาดการเลือกเท่ากับช่วงผลรวม ช่วงเกณฑ์และช่วงผลรวมจะต้องมีจำนวนแถวและจำนวนคอลัมน์เท่ากัน
• เกณฑ์สามารถใช้อักขระเครื่องหมายคำถาม (?) แทนอักขระเดี่ยว เครื่องหมายดอกจัน (*) แทนสตริงได้ หากเงื่อนไขเป็นเครื่องหมายคำถามหรือเครื่องหมายดอกจัน คุณต้องใส่เครื่องหมาย ~ นำหน้า ค่าเงื่อนไขจะเป็นข้อความที่อยู่ใน "
• มีการนำเงื่อนไขหลายข้อไปใช้โดยใช้ตรรกะ AND เช่น เกณฑ์1 และเกณฑ์2 เป็นต้น
• แต่ละช่วงเพิ่มเติมจะต้องมีจำนวนแถวและคอลัมน์เท่ากันกับช่วงทั้งหมด แต่ช่วงไม่จำเป็นต้องอยู่ติดกัน หากคุณระบุช่วงที่ไม่ตรงกัน คุณจะได้รับข้อผิดพลาด #VALUE
• สตริงข้อความในเกณฑ์จะต้องอยู่ในเครื่องหมายคำพูด ("") เช่น "apple", ">32", "jap*"
• การอ้างอิงเซลล์ในเกณฑ์ไม่สามารถใส่เครื่องหมายคำพูดได้ เช่น "<>
• SUMIF และ SUMIFS สามารถจัดการช่วงได้ แต่ไม่สามารถจัดการอาร์เรย์ได้ ซึ่งหมายความว่าคุณไม่สามารถใช้ฟังก์ชันอื่นๆ เช่น YEAR ในช่วงเกณฑ์ได้ เนื่องจากผลลัพธ์จะเป็นอาร์เรย์ หากคุณต้องการฟังก์ชันนี้ ให้ใช้ฟังก์ชัน SUMPRODUCT
• ลำดับของอาร์กิวเมนต์ระหว่างฟังก์ชัน SUMIFS และ SUMIF นั้นแตกต่างกัน ช่วงผลรวมเป็นอาร์กิวเมนต์แรกใน SUMIFS แต่เป็นอาร์กิวเมนต์ที่สามใน SUMIF

คำแนะนำในการใช้ฟังก์ชัน SUMIFS ใน Excel

ใช้ไวด์การ์ด

อักขระตัวแทนเช่น '*' และ '?' สามารถใช้ในอาร์กิวเมนต์เกณฑ์เมื่อใช้ฟังก์ชัน SUMIFS การใช้ไวด์การ์ดเหล่านี้จะช่วยให้คุณค้นหารายการที่ตรงกันแต่ไม่ตรงทั้งหมด

เครื่องหมายดอกจัน (*) - ตรงกับสตริงอักขระใดๆ ซึ่งสามารถใช้หลัง ก่อน หรือกับเกณฑ์โดยรอบ เพื่ออนุญาตให้ใช้เกณฑ์การค้นหาได้บางส่วน

ตัวอย่างเช่น ถ้าคุณใช้เกณฑ์ต่อไปนี้ในฟังก์ชัน SUMIFS:

• N * - หมายถึงเซลล์ทั้งหมดในช่วงที่ขึ้นต้นด้วย N
• * N - หมายถึงเซลล์ทั้งหมดในช่วงที่ลงท้ายด้วย N
• *N* - เซลล์ที่มี N

เครื่องหมายคำถาม (?) - จับคู่อักขระตัวเดียว สมมติว่าคุณใช้ N?r เป็นเกณฑ์ "?" ที่นี่จะแทนที่อักขระตัวเดียว N?r จะตรงกับ North, Nor เป็นต้น อย่างไรก็ตาม จะไม่คำนึงถึงชื่อ

จะเกิดอะไรขึ้นหากข้อมูลที่ระบุมีเครื่องหมายดอกจันหรือเครื่องหมายคำถามจริง?

ในกรณีนี้ คุณสามารถใช้เครื่องหมายตัวหนอน (~) คุณต้องพิมพ์ “~” หน้าเครื่องหมายคำถามในกรณีนี้

ใช้ Named Ranges กับฟังก์ชัน SUMIFS

Named Range คือชื่อที่สื่อความหมายชุดของเซลล์หรือช่วงของเซลล์ในเวิร์กชีต คุณสามารถใช้ Named Ranges ในขณะที่ใช้ฟังก์ชัน SUMIFS ได้

ตัวอย่างฟังก์ชัน SUMIFS ใน Excel

เราจะคำนวณผลรวมด้วยตารางข้อมูลด้านล่างโดยมีเงื่อนไขต่างๆ มากมาย

ตัวอย่างที่ 1 : คำนวณสินค้าทั้งหมดที่พนักงานของ Hoai ขายด้วยราคาต่อหน่วยน้อยกว่า 400,000 VND

ในกล่องอินพุตผลลัพธ์ เราป้อนสูตร =SUMIFS(D2:D7,C2:C7,"Hoai",E2:E7,"<400000") แล้วตามด้วย="" press="">

ในนั้น:

• D2:D7 คือพื้นที่ที่ต้องคำนวณรายการทั้งหมด
• C2:C7 คือช่วงค่าเงื่อนไขชื่อพนักงาน
• “ฮ่วย” คือเงื่อนไขสำหรับชื่อของพนักงานที่ต้องการคำนวณจำนวนรายการที่มีอยู่ในพื้นที่ C2:C7
• E2:E7 คือสภาพของสินค้า
• “400,000” เป็นเงื่อนไขสำหรับบรรจุสินค้าในพื้นที่ E2:E7

ผลลัพธ์ที่ได้คือจำนวนรายการทั้งหมดดังแสดงด้านล่าง

ตัวอย่างที่ 2 : คำนวณจำนวนสินค้าทั้งหมดที่ขายโดยพนักงานชาวรัสเซียด้วยหมายเลขซีเรียล <>

ในกล่องอินพุตผลลัพธ์ เราป้อนสูตร =SUMIFS(D2:D7,C2:C7,"=Russia",A2:A7,"<5") แล้ว="" press="" enter.="" ที่ = "" name="" multiplication="" tablet="" "nga"="" enter="" sign="or" Remove="" sign="even">

เป็นผลให้เราได้รับจำนวนสินค้าทั้งหมดที่ขายโดยพนักงานชาวรัสเซียที่มีหมายเลขซีเรียล < 5="" ซึ่งก็คือ 550 รายการ="">

ตัวอย่างที่ 3 : คำนวณจำนวนสินค้าทั้งหมดที่ขายโดยพนักงานชาวรัสเซีย ไม่รวมผลิตภัณฑ์ผ้าพันคอ

ในกล่องอินพุตผลลัพธ์ เราป้อนสูตร =SUMIFS(D2:D7,C2:C7,“Russia”,B2:B7,“<>scarf”) โดยที่เครื่องหมาย <> ใช้เพื่อยกเว้นวัตถุบางอย่างในพื้นที่ข้อมูลตามเงื่อนไข

ผลลัพธ์ที่ได้คือจำนวนสินค้าทั้งหมดที่แสดงด้านล่าง

ตัวอย่างที่ 4:

คำนวณผลรวมของสถิติทั้งหมดสำหรับโปเกมอนประเภทน้ำรุ่นที่ 1:

เงื่อนไขในที่นี้คือ น้ำและเจนเนอเรชั่นคือ 1

หมายเหตุ: ชุดข้อมูลทั้งหมดจะดำเนินต่อไปหลังจากแถวที่ 14 ไปจนถึงแถวที่ 759

นี่คือรายละเอียดทีละขั้นตอน:

1. เลือกเซลล์H3
2. พิมพ์=SUMIFS
3. ดับเบิลคลิกที่คำสั่ง SUMIFS
4. เลือกช่วงรวมC2:C759เป็น
5. พิมพ์,
6. เลือกช่วงสำหรับเงื่นไขแรกB2:B759(ค่าประเภท 1)
7. เลือกเกณฑ์ (เซลล์F3ที่มีค่าWater)
8. พิมพ์ ,
9. เลือกช่วงสำหรับเงื่อนไขที่สองจากD2:D759(ค่าการสร้าง)
10. พิมพ์,
11. กำหนดเกณฑ์ (เซลล์ G3 มีค่า 1)
12. กดปุ่มตกลง .

หมายเหตุ:คุณสามารถเพิ่มเงื่อนไขเพิ่มเติมได้โดยการทำซ้ำขั้นตอนที่ 9 ถึง 12 ก่อนที่จะกด Enter

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

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

ข้อจำกัดของฟังก์ชัน SUMIFS

ฟังก์ชัน SUMIFS มีข้อจำกัดบางประการที่คุณควรทราบ:

• เงื่อนไขใน SUMIFS จะรวมกันโดยใช้ตรรกะ AND กล่าวอีกนัยหนึ่ง เงื่อนไขทั้งหมดต้องเป็น TRUE จึงจะสามารถรวมเซลล์ไว้ในผลรวมได้ เมื่อต้องการรวมเซลล์โดยใช้ตรรกะ OR คุณสามารถใช้วิธีแก้ไขปัญหาอื่นในกรณีง่ายๆ ได้
• ฟังก์ชัน SUMIFS ต้องใช้ช่วงจริงสำหรับอาร์กิวเมนต์ช่วงทั้งหมด คุณไม่สามารถใช้อาร์เรย์ได้ ซึ่งหมายความว่าคุณไม่สามารถทำสิ่งต่างๆ เช่น แยกปีจากวันที่ภายในฟังก์ชัน SUMIFS ได้ หากต้องการเปลี่ยนค่าที่ปรากฏในอาร์กิวเมนต์ช่วงก่อนที่จะใช้เกณฑ์ ฟังก์ชัน SUMPRODUCT เป็นโซลูชันที่ยืดหยุ่น
• SUMIFS ไม่คำนึงถึงขนาดตัวพิมพ์ หากต้องการรวมค่าตามเงื่อนไขที่คำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ คุณสามารถใช้สูตรตามฟังก์ชัน SUMPRODUCT กับฟังก์ชัน EXACT ได้

วิธีทั่วไปในการแก้ไขข้อจำกัดข้างต้นคือการใช้ฟังก์ชัน SUMPRODUCT ใน Excel เวอร์ชันปัจจุบัน อีกทางเลือกหนึ่งคือการใช้ฟังก์ชัน BYROW และ BYCOL ที่ใหม่กว่า

วิธีแก้ไขข้อผิดพลาด #VALUE ในฟังก์ชัน SUMIFS ของ Excel

ปัญหา:สูตรอ้างอิงถึงเซลล์ในสมุดงานแบบปิด

ฟังก์ชัน SUMIF ที่อ้างอิงเซลล์หรือช่วงของเซลล์ในสมุดงานแบบปิดจะส่งผลให้เกิดข้อผิดพลาด #VALUE!

หมายเหตุ: นี่เป็นข้อผิดพลาดทั่วไป คุณมักจะพบข้อความนี้เมื่อใช้ฟังก์ชัน Excel อื่นๆ เช่น COUNTIF, COUNTIFS, COUNTBLANK...

สารละลาย:

เปิดสมุดงานที่มีอยู่ในสูตรนี้ กด F9 เพื่อรีเฟรชสูตร คุณยังสามารถแก้ไขปัญหาได้โดยใช้ทั้งฟังก์ชัน SUM และ IF ในสูตรอาร์เรย์

ปัญหา:สตริงเกณฑ์มีความยาวเกิน 255 อักขระ

ฟังก์ชัน SUMIFS ส่งกลับผลลัพธ์ที่ไม่ถูกต้องเมื่อคุณพยายามรวมสตริงที่ยาวเกิน 255 อักขระ

สารละลาย:

ย่อสตริงให้สั้นลงถ้าเป็นไปได้ หากเป็นไปไม่ได้ ให้ใช้ฟังก์ชัน CONCATENATE หรือตัวดำเนินการ & เพื่อแยกค่าออกเป็นหลายสตริง ตัวอย่างเช่น:

=SUMIF(B2:B12,"long string"&"another long string")

ปัญหา: ใน SUMIFS อาร์กิวเมนต์ Criteria_range ไม่สอดคล้องกับอาร์กิวเมนต์ sum_range

อาร์กิวเมนต์ช่วงจะต้องเหมือนกันเสมอใน SUMIFS นั่นหมายความว่าอาร์กิวเมนต์ Criteria_range และ sum_range ควรอ้างอิงถึงจำนวนแถวและคอลัมน์ที่เท่ากัน

ในตัวอย่างต่อไปนี้ สูตรจะส่งคืนยอดขายแอปเปิ้ลรายวันทั้งหมดในเมือง Bellevue อย่างไรก็ตาม อาร์กิวเมนต์ sum_range (C2:C10) ไม่สอดคล้องกับจำนวนแถวและคอลัมน์ใน Criteria_range (A2:A12 & B2:B12) การใช้สูตร=SUMIFS(C2:C10,A2:A12,A14,B2:B12,B14)จะทำให้เกิดข้อผิดพลาด #VALUE

สารละลาย:

เปลี่ยนsum_rangeเป็น C2:C12 แล้วลองใช้สูตรอีกครั้ง

ขอให้คุณประสบความสำเร็จ!