Join String

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

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

Video แสดงการเขียนฟังก์ชั่นเชื่อมข้อความ

ฟังก์ชันการเชื่อมข้อมูล

ปกติการเชื่อมข้อมูลเราสามารถใช้เครื่องหมาย & หรือฟังก์ชัน Concatenate มาช่วยได้ ยกตัวอย่างเช่น ต้องการเชื่อมข้อมูลที่อยู่ในช่วงเซลล์ A1:A5 เราสามารถเขียนสูตรได้ดังต่อไปนี้

1. =A1&A2&A3&A4&A5
2. =Concatenate(A1,A2,A3,A4,A5)

กรณีที่มีเซลล์จำนวนมากหากจะต้องเชื่อมกันในลักษณะนี้จะเกิดความไม่สะดวก ผมจึงเขียนเป็น Function VBA สำหรับการใช้งานดังกล่าวชื่อว่า Mconc โดยสามารถกำหนดตัวคั่นข้อความได้ด้วย ยกตัวอย่างเช่นหากต้องการเชื่อมข้อความในช่วงเซลล์ A1:A5 แล้วให้แสดงเครื่องหมาย / คั่นระหว่างข้อความ สามารถใช้สูตรได้ดังนี้

=Mconc(A1:A5,”/”)

ซึ่งเทียบเท่ากับ

=A1&”/”&A2&”/”&A3&”/”&A4&”/”&A5

โดย Code VBA ของฟังก์ชั่น Mconc เป็นตามด้านล่างครับ

Function MCONC$(range As range, Optional s As String)
    Dim r As range
    Dim i As Integer
    Dim a() As Variant
    For Each r In range
        If r <> "" Then
            i = i + 1
            ReDim Preserve a(i - 1)
            a(i - 1) = r
        End If
    Next r
    MCONC = Join(a, s)
End Function

การเติมค่าที่คีย์เป็นช่วงให้แสดงทุกรายการด้วย VBA

กรณีที่มีการคีย์ค่าเป็นช่วงแล้วต้องการให้แสดงทุกรายการในช่วงนั้น ๆ คงต้องพึ่งพา VBA ในการจัดการครับ ยกตัวอย่างเช่น ตามภาพด้านล่าง ค่าในช่วง A2:A5 จะมีการคีย์แบบช่วง และที่ B2:B5 คือตัวอย่างผลลัพธ์ที่ต้องการ

ภาพประกอบค่าที่คีย์เป็นช่วงและผลลัพธ์ที่ต้องการ

ซึ่งสามารถใช้ Code VBA ตามด้านล่างครับ

Function StringArray(t As String) As String()
    StringArray = Split(t, ",")
End Function

Sub SplitThenJoin()
    Dim s As String, a() As String
    Dim r As Range, rAll As Range
    Dim i As Integer, j As Integer, k As Integer
    With Worksheets("Sheet1")
        Set rAll = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
    End With
    For Each r In rAll
        a = StringArray(r.Value)
        For i = 0 To UBound(a)
            j = InStr(1, a(i), "-")
            If IsNumeric(Left(a(i), 1)) And j > 0 Then
                For k = Left(a(i), j - 1) To Mid(a(i), j + 1, 255)
                    s = s & k & ","
                Next k
            ElseIf j > 0 Then
                For k = Asc(Left(a(i), j - 1)) To Asc(Mid(a(i), j + 1, 255))
                    s = s & Chr(k) & ","
                Next k
            End If
            If Len(s) > 1 Then
                a(i) = Left(s, Len(s) - 1)
            Else
                a(i) = a(i)
            End If
            s = ""
        Next i
        r = Join(a, ",")
    Next r
End Sub

โดยมีแนวคิดคือ

1. นำค่าในแต่ละเซลล์ไปแบ่งให้เป็น Array โดยใช้เครื่องหมายคอมม่า (,) จากนั้นก็จะ Loop เพื่อว่าจากสมาชิก Array ที่ได้มีค่าใดประกอบด้วยเครื่องหมาย - ก็จะนำไปประมวลต่อโดยหาว่าหากด้านหน้าเครื่องหมาย - เป็นตัวเลขก็จะทำการเปลี่ยนค่าของสมาชิก Array นั้นเสียใหม่ เริ่มด้วยค่าที่อยู่หน้าเครื่องหมาย - แล้วบวกเข้าไปครั้งละ 1 จากนั้นคั่นด้วยเครื่องหมายคอมม่าจนได้ค่าสุดท้ายเท่ากับตัวเลขหลังเครื่องหมาย -
   
2. หากด้านหน้าเครื่องหมาย - เป็นตัวอักษรก็จะหาว่ารหัสตัวอักษร (Character Code) นั้นคือค่าใดและทำการบวกรหัสนั้นไปครั้งละ 1 จากนั้นคั่นด้วยเครื่องหมายคอมม่า จนกระทั่งเท่ากับรหัสของตัวอักษรสุดท้ายหลังเครื่องหมาย -
   
3. กรณีที่สมาชิก Array ไม่มีเครื่องหมาย - ก็ให้มีค่าเท่าเดิม

สำหรับสมาชิกของ http://www.snasui.com/ สามารถดาวน์โหลดไฟล์ตัวอย่างได้ที่นี่ครับ การแปลง Format ข้อมูล