Formel definieren und erstellen

In dieser Lektion stellen wir Ihnen Grundregeln zum Erstellen von Formeln und Verwenden von Funktionen vor. Wir glauben, dass eine der besten Möglichkeiten zum Lernen in der Praxis ist, deshalb stellen wir einige Beispiele bereit und erklären sie im Detail. Zu den Themen, die wir behandeln werden, gehören:

1. Warum brauchen Sie Formeln und Funktionen?
2. Formel definieren und erstellen
3. Relativer und absoluter Zellbezug und Formatierung
4. Nützliche Funktionen, die Sie kennenlernen sollten
5. Lookups, Diagramme, Statistiken und Pivot-Tabellen

• Reihen und Spalten
• Beispiel für eine mathematische Funktion: SUM()
• Betreiber
• Operator-Priorität
• Beispiel Finanzfunktion: PMT(), Kreditzahlung
• Verwenden einer Stringfunktion (String ist eine Abkürzung für Textstring) innerhalb einer Formel und Verschachtelungsfunktionen

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.

Reihen und Spalten

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

Zeilen verlaufen horizontal und Spalten vertikal. Um sich daran zu erinnern, was was ist, stellen Sie sich eine Säule vor, die ein Dach hält – Säulen gehen von oben nach unten und somit gehen Reihen von links nach rechts.

Spalten sind mit Buchstaben gekennzeichnet; Reihen nach 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 aufgebraucht ist, stellt Excel einen weiteren Buchstaben voran: AA, AB, AC… AZ, BA, BC, BC usw.

Beispiel: Funktion Sum()

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

Sie verwenden Funktionen, indem Sie sie direkt eingeben oder den Funktionsassistenten verwenden. Der Funktionsassistent wird geöffnet, 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 es Ihnen, eine Funktion auszuwählen.

Der Assistent sagt Ihnen, welche Argumente Sie für jede Funktion bereitstellen müssen. Es enthält auch einen Link zu Online-Anweisungen, wenn Sie Hilfe beim Verständnis der Funktion und der Verwendung dieser Funktion benötigen. Wenn Sie beispielsweise =sum in eine Zelle eingeben, zeigt Ihnen der Inline-Assistent, welche Argumente für die SUM-Funktion 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 SUM().

Für unsere erste Funktion verwenden wir SUM(), die eine Liste von Zahlen hinzufügt.

Angenommen, wir haben diese Tabelle mit Plänen für die Budgetierung des Urlaubs Ihrer Familie:

Um die Gesamtkosten zu berechnen, könnten 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 AutoSumme zu finden (Mathematiker verwenden den griechischen Buchstaben Σ zum Addieren einer Zahlenfolge).

Wenn sich der Cursor unter den Familienbudgetzahlen befindet, ist Excel intelligent genug, um zu wissen, dass Sie die Liste der Zahlen über der Position des Cursors summieren möchten, damit 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 eingegeben 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 unterhalb von Reihe 5 etwas Platz gelassen haben, damit Sie das Familienurlaubsbudget aufstocken können – die Kosten werden sicherlich steigen, wenn die Kinderliste länger wird, was sie tun und wohin sie wollen!

Mathefunktionen funktionieren nicht mit Buchstaben. Wenn Sie also Buchstaben in die Spalte eingeben, wird das Ergebnis als #NAME? Wie nachfolgend dargestellt.

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

• fehlerhafte Zellreferenz
• Buchstaben in mathematischen Funktionen verwenden
• Weglassen erforderlicher Argumente
• Funktionsname falsch buchstabiert
• 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 die Liste der Argumente zur Funktion hinzufügen oder daraus 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 oben auf das von Excel gezeichnete Quadrat geklickt, um Flugtickets aus dem Budget zu nehmen. Sie sehen das Fadenkreuz-Symbol, 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.

Es gibt andere Operatoren, die nichts mit Mathematik zu tun haben, wie &, was bedeutet, zwei Strings zu verketten (Ende-zu-Ende zu verbinden). Beispiel: =Excel & macht Spaß gleich Excel macht Spaß.

Jetzt schauen wir uns Vergleichsoperatoren an.

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

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

Rangfolge der Bedienerbefehle

Ordnungsvorrang ist eine Idee aus der Mathematik. Excel muss denselben Regeln folgen wie die Mathematik. Dieses Thema ist komplizierter, also atme durch und lass uns eintauchen.

Die Reihenfolge der Rangfolge bedeutet die Reihenfolge, in der der Computer die Antwort berechnet. Wie wir in Lektion 1 erklärt haben, ist die Fläche eines Kreises πr^zwei, was gleich ist mit π * r * r. es ist nicht (πr)^zwei.

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

Allgemein kann man das sagen:

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

Die Rangfolge mathematischer Operatoren wird unten in absteigender Reihenfolge angezeigt.

Es gibt andere Vorrangregeln in Bezug auf Zeichenfolgen und Referenzoperatoren. Im Moment bleiben wir einfach bei dem, was wir gerade besprochen haben. Schauen wir uns nun einige Beispiele an.

Beispiel: Berechnung der Fläche eines Kreises

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

Wenn wir uns die obige Tabelle ansehen, sehen wir, 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 großartigen Job machen, und er oder sie wird Ihnen eine Gehaltserhöhung von 10 % geben! Wie würden Sie Ihr neues Gehalt berechnen?

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

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

Angenommen, Ihr Gehalt beträgt 100 US-Dollar. Bei einer Erhöhung um 10 % beträgt Ihr neues Gehalt:

= 100 + 100 * 10% = 100 + 10 = 110

Du kannst es auch so schreiben:

= 100 + (100 * 10%) = 100 + 10 = 110

Im zweiten Fall müssen wir die Rangfolge durch die Verwendung von Klammern explizit machen. Denken Sie daran, dass Klammern vor allen anderen Operationen ausgewertet werden.

Die einfachere Schreibweise ist übrigens = Gehalt * 110%

Klammern können ineinander verschachtelt werden. Wenn wir also schreiben (3 + (4 * 2)) von innen nach außen arbeiten, berechnen wir zuerst 4 * 2 = 8, dann addieren Sie 3 + 8, um 11 zu erhalten.

Noch ein paar Beispiele

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

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

Sie könnten dies wiederum explizit machen, indem Sie = (4 * 3) / 2 . schreiben

Was ist mit = 4 + 3 * 2?

Der Computer sieht die Operatoren * und +. Nach den Vorrangregeln (Multiplikation kommt vor Addition) berechnet es 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, weil der Computer berechnet hat = (-1) ^ 3 = -1 * -1 * -1 = -1.

Denken Sie daran, dass negatives mal negativ positiv ist und negatives mal positiv negativ ist. Sie können dies so sehen (-1 * -1) * -1 = 1 * -1 = -1.

Es gibt also ein paar Beispiele für mathematische Reihenfolge und Rangfolge. Wir hoffen, dass dies hilft, einige Dinge darüber zu klären, wie Excel Berechnungen durchführt (und das ist wahrscheinlich genug Mathematik, um für einige von Ihnen ein Leben lang zu halten).

Beispiel: Funktion Darlehenszahlung (PMT)

Schauen wir uns ein Beispiel zur Berechnung einer Kreditzahlung an.

Beginnen Sie damit, ein neues Arbeitsblatt zu erstellen.

Formatieren Sie die Zahlen mit Dollarzeichen und verwenden Sie keine Dezimalstellen, da wir uns im Moment nicht für Cent interessieren, da sie keine große Rolle spielen, wenn Sie über Dollar sprechen (im nächsten Kapitel untersuchen wir, wie Zahlen im Detail formatiert werden). 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 in ähnlicher Weise die anderen Zellen für die Währung anstelle des Prozentsatzes und wählen Sie die Zahl für die Kreditlaufzeit aus.

Jetzt haben wir:

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

Beachten Sie das Hypothek Zelle ist nicht in der Summe enthalten. Excel weiß nicht, dass Sie diese Zahl einschließen möchten, da dort kein Wert vorhanden ist. Achten Sie also darauf, die Funktion SUM() nach oben zu erweitern, 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).

Wählen Sie im Menü Formeln das Dropdown-Menü Finanzen und dann die PMT-Funktion aus. Der Assistent erscheint:

Wählen Sie mit dem Cursor den Zinssatz.,npro (Kreditlaufzeit), Pv (Barwert oder Kreditbetrag). Beachten Sie, dass Sie den Zinssatz durch 12 teilen 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 Tabelle 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 verwenden Sie dann den Cursor, um auf das Zahlungsfeld zu zeigen. Die resultierende Formel ist =-B4.

Nun sieht die Tabelle so aus:

Ihre monatlichen Ausgaben betragen 1.863 US-Dollar – Autsch!

Beispiel: Textfunktion

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

Angenommen, Sie haben eine Liste mit Schülern wie unten gezeigt. Der Vor- und Nachname stehen in einem Feld, getrennt durch ein Komma. Wir müssen den Nach- und Firmennamen in separate Zellen eintragen. Wie machen wir das?

Um dieses Problem anzugehen, müssen Sie einen Algorithmus verwenden – d. h. eine schrittweise Vorgehensweise, um dies zu tun.

Sehen Sie sich zum Beispiel Washington an, George. Das Verfahren, um dies in zwei Wörter aufzuteilen, wäre:

1. Berechne die Länge des Strings.
2. Finden 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 besprechen, wie das geht.

1. Berechnen Sie die Länge des Strings mit der Funktion =LEN(A3) – das Ergebnis ist 18.
2. Ermitteln Sie nun die Position des Kommas durch Eingabe dieser Funktion =FIND(,,A3) – das Ergebnis ist 11.
3. Nehmen Sie nun die linke Seite des Strings 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 abziehen 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 sehen Sie, dass diese Formel alle Informationen aus Zelle A3 übernimmt und Washington eingibt.

Wir haben also Washington, jetzt müssen wir George holen. Wie machen wir das?

Beachten Sie, dass wir das Ergebnis aus Schritt 1 in einer eigenen Zelle, beispielsweise B6, hätten speichern können, und dann eine einfachere Formel schreiben = LINKS(A3, B6-1). Aber das verbraucht eine Zelle für den intermittierenden Schritt.

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

Nehmen Sie die Anzahl der Zeichen aus Schritt 3 und ziehen Sie eins ab, um Komma und 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 etwas Mathematik anwenden, um die Anzahl der zu verwendenden Zeichen zu ermitteln: =LEN(A3) – FIND(,,A3) – 1
4. Die rechte Seite des gesuchten Strings ist =RIGHT(A3,LEN(A3) – FIND(,,A3) – 1)

Ihre Tabelle sollte jetzt ähnlich wie im Screenshot unten aussehen. Wir haben die Formeln als Text in den unteren Teil der Tabelle kopiert, um sie besser lesbar und sichtbar zu machen.

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

Als nächstes kommt …

Damit ist unsere Lektion für heute abgeschlossen. Sie sollten jetzt ein ziemlich sicheres Verständnis von Formeln und Funktionen, Zeilen und Spalten haben und wie dies alles anhand mehrerer eindeutiger Beispiele angewendet werden kann.

Als nächstes in Lektion 3 werden wir den Zellbezug und die Formatierung sowie das Verschieben und Kopieren von Formeln besprechen, damit Sie nicht jede Formel immer wieder neu schreiben müssen!

• › 5 Websites, die jeder Linux-Benutzer mit einem Lesezeichen versehen sollte
• › So finden Sie Ihr Spotify Wrapped 2021
• & rsaquo; Cyber ​​Monday 2021: Die besten Tech-Deals
• & rsaquo; Cyber ​​Monday 2021: Die besten Apple-Angebote
• › Funktionen vs. Formeln in Microsoft Excel: Was ist der Unterschied?
• › Der Computerordner ist 40: Wie Xerox Star den Desktop erstellte