ตรวจจับภาษาในเซลล์ Excel อัตโนมัติด้วย Power Query และ VBA

บทนำ

เมื่อคุณจัดการข้อมูลหลายภาษาใน Excel การตรวจจับภาษาอาจมีประโยชน์มากสำหรับ Multilingual Datasets, Customer Feedback Analysis, หรือ Content Categorization

ในบทช่วยสอนนี้ จะแสดงวิธีตรวจจับภาษาอัตโนมัติในเซลล์ Excel โดยใช้ Power Query หรือ VBA

Power Query ตรวจจับภาษาอัตโนมัติ

Power Query เป็น Built-in Tool อันทรงพลังใน Excel อนุญาต Import, Clean, Transform Data และแม้แต่ API Calls สามารถใช้ DetectLanguage API, LibreTranslate API หรือ Paid API ใดๆ กับ Power Query

ขั้นตอนที่ 1: รับ Free API Key

• ไปที่ DetectLanguage.com
• Sign up (ฟรี) และ Copy API Key จาก Dashboard

ขั้นตอนที่ 2: เตรียม Data Table

ใส่รายการภาษาใน Excel

• เลือกช่วงข้อมูล
• Insert > Table
• เช็ค My table has headers
• คลิก OK

• เปลี่ยนชื่อ Table:
  • Table Design > Table Name > ใส่ Language_List

ขั้นตอนที่ 3: ใช้ Power Query ตรวจจับภาษา

• เลือกเซลล์ใน Table
• Data > From Table/Range

• Home > New Source > Other Sources > Blank Query
• ตั้งชื่อ Auto Detect Language

• Home > Advanced Editor
• คัดลอกวางโค้ดต่อไปนี้
• คลิก Done

let
    APIKey = "YOUR_API_KEY", // <-- Replace this
    Source = Excel.CurrentWorkbook(){[Name="Language_List"]}[Content],
    AddJson = Table.AddColumn(Source, "JsonBody", each Text.ToBinary("q=" & Uri.EscapeDataString([Text])) ),
    AddResponse = Table.AddColumn(AddJson, "Response", each Json.Document(
        Web.Contents("https://ws.detectlanguage.com/0.2/detect", [
            Headers = [
                #"Authorization" = "Bearer " & APIKey,
                #"Content-Type" = "application/x-www-form-urlencoded"
            ],
            Content = [JsonBody]
        ])
    ) ),
    ExtractLang = Table.AddColumn(AddResponse, "Language", each try [Response][data][detections]{0}[language] otherwise "error"),
    Final = Table.SelectColumns(ExtractLang, {"ID", "Text", "Language"})
in
    Final

• แทนที่ YOUR_API_KEY ด้วย Key จริง

• คลิก Close & Load จะเห็นคอลัมน์ Language พร้อม Language Codes (en, fr ฯลฯ)

ใช้ VBA ตรวจจับภาษาอัตโนมัติ

สามารถใช้ VBA ตรวจจับภาษาอัตโนมัติ ใน VBA เรียก Free ถึง Paid APIs ได้ทั้งหมด หรือสร้าง User-defined Functions ตรวจจับภาษา

• Developer > Visual Basic

• Insert > Module
• คัดลอกวางโค้ด VBA ต่อไปนี้

VBA Code:

Function Detect_Language(text As String) As String
    On Error GoTo handleErr
    Dim http As Object
    Dim url As String
    Dim response As String
    Dim apiKey As String
    
    apiKey = "YOUR_API_KEY" ' <-- Replace with your API key!
    url = "https://ws.detectlanguage.com/0.2/detect?q=" & URLEncode(text)
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        .Open "GET", url, False
        .setRequestHeader "Authorization", "Bearer " & apiKey
        .send
        response = .responseText
    End With
    
    ' Parse JSON สำหรับ "language" code
    Dim startPos As Integer
    Dim endPos As Integer
    startPos = InStr(response, """language"":""") + Len("""language"":""")
    If startPos > Len("""language"":""") Then
        endPos = InStr(startPos, response, """")
        Detect_Language = Mid(response, startPos, endPos - startPos)
    Else
        Detect_Language = "unknown"
    End If
    Exit Function
    
handleErr:
    Detect_Language = "ERROR: " & Err.Description
End Function

' Helper สำหรับ URL Encoding (รองรับ Unicode พื้นฐาน)
Function URLEncode(str As String) As String
    Dim i As Long
    Dim ch As String
    Dim encoded As String
    For i = 1 To Len(str)
        ch = Mid(str, i, 1)
        Select Case AscW(ch)
            Case 48 To 57, 65 To 90, 97 To 122 ' 0-9, A-Z, a-z
                encoded = encoded & ch
            Case Else
                encoded = encoded & "%" & Hex(AscW(ch))
        End Select
    Next i
    URLEncode = encoded
End Function

• Save โค้ด และกลับไปชีท Excel
• เลือกเซลล์ C2 ใส่ UDF Function ต่อไปนี้

Custom Function นี้ตรวจจับภาษาอัตโนมัติและคืน Language Code

Bonus: ฟังก์ชัน DETECTLANGUAGE ใน Excel (Microsoft 365 & Web เท่านั้น)

หากใช้ Excel for Microsoft 365 หรือ Excel for the Web สามารถใช้ Built-in DETECTLANGUAGE Function ระบุภาษาของข้อความในเซลล์ได้อัตโนมัติ ฟีเจอร์นี้เฉพาะ Microsoft 365 Subscribers และ Excel Online เปิดตัวปลาย 2022

• เลือกเซลล์ ใส่สูตร:

ฟังก์ชันดึงผลจาก Web

ฟังก์ชันนี้ Auto Detect ภาษา

Availability: DETECTLANGUAGE Function ใช้ได้เฉพาะ:

• Excel for the web (Excel Online)
• Microsoft 365 (บาง Desktop Builds ไม่ใช่ทุก User)
• ไม่ใช้ได้ ใน Excel 2019, 2016 หรือ Perpetual License versions ก่อนหน้า

สรุป

ด้วยวิธีข้างต้น สามารถตรวจจับภาษาในเซลล์ Excel อัตโนมัติได้ ด้วย Power Query สามารถ Supercharge Multilingual Data Processing Workflow ได้ง่าย Power Query แนะนำสำหรับผู้ใช้ส่วนใหญ่ ให้ Stability, Scalability, และ Direct Data Transformation มากกว่า VBA เหมาะสำหรับการใช้งานเบา Local และ Quick