“ลองเปลี่ยน – เป็น ก แล้วใช้ PROPER จากนั้นเปลี่ยน ก เป็น – ดีไหมคะ?”
“ยังไงนะครับ?” ผมตามเธอไม่ทัน
“แบบนี้ค่ะ” แล้วเธอก็พิมพ์สูตร
“เฮ้ย ! แบบนี้ได้ด้วยเหรอ !!” ผมตะโกนดังลั่น

ผมกำลังฮัมเพลงเดินเข้าออฟฟิศอย่างอารมณ์ดี จู่ ๆ ตั้มก็พรวดเข้ามา
“พี่บิว มาที่โต๊ะผมตอนนี้เลยได้ไหม”

สิ่งที่เห็นบนหน้าจอคอมพิวเตอร์คือ รายชื่อลูกค้าที่สะกดเป็นภาษาอังกฤษประมาณ 50,000 บรรทัด

“คือผมอยากเปลี่ยนรูปแบบชื่อ-นามสกุล โดยให้ขึ้นต้นด้วยตัวพิมพ์ใหญ่ ที่เหลือเป็นตัวพิมพ์เล็ก ทำไงดีพี่?”
“ใช้ฟังก์ชัน PROPER ไง”
“ยังไงนะพี่”
“แบบนี้ไง”

=PROPER(A4)

“เฮ้ย ! แค่นี้เองเหรอ” ตั้มตะโกนเสียงดังแปดหลอด

PROPER คือฟังก์ชันที่ถูกออกแบบมาเพื่อการนี้โดยเฉพาะ โครงสร้างคือ

PROPER( Text )

พูดง่ายๆคือเอา PROPER ไปครอบ จบ !

“เอ.. เหมือนมีปัญหานิดนึงพี่”
“ยังไงหรือตั้ม”
“คือส่วนใหญ่โอเคหมด ยกเว้นคนที่ชื่อหรือนามสกุลมีเครื่องหมาย – “ (ขีดกลาง , Hyphen)
“ถ้ามีเครื่องหมาย – อักษรตัวแรกหลังเครื่องหมายกลายเป็นตัวพิมพ์ใหญ่หมดเลยครับ”

การสะกดภาษาไทยเป็นภาษาอังกฤษ บางครั้งอาจอ่านได้หลายแบบ
บางครั้งมีตัวอักษรที่เป็นสระ (a, e , i, o, u) ติดกันหลายตัวทำให้อ่านยาก
ทางแก้คือ ใส่เครื่องหมาย – คั่น

แต่ฟังก์ชัน PROPER ดันไม่เห็นด้วย !
มันมองว่าตัวอักษรหลัง – ต้องเป็นตัวพิมพ์ใหญ่

ฮ่วย !!

แล้วจะทำยังไงดี?

เคสนี้ไม่ง่ายเลย
ถ้าเขียนสูตร ก็ต้องเช็คก่อนว่าชื่อนั้นมี – ไหม (ใช้ IF, ISERROR, FIND)

ถ้าไม่มี ก็ใช้ PROPER ตรงๆ

ถ้ามี
ต้องเปลี่ยนตัวอักษรทั้งหมดให้เป็นตัวพิมพ์เล็กก่อน (ใช้ LOWER)
ต้องเปลี่ยนอักษรตัวแรกให้เป็นตัวพิมพ์ใหญ่ (ใช้ LEFT, UPPER)
ต้องเปลี่ยนอักษรตัวแรกที่อยู่หลังช่องว่าง (space) ให้เป็นตัวพิมพ์ใหญ่ (ใช้ FIND, MID, UPPER)

แค่นี้สูตรก็ยากแล้ว

ปัญหาคือ
แล้วถ้าบางคนมีชื่อกลางล่ะ?
ผู้หญิงที่แต่งงานแล้วบางคนใช้นามสกุลเดิมเป็นชื่อกลาง

แล้วถ้าชื่อบางคนมี – มากกว่าหนึ่งตำแหน่งล่ะ?
เช่น On-uma Yam-iam
(อรอุมา แย้มเอี่ยม)

IF ซ้อนกันพึ่บพั่บ สูตรยาวเป็นหางว่าวจุฬา

ไม่เวิร์ก

ทำไงดี? ….

“คุยอะไรกันคะ หน้าเครียดเชียว” น้องโบเข้ามาทัก
“กำลังหาทางเขียนสูตรครับพี่โบ” แล้วตั้มก็อธิบายให้น้องโบฟัง

น้องโบคิดแป๊ปนึงแล้วพูดว่า
“ลองเปลี่ยน – เป็น ก แล้วใช้ PROPER จากนั้นเปลี่ยน ก เป็น – ดีไหมคะ?”
“ยังไงนะครับ?” ผมตามเธอไม่ทัน
“แบบนี้ค่ะ” ว่าแล้วน้องโบก็พิมพ์สูตร

=SUBSTITUTE( PROPER( SUBSTITUTE( A4,”-“,”ก” ) ),”ก”,”-” )

“เฮ้ย ! แบบนี้ได้ด้วยเหรอ !!” ผมตะโกนดังลั่น

ตอนแรกน้องโบคิดจะใช้ IF เหมือนกัน แต่พบว่าไม่เวิร์ก
ดังนั้นจึงใช้ตรรกะนี้ไม่ได้ ต้องใช้ตรรกะใหม่
ตรรกะที่น้องโบใช้นั้นแหวกแนวมาก
เค้ามองเป็น 3 สเต็ป แบบนี้ครับ

Step1: แทนที่เครื่องหมาย – ด้วย ก
เพื่อให้สามารถใช้ฟังก์ชัน PROPER ได้

Step2: ใช้ฟังก์ชัน PROPER
เพื่อเปลี่ยนอักษรตัวแรกของชื่อและนามสกุลให้เป็นตัวพิมพ์ใหญ่

Step3: เปลี่ยน ก เป็น –
เพื่อให้กลับมาเป็นเหมือนเดิม

Step1: แทนที่เครื่องหมาย – ด้วย ก

เพื่อให้ใช้ฟังก์ชัน PROPER ได้ จึงต้องกำจัดเครื่องหมาย – แบบ “ชั่วคราว” ก่อน
ซึ่งการกำจัดแบบ “ชั่วคราว” ก็คือแทนที่ – ด้วยอะไรซักอย่างที่ไม่ไช่ A-Z, ตัวเลข, อักขระพิเศษ

งั้นจะแทนที่ด้วยอะไรล่ะ?

ด้วย ก เพราะ ก ไม่มีในภาษาอังกฤษ !

จริง ๆ แล้ว แทนที่ด้วยตัวอักษรไทยอะไรก็ได้ จะเป็น ก-ฮ ได้ทั้งหมด
ใช้ไม้ยมก (ๆ) ยังได้เลย
แต่ใช้เลขไทยไม่ได้นะครับ
ใช้ ก นี่แหละ เข้าใจง่ายดี ^__^

แล้วจะแทนที่ – ด้วย ก ได้ยังไง?

ด้วยฟังก์ชัน SUBSTITUTE ครับ

ฟังก์ชัน SUBSTITUTE มีความหมายตามชื่อเลย คือใช้แทนที่ ข้อความเก่า ด้วย ข้อความใหม่

คำว่า “ข้อความ” ในที่นี้ เป็นได้ทั้งตัวอักษรตัวเดียว หรือหลายตัวก็ได้

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

SUBSTITUTE( text, old_text, new_text, [instance_num] )

text คือ ข้อความหรือเซลล์ที่ต้องการเปลี่ยน เช่น เคสนี้คือ เซลล์ A4

old_text คือ คือ ข้อความ (หรืออักขระ) ที่ต้องถูกแทนที่ เช่น เคสนี้คือ ขีดกลาง (“-“)

new_text คือ ข้อความ (หรืออักขระ) ที่นำมาแทนที่ เช่น เคสนี้คือ ก (“ก”)

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

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

=SUBSTITUTE(A4,”-“,”ก”)

ผลลัพธ์คือ

Step2: ใช้ฟังก์ชัน PROPER

พอแทนที่ – ด้วย ก ได้แล้ว ก็ใช้ฟังก์ชัน PROPER เพื่อเปลี่ยนให้ตัวอักษรตัวแรกเป็นตัวพิมพ์ใหญ่ และที่เหลือเป็นตัวพิมพ์เล็ก

หรือนำฟังก์ชัน PROPER ไปครอบ SUBSTITUTE แบบนี้

=PROPER( SUBSTITUTE(A4,”-“,”ก”) )

คราวนี้ไม่มีปัญหา เพราะ PROPER มอง ก เป็นอักษรตัวนึง อักษรหลัง ก จึงเป็นตัวพิมพ์เล็ก

จะพบว่าอักษรตัวแรกเป็นตัวพิมพ์ใหญ่ ที่เหลือเป็นตัวพิมพ์เล็ก เหลือแค่ ก ที่เป็น ‘สิ่งแปลกปลอม’

Step3: เปลี่ยน ก เป็น –

จริงๆแล้วตำแหน่งของ ก คือตำแหน่งของ – นั่นเอง

ถ้าเปลี่ยนจาก ก เป็น – ก็จบ

แล้วจะเปลี่ยนยังไงล่ะ?

ใช่แล้วครับ
SUBSTITUTE นั่นเอง !

หรือนำ SUBSTITUTE ไปครอบสูตรเดิม เป็น

=SUBSTITUTE( PROPER( SUBSTITUTE( A4,”-“,”ก” ) ),“ก”,”-” )

ว้าว ! โจทย์ข้อนี้สนุกดีใช่ไหมครับ ^_^

จากตรรกะเดิมที่ต้องใช้ IF หลายชั้นร่วมกับ ISERROR, FIND, LEFT, MID, UPPER, LOWER ซึ่งสูตรจะซับซ้อนและยาวไม่น้อยกว่า 3 บรรทัด

พอใช้ SUBSTITUTE บรรทัดเดียว จบ !

สูตรว่าเจ๋งแล้ว ที่เจ๋งกว่าคือ ตรรกะ

ในชีวิตจริง ถ้าเจอโจทย์ที่ต้องใช้ IF ซ้อนกันหลายชั้น ทางแก้คือ ให้ใช้วิธีอื่น
ถ้ามองที่มุมของ IF มุมมองอื่นจะโดนบัง
และมุมนั้นอาจเป็น SUBSTITUTE ก็เป็นได้

อ้อ ! ตรรกะนี้ผมไม่ได้คิดเองนะครับ คนคิดคือคุณ Bo Rydobon เจ้าของเฟซบุ๊กแฟนเพจ Excel Wizard นั่นเอง (บทความนี้ based on true story)

ขอบคุณ Excel Wizard ที่แชร์เทคนิคดี ๆ วิซาร์ด วิเศษ จริงๆ ^__^

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

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

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