Excel Hacks (eBook)

Formeln und Funktionen effektiv einsetzen. 250 praktische Lösungen für konkrete Aufgabenstellungen

Franz Böhm (Autor)

eBook Download: EPUB

2023 | 1. Auflage
352 Seiten
MITP Verlags GmbH & Co. KG
978-3-7475-0612-7 (ISBN)

Infos

• Excel-Funktionen effektiv kombinieren, um alltägliche Aufgaben zu lösen
• Von Datums- und Uhrzeitberechnungen über Umsatzermittlung und Statistik bis hin zu Datenbanken und bedingter Formatierung
• Zum Download: Alle Lösungen als Tabellenkalkulation für den sofortigen Einsatz
• Geeignet für Microsoft 365 sowie Excel 2019-2021 und Calc (LibreOffice)

Mit Microsoft Excel können Sie all Ihre Tabellenkalkulationsaufgaben lösen, doch Sie müssen wissen, welche Funktionen wie miteinander kombiniert werden, um die Software effektiv einzusetzen und alltägliche Aufgaben zu meistern.

Mit diesem Buch erhalten Sie 250 praktische Lösungen für gängige Aufgabenstellungen. Jede Lösung besteht aus einer kurzen Erläuterung der Aufgabe, einer leicht verständlichen Beschreibung des Lösungswegs sowie einer Liste aller verwendeten Funktionen mit Funktionssyntax, sodass Sie die Formeln leicht an Ihre Bedürfnisse anpassen oder als Anregung für eigene Lösungen zugrunde legen können.

Alle vorgestellten Formeln - sowie fünf umfangreiche Beispielanwendungen vom Urlaubskalender bis zum Haushaltsbuch - stehen sowohl im Format *.xlsx für Excel als auch im Format *.ods für Calc zum Download bereit und können sofort eingesetzt werden.

Aus dem Inhalt:

• Datums- und Uhrzeitfunktionen
• Geburtstage und Feiertage
• Kapital und Anlagen
• Zinsen, Kredite und Abschreibung
• Informationsfunktionen
• Umsatz- und Gewinnermittlung
• Rangfolgen und Runden
• Rechenfunktionen in der Praxis
• Textfunktionen
• Bedingte Formatierungen
• Tabelle und Datenbanken:
  • Zeilen und Spalten
  • Zellinhalte suchen und anzeigen
  • Zellbereiche auswerten
• Anwendungen zum Download:
  • Haushaltsbuch
  • Quittungsformular
  • Urlaubskalender
  • Jahreskalender
  • Sternzeichenberechnung

Franz Böhm beschäftigt sich bereits seit über 20 Jahren intensiv mit Microsoft Excel und den freien Varianten OpenOffice/LibreOffice Calc und kennt daher die Gemeinsamkeiten, aber auch die Unterschiede dieser Programme sowie Lösungswege für alle Problemstellungen.

Textauszug

Kapitel 1:
Datum und Uhrzeit

1.1  Zeitberechnungen

1.1.1  Eine aktualisierbare Uhrzeit‌ einfügen

Problem

In einem Formular soll die aktuelle Zeit angezeigt werden. Die Zeit soll sich beim Öffnen der Datei oder beim Drücken der Taste F9 automatisch aktualisieren.

Lösung

Schreiben Sie die Funktion = JETZT() in die Zelle, in der die aktuelle Uhrzeit angezeigt werden soll.

Hintergrund

Die Formel wird bei jeder Neuberechnung der Arbeitsmappe aktualisiert.

Wählen Sie zusätzlich Start|Format|Zellen, Registerkarte Zahlen, Kategorie Uhrzeit und dann das gewünschte Zeitformat.

Diese Funktion kann auch in Text eingebettet werden, wie die Formel in folgendem Beispiel zeigt:

="letzte Aktualisierung um "&TEXT(JETZT();"HH:MM:SS")

Verwendete Funktionen

JETZT()

TEXT(Wert;Format)

1.1.2  Zahlen in Minuten umwandeln

Problem

Sie möchten Minutenangaben, die als reine Zahlenwerte eingegeben wurden, in einen Zeitwert‌ mit Stunden und Minuten umwandeln. In der Zelle A3 steht beispielsweise der Wert 175 für eine geleistete Arbeitszeit von 175 Minuten. Dieser Wert soll zu einem echten Zeitwert umgerechnet werden, sodass in der Zelle C3 die Uhrzeit »02:55:00« steht.

Lösung

Wenn nur Zahlenwerte für die Minuten (und eventuell auch für Stunden und Sekunden) in der Zelle stehen, können Sie diese direkt mit der Tabellenfunktion ZEIT() in einen »echten« Zeitwert umrechnen. Dazu schreiben Sie in die Zelle C3 die Formel:

=ZEIT(0;A3;0)

Als Ergebnis erhalten Sie die Zahl: »0,121527777777778«, formatiert mit dem Zahlenformat HH:MM:SS die Zeit »02:55:00«. Sie können aber auch jedes andere verfügbare Zeitformat verwenden.

Hintergrund

Die Funktion ZEIT() erwartet drei Parametereingaben: Stunden, Minuten und Sekunden. Fehlt Ihnen eine Angabe, setzen Sie den entsprechenden Parameter einfach auf 0.

Verwendete Funktionen

ZEIT(Stunde;Minute;Sekunde)

1.1.3  Wie errechnen Sie aus Minuten die Stunden und Minuten als Dezimalzahl?

Problem

Die geleistete Arbeitszeit wird nur in Minuten angezeigt. Sie möchten aus dieser Zahl die Stunden und die Minuten als Dezimalzahl‌ berechnen.

Lösung

In der Zelle A3 steht die Arbeitszeit in Minuten: »330«.

In die Zelle B3 schreiben Sie die Formel:

=ZEIT(GANZZAHL(A3/60);REST(A3;60);0)

Als Ergebnis erhalten Sie die Dauer: »05:30«.

Soll dieses Ergebnis als Dezimalzahl ausgegeben werden, mit der Sie auch weiterrechnen können, verwenden Sie die Formel:

=GANZZAHL(A8/60) + REST((A8/60-GANZZAHL(A8/60));60)

Die Formel geht davon aus, dass die umzurechnenden Minuten in der Zelle A8 stehen. Als Ergebnis erhalten Sie »5,50« (Stunden), was auch zu weiteren Berechnungen verwendet werden kann.

Hintergrund

Diese Formel errechnet zuerst mithilfe der Funktion GANZZAHL() die Stunden aus den übergebenen Minuten und addiert anschließend mithilfe der Funktion REST() die verbleibenden Minuten als Dezimalzahl.

Verwendete Funktionen

GANZZAHL()

REST()

ZEIT()

1.1.4  Uhrzeiten runden

Problem

Wie können Sie eine Uhrzeit‌ im Format HH:MM:SS auf ganze Minuten runden?

Lösung

In der Zelle A3 steht die Uhrzeit, beispielsweise »07:15:25«.

In die Zelle B3 schreiben Sie die Formel:

=RUNDEN(A3*1440;0)/1440

Hintergrund

Zuerst wird die Uhrzeit in Tagesminuten umgerechnet (24 Stunden mal 60 Minuten = 1440 Minuten). Nach der Rundung erfolgt eine Division durch die Zahl 1440, um den Zeitwert wieder in Stunden und Minuten umzuwandeln. Soll die Uhrzeit auf ganze Stunden gerundet werden, brauchen Sie die Formel nur abzuwandeln in:

=RUNDEN(A3*24;0)/24

Verwendete Funktion

RUNDEN(Zahl[;Stellen])

1.1.5  Mit Uhrzeiten rechnen (Stundenlohn – Tageslohn‌ – Durchschnittsgeschwindigkeit)

Problem

Wie können Sie den Stundenlohn‌ aus dem bezahlten Lohn sowie den geleisteten Arbeitsstunden errechnen?

Lösung

Schreiben Sie in die Zelle A5 den erhaltenen Lohn: 560,00 €.

In die Zelle B5 tragen Sie die Stundenzahl ein: 8:15.

In die Ergebniszelle C5 schreiben Sie folgende Formel:

=A5/(B5*24)

Als Ergebnis erhalten Sie: »67,88 €«.

Hintergrund

Um ein richtiges Ergebnis zu erhalten, müssen Sie die Dezimalzahl, die hinter der Stundenzahl steht, erst durch die Multiplikation mit 24 auf eine 24-Stunden-Basis bringen. Mit der so gewonnenen Dezimalzahl kann nun der Stundenlohn errechnet werden. Steht in A1 »12:00« Uhr und in A2 »18:00« Uhr, so können Sie mit der Formel =A2-A1 die Zeitdifferenz bestimmen. Sie erhalten als Ergebnis »06:00«.

Intern rechnen Calc und Excel grundsätzlich ohne Zahlenformate, also mit Dezimalwerten. Dabei gibt es für jede Uhrzeit zwischen 0:00 Uhr und 24:00 Uhr einen Dezimalwert zwischen 0 und 1. Im obigen Beispiel rechnet das Programm dabei ganz einfach: 0,75 – 0,5 = 0,25. Dieses Ergebnis entspricht der formatierten Zeit: »06:00 Uhr«.

Auf der Basis von Dezimalzahlen lässt sich jede beliebige Uhrzeit bis auf die Sekunde genau abbilden. Soll umgekehrt eine Dezimalzahl in eine Uhrzeit verwandelt werden, teilen Sie einfach die Dezimalzahl durch 24. Zum Beispiel liefert die Division von =7,5/24 das Ergebnis »07:30« (mit dem Zahlenformat HH:SS formatiert).

Tipp

In gleicher Weise können Sie die Kilometerzahl durch eine Zeitangabe teilen, sodass als Ergebnis die Durchschnittsgeschwindigkeit‌ errechnet wird. Die Anzahl der Kilometer geteilt durch die so ermittelte Dezimalzahl der Zeit ergibt die Durchschnittsgeschwindigkeit. Das Ergebnis wird mit dem benutzerdefinierten Zahlenformat 0,00" km/h" angezeigt.

1.1.6  Positive und negative Zeitdifferenzen darstellen

Problem

Bei einem Trainingslauf war als voraussichtliche Ankunftszeit 15:00 Uhr vorgegeben. Die tatsächliche Ankunftszeit wurde erfasst und soll in Calc oder Excel ausgewertet und dargestellt werden. Wie können Sie die Zeitdifferenzen‌ ausweisen?

Lösung

In der Spalte A sind ab Zeile 3 die geplanten Ankunftszeiten eingetragen.

In der Spalte B erfassen Sie ab Zeile 3 die tatsächliche Ankunftszeit.

In die Zelle D3 geben Sie folgende Formel ein:

=WENN(B3-A3<0;"-"&TEXT(ABS(B3-A3);"[hh]:mm");B3-A3)

Als Ergebnis erhalten Sie die Zeitdifferenz als Textwert; hier: »-00:05«.

Alternative: Geben Sie in eine Zelle folgende Formel ein:

=(B15-A15)*1440

Als Ergebnis erhalten Sie die Zeitdifferenz als serielle Zahl; hier: »-5«. Kopieren Sie die Formeln in die übrigen Ergebniszellen.

Hintergrund

Im ersten Fall werden die positiven Zeitergebnisse als Zeitwerte‌ und die negativen Zeitwerte als Textwerte‌ ausgegeben (vgl. Ausrichtung der Textwerte und der Zahlenwerte!).

Als Ergebnis der Alternative erhalten Sie die Zeitergebnisse als rechenbare Minutenwerte (1 Tag = 24 Stunden * 60 Minuten = 1440 Minuten). Diese Darstellung eignet sich besonders, wenn Sie mit den ausgewiesenen Ergebnissen weiterrechnen müssen.

Verwendete Funktionen

ABS(Zahl)

TEXT(Zahl;Format)

WENN(Prüfung;DannWert;SonstWert)

1.1.7  Berechnung der Arbeitszeit mit Pausenzeit nach sechs Stunden

Problem

Bei der Berechnung der Arbeitszeiten wird eine Pause von 15 Minuten angerechnet, wenn die Arbeitszeit‌ mehr als 6 Stunden beträgt. Wie errechnen Sie die tatsächlich geleistete Arbeitszeit?

Lösung

Schreiben Sie in die Zelle B3 den Beginn der Arbeitszeit 19:00, in die Zelle B4 das Ende der Arbeitszeit 02:00 und in die Zelle B5 die anzurechnende Pause 0:15 (wenn die Arbeitszeit länger als 6 Stunden ist).

Die anzurechnende Arbeitszeit erhalten Sie in die Zelle C6 mit folgender Formel:

=WENN(ODER(B3="";B4="");"";(B4+(B4<B3)-B3)-(WENN((B4+(B4<B3)-B3)>0,25;B5;0)))

Als Ergebnis erhalten Sie 06:45 Stunden.

Hintergrund

Im ersten Teil der Formel wird durch eine WENN()-Funktion eine Fehlermeldung unterdrückt, falls keine Arbeitszeiten eingetragen wurden (ODER(B3="";B4="");""), sowie die eigentliche Arbeitszeit berechnet (=B4+(B4<B3)-B3). Die Formel berücksichtigt dabei auch einen eventuellen Tageswechsel der Uhrzeiten (B4<B3). Von der berechneten Stundenzahl wird anschließend die Pausenzeit abgezogen, falls die gesamte Arbeitszeit mehr als 6 Stunden (>0,25) ist.

Verwendete Funktionen

ODER(Wahrheitswert1[;Wahrheitswert2[;...[;Wahrheitswert 255]]])

WENN(Prüfung;Dann_Wert;[Sonst_Wert])

1.1.8  In...

Produkt-Details

• Erscheint lt. Verlag: 17.2.2023
• Sprache: deutsch
• Themenwelt: Mathematik / Informatik ► Informatik
• ISBN-10: 3-7475-0612-7 / 3747506127
• ISBN-13: 978-3-7475-0612-7 / 9783747506127