Excel für die Entscheidungsfindung

Neben Formeln besteht eine weitere Möglichkeit, mathematische Berechnungen in Excel durchzuführen, in Funktionen. Statistische Funktionen wenden einen mathematischen Prozess auf eine Gruppe von Zellen in einem Arbeitsblatt an. Die Summenfunktion wird beispielsweise verwendet, um die in einem Zellenbereich enthaltenen Werte zu addieren. Eine Liste häufig verwendeter statistischer Funktionen ist in Tabelle 2.4 dargestellt. Funktionen sind effizienter als Formeln, wenn Sie einen mathematischen Prozess auf eine Gruppe von Zellen anwenden. Wenn Sie eine Formel verwenden, um die Werte in einem Zellenbereich hinzuzufügen, müssen Sie jede Zellenposition einzeln zur Formel hinzufügen. Dies kann sehr zeitaufwändig sein, wenn Sie die Werte an einigen hundert Zellpositionen hinzufügen müssen. Wenn Sie jedoch eine Funktion verwenden, können Sie alle Zellen hervorheben, die Werte enthalten, die Sie in nur einem Schritt summieren möchten. Dieser Abschnitt zeigt eine Vielzahl statistischer Funktionen, die wir der persönlichen Budgetarbeitsmappe hinzufügen werden. In diesem Abschnitt werden nicht nur Funktionen demonstriert, sondern auch der Prozentsatz der Gesamtberechnungen und die Verwendung absoluter Referenzen überprüft.

Tabelle 2.4 Häufig verwendete statistische Funktionen

Die Summenfunktion wird verwendet, wenn Sie Summen für einen Zellbereich oder eine Gruppe ausgewählter Zellen in einem Arbeitsblatt berechnen müssen. In Bezug auf das Budgetdetailarbeitsblatt verwenden wir die Summenfunktion, um die Summen in Zeile 12 zu berechnen. Es ist wichtig zu beachten, dass es mehrere Methoden zum Hinzufügen einer Funktion zu einem Arbeitsblatt gibt, die im Rest dieses Kapitels demonstriert werden. Im Folgenden wird veranschaulicht, wie eine Funktion zu einem Arbeitsblatt hinzugefügt werden kann, indem sie in eine Zellenposition eingegeben wird:

1. Klicken Sie auf die Registerkarte Budgetdetailarbeitsblatt, um das Arbeitsblatt zu öffnen.
2. Klicken Sie auf Zelle C12.
3. Geben Sie ein Gleichheitszeichen = ein.
4. Geben Sie den Funktionsnamen SUM ein.
5. Geben Sie eine offene Klammer ein (.
6. Klicken Sie auf Zelle C3 und ziehen Sie nach unten zu Zelle C11. Dadurch wird der Bereich C3: C11 in die Funktion eingefügt.
7. Geben Sie eine schließende Klammer ein ).
8. Drücken Sie die Eingabetaste. Die Funktion berechnet die Summe für die Spalte Monatliche Ausgaben, die 1.496 USD beträgt.

Abbildung 2.11 zeigt das Aussehen der Summenfunktion, die dem Arbeitsblatt Budgetdetail hinzugefügt wurde, bevor die Eingabetaste gedrückt wurde.

Wie in Abbildung 2.11 gezeigt, wurde die Summenfunktion zu Zelle C12 hinzugefügt. Diese Funktion wird jedoch auch benötigt, um die Summen in den Spalten Jährliche Ausgaben und jährliche Ausgaben zu berechnen. Die Funktion kann aufgrund der relativen Referenzierung kopiert und in diese Zellpositionen eingefügt werden. Die relative Referenzierung dient für Funktionen demselben Zweck wie für Formeln. Im Folgenden wird gezeigt, wie die gesamte Zeile vervollständigt wird:

1. Klicken Sie im Arbeitsblatt Budgetdetail auf Zelle C12.
2. Klicken Sie auf der Registerkarte Start des Menübands auf die Schaltfläche Kopieren.
3. Markieren Sie die Zellen D12 und E12.
4. Klicken Sie auf der Registerkarte Start des Menübands auf die Schaltfläche Einfügen. Dadurch wird die Summenfunktion in die Zellen D12 und E12 eingefügt und die Summen für diese Spalten berechnet.
5. Klicken Sie auf Zelle F11.
6. Klicken Sie auf der Registerkarte Start des Menübands auf die Schaltfläche Kopieren.
7. Klicken Sie auf Zelle F12 und dann auf die Schaltfläche Einfügen auf der Registerkarte Start des Menübands. Da wir jetzt Summen in Zeile 12 haben, können wir die Prozentänderungsformel in diese Zeile einfügen.

Abbildung 2.12 zeigt die Ausgabe der Summenfunktion, die den Zellen C12, D12 und E12 hinzugefügt wurde. Außerdem wurde die prozentuale Änderungsformel kopiert und in Zelle F12 eingefügt. Beachten Sie, dass diese Version des Budgets einen Rückgang der Ausgaben um 1,7% im Vergleich zum Vorjahr plant.

Absolute Referenzen (Berechnung des Prozentsatzes der Summen)

Datendatei: Fahren Sie mit dem persönlichen Budget von CH2 fort.

Da Summen zu Zeile 12 des Arbeitsblatts Budgetdetail hinzugefügt wurden, kann ab Zelle B3 ein Prozentsatz der Gesamtberechnung zu Spalte B hinzugefügt werden. Der Prozentsatz der Gesamtberechnung zeigt den Prozentsatz für jeden Wert in der Spalte Jährliche Ausgaben in Bezug auf die Gesamtsumme in Zelle D12. Nachdem die Formel erstellt wurde, muss jedoch die relative Referenzierungsfunktion von Excel deaktiviert werden, bevor die Formel kopiert und in die übrigen Zellpositionen in der Spalte eingefügt wird. Das Deaktivieren der relativen Referenzierungsfunktion von Excel erfolgt über eine absolute Referenz. Die folgenden Schritte erklären, wie dies geschieht:

1. Klicken Sie im Arbeitsblatt Budgetdetail auf Zelle B3.
2. Geben Sie ein Gleichheitszeichen = ein.
3. Klicken Sie auf Zelle D3.
4. Geben Sie einen Schrägstrich / ein.
5. Klicken Sie auf Zelle D12.
6. Drücken Sie die Eingabetaste. Sie werden sehen, dass die Haushaltsmittel 16,7% des jährlichen Ausgabenbudgets ausmachen (siehe Abbildung 2.14).

Abbildung 2.14 zeigt die ausgefüllte Formel, mit der der Prozentsatz berechnet wird, den die jährlichen Ausgaben der Haushaltsversorger für die jährlichen Gesamtausgaben des Budgets darstellen (siehe Zelle B3). Normalerweise würden wir diese Formel kopieren und in den Bereich B4:B11 einfügen. Aufgrund der relativen Referenzierung erhöhen sich jedoch beide Zellreferenzen um eine Zeile, wenn die Formel in die Zellen unter B3 eingefügt wird. Dies ist für die erste Zellreferenz in der Formel (D3) in Ordnung, jedoch nicht für die zweite Zellreferenz (D12). Abbildung 2.15 veranschaulicht, was passiert, wenn wir die Formel im aktuellen Zustand in den Bereich B4: B12 einfügen. Beachten Sie, dass Excel den Fehlercode #DIV / 0 erzeugt. Dies bedeutet, dass Excel versucht, eine Zahl durch Null zu teilen, was unmöglich ist. Wenn Sie sich die Formel in Zelle B4 ansehen, sehen Sie, dass die erste Zellreferenz von D3 in D4 geändert wurde. Dies ist in Ordnung, da wir jetzt die jährlichen Ausgaben für Versicherungen durch die gesamten jährlichen Ausgaben in Zelle D12 teilen möchten. Excel hat jedoch auch den D12-Zellverweis in D13 geändert. Da die Zellenposition D13 leer ist, erzeugt die Formel den Fehlercode #DIV / 0.

Um den in Abbildung 2.15 gezeigten Dividieren-durch-Null-Fehler zu beseitigen, müssen wir einen absoluten Verweis auf Zelle D12 in der Formel hinzufügen. Eine absolute Referenz verhindert, dass die relative Referenzierung eine Zellreferenz in einer Formel ändert. Dies wird auch als Sperren einer Zelle bezeichnet. Im Folgenden wird erläutert, wie dies erreicht wird:

1. Doppelklicken Sie auf Zelle B3.
2. Platzieren Sie den Mauszeiger vor D12 und klicken Sie. Der blinkende Cursor sollte sich vor dem D in der Zellenreferenz D12 befinden.
3. Drücken Sie die Taste F4. Sie sehen ein Dollarzeichen ($) vor dem Spaltenbuchstaben D und der Zeilennummer 12. Sie können auch die Dollarzeichen vor dem Spaltenbuchstaben und der Zeilennummer eingeben.
4. Drücken Sie die Eingabetaste.
5. Klicken Sie auf Zelle B3.
6. Klicken Sie auf der Registerkarte Start des Menübands auf die Schaltfläche Kopieren.
7. Markieren Sie den Bereich B4:B11.
8. Klicken Sie auf der Registerkarte Start des Menübands auf die Schaltfläche Einfügen.

Abbildung 2.16 zeigt den Prozentsatz der Gesamtformel mit einer absoluten Referenz zu D12. Beachten Sie, dass in Zelle B4 die Zellreferenz D12 bleibt, anstatt zu D13 zu wechseln, wie in Abbildung 2.15 gezeigt. Außerdem werden Sie sehen, dass die Prozentsätze in den restlichen Zellen in der Spalte berechnet werden und der Dividieren-durch-Null-Fehler jetzt beseitigt ist.

Die Zählfunktion

Datendatei: Weiter mit CH2 Persönliches Budget.

Die nächste Funktion, die wir dem Budgetdetailarbeitsblatt hinzufügen werden, ist die Zählfunktion. Die COUNT-Funktion wird verwendet, um zu bestimmen, wie viele Zellen in einem Bereich einen numerischen Eintrag enthalten. Die Zählfunktion funktioniert nicht zum Zählen von Text oder anderen nicht numerischen Einträgen. Für das Arbeitsblatt Budgetdetail verwenden wir die Zählfunktion, um die Anzahl der Elemente zu zählen, die in der Spalte Jährliche Ausgaben (Spalte D) geplant sind. Im Folgenden wird erläutert, wie die Funktion COUNT mithilfe der Funktionsliste zum Arbeitsblatt hinzugefügt wird:

1. Klicken Sie im Arbeitsblatt Budgetdetail auf Zelle D13.
2. Geben Sie ein Gleichheitszeichen = ein.
3. Geben Sie den Buchstaben C ein.
4. Klicken Sie auf den Abwärtspfeil in der Bildlaufleiste der Funktionsliste (siehe Abbildung 2.17) und suchen Sie die Wortzahl.
5. Doppelklicken Sie auf die Wortzahl aus der Funktionsliste.
6. Markieren Sie den Bereich D3:D11.
7. Sie können eine schließende Klammer eingeben ) und dann die Eingabetaste drücken oder einfach die Eingabetaste drücken, und Excel schließt die Funktion für Sie. Die Funktion erzeugt eine Ausgabe von 9, da auf dem Arbeitsblatt 9 Elemente geplant sind.

Abbildung 2.17 zeigt das Funktionslistenfeld, das nach Abschluss der Schritte 2 und 3 für die Zählfunktion angezeigt wird. Die Funktionsliste bietet eine alternative Methode zum Hinzufügen einer Funktion zu einem Arbeitsblatt.

Abbildung 2.18 zeigt die Ausgabe der Zählfunktion nach Drücken der Eingabetaste. Die Funktion zählt die Anzahl der Zellen im Bereich D3:D11, die einen numerischen Wert enthalten. Das Ergebnis von 9 zeigt an, dass für dieses Budget 9 Kategorien geplant sind.

Die Durchschnittsfunktion

Die nächste Funktion, die wir dem Budgetdetailarbeitsblatt hinzufügen werden, ist die Durchschnittsfunktion. Diese Funktion wird verwendet, um das arithmetische Mittel für eine Gruppe von Zahlen zu berechnen. Für das Budgetdetailarbeitsblatt verwenden wir die Funktion, um den Durchschnitt der Werte in der Spalte Jährliche Ausgaben zu berechnen. Wir werden dies mithilfe der Funktionsbibliothek zum Arbeitsblatt hinzufügen. Die folgenden Schritte erklären, wie dies erreicht wird:

1. Klicken Sie im Arbeitsblatt Budgetdetail auf Zelle D14.
2. Klicken Sie im Menüband auf die Registerkarte Formeln.
3. Klicken Sie in der Befehlsgruppe Funktionsbibliothek auf die Schaltfläche Weitere Funktionen.
4. Platzieren Sie den Mauszeiger über die statistische Option aus der Dropdown-Liste der Optionen.
5. Klicken Sie in der Liste der im Menü angezeigten Funktionen auf den Namen der DURCHSCHNITTLICHEN Funktion (siehe Abbildung 2.19). Dies öffnet das Dialogfeld Funktionsargumente.
6. Klicken Sie im Dialogfeld Funktionsargumente auf die Schaltfläche Dialog reduzieren (siehe Abbildung 2.20).
7. Markieren Sie den Bereich D3:D11.
8. Klicken Sie im Dialogfeld Funktionsargumente auf die Schaltfläche Dialog erweitern (siehe Abbildung 2.21). Sie können auch die Eingabetaste drücken, um das gleiche Ergebnis zu erhalten.
9. Klicken Sie im Dialogfeld Funktionsargumente auf die Schaltfläche OK. Dadurch wird dem Arbeitsblatt die Durchschnittsfunktion hinzugefügt.

Abbildung 2.19 veranschaulicht, wie eine Funktion aus der Funktionsbibliothek auf der Registerkarte Formeln des Menübands ausgewählt wird.

Abbildung 2.20 zeigt das Dialogfeld Funktionsargumente. Dies wird angezeigt, nachdem eine Funktion aus der Funktionsbibliothek ausgewählt wurde. Die Schaltfläche Dialog reduzieren wird verwendet, um das Dialogfeld auszublenden, sodass ein Bereich von Zellen im Arbeitsblatt hervorgehoben und dann der Funktion hinzugefügt werden kann.

Abbildung 2.21 zeigt, wie ein Zellbereich aus dem Dialogfeld Funktionsargumente ausgewählt werden kann, sobald er reduziert wurde.

Abbildung 2.22 zeigt das Dialogfeld Funktionsargumente, nachdem der Zellbereich für die Durchschnittsfunktion definiert wurde. Das Dialogfeld zeigt das Ergebnis der Funktion an, bevor sie der Zellenposition hinzugefügt wird. Auf diese Weise können Sie die Funktionsausgabe bewerten, um festzustellen, ob sie sinnvoll ist, bevor Sie sie dem Arbeitsblatt hinzufügen.

definiert wurde Abbildung 2.23 zeigt die abgeschlossene Durchschnittsfunktion im Arbeitsblatt Budgetdetail. Die Ausgabe der Funktion zeigt, dass wir im Durchschnitt 1.994 US-Dollar für jede der in Spalte A des Budgets aufgeführten Kategorien ausgeben werden. Diese Berechnung der durchschnittlichen Ausgaben pro Kategorie kann als Indikator verwendet werden, um zu bestimmen, welche Kategorien mehr oder weniger als die durchschnittlichen budgetierten Ausgaben kosten.

Die MAX- und MIN-Funktionen

Datendatei: Fahren Sie mit CH2 Personal Budget fort.

Die letzten beiden statistischen Funktionen, die wir dem Budget-Detailarbeitsblatt hinzufügen werden, sind die MAX- und MIN-Funktionen. Diese Funktionen identifizieren die höchsten und niedrigsten Werte in einem Zellbereich. In den folgenden Schritten wird erläutert, wie Sie diese Funktionen dem Budgetdetailarbeitsblatt hinzufügen:

1. Klicken Sie im Arbeitsblatt Budgetdetail auf Zelle D15.
2. Geben Sie ein Gleichheitszeichen = ein.
3. Geben Sie das Wort MIN ein.
4. Geben Sie eine offene Klammer ein (.
5. Markieren Sie den Bereich D3:D11.
6. Geben Sie eine schließende Klammer ein ) und drücken Sie die Eingabetaste oder drücken Sie einfach die Eingabetaste, und Excel schließt die Funktion für Sie. Die MIN-Funktion erzeugt eine Ausgabe von $ 1.200, was der niedrigste Wert in der Spalte Jährliche Ausgaben ist (siehe Abbildung 2.24).
7. Klicken Sie auf Zelle D16.
8. Geben Sie ein Gleichheitszeichen = ein.
9. Geben Sie das Wort MAX.
10. Geben Sie eine offene Klammer ein (.
11. Markieren Sie den Bereich D3:D11.
12. Geben Sie eine schließende Klammer ein ) und drücken Sie die Eingabetaste oder drücken Sie einfach die Eingabetaste, und Excel schließt die Funktion für Sie. Die MAX-Funktion erzeugt eine Ausgabe von $ 3.500. Dies ist der höchste Wert in der Spalte Jährliche Ausgaben (siehe Abbildung 2.25).

Kopieren und Einfügen von Formeln (Einfügen ohne Formate)

Datendatei: Fahren Sie mit CH2 Personal Budget fort.

Wie in Abbildung 2.25 dargestellt, fassen die Funktionen COUNT, AVERAGE, MIN und MAX die Daten in der Spalte Annual Spend zusammen. Sie werden auch feststellen, dass es Platz zum Kopieren und Einfügen dieser Funktionen unter der Spalte LYSIS gibt. So können wir vergleichen, was wir letztes Jahr ausgegeben haben und was wir dieses Jahr ausgeben wollen. Normalerweise würden wir diese Funktionen einfach kopieren und in den Bereich E13: E16 einfügen. Möglicherweise haben Sie jedoch den zweizeiligen Rand bemerkt, der um den Umfang des Bereichs B13: E16 verwendet wurde. Wenn wir den regulären Befehl Einfügen verwenden, wird die doppelte Zeile auf der rechten Seite des Bereichs E13: E16 durch eine einzelne Zeile ersetzt. Daher werden wir einen der speziellen Befehle zum Einfügen verwenden, um nur die Funktionen ohne Formatierungsbehandlungen einzufügen. Dies wird durch die folgenden Schritte erreicht:

1. Markieren Sie den Bereich D13: D16 im Arbeitsblatt Budgetdetail.
2. Klicken Sie auf der Registerkarte Start des Menübands auf die Schaltfläche Kopieren.
3. Klicken Sie auf Zelle E13.
4. Klicken Sie auf den Abwärtspfeil unter der Schaltfläche Einfügen auf der Registerkarte Start des Menübands.
5. Klicken Sie in der Dropdown-Liste der Schaltflächen auf die Option Formeln (siehe Abbildung 2.26).

Abbildung 2.26 zeigt die Liste der Schaltflächen, die angezeigt werden, wenn Sie auf der Registerkarte Start des Menübands auf den Abwärtspfeil unter der Schaltfläche Einfügen klicken. Beachten Sie bei diesen Optionen, dass Sie eine Vorschau anzeigen können, bevor Sie eine Auswahl treffen, indem Sie den Mauszeiger über die Optionen ziehen. Wie in der Abbildung gezeigt, können Sie, wenn Sie den Mauszeiger über die Schaltfläche Formeln setzen, sehen, wie die Funktionen angezeigt werden, bevor Sie eine Auswahl treffen. Beachten Sie, dass sich der zweizeilige Rahmen nicht ändert, wenn diese Option in der Vorschau angezeigt wird. Aus diesem Grund wird diese Auswahl anstelle der regulären Einfügeoption getroffen.