การแกะสูตรการหาจำนวนอักขระจากข้อความ

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

ยกตัวอย่างเช่น เราจะหาว่าตัว W ปรากฏกี่ครั้งในข้อความด้านล่างนี้ซึ่งอยู่ที่เซลล์ A1

When Where What Who How

ซึ่งหากนับด้วยตาเราก็พบว่า 5 อักขระ ใครที่คิดว่าตนเองมีความชำนาญในการใช้ฟังก์ชั่น ลองคิดหาวิธีก่อนที่ผมจะเฉลยดูครับ

ฟังก์ชั่นที่ใช้ในการหาจำนวนครั้งที่ปรากฏของ W คือที่ B1 คีย์

=LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"W",""))

หลักการคิดคือนำจำนวนอักขระทั้งหมดหักด้วยจำนวนอักขระคงเหลือหลังจากลบอักขระที่ต้องการหาออกไปแล้ว

การแกะสูตร

จากสูตร =LEN(A1)-LEN(SUBSTITUTE(UPPER(A1),"W",""))

1. Len(A1) เป็นการนับว่าอักขระทั้งหมดมีกี่อักขระ คำตอบคือ 23
2. Upper(A1) เป็นการแปลงให้เซลล์ A1 เป็นตัวใหญ่ทั้งหมด การแกะสูตรให้นำเมาส์ไปคลุม Upper(A1) ที่ Formula bar แล้วกดแป้น F9 จะได้เป็น "WHAT WHERE WHEN WHO HOW"
3. จากข้อ 2 จะได้เป็น Substitute("WHAT WHERE WHEN WHO HOW","W","") ซึ่งหมายความว่าให้เปลี่ยน W ที่ปรากฎในข้อความให้เป็นค่าว่าง

หากลากเมาส์คลุม Substitute("WHAT WHERE WHEN WHO HOW","W","") แล้วกดแป้น F9 จะได้เป็น

LEN("HAT HERE HEN HO HO")

จะเห็นว่าตัว W หายไป ผลลัพธ์จากฟังก์ชั่น LEN("HAT HERE HEN HO HO") จะได้ค่า 18

เมื่อนำ 23 จากข้อ 1 มาหักจาก 18 ตามข้อ 3 จะได้ 5 ซึ่งเป็นคำตอบตรงกับที่นับด้วยตา

คำถาม ทำไมต้องใช้ฟังก์ชั่น Upper() มาช่วย

คำตอบ เนื่องจากโจทย์นี้มีทั้ง W และ w สองตัวนี้มีค่าไม่เทียบเท่ากันในฟังก์ชั่น Substitute จึงจำเป็นต้องแปลงให้เป็นตัวเล็กหรือตัวใหญ่เสียก่อน ซึ่งผมแปลงให้เป็นตัวใหญ่ด้วยฟังก์ชั่น Upper