Kapitel 9

Geschrieben von Torsten Lenneper. Veröffentlicht in Excel 2000

Weitere Funktionen

In den bisherigen Kapitel haben Sie die Funktionen Summe, Mittelwert, Minimum und Maximum kennen gelernt. Wie Sie sicherlich beim Aufruf des Funktions-Assistenten bemerkt haben, gibt es in Excel eine Unmenge an Funktionen. Die meisten dieser Funktionen werden Sie bei ihrer täglichen Arbeit nicht benötigen, da sie spezielle Berechnungen durchführen. Einige jedoch erleichtern Ihnen die Arbeit bei allgemeinen Berechnungen.

Runden

Der Syntax für diese Formel, also wie Sie Daten eingeben müssen, lautet «=RUNDEN(Zahl;Anzahl-Stellen)». Die Funktion rundet den Wert Zahl auf die im Argument «Anzahl-Stellen» angegebene Dezimalstelle. Excel rundet nach den kaufmännischen Regeln, dass heißt eine Zahl kleiner als 5 wird abgerundet, eine Zahl gleich oder grösser als 5 wird aufgerundet.

Übung 9.1

Fügen Sie der Arbeitsmappe ein neues Blatt hinzu und geben ihm den Namen wie abgebildet

Geben Sie den die folgenden Daten in das neue Blatt ein

Formatieren Sie den Zellbereich «C2:C9» mit dem Zahlenformat Währung und nachgestelltem Euro-Zeichen

Geben Sie die Daten wie in der nebenstehenden Tabelle ein

Wenn Sie die Ergebnisse für die Zwischensumme und die Gesamtsumme mit einem Taschenrechner kontrollieren, werden Sie feststellen, dass das Ergebnis um 0,01 bzw. 0,02 abweicht

Wie Sie bereits wissen, rundet Excel kaufmännisch. Wenn Sie Berechnungen durchführen, die als Ergebnis mehr als die zwei voreingestellten Dezimalstellen bringen, wird zwar automatisch auf zwei Nachkommastellen gerundet, Excel rechnet aber weiterhin mit allen Dezimalstellen und zeigt optisch ein falsches Ergebnis an. Wenn Sie mit allen Dezimalstellen einmal nachrechnen und die Zellen «C7» und «C9» runden, werden Sie feststellen, dass das Ergebnis ebenfalls korrekt ist.

Die Lösung für dieses Problem ist die Funktion «RUNDEN». Die entsprechenden Zahlen werden nach Ihren Vorgaben gerundet und Excel führt die folgenden Berechnungen auch nur mit diesen gerundeten Werten durch.

Übung 9.2

Ändern Sie die Formeln im Zellbereich «C2:C9» wie hier gezeigt und kontrollieren die Ergebnisse. Sie sollten nun korrekt angezeigt werden. Wie Sie sehen, ist es auch kein Problem, zwei oder auch mehr Funktionen miteinander zu verknüpfen. Achten Sie nur immer auf die richtige Klammersetzung

Im Bereich der gerundeten Dezimalstellen gibt es noch drei weitere nützliche Funktionen.

Kürzen

Der Syntax für diese Funktion lautet «KÜRZEN(Zahl;Anzahl-Stellen». Es werden die gleichen Argumente wie bei der vorherigen Funktion benötigt. In diesem Fall wird aber an der angegebenen Dezimalstelle abgeschnitten und nicht gerundet.

Aufrunden

Der Syntax für diese Funktion lautet «AUFRUNDEN(Zahl;Anzahl-Stellen». Die Argumente bleiben dieselben. An der entsprechenden Stelle wird aber in jedem Fall aufgerundet und die kaufmännischen Regeln außer Kraft gesetzt.

Abrunden

Der Syntax für diese Funktion lautet «ABRUNDEN(Zahl;Anzahl-Stellen». Diese Funktion bewirkt das genaue Gegenteil der Funktion «AUFRUNDEN».

Normalerweise werden Sie diese Funktionen benutzen, um Stellen nach dem Komma entsprechend zu runden oder zu kürzen. Wenn Sie jedoch für das Argument «Anzahl-Stellen» einen negativen Wert eingeben, so bezieht sich die Funktion auf die angegebene Stelle vor dem Komma. Sie bekommen als Ergebnis angezeigt, wenn Sie folgendes in eine Zelle eingeben: . Dies gilt natürlich auch für die anderen beschriebenen Funktionen.

Obergrenze

Eine weitere praktische und hilfreiche Funktion, gerade wenn Preise kalkuliert werden sollen, ist die Funktion «OBERGRENZE». Der Syntax für diese Funktion lautet «OBERGRENZE(Zahl;Schritt)». Die angegebene Zahl wird auf ein vielfaches, festgelegt mit dem Argument «Schritt», aufgerundet.

Übung 9.3

Geben Sie im Tabellenblatt die folgenden Daten ein und weisen Sie dem Zellbereich «G2:G6» wieder das Zahlenformat Währung mit zwei Dezimalstellen zu

Die Verkaufspreise werden zwar korrekt angezeigt, in der Regel werden Verkaufspreise allerdings auf glatte Beträge bzw. in 0,05€ Schritten ausgegeben. Es wäre also praktisch, wenn die Preise in den Zeilen 4 bis 6 automatisch wie gewünscht ausgegeben würden

Ändern Sie die Formel in den Zeilen der Spalte «G» folgendermaßen um . Sie sollten das folgende Ergebnis erhalten

Das Ergebnis entspricht wahrscheinlich bis auf die dritte Zeile Ihren Wünschen. Dort hätte der Dezimalbereich nicht verändert werden müssen. Die Funktion muss also noch angepasst werden. Mit folgendem Trick können Sie Excel dazu bringen, korrekt nach ihren Wünschen zu runden. Subtrahieren Sie einfach vom zu rundenden Wert 0,025. Die Funktion sieht dann so aus: . Achten Sie auf das korrekte Setzen der Klammern, wobei Sie die Klammern in diesem Fall auch hätten weglassen können, da ja die Punkt- vor der Strichrechnung berechnet wird. Um auf 10 Cent zu runden, subtrahieren Sie 0,05, für 50 Cent 0,25 usw. für weitere Rundungsmöglichkeiten.

Anzahl

In der nächsten Übung verwenden Sie drei weitere Funktionen aus der Kategorie Statistik. Um die Menge der Zellen in einem Zellbereich anzuzeigen, die Zahlen enthalten, verwenden Sie die Funktion ANZAHL mit folgendem Syntax «ANZAHL(Bereich)». Die Funktion ANZAHL2 gibt die Menge der Zellen in einem Zellbereich aus, die einen Wert enthalten, also Text oder Zahlen. Und wenn Sie sich die Anzahl der Zellen in einem Zellbereich anzeigen lassen möchten, die keinen Inhalt haben, verwenden Sie die Funktion ANZAHLLEEREZELLEN. Der Syntax für alle drei Funktionen ist gleich. Statt eines Zellbereichs können Sie auch einzelne Zellbezüge, getrennt durch ein Semikolon, angeben.

Übung 9.4

Geben Sie in die Zellen «B11» bis «B14» den folgenden Text ein. Um einen Zeilenumbruch innerhalb einer Zelle zu erzeugen, benutzen Sie die Tastenkombination Alt+Enter.

Nun müssen Sie in die nebenstehenden Zellen die passenden Funktionen eingeben. Klicken Sie auf um die Bearbeitung der Funktionen zu starten. Wählen Sie aus der Liste die passende Funktion aus oder öffnen Sie den Assistenten, in dem Sie auf klicken. Fügen Sie auf diese Weise die Funktionen für den Zellbereich «A1:C9» wie abgebildet ein

Um die Gesamtzahl der Zellen im markierten Zellbereich zu erhalten, müssen Sie einfach nur die Zellen «C11» und «C12» aufaddieren. Das Ergebnis zeigt Ihnen die nebenstehende Abbildung

Gerade bei umfangreichen Tabellen verliert man schnell den Überblick, da nicht alle Zellen sichtbar auf dem Bildschirm dargestellt werden können. Mit diesen drei Funktionen können Sie schnell überprüfen, ob auch alle Zellen die ausgefüllt werden müssen, auch Werte bzw. Zahlen enthalten. Sie sind also für Kontrollfunktionen besonders gut geeignet.

Wenn

Diese Funktion prüft eine Zelle oder einen Zellbereich auf die von Ihnen vorgegebenen Kriterien und gibt dann ein Ergebnis aus. Je nachdem, ob die Prüfung den Wert «wahr» oder «falsch» liefert, unterscheidet sich das Ergebnis. Der Syntax für diese Funktion lautet «WENN(Prüfung;Dann-Wert;Sonst-Wert». Liefert die Prüfung die Rückmeldung «wahr» wird der von Ihnen festgelegte «Dann-Wert» ausgegeben. Bringt die Prüfung den Wert «falsch», wird der «Sonst-Wert» ausgegeben.

Übung 9.5

Fügen Sie ein weiteres Arbeitsblatt mit dem Namen «Sieben» ein

Legen Sie die Tabelle wie unten abgebildet an. Sie können auch versuchen, die Formatierungen zu übernehmen, dies ist allerdings für die Übung nicht ausschlaggebend

Die Tabelle kann als Einnahme/Überschuss-Rechnung oder auch als Kassenbuch genutzt werden. In den Spalten «B» und «C» tragen Sie die laufende Nummer sowie das Belegdatum ein. In der Spalte «D» tragen Sie ein, ob es sich um eine Einnahme oder Ausgabe handelt. Die Spalten «E» und «F» sind selbsterklärend. Jetzt kommen Sie zu den interessanten Punkten in der Tabelle. Da es sich bei den Ausgaben natürlich um negative Beträge handelt, müssen Sie der Zahl ein Minus-Zeichen voranstellen. Und hier liegt schon die entscheidende Fehlerquelle. Wenn Sie nämlich vergessen, dem Betrag das Zeichen hinzuzufügen, wird er wie eine Einnahme behandelt und würde Ihre komplette Berechnung unbrauchbar machen.

Das Ziel ist nun, dass Sie den Betrag ohne Vorzeichen erfassen und von Excel entscheiden lassen, ob es sich um eine Einnahme oder Ausgabe handelt. Dazu geben Sie in der Spalte «G» den Betrag ohne jedes Vorzeichen ein. Die Spalte «I» dient Ihnen lediglich zur Berechnung. Dort müssen Sie jetzt eine Funktion eingeben, und zwar die WENN-Funktion. Wenn die Zelle «D3» den Wert «Ausgabe» enthält, soll die Zahl in der Zelle «G3» mit vorangestelltem Minus-Zeichen und zwei Dezimalstellen dargestellt werden, ansonsten als positive Zahl ohne Vorzeichen aber auch mit zwei Dezimalstellen.

Zusätzlich zur WENN-Funktion benötigen Sie noch eine weitere, und zwar die Text-Funktion. Der Syntax lautet «=Text(Wert;Textformat)». Die Funktion wandelt eine Zahl in Text mit vorgegebenem Format um. Weitere Berechnungen sind dann mit dem Inhalt der Zelle nicht mehr möglich.

Übung 9.6

Geben Sie also in der Zelle «I3» die folgende Formel ein: . Achten Sie wieder auf alle Klammern und auf die Anführungszeichen die den Text in der Formel umschließen müssen

Kopieren Sie die Formel bis in die Zelle «I8»

Füllen Sie die Zellen nun mit Daten

Die Spalten «G» und «I» dienen in dieser Tabelle nur zur Eingabe bzw. Berechnung und sollen später auch nicht mit gedruckt werden. Der eigentliche Betrag mit dem entsprechenden Vorzeichen soll in der Spalte «H» erscheinen. Außerdem sollen am Ende der Tabelle noch alle Ausgaben und Einnahmen einzeln aufaddiert werden und deren Differenz ausgegeben werden.

Übung 9.7

Geben Sie in die Zelle «H3» ein. Der Betrag wird dargestellt, allerdings ist er links ausgerichtet, da es sich ja um einen Text handelt. Auch eine manuelle Rechtsausrichtung ändert nichts am Format

Erweitern Sie die Formel in . Jetzt ist der Wert wieder eine Zahl mit der Sie weitere Berechnungen durchführen können

Kopieren Sie die Formel bis in die Zelle «H8»

Ändern Sie in diesem Bereich auch noch das Zahlenformat wie abgebildet

Um nur die Einnahmen in der Spalte «H» zu addieren, verwenden Sie eine weitere Funktion, und zwar die SUMMEWENN-Funktion. Dies ist eine Kombination aus den zwei Funktionen, die Sie ja bereits kennen. Wenn der Betrag grösser als null ist, handelt es sich um eine Einnahme. Die Ausgaben sind kleiner als null.

Übung 9.8

Die letzten Formeln, die Sie jetzt noch eingeben müssen, sehen Sie in der nebenstehenden Abbildung

Weisen Sie auch diesem Zellbereich wieder das Format wie oben zu

Blenden Sie die Spalten «G» und «I» über das Menu FORMAT►SPALTE►AUSBLENDEN aus und Sie können die Tabelle drucken. Um später wieder Daten eingeben zu können, blenden Sie die Spalten einfach wieder ein

Berechnungen mit Datum und Zeit

In Kapitel 5 haben Sie ja bereits das Datumsformat kennen gelernt und auf welche Arten man das Datum in eine Zelle eingeben kann. Auch kleine Berechnungen haben Sie schon durchgeführt. Sie erfahren nun, wie Sie weitere Berechnungen, auch mit Uhrzeiten und Tagen, vornehmen können. Hierzu werden Sie die Bekanntschaft mit weiteren Funktionen machen.

Zeitberechnung

Sie trennen bei der Eingabe einer Uhrzeit die Stunden, Minuten und Sekunden jeweils durch einen Doppelpunkt (Sie müssen aber keine Sekunden angeben), oder, wenn Sie im englischen Stil anzeigen möchten, dass heißt im 12-Stunden-Modus, setzen Sie ans Ende, getrennt durch ein Leerzeichen, ein «a» bzw. «am» (0-12) oder ein «p» bzw. «pm» (12-24). Auch die Kombination von Datum und Uhrzeit in einer Zelle ist möglich. Trennen Sie die beiden Teile einfach durch ein Leerzeichen.

Übung 9.9

Fügen Sie wieder ein neues Arbeitsblatt ein und geben ihm den Namen «Acht»

Geben Sie die folgenden Daten in das Arbeitsblatt ein

Formatieren Sie die Tabelle wie abgebildet

Wenn Sie mit Datum oder Zeit rechnen möchten, müssen Sie immer den jüngeren vom älteren Wert abziehen, damit Sie kein negatives Ergebnis erhalten. Das gilt natürlich auch für die Uhrzeit und die Kombinationen.

Übung 9.10

Hier sehen Sie die Formeln, die Sie in die Tabelle eingeben müssen

Weisen Sie den markierten Zellen das Zahlenformat «Standard» zu

Markieren Sie jetzt die Zelle «D8» und weisen ihr ein benutzerdefiniertes Format zu. Gehen Sie dazu wieder in das Menu FORMAT►ZELLEN und wählen dort die Kategorie «Benutzerdefiniert»

Löschen Sie den Inhalt im Feld «Typ» und geben dafür ein

Das Ergebnis sollte nun so aussehen

Wie Sie sehen, wandelt Excel auch die Uhrzeiten in Dezimalwerte um, damit mit diesen Werten Berechnungen durchgeführt werden können.

Arbeitszeitberechnungen

In den letzten Übungen haben Sie eine einfache Berechnung mit Uhrzeiten und einem Datum durchgeführt. Jetzt werden Sie sehen, welche Probleme solche Berechnungen mit sich bringen und wie Sie diese Probleme lösen. Wenn Sie sich eine Tabelle erstellen möchten, in denen Sie Ihre Arbeitszeiten genauestens erfassen und auch Urlaubstage verwalten, müssen Sie noch weitere Funktionen verwenden. Auch Nachtschichten, wo die Arbeitszeit über die Datumsgrenze hinaus geht, sollen korrekt berechnet werden.

Übung 9.11

Fügen Sie erneut ein Blatt in Ihre Arbeitsmappe ein und geben ihm den Namen «Neun»

Erstellen Sie zunächst einmal die folgende Tabelle und geben alle Daten ein. Benutzen Sie als Arbeitserleichterung das Werkzeug «AutoAusfüllen»

Statt die Wochentage einzugeben, können Sie sie auch von Excel berechnen lassen. Geben Sie dazu in die Zelle A2 die folgende Formel ein und ziehen dann mit «AutoAusfüllen» nach unten.

Sie haben nun die Arbeitszeiten für den Monat März 2003 erfasst und nun sollen die Stunden für die einzelnen Tage in der Spalte «E» berechnet werden.

Übung 9.12

Geben Sie in die Zelle «E2» die Formel ein und kopieren diese bis in die Zelle «E21»

In einigen Zellen werden Ihnen Rautensymbole angezeigt. Da automatisch das Format für die Uhrzeit auch für die Zellen in der Spalte «E» übernommen wurde und in diesen Zellen ein negativer Wert steht (überprüfen Sie das, in dem Sie das Zahlenformat «Standard» auswählen), kann der Wert nicht dargestellt werden, da negative Uhrzeiten nicht definiert sind. Sie müssen also die Formel ersetzen.

Übung 9.13

Entfernen Sie die Formeln mit der Entf-Taste oder machen Sie die letzten Aktionen mit dem Symbol wieder rückgängig

Fügen Sie nun in die Zelle «E2» die Formel ein und kopieren diese wieder nach unten. Die Werte sollten nun stimmen (eine manuelle Prüfung sollten Sie allerdings schon vornehmen)

Bei der Formel müssen Sie bedenken, dass Excel die Uhrzeiten intern in Dezimalzahlen umwandelt. 24 Uhr entspricht demnach dem Wert 1. In diesem Fall gehen Sie davon aus, dass die tägliche Soll-Arbeitszeit acht Stunden beträgt. Da Sie zu unterschiedlichen Zeiten arbeiten, sollen nun die Soll- mit den Ist- Zeiten verglichen werden und die Differenzen auf dem Stundenkonto ausgegeben werden.

Übung 9.14

Geben Sie in die ersten beiden Zellen der Spalte «F» die folgenden Zahlen ein

Markieren Sie die zwei Zellen und ziehen dann mit «AutoAusfüllen» wieder nach unten. Die letzte Zahl sollte 160 lauten

Um die aufgelaufenen Stunden zu addieren, geben Sie in der Zelle «G2» diese Formel ein: und kopieren wieder bis nach unten

In der Formel wurde die Zelle «G1» die ja nur Text enthält, mit verwendet. Das die Funktion diesen Sachverhalt ignoriert und somit keine Fehlermeldung bringt ist praktisch, da Sie die Formel so ohne weiteres nach unten kopieren können. Das Problem ist allerdings das Ergebnis in dieser Spalte. Excel hat in jedem Fall richtig gerechnet, dass können Sie nachprüfen. Allerdings wird, da den Zellen das Zahlenformat Uhrzeit zugewiesen wurde, nach 24:00 wieder mit 00:00 begonnen und weiter aufaddiert. Also muss auch diese Formel geändert werden. Kurz gesagt müssen Sie die einzelnen Stunden in Minuten umwandeln und addieren. Die Funktion STUNDE filtert die Stunden aus Ihrer Uhrzeit und wandelt sie in eine Dezimalzahl um die sie dann noch mit 60 multiplizieren. Zu dieser Summe addieren Sie die Summe der Minuten (sie müssen mit der Funktion MINUTE umgewandelt werden) und teilen am Ende die Gesamtminutenzahl durch 60 um wieder die Anzeige mit Stunden und Minuten zu erhalten.

Übung 9.15

Machen Sie die Aktionen wieder rückgängig, so dass in der Spalte «G» keine Werte mehr enthält

Die Formel geben Sie jetzt in die Zelle «G2» ein

Kopieren Sie auch diese Formel wieder nach unten und weisen dem Zellbereich das Zahlenformat «Zahl» mit zwei Dezimalstellen zu. Mittlerweile sollten die folgenden Informationen in Ihrer Tabelle stehen

An dieser Tabelle können Sie jederzeit ablesen, ob Sie mit Ihren Arbeitsstunden hinterherhängen und Sie etwas zulegen müssen oder ob Sie es ruhiger angehen lassen können und weniger Stunden arbeiten. Damit die Sache noch übersichtlicher wird und Sie auf einen Blick sehen, wie viele Stunden oder Minuten die Differenz zwischen Soll und Ist beträgt, werden Sie die Spalte «H» noch beanspruchen.

Übung 9.16

Geben Sie die Formel in die Zelle «H2» ein und kopieren diese nach unten

Weisen Sie dem Bereich das Zahlenformat «Zahl» zu mit roten negativen Werten

Da Sie zusätzlich auch noch Ihre Urlaubstage mit der Tabelle verwalten möchten, haben Sie die Spalte «I». Wenn Sie an einem Tag Urlaub genommen haben, tragen Sie keine Zeiten sondern in der Spalte einfach ein «ja» ein. In der Zelle «C26» werten Sie die Informationen dann mit der Funktion ZÄHLENWENN aus. Der Syntax dieser Funktion lautet «=ZÄHLENWENN(Bereich;Suchkriterien)». Sie suchen also in einem bestimmten Bereich nach Zellen mit dem Inhalt «ja». Diese Zellen werden gezählt und die Anzahl als Ergebnis ausgegeben.

Übung 9.17

Tragen Sie in der Zelle «C26» die Formel ein

Errechnen Sie mit die restlichen Urlaubstage

Mit diesen Funktionen verabschiedet sich jetzt auch das

Kapitel 9 von Ihnen

< Zurück