Formel definieren und erstellen

In dieser Lektion stellen wir Ihnen grundlegende Regeln zum Erstellen von Formeln und zum Verwenden von Funktionen vor. Wir glauben, dass eine der besten Möglichkeiten des Lernens die Praxis ist. Deshalb geben wir einige Beispiele und erläutern sie ausführlich. Zu den Themen, die wir behandeln werden, gehören:

SCHULNAVIGATION

Warum benötigen Sie Formeln und Funktionen??
Formel definieren und erstellen
Relative und absolute Zellreferenz und Formatierung
Nützliche Funktionen, die Sie kennenlernen sollten
Suchvorgänge, Diagramme, Statistiken und Pivot-Tabellen

Reihen und Spalten
Beispiel für eine mathematische Funktion: SUM ()
Betreiber
Operator Vorrang
Beispiel Finanzfunktion: PMT (), Darlehenszahlung
Verwenden einer "Zeichenfolge" -Funktion ("Zeichenfolge" ist eine Abkürzung für "Zeichenfolge") innerhalb einer Formel und Verschachtelungsfunktionen

Formeln sind eine Mischung aus "Funktionen", "Operatoren" und "Operanden". Bevor wir einige Formeln schreiben, müssen wir eine Funktion erstellen. Bevor wir jedoch eine Funktion erstellen können, müssen wir zunächst die Zeilen- und Spaltennotation verstehen.

Reihen und Spalten

Um zu verstehen, wie man Formeln und Funktionen schreibt, müssen Sie sich mit Zeilen und Spalten auskennen.

Zeilen laufen horizontal und Spalten vertikal. Um sich zu erinnern, welche ist welche, denken Sie an eine Säule, die ein Dach hält.

Spalten sind mit Buchstaben gekennzeichnet. Reihen nach Zahlen. Die erste Zelle in der Kalkulationstabelle ist A1, dh Spalte A, Zeile 1. Die Spalten sind mit A-Z bezeichnet. Wenn das Alphabet aufgebraucht ist, fügt Excel einen anderen Buchstaben vor: AA, AB, AC… AZ, BA, BC, BC usw..

Beispiel: Funktionssumme ()

Lassen Sie uns nun demonstrieren, wie eine Funktion verwendet wird.

Sie verwenden Funktionen, indem Sie sie direkt in den Funktionsassistenten eingeben. Der Funktions-Assistent wird geöffnet, wenn Sie entweder eine Funktion aus dem Menü „Formulas“ aus der „Funktionsbibliothek“ auswählen. Andernfalls können Sie = in eine Zelle eingeben. In einem praktischen Dropdown-Menü können Sie eine Funktion auswählen.

Der Assistent teilt Ihnen mit, welche Argumente Sie für jede Funktion angeben müssen. Es enthält auch einen Link zu Online-Anweisungen, wenn Sie Hilfe benötigen, um zu verstehen, was die Funktion bewirkt und wie sie verwendet wird. Wenn Sie beispielsweise = sum in eine Zelle eingeben, zeigt der Inline-Assistent an, welche Argumente für die SUM-Funktion erforderlich sind.

Wenn Sie eine Funktion eingeben, befindet sich der Assistent 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 SUM () - Funktion.

Als erste Funktion verwenden wir SUM (), um eine Liste mit Zahlen hinzuzufügen.

Nehmen wir an, wir haben diese Tabelle mit Plänen für die Budgetierung Ihres Familienurlaubs:

Um die Gesamtkosten zu berechnen, können Sie = b2 + b3 + b4 + b5 schreiben, aber es ist einfacher, die SUM () - Funktion zu verwenden.

Suchen Sie in Excel nach dem Symbol Σ in der oberen linken Ecke des Excel-Bildschirms, um die Schaltfläche AutoSummen zu finden (Mathematiker verwenden den griechischen Buchstaben Σ, um eine Reihe von Zahlen hinzuzufügen)..

Wenn sich der Cursor unter den Familienbudget-Zahlen befindet, ist Excel intelligent genug, um zu wissen, dass Sie die Liste der Zahlen oberhalb der Position, an der Sie den Cursor platziert haben, summieren möchten, sodass die Zahlen hervorgehoben werden.

Drücken Sie die Eingabetaste, um den von Excel ausgewählten Bereich zu akzeptieren, oder verwenden Sie den Cursor, um die ausgewählten Zellen zu ändern.

Wenn Sie sich ansehen, was Excel in die Tabelle eingefügt hat, können Sie feststellen, dass es diese Funktion geschrieben hat:

In dieser Formel summiert Excel die Zahlen von B2 bis B9. Beachten Sie, wir haben etwas Platz unter Reihe 5 gelassen, so dass Sie das Budget für den Familienurlaub aufstocken können. Die Kosten werden sicherlich steigen, da die Kinderliste dessen, was sie tun möchten und wo sie hin wollen, länger wächst!

Mathematische Funktionen funktionieren nicht mit Buchstaben. Wenn Sie also Buchstaben in die Spalte einfügen, wird das Ergebnis als „#NAME?“ Angezeigt (siehe unten).

#NAME? zeigt an, dass ein Fehler vorliegt. Es kann eine beliebige Anzahl von Dingen sein, einschließlich:

schlechte Zellreferenz
Verwenden von Buchstaben in mathematischen Funktionen
erforderliche Argumente weglassen
Name der Schreibweise falsch
illegale mathematische Operationen wie Division durch 0

Die Argumente in einer Berechnung können Sie am einfachsten mit der Maus auswählen. Sie können der Liste der Argumente zur Funktion hinzufügen oder sie entfernen, indem Sie das von Excel gezeichnete Feld vergrößern oder verkleinern, wenn Sie die Maus bewegen oder in eine andere Zelle klicken.

Wir haben oben auf das von Excel gezeichnete Feld geklickt, um "Flugtickets" aus dem Budget zu nehmen. Sie können das Fadenkreuz-Symbol sehen, das Sie zeichnen können, um den ausgewählten Bereich zu vergrößern oder zu verkleinern.

Drücken Sie die Eingabetaste, um die Ergebnisse zu bestätigen.

Berechnungsoperatoren

Es gibt zwei Arten von Operatoren: Mathematik und Vergleich.

Mathematischer Operator	Definition
+	Zusatz
-	Subtraktion oder Negation, z. B. 6 * -1 = -6
*	Multiplikation
/	Einteilung
%	Prozent
^	Exponent, z.B. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Es gibt andere Operatoren, die nicht mit der Mathematik zusammenhängen, wie "&", dh zwei aufeinanderfolgende Zeichenfolgen (Join-to-End). Zum Beispiel = "Excel" & "macht Spaß" entspricht "Excel macht Spaß".

Jetzt betrachten wir Vergleichsoperatoren.

Vergleichsoperator	Definition
=	beispielsweise gleich 2 = 4 oder "b" = "b"
>	größer als beispielsweise 4> 2 oder "b"> "a"
<	weniger als z. B. 2 < 4 or “a” < “b”
> =	größer als oder gleich - ein anderer Weg, dies zu denken, ist> = bedeutet entweder > oder =.
<=	weniger als oder gleich.
	beispielsweise nicht gleich 46

Wie Sie oben sehen können, arbeiten Vergleichsoperatoren mit Zahlen und Text.

Wenn Sie in eine Zelle = "a"> "b" eingeben, wird "FALSE" angezeigt, da "a" nicht größer als "b" ist. "B" kommt nach "a" im Alphabet, also "a". > "B" oder "B"> "a."

Operator Reihenfolge Vorrang

Ordnungsvorrang ist eine Idee aus der Mathematik. Excel muss den gleichen Regeln wie die Mathematik folgen. Dieses Thema ist komplizierter, also atmen Sie ein und lassen Sie uns eintauchen.

Reihenfolge der Reihenfolge bedeutet die Reihenfolge, in der der Computer die Antwort berechnet. Wie in Lektion 1 erläutert, ist die Fläche eines Kreises πr², das ist das gleiche wie π * r * r. Es ist nicht (πr)².

Sie müssen also die Reihenfolge der Reihenfolge verstehen, wenn Sie eine Formel schreiben.

Im Allgemeinen können Sie das sagen:

Excel wertet zuerst Elemente in Klammern aus, die von innen nach außen arbeiten.
Es verwendet dann die Ordnungsvorrangregeln der Mathematik.
Wenn zwei Elemente dieselbe Priorität haben, arbeitet Excel von links nach rechts.

Die Rangfolge der mathematischen Operatoren ist in absteigender Reihenfolge dargestellt.

( und )	Wenn Klammern verwendet werden, überschreiben sie die normalen Vorrangregeln. Das bedeutet, dass Excel diese Berechnung zuerst durchführt. Wir erklären das weiter unten.
-	Negation, z. B. -1. Dies ist dasselbe wie das Multiplizieren einer Zahl mit -1. -4 = 4 * (-1)
%	Prozent bedeutet Multiplikation mit 100. Zum Beispiel 0,003 = 0,3%.
^	Exponent, z. B. 10 ^ 2 = 100
* und /	Multiplizieren und dividieren. Wie können zwei Operatoren dieselbe Priorität haben? Es bedeutet nur, dass wenn eine Formel zwei weitere Operatoren mit derselben Priorität hat, die Berechnung von links nach rechts erfolgt.
+ und -	Addition und Subtraktion.

Es gibt andere Vorrangregeln, die sich auf Strings und Referenzoperatoren beziehen. Im Moment bleiben wir einfach bei dem, was wir gerade behandelt haben. Schauen wir uns jetzt einige Beispiele an.

Beispiel: Berechnung der Fläche eines Kreises

Die Fläche eines Kreises ist= PI () * Radius ^ 2.

Wenn Sie die Tabelle oben betrachten, sehen Sie, dass Exponenten vor der Multiplikation stehen. Der Computer berechnet also zuerst den Radius ^ 2 und multipliziert dann das Ergebnis mit Pi.

Beispiel: Berechnung einer Gehaltserhöhung

Nehmen wir an, Ihr Chef entscheidet, dass Sie einen guten Job machen und er oder sie wird Ihnen eine Erhöhung von 10% geben! Wie würden Sie Ihr neues Gehalt berechnen??

Denken Sie zunächst daran, dass die Multiplikation vor der Addition erfolgt.

Ist es = Gehalt + Gehalt * 10% oder ist es = Gehalt + (Gehalt * 10%)?

Angenommen, Ihr Gehalt beträgt 100 US-Dollar. Mit einer Erhöhung von 10% beträgt 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 anhand von Klammern explizit gemacht. Denken Sie daran, dass Klammern vor jeder anderen Operation ausgewertet werden.

Der einfachere Weg, dies zu schreiben, ist übrigens = Gehalt * 110%

Klammern können ineinander verschachtelt sein. Wenn wir also (3 + (4 * 2)) schreiben und von innen nach außen arbeiten, berechnet man zuerst 4 * 2 = 8 und addiert dann 3 + 8, um 11 zu erhalten.

Noch ein paar Beispiele

Hier ist ein anderes Beispiel: = 4 * 3 / 2. Wie lautet die Antwort??

Wir sehen aus den Regeln in der obigen Tabelle, dass * und / gleichrangig sind. Excel arbeitet also von links nach rechts, zuerst 4 * 3 = 12, dann dividiert es durch 2, um 6 zu erhalten.

Das könnte man wiederum durch das Schreiben von = (4 * 3) / 2 explizit machen

Was ist mit = 4 + 3 * 2??

Der Computer sieht sowohl * als auch + Operatoren. Nach den Regeln der Rangfolge (Multiplikation kommt vor der Addition) berechnet sie zuerst 3 * 2 = 6 und addiert dann 4, um 10 zu erhalten.

Wenn Sie die Rangfolge ändern möchten, schreiben Sie = (4 + 3) * 2 = 14.

Was ist mit diesem hier = -1 ^ 3?

Dann lautet die Antwort -3, da der Computer = (-1) ^ 3 = -1 * -1 * -1 = -1 berechnet.

Denken Sie daran, dass negative Zeiten negativ sind und negative Zeiten negativ. Sie können dies so sehen (-1 * -1) * -1 = 1 * -1 = -1.

Es gibt also ein paar Beispiele für mathematische Ordnung und Priorität. Wir hoffen, dass damit ein paar Dinge darüber geklärt werden, wie Excel Berechnungen durchführt (und das ist wahrscheinlich genug Mathematik, um einige von Ihnen ein Leben lang zu halten)..

Beispiel: Funktion Darlehenszahlung (PMT)

Schauen wir uns ein Beispiel an, um eine Darlehenszahlung zu berechnen.

Beginnen Sie mit dem Erstellen eines neuen Arbeitsblatts.

Formatieren Sie die Zahlen mit Dollarzeichen und verwenden Sie keine Dezimalstellen, da wir uns derzeit nicht für Cents interessieren, da sie keine Rolle spielen, wenn Sie über Dollar sprechen (im nächsten Kapitel untersuchen wir, wie Sie Zahlen im Detail formatieren). Um beispielsweise den Zinssatz zu formatieren, klicken Sie mit der rechten Maustaste auf die Zelle, und klicken Sie auf "Zellen formatieren". Wählen Sie den Prozentsatz aus und verwenden Sie 2 Dezimalstellen.

Formatieren Sie auf ähnliche Weise die anderen Zellen für "Währung" anstelle von Prozent und wählen Sie "Zahl" für die Darlehenslaufzeit.

Jetzt haben wir:

Fügen Sie die SUM () - Funktion zu den monatlichen Gesamtkosten hinzu.

Beachten Sie das Hypothek Zelle ist nicht in der Summe enthalten. Excel weiß nicht, dass Sie diese Nummer angeben möchten, da dort kein Wert vorhanden ist. Achten Sie daher darauf, die SUM () - Funktion nach oben zu erweitern, indem Sie entweder den Cursor verwenden oder E2 eingeben, wo E3 steht, um die Hypothek in die Summe aufzunehmen.

Setzen Sie den Cursor in die Zahlungszelle (B4).

Wählen Sie im Menü Formeln die Dropdown-Liste "Finanziell" aus und wählen Sie dann die PMT-Funktion aus. Der Assistent wird angezeigt:

Verwenden Sie den Cursor, um "Rate", "Nper" (Darlehenslaufzeit), "Pv" ("Barwert" oder Darlehensbetrag) auszuwählen. Beachten Sie, dass Sie den Zinssatz durch 12 dividieren müssen, da die Zinsen monatlich berechnet werden. Sie müssen auch die Kreditlaufzeit in Jahren mit 12 multiplizieren, um die Kreditlaufzeit in Monaten zu erhalten. Klicken Sie auf "OK", um das Ergebnis in der Kalkulationstabelle zu speichern.

Beachten Sie, dass die Zahlung als negative Zahl angezeigt wird: -1013.37062. Um es positiv zu machen und zu den monatlichen Ausgaben hinzuzufügen, zeigen Sie auf die Hypothekenzelle (E2). Geben Sie "= -" ein und zeigen Sie mit dem Cursor auf das Zahlungsfeld. Die resultierende Formel lautet = -B4.

Jetzt sieht die Tabelle so aus:

Ihre monatlichen Ausgaben betragen $ 1.863 - Autsch!

Beispiel: Textfunktion

Hier zeigen wir Ihnen, wie Sie Funktionen innerhalb einer Formel und von Textfunktionen verwenden.

Angenommen, Sie haben eine Liste der Schüler, wie unten gezeigt. Der Vor- und Nachname ist in einem Feld durch ein Komma getrennt. Wir müssen die letzten und festen Namen in getrennte Zellen eintragen. Wie machen wir das?

Um dieses Problem anzugehen, müssen Sie einen Algorithmus verwenden, d. H. Ein schrittweises Verfahren, um dies zu tun.

Schauen Sie sich zum Beispiel "Washington, George" an. Das Verfahren, um dies in zwei Wörter zu unterteilen, wäre:

Berechnen Sie die Länge der Zeichenfolge.
Finden Sie die Position des Kommas (dies zeigt an, wo ein Wort endet und das andere beginnt).
Kopieren Sie die linke Seite der Zeichenfolge bis zum Komma.
Kopieren Sie die rechte Seite der Zeichenfolge vom Komma bis zum Ende.

Lassen Sie uns mit "George Washington" Schritt für Schritt in Excel erläutern.

Berechnen Sie die Länge der Zeichenfolge mit der Funktion = LEN (A3) - das Ergebnis ist 18.
Finden Sie nun die Position des Kommas durch Eingabe dieser Funktion = FIND (",", A3 ") - das Ergebnis ist 11.
Nehmen Sie nun die linke Seite der Zeichenfolge bis zum Komma und erstellen Sie diese verschachtelte Formel mit dem Ergebnis aus Schritt 1: = LINKS (A3, FIND (“,”, A3) -1). Beachten Sie, dass wir 1 von der Länge subtrahieren müssen, da FIND die Position des Kommas angibt.

So sieht das alles aus, wenn alle Funktionen in einer Formel zusammengefasst sind. In Zelle B3 können Sie sehen, dass diese Formel alle Informationen aus Zelle A3 übernimmt und "Washington" eingibt.

Wir haben also "Washington", jetzt brauchen wir "George". Wie machen wir das??

Beachten Sie, dass wir das Ergebnis von Schritt 1 in einer Zelle selbst speichern konnten, beispielsweise B6, und dann eine einfachere Formel schreiben würde: = LINKS (A3, B6-1). Dies erfordert jedoch eine Zelle für den intermittierenden Schritt.

Erinnern Sie sich an die Position des Kommas oder berechnen Sie es erneut.
Berechnen Sie die Länge der Zeichenfolge.
Zähle 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 dies Schritt für Schritt tun.

Von oben ist dies = FIND (",", A3 ")
Die Länge der Zeichenfolge ist = LEN (A3)
Sie müssen einige Berechnungen durchführen, um die Anzahl der zu verwendenden Zeichen zu ermitteln: = LEN (A3) - FIND (",", A3) - 1
Die rechte Seite der gewünschten Zeichenfolge ist = RECHTS (A3, LEN (A3) - FIND (",", A3) - 1).

Ihre Tabelle sollte jetzt wie in der Abbildung unten aussehen. Wir haben die Formeln als Text in den unteren Teil der Tabelle kopiert, um das Lesen und Anzeigen zu erleichtern.

Das war etwas schwierig, aber Sie müssen diese Formeln nur einmal schreiben.

Im Anschluss…

Damit ist unsere Lektion für heute abgeschlossen. Sie sollten jetzt ein ziemlich genaues Verständnis von Formeln und Funktionen, Zeilen und Spalten haben und wie diese durch mehrere konkrete Beispiele verwendet werden können.

Als Nächstes in Lektion 3 werden Zellbezug und -formatierung sowie das Verschieben und Kopieren von Formeln besprochen, damit Sie nicht jede Formel immer wieder neu schreiben müssen!