Hilfe:Nützliche Datenumwandlungen in Tabellenblättern (Excel, LibreOffice)

Einzelwerte XVERWEIS() selbtätig übersetzen lassen

📖    In der gedachten Beispieltabelle „Sammeldaten“ soll die Spalte „geschätzte Hangneigung“ inhaltlich so bleiben, aber für einen Bericht sollen die Werte selbtätig in festgelegte Werte übersetzt werden – die eingetragene Werte seien diese:

	…	geschätzte Hangneigung	…
	…	0-5%	…
	…	11-20%	…
	…	0-5%	…

Sammeldaten

In einer gesonderten Übersetzungstabelle ordnet man die in eigenen Daten vorhandenen Werte in eine Zeile ein, und darunter gewissermaßen die gewünschten Übersetzungswerte zugeordnet drunter, die man übersetzen lassen will, dies können auch Übersetzungen in andere Sprachen sein, je nach Belieben:

	Feldbeschreibung	Wert1	Wert2	Wert3	Wert4	Wert5	Wert6	Wert7	Wert8	…
	exposition	N	S	E	W	NE	NW	SE	SW
	vorherrschende Himmelsrichtung (ersetzt)	N	S	O	W	NO	NW	SO	SW
	Hangneigung	Keine Angabe	0-5%	6-10%	11-20%	21-30%	>30%
	Hangneigung (ersetzt)	n.a. (nichts angegeben)	Eben (0-5%)	Wellig (6-10%)	Hügelig (11-20%)	Moderat (21-30%)	Steil (>30%)
	Slope (return values)	n.a. (not available)	level (0-5%)	wavy (6-10%)	rolling (11-20%)	moderate (21-30%)	steep (>30%)

Datenwertübersetzung

Das selbtätige Suchen und zugeordnete Übersetzen kann in XVERWEIS() allgemein so erwirkt werden:

XVERWEIS(HIERWERT-suchen; Übersetzung.SUCH-Bereich; Übersetzung.GEFUNDEN-Bereich(=Rückgabe); falls-nich-gefunden)

… und in der Bericht-Beispieltabelle …

… Hangneigung … … Eben (0-5%) … … Hügelig (11-20%) … … Eben (0-5%) … Bericht

… genauer in Zelle Bericht.B2 kommt die Formel dann zur Wirkung, beispielsweise notfalls mit unübersetzten Ursprungswerten, oder notfalls stattdessen bewußt ein ? (Fragezeichen um Übersetzungsfehler sichtbarer zu zeigen): =XVERWEIS(Sammeldaten!B2; Datenwertübersetzung!$B$5:$G$5; Datenwertübersetzung!$B$6:$G$6; Sammeldaten!B2) =XVERWEIS(Sammeldaten!B2; Datenwertübersetzung!$B$5:$G$5; Datenwertübersetzung!$B$6:$G$6; "?")

… und ergänzen kann man die Formel umschließend noch mit einer ISTLEER(…)-Prüfung, um Leerwerte fehlerfrei auch wieder Leerwerte sein zu lassen (sonst würde XVERWEIS() 0 ergeben):

=WENN(ISTLEER(Sammeldaten!B2);""; XVERWEIS(…) )

Dasselbe läßt sich bewirken für englische Übersetzungen.

---

📖    Weitergedacht läßt sich XVERWEIS(…) beim Zusammenschluß von Einzeltabellen anwenden – z.B. für Zeitreihen, wo es eine Stätte, Örtlichkeit gibt, die ja am Ort verbleibt, und die Zeitreihe, in der fortlaufende Beobachtungen zum Zeitpunkt-„X“ aufgenommen werden. Ein Beispiel sei die Ansiedlungsstätte einer Art (=Ortsbeschreibung), und die danachfolgende zeitlichen Beobachtungen ihres Gedeihens (=Zeitreihe). Die Tabelle Ansiedlungsstätte legte einen gemeinsamen eineinzigen Datenschlüssel fest, der später Tabellen verknüpfen läßt, und nebenbei beliebige Zusatzeigenschaften (Kennzeichnung, Jahr, Artname, Bundesland, Ortsname, Koordinaten usw.):

	Gemeinsamer Datenschlüssel (eineinzig)	Kennzeichnung	Jahr der Ansiedlung	Artname	…
	POTSD-104; Lysimachia nemorum	POTSD-104	2019	Lysimachia nemorum	…
	POTSD-106; Scabiosa canescens	POTSD-106	2019	Scabiosa canescens	…
	POTSD-112; Arnoseris minima	POTSD-112	2020	Arnoseris minima	…
	…	…	…	…	…

Ansiedlungsstätte

In einer zweiten Tabelle Beobachtungen_fortlaufend werden die Beobachtungen über die Zeit beschrieben, und anhand des gemeinsamen Datenschlüssels können Eigenschaften der Ansiedlungsstätte mit den fortlaufenden Beobachtungsereignissen nach Belieben verbunden werden:

	Gemeinsamer Datenschlüssel (Ansiedlunggsstätte)	Kennzeichnung (Ansiedlungsstätte)	Schutzstatus (Ansiedlungsstätte)	Art der Maßnahme (Ansiedlungsstätte)	Art (Ansiedlungsstätte)	Datum (Beobachtung)	Anzahl reproduktiver Einheiten	Anzahl vegetativer Einheiten	…
	POTSD-104; Lysimachia nemorum	POTSD-104	NSG	Wiederansiedlung	Lysimachia nemorum	07.05.2020	13	14	…
	POTSD-106; Scabiosa canescens	POTSD-106	NSG	Populationsstützung	Scabiosa canescens	21.07.2020	0	0	…
	…	…	…	…	…	…	…	…	…

Beobachtungen_fortlaufend

Beide Tabellen kann man nun über XVERWEIS zusammenwerkeln, in Zelle B2 schreibt man sinngemäß wie folgt:

=XVERWEIS(Datenschlüssel; Ansiedlungsstätte.Datenschlüssel; Ansiedlungsstätte.gewünschte-Rückgabe-Spalte; falls-nicht-gefunden-Hilfetext)
=XVERWEIS($A2; Ansiedlungsstätte!$A$2:$A$100; Ansiedlungsstätte!$B$2:$B$100; 'Hilfstabelle-Meldungen'!$A$2)

Dieses Absondern sich wiederholender Informationen in gesonderte Tabellen, und das über XVERWEIS() verbindenede wieder zusammenfügen, daß man händisch Daten übermäßig vielfacht, und sich Dokumentationslücken oder Verfehlungen, Kopierfehler einschleichen können. In einer Zusatztabelle „Auswertungen“ könnte man dann mit einer datenauswertenden Gliedertabelle/Pivot-Tabelle sich aus den Beobachtungen gewünschte Auswertungen zusammenfügen, je nachdem, welcher Forschungsfrage man nachgehen will. Auf diese Weise bekommen der Ort, die Beobachtung, und die nachherige Auswertung ihren eigenen Tabellenbereich. Die Einzeltabellen können ihrerseits auch um weitere Spalten ergänzt werden.

Umrechnung Gradzahl° Minuten′ Sekunden″ → Dezimalkoordinate

📖    Zu beachten sei, daß die Gradzahl° Minuten′ Sekunden″ maßgeblich die richtigen Zeichen enthalten SOLLTEN, damit wir die Zahlenteile später dann richtig ausrechnen können:

• Gradzeichen ° ist UNIZEICHEN(176)
• Minutenzeichen ′ ist UNIZEICHEN(8242)
• Sekundenzeichen ″ ist UNIZEICHEN(8243)

Die Berechnung ergibt sich sinngemäß aus:

=Gradzahl + Minutenzahl/60 + Sekundenzahl/3600

… siehe auch koordinaten-umrechner.de.

Für die Textteile kann man das Gradzahl-Zeichen ° im Zellentext A2 suchen, davor die Zahl befinden, und diese ausrechnen, und gleichfalls zum Minuten-Zeichen ′ suchen usw., und zum Sekunden-Zeichen ″ usw.. So ergeben sich für die Textstückchen folgende Formellösungen:

Dezimalgrad:

=ZAHLENWERT(TEIL(A2; 1; SUCHEN(UNIZEICHEN(176);A2;1) - LÄNGE(UNIZEICHEN(176))))

Minuten in Dezimalzahl (falls ′ unauffindbar, dann 0 zurückgeben, andernfalls Textbereich finden und Zahl ausrechnen):

=WENN(
  ISTFEHL(SUCHEN(UNIZEICHEN(8242);A2;1));
  0;
  ZAHLENWERT(
    TEIL(A2;
      SUCHEN(UNIZEICHEN(176);A2;1) + 1;
      SUCHEN(UNIZEICHEN(8242);A2;1) - SUCHEN(UNIZEICHEN(176);A2;1) - LÄNGE(UNIZEICHEN(8242))
    )
  ) / 60
)

… textverdichtet ohne Zeilenumbrüche:

=WENN(ISTFEHL(SUCHEN(UNIZEICHEN(8242);A2;1)); 0; ZAHLENWERT(TEIL(A2; SUCHEN(UNIZEICHEN(176);A2;1)+1; SUCHEN(UNIZEICHEN(8242);A2;1) - SUCHEN(UNIZEICHEN(176);A2;1) - LÄNGE(UNIZEICHEN(8242))))/60)

Sekunden in Dezimalzahl: (falls ′ unauffindbar, dann 0 zurückgeben, andernfalls Textbereich finden und Zahl ausrechnen)

=WENN(
  ISTFEHL(SUCHEN(UNIZEICHEN(8243);A2;1));
  0;
  ZAHLENWERT(
    TEIL(A2;
      SUCHEN(UNIZEICHEN(8242);A2;1) + 1;
      SUCHEN(UNIZEICHEN(8243);A2;1) - SUCHEN(UNIZEICHEN(8242);A2;1) - LÄNGE(UNIZEICHEN(8243))
    )
  ) / 3600
)

… textverdichtet ohne Zeilenumbrüche:

=WENN(ISTFEHL(SUCHEN(UNIZEICHEN(8243);A2;1));0;ZAHLENWERT(TEIL(A2; SUCHEN(UNIZEICHEN(8242);A2;1)+1; SUCHEN(UNIZEICHEN(8243);A2;1) - SUCHEN(UNIZEICHEN(8242);A2;1) - LÄNGE(UNIZEICHEN(8243))))/3600)

Zusammenfassend: Man kann diese Drei Formeln dann zusammenfügen, und noch eine ISTLEER()-Prüfung voranstellen, falls keine Zellwerte vorhanden sind, und endlich ergänzen wir noch die Himmelsrichtung, vermittels & "N" (siehe Ausklappkasten rechts) …

=WENN(
  ISTLEER(A2);
  "";
  ZAHLENWERT(TEIL(A2; 1; SUCHEN(UNIZEICHEN(176);A2;1) - LÄNGE(UNIZEICHEN(176))))
  +
  WENN(
    ISTFEHL(SUCHEN(UNIZEICHEN(8242);A2;1));
    0;
    ZAHLENWERT(
      TEIL(A2;
        SUCHEN(UNIZEICHEN(176);A2;1)  + 1;
        SUCHEN(UNIZEICHEN(8242);A2;1) - SUCHEN(UNIZEICHEN(176);A2;1) - LÄNGE(UNIZEICHEN(8242))
      )
    ) / 60
  )
  +
  WENN(
    ISTFEHL(SUCHEN(UNIZEICHEN(8243);A2;1));
    0;
    ZAHLENWERT(
      TEIL(A2;
        SUCHEN(UNIZEICHEN(8242);A2;1) + 1;
        SUCHEN(UNIZEICHEN(8243);A2;1) - SUCHEN(UNIZEICHEN(8242);A2;1) - LÄNGE(UNIZEICHEN(8243))
      )
    ) / 3600
  )
) & "N"

… oder dichter verfügt, ohne Zeilenumbrüche:

=WENN( ISTLEER(A2); ""; ZAHLENWERT(TEIL(A2; 1; SUCHEN(UNIZEICHEN(176);A2;1) - LÄNGE(UNIZEICHEN(176)))) + WENN( ISTFEHL(SUCHEN(UNIZEICHEN(8242);A2;1)); 0; ZAHLENWERT( TEIL(A2; SUCHEN(UNIZEICHEN(176);A2;1) + 1; SUCHEN(UNIZEICHEN(8242);A2;1) - SUCHEN(UNIZEICHEN(176);A2;1) - LÄNGE(UNIZEICHEN(8242)) ) ) / 60 ) + WENN( ISTFEHL(SUCHEN(UNIZEICHEN(8243);A2;1)); 0; ZAHLENWERT( TEIL(A2; SUCHEN(UNIZEICHEN(8242);A2;1) + 1; SUCHEN(UNIZEICHEN(8243);A2;1) - SUCHEN(UNIZEICHEN(8242);A2;1) - LÄNGE(UNIZEICHEN(8243)) ) ) / 3600 ) ) & "N"

Vorarbeiten/Nacharbeiten:

• für englische Daten wird das Punkt-Komma benötigt, was man mit der Formel =WECHSELN(…; ","; ".") ersetzen lassen kann, und umgekehrt in mitteleuropäische Strich-Komma mit der Formel =WECHSELN(…; "."; ",")

Umrechnung Dezimalkoordinate → Gradzahl° Minuten′ Sekunden″

📖    Aus der Dezimalkoordinate 54,2256160 (festgelegt als Zahlen-Datenzelle in A2) berechnet sich die Grad-Minuten-Sekunden Koordinate z.B. 54° 13′ 32,2176000000081″ vermittels der Beispielformel:

=GANZZAHL(A2) & UNIZEICHEN(176)
& " "
& GANZZAHL((A2-GANZZAHL(A2)) * 60) & UNIZEICHEN(8242)
& " "
& TEXT(
  (
    (A2-GANZZAHL(A2)) * 60 - GANZZAHL((A2-GANZZAHL(A2)) * 60)
  ) * 60;
  "0,0000"
) & UNIZEICHEN(8243)

… textverdichtet ohne Zeilenumbrüche:

=GANZZAHL(A2)&UNIZEICHEN(176)&" "&GANZZAHL((A2-GANZZAHL(A2))*60)&UNIZEICHEN(8242)&" "&TEXT(((A2-GANZZAHL(A2))*60-GANZZAHL((A2-GANZZAHL(A2))*60))*60;"0,0000")&UNIZEICHEN(8243)

Beispielformel für Englische Punktzahl:

=GANZZAHL(A2) & UNIZEICHEN(176)
& " "
& GANZZAHL((A2-GANZZAHL(A2)) * 60) & UNIZEICHEN(8242)
& " "
& WECHSELN(
  TEXT(((A2-GANZZAHL(A2)) * 60-GANZZAHL((A2-GANZZAHL(A2)) * 60)) * 60; "0,0000");
  ",";
  "."
) & UNIZEICHEN(8243)

… textverdichtet ohne Zeilenumbrüche:

=GANZZAHL(A2)&UNIZEICHEN(176)&" "&GANZZAHL((A2-GANZZAHL(A2))*60)&UNIZEICHEN(8242)&" "&WECHSELN(TEXT(((A2-GANZZAHL(A2))*60-GANZZAHL((A2-GANZZAHL(A2))*60))*60;"0,0000");",";".")&UNIZEICHEN(8243)

Für einzelne Grad, Minuten, Sekunden sind es die folgenden Formeln:

• nur Grad-Umrechnung: GANZZAHL(A2) oder textformatiert GANZZAHL(A2) & UNIZEICHEN(176) – wobei der Grad-Dezimalrest sich aus (A2-GANZZAHL(A2)) ergibt, mit dem weitergerechnet werden kann
• nur Minuten-Umrechnung, textformatiert GANZZAHL((A2-GANZZAHL(A2)) * 60) & UNIZEICHEN(8242)
• nur Sekunden-Umrechnung, textformatiert: ((A2-GANZZAHL(A2)) * 60 - GANZZAHL((A2-GANZZAHL(A2)) * 60)) * 60 & UNIZEICHEN(8243)

Vorarbeiten/Nacharbeiten:

• Um den Zellentext z.B. 54,2256160N ungeachtet der Himmelsrichtungen (N, S, O, W) in eine Zahl verrechnen lassen zu können, SOLLTE der betreffende Text ersetzend bereinigt werden, z.B. vermittels WECHSELN(…;"N";"") oder ausdrücklich als Zahlenwert formatieren vermittels ZAHLENWERT(WECHSELN(…;"N";"");",").