Top 10 der wichtigsten excel-funktionen
1. IF-Funktion
Die IF-Funktion gibt einen Wert zurück, wenn die von Ihnen angegebene Bedingung TRUE ist, andernfalls einen anderen Wert. Einfach ausgedrückt kann die IF-Funktion zunächst eine Bedingung testen und einen Wert basierend auf dem Ergebnis dieser Bedingung zurückgeben.
Syntax
IF(logical_test, value_if_true, value_if_false)
Argumente
- logical_test: Die Bedingung, die Sie auswerten möchten.
- value_if_true: Der Wert, den Sie erhalten möchten, wenn diese Bedingung TRUE ist.
- value_if_false: Der Wert, den Sie erhalten möchten, wenn diese Bedingung FALSE ist.
Kommentare
- Sie können maximal 64 verschachtelte Bedingungen ausführen.
- Sie können Vergleichsoperatoren verwenden, um eine Bedingung auszuwerten.
Beispiel
Im folgenden Beispiel haben wir einen Vergleichsoperator verwendet, um verschiedene Bedingungen auszuwerten.
- Wir haben einen bestimmten Text verwendet, um das Ergebnis zu erhalten, ob die Bedingung erfüllt ist oder nicht.
- Sie können auch TRUE und FALSE verwenden, um das Ergebnis zu erhalten.
- Wenn Sie die Angabe eines Werts überspringen, um das Ergebnis zu erhalten, wenn die Bedingung TRUE ist, wird Null zurückgegeben.
- Und wenn Sie die Angabe eines Werts überspringen, um das Ergebnis zu erhalten, wenn die Bedingung FALSE ist, wird Null zurückgegeben.
Im folgenden Beispiel haben wir die IF-Funktion verwendet, um eine Verschachtelungsformel zu erstellen.
Wir haben eine Bedingung angegeben und wenn diese Bedingung falsch ist, haben wir eine andere IF verwendet, um eine andere Bedingung auszuwerten und eine Aufgabe auszuführen, und wenn diese Bedingung FALSE ist, haben wir eine andere IF verwendet.
Auf diese Weise haben wir IF fünfmal verwendet, um eine Verschachtelungsformel zu erstellen. Sie können dasselbe 64 Mal für eine Verschachtelungsformel verwenden.
2. IFERROR-Funktion
Die IFERROR-Funktion gibt einen bestimmten Wert zurück, wenn ein Fehler auftritt. In einfachen Worten kann es den Wert testen und wenn dieser Wert ein Fehler ist, wird der von Ihnen angegebene Wert zurückgegeben.
Syntax
IFERROR(value, value_if_error)
Argumente
- Wert: Der Wert, den Sie auf Fehler testen möchten.
- value_if_error: Der Wert, den Sie zurückerhalten möchten, wenn ein Fehler auftritt.
Kommentare
- Die IFERROR-Funktion betrifft das Auftreten eines Fehlers, nicht die Art des Fehlers.
- Wenn Sie den Wert oder value_if_error ignorieren, wird im Ergebnis 0 zurückgegeben.
- Es kann #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME testen? und #NULL!.
- Wenn Sie ein Array auswerten, wird für jedes angegebene Element ein Array mit Ergebnissen zurückgegeben.
Beispiel
Im folgenden Beispiel haben wir die IFERROR-Funktion verwendet, um das #DIV/0! zu ersetzen. mit aussagekräftigem Text.
IFERROR ist nur mit den Versionen 2007 und früher kompatibel. Um dieses Problem zu lösen, können Sie ISERROR verwenden.
3.TRUNC-Funktion
Die TRUNC-Funktion gibt eine Ganzzahl zurück, nachdem die ursprüngliche Zahl abgeschnitten wurde. Vereinfacht ausgedrückt entfernt es Dezimalstellen mit einer bestimmten Genauigkeit aus einer Zahl und gibt dann den ganzzahligen Teil des Ergebnisses zurück.
Syntax
TRUNC(Zahl, [number_digits])
Argumente
- Zahl: Die Zahl, die Sie kürzen möchten.
- [num_digits]: Eine Zahl zur Angabe der Genauigkeit zum Abschneiden einer Zahl.
Kommentare
- Wenn Sie die Angabe mehrerer ignorieren, wird ein Fehler zurückgegeben.
- Es wird von Null aus gerundet.
- Wenn Sie zwei Vielfache im gleichen Abstand haben, wird das höhere Vielfache der Zahl zurückgegeben, die Sie runden.
Beispiel
Im folgenden Beispiel haben wir TRUNC verwendet, um die Daten zu kürzen und die Uhrzeit aus den Datumsangaben zu entfernen.
4. SUMIF-Funktion
Die SUMIF-Funktion gibt die Summe der Zahlen zurück, die die von Ihnen angegebene Bedingung erfüllen . Vereinfacht ausgedrückt wird nur die Summe der Werte berücksichtigt und berechnet, die die Bedingung erfüllen.
Syntax
SUMIF(Bereich, Kriterien, [sum_range])
Argumente
- Bereich: Ein Bereich von Zellen, deren Kriterien Sie überprüfen möchten.
- Kriterium: ein Kriterium, das eine Zahl, ein Text, ein Ausdruck, ein Zellbezug oder eine Funktion sein kann.
- [sum_range]: Ein Zellbereich mit den Werten, die Sie summieren möchten.
Kommentare
- Wenn sum_range weggelassen wird, werden die Zellen im Bereich summiert.
- Stellen Sie sicher, dass Sie doppelte Anführungszeichen verwenden, um Textkriterien anzugeben oder mathematische Symbole einzuschließen, die in doppelte Anführungszeichen gesetzt werden sollten.
- Die Größe des Kriterienbereichs und des Summenbereichs müssen gleich groß sein.
Beispiel
Im folgenden Beispiel haben wir A1:A9 als Kriterienbereich und B1:B9 als Summenbereich angegeben und anschließend das Kriterium in A12 angegeben, das den Wert C hat.
Sie können Kriterien auch direkt in die Funktion einfügen. Im folgenden Beispiel haben wir einen Sternchen-Platzhalter verwendet, um ein Kriterium anzugeben, das das Alphabet „S“ enthält.
Und wenn Sie den Summenbereich ignorieren, erhalten Sie die Summe des Kriterienbereichs. Dies ist jedoch nur möglich, wenn der Kriterienbereich numerische Werte hat.
5. INDEX-Funktion
Die INDEX-Funktion gibt einen Wert aus einer Werteliste basierend auf seiner Indexnummer zurück. Vereinfacht ausgedrückt gibt INDEX einen Wert aus einer Werteliste zurück und Sie müssen die Position dieses Werts angeben.
Syntax
INDEX hat zwei verschiedene Syntaxen. Im ersten Fall können Sie eine Array-Form eines Index verwenden, um einfach anhand seiner Position einen Wert aus einer Liste abzurufen.
INDEX(array, row_num, [column_num])
Im zweiten Fall können Sie ein Sponsoring-Formular verwenden, das im wirklichen Leben weniger verwendet wird. Sie können es jedoch verwenden, wenn Sie mehr als einen Bereich bewerben möchten.
INDEX(Referenz, Zeilennummer, [Spaltennummer], [Bereichsnummer])
Argumente
- Array: Ein Zellbereich oder eine Array-Konstante.
- Referenz: ein Zellbereich oder mehrere Bereiche.
- Zeilennummer: Zeilennummer, aus der Sie den Wert erhalten möchten.
- [col_number]: Die Nummer der Spalte, aus der Sie den Wert erhalten möchten.
- [Bereichsnummer]: Wenn Sie sich auf mehrere Zellbereiche beziehen (unter Verwendung der Referenzsyntax), geben Sie eine Zahl an, um auf einen Bereich unter allen zu verweisen.
Kommentare
- Wenn die Argumente „row_num“ und „column_num“ angegeben werden, wird der Wert in der Zelle am Schnittpunkt der beiden zurückgegeben.
- Wenn Sie row_num oder columns_num als 0 (Null) angeben, wird das Wertearray für die gesamte Spalte bzw. Zeile zurückgegeben.
- Wenn row_num und columns_num außerhalb des Bereichs liegen, wird #REF zurückgegeben! Fehler.
- Wenn Bereichsnummer größer als die von Ihnen angegebenen Nummernbereiche ist, wird #REF! zurückgegeben.
Beispiel 1 – ARRAY verwenden, um einen Wert aus einer Liste abzurufen
Im folgenden Beispiel haben wir die INDEX-Funktion verwendet, um die Menge für den Monat Juni zu ermitteln. In der Liste steht Jun an der 6. Position (6. Zeile), deshalb habe ich in row_number 6 angegeben. INDEX hat im Ergebnis den Wert 1904 zurückgegeben.
Und wenn Sie sich auf einen Bereich mit mehr als einer Spalte beziehen, müssen Sie die Spaltennummer angeben.
Beispiel 2 – Verwendung von REFERENCE, um den Wert mehrerer Listen abzurufen
Im folgenden Beispiel habe ich nicht den gesamten Bereich auf einmal ausgewählt, sondern drei verschiedene Bereiche. Im letzten Argument haben wir 2 in „area_number“ angegeben, wodurch der zu verwendende Bereich aus diesen drei verschiedenen Bereichen definiert wird.
In der zweiten Zeile beziehen wir uns nun auf die 5. Zeile und die 1. Spalte. INDEX hat den Wert 172 zurückgegeben, der in der 5. Zeile der 2. Zeile steht.
6. SVERWEIS-Funktion
Die VLOOKUP-Funktion sucht nach einem Wert in der ersten Spalte einer Tabelle und gibt den Wert in derselben Zeile des entsprechenden Werts mithilfe der Indexnummer zurück. Vereinfacht ausgedrückt führt es eine vertikale Suche durch.
Syntax
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
Argumente
- lookup_value: Ein Wert, nach dem Sie in einer Spalte suchen möchten. Sie können auf eine Zelle verweisen, die den Suchwert enthält, oder diesen Wert direkt in die Funktion eingeben.
- table_array: Ein Bereich von Zellen, ein benannter Bereich, aus dem Sie den Wert ermitteln möchten.
- col_index_num: Eine Zahl stellt die Spaltennummer dar, aus der Sie den Wert abrufen möchten.
- range_lookup: Verwenden Sie „false“ oder 0 für eine genaue Übereinstimmung und „true“ oder 1 für eine korrekte Übereinstimmung. Der Standardwert ist True.
Kommentare
- Wenn VLOOKUP den gesuchten Wert nicht findet, wird ein #N/A zurückgegeben.
- VLOOKUP kann Ihnen nur den Wert liefern, der rechts vom Suchwert steht. Wenn Sie von der rechten Seite schauen möchten, können Sie dafür INDEX und MATCH verwenden.
- Wenn Sie „Genaue Übereinstimmung“ verwenden, wird nur der Wert abgeglichen, der an erster Stelle in der Spalte steht.
- Sie können mit VLOOKUP auch Platzhalter verwenden.
- Sie können TRUE oder 1 verwenden, wenn Sie eine korrekte Übereinstimmung wünschen, und FALSE oder 0 für eine genaue Übereinstimmung.
- Wenn Sie eine richtige Übereinstimmung (True) verwenden: Es wird der nächstkleinere Wert in der Liste zurückgegeben, wenn es keine genaue Übereinstimmung gibt.
- Wenn der gesuchte Wert kleiner als der kleinste Wert in der Liste ist, gibt VLOOKUP #N/A zurück.
- Wenn Sie nach einem genauen Wert suchen, erhalten Sie genau diesen Wert.
- Stellen Sie sicher, dass Sie die Liste in aufsteigender Reihenfolge sortiert haben.
Beispiel
1. Verwenden von SVERWEIS für Kategorien
Im folgenden Beispiel haben wir eine Liste der Schüler mit den von ihnen erhaltenen Noten, und in der Bemerkungsspalte möchten wir eine auf ihren Noten basierende Note erhalten.
In der Markenliste oben möchten wir Anmerkungen entsprechend dem unten stehenden Kategorienbereich hinzufügen.
Dabei stehen uns zwei Möglichkeiten zur Verfügung.
Die ERSTE Option besteht darin, eine verschachtelte Formel mit IF zu erstellen, was etwas Zeit in Anspruch nimmt, und die ZWEITE Option besteht darin, mit VLOOKUP eine Formel mit einer geeigneten Übereinstimmung zu erstellen. Und die Formel wird sein:
=VLOOKUP(B2,$E$2:$G$5,3,TRUE)
Wie es funktioniert
Ich verwende die Spalte „MIN MARKS“, um den Suchwert abzugleichen und den Wert aus der Spalte „Remarks“ zurückzuerhalten.
Ich habe bereits erwähnt, dass, wenn Sie TRUE verwenden und es keinen genau passenden Suchwert gibt, der nächstkleinere Wert aus dem Suchwert zurückgegeben wird. Wenn wir beispielsweise in der Kategorietabelle nach dem Wert 77 suchen, ist 65 der kleinste Wert nach 77.
Deshalb haben wir in den Kommentaren die Note „Gut“ erhalten.
2. Fehlerbehandlung in der VLOOKUP-Funktion
Eines der häufigsten Probleme, die bei der Verwendung von VLOOKUP auftreten, besteht darin, dass Sie immer dann #N/A erhalten, wenn keine Übereinstimmung gefunden wird. Aber die Lösung für dieses Problem ist einfach und leicht. Lassen Sie mich es Ihnen anhand eines einfachen Beispiels zeigen.
Im folgenden Beispiel haben wir eine Liste mit Namen und deren Alter und in Zelle E6 verwenden wir die VLOOKUP-Funktion, um nach einem Namen in der Liste zu suchen. Jedes Mal, wenn ich einen Namen eingebe, der nicht auf der Liste steht, erhalte ich die Meldung #N/A.
Ich möchte hier jedoch eine aussagekräftige Meldung anstelle des Fehlers anzeigen. Die Formel lautet: =IFNA(VLOOKUP(D6,Sheet3!$A$1:$B$14,2,0),”Not Found”)
So funktioniert es : IFNA kann einen Wert auf #N/A testen und wenn ein Fehler auftritt, können Sie anstelle des Fehlers einen Wert angeben.
7. IFNA-Funktion
Die IFNA-Funktion gibt einen bestimmten Wert zurück, wenn ein #N/A-Fehler auftritt. Im Gegensatz zu IFERROR wertet es nur den #N/A-Fehler aus und gibt den von Ihnen angegebenen Wert zurück.
Syntax
IFNA(Wert, value_if_na)
Argumente
- Wert: Der Wert, den Sie auf #N/A-Fehler testen möchten.
- value_if_na: Der Wert, den Sie zurückgeben möchten, wenn ein Fehler aufgetreten ist.
Kommentare
- Wenn Sie keine Argumente angeben, behandelt IFNA diese als leere Zeichenfolge („“).
- Wenn ein Wert ein Array ist, wird das Ergebnis als Array zurückgegeben.
- Alle anderen Fehler werden ignoriert: #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME? und #NULL!.
Beispiel
In der SVERWEIS-Funktion tritt #N/A auf, wenn der Suchwert nicht im Suchbereich liegt und wir hierfür eine aussagekräftige Nachricht mit IFNA angegeben haben.
Hinweis: IFNA wurde in Excel 2013 eingeführt und ist daher in früheren Versionen nicht verfügbar.
8. RAND-Funktion
Die RAND-Funktion gibt eine Zufallszahl zwischen 0 und 1 zurück. Vereinfacht ausgedrückt können Sie eine Zufallszahl zwischen 0 und 1 generieren (sie aktualisiert ihren Wert jedes Mal, wenn Sie eine Änderung am Arbeitsblatt vornehmen).
Syntax
RAND()
Argumente
- In RAND-Funktionen müssen keine Argumente angegeben werden
Kommentare
- Wenn Sie in ein Vielfaches eine Null eingeben, wird im Ergebnis eine Null zurückgegeben.
- Wenn Sie die Angabe mehrerer ignorieren, wird ein Fehler zurückgegeben.
- Es wird von Null aus gerundet.
- Wenn Sie zwei Vielfache im gleichen Abstand haben, wird das höhere Vielfache der Zahl zurückgegeben, die Sie runden.
Beispiel
Neben Zahlen zwischen 0 und 1 können Sie RAND auch für Zufallszahlen zwischen zwei bestimmten Zahlen verwenden. Im folgenden Beispiel habe ich damit eine Formel erstellt, die eine Zufallszahl zwischen 50 und 100 generiert.
Wenn Sie diese Formel in eine Zelle eingeben, wird eine Zahl zwischen 100 und 50 zurückgegeben, indem die vom RAND zurückgegebenen Werte mit der von uns verwendeten Gleichung multipliziert werden. Um diese Formel zu verstehen, müssen wir sie in drei Teile unterteilen:
- Wenn zunächst die niedrigere Zahl von der höheren Zahl erkannt wird, erhalten Sie die Differenz zwischen den beiden.
- Dann multipliziert es diese Differenz mit der Zufallszahl, die nach der Subtraktion zurückgegeben wird.
- Und drittens addieren Sie diese Zahl mit der niedrigsten verbleibenden Zahl im dritten Teil der Gleichung.
Verwandte Themen: So generieren Sie schnell zufällige Buchstaben in Excel
9. SUM-Funktion
Die SUM-Funktion gibt die Summe der bereitgestellten Werte zurück. Vereinfacht ausgedrückt können Sie mit der SUM-Funktion die Summe einer Werteliste berechnen (Sie können einen Wert direkt in die Funktion eingeben oder sich auf einen Zellbereich beziehen).
Syntax
SUMME(Zahl1,[Zahl2],…)
Argumente
- Zahl1 : Zahl, Bereich von Zellen, die Zahlen enthalten, oder einzelne Zelle, die eine Zahl enthält.
- [Nummer2] : Zahl, Bereich von Zellen, die Zahlen enthalten, oder einzelne Zelle, die eine Zahl enthält.
Kommentare
- Es ignoriert Textwerte.
Beispiel
Im folgenden Beispiel können Sie Zahlen mit Kommas dazwischen direkt in die Funktion einfügen.
Sie können sich auch einfach auf einen Bereich beziehen, um die Summe der Zahlen zu berechnen. Wenn Text, logische Werte oder leere Zellen vorhanden sind, werden diese ignoriert.
Wenn in einer Zelle, auf die Sie sich beziehen, ein Fehlerwert vorhanden ist, wird im Ergebnis #N/A zurückgegeben.
Wenn Sie numerische Werte als Text formatiert haben, werden diese ignoriert. Es wird empfohlen, sie vor der Verwendung von SUM in Zahlen umzuwandeln.
10. ODER-Funktion
Die OR-Funktion gibt einen booleschen Wert (TRUE oder FALSE) zurück, nachdem die von Ihnen angegebenen Bedingungen getestet wurden. Vereinfacht ausgedrückt können Sie mit der AND-Funktion mehrere Bedingungen testen. Sie gibt TRUE zurück, wenn eine dieser Bedingungen (oder alle) TRUE ist, und gibt nur FALSE zurück, wenn alle dieser Bedingungen FALSE sind.
Syntax
ODER(Logik1, [Logik2], …)
Argumente
- logical1: Bedingung, die Sie überprüfen möchten.
- [logisch2]: Zusätzliche Bedingungen, die Sie überprüfen möchten.
Kommentare
- Werte werden ignoriert, wenn die Referenzzelle oder -tabelle eine leere Zelle oder einen leeren Text enthält.
- Das Ergebnis der Bedingungen muss ein logischer Wert (TRUE oder FALSE) sein.
- Es wird ein Fehler zurückgegeben, wenn kein logischer Wert zurückgegeben wird.
Beispiel
Im folgenden Beispiel haben wir mit der IF-Funktion eine Bedingung erstellt, dass die Formel WAHR zurückgibt, wenn ein Schüler in einem der beiden Fächer die oben genannten 60 Punkte erreicht.
Im folgenden Beispiel haben wir nun eine Zahl verwendet, um logische Werte in einer Formel zu erhalten. Sie können die obige Bedingung auch in umgekehrter Reihenfolge ausführen.
Anstelle von Zahlen können Sie TRUE und FALSE verwenden. Die OR-Funktion behandelt diese logischen Werte als Zahlen.
Weitere Tutorials
Statistische Funktionen / Datumsfunktionen / String – Textfunktionen / Finanzfunktionen