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

เมื่อคุณจัดการข้อมูลหลายภาษาใน Excel การตรวจจับภาษาจะมีประโยชน์อย่างเหลือเชื่อสำหรับชุดข้อมูลหลายภาษา การวิเคราะห์ความคิดเห็นของลูกค้า หรือการจัดหมวดหมู่เนื้อหา

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

Power Query เพื่อตรวจจับภาษาโดยอัตโนมัติ

Power Query เป็นเครื่องมือในตัวที่มีประสิทธิภาพใน Excel ที่ช่วยให้คุณสามารถนำเข้า ล้าง และแปลงข้อมูล และแม้กระทั่งทำการเรียก API คุณสามารถใช้ DetectLanguage API, LibreTranslate API หรือ API แบบชำระเงินใดๆ กับ Power Query ได้

ขั้นตอนที่ 1:รับคีย์ API ฟรีของคุณ

• ไปที่ DetectLanguage.com
• ลงทะเบียน (ฟรี) และคัดลอกคีย์ API ของคุณจากแดชบอร์ด

ขั้นตอนที่ 2:เตรียมตารางข้อมูลของคุณ

แทรกรายการภาษาของคุณใน Excel

• เลือกช่วงข้อมูล
• ไปที่ ส่วนแทรก แท็บ>> เลือก ตาราง .
• กาเครื่องหมาย ตารางของฉันมีส่วนหัว .
• คลิก ตกลง .

• เปลี่ยนชื่อตารางของคุณ:
  • ไปที่การออกแบบตาราง แท็บ>> เลือก ชื่อตาราง>> แทรก รายการภาษา .

ขั้นตอนที่ 3:การใช้ Power Query เพื่อตรวจหาภาษา

• เลือกเซลล์ใดก็ได้ในตารางของคุณ
• ไปที่ ข้อมูล แท็บ>> เลือก จากตาราง/ช่วง .

• ไปที่ หน้าแรก แท็บ>> เลือก แหล่งที่มาใหม่>> เลือก แหล่งที่มาอื่นๆ>> เลือก แบบสอบถามว่าง .
• ตั้งชื่อเป็น ตรวจหาภาษาอัตโนมัติ .

• ไปที่หน้าแรก แท็บ>> เลือก ตัวแก้ไขขั้นสูง .
• คัดลอกและวางโค้ดต่อไปนี้
• คลิก เสร็จสิ้น .

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” ด้วยกุญแจจริงของคุณ

• คลิก ปิดและโหลด . คุณจะเห็นภาษา คอลัมน์ที่มีรหัสภาษาที่ตรวจพบ (en, fr ฯลฯ)

การใช้ VBA เพื่อตรวจจับภาษาโดยอัตโนมัติ

คุณสามารถใช้ VBA เพื่อตรวจจับภาษาโดยอัตโนมัติ ใน VBA คุณสามารถโทรจาก API ทุกประเภททั้งแบบฟรีไปจนถึงแบบชำระเงิน หรือสร้างฟังก์ชันเฉพาะผู้ใช้เพื่อตรวจจับภาษา

• ไปที่ นักพัฒนา แท็บ>> เลือก Visual Basic .

• จาก แทรก >> เลือก โมดูล .
• คัดลอกและวางโค้ด VBA ต่อไปนี้

รหัส VBA:

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 result for "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 for URL encoding (supports most basic 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

• บันทึก รหัสและกลับไปที่แผ่นงาน Excel
• เลือกเซลล์ C2 และแทรกฟังก์ชัน UDF ต่อไปนี้

ฟังก์ชันแบบกำหนดเองนี้จะตรวจจับภาษาโดยอัตโนมัติและส่งกลับรหัสภาษา

โบนัส:การใช้ฟังก์ชัน DETECTLANGUAGE ใน Excel (Microsoft 365 และเว็บเท่านั้น)

ถ้าคุณใช้ Excel สำหรับ Microsoft 365 หรือ Excel สำหรับเว็บ คุณสามารถใช้ฟังก์ชัน DETECTLANGUAGE ที่มีอยู่แล้วภายในเพื่อระบุภาษาของข้อความในเซลล์ใดๆ โดยอัตโนมัติ ฟีเจอร์นี้พร้อมใช้งานสำหรับสมาชิก Microsoft 365 เท่านั้นและใน Excel Online ซึ่งเปิดตัวตั้งแต่ปลายปี 2022 เป็นต้นไป

• เลือกเซลล์และแทรกสูตรต่อไปนี้

มันดึงผลลัพธ์จากเว็บ

ฟังก์ชันนี้จะตรวจจับภาษาโดยอัตโนมัติ

ความพร้อมใช้งาน: ฟังก์ชัน DETECTLANGUAGE ใช้ได้เฉพาะใน:

• Excel สำหรับเว็บ (Excel ออนไลน์)
• ไมโครซอฟต์ 365 (เดสก์ท็อปบางรุ่น แต่ไม่ใช่ผู้ใช้ทั้งหมด)
• ไม่พร้อมใช้งาน ใน Excel 2019, Excel 2016 หรือเวอร์ชันสิทธิ์การใช้งานถาวรก่อนหน้า

บทสรุป

ตามวิธีการข้างต้น คุณสามารถตรวจหาภาษาในเซลล์ Excel ได้โดยอัตโนมัติ ด้วยการใช้ Power Query คุณสามารถเพิ่มประสิทธิภาพเวิร์กโฟลว์การประมวลผลข้อมูลหลายภาษาของคุณได้อย่างง่ายดาย Power Query เป็นที่ต้องการสำหรับผู้ใช้ส่วนใหญ่ โดยให้ความเสถียร ความสามารถในการปรับขนาด และการแปลงข้อมูลโดยตรงที่มากกว่า VBA เหมาะสำหรับการใช้งานแบบเบาๆ ในพื้นที่ และรวดเร็ว