วิธีการใช้ COUNTIFS, SUMIFS, AVERAGEIFS ใน Excel

สูตรสามสูตรที่ใช้บ่อยที่สุดใน Excel เพื่อทำการคำนวณทางคณิตศาสตร์อย่างง่ายคือCOUNT , SUMและAVERAGE ไม่ว่าคุณจะจัดการงบประมาณทางการเงินใน Excel หรือเพียงแค่ติดตามวันหยุดพักผ่อนครั้งต่อไปของคุณ คุณอาจเคยใช้หนึ่งในฟังก์ชันเหล่านี้มาก่อน

ในบทความนี้ เราจะพูดถึงพื้นฐานของฟังก์ชันทั้งสามนี้และฟังก์ชันที่เกี่ยวข้องและมีประโยชน์: COUNTIFS, SUMIFS และ AVERAGEIFS

สมมติว่าเรากำลังเริ่มต้นธุรกิจออนไลน์ใหม่โดยขายโทรศัพท์มือถือ และเรามีแผ่นงานที่แสดงยอดขายที่เราทำได้ในสองเดือนแรก ดาวน์โหลดตัวอย่างสเปรดชีต Excel ที่นี่

Excel นับ ผลรวม และค่าเฉลี่ย

หากต้องการทราบจำนวนโทรศัพท์มือถือที่เราขาย เราสามารถใช้ สูตร COUNT อย่างรวดเร็ว ที่แสดงด้านล่าง:

=COUNT(E2:E16)

ในทางกลับกัน เพื่อให้ได้ยอดขายรวมที่เราทำได้ เราสามารถใช้ สูตร SUMได้ดังนี้

=SUM(E2:E16)

สุดท้าย เพื่อหายอดขายเฉลี่ยที่เราทำได้สำหรับโทรศัพท์ทุกรุ่น เราสามารถใช้ สูตร AVERAGEดังต่อไปนี้:

=เฉลี่ย(E2:E16)

ผลลัพธ์ควรเป็นดังนี้:

สูตร COUNT, SUM และ AVERAGE จะใช้ได้กับระเบียนที่มีค่าเซลล์อยู่ในรูปแบบตัวเลขเท่านั้น ระเบียนใดๆ ที่อยู่ในช่วงสูตร (เช่นE2:E16ในตัวอย่างนี้) ที่ไม่ได้อยู่ในรูปแบบตัวเลขจะถูกละเว้น

ดังนั้น โปรดตรวจสอบให้แน่ใจว่าเซลล์ทั้งหมดภายในสูตร COUNT, SUM และ AVERAGE จัดรูปแบบเป็นNumber ทั้งหมดไม่ใช่Text พยายามใช้สูตรเดียวกัน แต่ใช้E:Eเป็นช่วงแทนE2: E16 มันจะส่งกลับผลลัพธ์เหมือนเดิมเพราะมันละเว้นส่วนหัว (เช่นราคาขาย ) ซึ่งอยู่ในรูปแบบข้อความ

ทีนี้ถ้าเราต้องการทราบจำนวนยอดขาย ยอดขายรวม และยอดขายเฉลี่ยต่อโทรศัพท์ เฉพาะที่ขายในสหรัฐอเมริกาล่ะ นี่คือจุดที่ COUNTIFS, SUMIFS และ AVERAGEIFS มีบทบาทสำคัญ สังเกตสูตรด้านล่าง:

เคาน์ตีฟ

รายละเอียดสูตร:

1. =COUNTIFS( – “=”หมายถึงจุดเริ่มต้นของสูตรในเซลล์และCOUNTIFSคือส่วนแรกของฟังก์ชัน Excel ที่เรากำลังใช้อยู่
2. D2:D16 – อ้างอิงช่วงของข้อมูลเพื่อตรวจสอบว่าตรงตามเกณฑ์ที่จะรวมไว้ในสูตรการนับหรือไม่
3. “สหรัฐอเมริกา” – เกณฑ์การค้นหาในช่วงข้อมูลที่ระบุ ( D2:D16 )
4. ) – วงเล็บปิดระบุจุดสิ้นสุดของสูตร

สูตรส่งคืน 6 ซึ่งเป็นจำนวนยอดขายสำหรับสินค้าที่จัดส่งจากคลังสินค้าในสหรัฐอเมริกา

SUMIFS

รายละเอียดสูตร:

1. =SUMIFS( – “=”หมายถึงจุดเริ่มต้นของสูตรอีกครั้ง
2. E2:E16 – หมายถึงช่วงของข้อมูลที่เราต้องการรวม เช่น ราคาขายในตัวอย่างของเรา
3. D2:D16 – อ้างอิงช่วงของข้อมูลเพื่อตรวจสอบว่าตรงตามเกณฑ์ที่จะรวมไว้ในจำนวนทั้งหมดหรือไม่
4. “สหรัฐอเมริกา” – เกณฑ์การค้นหาในช่วงข้อมูลที่ระบุ ( D2:D16 )
5. ) – วงเล็บปิดระบุจุดสิ้นสุดของสูตร

สูตรแสดง ยอดขายรวม 6,050 ดอลลาร์ที่เกิดขึ้นสำหรับผลิตภัณฑ์ที่จัดส่งจากคลังสินค้าในสหรัฐอเมริกา

ค่าเฉลี่ย

รายละเอียดสูตร:

1. =AVERAGEIFS( – “=”ระบุจุดเริ่มต้นของสูตร
2. E2:E16 – หมายถึงช่วงของข้อมูลที่เราต้องการหาค่าเฉลี่ย ในตัวอย่างนี้ เราต้องการรับยอดขายเฉลี่ยสำหรับโทรศัพท์ทั้งหมดที่ขายในสหรัฐอเมริกา
3. D2:D16 – อ้างอิงช่วงของข้อมูลเพื่อตรวจสอบว่าตรงตามเกณฑ์ที่จะรวมไว้ในสูตรค่าเฉลี่ยหรือไม่
4. “USA” – เกณฑ์การค้นหาในช่วงข้อมูลที่ระบุ
5. ) – วงเล็บปิดที่ระบุจุดสิ้นสุดของสูตร

สูตรแสดงให้เห็นว่าเราขายผลิตภัณฑ์ในราคาประมาณ$1,008ต่อโทรศัพท์หนึ่งเครื่องในสหรัฐอเมริกา

ทั้งสามสูตรสามารถใช้มากกว่าหนึ่งเกณฑ์ ตัวอย่างเช่น หากเราต้องการทราบตัวเลขเดียวกัน (เช่นCOUNT , SUMและAVERAGE ) สำหรับผลิตภัณฑ์ที่ขายในสหรัฐอเมริกาแต่เฉพาะเจาะจงสำหรับ  แบรนด์ Samsung เท่านั้น เราเพียงแค่เพิ่มช่วงข้อมูลที่จะตรวจสอบตามด้วยเกณฑ์

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

=COUNTIFS(D2:D16,"USA", B2:B16,"Samsung") =SUMIFS(E2:E16,D2:D16,"USA", B2:B16,"Samsung") =AVERAGEIFS(E2:E16,D2 :D16,"สหรัฐอเมริกา", B2:B16,"ซัมซุง")

คุณจะสังเกตเห็นว่า Excel ยังมี  สูตร COUNTIF , SUMIFและAVERAGEIF ที่ไม่มี "S"ต่อท้าย สิ่งเหล่า นี้ใช้คล้ายกับCOUNTIFS , SUMIFSและAVERAGEIFS อย่างไรก็ตาม ผู้ที่ไม่มี "S"ต่อท้ายในสูตรมีข้อจำกัดที่อนุญาตเพียงเกณฑ์เดียวต่อสูตร

เนื่องจากไวยากรณ์แตกต่างกันเล็กน้อย ฉันขอแนะนำให้ใช้  COUNTIFS , SUMIFSและAVERAGEIFSเท่านั้น เนื่องจากสามารถใช้กับเกณฑ์ใดเกณฑ์หนึ่งหรือมากกว่าก็ได้ หากจำเป็น สนุก!