การนับแบบหลายเงื่อนไขใน Excel

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

• อยากรู้ว่าพนักงานในฝ่ายวิศวกรรมมีเพศชายกี่คน เพศหญิงกี่คน หรือ
• อยากรู้ว่า รถรุ่นนี้ สีแดงในภาคตะวันออกขายได้กี่คัน เป็นต้น

เมื่อใช้ Excel เราจะใช้สูตรอะไร หรือฟังก์ชันไหนมาช่วยดี มาหาคำตอบกันในโพสต์นี้ครับ

สรุปย่อ

ใน Excel มีฟังก์ชันชื่อ COUNTIFS() สำหรับใช้นับแบบหลายเกณฑ์ หรือเงื่อนไข โดยจำนวนที่นับได้นั้น คือ จำนวนที่เข้าทุกเกณฑ์ หรือทุกเงื่อนไขที่ใส่เข้าไปในฟังก์ชัน หรือพูดให้ง่ายก็คือ COUNTIFS() ใช้เงื่อนไขแบบ “และ (AND)”

ดังนั้น ⚠️”ข้อมูลที่ตรงแค่บางเงื่อนไขจะไม่ถูกนับ” นะ

ตัวอย่าง

ขอยกตัวอย่างเป็นการนับพนักงานในแต่ละแผนกก็แล้วกันครับ โดยผลลัพธ์ที่ได้จะเป็นไปตามนี้

ซึ่งจะได้มาจากการนับข้อมูล ซึ่งผมกำหนดให้เป็นตาราง (Format as Table) ไว้แล้ว ดังภาพด้านล่าง หรือจะดูจากลิงค์ หรือดาวน์โหลด “ไฟล์ตัวอย่าง” มาลองใช้ดูก็ได้ 😁

โดยตารางนี้จะชื่อ EmployeeTable และมีหัวตารางชื่อ Firstname, Lastname, Gender, Department และ Salary แต่หากใครสะดวกใช้ range แบบปกติก็ไม่มีปัญหาอะไรนะ ผมชอบทำแบบนี้เพราะมันอ่านง่ายและที่สำคัญคือ Excel จะช่วย suggest ให้โดยที่เราไม่ต้องพิมพ์ชื่อ range หรือ cell ในสูตร

ส่วนจำนวนที่นับได้จะอยู่ในตารางชื่อ SummaryTable ดังภาพ

จะเห็นว่าในเซลล์ I2 สูตรจะเป็น

=COUNTIFS(EmployeeTable[Department],[@Department],EmployeeTable[Gender],"M")

โดยมีเกณฑ์ที่เราใส่เข้าไปอยู่ 2 อย่าง คือ “ชื่อแผนก” กับ “M” หรือเพศชาย และผลลัพธ์ที่ได้ออกมาคือ 3 ก็คือจากข้อมูลในแผนก HR & Administration มีพนักงานผู้ชายอยู่ 3 คน

ถึงตรงนี้หากใครยัง งงงง 🤔 อยู่ ไม่เป็นไรนะ เราไปดูไวยากรณ์ หรือวิธีการใช้ฟังก์ชันจะเข้าใจมากขึ้น

ไวยากรณ์

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)

มีอาร์กิวเมนต์ที่เราต้องบอกให้ฟังก์ชันทราบ ดังนี้

• criteria_range1 [จำเป็นต้องมี] ให้กำหนด range ข้อมูลของเกณฑ์แรก ที่เราจะให้ฟังก์ชันไปหา
• criteria1 [จำเป็นต้องมี] เกณฑ์ข้อมูลตัวแรก ที่เราต้องการจะนับ
• criteria_range2 กำหนด range ข้อมูลของเกณฑ์ที่สอง ที่เราจะให้ฟังก์ชันไปหา
• criteria2 เกณฑ์ข้อมูลที่ 2 ที่เราต้องการจะนับ
• … หมายถึง คุณใส่คู่ของ range และเกณฑ์เพิ่มเข้าไปได้เรื่อย ๆ 😏 แต่จำไม่ได้ว่าเท่าไหร่

⚠️ข้อควรระวัง

• สำคัญมาก❗criteria_range2 และอื่น ๆ ที่เพิ่มเข้าไป จะต้องมีแถวและคอลัมภ์เท่ากันกับ criteria_range1 เสมอ แต่ไม่จำเป็นต้องอยู่ติดกัน
• อันนี้บอกไปแล้ว ต้องเขาทุกเกณฑ์ หรือทุกเงื่อนไขนะ ถึงจะนับเพิ่มให้
• หากอาร์กิวเมนต์ criteria อ้างอิงไปยังเซลล์ที่ว่าง (ไม่มีข้อมูล) จะถือว่า อาร์กิวเมนต์นั้นคือ 0 (ศูนย์) ถ้าใน criteria_range มี 0 ถือว่าเข้าเงื่อนไขนะ และจะนับเพิ่มให้
• สามารถใช้อักขระตัวแทน (wildcard) เครื่องหมายคําถาม (?) และดอกจัน (*) ในเกณฑ์ได้ โดยที่
  • ? หมายถึง “อักขระตัวเดียว”
  • และ ??? หมายถึง “อักขระ 3 ตัว”
  • ส่วน * หมายถึง “อักขระกี่ตัวก็ได้”
  • แล้วถ้าอยากใช้ ? และ * ในเกณฑ์ ให้ใช้เครื่องหมาย ~ นำหน้า เช่น ~? และ ~*

อธิบายสูตรเพิ่มเติม

สำหรับคนที่ไม่ถนัดใช้ตาราง (Table) สูตรที่ผมใช้ใน I2 ก็คือ

=COUNTIFS($D$2:$D$36,G2,$C$2:$C$36,"M")

• โดย criteria_range1 ของเราก็คือ range ที่ชื่อแผนกทั้งหมดของพนักงานแต่ละคน โดยในที่นี้คือ $D$2:$D$36 หรือ EmployeeTable[Department]
• criteria1 ของเราคือ G2 หรือ [@Department] ซึ่งเป็นชื่อแผนกที่เรากำหนดเป็นเกณฑ์ ในที่นี้ก็คือ HR & Administration
• criteria_range2 ก็จะเป็น range ที่เก็บเพศของพนักงานแต่ละคน ในที่นี้คือ $C$2:$C$36 หรือ EmployeeTable[Gender]
• criteria2 เราใส่ “M” สำหรับผู้ชาย และ “F” สำหรับผู้หญิง เนื่องจากข้อมูลเราเป็นแบบนี้

สำหรับใครมีหลายเงื่อนไขกว่านี้ ก็ให้ใส่เพิ่มเข้าไปเป็นคู่ range กับ เกณฑ์ ที่จะนับแบบนี้ไปเรื่อย ๆ

อย่างเช่น เราจะนับว่ามีพนักงานที่มีเงินเดือนมากกว่า 50,000 ในแผนก Production และเป็นเพศหญิงกี่คน? เราก็จะได้สูตรเป็น

=COUNTIFS(EmployeeTable[Salary],">50000",EmployeeTable[Department],"Production",EmployeeTable[Gender],"F")

ฟังก์ชันที่เกี่ยวข้องและใช้งานเหมือนกัน

นอกจาก COUNTIFS() แล้ว ในกลุ่มนี้ยังมีฟังก์ชันอื่นที่ใช้งานเหมือนกัน และมักผมมักถูกถามอยู่บ่อย ๆ ได้แก่

• MINIFS() – หาค่าที่ต่ำที่สุด โดยที่ต้องเข้าทุกเกณฑ์ หรือทุกเงื่อนไข
• MAXIFS() – หาค่าที่มากที่สุด โดยที่ต้องเข้าทุกเกณฑ์ หรือทุกเงื่อนไข
• SUMIFS() – หาผลรวม โดยที่ต้องเข้าทุกเกณฑ์ หรือทุกเงื่อนไข
• AVERAGEIFS() – หาค่าเฉลี่ย โดยที่ต้องเข้าทุกเกณฑ์ หรือทุกเงื่อนไข

ส่วนการนับ หาค่าต่ำสุด หาค่าสูงสุด หาผลรวม และหาค่าเฉลี่ย แบบที่มีเกณฑ์เพียงตัวเดียว เราก็สามารถเลือกใช้ฟังก์ชัน COUTIF(), MINIF(), MAXIF(), SUMIF() และ AVERAGEIF() ตามลำดับได้เช่นกัน

ถึงตรงนี้แล้ว หากใครมียังสงสัยก็ 📝comment ไว้ได้เลยนะครับ 😊

และไฟล์ตัวอย่างสามารถดู หรือดาวน์โหลดได้จาก ➡️ OneDrive เช่นเคย