ฟังก์ชัน Excel COUNTIF ช่วยให้คุณสามารถนับจำนวนเซลล์ที่ตรงตามเกณฑ์หรือเงื่อนไขเฉพาะในช่วงที่กำหนด
ฟังก์ชัน COUNTIF เป็นหนึ่งในฟังก์ชันทางสถิติใน Excel ที่เป็นการรวมกันของฟังก์ชัน COUNT และ IF หรือฟังก์ชัน COUNTA เมื่อใช้ใน fomula ฟังก์ชันจะนับจำนวนเซลล์ที่ตรงกับเกณฑ์หรือเงื่อนไขเฉพาะในช่วงเดียวกันหรือหลายช่วง ฟังก์ชัน COUNTIF ช่วยนับเซลล์ที่มีข้อความ ตัวเลข หรือวันที่ที่ตรงกับเกณฑ์เฉพาะ
คุณสามารถนับเซลล์โดยใช้ฟังก์ชัน COUNTIF หรือ COUNTIFS ใน Excel ความแตกต่างระหว่างฟังก์ชัน COUNTIF และ COUNTIFS คือ COUNTIF ใช้สำหรับนับเซลล์ที่ตรงตามเกณฑ์หนึ่งในช่วงหนึ่ง ในขณะที่ COUNTIFS จะนับเซลล์ที่ตรงตามเงื่อนไขหลายข้อในช่วงเดียวกันหรือหลายช่วง
บทความนี้จะสาธิตวิธีการใช้ฟังก์ชันทั้งสองอย่าง COUNTIF และ COUNTIFS ใน Excel
ฟังก์ชัน Excel COUNTIF
ฟังก์ชัน COUNTIF ช่วยให้คุณสามารถนับข้อมูลตามเกณฑ์หรือเงื่อนไขเฉพาะได้ เงื่อนไขที่ใช้ในฟังก์ชันทำงานร่วมกับตัวดำเนินการเชิงตรรกะ (, , =, >=, <=) และอักขระตัวแทน (*, ?) สำหรับการจับคู่บางส่วน
ไวยากรณ์ของฟังก์ชัน COUNTIF
โครงสร้างของฟังก์ชัน COUNTIF คือ:
=COUNTIF(ช่วง,เกณฑ์)พารามิเตอร์:
พิสัย– ช่วงของเซลล์ที่จะนับเกณฑ์– เงื่อนไขกำหนดว่าควรรวมเซลล์ใดในการนับในช่วงที่ระบุ เกณฑ์อาจเป็นค่าตัวเลข ข้อความ การอ้างอิงไปยังที่อยู่ของเซลล์หรือสมการ
การใช้ฟังก์ชัน COUNTIF เพื่อนับค่าตัวเลข
ดังที่เราได้กล่าวไว้ข้างต้น เกณฑ์ (อาร์กิวเมนต์ที่สอง) ในฟังก์ชัน COUNTIF จะกำหนดเงื่อนไขที่บอกฟังก์ชันว่าจะนับเซลล์ใด
ฟังก์ชันนี้ช่วยให้คุณนับจำนวนเซลล์ที่มีค่าที่ตรงตามเงื่อนไขทางตรรกะ เช่น เท่ากับ มากกว่า น้อยกว่าหรือไม่เท่ากับค่าที่ระบุ เป็นต้น
ในตัวอย่างด้านล่าง สูตรจะนับเซลล์ที่มีค่าเท่ากับ 5 (เกณฑ์) คุณสามารถแทรก '5 ในสูตรได้โดยตรงหรือใช้การอ้างอิงไปยังที่อยู่เซลล์ที่มีค่า (เซลล์ D2 ในตัวอย่างด้านล่าง)
=COUNTIF(B2:B11,D2)
สูตรข้างต้นนับจำนวนเซลล์ในช่วงเซลล์ (B2:B11) ที่มีค่าเท่ากับค่าในเซลล์ D2
สูตรต่อไปนี้นับเซลล์ที่มีค่าน้อยกว่า 5
=COUNTIF(B2:B11,"<5")ตัวดำเนินการน้อยกว่า (<) บอกให้สูตรนับเซลล์ที่มีค่าน้อยกว่า '5' ในช่วง B2:B11 เมื่อใดก็ตามที่คุณใช้ตัวดำเนินการตามเงื่อนไข อย่าลืมใส่เครื่องหมายคำพูดคู่ (“”)
บางครั้งเมื่อคุณต้องการนับเซลล์โดยพิจารณาจากเกณฑ์ (ค่า) ในเซลล์ ในกรณีดังกล่าว ให้สร้างเกณฑ์โดยเข้าร่วมตัวดำเนินการและการอ้างอิงเซลล์ เมื่อคุณทำเช่นนั้น คุณจะต้องใส่ตัวดำเนินการเปรียบเทียบในเครื่องหมายคำพูดคู่ (“”) แล้วใส่เครื่องหมายและ (&) ระหว่างตัวดำเนินการเปรียบเทียบและการอ้างอิงเซลล์
=COUNTIF(B2:B11,">="&D2)
รูปภาพด้านล่างแสดงสูตรตัวอย่างบางส่วนและผลลัพธ์
การใช้ฟังก์ชัน COUNTIF เพื่อนับค่าข้อความ
เมื่อต้องการนับเซลล์ที่มีสตริงข้อความ ให้ใช้สตริงข้อความนั้นเป็นอาร์กิวเมนต์เกณฑ์หรือเซลล์ที่มีสตริงข้อความ ตัวอย่างเช่น ในตารางด้านล่าง หากเราต้องการนับเซลล์ทั้งหมดในช่วง (B21:D27) โดยมีค่าข้อความในเซลล์ B21 (แซม) เราสามารถใช้สูตรต่อไปนี้ได้:
=COUNTIF(B21:D27,B21)
ดังที่เราได้กล่าวไว้ก่อนหน้านี้ เราสามารถใช้ข้อความ 'sam' โดยตรงในสูตรหรือใช้การอ้างอิงเซลล์ที่มีเกณฑ์ (B21) สตริงข้อความควรอยู่ในเครื่องหมายคำพูดคู่ (“”) เสมอเมื่อใช้ในสูตรใน excel
=COUNTIF(B21:D27,"แซม")
ในการนับเซลล์ที่ไม่มีข้อความที่ระบุ ให้ใช้สูตรด้านล่าง:
=COUNTIF(B21:D27,""&B21)ตรวจสอบให้แน่ใจว่าได้ใส่ 'ไม่เท่ากับ' "" ตัวดำเนินการในเครื่องหมายคำพูดคู่
หากคุณกำลังใช้ข้อความ 'sam' โดยตรงในสูตร คุณจะต้องใส่โอเปอเรเตอร์ '' และสตริงข้อความไว้ด้วยกัน ("แซม") ในเครื่องหมายคำพูดคู่
=COUNTIF(B21:D27,"แซม") 
การใช้สัญลักษณ์แทนในฟังก์ชัน Excel COUNTIF (การจับคู่บางส่วน)
คุณสามารถใช้สูตร COUNTIF ที่มีอักขระตัวแทนเพื่อนับเซลล์ที่มีคำ วลี หรือตัวอักษรเฉพาะ มีอักขระตัวแทนสามตัวที่คุณสามารถใช้ในฟังก์ชัน Excel COUNTIF:
*(เครื่องหมายดอกจัน) – ใช้เพื่อนับเซลล์ที่มีอักขระ/ตัวอักษรเริ่มต้นและสิ้นสุดจำนวนเท่าใดก็ได้ (เช่น St* อาจหมายถึง Stark, Stork, Stacks เป็นต้น?(เครื่องหมายคำถาม) – ใช้เพื่อค้นหาเซลล์ที่มีอักขระตัวเดียว (เช่น St?rk อาจหมายถึง Stark หรือ Stork~(ตัวหนอน) – ใช้เพื่อค้นหาและนับจำนวนเซลล์ที่มีเครื่องหมายคำถามหรือเครื่องหมายดอกจัน (~, *, ?) ในข้อความ
การนับเซลล์ที่ขึ้นต้นหรือลงท้ายด้วยอักขระบางตัว
ในการนับเซลล์ที่ขึ้นต้นหรือลงท้ายด้วยข้อความเฉพาะที่มีอักขระอื่นๆ จำนวนเท่าใดก็ได้ในเซลล์ ให้ใช้สัญลักษณ์แทนดอกจัน (*) ในอาร์กิวเมนต์ที่สองของฟังก์ชัน COUNTIF
ใช้สูตรตัวอย่างเหล่านี้:
=COUNTIF(A1:A10,"A*") – เพื่อนับเซลล์ที่ขึ้นต้นด้วย “A”
=COUNTIF(A19:A28,"*er") – เพื่อนับจำนวนเซลล์ที่ลงท้ายด้วยอักขระ “เอ้อ”
=COUNTIF(A2:A12,"*QLD*") – สำหรับการนับเซลล์ที่มีข้อความ “QLD” ที่ใดก็ได้ในสตริงข้อความ
เอ ? แทนอักขระหนึ่งตัว ใช้สัญลักษณ์แทนนี้ในฟังก์ชัน COUNTIF ด้านล่างเพื่อนับจำนวนเซลล์ที่มีอักขระ +1 โดยที่ '?' ถูกนำมาใช้.
=COUNTIF(A1:A10,"พาร์")
การนับเซลล์ว่างและไม่ว่างด้วยฟังก์ชัน COUNTIF
สูตร COUNTIF ยังมีประโยชน์ในการนับจำนวนเซลล์ว่างหรือไม่ว่างในช่วงที่กำหนด
นับเซลล์ที่ไม่ว่างเปล่า
หากคุณต้องการนับเฉพาะเซลล์ที่มีค่า 'ข้อความ' ให้ใช้สูตรด้านล่าง สูตรนี้จะพิจารณาเซลล์ที่มีวันที่และตัวเลขเป็นเซลล์ว่าง และจะไม่รวมเซลล์เหล่านั้นในการนับ
=COUNTIF(A1:B12,"*")ตัวแทน * จับคู่กับเฉพาะค่าข้อความและส่งคืนการนับค่าข้อความทั้งหมดในช่วงที่กำหนด
ถ้าคุณต้องการนับเซลล์ที่ไม่ว่างทั้งหมดในช่วงที่กำหนด ให้ลองใช้สูตรนี้:
=COUNTIF(A1:B12,"")
นับเซลล์ว่าง
หากคุณต้องการนับเซลล์ว่างในช่วงที่กำหนด ให้ใช้ฟังก์ชัน COUNTIF กับ * อักขระตัวแทนและ ตัวดำเนินการในอาร์กิวเมนต์เกณฑ์เพื่อนับเซลล์ว่าง
สูตรนี้นับเซลล์ที่ไม่มีค่าข้อความ:
=COUNTIF(A1:B12,""&"*")ตั้งแต่ * สัญลักษณ์แทนตรงกับค่าข้อความใด ๆ สูตรข้างต้นจะนับเซลล์ทั้งหมดไม่เท่ากับ *. นับเซลล์ที่มีวันที่และตัวเลขเป็นช่องว่างเช่นกัน
ในการนับช่องว่างทั้งหมด (ทุกประเภทค่า):
=COUNTIF(A1:B12,"")ฟังก์ชันนี้นับเฉพาะเซลล์ว่างในช่วง
การใช้ฟังก์ชัน COUNTIF เพื่อนับวันที่
คุณสามารถนับเซลล์ที่มีวันที่ (เหมือนกับที่คุณทำกับเกณฑ์ตัวเลข) ที่ตรงตามเงื่อนไขทางตรรกะหรือวันที่หรือวันที่ที่ระบุในเซลล์อ้างอิง
ในการนับเซลล์ที่มีวันที่ระบุ (05-05-2020) เราจะใช้สูตรนี้:
=COUNTIF(B2:B10,"05-05-2020")
คุณยังสามารถระบุวันที่ในรูปแบบต่างๆ เป็นเกณฑ์ในฟังก์ชัน COUNTIF ดังที่แสดงด้านล่าง:
ถ้าคุณต้องการนับเซลล์ที่มีวันที่ก่อนหรือหลังวันที่หนึ่งๆ ให้ใช้ตัวดำเนินการน้อยกว่า (ก่อน) หรือมากกว่า (หลัง) พร้อมกับวันที่ที่ระบุหรือการอ้างอิงเซลล์
=COUNTIF(B2:B10,">=05/05/2020")
คุณยังสามารถใช้การอ้างอิงเซลล์ที่มีวันที่โดยการรวมเข้ากับตัวดำเนินการ (ภายในเครื่องหมายคำพูดคู่)
ในการนับจำนวนเซลล์ในช่วง A2:A14 ที่มีวันที่ก่อนวันที่ใน E3 ให้ใช้สูตรด้านล่าง โดยที่ตัวดำเนินการมากกว่า (<) หมายถึงก่อนวันที่ใน E3
=COUNTIF(A2:A14,"<"&E3)
สูตรตัวอย่างบางส่วนและผลลัพธ์:
นับวันที่ตามวันที่ปัจจุบัน
คุณสามารถรวมฟังก์ชัน COUNTIF กับฟังก์ชันวันที่ของ Excel เช่น TODAY() เพื่อนับเซลล์ที่มีวันที่ปัจจุบันได้
=COUNTIF(A2:A14,">"&TODAY())ฟังก์ชันนี้นับวันที่ทั้งหมดจากวันนี้ในช่วง (A2:A14)
นับวันที่ระหว่างช่วงวันที่ที่ระบุ
ถ้าคุณต้องการนับวันที่ทั้งหมดระหว่างวันที่สองวัน คุณต้องใช้เกณฑ์สองข้อในสูตร
เราสามารถทำได้โดยใช้สองวิธี: ฟังก์ชัน COUNTIF และ COUNTIFS
การใช้ฟังก์ชัน Excel COUNTIF
คุณต้องใช้ฟังก์ชัน COUNTIF สองฟังก์ชันเพื่อนับวันที่ทั้งหมดระหว่างวันที่ระบุสองวัน
ในการนับวันที่ระหว่าง '09-02-2020' และ '20-08-2021' ให้ใช้สูตรนี้:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">20-08-2021")สูตรนี้จะค้นหาจำนวนเซลล์ที่มีวันที่หลังวันที่ 2 กุมภาพันธ์ก่อน และลบจำนวนเซลล์ที่มีวันที่หลังวันที่ 20 สิงหาคม ตอนนี้เราได้ตัวเลขแล้ว ของเซลล์ที่มีวันที่หลังวันที่ 2 กุมภาพันธ์ และในหรือก่อนวันที่ 20 สิงหาคม (นับเป็น 9)
ถ้าคุณไม่ต้องการให้สูตรนับทั้ง 2 กุมภาพันธ์และ 20 สิงหาคม ให้ใช้สูตรนี้แทน:
=COUNTIF(A2:A14,">09-02-2020")-COUNTIF(A2:A14,">=20-08-2021")เพียงแทนที่ตัวดำเนินการ '>' ด้วย '>=' ในเกณฑ์ที่สอง
การใช้ฟังก์ชัน Excel COUNTIFS
ฟังก์ชัน COUNTIFS รองรับเกณฑ์หลายเกณฑ์เช่นกัน และไม่เหมือนฟังก์ชัน COUNTIF จะนับเซลล์หลังจากตรงตามเงื่อนไขทั้งหมดเท่านั้น ถ้าคุณต้องการนับเซลล์ที่มีวันที่ทั้งหมดระหว่างวันที่ระบุสองวัน ให้ป้อนสูตรนี้:
=COUNTIFS(A2:A14,">"&A11,A2:A14,"<"&A10)
หากคุณต้องการรวมวันที่ที่ระบุด้วยในการนับ ให้ใช้ตัวดำเนินการ '>=' และ '<=' ไปกับสูตรนี้:
=COUNTIFS(A2:A14,">=09-02-2020",A2:A14,"<=20-08-2021")เราใช้วันที่โดยตรงในเกณฑ์แทนการอ้างอิงเซลล์สำหรับตัวอย่างนี้
วิธีจัดการ COUNTIF และ COUNTIFS ด้วยเกณฑ์หลายเกณฑ์ใน Excel
ฟังก์ชัน COUNTIF ส่วนใหญ่จะใช้สำหรับการนับเซลล์ที่มีเกณฑ์เดียว (เงื่อนไข) ในช่วงเดียว แต่คุณยังสามารถใช้ COUNTIF เพื่อนับเซลล์ที่ตรงกับหลายเงื่อนไขในช่วงเดียวกันได้ อย่างไรก็ตาม สามารถใช้ฟังก์ชัน COUNTIFS เพื่อนับเซลล์ที่ตรงตามเงื่อนไขหลายข้อในช่วงเดียวกันหรือต่างกันได้
วิธีการนับตัวเลขภายในช่วง
คุณสามารถนับเซลล์ที่มีตัวเลขระหว่างตัวเลขทั้งสองที่ระบุได้โดยใช้สองฟังก์ชัน: COUNTIF และ COUNTIFS
COUNTIF เพื่อนับตัวเลขระหว่างสองตัวเลข
การใช้งานทั่วไปอย่างหนึ่งสำหรับฟังก์ชัน COUNTIF ที่มีหลายเกณฑ์คือการนับตัวเลขระหว่างตัวเลขที่ระบุสองตัว เช่น เพื่อนับจำนวนที่มากกว่า 10 แต่น้อยกว่า 50 เมื่อต้องการนับตัวเลขภายในช่วง ให้รวมฟังก์ชัน COUNTIF สองฟังก์ชันขึ้นไปเข้าด้วยกันในสูตรเดียว ให้เราแสดงให้คุณเห็นว่า
สมมติว่าคุณต้องการนับเซลล์ในช่วง B2:B9 โดยที่ค่ามากกว่า 10 และน้อยกว่า 21 (ไม่รวม 10 และ 21) ให้ไปกับสูตรนี้:
=COUNTIF(B2:B14,">10")-COUNTIF(B2:B14,">=21")พบความแตกต่างระหว่างตัวเลขสองตัวโดยการลบสูตรหนึ่งออกจากอีกสูตรหนึ่ง สูตรแรกนับตัวเลขที่มากกว่า 10 (ซึ่งก็คือ 7) สูตรที่สองจะคืนค่าการนับจำนวนที่มากกว่าหรือเท่ากับ 21 (ซึ่งก็คือ 4) และผลลัพธ์ของสูตรที่สองจะถูกลบออกจากสูตรแรก (7 -4) เพื่อรับการนับตัวเลขระหว่างสองตัวเลข (3)
ถ้าคุณต้องการนับเซลล์ที่มีตัวเลขมากกว่า 10 และน้อยกว่า 21 ในช่วง B2:B14 รวมทั้งตัวเลข 10 และ 21 ให้ใช้สูตรนี้:
=COUNTIF(B2:B14,">=10")-COUNTIF(B2:B14,">21")
COUNTIFS เพื่อนับตัวเลขระหว่าง 2 หมายเลข
ในการนับตัวเลขระหว่าง 10 ถึง 21 (ยกเว้น 10 ถึง 21) จะอยู่ในเซลล์ B2 ถึง B9 ให้ใช้สูตรนี้:
=COUNTIFS(B2:B14,">10",B2:B14,"<21")
หากต้องการรวม 10 และ 21 ในการนับ ให้ใช้ 'มากกว่าหรือเท่ากับ' (>=) แทน 'มากกว่า' และ 'น้อยกว่าหรือเท่ากับ' (<=) แทนตัวดำเนินการ 'น้อยกว่า' ในสูตร .
COUNTIFS เพื่อนับเซลล์ด้วยเกณฑ์หลายเกณฑ์ (AND Criteria)
ฟังก์ชัน COUNTIFS เป็นพหูพจน์ของฟังก์ชัน COUNTIF ที่นับเซลล์ตามเกณฑ์ตั้งแต่สองเกณฑ์ขึ้นไปในช่วงเดียวกันหรือหลายช่วง เรียกว่า 'AND logic' เนื่องจากฟังก์ชันนี้สร้างขึ้นสำหรับการนับเซลล์เมื่อเงื่อนไขที่กำหนดทั้งหมดเป็น TRUE เท่านั้น
ตัวอย่างเช่น เราต้องการทราบจำนวนครั้ง (จำนวนเซลล์) ที่ขนมปัง (ค่าในคอลัมน์ A) ขายได้น้อยกว่า 5 (ค่าในคอลัมน์ C)
เราสามารถใช้สูตรนี้:
=COUNTIFS(A2:A14,"ขนมปัง",C2:C14,"<5")
COUNTIF เพื่อนับเซลล์ด้วยเกณฑ์หลายเกณฑ์ (OR Criteria)
ถ้าคุณต้องการนับจำนวนเซลล์ที่ตรงกับเกณฑ์หลายรายการในช่วงเดียวกัน ให้รวมฟังก์ชัน COUNTIF สองฟังก์ชันขึ้นไปเข้าด้วยกัน ตัวอย่างเช่น หากคุณต้องการค้นหาจำนวนครั้งที่ 'Bread' หรือ 'Cheese' ทำซ้ำในช่วงที่ระบุ (A2:A14) ให้ใช้สูตรด้านล่าง:
=COUNTIF(A2:A14,"Bread")+COUNTIF(A2:A14,"ชีส")สูตรนี้นับเซลล์ที่มีเงื่อนไขเป็น TRUE อย่างน้อยหนึ่งข้อ นั่นเป็นเหตุผลที่เรียกว่า 'OR ตรรกะ'
หากคุณต้องการประเมินมากกว่าหนึ่งเกณฑ์ในแต่ละฟังก์ชัน ควรใช้ COUNTIFS แทน COUNTIF ในตัวอย่างด้านล่าง เราต้องการนับสถานะ "สั่งแล้ว" และ "ส่งแล้ว" สำหรับ "ขนมปัง" ดังนั้นเราจะใช้สูตรนี้:
=COUNTIFS(A2:A14,"Bread",C2:C14,"สั่งแล้ว")+COUNTIFS(A2:A14,"Bread",C2:C14,"ส่งมอบแล้ว")
เราหวังว่าสิ่งนี้จะง่าย แต่การสอนแบบยาวจะช่วยให้คุณมีแนวคิดเกี่ยวกับวิธีใช้ฟังก์ชัน COUNTIF และ COUNTIF ใน Excel