Tự động phát hiện ngôn ngữ trong ô Excel bằng Power Query &VBA

Khi bạn đang quản lý dữ liệu đa ngôn ngữ trong Excel, tính năng 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 của 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ẽ trình bày cách tự động phát hiện ngôn ngữ trong các ô Excel bằng Power Query hoặc VBA.

Power Query để tự động phát hiện ngôn ngữ

Power Query là một công cụ tích hợp mạnh mẽ trong Excel cho phép bạn nhập, dọn dẹp và chuyển đổi dữ liệu, thậm chí thực hiện lệnh gọi API. Bạn có thể sử dụng API DetectLanguage, API LibreTranslate hoặc bất kỳ API trả phí nào với Power Query.

Bước 1:Nhận khóa API miễn phí

• Truy cập DetectLanguage.com
• Đăng ký (miễn phí) và sao chép khóa API của bạn từ trang tổng quan.

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.
• Đi tới Chèn tab>> chọn Bảng .
• Kiểm tra Bảng của tôi có tiêu đề .
• Nhấp vào OK .

• Đổi tên bảng của bạn:
  • Đi tới Thiết kế bảng tab>> chọn Tên bảng>> chèn Danh sách ngôn ngữ .

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.
• Đi tới Dữ liệu tab>> chọn Từ Bảng/Phạm vi .

• Truy cập Trang chủ tab>> chọn Nguồn mới>> chọn Các nguồn khác>> chọn Truy vấn trống .
• Đặt tên cho nó là Tự động phát hiện ngôn ngữ .

• Truy cập Trang chủ tab>> chọn Trình chỉnh sửa nâng cao .
• Sao chép và dán đoạn mã sau.
• Nhấp vào Xong .

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 của bạn.

• Nhấp vào Đóng và tải . Bạn sẽ thấy Ngôn ngữ cột có mã ngôn ngữ được phát hiện (en, fr, v.v.).

Sử dụng VBA để tự động phát hiện ngôn 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 các hàm dành riêng cho người dùng để phát hiện ngôn ngữ.

• Đi tới Nhà phát triển tab>> chọn Visual Basic .

• Từ Chèn >> chọn Mô-đun .
• 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 bảng Excel.
• Chọn ô C2 và chèn hàm UDF sau.

Chức năng tùy chỉnh này tự động phát hiện ngôn ngữ và trả về mã ngôn ngữ.

Phần thưởng:Sử dụng hàm DETECTLANGUAGE trong Excel (Chỉ Microsoft 365 &Web)

Nếu bạn đang sử dụng Excel dành cho Microsoft 365 hoặc Excel dành cho web, bạn có thể sử dụng hàm DETECTLANGUAGE tích hợp sẵn để tự động xác định ngôn ngữ của văn bản của bất kỳ ô nào. Tính năng này chỉ có sẵn cho những người đăng ký Microsoft 365 và trong Excel Online, được triển khai từ cuối năm 2022 trở đi.

• Chọn một ô và chèn công thức sau.

Nó lấy kết quả từ Web.

Chức năng này tự động phát hiện ngôn ngữ.

Tính sẵn có: Chức năng DETECTLANGUAGE chỉ khả dụng trong:

• Excel dành cho web (Excel trực tuyến).
• Microsoft 365 (một số bản dựng dành cho máy tính để bàn nhưng không phải tất cả người dùng).
• Không có sẵn trong Excel 2019, Excel 2016 hoặc các phiên bản giấy phép vĩnh viễn trước đó.

Kết luận

Thực hiện theo các phương pháp trên, bạn có thể tự động phát hiện ngôn ngữ trong các ô Excel. Bằng cách sử dụng Power Query, bạn có thể dễ dàng tăng cường quy trình xử lý dữ liệu đa ngôn ngữ của mình. Power Query được hầu hết người dùng ưa thích vì mang lại độ ổn định, khả năng mở rộng và chuyển đổi dữ liệu trực tiếp cao hơn. VBA là lựa chọn tốt nhất cho việc sử dụng đơn giản, cục bộ và nhanh chóng.