Beim Erlernen und Verwenden von Excel-Formeln und -Funktionen ist es wichtig, die wichtigsten und am häufigsten verwendeten zu kennen, wie wir bereits im Abschnitt Erstellen einer Funktion in Excel erläutert haben.
Eine der am häufigsten verwendeten Funktionen, bei deren Erstellung wir anfangs auf einige Schwierigkeiten stoßen können, sind bedingte Formeln. Aber wie wir Ihnen weiter unten zeigen werden, können wir eine bedingte Formel in eine einfache Aufgabe umwandeln.
Denken Sie auch daran, dass Sie diese Erklärung noch einmal einsehen können, wenn Sie zu irgendeinem Zeitpunkt Fragen haben oder vergessen haben, wie man diese erledigt . Das Wichtigste ist, dass wir heute lernen, was eine bedingte Formel ist, dass wir wissen, wie man sie mit der Syntax und den Beispielen anwendet, die wir Ihnen beibringen werden; Und dass wir wissen, welche Arten von Bedingungsformeln wir anwenden können, um eine oder mehrere Bedingungen für unsere Formel oder unseren Ausdruck festzulegen.
Wir beginnen wie immer am Anfang und erklären, was eine bedingte Formel ist.
Contents
1 Was ist eine bedingte Formel in Excel?
Eine bedingte Formel in Excel ist ein Ausdruck, der verwendet wird, um eine Bedingung festzulegen und in der Lage zu sein, in einer Zelle oder einer Gruppe von Zellen auszuwerten, ob diese Bedingung erfüllt ist oder nicht .
Das Ergebnis einer bedingten Formel gibt das eine oder andere Ergebnis zurück, abhängig von der Bedingung oder den Bedingungen, die wir im Ausdruck festlegen, abhängig davon, ob die Bedingung oder Bedingungen erfüllt sind oder nicht, und abhängig von den Antworten, die wir festlegen werden.
Bedingungsformeln gehören zu den Funktionen, die als logische Funktionen bezeichnet werden. Sie sind sehr wichtig und werden in einem Analysekontext verwendet, um je nach festgelegter Bedingung unterschiedliche Ergebnisse mit derselben Formel erzielen zu können.
Formeln oder bedingte Ausdrücke in Excel scheinen zunächst komplizierter zu sein: Diese Formeln haben, wie wir gleich erklären werden, eine andere Syntax als andere einfachere Funktionen und sind außerdem tendenziell länger als üblich. Wenn wir jedoch wissen, wie man sie nutzt, wird es uns leicht fallen.
Die Syntax einer einfachen Formel, die eine einfache Funktion in Excel verwendet, wie beispielsweise die Durchschnittsfunktion, besteht, wie wir bereits gesehen haben, aus dem Namen einer Funktion, im Beispiel der Funktion „AVERAGE“, und einem Datenbereich in Klammern. Wie wir im Beispielbild unten sehen, würde dies wie eine Formel aussehen, die eine einfache Funktion wie die Durchschnittsfunktion verwendet.
Allerdings sind nicht alle Formeln so einfach, so kurz, noch haben sie alle die gleiche Struktur oder Form.
Um Operationen oder Berechnungen durchzuführen, müssen Sie oft weitere Parameter oder Informationen in den Ausdruck aufnehmen, um das erwartete Ergebnis zu erhalten. Dies ist bei bedingten Funktionen der Fall, bei denen wir zusätzlich zum Namen einer Funktion und dem Datenbereich zum Ausdruck hinzufügen müssen, welche Anforderung(en) zur Auswertung der Daten verwendet werden sollen. Darüber hinaus müssen wir in den Ausdruck oder die Formel auch die Antwort einbeziehen, die wir erhalten möchten, wenn diese Anforderung erfüllt ist, und die Antwort, die wir erhalten möchten, wenn diese Anforderung nicht erfüllt ist.
Bedingte Funktionen sind in der Regel längere Formeln, kurz gesagt, weil wir in die Formel eine Reihe von Kriterien oder Bedingungen einbeziehen müssen und weil mehrere Ergebnisse oder Varianten angegeben werden können, die wir auch im Ausdruck berücksichtigen müssen.
Unten im Bild können Sie sehen, wie die Syntax einer einfachen bedingten Formel aussieht, wobei wir eine Bedingung in der Formel festlegen und dann die beiden möglichen Szenarien hinzufügen müssen, die auftreten:
- Wenn die von uns festgelegte Bedingung erfüllt ist
- Die festgelegte Bedingung ist nicht erfüllt
Dies wäre die Syntax einer einfachen Bedingungsformel, in der wir eine einzelne Bedingung festgelegt haben.
2 Wie sieht eine Formel oder ein bedingter Ausdruck in Excel aus?
In Excel werden bedingte Formeln häufig mit dem Befehl „IF“ verwendet , wie wir im Bild gesehen haben. Im „IF“-Ausdruck oder in der Formel müssen wir die Bedingung (oder Bedingungen) einschließen und dann angeben, welchen Wert die Formel zurückgeben soll, wenn die Bedingung wahr ist (wenn die Bedingung erfüllt ist) und welchen Wert sie zurückgeben soll, wenn die Bedingung falsch ist ( wenn die Bedingung wahr ist). Bedingung ist nicht erfüllt).
In einer Bedingungsformel können wir mehr als ein Kriterium oder eine Bedingung festlegen. Und wir werden es tun, indem wir die „IF“-Funktion mit anderen Funktionen wie „AND“ oder der „OR“-Funktion verwenden.
Anhand eines einfachen Beispiels wollen wir mithilfe einer Bedingungsformel anhand der Prüfungsnote ermitteln, ob mehrere Studierende die Lehrveranstaltung bestanden haben oder nicht. Im Bild unten sehen wir die Liste der Studenten mit der Note, die jeder einzelne in der Prüfung erhalten hat.
Wir können, wie wir weiter unten in den Beispielen sehen werden, eine Formel mit dieser Logik erstellen:
Wenn ein Schüler in der Prüfung die Note 5 oder mehr als 5 erreicht hat, hat er oder sie bestanden. Liegt die Prüfungsnote dagegen unter 5, wäre das Ergebnis ein Nichtbestehen.
Darüber hinaus verwenden wir in der Formel, wie Sie im Bild gesehen haben, das Dollarzeichen $, um den Cutoff-Score festzulegen, und dass das Ziehen der Formel nach unten diese Zelle in der Formel nicht verschiebt.
Wenn wir diese Formel anwenden, ist das Ergebnis, wie wir im Bild unten sehen können, das, was wir erwartet haben, und wir haben als Ergebnis der Formel „JA“ UND „NEIN“ erhalten. „JA“ wären diejenigen, die den Kurs bestanden haben, „NEIN“ wären die Studenten, die die Bedingung nicht erfüllt haben, was, wenn wir uns erinnern, darin bestand, in der jährlichen Kursnote eine 5 oder mehr zu erreichen.
Im Beispiel haben wir gesehen, wie man eine bedingte Formel mit einer Bedingung erstellt. Allerdings können wir in einer Bedingungsformel so viele Bedingungen und Anforderungen wie nötig festlegen, indem wir die oben genannten „AND“- oder „OR“-Funktionen verwenden, jede mit einem anderen Zweck.
Wir werden sehen, wie die „AND“-Funktion und die „OR“-Funktion funktionieren, und wir werden Beispiele sehen, um zu lernen, wann wir die einzelnen Funktionen verwenden sollten.
- „AND“-Funktion: Mit der „AND“-Funktion können wir eine Reihe von Kriterien oder Bedingungen festlegen, die alle erfüllt sein müssen. Was die „UND“-Funktion innerhalb einer bedingten Formel am besten charakterisiert, ist, dass wir mehrere Kriterien festlegen, die erfüllt werden sollen. Im Beispiel würden wir die „AND“-Funktion verwenden, um in der Formel widerzuspiegeln, dass ein Student, um zu bestehen, beide Teile des Kurses bestanden haben muss.
- Mit der Funktion „ODER“ sagen wir, dass eines der Kriterien erfüllt sein soll.
3 So erstellen Sie eine bedingte Formel mit zwei Bedingungen in Excel („IF“-Funktion mit 2 oder mehr Bedingungen)
Wie wir Ihnen bereits gesagt haben, möchten wir in einer bedingten Formel möglicherweise weitere Kriterien festlegen. Somit können wir zwei oder mehr Kriterien in der Bedingungsformel festlegen, indem wir die „AND“-Funktion oder die „OR“-Funktion verwenden , je nachdem, welche Kriterien für die Bedingung festgelegt wurden:
- Wollen wir, dass mehrere Bedingungen erfüllt sind, ja oder ja? Wir werden die „UND“-Funktion verwenden
- Wollen wir, dass nur eine der von uns festgelegten Bedingungen erfüllt ist? Wir verwenden dann die „ODER“-Funktion.
Lassen Sie uns anhand eines Beispiels die Verwendung mehrerer Bedingungen in derselben Formel besser sehen.
Fahren wir mit dem gleichen Beispiel fort, bei dem ein Lehrer die Jahresergebnisse seiner Schüler auswertet, um festzustellen, ob sie den Kurs bestanden haben oder nicht. Die Ergebnisse der Studierenden spiegeln sich in zwei Prüfungen wider, die sie während des Kurses ablegen mussten.
In diesem Fall muss ein Student, damit er besteht, nun die 2-semestrigen Prüfungen des Kurses bestanden haben, und deshalb müssen wir in der Formel alle diese Informationen sammeln.
- Wenn er/sie in den beiden Prüfungen eine 5 oder mehr als 5 erreicht hat, hat der/die Studierende bestanden.
- Wenn ein Student mindestens eine der Prüfungen nicht bestanden hat, also in einigen Prüfungen weniger als 5 Punkte erzielt hat, hat dieser Student die Lehrveranstaltung nicht bestanden.
Wie würde eine bedingte Formel mit zwei oder mehr Kriterien oder Bedingungen aussehen? Zu der Syntax, die wir zuvor erstellt haben, müssen wir eine neue Funktion hinzufügen, die mit der bedingten „IF“-Funktion kombiniert wird, über die wir bereits gesprochen und erklärt haben: die „AND“-Funktion.
Die „AND“-Funktion reagiert auf die Sprachlogik. In unserer Formel wollen wir die Bedingung 1, die besagt, dass der Student das erste Semester bestanden hat, UND die Bedingung 2, die besagt, dass der Student auch das zweite Semester bestanden hat, setzen. Das heißt, Sie müssen beide Prüfungen mit „Ja“ oder „Ja“ bestanden haben, um bestanden zu werden. (Und wenn diese Bedingung, beide Prüfungen bestanden zu haben, nicht erfüllt ist, dann wäre es ein Nichtbestehen).
Somit hätte die von uns erstellte Formel die folgende Form, die Sie im Bild sehen können, mit der Erklärung jedes Teils der Formel.
Wenn wir die Formel auf alle Schüler anwenden, erhalten wir dieses Ergebnis. Im Gegensatz zu dem Beispiel, das wir zuvor gesehen haben, erscheinen nun nur noch Studierende mit dem „JA“ für das Bestehen, die beide Prüfungen bestanden haben.
Mit dieser Methode können Sie mithilfe der „AND“-Funktion so viele Kriterien zur Formel hinzufügen, wie Sie möchten, und dabei der gleichen Struktur der Formel folgen, die wir Ihnen gerade im Beispiel vorgestellt haben.
Jetzt wissen wir, wie man eine bedingte Formel mit mehreren Bedingungen erstellt. In den Beispielen, die wir gesehen haben, haben wir Ausdrücke mit einer oder mehreren Bedingungen konstruiert und als Antwort zwei mögliche Szenarios erstellt. Sind die Voraussetzungen erfüllt, im Beispiel also, dass beide Prüfungen bestanden wurden, erhalten wir die erste unserer Antworten: JA, es ist genehmigt.
Bei Nichtbestehen wird in der Formel mit „Nein“ geantwortet, wenn ein Studierender eine der beiden Prüfungen oder beide Prüfungen nicht bestanden hat. Das bedeutet, dass wir zwei mögliche Szenarien haben.
- Die Bedingung ist erfüllt.
- Es ist nicht erfüllt.
Was bedingte Formeln betrifft, gibt es auch die Möglichkeit, mehr als zwei Antworten oder Szenarien festzulegen, wie wir im Folgenden erläutern werden, und Sie werden am Beispiel sehen, wie einfach das ist.
4 So verwenden Sie bedingte Formeln in Excel mit mehreren Antworten
Wenn wir mit dem Erlernen von Bedingungsformeln fortfahren, werden wir im Folgenden eine Möglichkeit sehen, Ausdrücke zu erstellen, die mehr als zwei Antworten enthalten, indem wir die Ressourcen verwenden, die wir gelernt haben:
- Verwenden der „IF“-Funktion zum Erstellen der bedingten Formel
- Verwendung der Funktionen „AND“ und „OR“ innerhalb der Bedingungsformel.
Diese Formel wird länger sein als die Formeln, die wir uns angesehen haben, da sie, wie wir bereits wissen, die Bedingung(en) enthalten muss, die wir verwenden möchten, sowie die möglichen Szenarien, die je nach dem von uns erhofften Ergebnis eintreten können erhalten.
Sehen wir es uns wie immer anhand eines Beispiels an. Wir werden sehen, dass wir eine lange Formel erstellen werden, dass wir sie aber dennoch gut und einfach erstellen können, wenn wir wissen, wie es geht.
Wir fahren also mit dem gleichen Beispiel fort, wollen nun aber eine andere Formel erstellen, denn wir wollen 3 Bewertungskategorien (3 mögliche Antworten) entsprechend den Noten der Benutzer in den beiden Prüfungen erstellen.
Im Beispiel erstellen wir daher eine Formel, die 3 mögliche Antworten enthält, da wir eine neue Bewertungskategorie erstellen möchten, um Studierende einzubeziehen, die 1 der 2 Prüfungen bestanden haben.
Die drei Arten der Bewertung, die wir anwenden möchten, sind also die folgenden:
- Wer beide Prüfungen bestanden hat, gilt als „BESTANDEN“. Dabei handelt es sich um Studierende, die in jeder Prüfung die Note 5 oder mehr erreicht haben.
- Diejenigen, die beide Prüfungen nicht bestanden haben: sind „FAIL“-Schüler (Studenten, die in jeder Prüfung eine Note von weniger als 5 erhalten haben).
- Die neue Kategorie von Benutzern wird diejenigen sein, die nur eine der beiden Prüfungen bestanden haben. Dabei handelt es sich um Studierende, die wir als „RECOVERY“ kategorisieren werden (das sind Studierende, die in einer der beiden abgelegten Prüfungen eine 5 oder mehr in einer der Prüfungen und weniger als 5 in der anderen Prüfung erreicht haben) .
Bevor wir die Formel erstellen, sehen wir uns an, wie die Syntax des Ausdrucks aussehen würde, den wir entsprechend dem Beispiel, an dem wir arbeiten, erstellen möchten.
Unter Berücksichtigung der 3 Bewertungskategorien muss die Syntax der Formel unter Berücksichtigung der Tatsache konstruiert werden, dass es 3 verschiedene Arten von Studierenden gibt:
- Für das erste Szenario verwenden wir die „Ja“-Funktion und müssen, wie wir bereits gesehen haben, die „UND“-Funktion verwenden (Um in Szenario 1 ausdrücken zu können, dass Sie, um einen Pass zu haben, … muss Prüfung 1 und Prüfung 2 bestanden haben).
- Für Szenario 2 verwenden wir erneut die „IF“-Funktion und in diesem Fall die „OR“-Funktion. Denn dies ist bei den Studenten der Fall, die wir „RECOVERY“ nennen und die entweder eine Prüfung oder die andere bestanden haben. Wenn Szenario 1 und 2 nicht erfüllt sind, wird dieser Student als „FAIL“-Student eingestuft.
Als nächstes haben wir den Ausdruck für das Beispiel erstellt, der wie im Bild unten aussehen würde. Es ist ein Ausdruck, der hilft, ihn besser zu verstehen, wenn wir ihn, wie wir an der Syntax gesehen haben, nach und nach aufbauen und dabei die gleiche Logik verwenden.
Wenn wir die Formel anwenden und auf die restlichen Zellen ziehen, um die Note aller Schüler zu erhalten, können wir sehen, wie gut die Formel funktioniert, und wir erhalten die drei erwähnten Bewertungsarten: PASS, FAIL, RECOVERY.
Auf diese Weise verstehen wir besser, warum eine bedingte Formel zunächst komplizierter sein kann, wir sie aber dennoch einfacher machen können, wenn wir über die entsprechende Schulung verfügen. Das ist das heutige Ziel, etwas zunächst Schwieriges, wie eine bedingte Formel, in eine einfache Aufgabe zu verwandeln.
Wir müssen bedenken, dass, wie wir gesehen haben, mehrere Fälle in einer Bedingungsformel auftreten können, und wir haben sie alle erklärt:
- Eine einfache bedingte Formel
- Eine bedingte Formel mit zwei oder mehr Kriterien
- Eine bedingte Formel mit mehr als zwei Kriterien und auch mit mehreren Antworten
Wenn wir den Umgang mit einem praktischen Werkzeug wie Excel erlernen, ist es am besten, das erworbene Wissen anzuwenden und zu testen. Auf diese Weise können Sie das Gelernte in die Praxis umsetzen und sich gleichzeitig die praktische Anwendung von Excel in seinen vielfältigen Funktionen und Einsatzmöglichkeiten merken.
Bedingte Formeln helfen Ihnen bei der Auswertung von Daten und erhöhen Ihre Ressourcen, wenn Sie Formeln und Funktionen in Excel für die Datenanalyse verwenden müssen.
