Textfunktionen in ExcelMit SPRACHEERKENNEN mehrsprachige Kundenanfragen in Excel zuordnen

Was die Funktion SPRACHEERKENNEN leistet

Über das Kontaktformular Ihrer Webseite kommen Anfragen auf Deutsch, Englisch, Französisch und Spanisch herein. Bevor jemand antworten kann, muss feststehen, welches Team die Sprache spricht.

Die Excel-Funktion SPRACHEERKENNEN() bestimmt, in welcher Sprache ein Text geschrieben ist, und gibt dafür einen Sprachcode zurück. Für „Hola mundo!“ liefert die Funktion den Code „es“ für Spanisch. Die eigentliche Erkennung übernimmt dabei nicht Excel selbst, sondern der Übersetzungsdienst von Microsoft. 

Wer eine Anfrage bearbeitet, kennt die Funktion nicht. Das legen Sie selbst fest, und zwar in einer Tabelle, die jedem Sprachcode ein Team zuordnet. Der Code ist der Schlüssel, über den Excel die Nachricht mit dem Team verbindet.

Hinweis: Wenn Sie Texte übersetzen wollen, brauchen Sie SPRACHEERKENNEN nicht. Dafür gibt es ÜBERSETZEN (englisch TRANSLATE). Diese Funktion erkennt die Ausgangssprache ebenfalls selbstständig.

Voraussetzungen für den Einsatz prüfen

Die Funktion SPRACHEERKENNEN in Excel gibt es für Microsoft 365 unter Windows und Mac sowie für Excel Mobile und Excel für das Web. 

Weil die Erkennung über einen Onlinedienst läuft, braucht Excel eine Internetverbindung. Fehlt sie, erscheint der Fehler #VERBINDEN!. Microsoft weist darauf hin, dass sich die unterstützten Sprachen und die Ergebnisse ändern können.

Die Texte werden dabei an Microsoft übertragen. Speicherfristen oder Verarbeitungsorte für SPRACHEERKENNEN nennt Microsoft nicht. Klären Sie deshalb mit Ihrer IT oder dem Datenschutz, ob Sie Kundentexte verarbeiten dürfen.

Syntax und Rückgabewert verstehen

Die Funktion hat genau ein Argument: den Text, dessen Sprache Sie bestimmen wollen: 

=SPRACHEERKENNEN(Text)

Meist ist das Argument ein Zellbezug:

=SPRACHEERKENNEN(B2)

Sie können den Text aber auch direkt in Anführungszeichen eingeben:

=SPRACHEERKENNEN("Hola mundo!")

Zurück kommt ein Sprachcode als Text, keine ausgeschriebene Sprache. Die meisten Codes haben zwei Buchstaben, etwa de, en oder fr. Es gibt aber Ausnahmen wie zh-Hans für Chinesisch (vereinfacht) oder nb für Norwegisch (Bokmål).

Alle Codes finden Sie in der Sprachliste von Microsoft Translator.

Beispiel: Wie Sie die Sprache der Kundenanfragen ermitteln

Schritt 1: Die Ausgangsliste anlegen

Die fiktive Firma Beispielhandel erhält ihre Anfragen über ein Kontaktformular auf ihrer Webseite. Alle Anfragen werden an eine Excel-Tabelle übertragen mit der Anfrage-ID für die eindeutige Zuordnung und dem übermittelten Text.

Die Tabelle wird als dynamische Tabelle formatiert (Strg + T).

Schritt 2: Die Erkennungsformel eingeben

Sie ergänzen nun den Sprachcode für jede Anfrage in einer weiteren Spalte der Tabelle. Dazu klicken Sie in Zelle C2 und geben ein:

=SPRACHEERKENNEN([@Nachricht])

[@Nachricht] steht für die Nachricht in derselben Zeile. Nach dem Bestätigen mit der Eingabetaste ergänzt Excel die Formel für die gesamte Spalte C der dynamischen Tabelle.

Schritt 3: Die Ergebnisse kontrollieren

Gehen Sie die Codes einmal durch und vergleichen Sie sie mit den Nachrichten. Für K004 muss „es“ erscheinen.

Beachten Sie: Nicht jede Sprache, die der Dienst übersetzen kann, erkennt er auch automatisch. Nach Land oder Region können Sie Anfragen mit SPRACHEERKENNEN nicht trennen. Für Portugiesisch (Brasilien) und Französisch (Kanada) fehlt beispielsweise die automatische Erkennung des Ursprunglandes.

Schritt 4: Eine Zuordnungstabelle anlegen

Welche Sprache zu welchem Team gehört, steht in einer eigenen Tabelle. Legen Sie ein Arbeitsblatt Zuordnung an und tragen Sie beispielsweise im Bereich A1:C5 die Daten wie in der folgenden Abbildung ein.

Formatieren Sie den Bereich ebenfalls als dynamische Tabelle und geben Sie ihr den Tabellennamen Sprachen.

Beachten Sie: Jeder Code darf nur einmal vorkommen. XVERWEIS liefert immer den ersten Treffer, ein zweiter Eintrag mit demselben Code würde also übergangen. 

Schritt 5: Den Sprachnamen ergänzen

In der Tabelle mit den Nachrichten ergänzen Sie nun eine weitere Spalte D und geben dort ein:

=XVERWEIS([@Sprachcode]; Sprachen[Code]; Sprachen[Sprache]; "Nicht zugeordnet")

Die Funktion XVERWEIS() sucht den Sprachcode in der Spalte Code und gibt die Sprache aus derselben Zeile zurück. Findet XVERWEIS den Code nicht, erscheint „Nicht zugeordnet“.

Das bedeutet nicht, dass die Erkennung danebenlag, sondern nur, dass der Code in Ihrer Zuordnungstabelle Sprachen fehlt. Ergänzen Sie ihn dann genau in der Schreibweise, die SPRACHEERKENNEN ausgibt, etwa zh-Hans, und legen Sie ein Team dafür fest.

Schritt 6: Das zuständige Team ergänzen

Schließlich ergänzen Sie in der Spalte E, welches Team sich um die jeweilige Anfrage kümmern sollte. Dazu geben Sie in der Spalte E ein:

=XVERWEIS([@Sprachcode]; Sprachen[Code]; Sprachen[Team]; "Manuell zuordnen")

Beispiel: Steht in Zelle C2 der Code de, zeigt Zelle D2 Deutsch und Zelle E2 Service Deutschland. Fehlt der Code in der Tabelle Sprachen, erscheint „Manuell zuordnen“.

Erkennungsergebnisse auf Plausibilität prüfen

Wenn die Funktion SPRACHEERKENNEN() keinen Fehler meldet, heißt das noch nicht, dass die Sprache stimmt. Sehen Sie sich die Ergebnisse deshalb an, bevor Sie Anfragen verteilen. Genauer hinschauen sollten Sie bei zwei Arten von Nachrichten.

Kurze Texte: Für ÜBERSETZEN empfiehlt Microsoft, bei kurzen Texten die Ausgangssprache anzugeben, wenn sie bekannt ist. Microsoft rechnet also selbst damit, dass die Erkennung bei wenig Text danebenliegen kann.

Gemischte Texte: Wie die Funktion entscheidet, wenn eine Nachricht mehrere Sprachen enthält, dokumentiert Microsoft nicht. Office Watch hat „Thanks Danke“ getestet: Frühe Builds lieferten Deutsch, spätere Englisch. Die Funktion nimmt also nicht einfach die erste Sprache, die sie findet.

Probieren Sie es mit diesen drei Nachrichten selbst aus:

Fehler behandeln

Erscheint eine Fehlermeldung, kann das folgende Gründe haben:

• zu lange Texte
• Werte, die kein Text sind
• ein überschrittenes Tageskontingent

Leere Eingaben gesondert behandeln

Nicht jede Zeile enthält eine Nachricht. Bei einer leeren Zeichenfolge gibt die Funktion selbst eine leere Zeichenfolge zurück. Heikler sind Zellen, in denen nur Leerzeichen stehen. Sie sehen leer aus, sind es aber nicht.

Mit GLÄTTEN fangen Sie diesen Fall ab:

=WENN(GLÄTTEN([@Nachricht])=""; ""; SPRACHEERKENNEN([@Nachricht]))

GLÄTTEN entfernt alle Leerzeichen bis auf einzelne zwischen zwei Wörtern. Bleibt danach nichts übrig, gibt WENN eine leere Zeichenfolge aus. Andernfalls ermittelt SPRACHEERKENNEN die Sprache.

Formelfehler mit einem Hinweis kennzeichnen

Soll in der Liste statt eines Fehlerwerts ein Hinweis stehen, ergänzen Sie WENNFEHLER:

=WENN(GLÄTTEN([@Nachricht])=""; ""; WENNFEHLER(SPRACHEERKENNEN([@Nachricht]); "Prüfen"))

Tritt ein Fehler auf, zeigt die Zelle „Prüfen“. Nutzen Sie WENNFEHLER erst, wenn Sie alle Tabellen eingerichtet und getestet haben. Solange Sie noch einrichten, verdeckt Prüfen die Ursache: Sie sehen nicht, ob die Eingabe nicht stimmt oder der Dienst streikt.

Passend zur Ursache vorgehen

• Steht in der Nachrichtenspalte eine Zahl, wurde beim Export womöglich etwas falsch übertragen.
• Ist ein Text zu lang, empfiehlt Microsoft, ihn zu kürzen. Bewahren Sie das Original dann an anderer Stelle auf.
• Ist das Tageskontingent erschöpft, hilft nur Warten. Wann es zurückgesetzt wird, verrät die Funktionshilfe nicht.
• Bei #VERBINDEN! prüfen Sie zuerst die Internetverbindung. Danach lassen Sie die Zelle über das Fehlermenü neu berechnen oder klicken in die Bearbeitungsleiste und drücken die Eingabetaste.