Khi bạn quản lý dữ liệu đa ngôn ngữ trong Excel, phát hiện ngôn ngữ có thể cực kỳ hữu ích cho các tập dữ liệu đa ngôn ngữ, phân tích phản hồi khách hàng, hoặc phân loại nội dung.
Trong hướng dẫn này, chúng tôi sẽ chỉ cách phát hiện ngôn ngữ tự động trong ô Excel sử dụng Power Query hoặc VBA.
Power Query Để Phát Hiện Ngôn Ngữ Tự Động
Power Query là công cụ mạnh mẽ tích hợp sẵn trong Excel cho phép bạn nhập, làm sạch, biến đổi dữ liệu, và thậm chí thực hiện các cuộc gọi API. Bạn có thể sử dụng DetectLanguage API, LibreTranslate API, hoặc bất kỳ API trả phí nào với Power Query.
Bước 1: Lấy Khóa API Miễn Phí
- Đi đến DetectLanguage.com
- Đăng ký (miễn phí) và sao chép khóa API từ bảng điều khiển.
Bước 2: Chuẩn Bị Bảng Dữ Liệu Của Bạn
Chèn danh sách ngôn ngữ của bạn vào Excel.
- Chọn phạm vi dữ liệu.
- Chuyển đến tab Insert >> chọn Table.
- Kiểm tra My table has headers.
- Nhấp OK.
- Đổi tên bảng của bạn:
- Chuyển đến tab Table Design >> chọn Table Name >> chèn Language_List.
Bước 3: Sử Dụng Power Query Để Phát Hiện Ngôn Ngữ
- Chọn bất kỳ ô nào trong bảng của bạn.
- Chuyển đến tab Data >> chọn From Table/Range.
- Chuyển đến tab Home >> chọn New Source >> chọn Other Sources >> chọn Blank Query.
- Đặt tên nó Auto Detect Language.
- Chuyển đến tab Home >> chọn Advanced Editor.
- Sao chép và dán mã sau.
- Nhấp 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
- Thay thế “YOUR_API_KEY” bằng khóa thực tế của bạn.
- Nhấp Close & Load. Bạn sẽ thấy cột Language với mã ngôn ngữ được phát hiện (en, fr, v.v.).
Sử Dụng VBA Để Phát Hiện Ngôn Ngữ Tự Động
Bạn có thể sử dụng VBA để tự động phát hiện ngôn ngữ. Trong VBA, bạn có thể gọi từ miễn phí đến trả phí tất cả các loại API hoặc tạo hàm tùy chỉnh người dùng để phát hiện ngôn ngữ.
- Chuyển đến tab Developer >> chọn Visual Basic.
- Từ Insert >> chọn Module.
- Sao chép-dán mã VBA sau.
Mã 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
- Lưu mã và quay lại sheet Excel.
- Chọn ô C2 và chèn hàm UDF sau.
Hàm tùy chỉnh này tự động phát hiện ngôn ngữ và trả về mã ngôn ngữ.
Bonus: Sử Dụng Hàm DETECTLANGUAGE Trong Excel (Chỉ Microsoft 365 & Web)
Nếu bạn đang sử dụng Excel cho Microsoft 365 hoặc Excel cho web, bạn có thể sử dụng hàm DETECTLANGUAGE tích hợp sẵn để xác định ngôn ngữ của văn bản bất kỳ ô nào một cách tự động. Tính năng này chỉ khả dụng cho người đăng ký Microsoft 365 và trong Excel Online, được triển khai từ cuối 2022 trở đi.
- Chọn một ô và chèn công thức sau.
Nó lấy kết quả từ Web.
Hàm này tự động phát hiện ngôn ngữ.
Khả Dụng: Hàm DETECTLANGUAGE chỉ khả dụng trong:
- Excel cho web (Excel Online).
- Microsoft 365 (một số bản build desktop, nhưng không phải tất cả người dùng).
- Không khả dụng trong Excel 2019, Excel 2016, hoặc các phiên bản perpetual license trước đó.
Kết Luận
Bằng cách làm theo các phương pháp trên, bạn có thể tự động phát hiện ngôn ngữ trong ô Excel. Bằng cách sử dụng Power Query, bạn có thể dễ dàng siêu sạc quy trình xử lý dữ liệu đa ngôn ngữ của mình. Power Query được ưu tiên cho hầu hết người dùng, cung cấp nhiều độ ổn định, khả năng mở rộng và biến đổi dữ liệu trực tiếp hơn. VBA tốt nhất cho việc sử dụng nhẹ, cục bộ và nhanh chóng.
Nhận Bài Tập Excel Nâng Cao Miễn Phí Có Giải Pháp!