หัดเล่นๆให้เป็นเซียน Excel เขียน Blog โดย แมวใหญ่ Bigcat9: COUNTIF

COUNTIF การนับแบบมีเงื่อนไข คือ มีเงื่อนไขแล้วจึงนับ IF แล้วจึง COUNT

ไวยากรณ์
=COUNTIF(range,criteria)
=COUNTIF(ข้อมูลหรือชุดข้อมูลที่ต้องการนับ ,เงื่อนไข)

ตัวอย่าง

ข้อสังเกตุ
ถ้าเงื่อนไขคือการเท่ากับ เราไม่ต้องพิมพ์เครื่องหมาย " " ปิดหน้า-หลังของเงื่อนไข ถ้าเงื่อนไข ไม่ใช่เท่ากับ เช่น มากกว่า,น้อยกว่า, มากกว่าหรือเท่ากับ, น้อยกว่าหรือเท่ากับเป็นต้น แบบนี้ต้องมีเครื่องหมาย " " ครอบเงื่อนไขอีกทีหนึ่ง สาเหตุก็เนื่องจาก Excel อ่านค่าเครื่องหมายเหล่านี้ เป็น Text นั่นเอง พอเป็น Text ก็ต้องใช้ " " ปิดหน้าหลัง

อีกประการคือถ้าต้องการให้เงื่อนไขอ่านจากเซลล์อ้างอิงใดๆ ถ้าเงื่อนไขมีค่าไม่เท่ากับเซลล์อ้างอิงนั้น ต้อง เชื่อมด้วยเครื่องหมาย & ที่เงื่อนไขด้วย เช่น จากโจทย์เดิม แต่เงื่อนไขอยู่ที่เซลล์ A1

จะพบว่า =COUNTIF(C3:C7,A1) อันนี้พิมพ์ A1 ลงไปเป็นเงื่อนไขได้เลย เนื่องจากเงื่อนไขมีความหมายเป็น เท่ากับ ส่วนข้ออื่นๆ เงื่อนไขไม่ได้เป็นเท่ากับ ต้องพิมพ์ " " ปิดหน้าหลัง เงื่อนไขตามที่กล่าวมาแล้ว และจะต้อง เชื่อมด้วย & เสมอ

การนับเซลล์ว่าง
เราสามารถใช้ COUNTIF นับเซลล์ว่างได้ ดังนี้ สมมุติชุดข้อมูลอยู่ที่ เซลล์ B2:B7 ถ้าต้องการนับเซลล์ว่าง
=COUNTIF(B2:B7,"<>"&"*") ความหมายคือ ไม่เท่ากับ อะไรเลยซึ่งก็คือเซลล์ว่างนั่นเอง

หรือจะเลี่ยงไป ใช้คำสั่งนี้แทนก็ได้ =COUNTBLANK()ซึ่งในที่นี้คือ COUNTBLANK(B2:B7) นั่นเอง

The COUNTIF function does not support multiple ranges.
ขออนุญาตยกภาษาฝรั่งมังค่า มาอ้างอิงเล็กน้อย แปลกันง่ายๆได้ว่า เจ้า COUNTIF นี่ไม่สามารถนับเงื่อนไขหลายๆเงื่อนไขได้นั่นเอง
ถ้ามีหลายๆเงื่อนไขให้นับ แล้วเราจะทำ จะใดดี..
ทำได้อยู่หลายๆวิธี เช่น ใช้ IF ก่อน แล้วค่อยมา COUNT จากผลลัพท์ของ IF ก็ได้ อันนี้วิธีหนึ่ง อีกวิธีหนึ่งพอเป็นกระสัยคือ ทำCOUNTIF แต่ละเงื่อนไขให้จบไปในตัวก่อน แล้วนำผลลัพท์ของแต่ละ COUNTIF ที่ได้มารวมกัน จะเอามาบวกหรือจะ Sumก็ตามแต่ถนัด

เช่น เขียนสูตรแบบนี้
=COUNTIF(A3,A8,F16,">0") แบบนี้ไม่ได้แน่ เพราะมันมีหลายๆเงื่อนไขใน COUNTIF เดียว

ลองดูวิธีนี้ =COUNTIF(A3,">0")+COUNTIF(A8,">0")+COUNTIF(F16">0")
หรือ =SUM(COUNTIF(A3,">0"),COUNTIF(A8,">0"),COUNTIF(F16,">0"))
อย่างนี้เป็นต้น

ขอยกตัวอย่างกรณีอื่นๆที่ให้เป็นแนวทางในการใช้
IF แล้ว COUNT และ COUNTIF เพื่อเปรียบเทียบ

ตัวอย่าง เงื่อนไขคือต้องการหาค่าที่มากกว่า 0

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

การนับค่าเป็นช่วงๆ
เช่นเงื่อนไขต้องการนับค่าที่มากกว่า100 และค่าที่น้อยกว่า 500

มีหลายๆวิธีที่จะหาคำตอบออกมา
เช่น ใช้ IF อย่างเดียวก็ได้ โดยการเพิ่มคอลัมน์ แล้วใช้ IF บังคับทั้ง 2 เงื่อนไขให้เป็นเลขลงตัวน้อยๆ (แนะนำให้เป็น 0 และ 1 จะง่าย) กรณีเป็นจริงให้เป็น 1 ถ้าเป็นเท็จให้เป็น 0 ทั้ง 2กรณี แล้วนำทั้ง 2 กรณีมา คูณกัน จากนั้น Sum หาผลรวมก็จะได้จำนวนออกมาตามเงื่อนไขที่ต้องการ

วิธีนี้ดูเหมือนจะยุ่งยากซับซ้อน แต่วิธีนี้กลับเป็นวิธีที่ตรวจสอบง่ายที่สุด เนื่องจากการกระจายเงื่อนไขออกมานั่นเอง

วิธีการเดียวกันนี้หากไม่ต้องการกระจายสูตรสามารถรวบสูตรเข้ามาให้กระชับดังนี้

เนื่องจากเงื่อนไขให้บังคับทั้ง 2 กรณี ดังนั้นจะใช้ IF(AND() ก็ได้ ดังนี้

หากจะทำเป็น Array ก็อาจจะทำได้ดังนี้

มาจากสูตร IF ธรรมดา ดังตัวอย่างด้านบน ที่เซลล์ใดๆ พิมพ์
=SUM(IF(B2:B7>100,1,0)*IF(B2:B7<500,1,0))
จากนั้นบังคับ กด Ctrl + Shift +Enter พร้อมกัน ..ติดรูป
{=SUM(IF(B2:B7>100,1,0)*IF(B2:B7<500,1,0))}

กรณีใช้ COUNTIF

ต้องใช้ COUNTIF ทีละกรณีก่อน แล้วนำผลลัพท์มาลบกัน COUNTIF ที่น้อยกว่า 500 - COUNTIF ที่น้อยกว่าหรือเท่ากับ 100

การใช้ COUNTIF นับค่าที่ซ้ำกัน
ตัวอย่าง ต้องการหาค่าที่ไม่ซ้ำกันในตารางด้านล่าง

สามารถใช้หลายๆวิธีในการหาค่าที่ไม่ซ้ำกันออกมา
วิธีที่ 1 ใช้ COUNTIF และ IF แบบกระจายสูตรออกมา

ใช้ COUNTIF เพื่อนับค่าที่ซ้ำกัน ถ้าไม่ซ้ำจะเป็น 1 ถ้า ซ้ำ จะมากกว่า 1
ใช้ IF บังคับค่า COUNTIF ที่ได้ ถ้ามากกว่า 1 ให้เป็น ค่าว่าง (Blank) ถ้าไม่ใช่ให้เป็นค่าที่ต้องการ
- - - - - - - - - - -

วิธีที่ 2. มาจากวิธีที่ 1 แต่ใช้วิธีรวบสูตรเข้าไว้ด้วยกัน

- - - - - - - - - - -

วิธีที่ 3 หากสามารถ Sort data ได้โดยเรียงค่าจากน้อยไปมาก หรือมากไปน้อยก็ได้ สามารถใช้ IF ธรรมดา หาค่าที่ต้องการออกมาได้เช่นกัน

จากสูตร อธิบายได้ว่า ถ้า B2=B1 ให้เป็น ค่าว่าง (Blank) ถ้าไม่ใช่ให้เป็น B2 (ตัวมันเอง)

- - - - - - - - - - -

วิธีที่ 4. แถมให้อีกวิธี ใช้ Array วิธีนี้จะไม่อธิบายมาก เพียงแต่อยากแสดงให้ดูว่า จะใช้ Array ก็ได้

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

อีกซักตัวอย่าง โจทย์ต้องการให้ดึงเอาเฉพาะค่าที่ไม่ซ้ำออกมา