Archiv des LibreOffice- und OpenOffice.org-Wiki

[ÜberSicht] [TitelIndex] [WortIndex] [SeiteFinden

Die Funktion Einfügen → Namen → festlegen kann nicht nur dafür benutzt werden, einer Zelle oder einem Zellbereich einen Namen zuzuweisen. Statt einer Zelladresse kann man in einem solchen Namen auch eine Formel speichern.

Wozu soll man eine Formel mit einem Namen belegen?

Es gibt 2 gute Gründe dafür: zum einen kann man damit die Übersichtlichkeit von Formeln bedeutend verbessern, zum anderen kann man Änderungen zentral vornehmen, ohne die betroffenen Zellen suchen und markieren zu müssen.

1. Beschreibung des Beispiels

Eine importierte Tabelle liefert unter anderem eine Spalte (Spalte A), deren Zellen einen klar strukturierten Inhalt haben:
Alle Zellen enthalten eine Zeichenkette, die durch Punkte in drei oder vier Teilketten unterteilt ist.

Aus dieser Spalte sollen (in Spalte B)die Zeichen zwischen dem zweiten und dritten Punkt herausgefiltert werden, bzw. vom zweiten Punkt bis zum Ende, falls es keinen dritten Punkt gibt.

Nam0.gif

Die Formel, die dazu benötigt wird, ist beinahe 300 Zeichen lang und entsprechend unübersichtlich:

=WENN( (ISTFEHLER( (FINDEN( (".";A11;FINDEN( (".";A11;FINDEN( (".";A11)+1)+1));TEIL( (A11;FINDEN( (".";A11;FINDEN( (".";A11)+1)+1;LÄNGE( (A11)-FINDEN( (".";A11;FINDEN( (".";A11)+1));TEIL( (A11;FINDEN( (".";A11;FINDEN( (".";A11)+1)+1;FINDEN( (".";A11;FINDEN( (".";A11;FINDEN( (".";A11)+1)+1)-FINDEN( (".";A11;FINDEN( (".";A11)+1)-1))

Daraus machen wir im folgenden:

WENN(ISTFEHLER(Punkt3);BisEnde;BisPunkt3)

1.1. Zellbezüge

Wenn in einer Formel ein direkter Zellbezug enthalten ist, also in der Art: A1, so wird daraus eine absolute Adresse, wenn man die Formel mit einem Namen speichert. Man könnte sie also nicht einfach kopieren. Daher muss man eine andere Schreibweise für Zellbezüge suchen, diese Möglichkeit liefert die Kombination von ADRESSE() und INDIREKT().

Mit ADRESSE() wird dabei eine Adresse ermittelt, deren Spalten- und Zeilennummer bekannt ist und mit INDIREKT(ADRESSE()) wird der Inhalt dieser Zelle gelesen. In unserem Beispiel soll die Formelspalte rechts neben der Spalte mit den zu bearbeitenden Zeichenketten stehen. Also hat die Zelle, auf die sich die Formel bezieht, die gleiche Zeilennummer wie die Formelzelle selbst und eine in Bezug zur Formelzelle um 1 zu vermindernde Spaltennummer. Die aktuelle Zeilennummer ermittelt die Funktion ZEILE(), die aktuelle Spaltennummer die Funktion SPALTE().

=ADRESSE(ZEILE();SPALTE()-1)

Wenn in B1 steht: =A1, so liefert das den gleichen Wert wie

INDIREKT(ADRESSE(ZEILE();SPALTE()-1))

Der erste Formelname, den wir erfassen, heisst OriginalNummer und hat den Inhalt:

INDIREKT(ADRESSE(ZEILE();SPALTE()-1))

Nam1.png

Immer wenn sich die Formel in Spalte B auf den Inhalt der entsprechenden Zelle aus Spalte A bezieht, wird statt der Zelladresse der Name OriginalNummer verwendet.1 Diese Formel kann problemlos nach unten kopiert werden und bezieht sich in jeder Zeile auf die richtige Zelle.2

1.2. Die Punkte lokalisieren

Wir wissen, dass zwei oder drei Punkte in der Zeichenkette vorkommen können. Die Position des ersten Punktes finden wir mit FINDEN(), wobei ab dem Anfang der Zeichenkette geprüft wird, für den zweiten Punkt wird erst nach der Fundstelle des ersten Punktes geprüft, für den dritten nach der Fundstelle des zweiten Punktes. Daraus leiten wir drei Formeln ab:

Formel Punkt1:

FINDEN(".";OriginalNummer)

Formel Punkt2:

FINDEN(".";OriginalNummer;Punkt1+1)

Formel Punkt3:

FINDEN(".";OriginalNummer;Punkt2+1)

1.3. Die Teil-Zeichenkette zwischen dem zweiten und dritten Punkt

Diese Zeichenkette fängt eine Stelle nach Punkt2 an. Für die Länge der Zeichenkette muss die Differenz zwischen der Fundstelle des dritten und zweiten Punktes abzüglich 1 (der Punkt selbst soll ja nicht in die Teilkette übernommen werden) ermittelt werden.

Formel BisPunkt3:

TEIL(OriginalNummer;Punkt2+1;Punkt3-Punkt2-1)

1.4. Die Teil-Zeichenkette nach dem zweiten Punkt bis zum Ende

Auch diese Zeichenkette fängt eine Stelle nach Punkt2 an. Für die Länge der Zeichenkette muss die Differenz zwischen dem Ende der Zeichenkette (ermitteln mit LÄNGE) und der Stelle nach dem zweiten Punkt ermittelt werden.

Formel BisEnde:

TEIL(OriginalNummer;Punkt2+1;LÄNGE(OriginalNummer)-Punkt2)

1.5. Zusammensetzen der Formel

Wenn es keinen dritten Punkt gibt, liefert die Formel Punkt3 den Fehlerwert #NV. Darauf baut die endgültige Formel ihre Prüfung auf:

=WENN(ISTFEHLER(Punkt3);BisEnde;BisPunkt3)

2. Ändern der Formel

Angenommen, man stellt fest, dass man den Formelnamen OriginalNummer auch noch in anderen Spalten verwenden will, dann muss eine halbabsolute Adressierung gefunden werden, damit sich die Formel stets auf Spalte A bezieht.

Also ersetzt man in der Formel OriginalNummer den Ausdruck SPALTE()-1 durch den Ausdruck SPALTE()-(SPALTE()-1)

Dazu einfach aufrufen: Einfügen → Namen → Festlegen, den Namen OriginalNummer anklicken, die Formel korrigieren und Ändern drücken.

Aenderungen in benannten Formeln wirken sich unmittelbar in allen Zellen aus, in denen die Formel benutzt wird.

3. Einschränkungen, Fehlercode 512

Die Schreibweise von Zellbezügen in der Form INDIREKT(ADRESSE(ZEILE();SPALTE()-1)) macht die Gesamtformel intern viel länger als dies mit der üblichen Schreibweise der Fall wäre.

Aus:

=WENN( (ISTFEHLER( (FINDEN( (".";A11;FINDEN( (".";A11;FINDEN( (".";A11)+1)+1));TEIL( (A11;FINDEN( (".";A11;FINDEN( (".";A11)+1)+1;LÄNGE( (A11)-FINDEN( (".";A11;FINDEN( (".";A11)+1));TEIL( (A11;FINDEN( (".";A11;FINDEN( (".";A11)+1)+1;FINDEN( (".";A11;FINDEN( (".";A11;FINDEN( (".";A11)+1)+1)-FINDEN( (".";A11;FINDEN( (".";A11)+1)-1))

wird:

=WENN( ISTFEHLER( FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1)))+1)+1));TEIL( INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1)))+1)+1;LÄNGE( INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1)))-FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1)))+1));TEIL( INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1)))+1)+1;FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1)))+1)+1)-FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1));FINDEN( ".";INDIREKT( ADRESSE( ZEILE( );SPALTE( )-1)))+1)-1))

Dadurch kommt man schneller an die zulässige Gesamtlänge von Formeln und läuft Gefahr, den Fehlercode 512 zu erhalten. Wenn man trotzdem mit Formelnamen arbeiten möchte, muss man das Ganze in Teilschritten lösen und Hilfsspalten einfügen.

4. Siehe auch


KategorieCalc

  1. Wenn man einen Formelnamen benutzt, muss die Eingabe mit 2x Enter abgeschlossen werden. (1)

  2. Wenn eine Zelladresse, wie sie jetzt in OriginalNummer gespeichert ist, auch noch in anderen Spalten benutzt werden und sich dabei immer auf die gleiche Ausgangsspalte (A) beziehen soll, muss man einen anderen Spaltenbezug finden, als konstant: SPALTE()-1. Zum Beispiel, indem man mit WENN() prüft, in welcher Spalte sich die aktuelle Formel befindet und dann eine unterschiedliche Anzahl Spalten abzieht oder hinzuzählt. Oder indem man eine Verweistabelle erfasst, aus der man sich die gerade benötigte Anzahl Spalten und/oder Zeilen mit SVERWEIS() holt, um die die gesuchte Zelle gegenüber der Formelzelle verschoben ist. (2)


LizenzBedingungen | AnbieterKennzeichnung | DatenschutzErklärung | Stand: 2013-04-28