Tipps und Tricks
 
1.   Darstellung der Zeiten in der Form ...42 Std 0m 25s...
2.   Berechnung von Zeitunterschieden und Konvertierung zu Unterrichtseinheiten
3.   Tipphilfe bei Datumsfeldern
4.   Jede 2te Zeile einer Tabelle hervorheben
5.   SummeWenn I
6.   ZählenWenn II
7.   String trennen
8.   x Monate zu einem Datum zählen
9.   Datumswerte aus mehreren Tabellenzellen zusammenfassend eintragen
10. Verketten und führende Nullen
11. Laufende Nummer – aber nur wenn Eintrag vorhanden
12. Den Namen des Tabellenblattes in eine Zelle eintragen.
13. Zahl in Text umwandeln – 7231 -> sieben-zwei-drei-eins -
14. Summe wenn zwei Bedingungen erfüllt sind
15. Berechnung des Unterschieds zweier Datumsangaben in Jahren, Monaten, Tage
16. Wert aus der letzten Zeile auslesen
17. Ermittlung des Schnittpunktes
18. Summen bei zwei Bedingungen
19. Ermittlung eines best. Tages der vor einem Datum liegt.
20. Vorlagenanwendung bei bedingten Formeln und Sverweis

21. Separieren einzelner Stellen aus einem String

 
 

1

Darstellung der Zeiten in der Form ...42 Std 0m 25s...


 


 




Format der Zelle: [h]\Std m\m s\s (genau so eingeben)

oben

2

Berechnung von Zeitunterschieden und Konvertierung zu Unterrichtseinheiten



In Spalte F wird E6 – D6....... errechnet

mit dem Ergebnis [ 05:20:00 ].............
Eine so errechnete Zeit ganze kann zu Unterrichtsstunden (45 Minuten) umgerechnet werden:
=(((STUNDE(F6)*60))+MINUTE(F6))/45

Das Ergebnis steht in der Spalte Unterrichtseinheiten.
5 Stunden und 20 Minuten entsprechen demnach 7,11 Unterrichtsstunden a´45 Minuten.
Zur Zellformatierung:

Für die Zellen „Von“ und „Bis“ lautet der Formatierungscode: HH:MM:SS
Für die Zellen Stunden und Unterrichtseinheiten lautet sie ZahlStandard mit 2 Nachkommastellen.

oben

3

Tipphilfe bei Datumsfeldern

In ein Datumsfeld braucht für ein Datum des laufenden Jahres nur der Tag, der Punkt und der Monat ( 3.2) eingetippt werden. Sobald die Zelle verlassen wird, wird sie automatisch mit dem Jahr aufgefüllt, sogar der Punkt nach dem Monat wird eingefügt (also in unserem Beispiel 3.2.2004 oder 3.2.04 je nach Einstellung).

oben

4


Jede 2te Zeile einer Tabelle hervorheben


Um in einer Tabelle jede 2te Zeile farblich hervor zu heben, kann so vorgegangen werden:


(Die 2 nach dem Semikolon bedeutet jede 2. Zeile, eine 3 jede 3. etc.)

  1. Erstellen einer Zellvorlage, deren Hintergrund eine Farbe zugewiesen wird.

  2. Über „Format“ „bedingte Formatierung“ bei „Formel ist“ eingeben:
    WENN(REST(ZEILE(A1);2)=0), und die eben erstellte oder eine vorhandene Zellvorlage auswählen und mit OK abschließen

  3. Dann die eben formatierte Zelle kopieren (im Beispiel A1, wenn anders, dann in Formel anpassen) (in Zelle klicken, rechte Maustaste, „Kopieren“ wählen

  4. Den ganzen Bereich der Tabelle markieren, der die farbliche Hervorhebung bekommen soll und „Bearbeiten“ „Inhalte einfügen“ wählen

  5. Bei „Auswahl“ alles weg klicken bis auf „Formate“ und mit OK beenden

Achtung: Sind in den Zellen bereits Eintragungen vorhanden gewesen, muss das (Zahlen)Format in den entsprechenden Zellen möglicherweise wieder angepasst werden.

Praktisch: Sollten später neue Zeilen eingefügt werden, passt sich die zeilenweise Hervorhebung automatisch an.

oben

5

SummeWenn I

Die Formel

SUMMEWENN(A1:A10;"x";B1:B10)

summiert alle Werte die im Bereich A1 bis A10 stehen, wenn ein „x“ in B1 bis B10 steht (wird zeilenweise geprüft).

oben

6

ZählenWenn II
 

Die Funktion „ZÄHLENWENN“ kann erweitert werden so dass auch mit Teilen des gesuchten Strings (x) zu rechnen ist.

ZÄHLENWENN(A1:A10;".*gesuchter (Teil)wert.*")

also vor dem gesuchten Wort etc. ein .* und danach.

Hinweis: Unter EXTRAS OPTIONEN TABELLENDOKUMENT BERECHNEN muss die Option Reguläre Ausdrücke in Formeln ermöglichen aktiviert sein.

 

oben

7

String trennen

Strings in einer Tabellenzelle (z. B. Müller,Erna) können mit folgenden Formeln nach Name und Vorname in 2 verschiedenen Tabellenzellen eingetragen werden:

Angenommen, der String (Müller,Erna) steht in Tabellenzelle B5

Für den Namen : LINKS(B5;(FINDEN(",";B5)-1)) Ergebnis: Müller

Für den Vornamen : TEIL(B5;(FINDEN(",";B5)+1);LÄNGE(B5)-FINDEN(",";B5)) Ergebnis: Erna

oben

8

x Monate zu einem Datum zählen

Diese Formel einfügen.

EDATUM("1.1.2004";"4")
die "4" nach dem Semikolon bedeutet: 4 Monate zum Datum zählen

Statt des absoluten Datums kann auch ein Zellbezug stehen
EDATUM((A1);"4")

oder Zellbezüge für Beides
EDATUM((A1);B3)

oben

9

Datumswerte aus mehreren Tabellenzellen zusammenfassend eintragen

Aus Einträgen in mehreren Tabellenzellen kann ein Datumswert generiert werden.


Formel in E3: =Datum(D3;C3;B3)

Generell: =Datum(Jahr,Monat,Tag)

oben

10

Verketten und führende Nullen

Man kann Verketten und dabei führende Nullen berücksichtigen, so dass der verkettete Wert rechtsbündig ist.

Beispiel:


FORMEL

=VERKETTEN("ART-";TEXT(B4;"0000000"))

oben

11

Laufende Nummer – aber nur wenn Eintrag vorhanden

 

Wenn in einer Tabelle eine fortlaufende Nummerierung der Spalteneinträge vorgenommen werden soll, kann dies mit der folgenden Formel erreicht werden.

=WENN(C3="";"";ANZAHL2($C$3:C3))



Quelle: Forum http://de.openoffice.info

Beispiel: In Spalte C stehen in einigen Zellen Namen, die in Spalte A fortlaufend nummeriert werden sollen. Aber nur dann, wenn in der Zelle etwas steht.

 

oben

12 

Den Namen des Tabellenblattes in eine Zelle eintragen.

Bei einer Namensänderung des Blattes wird der aktualisierte Blattname mit der Tastenkombination [Strg] [Umschalt][F9] angezeigt.

=RECHTS(ZELLE("FILENAME");LÄNGE(ZELLE("FILENAME"))-SUCHEN("#";ZELLE("FILENAME");1)-1)

Quelle: Forum http://de.openoffice.info

oben

13

Zahl in Text umwandeln – 7231 -> sieben-zwei-drei-eins -

Function ZIW (a)
a = TRIM(STR(a))
k = ""
For i = 1 to LEN(a)
   j = MID(a,i,1)
   Select Case j
      Case "1"
         k = k & "eins-"
      Case "2"
         k = k & "zwei-"
      Case "3"
         k = k & "drei-"
      Case "4"
         k = k & "vier-"
      Case "5"
         k = k & "fünf-"
      Case "6"
         k = k & "sechs-"
      Case "7"
         k = k & "sieben-"
      Case "8"
         k = k & "acht-"
      Case "9"
         k = k & "neun-"
      Case "0"
         k = k & "null-"
   End Select
Next i
ZIW = LEFT(k, LEN(k)-1)
End Function

Erklärung: Zunächst die komplette Funktion in eine Bibliothek kopieren, z.B. die StandardBibliothek von OOo:
Extras>Makros>Makro...
im Dialogfenster links den Ordner Standard suchen da sollte schon Modul1 drunterstehen
Modul1 markieren, dann werden im rechten Fensterteil die enthaltenen Makros sichtbar
eines davon markieren und Bearbeiten-Schaltfläche drücken
jetzt den kompletten Text der Fubction da rein kopieren, vorhandene Makros nicht löschen einfach dazu kopieren,
das Basic-IDE Fenster schließen,

auf dem Tabellenblatt eine Zelle markieren und das hineinschreiben:

=ZIW(<Zelladresse>)

z.b. wenn 7213 in A1 steht dann in Ergebniszelle schreiben:

=ZIW(A1)

(Quelle: de.openoffice.info: stephan)

 

oben

 

 

 

 

 

 

 

 

 

 

oben

14

Summe wenn zwei Bedingungen erfüllt sind

oben

15

 

Berechnung Unterschied zweier Datumsangaben in Jahre, Monate, Tage

 

In Zelle C3 steht das Geburtsdatum. In Zelle E3 das Bezugsdatum, bspw. Heute()

Folgende Formel errechnet den Unterschied in Jahren, Monaten und Tagen.

 =JAHRE(C3;E3;0)&"Jahre, "&REST(MONATE(C3;E3;0);12)&"Monate, "&E3-EDATUM(C3;MONATE(C3;E3;0))&"Tage"

(Quelle: http://de.openoffice.info: Karolus)

 

oben
     
16

Wert aus der letzten Zeile auslesen

In einer Spalte stehen mehrere Werte untereinander und es kommen neue Werte hinzu. Wie kann der Wert ausgelesen werden, der in der letzten Zeile steht?

2 Lösungen

  1. =INDIREKT(VERKETTEN("B";10000-ANZAHLLEEREZELLEN(B1:B10000)))

    Darauf achten dass die Zellen auch tatsächlich ab B1 beginnen und leere Zellen nicht enthaltenen sind. (Quelle: de.openoffice.info: nevis)
     

  2. =INDIREKT("B"&VERGLEICH(ISTLEER(B1:B10000);B1:B10000;-1)). Leere Werte zwischendurch sind hierbei unerheblich.
    (Quelle: de.openoffice.info: Karolus)

 

oben
17 Ermittlung des Schnittpunktes
 
2 B C D E F G
3
 
Y-Achse
 

 

 

 
4 X-Achse 27 28 29 30 31
5 120 121 122 123 124 125
6 121 122 123 124 125 126
7 122 123 124 125 126 127
8 123 124 125 126 127 128
9 124 125 126 127 128 129
10 125 126 127 128 129 130
11 126 127 128 129 130 131
12 127 128 129 130 131 132
13 128 129 130 131 132 133
14 129 130 131 132 133 134

 

 

 

 

 

 

 

 

 
Wert auf der X-Achse
 
127 <- Eingabe
 

 

 

 

 

 

 

 

 

 
Wert auf der Y-Achse
 
29 <- Eingabe
 

 

 

 

 

 

 

 

 

 
Schnittpunkt
 
130 Wird errechnet
 

 

 

 

 

 

 

 

 
Formel          =SVERWEIS(E17;$B$5:$G$14;VERGLEICH(E19;$B$4:$G$4;0);0)
oben
18

Summen bei zwei Bedingungen

 

 
19

Ermittlung eines best. Tages der vor einem Datum liegt.


Wenn Du von einem beliebigen Datum die Wochentagsnummer dieses Datums abziehst, kommst Du immer zum letzten Samstag, der vor diesem Datum liegt:
=Datum-Wochentag(Datum)
also kommst Du so:
=Datum-Wochentag(Datum)+2
immer auf den letzten Montag, der vor dem gesuchten Datum liegt oder auf genau dieses Datum, falls 'Datum' zufällig grad ein Montag ist.“


Quelle: (www. de.openoffice.info: Eia )

 
20

Vorlagenanwendung bei bedingten Formeln und Sverweis

 

 
21

Separieren einzelner Stellen aus einem String

 
   

A l l e    A n g a b e n   o h n e    G e w ä h r

 

 


oben