วิธีใช้ COUNTIFS, SUMIFS, AVERAGEIFS ใน Excel
สามสูตรที่ใช้กันมากที่สุดใน Excel ที่ดำเนินการคำนวณทางคณิตศาสตร์อย่างง่ายคือ นับ, SUM และ เฉลี่ย. ไม่ว่าคุณจะจัดการงบประมาณทางการเงินใน Excel หรือเพียงแค่ติดตามวันหยุดพักผ่อนของคุณต่อไปคุณอาจเคยใช้หนึ่งในฟังก์ชั่นเหล่านี้มาก่อน.
ในบทความนี้เราจะอธิบายพื้นฐานของฟังก์ชั่นทั้งสามนี้และคู่หูที่เกี่ยวข้องและเป็นประโยชน์: COUNTIFS, SUMIFS และ AVERAGEIFS.
สมมติว่าเรากำลังเริ่มธุรกิจออนไลน์ใหม่ที่ขายโทรศัพท์มือถือและเรามีแผ่นงานที่แสดงยอดขายที่เราทำในสองเดือนแรก. ดาวน์โหลดตัวอย่างสเปรดชีต Excel ที่นี่.
Excel COUNT, SUM และ AVERAGE
เพื่อทราบจำนวนโทรศัพท์มือถือที่เราขายไปเราสามารถใช้ นับ สูตรที่แสดงด้านล่าง:
= COUNT (E2: E16)
ในทางกลับกันเพื่อให้ได้ยอดขายทั้งหมดที่เราทำเราสามารถใช้ SUM สูตรที่แสดงด้านล่าง:
= SUM (E2: E16)
สุดท้ายเพื่อหายอดขายเฉลี่ยที่เราทำกับโทรศัพท์ทุกรุ่นเราสามารถใช้ เฉลี่ย สูตรดังต่อไปนี้:
= เฉลี่ย (E2: E16)
ผลลัพธ์ควรเป็นดังนี้:
สูตร COUNT, SUM และ AVERAGE จะใช้งานได้กับระเบียนที่ค่าเซลล์อยู่ในรูปแบบตัวเลขเท่านั้น บันทึกใด ๆ ที่อยู่ในช่วงสูตร (เช่น. E2: E16 ในตัวอย่างนี้) ไม่ได้อยู่ในรูปแบบตัวเลขจะถูกละเว้น.
ดังนั้นโปรดตรวจสอบให้แน่ใจว่าเซลล์ทั้งหมดภายในสูตร COUNT, SUM และ AVERAGE ถูกจัดรูปแบบเป็น จำนวน, ไม่ ข้อความ. ลองใช้สูตรเดียวกัน แต่ด้วย E: E เป็นช่วงแทน E2: E16. จะส่งคืนผลลัพธ์เดิมเหมือนก่อนเพราะจะละเว้นส่วนหัว (เช่น. ลดราคา) ซึ่งอยู่ในรูปแบบข้อความ.
ทีนี้ถ้าเราต้องการทราบจำนวนการขายยอดขายทั้งหมดและยอดขายเฉลี่ยต่อโทรศัพท์เพียงแค่สำหรับผู้ที่ขายในสหรัฐอเมริกา นี่คือที่ COUNTIFS, SUMIFS และ AVERAGEIFS มีบทบาทสำคัญ สังเกตสูตรด้านล่าง:
COUNTIFS
การแยกย่อยสูตร:
- = COUNTIFS ( - “=” ระบุจุดเริ่มต้นของสูตรในเซลล์และ COUNTIFS เป็นส่วนแรกของฟังก์ชัน Excel ที่เราใช้.
- D2: D16 - อ้างถึงช่วงของข้อมูลที่ตรวจสอบเพื่อดูว่าเป็นไปตามเกณฑ์ที่จะรวมอยู่ในสูตรการนับ.
- "สหรัฐอเมริกา" - เกณฑ์ที่จะค้นหาในช่วงข้อมูลที่ระบุ (D2: D16)
- ) - วงเล็บปิดที่ระบุจุดสิ้นสุดของสูตร.
สูตรส่งคืน 6 ซึ่งเป็นจำนวนการขายสินค้าที่จัดส่งจากคลังสินค้าของสหรัฐอเมริกา.
SUMIFS
การแยกย่อยสูตร:
- = SUMIFS ( - “=” ระบุจุดเริ่มต้นของสูตรอีกครั้ง.
- E2: E16 - อ้างถึงช่วงของข้อมูลที่เราต้องการรวมคือราคาขายในตัวอย่างของเรา.
- D2: D16 - อ้างถึงช่วงของข้อมูลที่ตรวจสอบเพื่อดูว่าเป็นไปตามเกณฑ์ที่จะรวมในจำนวนทั้งหมดหรือไม่.
- "สหรัฐอเมริกา" - เกณฑ์ที่จะค้นหาในช่วงข้อมูลที่ระบุ (D2: D16)
- ) - วงเล็บปิดที่ระบุจุดสิ้นสุดของสูตร.
สูตรแสดงให้เห็น $ 6,050 ยอดขายรวมที่ทำขึ้นสำหรับผลิตภัณฑ์ที่จัดส่งจากคลังสินค้าของสหรัฐอเมริกา.
AVERAGEIFS
การแยกย่อยสูตร:
- = AVERAGEIFS ( - “=” ระบุจุดเริ่มต้นของสูตร.
- E2: E16 - หมายถึงช่วงของข้อมูลที่เราต้องการเฉลี่ย ในตัวอย่างนี้เราต้องการรับยอดขายเฉลี่ยสำหรับโทรศัพท์ทุกรุ่นที่ขายในสหรัฐอเมริกา.
- D2: D16 - อ้างถึงช่วงของข้อมูลที่ตรวจสอบเพื่อดูว่าเป็นไปตามเกณฑ์ที่จะรวมอยู่ในสูตรเฉลี่ยหรือไม่.
- "สหรัฐอเมริกา" - เกณฑ์ที่จะค้นหาในช่วงข้อมูลที่ระบุ
- ) - วงเล็บปิดที่ระบุจุดสิ้นสุดของสูตร.
สูตรแสดงให้เห็นว่าเราขายผลิตภัณฑ์สำหรับรอบ $ 1,008 ต่อโทรศัพท์ในสหรัฐอเมริกา.
ทั้งสามสูตรสามารถใช้เกณฑ์ได้มากกว่าหนึ่งเกณฑ์ ตัวอย่างเช่นหากเราต้องการทราบตัวเลขเดียวกัน (เช่น. นับ, SUM และ เฉลี่ย) สำหรับผลิตภัณฑ์ที่จำหน่ายใน สหรัฐอเมริกา, แต่เฉพาะสำหรับ ซัมซุง แบรนด์เราเพียงแค่ต้องเพิ่มช่วงข้อมูลที่จะตรวจสอบตามด้วยเกณฑ์.
โปรดดูตัวอย่างด้านล่างที่จะเพิ่มเกณฑ์ที่สองในการตรวจสอบเกณฑ์เริ่มต้น (ข้อความสีฟ้าหมายถึงเกณฑ์แรกและสีแดงหมายถึงเกณฑ์ที่สอง)
= COUNTIFS (D2: D16, "USA", B2: B16, "Samsung") = SUMIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung") = AVERAGEIFS (E2: E16, D2: D16, "USA", B2: B16, "Samsung")
คุณจะสังเกตเห็นว่า Excel ยังมี COUNTIF, SUMIF และ AVERAGEIF สูตรที่ไม่มีคำต่อท้าย “S”. เหล่านั้นถูกนำมาใช้คล้ายกับ COUNTIFS, SUMIFS และ AVERAGEIFS. อย่างไรก็ตามผู้ที่ไม่มีคำต่อท้าย “S” ในสูตรมีข้อ จำกัด เพียงหนึ่งเงื่อนไขเท่านั้นที่อนุญาตให้ใช้หนึ่งสูตร.
เนื่องจากไวยากรณ์แตกต่างกันเล็กน้อยฉันขอแนะนำให้ใช้ COUNTIFS, SUMIFS และ AVERAGEIFS เฉพาะในกรณีที่สามารถใช้กับเกณฑ์ใดเกณฑ์หนึ่งหรือมากกว่าหากจำเป็น สนุก!
