Formel definieren und erstellen

Inhaltsverzeichnis:

Video: Formel definieren und erstellen

Video: Formel definieren und erstellen
Video: iPhone Ausschalten & Neustart Tutorial #ElekTricks - Robin.tv 2024, März
Formel definieren und erstellen
Formel definieren und erstellen
Anonim
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:
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

  1. Warum benötigen Sie Formeln und Funktionen?
  2. Formel definieren und erstellen
  3. Relative und absolute Zellreferenz und Formatierung
  4. Nützliche Funktionen, die Sie kennenlernen sollten
  5. 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 - die Säulen gehen auf und ab und die Reihen gehen von links nach rechts.

Spalten sind mit Buchstaben gekennzeichnet. Reihen nach Zahlen. Die erste Zelle in der Tabelle 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.
Spalten sind mit Buchstaben gekennzeichnet. Reihen nach Zahlen. Die erste Zelle in der Tabelle 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.
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 Funktion SUM ().
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 Funktion SUM ().
Als erste Funktion verwenden wir SUM (), um eine Liste mit Zahlen hinzuzufügen.
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 Funktion SUM () zu verwenden.
Um die Gesamtkosten zu berechnen, können Sie = b2 + b3 + b4 + b5 schreiben, aber es ist einfacher, die Funktion SUM () zu verwenden.

Suchen Sie in Excel nach dem Symbol Σ in der oberen linken Ecke des Excel-Bildschirms, um die Schaltfläche AutoSumme 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.
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.
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!
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:
#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 einfachste Möglichkeit, die Argumente in einer Berechnung auszuwählen, ist die Verwendung der Maus. 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.
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. 24 = 2 ^ 4 = 2 * 2 * 2 * 2 =16

Es gibt andere Operatoren, die nicht mit der Mathematik zusammenhängen, wie "&", dh zwei aufeinanderfolgende Verknüpfungen (Join End-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 oder "a" <"b"
>= größer als oder gleich - ein anderer Weg, dies zu denken, ist> = bedeutet entweder > oder =.
<= weniger als oder gleich.
nicht gleich beispielsweise 4 <> 6

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 πr2, das ist das gleiche wie π * r * r. Es ist nicht (πr)2.

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

Im Allgemeinen können Sie das sagen:

  1. Excel wertet zuerst Elemente in Klammern aus, die von innen nach außen arbeiten.
  2. Es verwendet dann die Ordnungsvorrangregeln der Mathematik.
  3. 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, z. B. 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. Betrachten wir nun einige Beispiele.

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

Angenommen, Ihr Chef entscheidet, dass Sie gute Arbeit leisten, und er oder sie wird Ihnen eine Erhöhung von 10% gewähren. 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. Was ist 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 noch einmal deutlich machen, indem man = (4 * 3) / 2 schreibt

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 = -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. Das kann man so sehen (-1 * -1) * -1 = 1 * -1 = -1.

Es gibt also einige 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 für einige von Ihnen ein Leben lang zu bestehen).

Beispiel: Funktion Darlehenszahlung (PMT)

Schauen wir uns ein Beispiel zur Berechnung einer Darlehenszahlung an.

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 für das Thema Dollar keine große Rolle spielen (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:
Jetzt haben wir:
Image
Image

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

Image
Image

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 angegeben 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" und anschließend die Funktion PMT. Der Assistent wird angezeigt:
Wählen Sie im Menü Formeln die Dropdown-Liste "Finanziell" und anschließend die Funktion PMT. 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. Außerdem müssen Sie die Kreditlaufzeit in Jahren mit 12 multiplizieren, um die Kreditlaufzeit in Monaten zu erhalten. Drücken Sie “OK”, um das Ergebnis in der Kalkulationstabelle zu speichern.
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. Außerdem müssen Sie die Kreditlaufzeit in Jahren mit 12 multiplizieren, um die Kreditlaufzeit in Monaten zu erhalten. Drücken Sie “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:
Jetzt sieht die Tabelle so aus:
Ihre monatlichen Ausgaben betragen $ 1.863 - Autsch!
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.
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 aufzuteilen, wäre:

  1. Berechnen Sie die Länge der Zeichenfolge.
  2. Suchen Sie die Position des Kommas (dies zeigt an, wo ein Wort endet und das andere beginnt).
  3. Kopieren Sie die linke Seite der Zeichenfolge bis zum Komma.
  4. 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.

  1. Berechnen Sie die Länge der Zeichenfolge mit der Funktion = LEN (A3) - das Ergebnis ist 18.
  2. Finden Sie nun die Position des Kommas durch Eingabe dieser Funktion = FIND (",", A3 ") - das Ergebnis ist 11.
  3. 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, wir müssen 1 von der Länge subtrahieren, 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?
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). Das verbraucht jedoch eine Zelle für den intermittierenden Schritt.

  1. Merken Sie sich die Position des Kommas oder berechnen Sie es erneut.
  2. Berechnen Sie die Länge der Zeichenfolge.
  3. Zähle die Zeichen vom Ende der Zeichenfolge bis zum Komma.

Nimm die Anzahl der Zeichen aus Schritt 3 und subtrahiere eins, um das Komma und das Leerzeichen wegzulassen.

Lassen Sie uns dies Schritt für Schritt tun.

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

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

Das war etwas schwierig, aber Sie müssen diese Formeln nur einmal schreiben.
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 für 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 wir die Zellreferenz und -formatierung sowie das Verschieben und Kopieren von Formeln besprechen, damit Sie nicht jede Formel immer wieder neu schreiben müssen!

Empfohlen: