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

Einzelwerte XVERWEIS() selbtätig übersetzen lassen

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

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

Sammeldaten

In einer gesonderten Übersetzungstabelle hält man die Zuordnungen der vorhandenen und gewünschten Werte vorrätig, die man übersetzen 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 bewirkt man mit der allg. Formel:

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: =XVERWEIS(Sammeldaten!B2; Datenwertübersetzung!$B$5:$G$5; Datenwertübersetzung!$B$6:$G$6; Sammeldaten!B2)

… und notfalls ergänzt man die Formel umschließend noch mit einer ISTLEER(…)-Prüfung:

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

Dasselbe läßt sich bewirken mit englischen Ü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 Daten fortlaufend aufgenommen werden und sich Daten ändern. So ließe sich eine Arten-Spalte, aus der Tabelle Ansiedlungstätte (neben anderen Infos zur Ansiedlungsstätte usw.) mit einer fortlaufenden Tabelle Beobachtungen_fortlaufend in Zusammenhang bringen über sinngemäß:

=XVERWEIS(Datenschlüssel; Ansiedlungsstätte.Datenschlüssel; Ansiedlungsstätte.Art-Spalte (=Rückgabe); falls-nicht-gefunden)

… wobei der Datenschlüssel nur in der Ansiedlungsstätte festgelegt wird, und dieser in anderen Tabellen eingetragen werden kann, um vermittels XVERWEIS(…) beliebige Informationen der Ansiedlungsstätte zusammenzugliedern. Auf diese Weise kann man Ort- und Zeitereignis-Daten sondern und trotzdem zusammen auswerten.

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";"");",").