Sammlung ==> #01 Formeln und Funktionen, kurze Auffrischung

Formeln und Funktionen:

Die Haupteigenschaft von Excel sind die Rechenfunktionen, die man mit dem Programm durchführen kann. Überhaupt ist die Nutzung von Excel nur dann sinnvoll, wenn kompliziertere Funktionseinträge in den Tabellen erforderlich sind, da sich einfache Rechnungen auch in einem gängigen Textverarbeitungsprogramm durchführen lassen (z.B. Winword).

Die Rechenfunktionen werden in die betreffenden Tabellenzellen eingetragen. Grundsätzlich beginnt jeder Recheneintrag mit dem Gleichheitszeichen. Anschliessend wird die Rechenformel bzw. –funktion eingetragen. Im einfachsten Fall wäre zum Beispiel folgender Eintrag denkbar:

=7+5

Die Eintragung schliesst mit der Returntaste, was zur Folge hat, dass im Anschluss Excel sofort den Ergebniswert in der Zelle umsetzt. Dagegen erscheint bei formell oder logisch falschen Eingaben eine Fehlermeldung. Die eingetragene Formel/Funktion lässt sich nach jedem Eintrag in der Bearbeitungszeile anzeigen, dazu muss lediglich der Zellcursor auf die Zelle mit der Formel/Funktion gesetzt werden.

Natürlich verwendet man Excel nicht für derartig einfache Zahlenrechnungen wie im obigen Beispiel. Vielmehr ist die Regel, dass sogenannte Zellbezüge hergestellt werden. Das bedeutet, dass man sich in der Rechnung auf eine oder mehrere Zelladressen bezieht, in denen eingegebene oder errechnete Inhalte stehen. In der Abbildungstabelle wurde hierzu in der Zelle C1 ein Formeleintrag getätigt, welcher die Werte aus den Zellen A1 und B1 so addiert, dass wenn in A1 und B1 Änderungen eingetragen werden, das Ergebnis in C1 entsprechend aktualisiert wird.

Der Formeleintrag in der Zelle C1 lautet hier nicht =7+5 sondern:

=A1+B1

Wenn jetzt in A1 oder B1 andere Werte eingetragen werden, so wird das Ergebnis in C1 automatisch aktualisiert.

Das Prinzip der Zellbezüge unterscheidet hier Eingabezellen und Ausgabezellen. Im Beispiel sind A1 und B1 Eingabezellen, die Zelle C1 ist die Ausgabezelle. Grundsätzlich beinhaltet jede Ausgabezelle einen Formel- bzw. Funktionseintrag.

Das Ergebnis einer Ausgabezelle muss aber nicht zwingend aus Eingabezellen resultieren, sondern kann wiederum von einer anderen Ausgabezelle abhängen. Das folgende Beispiel zeigt dies, in dem in E1 ein Wert eingetragen wurde und in F1 dieser Eingabewert mit dem Ausgabewert aus C1 addiert wird. Der Formeleintrag in F1 ist:

=C1+E1

Wenn man nun zum Beispiel in A1 einen neuen Wert einträgt, so wirkt sich das Ergebnis direkt auf C1 aus, damit aber auch direkt auf F1. Diese "Rechendynamik" unter Verwendung von Zellbezügen bewirkt, dass sich in Exceltabellen viele Ausgabewerte ändern können, bei nur wenigen Änderungen der Eingabewerte.

Neben der Begriffsunterscheidung Eingabe-/Ausgabezellen lässt sich nun auch die Unterscheidung von Vorgänger- bzw. Nachfolgerzellen verdeutlichen. So ist zum Beispiel A1 Vorgängerzelle von C1, umgekehrt ist C1 Nachfolgerzelle von A1. C1 ist wiederum Vorgängerzelle von F1. Es wird klar, dass bis auf F1 alle Zellen Vorgängerzellen sind, da F1 keine Nachfolgerzelle hat. Mit anderen Worten: F1 wird in keiner weiteren Ausgabezelle verrechnet.

Die Bandbreite der Rechenfunktionen ist in Excel sehr gross, so dass es sinnvoll ist, folgende Arten von Recheneinträgen zu unterscheiden:

• Einfache arithmetische Formeleinträge
• Syntax-Funktionseinträge
• Gemischte Funktionseinträge

Einfache arithmetische Formeleinträge:

Diese Art von Recheneinträgen sind bereits im obigen Beispiel verwendet worden. Es handelt sich dabei um Einträge, bei denen die Art der Verrechnung durch den Benutzer in Form der gängigen Rechenzeichen vorgegeben wird.

Die folgende Übersicht ordnet dabei speziellen Excel-Rechenzeichen jeweilige Rechenarten zu:

Beispiele für einfache arithmetische Formeleinträge:

=A1+B5

=17+D4

=B1*(A10+A12)

=B3+A12/3

=(T1-T2-T12)/(A2+A5+A7)

Syntax-Funktionseinträge:

Grundsätzliches:

Obwohl bereits mit den einfachen arithmetischen Formeln umfangreiche Berechnungen gemacht werden können, so sind die Syntax-Funktionseinträge wesentlich komplexer. Bei dieser Funktionsart bildet eine jeweilige Funktionssyntax eine vorgegebene Eingabestruktur, die eine funktionsspezifische Verrechnungsart zur Folge hat.

Eine einfache Syntax-Funktion ist zum Beispiel die MAX-Funktion. Die sogenannte allgemeine Syntax der MAX-Funktion lautet:

=MAX(Zahl1;Zahl2...)

Dabei können für die Argumente Zahl1, Zahl2 usw. konkrete Zahlen stehen, aber auch Zelladressen bzw. Zellbereiche, welche Zahlen enthalten.

In Bezug auf Tabelle "Beispielwerte" folgt eine Übersicht über Beispiele möglicher MAX-Funktionen:

Beispielwerte:

Einige einfachere Syntaxfunktionen funktionieren analog, das heißt ausschlaggebend für die funktionsspezifische Verrechnungsart ist der Funktionsname der Funktion. Beispiele sind hierfür neben der MAX-Funktion die Funktionen MIN, SUMME, MITTELWERT und PRODUKT. Dabei lautet die allgemeine Syntax der jeweiligen Funktion:

Dabei können für die Argumente Zahl1, Zahl2 usw. konkrete Zahlen stehen, aber auch Zelladressen bzw. Zellbereiche, welche Zahlen enthalten.

Folgende Tabelle führt für diese Funktionen einige Beispiele auf (in Bezug auf Tabelle "Beispielwerte"):

Die bislang gezeigten Funktionsbeispiele haben die Gemeinsamkeit, dass die Anzahl der Argumente Zahl1;Zahl2... praktisch unbegrenzt ist. Bei vielen Syntax-Funktionen ist dies jedoch nicht so, das heißt, die Anzahl der Argumente ist genau festgelegt. Ein Beispiel hierfür ist die Funktion RUNDEN mit der allgemeinen Funktionssyntax

=RUNDEN(ZAHL;ANZAHL_STELLEN).

Dabei bedeutet ZAHL der Wert, welcher gerundet werden soll. Dieser kann auch durch einen Zellbezug definiert sein. ANZAHL_STELLEN gibt die Anzahl der Nachkommastellen an, auf den die Zahl kaufmännisch auf- bzw. abgerundet werden soll.

Übersicht über Beispiele möglicher RUNDEN-Funktionen (in Bezug auf Tabelle "Beispielwerte"):

Verschachtelungen:

Oftmals wird in Excel-Tabellen von der Möglichkeit Gebrauch gemacht, dass sich Syntax-Funktionen miteinander verschachteln lassen. Eine Verschachtelung liegt dann vor, wenn innerhalb einer Syntaxfunktion eine weitere Syntaxfunktion als Argument plaziert ist. Dabei gilt, dass dann in der "inneren" Funktion das Gleichheitszeichen nicht nocheinmal gesetzt sein darf.

Zur Verdeutlichung sind in folgender Übersicht als Beispiel 3 einfache Verschachtelungen gezeigt:

Bei der Funktion SUMME wird dabei zunächst der kleinste Wert (MIN) aus 7; 10; 5 mit 5 ermittelt und dann mit der 10 und 3 addiert. Bei der Funktion MAX wird zunächst die Summe aus 2; 4; 3 mit 9 ermittelt und dann hinsichtlich der Werte 2 und 10 auf den größten Wert verglichen. Bei der Funktion MITTELWERT wird zunächst der größte Wert (MAX) aus 22; 30; 11 mit 30 ermittelt und dann mit 10 und 20 in die Durchschnittsberechnung mit einbezogen.

Funktions-Assistent für Syntax-Funktionen:

Der Funktions-Assistent in Excel ist ein Tool, welches zum einen die von Microsoft bereitgestellten Funktionen in einer Übersicht katalogisiert und zum anderen bei Syntax-Funktionseinträgen schrittweise den Benutzer führt. Der Assistent kann über die Menüfolge EINFÜGEN – FUNKTION geöffnet werden oder schneller über das Symbol [SYMBOL-F(X)]. Im sich öffnenden Dialogfenster sind auf der linken Hälfte die Funktionskategorien gelistet. Wenn eine Funktionskategorie angeklickt wird, erscheinen in der rechten Hälfte die betreffenden Funktionsnamen.

Als Beispiel wird die Funktion MITTELWERT ausgewählt, welche in Zelle A10 den Durchschnittswert von A1:A9 ermitteln soll. Die Verwendung des Funktions-Assistenten verläuft dabei in folgenden Schritten:

Schritt 1:

Öffnen des Funktions-Assistenten über das Symbol [SYMBOL-FX] oder über Menü EINFÜGEN - FUNKTION. Dabei muss der Zellcursor in der betreffenden Ausgabezelle stehen, also in A10.

Schritt 2:

Auswahl der Funktion MITTELWERT. Hierfür wird zunächst in der linken Hälfte die Kategorie "Statistik" ausgewählt. In der rechten Hälfte wird MITTELWERT gewählt. Mit "OK" oder Doppelklick auf MITTELWERT erscheint ein spezifisches Funktions-Dialogfenster.

Schritt 3:

Da es sich im Beispiel um einen zusammenhängenden Zellbereich handelt, wird im Eintragsfeld "Zahl 1" der Zellbereich A1:A9 eingetragen. Durch das Wegziehen des Dialogfensters mit der Maus kann der Zellbereich der Spalte A sichtbar gemacht werden und der Eintrag kann auch mit der Maus erfolgen, in dem in das Eintragsfeld "Zahl 1" kurz geklickt wird und anschliessend die Maus mit gedrückter linker Taste über den Bereich A1:A9 geführt wird.

Eingabe mit "Ende" bestätigen.

Gemischte Funktionseinträge / Verkettungen:

Ein gemischter Funktionseintrag hat die Eigenschaft, dass innerhalb einer Syntax-Funktion ein einfacher arithmetischer Formeleintrag als Argument steht.

Beispiel:

=MAX(A5;B7;D5+D6+D8)

Eine besondere Eigenschaft bilden die Verkettungen von Funktionen durch das "&"-Zeichen. Innerhalb eines einzigen Zelleintrages können somit mehrere Funktionen hintereinander ausgeführt werden.

Beispiel:

=MAX(A5;D5;F7)&SVERWEIS(B2;D3:D30;3)&"Text"

Im Zusammenhang vorliegender Thematik würde eine Vertiefung dieser Arten den Rahmen dieser kurzen Auffrischung sprengen.

Ausfüllfunktion / Relative und absolute Zellbezüge:

Die Ausfüllfunktion ermöglicht das Wiederholen von Funktionseinträgen, welche sich zum Beispiel über mehrere Zeilen logisch wiederholen. In diesem Zusammenhang ist die Unterscheidung der relativen und absoluten Zellbezüge wichtig. Zur Verdeutlichung der Zusammenhänge sind folgende einfachen Beispiele (ohne Sachbezug) aufgeführt.

Im ersten Beispiel (Tabelle Relative Bezüge) sollen in der Spalte D jeweils die Kosten aus den Angaben Preis und Menge der 10 Produkte jeweils ermittelt werden. Für Produkt 1 ist dabei in Zelle D2 der Formeleintrag =B2*C2 erforderlich. Für die nächsten Produkte wären die Einträge =B3*C3; =B4*D4; =B5*D5 usw. in den Zellen D3; D4; D5 usw. einzutragen. Da hier lediglich die Zeilenkennung um die Einheit 1 von Zeile zu Zeile erhöht wird, kann hier die Ausfüllfunktion verwendet werden. Dazu wird zunächst nur die Funktion =B2*C2 in Zelle D2 eingetragen und mit der Return-Taste bestätigt. Nach der Eintragung wird der Zellcursor auf D2 zurückgesetzt. Der Zellcursor hat am rechten unteren Eck eine Verdickung, auf die nun die Maus geschoben wird, bis ein kleines Kreuz erscheint; dann muss die linke Maustaste gedrückt und gehalten werden. Bei gedrückter linker Maustaste wird nun die Maus über die Zeilen 3, 4, 5 usw. bis zur Zelle D11 senkrecht nach unten geführt und dort schliesslich losgelassen. Das Ergebnis ist der automatische Eintrag der passenden Funktion in den Zellen D3:D11. Die Funktion =B2*C2 aus Zelle D2 wurde somit nach unten ausgefüllt. Der Begriff des relativen Zellbezugs bedeutet hier, dass die Zeilenkennungen relativ zur Zeilenhöhe um die Einheit 1 erhöht wurden.

Im zweiten Beispiel (Tabelle Absolute Bezüge) kommt als Erweiterung ein Fixkostenbetrag (Zelle F2) hinzu, der bei allen Produkten zusätzlich zum Gesamtpreis hinzuaddiert werden soll. Für Produkt 1 wäre dabei in Zelle D2 der Formeleintrag =B2*C2+F2 einzutragen. Unter Verwendung der Ausfüllfunktion würde jedoch dieser Eintrag für die folgenden Produkte zu falschen Ergebnissen führen, zum Beispiel würde in D3 der Eintrag =B3*C3+F3 stehen, in F3 ist aber nicht der Fixkostenbetrag. Damit nun in den Folgezeilen die Zeilenkennung bei F2 sich nicht verändert, kann man die Zelle F2 genauer gesagt ihre Zeilenkennung 2) in der Formel fixieren. Dies geschieht durch das Setzen des "$"-Zeichens vor die Zeilenkennung. Folgender Formeleintrag in der Zelle D2 ist also erforderlich:

=B2*C2+F$2

Wenn dieser Formeleintrag mit der Ausfüllfunktion nach unten ausgefüllt wird, steht im Ergebnis in den Folgezeilen immer "...+F$2". Der Begriff des absoluten Zellbezugs bedeutet hier, dass der Bezug auf die Zeilenkennung bei F2 absolut ist (fixiert ist).

Relative Bezüge:

Absolute Bezüge:

Fehlerwerte und Korrekturen von Funktionen:

Wenn Excel einen Funktionseintrag nicht korrekt berechnen kann, wird als Ergebnis innerhalb der Ausgabezelle ein Fehlerwert angezeigt.

In bestimmten Zusammenhängen können einzelne Fehlerwerte gezielt gesucht werden. Es gibt auch Situationen, in denen etwa der Fehlerwert #NV unvermeidbar ist und seine Ausweisung über andere Funktionen gezielt abgefangen wird. Diese Fälle sollen jedoch hier nicht vertieft werden. Wichtig ist, dass eine Folge von Zellbezügen in mehreren Funktionseinträgen durch nur einen Fehlerwert gebrochen werden kann. Mit anderen Worten: Bezieht sich eine Nachfolgerzelle auf eine Vorgängerzelle, welche einen Fehlerwert aufweist, so wird auch in der Nachfolgerzelle ein Fehlerwert stehen.

Zur Korrektur von Funktionen mit Fehlerwerten stehen in Excel folgende Eingabemöglichkeiten zur Verfügung:

• Der Zellcursor wird auf die betreffende Zelle gebracht. Durch die Funktionstaste F2 wird die Zelle "eingabeaktiv" und die Funktion kann bearbeitet werden.
• Durch Doppelklick mit der linken Maustaste kann die Zelle zur Eingabe aktiviert werden.
• Es kann die Bearbeitungsleiste angeklickt werden, nach dem der Zellcursor auf der Zelle steht. Die Korrekturen können dann direkt in der Bearbeitungsleiste durchgeführt werden.
• Der Zelleintrag kann einfach komplett überschrieben werden.

Bei allen genannten Möglichkeiten muß abschliessend die Return-Taste gedrückt werden.

Für Rückfragen steht Ihnen der Autor von excelfunktionen.de gerne zur Verfügung.