ฟังก์ชัน COUNTIFS วิธีใช้ฟังก์ชันนับเซลล์ตามเงื่อนไขต่างๆ ใน ​​Excel

ฟังก์ชัน COUNTIFS ใน Excelมีแอปพลิเคชันที่เป็นประโยชน์มากมาย นี่คือสิ่งที่คุณต้องรู้เกี่ยวกับการใช้ฟังก์ชัน COUNTIFS ใน Excel

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

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

• วิธีเปรียบเทียบข้อมูลใน 2 คอลัมน์ Excel
• วิธีใช้ฟังก์ชัน DSUM เพื่อคำนวณผลรวมที่มีเงื่อนไขที่ซับซ้อนใน Excel
• วิธีใช้ฟังก์ชัน SUMIF เพื่อคำนวณผลรวมตามเงื่อนไขใน Excel
• การใช้ฟังก์ชัน VLOOKUP และตัวอย่างเฉพาะ

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

ไวยากรณ์ของฟังก์ชัน COUNTIFS คือ=COUNTIFS(criteria_range1, เกณฑ์1, [criteria_range2, เกณฑ์2],… )

ในนั้น:

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

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

• การเลือกเพิ่มเติมจะต้องมีจำนวนแถวและคอลัมน์เท่ากันกับพื้นที่เกณฑ์_ช่วง 1 และสามารถแยกออกจากกันได้
• ถ้าเงื่อนไขการเลือกอ้างถึงเซลล์ว่าง ฟังก์ชัน COUNTIFS จะพิจารณาค่าเป็น 0 โดยอัตโนมัติ
• ตัวอักษร ? สามารถใช้ได้ หากต้องการแทนที่อักขระบางตัว เครื่องหมาย * จะแทนที่สตริงอักขระทั้งหมด หากท่านจำเป็นต้องหาป้าย? หรือเครื่องหมาย * จริง ให้พิมพ์ ~ นำหน้าตัวละครนั้น
• โปรดทราบว่าฟังก์ชัน COUNTIFS ไม่คำนึงถึงขนาดตัวพิมพ์
• โดยทั่วไป ค่าข้อความจะต้องอยู่ในเครื่องหมายคำพูด แต่ตัวเลขไม่ต้องใส่ อย่างไรก็ตาม เมื่อรวมตัวดำเนินการเชิงตรรกะเข้ากับตัวเลขแล้ว ตัวเลขและตัวดำเนินการจะต้องอยู่ในเครื่องหมายคำพูดดังที่แสดงด้านล่าง:

=COUNTIFS(A1:A10,100) // count equal to 100
=COUNTIFS(A1:A10,">50") // count greater than 50
=COUNTIFS(A1:A10,"jim") // count equal to "jim"

หมายเหตุ : เงื่อนไขเพิ่มเติมต้องเป็นไปตามหลักเกณฑ์เดียวกัน

• เมื่อใช้ค่าจากเซลล์อื่นในเงื่อนไข การอ้างอิงเซลล์จะต้องต่อกับตัวดำเนินการเมื่อใช้ ในตัวอย่างด้านล่าง COUNTIFS จะนับค่าใน A1:A10 ที่น้อยกว่าค่าในเซลล์ B1 โปรดทราบว่าตัวดำเนินการน้อยกว่า (ซึ่งเป็นข้อความ) จะอยู่ในเครื่องหมายคำพูด แต่ไม่จำเป็นต้องอ้างอิงเซลล์:

=COUNTIFS(A1:A10,"<"&b1) count="" cells="" less="" than="">

หมายเหตุ : COUNTIFS เป็นหนึ่งในหลายฟังก์ชันที่แบ่งเงื่อนไขออกเป็นสองส่วน ได้แก่ ช่วง + เกณฑ์ ซึ่งทำให้เกิดความไม่สอดคล้องกับสูตรและฟังก์ชันอื่นๆ

• COUNTIFS สามารถนับเซลล์ว่างหรือเซลล์ที่ไม่ว่างได้ สูตรด้านล่างนับเซลล์ว่างและไม่ว่างในช่วง A1:A10:

=COUNTIFS(A1:A10,"<>") // not blank
=COUNTIFS(A1:A10,"") // blank

• วิธีที่ง่ายที่สุดในการใช้ COUNTIFS กับวันที่คือการอ้างอิงวันที่ที่ถูกต้องในเซลล์อื่นที่มีการอ้างอิงเซลล์ ตัวอย่างเช่น หากต้องการนับเซลล์ใน A1:A10 ที่มีวันที่มากกว่าวันที่ใน B1 คุณสามารถใช้สูตรดังนี้:

=COUNTIFS(A1:A10, ">"&B1) // count dates greater than A1

ตัวอย่างการใช้ฟังก์ชัน COUNTIFS ใน Excel

1. เอกสารข้อมูลหมายเลข 1

เรามีตารางข้อมูลด้านล่างเพื่อส่งคำขอไปยังตาราง

ตัวอย่างที่ 1: คำนวณจำนวนพนักงานชายที่มี 25 วันทำการ

ในเซลล์ที่เราต้องป้อนผลลัพธ์ ให้ป้อนสูตร=COUNTIFS(C2:C7,"Male",D2:D7,25)แล้วกด Enter

ในนั้น:

• C2:C7 เป็นพื้นที่บังคับที่มีการนับ 1 ครั้งโดยระบุเพศของพนักงาน
• ทิศใต้ คือ สภาพพื้นที่นับ 1
• C2:C7 คือพื้นที่นับที่ 2 โดยมีวันทำงานของพนักงาน
• 25 คือเงื่อนไขของนับ 2 ภูมิภาค

ส่งผลให้เรามีพนักงาน 2 คน ชื่อน้ำ มี 25 วันทำการ

ตัวอย่างที่ 2: คำนวณจำนวนพนักงานชายที่มีวันหยุดเป็น 0

ในกล่องอินพุตสูตร ให้ป้อน=COUNTIFS(C2:C7,"Nam",E2:E7,0)แล้วกด Enter

ผลปรากฏว่า พนักงานชาย 1 ราย หยุดงาน 0 วัน

ตัวอย่างที่ 3: คำนวณจำนวนพนักงานชายที่ทำงาน 25 วันและมีวันหยุดน้อยกว่าหรือเท่ากับ 2 วัน

เราป้อนสูตร=COUNTIFS(C2:C7,"Male",D2:D7,25,E2:E7,"<>แล้วกด Enter

ส่งผลให้มีพนักงานชายจำนวน 2 คนที่มีคุณสมบัติมีวันหยุดน้อยกว่าหรือเท่ากับ 2 วัน

2. เอกสารข้อมูลหมายเลข 2

ตัวอย่างที่ 1: คำนวณผลรวมของผลิตภัณฑ์ไต้หวันที่มีราคาต่อหน่วยน้อยกว่า 200,000 VND

ในกล่องอินพุตผลลัพธ์ เราป้อนสูตรการคำนวณ=COUNTIFS(B2:B7,"*Taiwan",C2:C7,"<>แล้วกด Enter

ผลลัพธ์ที่ได้คือสินค้าไต้หวัน 2 ชิ้นที่ตรงตามข้อกำหนด

ตัวอย่างที่ 2: คำนวณสินค้าไต้หวันทั้งหมดที่มีราคาต่อหน่วยระหว่าง 100,000 VND ถึง 150,000 VND

ในกล่องอินพุตผลลัพธ์ ให้ป้อนสูตร=COUNTIFS(B2:B7,"*Taiwan",C2:C7,">100000",C2:C7,"<>จากนั้นกด Enter

โดยจะมีสินค้าไต้หวัน 2 รายการที่มีราคาระหว่าง 100,000 VND - 150,000 VND

วิธีใช้ฟังก์ชัน COUNTIFS รวมกับ SUM ใน Excel

ฟังก์ชัน COUNTIFS นั้นเป็นเวอร์ชันที่ซับซ้อนกว่าของฟังก์ชัน COUNTIF ข้อได้เปรียบหลักที่ COUNTIFS มีมากกว่า COUNTIF คือรองรับเงื่อนไขและช่วงหลายรายการ

อย่างไรก็ตาม คุณยังสามารถกำหนดช่วงและเงื่อนไขเดียวสำหรับฟังก์ชัน COUNTIFS ได้ เช่นเดียวกับที่คุณกำหนดกับฟังก์ชัน COUNTIF

สิ่งสำคัญสิ่งหนึ่งที่คุณควรเข้าใจเกี่ยวกับฟังก์ชัน COUNTIFS ก่อนที่จะใช้งานก็คือ ฟังก์ชันนี้ทำมากกว่าแค่การรวมผลลัพธ์ของเซลล์ที่ตรงตามเกณฑ์สำหรับแต่ละช่วงของเซลล์

ถ้าคุณมีเงื่อนไขสองข้อสำหรับสองช่วง เซลล์ในช่วงแรกจะถูกกรองสองครั้ง: หนึ่งครั้งผ่านเงื่อนไขแรกและจากนั้นผ่านเงื่อนไขที่สอง ซึ่งหมายความว่าฟังก์ชัน COUTNIFS จะส่งคืนค่าที่ตรงตามเงื่อนไขทั้งสองภายในช่วงที่กำหนดเท่านั้น

คุณสามารถเข้าใจฟังก์ชันการทำงานของฟังก์ชัน COUNTIFS ได้ดีขึ้นโดยศึกษาตัวอย่างด้านล่าง

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

นอกจากสีของแอปเปิ้ลแล้ว ยังมีคอลัมน์อธิบายขนาดของแอปเปิ้ลด้วย เป้าหมายสูงสุดในตัวอย่างนี้คือการนับจำนวนแอปเปิ้ลสีแดงลูกใหญ่

1. เลือกเซลล์ที่คุณต้องการแสดงผลลัพธ์ (ในตัวอย่างนี้ บทความนี้จะแสดงแอปเปิ้ลสีแดงจำนวนมากในเซลล์ E3)

2. ไปที่แถบสูตรแล้วป้อนสูตรด้านล่าง:

=COUNTIFS(A3:A11, "Red", B3:B11, "Big")

3. ด้วยคำสั่งนี้ สูตรจะตรวจสอบเซลล์A3ถึงA11เพื่อหาเงื่อนไข“สีแดง” เซลล์ที่ผ่านการทดสอบ นี้จะได้รับการทดสอบอีกครั้งในช่วงB3ถึงB11สำหรับสภาวะ "ใหญ่"

4. กดปุ่ม Enter

5. ตอนนี้ Excel จะนับจำนวนแอปเปิ้ลสีแดงขนาดใหญ่

ตัวอย่างผลลัพธ์ของฟังก์ชัน Excel COUNTIFS

สังเกตวิธีที่สูตรนับเซลล์ที่มีทั้งแอตทริบิวต์สีแดงและ ขนาดใหญ่ สูตรจะนำเซลล์A3ถึงA11และทดสอบเพื่อหาผลลัพธ์สีแดง เซลล์ที่ผ่านเงื่อนไขนี้จะถูกทดสอบอีกครั้งกับเงื่อนไขถัดไปในช่วงที่สอง ซึ่งในกรณีนี้คือเงื่อนไขใหญ่

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

1. เลือกเซลล์ที่คุณต้องการแสดงผลลัพธ์ของสูตร (ในตัวอย่างนี้ บทความจะใช้เซลล์ E6)

2. ป้อนสูตรด้านล่าง:

=SUM(COUNTIF(A3:A11, "Red"), COUNTIF(B3:B11, "Big"))

3. สูตรนี้จะนับเซลล์ที่มีแอปเปิ้ลสีแดง จากนั้นจำนวนเซลล์ที่มีแอปเปิ้ลขนาดใหญ่ และสุดท้ายก็จะรวมตัวเลขทั้งสองนี้

4. กดปุ่ม Enter

5. Excel จะนับและแสดงจำนวนแอปเปิ้ลขนาดใหญ่หรือสีแดง

รวมฟังก์ชัน COUNTIF เข้ากับฟังก์ชัน SUM

ด้านบนคือตารางข้อมูล 2 ตารางที่มีการใช้งานฟังก์ชัน COUNTIFS ใน Excel ผู้ใช้จะต้องเขียนเงื่อนไขที่มาพร้อมกับพื้นที่ข้อมูลเงื่อนไขเพื่อให้ฟังก์ชัน COUNTIFS พิจารณาได้อย่างถูกต้อง

วิธีแก้ไขข้อผิดพลาดทั่วไปเมื่อใช้ฟังก์ชัน COUNTIFS ใน Excel

COUNTIFS ไม่ทำงานเมื่อนับค่าข้อความ

เมื่อนับสตริงข้อความ จะต้องแทรกสตริงเหล่านั้นภายในเครื่องหมายคำพูด มิฉะนั้น ฟังก์ชัน COUNTIFS จะไม่สามารถนับสตริงข้อความและจะส่งกลับค่า 0 ในภาพตัวอย่างต่อไปนี้ ไม่ได้วางสตริงข้อความในเครื่องหมายคำพูด ดังนั้น สูตรนี้จึงคืนค่า 0

เพื่อแก้ไขข้อผิดพลาดนี้ คุณเพียงแค่ต้องเขียนสูตรใหม่ให้ตรงกัน: =COUNTIFS(E5:E12, "Car")

COUNTIFS ไม่ทำงานเนื่องจากการอ้างอิงช่วงไม่ถูกต้อง

เมื่อใช้มากกว่าหนึ่งเกณฑ์ในฟังก์ชัน COUNTIFS ช่วงเซลล์สำหรับเกณฑ์อื่นๆ จะต้องมีจำนวนเซลล์เท่ากัน มิฉะนั้น ฟังก์ชัน COUNTIFS จะไม่ทำงาน

สมมติว่าในตัวอย่างนี้ เราต้องการนับยอดขายรถยนต์ในออสติน สูตรที่ป้อนคือ =COUNTIFS(E5:E12,"Car",D5:D11,"Austin") เมื่อดูสูตรอย่างละเอียด คุณจะเห็นว่าช่วงเกณฑ์แรกที่นี่คือ E5:E12 แต่ช่วงเกณฑ์ที่สองคือ D5:D11 จำนวนเซลล์ในช่วงสำหรับเกณฑ์นี้ไม่เท่ากัน

ตอนนี้กดEnterสูตรจะส่งกลับ#VALUE! .

เขียนสูตรใหม่อย่างถูกต้องดังนี้: =COUNTIFS(E5:E12,"Car",D5:D12,"Austin")

COUNTIFS ไม่ทำงานเนื่องจากมีข้อผิดพลาดในสูตร

ถ้าใส่สูตรไม่ถูกต้อง ฟังก์ชัน COUNTIFS จะไม่ทำงาน เมื่อใช้ตัวดำเนินการทางคณิตศาสตร์ใดๆ เช่น มากกว่า (>) น้อยกว่า (<) เท่ากับ="" (=")" และ "not="" เท่ากับ=""> <> ) ทั้งตัวดำเนินการและ ต้องป้อนเกณฑ์ตัวเลขในสมการเดียวกัน ตัวอย่างเช่น หากคุณต้องการค้นหายอดขายที่มากกว่า 100,000 ดอลลาร์ คุณจะต้องแทรกสูตรต่อไปนี้:

=COUNTIFS(F5:F12,">" 100000)

ในที่นี้ มีเพียงโอเปอเรเตอร์เท่านั้นที่ถูกแทรกเข้าไปในสมการ ไม่มีเกณฑ์ที่เป็นตัวเลข

หากคุณกด Enter กล่องโต้ตอบข้อความ Microsoft Excel จะแสดง: "มีปัญหากับสูตรนี้"

เพื่อแก้ไขปัญหา พิมพ์สูตรให้ถูกต้อง:

=COUNTIFS(F5:F12,">100000")

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

กดปุ่มตกลง.

ด้วยเหตุนี้ คุณจะได้รับยอดขายมากกว่า 100,000USD

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