In dieser Lektion stellen wir Ihnen die Grundregeln für das Erstellen von Formeln und die Verwendung von Funktionen vor. Da wir der Meinung sind, dass man am besten durch Übung lernt, stellen wir mehrere Beispiele zur Verfügung und erklären sie im Detail. Zu den Themen, die wir behandeln werden, gehören:
- Zeilen und Spalten
- Beispiel für eine mathematische Funktion: SUMME()
- Operatoren
- Operatorenvorrang
- Beispiel Finanzfunktion: PMT(), Kreditauszahlung
- Verwendung einer „String“-Funktion („String“ ist die Abkürzung für „Textkette“) innerhalb einer Formel und Verschachtelung von Funktionen
Formeln sind eine Mischung aus „Funktionen“, „Operatoren“ und „Operanden“. Bevor wir ein paar Formeln schreiben, müssen wir eine Funktion erstellen, aber bevor wir eine Funktion erstellen können, müssen wir zuerst die Zeilen- und Spaltennotation verstehen.
Zeilen und Spalten
Um zu verstehen, wie man Formeln und Funktionen schreibt, müssen Sie etwas über Zeilen und Spalten wissen.
Zeilen verlaufen horizontal und Spalten vertikal. Um sich zu merken, welche welche ist, denken Sie an eine Säule, die ein Dach hält – Spalten gehen von oben nach unten und somit gehen Zeilen von links nach rechts.
Spalten werden mit Buchstaben beschriftet, Zeilen mit Zahlen. Die erste Zelle in der Tabelle ist A1, was Spalte A, Zeile 1 bedeutet. Die Spalten sind mit A-Z beschriftet. Wenn das Alphabet ausläuft, stellt Excel einen weiteren Buchstaben voran: AA, AB, AC… AZ, BA, BC, BC, usw.
Beispiel: Funktion Summe()
Nun wollen wir Ihnen zeigen, wie Sie eine Funktion verwenden.
Sie verwenden Funktionen, indem Sie sie direkt eingeben oder den Funktionsassistenten verwenden. Der Funktionsassistent öffnet sich, wenn Sie entweder eine Funktion aus dem Menü „Formeln“ aus der „Funktionsbibliothek“ auswählen. Andernfalls können Sie = in eine Zelle eingeben, und ein praktisches Dropdown-Menü ermöglicht Ihnen die Auswahl einer Funktion.
Der Assistent sagt Ihnen, welche Argumente Sie für jede Funktion angeben müssen. Er bietet auch einen Link zu einer Online-Anleitung, wenn Sie Hilfe benötigen, um zu verstehen, was die Funktion tut und wie sie zu verwenden ist. Wenn Sie zum Beispiel =sum in eine Zelle eingeben, zeigt Ihnen der Inline-Assistent, welche Argumente für die Funktion SUMME erforderlich sind.
Wenn Sie eine Funktion eingeben, ist der Assistent inline oder direkt an Ihren Fingern. Wenn Sie eine Funktion aus dem Menü „Formeln“ auswählen, ist der Assistent ein Popup-Fenster. Hier ist der Popup-Assistent für die Funktion SUMME().
Für unsere erste Funktion verwenden wir SUMME(), die eine Liste von Zahlen addiert.
Angenommen, wir haben diese Tabellenkalkulation, die Pläne für den Urlaub Ihrer Familie enthält:
Um die Gesamtkosten zu berechnen, könnten Sie =b2+b3+b4+b5 schreiben, aber es ist einfacher, die Funktion SUMME() zu verwenden.
In Excel suchen Sie nach dem Symbol Σ in der linken oberen Ecke des Excel-Bildschirms, um die AutoSum-Schaltfläche zu finden (Mathematiker verwenden den griechischen Buchstaben Σ für das Addieren einer Reihe von Zahlen).
Wenn sich der Cursor unter den Zahlen des Familienbudgets befindet, ist Excel schlau genug, um zu wissen, dass Sie die Liste der Zahlen über der Stelle, an der Sie den Cursor platziert haben, summieren wollen, und hebt die Zahlen daher hervor.
Drücken Sie die Eingabetaste, um den von Excel ausgewählten Bereich zu übernehmen, oder verwenden Sie den Cursor, um zu ändern, welche Zellen ausgewählt sind.
Wenn Sie sich anschauen, was Excel in das Arbeitsblatt geschrieben hat, können Sie sehen, dass es diese Funktion geschrieben hat:
In dieser Formel summiert Excel die Zahlen von B2 bis B9. Beachten Sie, dass wir unter Zeile 5 etwas Platz gelassen haben, damit Sie das Budget für den Familienurlaub aufstocken können – die Kosten werden sicherlich steigen, wenn die Liste der Kinder, was sie tun und wohin sie fahren wollen, länger wird!
Math-Funktionen arbeiten nicht mit Buchstaben, wenn Sie also Buchstaben in die Spalte eingeben, wird das Ergebnis als „#NAME?“ angezeigt, wie unten gezeigt.
#NAME? zeigt an, dass eine Art Fehler vorliegt. Das kann alles Mögliche sein, z. B.:
- schlechter Zellbezug
- Verwendung von Buchstaben in mathematischen Funktionen
- Auslassung von erforderlichen Argumenten
- falsche Schreibweise des Funktionsnamens
- unerlaubte mathematische Operationen wie Division durch 0
Am einfachsten können Sie die Argumente in einer Berechnung mit der Maus auswählen. Sie können die Liste der Argumente der Funktion ergänzen oder entfernen, indem Sie das Kästchen, das Excel zeichnet, wenn Sie die Maus bewegen oder in eine andere Zelle klicken, vergrößern oder verkleinern.
Wir haben auf den oberen Rand des von Excel gezeichneten Quadrats geklickt, um „Flugtickets“ aus dem Budget zu nehmen. Sie sehen das Fadenkreuz-Symbol, mit dem Sie den ausgewählten Bereich vergrößern oder verkleinern können.
Drücken Sie auf „Enter“, um das Ergebnis zu bestätigen.
Berechnungsoperatoren
Es gibt zwei Arten von Operatoren: Mathematik und Vergleich.
Es gibt auch andere Operatoren, die nichts mit Mathematik zu tun haben, wie z.B. „&“, was bedeutet, dass zwei Zeichenketten aneinandergehängt werden (Ende an Ende). Zum Beispiel: „& “ macht Spaß“ ist gleich „Excel macht Spaß“.
Nun schauen wir uns die Vergleichsoperatoren an.
Wie Sie oben sehen können, arbeiten Vergleichsoperatoren mit Zahlen und Text.
Bei der Eingabe von =“a“>“b“ in eine Zelle wird „FALSCH“ angezeigt, da „a“ nicht größer als „b“ ist. „b“ kommt im Alphabet nach „a“, also „a“ > „b“ oder „b“ > „a.“
Operatorreihenfolge Vorrang
Vorrang ist eine Idee aus der Mathematik. Excel muss die gleichen Regeln befolgen wie die Mathematik. Dieses Thema ist etwas komplizierter, also atmen Sie durch und lassen Sie uns eintauchen.
Operatorreihenfolge bedeutet die Reihenfolge, in der der Computer die Antwort berechnet. Wie wir in Lektion 1 erklärt haben, ist der Flächeninhalt eines Kreises πr2, was dasselbe ist wie π * r * r. Es ist nicht (πr)2.
So müssen Sie die Rangfolge verstehen, wenn Sie eine Formel schreiben.
Generell kann man Folgendes sagen:
- Excel wertet zuerst die Elemente in Klammern aus und arbeitet von innen nach außen.
- Dann verwendet es die Vorrangregeln der Mathematik.
- Wenn zwei Elemente den gleichen Vorrang haben, arbeitet Excel von links nach rechts.
Die Vorrangregeln der mathematischen Operatoren sind unten in absteigender Reihenfolge dargestellt.
Es gibt weitere Vorrangregeln, die sich auf Zeichenketten und Referenzoperatoren beziehen. Für den Moment bleiben wir bei dem, was wir gerade behandelt haben. Schauen wir uns nun einige Beispiele an.
Beispiel: Berechnen des Flächeninhalts eines Kreises
Der Flächeninhalt eines Kreises ist =PI() * Radius ^ 2.
Wenn wir uns die obige Tabelle ansehen, sehen wir, dass der Exponent vor der Multiplikation kommt. Der Computer berechnet also zuerst den Radius ^ 2 und multipliziert dann das Ergebnis mit Pi.
Beispiel: Berechnung einer Gehaltserhöhung
Angenommen, Ihr Chef entscheidet, dass Sie gute Arbeit leisten, und er gibt Ihnen eine Gehaltserhöhung von 10 %! Wie würden Sie Ihr neues Gehalt berechnen?
Erst denken Sie daran, dass die Multiplikation vor der Addition kommt.
Ist es =Gehalt + Gehalt * 10% oder ist es =Gehalt + (Gehalt * 10%)?
Angenommen, Ihr Gehalt beträgt 100 $. Mit einer Gehaltserhöhung von 10 % ist Ihr neues Gehalt:
= 100 + 100 * 10% = 100 + 10 = 110
Sie können es auch so schreiben:
=100 + (100 * 10%) = 100 + 10 = 110
Im zweiten Fall haben wir die Rangfolge durch die Verwendung von Klammern explizit gemacht. Denken Sie daran, dass Klammern vor jeder anderen Operation ausgewertet werden.
Die einfachere Schreibweise ist übrigens = Gehalt * 110%
Ein paar weitere Beispiele
Hier ist ein weiteres Beispiel: = 4 * 3 / 2. Wie lautet die Antwort?
Wir sehen aus den Regeln in der Tabelle oben, dass * und / den gleichen Vorrang haben. Excel arbeitet also von links nach rechts, 4 * 3 = 12 zuerst, dann dividiert es das durch 2, um 6 zu erhalten.
Auch das könnte man explizit machen, indem man = (4 * 3) / 2 schreibt
Was ist mit = 4 + 3 * 2?
Der Computer sieht beide Operatoren * und +. Der Vorrangregel folgend (Multiplikation kommt vor Addition) berechnet er also zuerst 3 * 2 = 6 und addiert dann 4, um 10 zu erhalten.
Wenn Sie die Vorrangregel ändern wollten, würden Sie = (4 + 3) * 2 = 14 schreiben.
Was ist mit diesem = -1 ^ 3?
Die Antwort ist -3, weil der Computer = (-1) ^ 3 = -1 * -1 * -1 = -1 errechnet hat.
So, das sind ein paar Beispiele für mathematische Reihenfolge und Vorrang, wir hoffen, dass das hilft, ein paar Dinge darüber zu klären, wie Excel Berechnungen durchführt (und das ist wahrscheinlich genug Mathe für ein ganzes Leben für einige von Ihnen).
Beispiel: Funktion Darlehenszahlung (PMT)
Schauen wir uns ein Beispiel an, um eine Darlehenszahlung zu berechnen.
Beginnen Sie, indem Sie ein neues Arbeitsblatt erstellen.
Formatieren Sie die Zahlen mit Dollarzeichen und verwenden Sie null Nachkommastellen, da wir uns im Moment nicht für Cents interessieren, da sie keine große Rolle spielen, wenn es um Dollar geht (im nächsten Kapitel untersuchen wir, wie man Zahlen im Detail formatiert). Um zum Beispiel den Zinssatz zu formatieren, klicken Sie mit der rechten Maustaste auf die Zelle und dann auf „Zellen formatieren“. Wählen Sie „Prozent“ und verwenden Sie 2 Nachkommastellen.
Auch die anderen Zellen formatieren Sie für „Währung“ anstelle von „Prozent“ und wählen „Zahl“ für die Kreditlaufzeit.
Nun haben wir:
Fügen Sie die Funktion SUMME() ein, um die monatlichen Ausgaben zu „summieren“.
Beachten Sie, dass die Zelle „Hypothek“ nicht in der Summe enthalten ist. Excel weiß nicht, dass Sie diese Zahl einbeziehen wollen, da es dort keinen Wert gibt. Achten Sie also darauf, die Funktion SUMME() nach oben zu verlängern, indem Sie entweder den Cursor verwenden oder E2 eingeben, wo E3 steht, um die Hypothek in die Summe einzubeziehen.
Setzen Sie den Cursor in die Zahlungszelle (B4).
Im Menü „Formeln“ wählen Sie das Dropdown-Menü „Finanzen“ und dann die Funktion PMT. Der Assistent öffnet sich:
Wählen Sie mit dem Cursor „Rate.“, „nper“ (Darlehenslaufzeit), „Pv“ („Barwert“ oder Darlehensbetrag). Beachten Sie, dass Sie den Zinssatz durch 12 dividieren müssen, da die Zinsen monatlich berechnet werden. Außerdem müssen Sie die Darlehenslaufzeit in Jahren mit 12 multiplizieren, um die Darlehenslaufzeit in Monaten zu erhalten. Drücken Sie „OK“, um das Ergebnis in der Tabelle zu speichern.
Beachten Sie, dass die Zahlung als negative Zahl angezeigt wird: -1013.37062. Um sie positiv zu machen und sie zu den monatlichen Ausgaben zu addieren, zeigen Sie auf die Zelle „Hypothek“ (E2). Geben Sie „=-“ ein und zeigen Sie dann mit dem Cursor auf das Zahlungsfeld. Die resultierende Formel ist =-B4.
Nun sieht die Tabelle so aus:
Ihre monatlichen Ausgaben betragen 1.863 $ – Autsch!
Beispiel: Textfunktion
Hier demonstrieren wir die Verwendung von Funktionen innerhalb einer Formel und von Textfunktionen.
Angenommen, Sie haben eine Liste von Schülern wie unten gezeigt. Der Vor- und Nachname steht in einem Feld, getrennt durch ein Komma. Wir müssen den Nachnamen und den Firmennamen in getrennten Zellen unterbringen. Wie machen wir das?
Um dieses Problem zu lösen, müssen Sie einen Algorithmus verwenden – d.h. eine Schritt-für-Schritt-Prozedur, um dies zu tun.
Betrachten Sie zum Beispiel „Washington, George“. Die Prozedur, um das in zwei Wörter aufzuteilen, wäre:
- Berechnen Sie die Länge der Zeichenkette.
- Finden Sie die Position des Kommas (das zeigt, wo ein Wort endet und das andere beginnt).
- Kopieren Sie die linke Seite der Zeichenkette bis zum Komma.
- Kopieren Sie die rechte Seite der Zeichenkette vom Komma bis zum Ende.
Lassen Sie uns nun Schritt für Schritt in Excel besprechen, wie Sie das mit „George Washington“ machen.
- Berechnen Sie die Länge der Zeichenkette mit der Funktion =LEN(A3) – das Ergebnis ist 18.
- Nun finden Sie die Position des Kommas mit dieser Funktion =FIND(„,“,A3″) – das Ergebnis ist 11.
- Nun nehmen Sie die linke Seite der Zeichenkette bis zum Komma und erstellen Sie diese verschachtelte Formel mit dem Ergebnis aus Schritt 1: =LEFT(A3,FIND(„,“,A3)-1). Beachten Sie, dass wir 1 von der Länge subtrahieren müssen, weil FIND die Position des Kommas angibt.
So sieht das Ganze aus, wenn alle Funktionen in einer Formel zusammengefügt sind. In Zelle B3 sehen Sie, dass diese Formel alle Informationen aus Zelle A3 nimmt und „Washington“ einträgt.
So haben wir „Washington“, jetzt müssen wir „George“ bekommen. Wie machen wir das?
Nachdem wir das Ergebnis aus Schritt 1 in einer eigenen Zelle, sagen wir B6, gespeichert haben, hätten wir eine einfachere Formel =LEFT(A3,B6-1) schreiben können. Aber das verbraucht eine Zelle für den Zwischenschritt.
- Erinnern Sie sich an die Position des Kommas oder berechnen Sie sie neu.
- Berechnen Sie die Länge der Zeichenfolge.
- Zählen Sie die Zeichen vom Ende der Zeichenfolge bis zum Komma.
Nehmen Sie die Anzahl der Zeichen aus Schritt 3 und subtrahieren Sie eins, um das Komma und das Leerzeichen wegzulassen.
Lassen Sie uns das Schritt für Schritt machen.
Ihre Tabellenkalkulation sollte jetzt ähnlich aussehen wie im Screenshot unten. Wir haben die Formeln als Text in den unteren Teil des Arbeitsblatts kopiert, damit es leichter zu lesen und zu sehen ist.
Das war ein wenig schwierig, aber Sie müssen diese Formeln nur einmal schreiben.
Nächstes Mal …
Damit ist unsere Lektion für heute beendet. Sie sollten jetzt ein ziemlich sicheres Verständnis von Formeln und Funktionen, Zeilen und Spalten und der Art und Weise haben, wie dies alles anhand einiger konkreter Beispiele eingesetzt werden kann.