“อยากนับเฉพาะค่าบวก เขียนสูตรยังไงดี?”

“อยากนับเฉพาะค่าว่าง เขียนสูตรยังไงดี?”

“อยากนับเฉพาะตัวหนังสือ เขียนสูตรยังไงดี?”

เหล่านี้คือคำถามที่เกี่ยวกับการนับ (COUNT) ใน Excel ที่มีคนถามผมบ่อยๆ

ตอนแรกผมคิดว่าการนับไม่มีเทคนิคอะไรมาก เพราะมีฟังก์ชันให้ใช้ไม่กี่ตัว แต่พอศึกษาถึงได้รู้ว่าการนับที่ดูเหมือนไม่มีอะไรนั้น แท้จริงแล้วมีเทคนิคและรายละเอียดไม่น้อยเลย

ยิ่งศึกษา ยิ่งพบว่าสิ่งที่เคยรู้มา หลายอย่างเข้าใจผิด

และเข้าใจผิดมาตลอด

เพื่อไม่ให้มีคนเข้าใจผิดเหมือนผม ขอสรุปสิ่งที่ศึกษาเป็น 10 ข้อ ตั้งชื่อว่า 10 เทคนิค COUNT ใน Excel  ที่คุณอาจยังไม่รู้ ครับ ^__^

เพื่ออธิบายทั้ง 10 ข้อให้เห็นภาพ ขออธิบายด้วยตัวอย่างนี้ครับ

สมมติมีข้อมูลทั้งหมด 15 ค่า ถูกเก็บไว้ในเซลล์ A4:A18 ดังนี้

A10 และ A10 คือค่าว่าง (Blank)

A16 ดูเผินๆเหมือนเป็นค่าว่าง แต่จริงๆแล้วคือ “” (=””)

แล้ว ค่าว่าง (Blank) กับ “” ต่างกันยังไง?

เป็นคำถามที่ดีมากครับ

ค่าว่าง (Blank) คือเซลล์น้้นไม่มีอะไรเลย ไม่มีการอินพุตข้อมูลใดๆทั้งสิ้น ไม่มีแบบไม่มีจริงๆ

“” คือค่าเสมือนว่าง ดูด้วยตาเหมือนไม่มีข้อมูล แต่แท้จริงแล้วเก็บค่า “” เอาไว้ (หรืออาจเกิดจากสูตรก็ได้) ถ้าพูดกันจริงๆ “” ถือเป็นตัวหนังสือ (Text) ไม่ใช่ค่าว่าง (Blank)

หรือจะพูดว่า ค่าเสมือนว่าง (“”) คือ ว่างเก๊ ก็มิผิด ^^

มาลองดูเทคนิคทีละข้อครับ ^__^

1 นับเฉพาะตัวเลข

ก่อนหน้านี้ผมเคยเข้าใจว่า ถ้าต้องการนับให้ใช้ฟังก์ชัน COUNT

เพิ่งมารู้ว่าความเข้าใจนี้ไม่ถูกต้อง เพราะ COUNT ไม่ได้ใช้นับ แต่ใช้นับเฉพาะตัวเลข

จากตัวอย่างเดิม ถ้าใช้สูตร

=COUNT(A4:A18)

คำตอบที่ได้คือ 6

จากตัวอย่างนี้จะพบว่า จากข้อมูลทั้งหมด 15 ตัว มีเพียง 6 ตัวเท่านั้นที่ถูกนับ และ 6 ตัวนั้นคือตัวเลข (11, 0, -22, -23, 12, 13)

ส่วน “อื่นๆ” ไม่ถูกนับรวม

“อื่นๆ” ทีว่าคือ

1. ตัวหนังสือ (Text) และหมายรวมถึงค่าเสมือนว่าง (“”) ด้วย
2. ค่าว่าง (Blank)
3. ค่าความผิดพลาด (Error)

จากนี้ไป ต้องเข้าใจว่า COUNT ไม่ได้ใช้นับ แต่ใช้นับตัวเฉพาะตัวเลขนะครับ ^__^

2 นับเฉพาะ 0 (ศูนย์)

ถ้าต้องการนับเฉพาะตััวเลขที่เป็น 0 (ศูนย์, zero) การนับนี้เป็นแบบมีเงื่อนไข ฟังก์ชันที่ตอบโจทย์คือ COUNTIF หรือ COUNTIFS

ความต่างของฟังก์ชัน COUNTIF และ COUNTIFS คือ

COUNTIF นับแบบมีเงื่อนไขเดียว

แต่ COUNTIFS นับแบบมีหลายเงื่อนไขได้

เคสนี้มีเงื่อนไขเดียว (เงื่อนไขคือ 0) ใช้ฟังก์ชัน COUNTIF ได้เลย

โครงสร้างของฟังก์ชัน COUNTIF คือ

COUNTIF(range, criteria)

range คือ ช่วงข้อมูล เคสนี้คือ A4:A18 นั่นเอง

criteria คือ เงื่อนไข เคสนี้คือ 0 นั่นเอง

งั้นก็จับเข้าสูตรซะเลยเป็น

=COUNTIF(A4:A18,0)

ผลลัพธ์ที่ได้คือ 1 นั่นเอง !

สังเกตว่า มีแค่ 0 เท่านั้นที่ถูกนับ ส่วนค่าว่าง หรือ ค่าเสมือนว่าง (“”) ไม่ได้ถูกนับนะครับ ^__^

3 นับเฉพาะค่าบวก

ถ้าต้องการนับเฉพาะค่าบวก ถือเป็นการนับแบบมีเงื่อนไข ใช้ฟังก์ชันเดียวกันกับนับเฉพาะ 0 หรือก็คือ COUNTIF นั่นเอง

แต่เคสนี้พิเศษเล็กน้อย ตรงที่ไม่สามารถระบุเป็นเลข 0 หรือเลขใดๆได้

งั้นต้อง “เล่นแร่แปรธาตุนิดหน่อย” กันหน่อย ^^

ค่าบวก ถ้าแปลเป็นภาษาตรรกะก็คือ >0 นั่นเอง

แต่เราไม่สามารถเขียนสูตรเป็น COUNTIF(A4:A18,>0) ตรงๆได้ เพราะ criteria ไม่รองรับการเขียนแบบนี้

งั้นทำไงดี?

ง่ายมากครับ แค่ใส่เครื่องหมายคำพูด (”  “) ครอบ criteria ก็พอ หรือเขียนสูตรเป็น

=COUNTIF(A4:A18,“>0”)

ชะแว๊บ !!

ได้คำตอบเท่ากับ 3 นั่นเอง (11, 12, 13) ^__^

4 นับเฉพาะค่าลบ

ถ้าสูตรนับเฉพาะค่าบวกคือ  =COUNTIF(A4:A18,”>0″) พอจะเดาสูตรการนับเฉพาะค่าลบได้ไหมครับ?

ติ๊กต่อก ติ๊กต่อก …

ใช่แล้ว !

สูตรก็คือ

=COUNTIF(A4:A18,“<0”)

ไม่ยากใช่ไหมครับ ^__^

5 นับเฉพาะค่าว่าง (Blank)

ถ้านับเฉพาะค่าว่าง (Blank) จะมีความซับซ้อนนิดนึง เพราะต้องเช็คก่อนว่าค่านั้นเป็นค่าว่างจริงหรือเปล่า

ฟังก์ชันที่ใช้ตรวจสอบว่าค่านั้นเป็นค่าว่างหรือเปล่าก็คือ ISBLANK

โครงสร้างของฟังก์ชัน ISBLANK ง่ายมากคือ

ISBLANK(value)

value ก็คือ ค่าที่ต้องการตรวจสอบนั่นเอง โดย value อาจใส่เป็นค่าเดี่ยวๆ เช่น A4 หรือใส่เป็นช่วงข้อมูล เช่น A4:A18 ก็ได้ (ถ้า value เป็นช่วงข้อมูล ต้องทำเป็นสูตรอาร์เรย์ (Array Formula))

สูตรสำหรับเคสนี้ก็คือ

ISBLANK(A4:A18)

ถ้าค่าใดเป็นค่าว่าง (Blank) จะได้คำตอบเป็น TRUE

ผลลัพธ์ของ ISBLANK(A4:A18) จะได้เป็น

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

ถ้าได้ผลลัพธ์แบบนี้ นำไปทำอะไรต่อไม่ได้ ต้องแปลง TRUE, FALSE ให้เป็นตัวเลขก่อน

หรือก็คือแปลง TRUE ให้เป็น 1 และแปลง FALSE ให้เป็น 0

โดยการใส่เครื่องหมาย — นำหน้า (เครื่องหมายลบติดกันสองอัน) หรือเขียนสูตรเป็น

=–ISBLANK(A4:A18)

จะเปลี่ยนผลลัพธ์จาก TRUE, FALSE เมื่อกี้เป็น

{0;0;0;0;0;0;1;0;0;1;0;0;0;0;0}

ค่าที่เป็น 1 ก็คือค่าที่เป็นค่าว่าง (Blank) นั่นเอง

สเต็ปต่อมาคือต้องการรวมเฉพาะค่าที่เป็น 1 เราก็ใส่ฟังก์ชัน SUM ครอบ หรือเขียนเป็น

=SUM(–ISBLANK(A4:A18))

เนื่องจากเคสนี้ใส่ value เป็น A4:A18 สูตรนี้จึงถือเป็นสูตรอาร์เรย์ (Array Formula) ถ้าต้องการให้คำนวณถูกต้องต้องกดปุ่ม Ctrl+Shift+Enter แทนการกด Enter ธรรมดา หรือสูตรที่ถูกต้องคือ

{=SUM(–ISBLANK(A4:A18))}

(วงเล็บปีกกาในสูตรเกิดการจากการกด Ctrl+Shift+Enter ไม่ได้เกิดจากการพิมพ์)

แต่การกด Ctrl+Shift+Enter มีโอกาสลืมสูง เพื่อป้องกันการลืม (สำหรับเคสนี้) สามารถเปลี่ยนจากฟังก์ชัน SUM เป็น SUMPRODUCT ได้เลย หรือสูตรก็คือ

=SUMPRODUCT(–ISBLANK(A4:A18))

ถ้าใช้ SUMPRODUCT เขียนสูตรเสร็จแล้วก็ไม่ต้องกด Ctrl+Shift+Enter ให้เมื่อยตุ้ม ^^

6 นับเฉพาะค่าว่างและค่าเสมือนว่าง (“”)

จากข้อ 5 บางคนอาจสงสัยว่า

ทำไมไม่ใช้ฟังก์ชัน COUNTBLANK?

เหตุผลที่ไม่ใช้ เพราะ COUNTBLANK นับทั้งค่าว่าง (Blank) และค่าเสมือนว่าง (“”)

แต่สูตร =SUMPRODUCT(–ISBLANK(A4:A18)) นับเฉพาะค่าว่าง (Blank) เพียงอย่างเดียว

ความต่างของสองสูตรนี้ก็คือค่าเสมือนว่าง (“”) นั่นเอง

สำหรับเคสนี้ ถ้าใช้สูตร

=COUNTBLANK(A4:A18)

ผลลัพธ์ที่ได้คือ 3 หรือก็คือค่าว่างในเซลล์ A10, A13 และค่าเสมือนว่างในเซลล์ A16 (“”) นั่นเอง ^__^

7 นับทุกอย่างยกเว้นค่าว่าง 

ถ้าสูตรการนับค่าว่างคือ =SUMPRODUCT(–ISBLANK(A4:A18)) พอเดาได้ไหมครับว่า ถ้าต้องการนับทุกอย่างยกเว้นค่าว่าง (Blank) สูตรคืออะไร

ไม่ต้องคิดเยอะครับ ^^

เพราะสูตรคือ

=COUNTA(A4:A18)

อ้าว! COUNTA ไม่ได้นับเฉพาะตัวเลขกับตัวหนังสือเหรอ?

ก่อนหน้านี้ ผมเคยเข้าใจว่า COUNTA นับเฉพาะตัวเลขกับตัวหนังสือ (Text) พอศึกษาจึงรู้ว่าความเข้าใจนี้ไม่ถูกต้อง

ที่ถูกต้องแล้ว COUNTA นับทั้งตัวเลข ตัวหนังสือ และค่าความผิดพลาด (Error)

หรือพูดง่ายๆก็คือ COUNTA ไม่นับเฉพาะค่าว่าง (Blank) นั่นเอง !

นั่นแปลว่า COUNTA นับค่าเสมือนว่าง (“”) ด้วย !

จากเคสนี้ ถ้าใส่สูตรเป็น

=COUNTA(A4:A18)

ผลลัพธ์ที่ได้คือ 13 (ไม่นับ A10, A13 ที่เป็นค่าว่าง (Blank) แต่นับ A16 ที่เป็นค่าเสมือนว่าง (“”))

เชื่อว่าหลายคนน่าจะเคยเข้าใจผิดเหมือนผม

ไม่เป็นไร วันนี้เราเข้าใจถูกแล้วครัับ ^__^

อ้อ! เนื่องจากสูตรการนับค่าว่างคือ =SUMPRODUCT(–ISBLANK(A4:A18)) ถ้าปรับสูตรเล็กน้อยก็สามารถนับทุกอย่างยกเว้นค่าว่างได้นะครับ

ปรับยังไงน่ะหรือครับ?

หลักการคือเปลี่ยนจาก ISBLANK ให้เป็นค่าการตรวจสอบค่าที่ไม่ใช่ค่าว่าง

Excel ไม่มีฟังก์ชัน ISNONBLANK งั้นก็ใช้ฟังก์ชัน NOT ครอบ ISBLANK ได้เลยครับ ^__^

(NOT แปลงผลลัพธ์ให้เป็นตรงกันข้าม)

หรือเขียนสููตรเป็น

=SUMPRODUCT(–NOT(ISBLANK(A4:A18)))

ได้ผลลัพธ์เป็น 13 เหมือนกัน

แต่ผมว่าใช้ COUNTA ง่ายกว่าเยอะเลย ^^

8 นับเฉพาะตัวหนังสือ (Text)

ถ้าต้องการนับเฉพาะตัวหนังสือ ยังคงใช้ฟังก์ชัน COUNTIF แต่ปรัับสูตรเล็กน้อยเป็น

=COUNTIF(A4:A18,”*”)

เคสนี้เราใช้เครื่องหมายดอกจัน (*, Asterisk) มาช่วย

ความหมายของเครื่องหมายดอกจันคือ เป็นอะไรก็ได้ กี่ตัวอักษรก็ได้ เป็นค่าว่างก็ได้ (any number of characters)

ผลลัพธ์ที่ได้คือ 4 (Elon, Mark, เซลล์ A16 (ค่าเสมือนว่าง), Smith)

นอกจากสูตรนี้แล้ว ยังสามารถใช้ฟังก์ชัน ISTEXT ร่วมกับ SUMPRODUCT ได้ด้วย

(ฟังก์ชัน ISTEXT ใช้ตรวจสอบว่าเป็นตัวหนังสือ (Text) หรือไม่ ผลลัพธ์ที่ได้คือ TRUE, FALSE)

เขียนสูตรได้เป็น

=SUMPRODUCT(–ISTEXT(A4:A18))

สังเกตว่าค่าเสมือนว่าง (“”) จะถูกนับด้วย (ทั้ง 2 สูตร) เพราะค่าเสมือนว่างถูกมองว่าเป็นตัวหนังสือ (Text) แบบหนึ่งนั่นเอง

บอกแล้วว่า ค่าเสมือนว่าง (“”) คือ ว่างเก๊ ^^

9 นับเฉพาะตัวหนังสือ ไม่นับค่าเสมือนว่าง (“”)

ถ้าสูตรการนับเฉพาะตัวหนังสือคือ =COUNTIF(A4:A18,”*”) ลองเดาไหมครับว่า ถ้าต้องการนับเฉพาะตัวหนังสืออย่างเดียว ไม่นับค่าเสมือนว่าง (“”) ต้องใช้สูตรใด?

ติ๊กต่อก ติ๊กต่อก …

เฉลยเลยดีกว่า

ใช้สูตร

=COUNTIF(A4:A18,“?*”)

ผลลัพธ์ที่ได้คือ 3 (Elon, Mark, Smith)

จะเห็นว่าเปลี่ยนสูตรนิดเดียวจาก “*” เป็น “?*” นั่นเอง

เครื่องหมายคำถาม (?, Question Mark) และดอกจัน (*, Asterisk) จัดเป็นสัญลักษณ์แทน (Wildcard) แบบหนึ่ง

ดอกจัน (*) ใช้แทนอะไรก็ได้ กี่ตัวอักษรก็ได้ เป็นค่าว่างก็ได้ (any number of characters)

เครื่องหมายคำถาม (?) ใช้แทนอะไรก็ได้ที่มี 1 อักขระ (any single character) แต่ใช้เป็นค่าว่างไม่ได้

เคสนี้ ต้องการหาตัวอักษรที่ไม่ใช่ค่าเสมือนว่าง จึงใช้สัญลักษณ์เป็น ?*

สูตรแนวนี้ใช้กับเคสแบบไหนน่ะหรือครับ?

เท่าที่ผมเคยเจอ ส่วนใหญ่ใช้สร้าง Dropdown List แบบมีเงื่อนไข

เช่นสมมติว่า ให้เลือกเพศในเซลล์แรก เซลล์ถัดมาต้องแสดง dropdown list เฉพาะชื่อคนที่เป็นเพศนั้นเท่านั้น

ขอไม่อธิบายการสร้าง dropdown list ประเภทนี้นะครับ ถ้าอธิบายคงต้องใช้เวลาอีก 1 บทความเต็มๆ T_T

แต่ถ้าสนใจเทคนิคการสร้าง สามารถดูรายละเอียดจากชีต 9_1_MultiMatch ในไฟล์ตัวอย่างของบทความนี้ได้เลยครับ ^__^

10 นับเฉพาะค่าความผิดพลาด (Error)

ถ้าสูตรนับเฉพาะค่าว่าง (Blank) คือ

=SUMPRODUCT(–ISBLANK(A4:A18))

พอเดาได้ไหมครับว่า ถ้าต้องการนับเฉพาะค่าความผิดพลาด (Error) ต้องใช้สูตรใด?

ติ๊กต่อก ติ๊กต่อก …

ใช่แล้วครับ !

ใช้สูตร

=SUMPRODUCT(–ISERROR(A4:A18))

ฟังก์ชัน ISERROR ใช้ตรวจสอบว่าค่านี้เป็นค่าความผิดพลาด (Error) หรือเปล่า ผลลัพธ์ที่ได้คือ TRUE, FALSE วิธีใช้งานเหมือนฟังก์ชัน ISBLANK หรือ ISTEXT ทุกประการ

(ถ้าสนใจรายละเอียดการใช้ฟังก์ชัน ISERROR อ่านได้จากบทความเรื่อง ISERROR vs IFERROR ความเหมือนที่แตกต่าง ครับ)

…

เป็นยังไงกันบ้างกับทั้ง 10 เทคนิค เชื่อว่ามีประโยชน์กับทำงานจริงแน่นอน ^__^

ก่อนที่ผมจะเขียนบทความนี้ ผมคิดว่าเข้าใจฟังก์ชันเกี่ยวกับการนับหมดแล้วนะ แต่พอศึกษาในรายละเอียดถึงได้รู้ว่ามีหลายอย่างที่ยังไม่รู้ และหลายอย่างที่เข้าใจผิด

บางเทคนิคทำเอาอึ้งทีเดียว เช่น =COUNTIF(A4:A18,”?*”) (นับเฉพาะตัวหนังสือที่ไม่ใช่ค่าเสมือนว่าง (“”))

(เพิ่งรู้ว่าทำแบบนี้ได้)

รู้แล้วเก็บไว้คนเดียวคงไม่มีประโยชน์

งั้นเอามาแฉ เอ๊ย! เอามาแชร์ดีกว่า ^^

อ่านจบแล้ว อย่าลืมทดลองด้วยนะครับ ^__^

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

COUNTTechnique_180131

…

หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก Like เฟสบุ๊คแฟนเพจ วิศวกรรีพอร์ต หรือคลิก ที่นี่

อย่าลืมแชร์ให้เพื่อนอ่านเพื่อเป็นกำลังใจให้คนเขียนด้วยนะครับ ^__^

อ้อ! ตอนนี้ผมมีคอร์สออนไลน์ชื่อ “Excel In Essence: รู้ Excel เท่านี้ ชีวิตดีขึ้นมากมาย” สนใจดูรายละเอียดได้จาก ลิงค์นี้ ครับ
..

ขอบคุณข้อมูลดีๆจาก

https://support.office.com/en-us/article/countblank-function-6a92d772-675c-4bee-b346-24af6bd3ac22

https://support.office.com/en-us/article/wildcard-characters-ef94362e-9999-4350-ad74-4d2371110adb

https://exceljet.net/formula/count-cells-that-contain-text

https://exceljet.net/formula/count-cells-that-contain-errors

https://exceljet.net/excel-functions/excel-countif-function