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% | … |
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%) |
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 …
Bericht
|
… genauer in Zelle
=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 | … | |
| … | … | … | … | … |
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 | … | |
| … | … | … | … | … | … | … | … | … |
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, verhindert, daß man händisch Daten übermäßig vielfacht, und sich Dokumentationslücken, Verfehlungen und 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
°istUNIZEICHEN(176) - Minutenzeichen
′istUNIZEICHEN(8242) - Sekundenzeichen
″istUNIZEICHEN(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 textformatiertGANZZAHL(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,2256160Nungeachtet der Himmelsrichtungen (N, S, O, W) in eine Zahl verrechnen lassen zu können, SOLLTE der betreffende Text ersetzend bereinigt werden, z.B. vermittelsWECHSELN(…;"N";"")oder ausdrücklich als Zahlenwert formatieren vermittelsZAHLENWERT(WECHSELN(…;"N";"");",").
