Text Functions

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

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

Video แสดงการใช้งาน Text Functions

สอบถามปัญหาการใช้งาน Excel และ VBA ได้ที่ snasui.com

การนำข้อมูลมาเชื่อมต่อกันโดยดูตามสี

ใน Microsoft Excel ไม่มีฟังก์ชั่นสำหรับการ List รายการโดยดูสีที่เข้าเงื่อนไข หากจะทำเช่นนั้นต้องอาศัย Add-ins เช่น Morefunc (Support Excel 2007 ลงไป) หรือ VBA เข้ามาจัดการ และเนื่องจากว่าการใช้ Morefunc ไม่ Support กับ Excel ทุกรุ่น จึงขอนำเสนอการ List ข้อมูลตามสีที่เข้าเงื่อนไขด้วย VBA ครับ

ยกตัวอย่างตามภาพด้านล่าง มีข้อมูลตัวเลขตามสีต่าง ๆ ที่ A1:G9 ของ Sheet1 ต้องการจะให้ List สีออกมาทั้งหมดโดยให้อยู่ในคอลัมน์ I และนำค่าในเซลล์ที่มีสีนั้น ๆ มาเชื่อมต่อกันในคอลัมน์ J ในชีทเดียวกัน

ภาพประกอบการนำข้อมูลมาเชื่อมต่อกันโดยดูตามสี

เราสามารถใช้ VBA เพื่อแสดงผลลัพธ์ที่ต้องการได้ตามด้านล่างครับ

Sub ConcateValuesFormColor()
    Dim rColor As Range, rcAll As Range
    Dim r As Range, i As Integer, rc As Range
    With Sheets("Sheet1")
        .Range("I:J").Clear
        Set rColor = .Range("A1:G9")
    End With
    i = 1
    For Each r In rColor
        Set rcAll = Sheets("Sheet1").Range(Cells(1, "I"), Cells(i, "I"))
        If Application.CountIf(rcAll, r.Interior.Color) = 0 Then
            Cells(i, "I") = r.Interior.Color
            i = i + 1
         End If
    Next r
    Set rcAll = rcAll.Resize(i - 1)
    For Each rc In rcAll
        For Each r In rColor
            If r.Interior.Color = rc Then
                rc.Offset(0, 1) = rc.Offset(0, 1) & r.Value & ","
            End If
        Next r
        rc.Offset(0, 1) = Left(rc.Offset(0, 1), Len(rc.Offset(0, 1)) - 1)
        rc.Interior.Color = rc.Value
        rc.ClearContents
    Next rc
End Sub

การจัดเรียงข้อมูลจากน้อยไปหามากหรือตรงกันข้ามด้วยสูตร

สำหรับการจัดเรียงข้อมูลด้วยสูตรนั้น ประโยชน์ที่ได้คือสามารถลดเวลาในการจัดเรียงข้อมูลกรณีที่ข้อมูลเปลี่ยนแปลงบ่อย ๆ เนื่องจากข้อมูลถูกจัดเรียงอัตโนมัติตามที่ต้องการ ไม่ต้องทำการจัดเรียงเองทุกครั้งที่ข้อมูลเปลี่ยนแปลง แต่สูตรที่ใช้เรียงข้อมูลนั้นค่อนข้างยากต่อการทำความเข้าใจเนื่องจากต้องใช้สูตร Array

ตามด้านล่างจะเป็นภาพการเรียงข้อมูลจากน้อยไปหามากด้วยสูตรเปรียบเทียบกับการเรียงข้อมูลด้วยเมนู Data > Sort

ภาพการเรียงข้อมูลจากน้อยไปหามากด้วยสูตร

โดยมีวิธีการดังนี้

1. ข้อมูลอยู่ที่เซลล์ A1:A12
   
2. ที่ B1 คีย์สูตรเพื่อนับว่ามีเซลล์ที่มีข้อมูลจำนวนเท่าไร
   
   =Counta(A:A)
   
   Enter
   
3. ที่เซลล์ C1 คีย์สูตรเพื่อเรียงข้อมูลจากน้อยไปหามาก
   
   =IF(ROWS(C$1:C1)>$B$1,"",INDEX($A$1:$A$12,MATCH(SMALL(IF(ISERR(CODE($A$1:$A$12)),"",CODE($A$1:$A$12)+COUNTIF($A$1:$A$12,"<"&$A$1:$A$12)),ROWS(C$2:C2)),CODE($A$1:$A$12)+COUNTIF($A$1:$A$12,"<"&$A$1:$A$12),0)))
   
   Ctrl+Shift+Enter > Copy ลงด้านล่าง

Note:

กรณีต้องการเรียงข้อมูลจากมากไปหาน้อยให้เปลี่ยน Small เป็น Large