“อยากนับเฉพาะเซลล์ที่มีตัวอักษร 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 ข้อคือ
- แยกความแตกต่างระหว่าง A กับ a ไม่ได้ (ไม่สนใจว่าเป็นตัวพิมพ์ใหญ่หรือพิมพ์เล็ก, ignore case-sensitive)
- ใช้ได้กับข้อมูลที่เป็นตัวหนังสือ (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 ก็ได้ทั้งนั้น
ขอให้สูตรนั้นหาคำตอบได้ถูกต้อง และเป็นสูตรที่เราเข้าใจก็เพียงพอแล้วครับ ^__^
ถ้าสนใจไฟล์ตัวอย่างของบทความนี้ สามารถดาวน์โหลดเพื่อศึกษาเพิ่มเติมได้เลยครับ ^__^
.
หากคุณชอบบทความแนวนี้ สามารถอัพเดตบทความใหม่ๆโดยคลิก 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
Reblogged this on paohue.
Great stuff.