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

Tabellen verknüpfend nutzen

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.

Wir können die Formel auch etwas verständlicher (be)schreiben, indem wir LET(Bezeichnung1; Wert1OderBerechnung1; Bezeichnung2OderBerechnung2; Wert2OderBerechnung2; …3; …3; …) schreiben, wie folgt (wobei die Zeilenumbrüche unnötig sind und hier nur der verbesserten Lesbarkeit dienen sollen):

=LET(
 ZellenWert; Sammeldaten!B2;
 ÜbersetzungsSuchBereich; Datenwertübersetzung!$B$5:$G$5;
 ÜbersetzungGefundenBereich; Datenwertübersetzung!$B$6:$G$6;
 ErgebnisFallsNichtsGefunden; "?";
 XVERWEIS(ZellenWert; ÜbersetzungsSuchBereich; ÜbersetzungGefundenBereich; ErgebnisFallsNichtsGefunden)
)

Fortlaufende Beobachtungen mit XVERWEIS() auf die Ansiedlungsstätte

📖    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 Beispieltabelle 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

Anhand eines gemeinsam gewählten Datenschlüssels, den wir einzig in Ansiedlungsstätte festlegen, können wir diesen wiederum in einer zweiten Tabelle Beobachtungen_fortlaufend verwenden, um verschiedenste Beobachtungen fortlaufend über die Zeit zu beschreiben, und in dieser können wir nach Belieben Spalten aus Ansiedlungen (=Ortsbeschreibung und eingepflanzte Art) zusammenholen, und mit den eigentlichen Beobachtungen weiterführen:

	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, 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.

Herbarverknüpfungen zweier verschiedener Datensätze neu zuordnen über XVERWEIS()

📖 – Siehe auch das Hilfsbeispiel Datei:Beispieldaten – Herbar-Verknüpfungen eines Datensatzes einem anderen neu zuordnen, über XVERWEIS.xlsx

Hierbei wollen wir die Verknüpfungen des JACQ-Herbarbeleges finden, für Datensätze von denen wir eine übereinstimmende Akzessionsnummern haben, und diese gefundenen Herbarbelegdaten sollen dem WIPs-Testdatensatz neu zugeordnet werden – über XVERWEIS kann jeder beliebige Suchwert abgefragt werden – wir verwenden also die Akzessionsnummer.

• Im JACQ Datensatz ist die Akzessionsnummer irgendwo im Textfeld »Anmerkungen«, und im WIPs-Testdatensatz ist sie im Feld »Akzessionsnummer«
• Als Suchbereich (Suchmatrix) in XVERWEIS verwenden wir den JACQ Datensatz, das Feld »Anmerkungen«, in der irgendwo der Suchwert zu finden sei
• Als Rückgabebereich (Rückgabematrix) in XVERWEIS verwenden wir ebenso den JACQ Datensatz, das Feld »Stabiler Identifier«, was die Herbelegverknüpfung hat
• Den Suchtextwert allerdings für XVERWEIS stellen wir so zusammen, daß wir die Platzhaltersuche nutzen können, was vermittels Sternchen-Suche »*001-40-14-10*« erfolgt, und wir verwenden den WIPs-Testdatensatz, siehe Abbildung:

Umrechnung Gradzahl° Minuten′ Sekunden″ → Dezimalkoordinate

📖    Siehe auch die Formelsammlung in Datei:Hilfsformeln zur Koordinatenumwandlung (Englisch und Deutsch).xlsx. – Zu beachten sei hier, 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..

Breitengrad (…°…′…″) Breitengrad (dezimal) 52°24′48″N 52,4133333333333N

(die Gesamtformel zur Umrechnung des A2-Wertes, die in B2 einzutragen wäre, findet sich in der Zusammenfassung)

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) …

Siehe auch die Formelsammlung in Datei:Hilfsformeln zur Koordinatenumwandlung (Englisch und Deutsch).xlsx.

=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 für Kommazahl (europäische Länder):

=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 für englische Punkt-„Komma“-Zahl (englischsprachige Länder):

=WENN( ISTLEER(A2); ""; WECHSELN( 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″

📖    Siehe auch die Formelsammlung in Datei:Hilfsformeln zur Koordinatenumwandlung (Englisch und Deutsch).xlsx – 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";"");",").