Power Query と VBA を使用して Excel セル内の言語を自動的に検出する

 

Excel で多言語データを管理する場合、言語検出は多言語データセット、顧客フィードバック分析、コンテンツの分類に非常に役立ちます。

このチュートリアルでは、Power Query または VBA を使用して Excel セル内の言語を自動的に検出する方法を示します。

言語を自動的に検出する Power Query

Power Query は Excel の強力な組み込みツールであり、データのインポート、クリーンアップ、変換、さらには API 呼び出しを行うことができます。 Power Query では、DetectLanguage API、LibreTranslate API、または任意の有料 API を使用できます。

ステップ 1:無料の API キーを取得する

• DetectLanguage.com にアクセスします
• サインアップ (無料) し、ダッシュボードから API キーをコピーします。

ステップ 2:データ テーブルを準備する

Excel に言語リストを挿入します。

• データ範囲を選択します。
• 挿入に移動します タブ>> テーブルを選択します .
• テーブルにヘッダーがあるをチェックします。 .
• [OK] をクリックします。 .

• テーブルの名前を変更します:
  • テーブル デザインに移動します。 タブ>> テーブル名を選択します>> 言語リストを挿入 .

ステップ 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 関数を挿入します。

このカスタム関数は、言語を自動的に検出し、言語コードを返します。

ボーナス:Excel での DETECTLANGUAGE 関数の使用 (Microsoft 365 および Web のみ)

Excel for Microsoft 365 または Excel for the web を使用している場合は、組み込みの DETECTLANGUAGE 関数を使用して、セルのテキストの言語を自動的に識別できます。この機能は、Microsoft 365 サブスクライバーと Excel Online でのみ利用可能で、2022 年後半以降に展開されます。

• セルを選択し、次の数式を挿入します。

Web から結果を取得します。

この関数は言語を自動検出します。

可用性: DETECTLANGUAGE 関数は、以下でのみ使用できます。

• Web 用 Excel (Excel Online)。
• Microsoft 365 (一部のデスクトップ ビルドではありますが、すべてのユーザーではありません)
• 利用できません Excel 2019、Excel 2016、またはそれ以前の永久ライセンス バージョンでは。

結論

上記の方法に従って、Excel セル内の言語を自動的に検出できます。 Power Query を使用すると、多言語データ処理ワークフローを簡単に強化できます。 Power Query は、安定性、拡張性、直接的なデータ変換を提供するため、ほとんどのユーザーに好まれます。 VBA は、軽量でローカルで迅速に使用するのに最適です。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!