“อยากนับเฉพาะเซลล์ที่มีตัวอักษร A ต้องเขียนสูตรยังไงคะ?” ลูกศิษย์คนหนึ่งถาม

“เคยลองใช้ COUNTIF แต่ไม่ได้คำตอบที่ต้องการค่ะ” 

“ถ้านับเซลล์ที่มีตัวอักษร A แล้วตัวอักษร a จะถูกนับด้วยไหมคะ?” เธอปล่อยคำถามแบบคอมโบเซ็ต

ทั้ง 3 คำถาม คือคำถามที่น่าสนใจ และผมคิดว่ามี (อย่างน้อย) 4 วิธีที่หาคำตอบได้

แต่ละวิธีทำยังไง มาดูแบบจะๆกันเลยครับ ^__^

1 COUNTIF

“อ้าว เค้าบอกแล้วไม่ใช่เหรอว่าใช้ COUNTIF ไม่ได้?”

เค้าอาจพูดตกหล่นไปนิดนึงครับ ถ้าเขียนให้ครบต้องเขียนว่า ใช้ COUNTIF แบบตรงๆไม่ได้ ^__^

เหตุผลที่ใช้ COUNTIF (หรือ COUNTIFS) แบบตรงๆไม่ได้ เพราะ COUNTIF จะนับเฉพาะเซลล์ที่มีตัวอักษร A ทั้งเซลล์

(มี A เป็นส่วนหนึ่งของข้อมูลในเซลล์ไม่ได้)

มาทำความรู้จักฟังก์ชัน COUNTIF กับนิดนึงครับ

COUNTIF ใช้นับว่าในช่วงข้อมูลใดๆ มีกี่เซลล์ที่มีข้อมูลตรงกับเงื่อนไขที่ต้องการ

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

COUNTIF(range, criteria)

range คือ ช่วงข้อมูล เช่น A4:A15

criteria คือ เงื่อนไขที่ต้องการ เช่น Product1, online, a

(ถ้าระบุ criteria ตรงๆในสูตรต้องมีเครื่องหมายคำพูดครอบ หรือเขียนเป็น “Product1”, “online”, “a”)

ถ้ากำหนด criteria เป็น a COUNTIF จะนับเฉพาะเซลล์ที่มีค่าเป็น a เท่านั้น (ห้ามมีตัวอักษรหรืออักขระอื่นปะปน)

แปลว่าเซลล์ที่มีตัวอักษรอื่นปนอยู่ด้วยจะไม่ถูกนับ

จากภาพด้านบน ผลลัพธ์ของ COUNTIF(A4:A15,D3) คือ 0 (ศูนย์) เพราะไม่มีเซลล์ใดใน A4:A15 ที่มีค่าเป็น a แม้มี 4 เซลล์ที่มี a อยู่ในนั้น ไม่ว่าจะเป็น

• abc
• 89afw
• C2-1AB
• 52-cat

แต่ทั้ง 4 เซลล์มีตัวอักษรหรืออักขระอื่นปนอยู่ด้วย จึงไม่ถูกนับ

ถ้าต้องการนับ 4 เซลล์นี้ ต้องปร้บวิธีการเขียน criteria นิดนึงครับ

ต้องเพิ่ม “อักขระตัวแทน” (Wildcard Character) เข้าไปด้วย นั่นคือตัว * (ดอกจัน, Asterisk)

* มีความหมายว่า ใช้แทนอักขระใดก็ได้ แทนกี่ตัวอักษรก็ได้

จากเดิมระบุเป็น a มีความหมายว่าข้อมูลต้องเป็น a เพียงอย่างเดียวเท่านั้น

ถ้าต้องการให้ a เป็นส่วนหนึ่งของคำที่ค้นหา ก็ระบุเป็น *a* หรืออาจเขียนสูตรเป็น

COUNTIF(A4:A15,”*a*”)

ถ้าไม่อยากระบุ a ในสูตรตรงๆ หรืออยากเขียนสูตรแบบอ้างอิง ก็ให้เก็บค่า a ไว้ในเซลล์นึง (เช่น D3) แล้วลิงค์ค่ามา หรือเขียนสูตรเป็น

=COUNTIF(A4:A15,”*”&D3&”*”)

ได้ผลลัพธ์เท่ากับ 4 ตามต้องการ

หรืออาจใช้ฟังก์ชัน COUNTIFS ก็ได้เช่นกัน

เยส !!

ง่ายกว่าที่คิดเยอะเลย วิธีนี้มีข้อเสียไหม?

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

ข้อเสียของวิธีนี้ ดูเผินๆเหมือนจะไม่มี แต่ถ้าดูดีๆจะพบว่ามี 2 ข้อคือ

1. แยกความแตกต่างระหว่าง A กับ a ไม่ได้ (ไม่สนใจว่าเป็นตัวพิมพ์ใหญ่หรือพิมพ์เล็ก, ignore case-sensitive)
2. ใช้ได้กับข้อมูลที่เป็นตัวหนังสือ (Text) เท่านั้น หรือถ้าพูดอีกนัยหนึ่งก็คือ ใช้กับข้อมูลที่เป็นตัวเลขไม่ได้

ข้อแรกพอเข้าใจ แต่งงข้อ 2 อ่ะ?

ขออธิบายเพิ่มแบบนี้ครับ

สมมติต้องการค้นหาเซลล์ที่มีตัวเลข 1 กับข้อมูลชุดนึง สูตรนี้จะนับเฉพาะข้อมูลที่เป็นตัวหนังสือเท่านั้น ข้อมูลที่เป็นตัวเลขจะไม่ถูกนับ

จากภาพด้านบน จะเห็นว่าผลลัพธ์ของ COUNTIF(A4:A15,”*”&D3&”*”) คือ 1

เพราะสูตรนี้มองว่าข้อมูลที่เข้าข่ายมีเพียงเซลล์เดียว คือ C2-1AB

นั่นแปลว่าเซลล์อื่นที่มี 1 เป็นส่วนประกอบ (21,345, 123, 42,187) จะไม่ถูกนับรวม

ถ้าต้องการนับเฉพาะเซลล์ที่เป็นตัวหนังสือ สูตรนี้ใช้ได้ ไม่มีปัญหา

แต่ถ้าต้องการนับเซลล์ที่เป็นตัวเลขด้วย สูตรนี้จะใช้ไม่ได้

งั้นทำยังไงดี?

นั่นจึงเป็นที่มาของวิธีที่ 2 ครับ ^__^

2 COUNT & SEARCH

คุณรู้จักฟังก์ชัน SEARCH ไหมครับ?

ไม่รู้จักอ่ะ ใช้ทำอะไรเหรอ?

ฟังก์ชัน SEARCH ใช้ค้นหาว่าในเซลล์นั้นมีคำที่เราต้องการหรือไม่

ถ้ามี จะส่งค่ากลับมาว่าคำที่ต้องการอยู่ลำดับที่เท่าไร (นับจากซ้ายไปขวา)

เช่น อยากรู้ว่า 89afw มีตัวอักษร a หรือไม่

ถ้าเขียนสูตรเป็น

=SEARCH(“a”,”89afw”)

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

มาทำความรู้จักฟังก์ชัน SEARCH เพิ่มเติมกันนิดนึงครับ ^__^

โครงสร้างคือ

SEARCH(find_text, within_text, [start_num])

find_text คือ คำที่ต้องการ (ตัวอักษรหรือตัวเลขก็ได้ กี่อักขระก็ได้)

within_text คือ เซลล์ที่ถูกค้นหา อาจเป็นเซลล์เดียวหรืออาจเป็นช่วงเซลล์ (Range) ก็ได้

(ถ้าใช้กับช่วงเซลล์ ถือเป็นสูตรอาร์เรย์ ต้องกด Ctrl+Shift+Enter แทนการกด Enter)

[start_num] ถือเป็นออปชั่น (ใส่หรือไม่ใส่ก็ได้) ใช้ในกรณีเซลล์มีคำที่ต้องการมากกว่าหนึ่งตำแหน่ง สามารถระบุตำแหน่งได้ว่าให้เริ่มค้นหาจากอักขระลำดับที่เท่าใด (นับจากซ้ายไปขวา)

(ถ้าไม่ระบุ start_num เอ็กเซลจะมองว่าให้เริ่มค้นหาจากอักขระตัวแรก (ซ้ายสุด) เสมอ)

เช่น คำว่า banana มี a 3 ตัว

ถ้าเขียนสูตรเป็น SEARCH(“a”,”banana”) คำตอบที่ได้คือ 2

เพราะถือว่าไม่ระบุ start_num ตำแหน่งของ a ที่ถูกส่งกลับมาคือ a ตัวแรก

ถ้าเขียนสูตรเป็น SEARCH(“a”,”banana”,3) คำตอบที่ได้คือ 4

เพราะระบุ start_num เป็น 3 แปลว่าเริ่มหาจากอักขระลำดับที่สาม (เคสนี้คือ n) ตำแหน่งของ a ที่ถูกส่งกลับมาคือ a ตัวที่สอง (ตำแหน่ง a ตัวแรกที่เป็นอักขระลำดับที่สองจะถูกข้ามไป)

ถ้าเขียนสูตรเป็น SEARCH(“a”,”banana”,5) คำตอบที่ได้คือ 6

เพราะระบุ start_num เป็น 4 ตำแหน่งของ a ที่ถูกส่งกลับมาคือ a ตัวที่สาม

นอกจากใช้กับเซลล์เดี่ยวๆแล้ว SEARCH ยังสามารถใช้กับช่วงเซลล์ได้ด้วย แต่ต้องกด Ctrl+Shift+Enter เพราะถือเป็นสูตรอาร์เรย์ (Array Formula)

จากเคสเดิม ถ้าเขียนสูตรเป็น

SEARCH(1,A4:A15)

จะได้ผลลัพธ์เป็น

{#VALUE!;#VALUE!;#VALUE!;2;#VALUE!;#VALUE!;4;#VALUE!;#VALUE!;1;#VALUE!;3}

นั่นแปลว่า SEARCH มองหาตัวเลข 1 จาก A4:A15 และส่งค่ากลับมาเป็นตำแหน่งของเลข 1 ในเซลล์นั้นๆ

หรือก็คือตำแหน่งของเลข 1 จาก 4 ข้อมูลนี้

• 21,345
• C2-1AB
• 123
• 42,187

ส่วนเซลล์ที่ไม่มีเลข 1 เป็นส่วนประกอบ จะส่งค่ากลับมาเป็น #VALUE!

ดังนั้น ถ้าต้องการนับเฉพาะเซลล์ที่มีเลข 1 เป็นส่วนประกอบ ก็เพียงครอบสูตรด้วยฟังก์ชัน COUNT

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

{=COUNT(SEARCH(1,A4:A15))}

(สูตรนี้เป็นสูตรอาร์เรย์ ต้องกด Ctrl+Shift+Enter แทนการกด Enter)

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

ถ้าไม่ต้องการระบุเลข 1 ในสูตร สามารถอ้างอิงจากเซลล์อื่น (เช่น D3) หรือเปลี่ยนสูตรเป็น

{=COUNT(SEARCH(D3,A4:A15))}

ได้คำตอบเป็น 4 ตามต้องการเป๊ะ!

เจ๋งมั๊ยล่ะ ^__^

เจ๋งมากอ่ะ สูตรนี้มีข้อเสียไหม?

ข้อเสียคือ ต้องกด Ctrl+Shift+Enter แทนการกด Enter ซึ่งมีโอกาสที่จะลืม (ถ้าไม่กด จะไม่ได้คำตอบที่ต้องการ)

นอกจากเรื่อง Ctrl+Shift+Enter แล้ว สูตรนี้ยังมีข้อเสียอีกข้อนึงคือ แยกความแตกต่างระหว่าง A กับ a ไม่ได้ (ignore case-sensitive)

นั่นคือ แยกความแตกต่างระหว่างตัวอักษรพิมพ์ใหญ่กับพิมพ์เล็กไม่ได้

จากภาพ ถ้าใช้สูตรนี้นับเฉพาะข้อมูลที่มีตัวอักษร a ผลลัพธ์ที่ได้คือ 4 เพราะสูตรจะนับรวมข้อมูลที่มีตัวอักษร A (C2-1AB) เข้าไปด้วย

แล้วถ้าต้องการนับเฉพาะเซลล์ที่มีตัวอักษร a อย่างเดียวล่ะ ทำยังไงดี?

นั่นจึงเป็นที่มีของวิธีที่ 3 ครับ ^__^

3 COUNT & FIND

ฟังก์ชัน SEARCH ไม่สนใจความแตกต่างของตัวอักษรพิมพ์ใหญ่หรือพิมพ์เล็ก

สำหรับ SEARCH แล้ว A กับ a คืออักษรตัวเดียวกัน

แต่ถ้าเราสนล่ะ?

คำตอบคือ ต้องใช้ฟังก์ชัน FIND แทนครับ ^__^

ฟังก์ชัน FIND และ SEARCH ใช้หาลำดับที่ของคำที่ต้องการเหมือนกัน มีโครงสร้างเหมือนกัน

ความแตกต่างคือ FIND สนใจความแตกต่างของตัวอักษรพิมพ์ใหญ่และพิมพ์เล็ก (case-sensitive)

จากภาพด้านบนจะพบว่า ถ้าใช้ FIND จะหา a ใน C2-1AB ไม่พบ (ในขณะที่ SEARCH หาพบ)

จากสูตรเดิมที่พบว่า SEARCH ไม่สามารถแยกความแตกต่างของ A กับ a ได้ ถ้าเปลี่ยนเป็น FIND จะแยกความแตกต่างได้ หรือเขียนสูตรเป็น

{=COUNT(FIND(D3,A4:A15))}

ผลลัพธ์ที่ได้คือ 3 ตามต้องการ

(สูตรนี้ถือเป็นสูตรอาร์เรย์ ต้องกด Ctrl+Shift+Enter)

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

อั๊ย..ย่ะ !!

แล้ววิธีนี้มีข้อเสียไหม?

ข้อเสียยังคงเหมือนกับวิธีที่ 2 คือ ต้องกด Ctrl+Shift+Enter (แทนการกด Enter) ซึ่งมีโอกาสลืม

งั้นทำยังไงดี?

คำตอบคือวิธีที่ 4 ครับ ^__^

4 SUMPRODUCT & ISNUMBER 

ในบางเคส (เช่นเคสนี้) ถ้าไม่ต้องการกด Ctrl+Shift+Enter สามารถใช้ฟังก์ชัน SUMPRODUCT ช่วยได้

(ถ้าเป็นเคสที่ใช้กับ IF จะไม่สามารถใช้ SUMPRODUCT ช่วยได้)

แต่ไม่สามารถครอบ FIND ด้วย SUMPRODUCT แบบตรงๆ

หรือจะเขียนสูตรเป็น SUMPRODUCT((FIND(“a”,A4:A15)) ไม่ได้

ทำไมล่ะ?

เพราะจากสูตร

FIND(“a”,A4:A15)

ผลลัพธ์ที่ได้คือ

{1;#VALUE!;#VALUE!;#VALUE!;3;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;5;#VALUE!}

จะพบว่ามี #VALUE! เต็มไปหมด

ข้อจำกัดหนึ่งของ SUMPRODUCT คือ

SUMPRODUCT ใช้กับข้อมูลที่เป็นความผิดพลาด (Error) ไม่ได้

งั้นทำยังไงดี?

ต้องแปลงความผิดพลาดให้เป็น TRUE, FALSE ครับ

ทำยังไงอ่ะ?

โดยใช้ฟังก์ชัน ISNUMBER ช่วยนั่นเอง

วิธีคือ ครอบ FIND ด้วย ISNUMBER หรือเขียนสูตรเป็น

=ISNUMBER(FIND(“a”,A4:A15))

ผลลัพธ์ที่ได้คือ

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

จะเห็นว่า #VALUE! ถูกเปลี่ยนเป็น FALSE และตัวเลขถูกเปลี่ยน TRUE

คราวนี้สูตรของเราก็ไม่มีค่าความผิดพลาด (Error) แล้ว ^__^

งั้นครอบสูตรนี้ด้วย SUMPRODUCT ได้เลยใช่ไหม?

น่าเสียดายที่ยังไม่ได้ครับ

อ้าว! ทำไมล่ะ?

เพราะ SUMPRODUCT ใช้ได้กับข้อมูลที่เป็นตัวเลขเท่านั้น

ใช้กับ TRUE, FALSE ไม่ได้

งั้นทำยังไงดีล่ะ?

ต้องเปลี่ยน TRUE, FALSE ให้กลายเป็นตัวเลข โดยเปลี่ยน TRUE เป็น 1 และเปลี่ยน FALSE เป็น 0

เปลี่ยนยังไงอ่ะ?

ในทางตรรกศาสตร์แล้ว TRUE มีค่าเทียบเท่า 1 ส่วน FALSE มีค่าเทียบเท่า 0

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

ซึ่งทำได้หลายวิธี เช่น

• +0
• -0
• *1 (คูณด้วย 1)
• /1 (หารด้วย 1)

แต่วิธีที่นิยมและใช้ง่ายที่สุดคือ ใส่เครื่องหมาย — (เครื่องหมายลบสองอันติดกัน) ไว้ด้านหน้าครับ ^__^

ด้วยวิธีนี้ เสมือนใส่เครื่องหมายลบสองอันด้านหน้า ลบกับลบเจอกันกลายเป็นบวก แถมยังเปลี่ยน TRUE ให้กลายเป็น 1 และ FALSE กลายเป็น 0 ด้วย

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

=–ISNUMBER(FIND(“a”,A4:A15))

เมื่อครอบด้วยฟังก์ชัน SUMPRODUCT สูตรจึงกลายเป็น

=SUMPRODUCT(–ISNUMBER(FIND(“a”,A4:A15)))

ถ้าไม่ต้องการระบุตัวอักษร a ในสูตร ก็สามารถอ้างอิงจากเซลล์ใดๆ (เช่น D3) ได้เป็น

=SUMPRODUCT(–ISNUMBER(FIND(D3,A4:A15)))

(ไม่ต้องกด Ctrl+Shift+Enter)

สูตรนี้ใช้ฟังก์ชัน FIND จึงสนใจว่าเป็นตัวอักษรพิมพ์ใหญ่หรือพิมพ์เล็ก (case-sensitive)

ถ้าอยากใช้สูตรแบบเดียวกัน แต่ไม่สนใจว่าเป็นตัวอักษรพิมพ์ใหญ่หรือพิมพ์เล็ก ก็เพียงปรับจากฟังก์ชัน FIND เป็น SEARCH หรือเขียนสูตรเป็น

=SUMPRODUCT(–ISNUMBER(SEARCH(D3,A4:A15)))

จากภาพจะเห็นว่า ถ้าใช้สูตร

=SUMPRODUCT(–ISNUMBER(FIND(D3,A4:A15)))

จะได้ผลลัพธ์เป็น 3 เพราะ C2-1AB ไม่ถูกนับไปด้วย (A เป็นตัวพิมพ์ใหญ่)

แต่ถ้าใช้สูตร

=SUMPRODUCT(–ISNUMBER(SEARCH(D3,A4:A15)))

จะได้ผลลัพธ์เป็น 4 เพราะ SEARCH สนใจแค่ว่าเป็นตัวอักษร a จะเป็นตัวพิมพ์ใหญ่ (A) หรือพิมพ์เล็ก (a) ก็ได้ทั้งนั้น

สูตรนี้เท่ใช่ไหมล่ะ ^__^

.

งั้นแปลว่าวิธีที่ 4 ดีสุดใช่ไหม?

สรุปแบบนั้นไม่ได้ครับ

ทำไมล่ะ?

เพราะถ้าเคสที่เจอไม่สนใจเรื่องตัวพิมพ์ใหญ่หรือพิมพ์เล็ก (ignore case-sensitive) และข้อมูลทั้งหมดเป็นตัวหนังสือ วิธีที่ดีที่สุดคือวิธีที่ 1

(ในชีวิตจริงเคสเป็นแบบนี้มากที่สุด)

วิธีที่ 1 เขียนสูตรง่าย (COUNTIF(A4:A15,”*”&D3&”*”)) แถมไม่ต้องกด Ctrl+Shift+Enter ด้วย

แต่ถ้าเคสที่เจอเป็นการค้นหาตัวเลข และข้อมูลทั้งหมดเป็นตัวเลข (ไม่เป็นตัวหนังสือ) วิธีที่สอง {=COUNT(SEARCH(D3,A4:A15))} คือวิธีที่ดีกว่า เพียงแต่มีข้อเสียคือต้องกด Ctrl+Shift+Enter

แต่ถ้าเคสที่เจอสนใจเรื่องตัวอักษรพิมพ์ใหญ่หรือพิมพ์เล็ก (case-sensitive) วิธีที่สาม {=COUNT(FIND(D3,A4:A15))} คือวิธีที่ดีกว่า แต่มีข้อเสียคือต้องกด Ctrl+Shift+Enter

ถ้ากลัวลืมกด Ctrl+Shift+Enter และเคสนั้นสนใจตัวพิมพ์ใหญ่พิมพ์เล็ก วิธีที่ 4 (=SUMPRODUCT(–ISNUMBER(FIND(D3,A4:A15)))) อาจเป็นวิธีที่ดีกว่า แต่มีข้อเสียคือสูตรอาจดูซับซ้อนเมื่อเทียบกับวิธีอื่น

ถ้าเราเข้าใจสูตร สูตรจะดูซับซ้อนหรือไม่ ไม่ใช่ปัญหา

แต่ถ้าเราไม่เข้าใจสูตร ต่อให้สูตรง่ายกว่านี้ ก็เป็นปัญหาได้

ในมุมมองของผมนั้น วิธีที่ดีที่สุดคือ วิธีที่เราเข้าใจและได้คำตอบที่ถูกต้อง จะเป็นวิธีที่ 1, 2, 3 หรือ 4 ก็ได้ทั้งนั้น

ขอให้สูตรนั้นหาคำตอบได้ถูกต้อง และเป็นสูตรที่เราเข้าใจก็เพียงพอแล้วครับ ^__^

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

COUNTOnlySpecificText_180228

.

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

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

อ้อ! ตอนนี้ผมมีคอร์สออนไลน์กับทาง SkillLane แล้ว ดูรายละเอียดได้จากลิงค์นี้ครับ https://www.skilllane.com/courses/excel-in-essence

อ้างอิงข้อมูลจาก
https://exceljet.net/formula/count-cells-that-contain-specific-text
http://www.contextures.com/xlFunctions04.html