วันอังคารที่ 8 กรกฎาคม พ.ศ. 2557

สูตร Excel การเอาตัวหนังสือออกจากตัวเลข

สูตร Excel วันนี้ เราจะมาเสอน วิธีการที่น่าจะมีประโยชน์อย่างยิ่งสำหรับคนที่ทำงานด้านตัวเลขทั้งหลาย...นั่นก็คือ การแยก ตัวหนังสือออกจากตัวเล... thumbnail 1 summary
สูตร Excel วันนี้ เราจะมาเสอน วิธีการที่น่าจะมีประโยชน์อย่างยิ่งสำหรับคนที่ทำงานด้านตัวเลขทั้งหลาย...นั่นก็คือ การแยก ตัวหนังสือออกจากตัวเลข นั่นเอง
สมมติว่า ที่ เซลล์ A1 เรามีข้อความเช่น
aa354m7ne9  หากเราต้องการจะแยกตัวเลขออกจากตัวหนังสือ โดยให้เซลล์ A1 แสดงเฉพาะตัวเลขเท่านั้น เราจะทำได้อย่างไร
เพื่อความเข้าใจที่มากขึ้น เราจะสมมติให้เห็นภาพมากกว่านี้ ดังนี้
aa354m7ne9 แยกตัวหนังสือออกมา เซลล์ A1 จะเท่ากับ 35479 อย่างนี้เป็นต้น
จะเห็นว่า โจทย์ต้องการคำตอบเป็นแบบนี้ เราก็ต้องมาหาวิธีการกันว่าจะมีวิธีการใดที่สามารถจะทำแบบนี้ได้
    ในตัวอย่าง  สูตร Excel วันนี้ เราจะใช้ การเขียน vba เข้ามาช่วย โดยให้เราทำดังนี้
  1. เปิด Excel ขึ้นมา 
  2. พิมพ์ตัวเลขและตัวหนังสือว่า aa354m7ne9 ที่เซลล์ A1
    สูตร Excel การเอาตัวหนังสือออกจากตัวเลข pic 1
  3. กด Alt+F11 แล้ว 
  4. ดับเบิ้ลคลิ้กที่ Sheet 1 ที่อยู่ในส่วน VBAproject
  5. ใส่โค้ดต่อไปนี้ลงไป

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


    Dim rngCell As Range
    Dim intChar As Integer
    Dim strCheckString As String
    Dim strCheckChar As String
    Dim intCheckChar As Integer
    Dim strClean As String
   
 If Target.Address = "$B$1" Then
 Range("a1").Select
    For Each rngCell In Selection
        strCheckString = rngCell.Value
        strClean = ""

        For intChar = 1 To Len(strCheckString)
            strCheckChar = Mid(strCheckString, intChar, 1)
            intCheckChar = Asc(strCheckChar)
            Select Case intCheckChar
                Case 65 To 90
                Case 97 To 122
                Case 128 To 151
                Case 153 To 154
                Case 159 To 165
                Case Else
                    strClean = strClean & strCheckChar
            End Select

        Next intChar
        rngCell.Value = strClean

    Next rngCell
   
End If

End Sub

6. กด Alt+F11 เพื่อกลับมาที่ Excel อีกครั้ง7. ทดสอบการทำงานของโปรแกรม โดยให้เราใช้เม้าสคลิ้กที่เซลล์ B1 แล้วสังเกตผลลัพธ์ที่เกิดขึ้นที่ช่อง A1
สูตร Excel การเอาตัวหนังสือออกจากตัวเลข pic 2
 จากภาพ เราจะเห็นว่า เมื่อเรา คลิ้กที่ เซลล์ b1 ในช่อง A1 ตัวหนังสือจะถูกตัดทิ้งทั้งหมด เหลือเพียงตัวเลขเท่านั้น

อธิบาย สูตร Excel ได้ดังนี้

 If Target.Address = "$B$1" Then
 Range("a1").Select

เป็นการตรวจสอบว่า เซลล์ที่เราคลิ้กคือเซลล์ B1 ใช่หรือไม่
ถ้าใช่ ก็ให้ เซลล์ A1 ถูกเลือก (เพื่อเป็นการบอก Excel ว่า จะให้จัดการ เซลล์ A1 ให้หน่อยน่ะ)

For Each rngCell In Selection
        strCheckString = rngCell.Value
        strClean = ""

        For intChar = 1 To Len(strCheckString)
            strCheckChar = Mid(strCheckString, intChar, 1)
            intCheckChar = Asc(strCheckChar)
            Select Case intCheckChar
                Case 65 To 90
                Case 97 To 122
                Case 128 To 151
                Case 153 To 154
                Case 159 To 165
                Case Else
                    strClean = strClean & strCheckChar
            End Select

        Next intChar
        rngCell.Value = strClean

    Next rngCell
ส่วนภายในคำสั่ง For จะเป็นการ ดึงตัวอักษรแต่ละตัวมาตรวจสอบว่า มีตัวไหนบ้างที่เป็นตัวเลข ตัวไหนบ้างเป็นตัวหนังสือ โดยใช้คำสั่ง mid ในการเข้าถึงตัวอักษรแต่ละตัว