เปลี่ยนตัวเลขเป็นตัวอักษร ตอนที่เราพิมพ์ตัวเลข เช่น 1,200 บาท จะให้เป็นตัวอักษรอย่างเช่น หนึ่งพันสองร้อยบาทถ้วน ใน Excel เราใช้ bathtext ได้สบาย ง่าย ๆ ใช่มั้ย แต่ถ้าจะให้เป็นภาษาอังกฤษ คือ One Thousand and two hundred baht แบบนี้จะทำอย่างไร? ในเมื่อมันไม่มีฟังก์ชัน dollartext
ถ้าเป็นเงินบาท มันมีฟังก์ชัน BahtText ที่เราแค่เอาไปใช้งานได้เลย แบบนี้
แต่มันเป็นภาษาไทย และมีแต่ภาษาไทยเท่านั้น ไม่มีภาษาอังกฤษ หรือ จีน หรือ อินเดีย หรือ ญี่ปุ่น หรือ ฟินแลนด์
เอ๊ะ หรือว่ามี? อันนี้พยายามหา dollartext หรืออะไรอื่นแล้วไม่เจอนะ
อันนี้ก็งง ๆ เหมือนว่าเป็นอภิสิทธิ์ของเราชาวไทยที่มีฟังก์ชันซึ่งไม่น่าจะมีประโยชน์กับคนชาติอื่นเท่าไหร่
แล้วฝรั่งเขาไม่ใช้อะไรแบบนี้เลยหรืออย่างไร? ก็เลยค้นกูเกิลดู พบว่าในเว็บไซต์ ไมโครซอฟต์ ได้เชียน VBA เพื่อเปลี่ยนตัวเลขเป็นตัวอักษรไว้แล้ว ย้าฮู้ วู้ปี้ กริ๊บกิ้ววววววววววววว
ซึ่งในเว็บไมโครซอฟต์ ตามลิงก์นี้ https://support.microsoft.com/en-gb/office/convert-numbers-into-words-a0d166fb-e1ea-4090-95c8-69442cd55d98 ได้ให้สูตรเอาไว้
เราก็ copy ไปใช้เลยจ้ะ
ขออนุญาตไม่ copy มาให้ดูตรงนี้ เพราะได้ลองแล้วเจอบั๊กแฮะ
อันนี้แปลกใจว่าเป็นเพราะอะไร ดูแล้วก็ครอบคลุมเวอร์ชันที่ใช้อยู่ (ด้านบนจะเขียนว่า Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010)
ในเมื่อมีบั๊ก เราก็ต้องแก้บั๊กกัน
Create custom functions
เราอาจจะคุ้นเคยกับการใช้ฟังก์ชันต่าง ๆ ใน Excel อย่างเช่น xlookup sumif หรืออะไรพวกนี้ ความจริง ฟังก์ชันเหล่านี้ก็คือการเขียนสูตรยาว ๆ แล้วเก็บเป็นโมดูลให้เราเรียกใช้ง่าย ๆ ซึ่ง เราก็สามารถเขียนฟังก์ชันขึ้นมาเองก็ได้
วิธีคือ กดเข้าไปที่แท็บ Developer แล้วไปที่ Visual Basic แล้วกด Insert เลือก Module ขึ้นมา
(หรือกด Alt + F11 ก็ได้ ง่าย ๆ เร็วดี)
หมายเหตุ หากว่าท่านไม่มีแท็บ Developer ให้ไปที่แท็บ File เลือก Options แล้วเลือก Customize Ribbon.
หน้าต่าง Customize the Ribbon ให้ดูที่ Main หาคำว่า Developer ติ๊กถูก คลิกโอเค
—–
พอเปิด Visual Basic Editor ขึ้นมาแล้ว ก็พิมพ์สูตรที่เราต้องการลงไป
ข้อเสียคือ สิ่งที่เราเขียนเพิ่มเติมนี้ เป็น Macro ถ้าเราจะบันทึกไฟล์ Excel เราต้องบันทึกเป็น Excel Macro-Enabled Workbook หรือ .xlsm นะจ๊ะ ถ้าเราบันทึกเป็น xlsx จะไม่เก็บ VBA ที่เราเขียน
การใช้งาน
วิธีการใช้งานก็เหมือนกับฟังก์ชันทั่วไป คือ เรามาใส่ = ตามด้วยชื่อฟังก์ชัน
เช่น = SpellNumber (D3)
ซึ่งพอกดแล้ว อ้าว error ต้องไป debug
เปลี่ยนตัวเลขเป็นตัวอักษร ในโค้ดของไมโครซอฟต์มีอะไรบ้าง?
ในฟังก์ชันนี้ (ซึ่งไมโครซอฟต์ใช้ชื่อฟังก์ชัน SpellNumber โดยในช่วงแรกจะมีการจัดการกับทศนิยม คือ ต้องกำหนดก่อนว่า ตรงที่เป็นทศนิยม จะเป็น Cent หรือ Cents กับตรงตัวจำนวนเต็มจะเป็น dollar หรือ dollars โดยถ้าเป็น 0 จะเรียกว่า no cent หรือ no dollar
และจะมี ฟังก์ชันย่อยอีกคือ
Function GetHundreds
Function GetTens
Function GetDigit
ศึกษา สูตรเปลี่ยนตัวเลขเป็นตัวอักษร ที่เขาให้มา ไล่ดูว่าวิธีการคิดของเขาจะมีอะไรบ้าง เวลาที่ Excel ส่งไปหน้า Visual Basic Editor นี่ จะไปไฮไลต์ตรงที่มีปัญหาให้ทันที โดยเราสามารถกดปุ่มเพลย์ (ที่เป็นสามเหลี่ยมอยู่ตรง toolbar) หรือจะกด F5 ก็ได้ เมื่อได้ลองกด และเช็คเส้นทางแล้ว ก็เลยคิดว่าบางอย่างน่าจะบายพาสต์ไปได้ อย่างเช่นตรงที่แรกที่มีปัญหา นั้น ชี้ไปที่โค้ดซึ่งมีการสร้าง temp ขึ้นมาก่อน
Function GetDigit
GetDigit เป็นแค่การกำหนดว่า 1 = One อะไรพวกนี้ ไม่มีอะไรซับซ้อน
จะเป็นการเปลี่ยนตัวเลขเป็นตัวอักษร นั่นคือ
1 = One
2 = Two
3 = Three
4 = Four
5 = Five
6 = Six
7 = Seven
8 = Eight
9 = Nine
Function GetTens
ฟังก์ชันต่อมา GetTens จะเป็นการเปลี่ยนตัวเลขเป็นตัวอักษรกับหลักสิบ หรือระหว่าง 10 – 99 ซึ่งในภาษาอังกฤษ ค่าตั้งแต่ 10 ถึง 19 จะมีคำเรียกเฉพาะ โค้ดของไมโครซอฟต์นี้จะแยกเป็น 2 กรณี โดยตั้งตัวเลือกไว้ว่า ให้ตัดตัวซ้าย (left) ถ้าตัวหน้า เป็นเลข 1 ให้ทำ case 10 ถึง 19 แต่ถ้าตัวนำหน้าไม่ใช่เลข 1 ก็กำหนดเลยว่า ถ้านำหน้าด้วย 2 ให้แปลงเป็น twenty อะไรทำนองนั้น
กลุ่มแรก ถ้าตัวหน้า (ของหลักสิบ) เป็นเลข 1
10 =Ten
11 = Eleven
12 = Twelve
13 = “hirteen
14 = Fourteen
15 = Fifteen
16 = Sixteen
17 = Seventeen
18 = Eighteen
19 = Nineteen
ถ้าตัวหน้า (ของหลักสิบ) ไม่ใช่ 1 ให้ดูตามนี้
2 = Twenty
3 = Thirty
4 = Forty
5 = Fifty
6 = Sixty
7 = Seventy
8 = Eighty
9 = Ninety
Function GetHundreds
จากนั้นจะเป็นการจัดการกับหลักต่าง ๆ ซึ่งในสูตรนี้จะผูกร่วมกับฟังก์ชันอื่น ๆ และ การสร้างหลักอื่นด้วย คือ หลักพันเรียกว่า Thousand หลักหมื่น เรียกว่า Ten Thousand
หลักแสน เรียกว่า hundred Thousand หลักล้าน เรียกว่า Million หลักพันล้าน เรียกว่า Billion หลักล้านล้าน เรียกว่า Trillion หลัก เอ่อ เรียกหลักอะไร ถ้ามีศูนย์ต่อท้าย 15 ตัว จะเรียกว่า Quadrillion มีศูนย์ต่อท้าย 18 ตัว เรียกว่า Quintillion ไปจนถึง เลขศูนย์ต่อท้าย 303 ตัว เรียกว่า Centillion
แต่เราทำถึงหลักล้านล้านก็แล้วกัน เกินจากนี้โอกาสใช้งานคงน้อยมากมากมากมากมากกกกกกกกกกกกกกก (เติม ก ไก่ หมดโลก)
แทรกเป็นเกร็ดนิดนึง เมื่อก่อน billion จะเป็นหลัก ล้านล้าน หรือ 1,000,000,000,000 แต่พอสหรัฐครองโลก ก็ปรับการนับ billion เป็นแค่ พันล้าน 1,000,000,000 ส่วนที่เป็นล้านล้าน จะเป็น Trillion
คร่าว ๆ ก็ประมาณนี้
Code ที่แก้ไข
จาก debug ที่ขึ้นว่ามีปัญหา คือ
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _ "00", 2))
ตรงนี้คือการจัดการเรื่อง cents (ในทศนิยม) และจัดการปรับ MyNumber (ตัวเลขที่ใส่) ให้เป็น dollar เมื่อเห็นบรรทัดนี้ สิ่งแรกที่สงสัยคือ & _ ตรงขีดล่างนั้นมาจากไหนใช้ทำอะไร? เพราะคำสั่งนี้ไม่น่ามีปัญหา การปรับทศนิยม 2 ตำแหน่ง
เมื่อนั่งไล่ดูโค้ด ก็รู้สึกว่า เอ๊ะ ทำไมโค้ดนี้สร้าง Temp ขึ้นมาก่อน มันจำเป็นต้องสร้าง Temp หรือเปล่า?
ดังนั้น โค้ดบรรทัดแรกคือ
Dim Dollars, Cents, Temp
ถ้าเราจะตัด Temp ออก แล้ว Temp ไว้ทำอะไร ? เลื่อนไปดูด้านล่าง จะเห็นโค้ดว่า
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
นั่นคือเป็นตัวที่เก็บข้อมูลของตัวดอลลาร์ หรือ จำนวนเต็ม
ก็เลยคิดว่า ถ้าตัดตรง Temp ออกเลย ไม่ต้องไปสนใจ แล้วเปลี่ยนให้ dollar เป็นตัวแทนเลย
Dollars = GetHundreds & Temp (Count) & Dollars
โดยกำหนดใหม่เลยว่า Temp เป็น อาร์เรย์ ของค่า Thousand Million
เมื่อไล่แก้แล้ว เปลี่ยนตัวเลขเป็นตัวอักษร โดยอยู่บนพื้นโค้ดของไมโครซอฟต์ก็จะได้เป็น โค้ดใหม่ ดังนี้
Function SpellNumber(ByVal MyNumber)
'Debug by DataRevol
Dim Dollars, Cents
Temp = Array("", "", " Thousand ", " Million ", " Billion ", " Trillion ")
MyNumber = Trim(Str(MyNumber))
' จัดการกับทศนิยม
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
GetHundreds = ""
GetValue = Right(MyNumber, 3)
If Val(GetValue) <> 0 Then
GetValue = Right("000" & GetValue, 3)
If Mid(GetValue, 1, 1) <> "0" Then
GetHundreds = GetDigit(Mid(GetValue, 1, 1)) & " Hundred "
End If
If Mid(GetValue, 2, 1) <> "0" Then
GetHundreds = GetHundreds & GetTens(Mid(GetValue, 2))
Else
GetHundreds = GetHundreds & GetDigit(Mid(GetValue, 3))
End If
End If
If GetHundreds <> "" Then
Dollars = GetHundreds & Temp(Count) & Dollars
End If
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select
Select Case Cents
Case ""
Cents = " and No Cents"
Case "One"
Cents = " and One Cent"
Case Else
Cents = " and " & Cents & " Cents"
End Select
SpellNumber = Dollars & Cents
End Function
' แปลงเลขในช่วง 10 ถึง 99 เป็นตัวหนังสือ
Function GetTens(TensText)
Dim Result As String
Result = "" ' ตั้งค่า Temp จากสูตรเดิม
If Val(Left(TensText, 1)) = 1 Then ' ตรงนี้คือการกำหนด ถ้านำหน้าด้วย 1 หรือ 10-19
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else
Select Case Val(Left(TensText, 1))' ตรงนี้คือการกำหนด ถ้าเลขนำหน้าไม่ใช่ 1
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit(Right(TensText, 1))
End If
GetTens = Result
End Function
' เปลี่ยนเลขจาก 1 ถึง 9 เป็นตัวหนังสือ
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
ซึ่งก็ลองเอาไปเทสต์แล้ว ก็ใช้งานได้อยู่
ที่เขียนเรื่อง เปลี่ยนตัวเลขเป็นตัวอักษร นี้ก็เพื่อให้เห็นว่า บางทีถ้าเราอยากจะทำฟังก์ชันเป็นพิเศษใช้เองก็ได้เหมือนกัน
Discover more from Data Revol
Subscribe to get the latest posts sent to your email.