In den LibreOffice-Tipps erfahren Sie diesmal, wie Sie einen unglaublich hohen Stundenlohn erzielen, Arbeitszeiten runden und eine Formel bequem in viele Zellen kopieren.

Tipp: Calc: Wie hoch ist mein Stundenlohn?

Sie haben in Calc in die Zelle A2 den erhaltenen Lohn von 650,00 Euro und in die Zelle B2 die geleistete Stundenzahl von 8:15 Stunden eingetragen. Errechnen Sie nun in der Zelle C2 mit der Formel

=A2/B2

Ihren Stundenlohn, erleben Sie eine Überraschung: Calc errechnet nämlich einen Traumsalär von 1.890,91 Euro, obwohl die Formel Lohn/Stundenzahl doch das richtige Ergebnis liefern sollte (Abbildung 1). Was ist passiert?

Abbildung 1: Wer wäre nicht glücklich über einen Stundenlohn von fast 2000 Euro. Leider passieren solche Rechenfehler viel zu selten.

Das Problem verursacht die Zelle mit der Stundenzahl. Tippen Sie hier 8:15 ein, verwendet Calc automatisch das Zeitformat HH:MM:SS und weist somit als Eintrag 08:15:00 aus. Klicken Sie auf diese Zelle und wählen Sie aus dem Kontextmenü den Eintrag Direkte Formatierung entfernen, kommt der tatsächlich von Calc eingetragene Wert zum Vorschein – nämlich 0,34375. Der Grund dafür ist, dass Calc Zeitangaben als Bruchteile eines ganzen Tages betrachtet. Steht in Ihrer Tabelle zum Beispiel die Zeitangabe 12:00, rechnet Calc intern mit dem Wert 0,5 – die Software betrachtet 12 Stunden als die Hälfte eines ganzen Tages. Analog dazu entsprechen der Zeitwert von 18:00 der Dezimalzahl 0,75 und der Zeitwert 8:15 der Dezimalzahl 0,34375. Dies erklärt jetzt auch den überhöhten Stundenlohn.

Aber wie erhalten Sie nun den passenden Wert für die Stundenlohnberechnung? Ganz einfach: Sie multiplizieren den Zeitwert mit 24. Als Ergebnis erhalten Sie die vollen Stunden mit dem passenden Dezimalanteil für die Minuten. Aus 8:15 macht die Multiplikation mit 24 den Dezimalwert 8,25. Und damit berechnen Sie problemlos den Stundenlohn. Das richtige Ergebnis von 78,79 Euro erhalten Sie also, wenn Sie in C2 die folgende Formel eintragen:

=A2/(B2*24)

Sie beheben dieses Problem alternativ aber auch mit einer weiteren, nicht minder interessanten, Formel (Abbildung 2). Diese bringt Sie ebenfalls zum richtigen Resultat.

=A2/(((TEXT(LINKS(TEXT(B2;"HH:MM");2);"0"))*1)+((TEXT(RECHTS(TEXT(B2;"HH:MM");2);"0"))*1)/60)

Mit Hilfe der Funktion TEXT(Zahl; Format) bringt Calc die Stunden (8) und die Minuten (15) jeweils in ein zweistelliges Zahlenformat. Aus diesem extrahieren die Funktionen LINKS(“Text”; Zahl) und RECHTS(“Text”; Zahl) die Stunden und Minuten und wandeln diese dann in Stunden um. Mit der so gewonnenen Dezimalzahl errechnen Sie ebenfalls den Stundenlohn. Als Ergebnis erhalten Sie wieder 78,79 Euro.

Abbildung 2: Die Formel ist zwar sehr lang und umständlich, funktioniert aber auch. Wichtig: Unter Kubuntu müssen Sie die englischen Namen der Funktionen verwenden.

Nutzen Sie Kubuntu 11.10, müssen Sie die Formel etwas abändern. Der Grund: Kubuntu kennt die deutschen Namen der Formeln nicht, Sie müssen die englischen Bezeichnungen verwenden:

=A2/(((TEXT(LEFT(TEXT(B2;"HH:MM");2);"0"))*1)+((TEXT(RIGHT(TEXT(B2;"HH:MM");2);"0"))*1)/60)

Tipp: Calc: Wie hoch ist mein Tagesverdienst?

Sie haben sich die geleisteten Arbeitsstunden aufgeschrieben und einen Stundenlohn von 55,00 Euro vereinbart. Nun möchten Sie mit Calc Ihren Gesamtverdienst berechnen. Dazu schreiben Sie in die Zelle A2 den Stundenlohn von 55,00 Euro. In der Zelle B2 erfassen Sie die Stundenzahl: 8:15 Stunden.

Damit Calc das richtige Ergebnis ausweist, tippen Sie in die Ergebniszelle C2 folgende Formel:

=STUNDE(B2)*A2+MINUTE(B2)*A2/60

oder in Kubuntu 11.10

=HOUR(B2)*A2+MINUTE(B2)*A2/60

Die Tabellenfunktion STUNDE() verwandelt den in Textform vorliegenden Stundeneintrag in eine Zeitangabe zurück. Calc gibt die Stunde als ganze Zahl aus, die einen Wert von 0 (0 Uhr) bis 23 (23 Uhr) annehmen kann. Die Tabellenfunktion MINUTE() wandelt eine fortlaufende Zahl hingegen in Minuten um. Die Minute wird dabei als ganze Zahl ausgegeben, die einen Wert von 0 bis 59 annehmen kann. Diese beiden Werte multiplizieren Sie mit dem vereinbarten Stundenlohn der Zelle A2. Das Ergebnis der Minutenberechnung müssen Sie abschließend durch 60 dividieren, um auf einen Stundenlohn zu kommen. Als Gesamtverdienst erhalten Sie 453,75 Euro (Abbildung 3).

Abbildung 3: Mit Hilfe der Funktionen STUNDE() und MINUTE() rechnen Sie einfach den Tagesverdienst aus.

Tipp: Calc: Arbeitszeiten auf zehn Minuten aufrunden

Angenommen, Sie erfassen die Arbeitszeiten zur Lohnberechnung in einer Calc-Tabelle. Die Arbeitszeit berechnet die Software immer im Zehn-Minuten-Takt, wobei der Arbeitgeber jede angebrochene 10-Minuten-Einheit voll bezahlt. In der Tabelle müssen Sie die tägliche Arbeitszeit also immer auf die nächsten zehn Minuten aufrunden.

Für solche Rundungen bieten sich die Funktionen OBERGRENZE(Zahl; Schrittweite) (zum Aufrunden) und UNTERGRENZE(Zahl; Schrittweite) (zum Abrunden) an. Damit runden Sie Zahlen auf ein Vielfaches eines bestimmten Wertes auf und ab. Unter Kubuntu 11.10 heißen die Funktionen CEILING (Obergrenze) und FLOOR (Untergrenze).

Da es sich bei Zeitwerten – wie bereits erwähnt – programmintern um Bruchteile eines ganzen Tages handelt (die Stundenangabe 12:00 entspricht einem halben Tag, also 0,5; die Angabe 6:00 einem Vierteltag, also 0,25 usw.), müssen Sie auf einen Bruchteil des Tages runden. Der Wert dieses Bruchteils richtet sich nach der gewünschten Genauigkeit.

Im Beispiel geht es um 10-Minuten-Einheiten, weshalb Sie auf den Wert 1/(Anzahl der Zehn-Minuten eines Tages), also auf 1/(24*6) oder 1/144 runden müssten. Wären die Abrechnungszeiten Viertelstunden, müssten Sie auf 1/(24*4) oder 1/96 runden etc.

Die Beispieltabelle erfasst nun in Spalte A die Anfangszeit und in Spalte B die Endzeit. In der Spalte C berechnet Calc die auf 10-Minuten gerundete Arbeitszeit, indem es die Differenz der beiden Zellen B2 und A2 mit folgender Formel errechnet:

=OBERGRENZE(B2-A2;1/144)

Unter Kubuntu 11.10 heißt diese Funktion CEILING, analog verwenden Sie FLOOR() für UNTERGRENZE(). Anschließend müssen Sie in der Formelzelle noch ein Uhrzeitformat zuweisen, damit die anstelle eines Dezimalwertes einen Zeitwert anzeigt. Dazu klicken Sie mit der rechten Maustaste auf die Zelle, wählen Zellen formatieren und dann die Kategorie Zeit und das Format 13:37:46 (Abbildung 4).

Abbildung 4: Für das Aufrunden und Abrunden von Werten verwenden Sie zum Beispiel die Funktionen OBERGRENZE() und UNTERGRENZE().

Tipp: Dezimalwerte in Stundenangaben umrechnen

Eine weitere Tabelle berechnet bestimmte Sollstunden mit Hilfe einer Formel. Diese liefert jedoch Dezimalwerte zurück – beispielsweise 5,33 oder 2,16, die Calc nun als Zeitangaben darstellen muss, hier also als 5:20 oder 2:10.

Bekannterweise betrachtet Calc Uhrzeiten als Bruchteile eines ganzen Tages, also Dezimalwerte zwischen 0 und 1. Somit brauchen Sie eine dezimale Stundenangabe wie 5,33 nur durch die Anzahl der Stunden eines Tages – also durch 24 – zu teilen, um den gewünschten Zeitwert zu erhalten. Wenn in Zelle A2 zum Beispiel der Wert 5,33 steht, berechnen Sie mit folgender Formel problemlos den entsprechenden Zeitwert:

=A2/24

Als Ergebnis liefert die Formel zunächst eine Dezimalzahl zurück (0,22); das Umwandeln in eine Zeit entpuppt sich jedoch als reine Formatfrage. Wählen Sie Format / Zellen und wechseln Sie zur Registerkarte Zahlen. In der Kategorie Zeit wählen Sie im Listenfeld Format die gewünschte Darstellungsform, zum Beispiel 13:37:46. Ein Klick auf OK bestätigt die Auswahl.

Dieses Verfahren hat jedoch einen Nachteil: Setzen Sie ein Zahlenformat ein, das nur Stunden und Minuten ausgibt, rundet Calc das Ergebnis nicht korrekt auf die nächste Minute. Stattdessen schneidet es einfach den Sekundenanteil ab und macht so aus 5,33 Stunden den Zeitwert 5:19. Da sich bei sekundengenauer Darstellung 05:19:48 ergibt, müssten Sie den Zeitwert tatsächlich auf 5:20 aufrunden.

In so einem Fall rechnen Sie die dezimale Stundenangabe in Minuten um und runden das Ergebnis auf die nächste ganze Zahl. Um daraus einen typischen Zeitwert zu machen, müssen Sie die errechneten Minuten noch durch die Anzahl der Minuten eines ganzen Tages – also durch 24*60 = 1440 – teilen.

Steht beispielsweise in B2 die dezimale Stundenangabe 0,22, erhalten Sie mit folgender Formel ein korrekt auf die nächste Minute gerundetes Ergebnis:

=RUNDEN(A2*60;0)/1440

Formatieren Sie den daraus resultierenden Dezimalwert wieder als Zeit, ergibt sich 05:20 (Abbildung 5). Unter Kubuntu 11.10 rufen Sie die RUNDEN-Funktion über den englischen Namen ROUND() auf.

Abbildung 5: In einigen Fällen macht es Sinn, einen Wert auf Minuten zu runden und die Sekunden zu entfernen.

Tipp: Calc: Formel in viele Zellen kopieren

Eine Formel in viele Zellen einer Spalte – beispielsweise in alle Zellen des Bereichs C2:C1000 – einzufügen, ist immer schwierig. Erledigen Sie dies auf die übliche Art (Formel in C2 eintragen und sie dann mit Hilfe des Aufziehkästchens nach unten kopieren), bekommen Sie sicher Probleme mit dem Markieren, oder das Kopieren dauert sehr lange. Es gibt aber Möglichkeiten, das elegant zu umgehen.

Die erste Möglichkeit: Sie kopieren die Formel in alle Zellen der Spalte. Dazu klicken Sie auf die Zelle mit der Formel, um sie zu aktivieren und übernehmen dann die Formel mit [Strg]+[C] in die Zwischenablage. Nun klicken Sie auf den Spaltenkopf, um die ganze Spalte zu markieren. Über [Strg]+[V] fügen Sie die Formel dann in alle Zellen der markierten Spalte ein. Weil dieses Vorgehen je nach Rechner doch einige Zeit dauert und Sie selten alle 1.048.576 Zellen (!) benötigen, empfehle ich die zweite Möglichkeit.

Die zweite Möglichkeit: Sie füllen nur einen Bereich der Spalte C, etwa C2:C1000. Dazu schreiben Sie wiederum Ihre Formel in die Zelle C2. Dann kopieren Sie diese mit [Strg]+[C] in die Zwischenablage, wobei C2 aktiv bleibt. Im Namensfeld der Rechenleiste (ganz links) steht die Zellbezeichnung C2. Ergänzen Sie diese um den Eintrag :C1000, so dass dort nun C2:C1000 steht.

Drücken Sie jetzt [Eingabe], markiert das den Bereich C2:C1000. Über [Strg]+[V] fügen Sie die Formel in alle markierten Zellen ein. Ein erneuter Druck auf [Eingabe] schließt den Vorgang ab und übernimmt die Formel in alle Zellen des markierten Bereichs. Die folgende Warnung, nach der die Formel die Startzelle überschreibt, bestätigen Sie mit einem Klick auf Ja.

Tipp: Calc: Störendes Apostroph löschen

Nach dem Export aus einem Buchhaltungsprogramm erhalten Sie oft eine Calc-Tabelle, in der einige Spalten störende Apostrophe enthalten. Wie werden Sie diese Artefakte wieder los?

Hier wurden offenbar mit Hilfe eines Apostrophs Zahlen- oder Datumswert als Text definiert. Wollen Sie zum Beispiel 1/12 in eine Tabellenzelle eingeben, scheitert eine direkte Eingabe zunächst. Calc wertet Ihre Eingabe als Datumswert aus und wandelt sie in 01.12.12 um. Damit Calc das unterlässt, stellen Sie Ihrem Wert ein Apostroph voran:

'1/12

Calc stellt nun den Wert als Text dar, und zeigt das Apostroph nicht mehr im Tabellenblatt an. Sichtbar wird dieses nur in der Bearbeitungszeile. Wollen Sie die Daten jedoch in andere Programme übernehmen, stören die Apostrophe womöglich. Die Funktion Text in Spalten bietet ein zuverlässiges Verfahren an, störende Apostrophe aus ganzen Tabellenspalten zu entfernen. Diese setzen Sie wie folgt ein:

1. Markieren Sie die Tabellenspalte, in der Sie die Apostrophe beseitigen wollen.
2. Rufen Sie im Menü Daten den Befehl Text in Spalten auf.
3. Im nun erscheinenden gleichnamigen Dialogfeld aktivieren Sie die Trennoption Getrennt und löschen dann alle Häkchen in den Kontrollkästchen des Bereichs Getrennt.
4. Im letzten Schritt des Dialogs müssen Sie im Abschnitt Felder als Spaltentyp die Option Standard aktivieren (Abbildung 6). Damit das klappt, müssen Sie aber zuerst auf die Zellen im Vorschaubild klicken.

5. Der abschließende Klick auf OK wandelt die markierten Werte an Ort und Stelle in Zahlenwerte um, sofern sie sich entsprechend interpretieren lassen. Zellen, die nur ein Apostroph enthielten, formatiert Calc ebenfalls im Zahlenformat Standard und zeigt nachfolgende Zahleneingaben als rechenbare Zahlen und nicht nur als Text an.

   

   Abbildung 6: Lästige Apostrophe, die mitunter beim Importieren von Daten entstehen, lassen sich einfach entfernen.

Tipp: Calc: Wertereihen mit einem Faktor multiplizieren

In einer Calc-Tabelle wollen Sie sämtliche Werte um einen bestimmten Prozentsatz erhöhen. Dazu könnten Sie zwar mit einer Hilfsspalte arbeiten, in der Sie die geeignete Multiplikation vornehmen, aber dann besteht auch die Gefahr, dass Sie dem mühsam eingerichteten Tabellenlayout schaden. Optimal wäre es also, wenn Sie die Werte direkt in der aktuellen Wertespalte umrechnen.

Mit einem kleinen Trick ist dies auch möglich. Über Inhalte einfügen erledigen Sie quasi “vor Ort” Standardberechnungen mit ganzen Tabellenbereichen – so eben auch jene Multiplikationen mit einem bestimmten Faktor, die Sie für die Werteerhöhung benötigen.

Um etwa alle Werte Ihrer Liste um 10 Prozent zu erhöhen, gehen Sie folgendermaßen vor:

1. Schreiben Sie in eine leere Zelle den Faktor für die Multiplikation – für eine zehnprozentige Preiserhöhung müssten Sie also 1,10 eingeben. Bei der Eingabezelle kann es sich um eine leere Zelle im selben Tabellenblatt wie die Werteliste handeln oder auch um eine Zelle in einem anderen Blatt.
2. Markieren Sie die Eingabezelle und drücken Sie [Strg]+[C], um den Inhalt in die Zwischenablage zu kopieren.
3. Markieren Sie in Ihrer Werteliste die Zellen mit den zu erhöhenden Zahlen.
4. Rufen Sie den Befehl Bearbeiten / Inhalte einfügen auf.
5. Im nun angezeigten Dialogfenster (Abbildung 7) aktivieren Sie im Bereich Auswahl die Option Alles einfügen. Bei Rechenoperationen wählen Sie die Option Multiplizieren aus. Das führt dazu, dass Calc alle derzeit markierten Werte mit dem zuvor kopierten Faktor multipliziert. Sie können die Werte bei Bedarf auch pauschal Addieren, Subtrahieren oder durch sie Dividieren.
6. Im Abschnitt Zellen verschieben aktivieren Sie nun die Option Nicht verschieben.

7. Nun klicken Sie auf Ok, um die Berechnung – und damit die Werterhöhung – umzusetzen. Das Ergebnis erscheint direkt in den markierten Zellen. Die ursprüngliche Formatierung stellen Sie über Zellen formatieren wieder her.

   

   Abbildung 7: Über einen einfachen Trick erhöhen Sie mehrere Werte in bereits vorhandenen Zellen auf einen Streich.

Tipp: Calc: Geburtstagslisten sortieren

Geburtstagslisten erfassen normalerweise das Datum der verschiedensten Tage und unterschiedlichsten Jahre, etwa 20.10.2010. Solche Listen nach dem Tagesdatum zu sortieren, bereitet immer wieder Schwierigkeiten, weil Sie die erfassten Geburtstage unabhängig vom Geburtsjahr zuerst nach dem Monat und dann nach dem Tag sortieren.

Als Lösung bietet sich folgende Formel an, die Monat und Tag eines Datumswerts, der in der Zelle B5 steht, mathematisch in eine sortierfähige Zahl umwandelt (Abbildung 8):

=MONAT(B5)*100+TAG(B5)

Für ein Datum am 24. Dezember ermittelt die Formel den Wert 1224, für ein Datum an einem 23. Juni den Wert 623. Unter Kubuntu 11.10 greifen Sie zu den Funktionen MONTH() und DAY(). Die Spalte mit diesen Formelergebnissen verwenden Sie anschließend als Sortierkriterium für die Geburtstagsliste.

Abbildung 8: Um Geburtstagslisten nach Tagen und Monaten zu sortieren, verwenden Sie die Funktionen MONAT() und TAG().