“อยากนับเครื่องหมายบวก (+) ในสูตร Excel ต้องเขียนสูตรยังไงครับ?” จู่ๆกล่องข้อความก็แสดงคำถามนี้
เช่น สูตรในเซลล์ B3 คือ =20+10+35+20+20+20+50+30+25+20+65+10+20+45+30+15
อยากรู้ว่ามีเครื่องหมาย + ทั้งหมดกี่ตัว?
“ไม่น่าจะยากนะ” ผมคิดในใจ พลางนึกถึงบทความเรื่อง สูตร Excel นับคำซ้ำในประโยค ที่เคยเขียนไว้เมื่อ 3 ปีก่อน
เลยลองพิมพ์สูตร
= LEN(B3) – LEN(SUBSTITUTE(B3,”+”,””))
= 0
เฮ้ย!!

ได้ 0 เฉยเลย!
ทำไม?
.
.
เพราะสูตรนี้มอง B3 เป็นผลลัพธ์จากสูตร หรือมองเป็น 435
ไม่ใช่ 20+10+35+20+20+20+50+30+25+20+65+10+20+45+30+15
พอเขียนสูตรนับเครื่องหมาย + จาก 435 ก็เลยได้ 0

ถ้าจะนับเครื่องหมาย + ต้องหาทางเปลี่ยนสูตรให้เป็นตัวอักษร (Text)

แล้วจะเปลี่ยนสูตรให้เป็นตัวอักษรได้ยังไงล่ะ?
ติ๊กต็อก
ติ๊กต็อก ..
.
.
คำตอบคือฟังก์ชัน FORMULATEXT ^__^

จากเคสนี้ ถ้าเขียนสูตรเป็น
=FORMULATEXT(B3)
จะได้ผลลัพธ์
=20+10+35+20+20+20+50+30+25+20+65+10+20+45+30+15

นั่นคือ FORMULATEXT ได้เปลี่ยนสูตรในเซลล์ B3 ให้เป็นตัวอักษร (Text)
พอเป็นตัวอักษรแล้วก็ใช้กับฟังก์ชัน LEN หรือ SUBSTITUTE ได้เลย

จากสูตรเดิม
= LEN(B3) – LEN( SUBSTITUTE(B3,”+”,””) )
ก็แค่เปลี่ยน ฺB3 ให้เป็น FORMULATEXT(B3)
หรือเขียนสูตรเป็น

= LEN( FORMULATEXT(B3) ) – LEN( SUBSTITUTE( FORMULATEXT(B3),”+”,”” ) )
= 48 – 33
= 15

อ้อ! ฟังก์ชัน FORMULATEXT ใช้ได้ตั้งแต่ Excel 2013 หรือได้เฉพาะ Excel 2013/ 2016/ 2019 และ Office 365
หรือแปลว่า Excel 2003/ 2007/ 2010 หมดสิทธิ์

“แล้วถ้าใช้ Excel 2010 แต่ต้องการผลลัพธ์เหมือน FORMULATEXT ล่ะ?”
ถ้าไม่มีฟังก์ชัน FORMULATEXT อาจใช้ GetFormula ซึ่งเป็นฟังก์ชันของ XL4 Macro Function ได้
แต่การใช้ GetFormula อาจไม่สะดวกเหมือน FORMULATEXT
อ่านรายละเอียดได้จากลิงค์นี้ครับ
https://www.excelforum.com/excel-formulas-and-functions/1163182-formulatext-function-equivalent-for-2007-a.html

พอหาคำตอบได้แล้ว มีคำถามใหม่ผุดขึ้นในหัวผม
ทำไมถึงมีคำถามนี้?
โจทย์แบบนี้เกิดขึ้นกับงานแบบไหน?
เลยถามกลับไปยังเจ้าของคำถาม ได้คำตอบว่าเกิดขึ้นในงานขาย

“งานขายเนี่ยนะ?” ผมคิดในใจ
พอถามต่อถึงได้รู้ว่า เคสนี้เกิดจากการบันทึกการขายของร้านค้า
คล้ายกับว่าทุกครั้งที่ขายจะพิมพ์ตัวเลขเข้าไปในเซลล์ B3
ถ้ามีลูกค้ามาซื้อเพิ่ม ก็พิมพ์ + ต่อไปเรื่อยๆ
เลยกลายเป็น
=20+10+35+20+20+20+50+30+25+20+65+10+20+45+30+15
ถ้าอยากรู้ว่าวันนั้นมีคนซื้อของทั้งหมดกี่คน ก็นับว่ามีเครื่องหมายบวกทั้งหมดกี่อัน (แล้วบวกด้วย 1)
ผมเลยถึงบางอ้อ …

ในมุมมองของผม เคสนี้ไม่ควรแก้ปัญหาด้วยสูตร
แต่ควรแก้ปัญหาด้วยวิธีการเก็บข้อมูล

นั่นคือ ควรบันทึกการขายหนึ่งครั้งต่อหนึ่งเซลล์ หรือบันทึกในรูปแบบนี้

ถ้าบันทึกการขายแบบนี้ พอจะนับว่ามีลูกค้ากี่คน ก็เขียนสูตรได้ง่ายๆเลย เช่น
=COUNT(B5:B1000000)
ไม่จำเป็นต้องมี FORMULATEXT, LEN, SUBSTITUTE หรือฟังก์ชันซับซ้อนอะไรเลย

หรือถ้าให้ดี ควรบันทึกด้วยว่าขายวันไหน ขายสินค้าอะไร และมี running number ของแต่ละ transaction
แบบนี้

ถ้าให้ดียิ่งขึ้น ควรปรับฟอร์แมตให้เป็น Table (กด Ctrl+T)
การทำเป็น Table สามารถนับจำนวน transaction ได้ง่ายๆ โดยคลิกที่ Design/ Total Row แล้วเลือกเป็น Count (ไม่ต้องเขียนสูตร)

ข้อดีของการทำเป็น Table คือช่วงข้อมูลจะเป็น dynamic (ขยายต่อได้เรื่อยๆ) สามารถนำไปใช้ต่อได้ง่าย
เช่น ใช้เป็นฐานข้อมูลของ Pivot Table เพื่อสรุปข้อมูล

หรือถ้าต้องการเพิ่มมุมมองการวิเคราะห์ ก็สามารถใช้ Power Pivot เพื่อเขียนสูตรได้ง่ายและวิเคราะห์ได้หลากหลาย
(เช่น หารายชื่อลูกค้า Top3 ที่ซื้อสินค้าแต่ละกลุ่ม, หาจำนวนลูกค้าที่มีการซื้อมากกว่าหนึ่งแสนบาท, หาจำนวนลูกค้าที่มีการซื้ออย่างน้อยหนึ่งครั้งในสามเดือนล่าสุด)

จากประสบการณ์คลุกคลีกับ Excel มายาวนาน ผมพบว่าปัญหากว่า 80% เกิดจากวิธีการเก็บข้อมูลที่ไม่ถูกต้อง
ถ้าลองปรับวิธีการเก็บข้อมูลนิดนึง จะพบว่าจริงๆแล้ว Excel น่าสนุก มีอะไรให้ศึกษา มีอะไรให้ค้นหาอีกมากมาย
แถมบอกลาปัญหาเดิม แล้วเดินจากมาอย่างหล่อๆได้เลย

จริงไหมครับ ^__^

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

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

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