SUMIF และ SUMIFS: การรวมค่าแบบมีเงื่อนไขใน Excel

การบวกตัวเลขเป็นเรื่องง่าย — ฟังก์ชัน SUM ของ Excel จัดการได้ในไม่กี่วินาที แต่จะเกิดอะไรขึ้นเมื่อคุณต้องการรวมเฉพาะค่าที่ตรงตามเงื่อนไขที่กำหนด? นั่นคือจุดที่ SUMIF และ SUMIFS กลายเป็นเครื่องมือที่thiขาดไม่ได้ ฟังก์ชันทั้งสองนี้ช่วยให้คุณบวกตัวเลขแบบเลือกได้ตามเงื่อนไขเดียวหรือหลายเงื่อนไข และนับเป็นหนึ่งในสูตรที่มีประโยชน์ที่สุดสำหรับการทำงานกับสเปรดชีตในชีวิตประจำวัน

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

SUMIF คืออะไร?

SUMIF รวมค่าในช่วงเซลล์เฉพาะเมื่อเซลล์ที่สอดคล้องกันในช่วงอื่นตรงตามเงื่อนไขที่คุณกำหนด เหมาะอย่างยิ่งเมื่อคุณมีเกณฑ์เพียงข้อเดียว เช่น "รวมยอดขายทั้งหมดจากภาคตะวันออก" หรือ "รวมค่าใช้จ่ายที่มากกว่า 500 ดอลลาร์"

ไวยากรณ์ของ SUMIF

=SUMIF(range, criteria, [sum_range])

• range — เซลล์ที่คุณต้องการประเมินตามเกณฑ์
• criteria — เงื่อนไขที่ต้องเป็นจริง สามารถเป็นตัวเลข ข้อความ การอ้างอิงเซลล์ หรือนิพจน์ก็ได้
• sum_range — (ไม่บังคับ) เซลล์ที่ต้องการรวมค่าจริงๆ หากละไว้ Excel จะรวมค่าใน range แทน

ตัวอย่างพื้นฐานของ SUMIF

สมมติว่าคอลัมน์ A มีหมวดหมู่สินค้า และคอลัมน์ B มียอดขาย หากต้องการรวมยอดขายทั้งหมดสำหรับ "Electronics":

=SUMIF(A2:A100, "Electronics", B2:B100)

หากต้องการรวมค่าทั้งหมดในคอลัมน์ B ที่มากกว่า 1000:

=SUMIF(B2:B100, ">1000")

โปรดสังเกตว่าเมื่อ range และ sum_range เป็นช่วงเดียวกัน คุณสามารถละอาร์กิวเมนต์ที่สามได้ และโปรดระวังว่าตัวดำเนินการเปรียบเทียบ เช่น >, <, >= และ <> ต้องอยู่ในเครื่องหมายคำพูด

SUMIFS คืออะไร?

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

ไวยากรณ์ของ SUMIFS

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range — เซลล์ที่ต้องการรวมค่า (เสมออยู่เป็นอาร์กิวเมนต์แรก)
• criteria_range1 — ช่วงแรกที่ต้องการประเมิน
• criteria1 — เงื่อนไขสำหรับช่วงแรก
• criteria_range2, criteria2 — คู่ช่วง/เงื่อนไขเพิ่มเติม (รองรับได้สูงสุด 127 คู่)

ตัวอย่างพื้นฐานของ SUMIFS

ใช้ชุดข้อมูลเดียวกัน หากต้องการรวมยอดขาย "Electronics" ในภาค "East" (สมมติว่าคอลัมน์ C มีชื่อภาค):

=SUMIFS(B2:B100, A2:A100, "Electronics", C2:C100, "East")

สูตรนี้ตรวจสอบแต่ละแถว: ถ้าคอลัมน์ A เป็น "Electronics" และคอลัมน์ C เป็น "East" ค่าที่สอดคล้องในคอลัมน์ B จะถูกรวมเข้าในผลลัพธ์

คำแนะนำปฏิบัติ: การวิเคราะห์ข้อมูลยอดขาย

มาสร้างสถานการณ์จริงกัน สมมติว่าคุณกำลังจัดการรายงานยอดขายที่มีคอลัมน์ดังนี้:

A: พนักงานขาย	B: ภาค	C: สินค้า	D: เดือน	E: รายรับ
Alice	East	Laptops	January	$4,200
Bob	West	Phones	January	$3,800
Alice	East	Phones	February	$2,900
Carol	East	Laptops	February	$5,100
Bob	West	Laptops	February	$4,400

ข้อมูลของคุณอยู่ในแถวที่ 2 ถึง 500 ต่อไปนี้คือสูตรสำหรับตอบคำถามทางธุรกิจที่พบบ่อย:

รายรับรวมของ Alice:

=SUMIF(A2:A500, "Alice", E2:E500)

รายรับรวมในภาค East:

=SUMIF(B2:B500, "East", E2:E500)

รายรับรวมจาก Laptops ในภาค East:

=SUMIFS(E2:E500, C2:C500, "Laptops", B2:B500, "East")

รายรับรวมของ Alice จากการขาย Laptops ในเดือน January:

=SUMIFS(E2:E500, A2:A500, "Alice", C2:C500, "Laptops", D2:D500, "January")

สังเกตว่าแต่ละเงื่อนไขที่เพิ่มขึ้นจะช่วยกรองผลลัพธ์ให้แคบลงเรื่อยๆ การวิเคราะห์แบบนี้หากทำด้วยมือต้องใช้เวลาหลายนาที แต่ด้วย SUMIFS จะได้ผลลัพธ์ในทันที หากคุณกำลังสร้างเครื่องมือรายงานแบบครบวงจร เทคนิคนี้เข้ากันได้ดีกับเนื้อหาในคู่มือ แดชบอร์ดยอดขายใน Excel: ติดตาม KPI และประสิทธิภาพ

การใช้การอ้างอิงเซลล์แทนการพิมพ์ข้อความตรงๆ

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

วาง "Alice" ในเซลล์ H2 และ "Laptops" ในเซลล์ H3 สูตรของคุณจะกลายเป็น:

=SUMIFS(E2:E500, A2:A500, H2, C2:C500, H3)

เมื่อเปลี่ยน H2 เป็น "Bob" สูตรจะคำนวณยอดขาย Laptops ของ Bob ใหม่ทันที วิธีนี้จำเป็นมากสำหรับแดชบอร์ดแบบโต้ตอบ การทำความเข้าใจ การอ้างอิงเซลล์ใน Excel — แบบสัมพัทธ์และแบบสัมบูรณ์ จะช่วยให้คุณมั่นใจว่าการอ้างอิงเหล่านี้จะไม่เลื่อนโดยไม่คาดคิดเมื่อคุณคัดลอกสูตร

อักขระตัวแทน (Wildcard) ใน SUMIF และ SUMIFS

ฟังก์ชันทั้งสองรองรับอักขระตัวแทน ซึ่งมีประโยชน์มากเมื่อข้อมูลของคุณไม่สม่ำเสมอ:

• * — จับคู่กับอักขระลำดับใดก็ได้ "Lap*" จับคู่กับ "Laptops", "Laptop Bag" และอื่นๆ
• ? — จับคู่กับอักขระเดี่ยวใดก็ได้ "Bo?" จับคู่กับ "Bob", "Boy", "Bog"
• ~ — อักขระ Escape ใช้ ~* เพื่อจับคู่กับเครื่องหมายดอกจันแบบตัวอักษรจริงๆ

ตัวอย่าง — รวมรายรับทั้งหมดสำหรับสินค้าที่ขึ้นต้นด้วย "Lap":

=SUMIF(C2:C500, "Lap*", E2:E500)

การรวมค่าแบบมีเงื่อนไขตามวันที่

SUMIFS จัดการวันที่ได้โดยธรรมชาติ เนื่องจาก Excel เก็บวันที่เป็นตัวเลขลำดับ คุณสามารถใช้ตัวดำเนินการเปรียบเทียบเพื่อรวมค่าในช่วงวันที่ได้

สมมติว่าคอลัมน์ D มีค่าวันที่จริง (ไม่ใช่ข้อความธรรมดา) หากต้องการรวมรายรับตั้งแต่วันที่ 1 มกราคม ถึง 31 มีนาคม 2024:

=SUMIFS(E2:E500, D2:D500, ">="&DATE(2024,1,1), D2:D500, "<="&DATE(2024,3,31))

ตัวดำเนินการ & ใช้ต่อตัวดำเนินการเปรียบเทียบ (ในรูปข้อความ) เข้ากับผลลัพธ์ของฟังก์ชัน DATE รูปแบบนี้เป็นที่นิยมมากและควรจำไว้

ข้อผิดพลาดที่พบบ่อยและวิธีหลีกเลี่ยง

ขนาดช่วงไม่ตรงกัน

ช่วงทั้งหมดใน SUMIFS ต้องมีขนาดเท่ากัน หาก sum_range มี 500 แถว แต่ criteria_range มี 499 แถว Excel จะแสดงข้อผิดพลาด ตรวจสอบให้แน่ใจเสมอว่าช่วงของคุณสอดคล้องกัน

ลืมใส่เครื่องหมายคำพูดรอบตัวดำเนินการ

การเขียน =SUMIF(B2:B100, >500, B2:B100) จะเกิดข้อผิดพลาด ตัวดำเนินการและเกณฑ์ข้อความต้องอยู่ในเครื่องหมายคำพูด: ">500" หรือ "Electronics"

ลำดับอาร์กิวเมนต์ของ SUMIF กับ SUMIFS

ใน SUMIF sum_range คืออาร์กิวเมนต์ที่สาม แต่ใน SUMIFS คืออาร์กิวเมนต์แรก การสลับกันเป็นสาเหตุที่พบบ่อยของคำตอบที่ผิด — ตรวจสอบลำดับอาร์กิวเมนต์ทุกครั้ง

ข้อความที่เก็บเป็นตัวเลข (หรือในทางกลับกัน)

หากคอลัมน์ criteria_range มีตัวเลขที่เก็บเป็นข้อความ เกณฑ์ตัวเลขจะไม่สามารถจับคู่ได้ คุณอาจต้องล้างข้อมูลก่อน บทความเรื่อง Power Query: นำเข้าและแปลงข้อมูลอย่างมืออาชีพ อธิบายวิธีจัดการปัญหาคุณภาพข้อมูลเหล่านี้อย่างมีประสิทธิภาพ

SUMIF และ SUMIFS เทียบกับวิธีการอื่น

มีวิธีอื่นในการรวมค่าแบบมีเงื่อนไขใน Excel ซึ่งควรรู้ว่าควรใช้วิธีใดในสถานการณ์ใด:

• Pivot Table — เหมาะสำหรับการวิเคราะห์เชิงสำรวจและการสรุปข้อมูลอย่างรวดเร็ว แต่ผลลัพธ์ไม่ใช่สูตรที่ทำงานแบบสดซึ่งสามารถฝังในเซลล์แดชบอร์ดได้ ดูได้ที่ Pivot Table: คู่มือฉบับสมบูรณ์สำหรับผู้เริ่มต้น เพื่อทราบว่าวิธีนี้เหมาะสมกว่าเมื่อใด
• SUMPRODUCT — มีความยืดหยุ่นกว่า SUMIFS สำหรับเงื่อนไขที่ซับซ้อนที่เกี่ยวข้องกับอาร์เรย์หรือเกณฑ์ที่คำนวณได้ แต่ทำงานช้ากว่าบนชุดข้อมูลขนาดใหญ่
• IF + SUM (สูตรอาร์เรย์) — วิธีเก่าที่ใช้ Ctrl+Shift+Enter SUMIFS เป็นทางเลือกที่ดูแลรักษาง่ายกว่าและเร็วกว่าสำหรับกรณีการใช้งานทั่วไป

สำหรับงานรายงานทางธุรกิจส่วนใหญ่ SUMIFS คือเครื่องมือที่เหมาะสม: เร็ว อ่านง่าย และรับมือกับสถานการณ์การรวมค่าแบบมีเงื่อนไขได้เกือบทุกกรณี เมื่อคุณกำลังสร้างภาพรวมทางการเงินแบบครบวงจร การผสม SUMIFS เข้ากับเทคนิคใน เทมเพลตงบประมาณ Excel: ติดตามการเงินส่วนตัวหรือธุรกิจ จะสร้างระบบรายงานที่ทรงพลังและยืดหยุ่นได้

การผสม SUMIFS กับฟังก์ชันอื่น

SUMIFS มีความสามารถมากขึ้นเมื่อนำไปซ้อนในสูตรอื่น:

คำนวณเปอร์เซ็นต์ของยอดรวม:

=SUMIFS(E2:E500, B2:B500, "East") / SUM(E2:E500)

เปรียบเทียบผลรวมแบบมีเงื่อนไขสองค่า:

=SUMIFS(E2:E500, B2:B500, "East") - SUMIFS(E2:E500, B2:B500, "West")

ใช้ร่วมกับ IF เพื่อจัดการกับเกณฑ์ที่ว่างเปล่าอย่างสวยงาม:

=IF(H2="", SUM(E2:E500), SUMIFS(E2:E500, A2:A500, H2))

หากคุณต้องการพัฒนาทักษะสูตรเชิงตรรกะให้ก้าวหน้ายิ่งขึ้น บทความเรื่อง ฟังก์ชัน IF: การทดสอบเชิงตรรกะและ IF ซ้อนกัน คือขั้นตอนต่อไปที่เหมาะสม

เขียนสูตรให้ถูกต้องได้เร็วขึ้น

หากคุณกำลังมองดู SUMIFS ที่ซับซ้อนมีสี่หรือห้าเกณฑ์แต่ไม่รู้ว่าทำไมถึงได้ผลเป็นศูนย์ ลองอธิบายสิ่งที่ต้องการเป็นภาษาพูดธรรมดา — เครื่องมืออย่าง GPTExcel สามารถสร้างสูตรที่ถูกต้องจากคำอธิบาย เช่น "รวมรายรับที่ภาคเป็น East สินค้าเป็น Laptops และวันที่อยู่ใน Q1 2024" ได้ทันที ให้คุณตรวจสอบและนำไปใช้ได้เลย

คำถามที่พบบ่อย

SUMIF รองรับหลายเงื่อนไขได้หรือไม่?

ไม่โดยตรง SUMIF ออกแบบมาสำหรับเงื่อนไขเดียว หากต้องการสองเงื่อนไขขึ้นไป ให้ใช้ SUMIFS แทน อย่างไรก็ตาม คุณสามารถแก้ปัญหาชั่วคราวได้โดยบวกผลลัพธ์ SUMIF หลายตัวเข้าด้วยกัน เมื่อเกณฑ์ใช้กับช่วงเดียวกันและต้องการเงื่อนไข OR (เช่น รวมแถวที่เป็น "East" หรือ "West")

ทำไม SUMIFS ถึงคืนค่า 0 ทั้งที่รู้ว่ามีข้อมูลที่ตรงกัน?

สาเหตุที่พบบ่อยที่สุด ได้แก่: ตัวเลขที่เก็บเป็นข้อความในช่วงผลรวมหรือช่วงเกณฑ์ ช่องว่างพิเศษในค่าเซลล์ หรือขนาดช่วงที่ไม่ตรงกัน (SUMIFS ไม่คำนึงถึงตัวพิมพ์ใหญ่-เล็ก จึงไม่ใช่ปัญหาเรื่องนั้น) ใช้ฟังก์ชัน TRIM หรือขั้นตอนการล้างข้อมูลเพื่อแก้ปัญหาช่องว่าง

SUMIFS ใช้งานกับวันที่ได้หรือไม่?

ได้ โดยมีเงื่อนไขว่าวันที่ต้องเก็บเป็นค่าวันที่ Excel จริง (ไม่ใช่ข้อความ) ใช้ตัวดำเนินการเปรียบเทียบร่วมกับฟังก์ชัน DATE หรือการอ้างอิงวันที่โดยตรง: =SUMIFS(E2:E500, D2:D500, ">="&H1, D2:D500, "<="&H2) โดยที่ H1 และ H2 มีวันที่เริ่มต้นและสิ้นสุด

SUMIFS มีขีดจำกัดจำนวนเกณฑ์หรือไม่?

Excel รองรับได้สูงสุด 127 คู่ช่วงเกณฑ์/เกณฑ์ในสูตร SUMIFS เดียว ซึ่งมากกว่าที่คุณจะต้องใช้ในทางปฏิบัติอย่างมาก ประสิทธิภาพอาจช้าลงเมื่อใช้กับชุดข้อมูลขนาดใหญ่มากและมีเกณฑ์จำนวนมาก แต่สำหรับข้อมูลธุรกิจทั่วไป (หลายหมื่นแถว) SUMIFS ยังคงทำงานได้รวดเร็วและเชื่อถือได้