Excel ist ein großartiges Werkzeug zum Berichten, Analysieren, Organisieren und Automatisieren von Daten. Die Excel-Funktionen helfen sehr bei der Bearbeitung von Daten. Die Funktionen wie COUNTIFS, SUMIFS, SLOOKUP usw. sind die mächtigsten und am häufigsten verwendeten Funktionen seit ihrer Einführung in der Excel-Welt.
Zwar reichen die in Excel 2016 und älter verfügbaren Funktionen aus, um jede Art von Berechnung und Automatisierung zu erarbeiten, aber manchmal werden die Formeln knifflig. Wenn Sie beispielsweise bei einigen Bedingungen nicht den maximalen Wert finden, müssen Sie in der älteren Excel-Version 2016 einige Tricks anwenden. Diese kleinen, aber wichtigen Dinge werden in Excel 2019 und 365 gelöst.
Es gibt über 10 neue Funktionen in Excel 2019 und 365, die den menschlichen Aufwand und die Komplexität der Formeln reduzieren.
1. Die MAXIFS-Funktion
Wenn Sie in Excel 2016 und älter den maximalen Wert in einem Bereich erhalten möchten, wenn eine oder mehrere Bedingungen zutreffen, müssen Sie MAX mit IF mit einigen Tricks verwenden. Das ist nicht sehr schwierig, aber zeitaufwendig und für manche verwirrend.
Excel 2019 führt eine neue Funktion namens MAXIFS-Funktion ein. Diese Funktion gibt den maximalen Wert aus einem Array zurück, wenn alle angegebenen Bedingungen erfüllt sind.
Die Syntax der Funktion lautet:
| =MAXIFS(max_range,criteria_range1,criteria1, Criteria_range2,criteria2… ) |
Max_Bereich1: Es ist der Zahlenbereich, der den Maximalwert enthält.
Kriterien_Bereich1: Dies ist der Kriterienbereich, den Sie filtern möchten, bevor Sie den maximalen Wert erhalten.
Kriterien1: Es sind die Kriterien oder der Filter, die Sie auf den Kriterienbereich setzen möchten, bevor Sie den Max-Wert erhalten.
Angenommen, Sie müssen die Höchstpunktzahl von Klasse 3 erreichen, dann lautet die Formel:
| =MAXIFS(Markierungen,Klasse,3) |
Hier sind Markierungen der benannte Bereich, der die Markierungen enthält, und Klasse ist der benannte Bereich, der die Klasse enthält.
Lesen Sie hier mehr über die MAXIFS-Funktion.
2. Die MINIFS-Funktion
Wie die MAXIFS-Funktion wird die MINIFS-Funktion verwendet, um den Mindestwert aus dem gegebenen Bereich zu erhalten, wenn alle gegebenen Bedingungen erfüllt sind.
Die Syntax der Funktion lautet:
| =MINIFS(min_range,criteria_range1,criteria1, Criteria_range2,criteria2… |
Min_range1: Es ist der Zahlenbereich, der den Mindestwert enthält.
Kriterien_Bereich1: Dies ist der Kriterienbereich, den Sie filtern möchten, bevor Sie den Mindestwert erhalten.
Kriterien1: Es sind die Kriterien oder der Filter, die Sie auf den Kriterienbereich setzen möchten, bevor Sie den Mindestwert erhalten.
Angenommen, Sie müssen die Mindestpunktzahl von Klasse 3 erreichen, dann lautet die Formel
| =MINIFS(Markierungen,Klasse,3) |
Hier ist "marks" der benannte Bereich, der die Markierungen enthält, und "class" ist der benannte Bereich, der die Klasse enthält.
Lesen Sie hier mehr über die MAXIFS-Funktion.
Um den Mindestwert im Bereich mit Bedingungen in Excel 2016 und älter zu finden, lesen Sie dies.
3. Die IFS-Funktion
Da das verschachtelte Ifs einen besonderen Platz in unserem Arbeitsalltag einnimmt, gefällt es uns sehr gut. Aber für einige neue Lernende ist es komplex. Mit den verschachtelten ifs können wir mehrere Bedingungen überprüfen und einen anderen Wert zurückgeben, wenn eine der Bedingungen erfüllt ist. Die Formeln werden mit immer mehr IF-Funktionen komplexer.
Excel 2019 und Excel 365 verwenden jetzt die IFS-Funktion. Es kann mehrere Bedingungen prüfen und für jede Bedingung unterschiedliche Werte zurückgeben.
Syntax der IFS-Funktion:
| =IFS (Bedingung1, Wert1_If_True, [Bedingung2, Wert2_If_True],… ) |
Bedingung1:Die erste Bedingung.
Wert1_If_True: Wert, wenn die erste Bedingung wahr ist.
[Bedingung2]: Dies ist optional. Die zweite Bedingung, falls vorhanden.
[Wert1_If_True]: Wert, wenn die zweite Bedingung wahr ist.
Sie können beliebig viele Kombinationen von Bedingungen und Werten verwenden. Es gibt eine Grenze, aber Sie müssen diese Grenze nie erreichen.
Nehmen wir an, Sie müssen den Schülern die Noten nach ihren Noten geben. Für Noten über 80, Note A, B für mehr als 60, C für mehr als 40 und F für weniger als oder gleich 40.
| =IFS (A1>80, "A", A1>60, "B",A1>40, "C",A1<=40, "F") |
Eine ausführliche Erläuterung der IFS-Funktion finden Sie hier.
4. Die SWITCH-Funktion
Die switch-Funktion gibt abhängig von den Ergebnissen eines Ausdrucks unterschiedliche Werte zurück. Klingt nach IFS? Es ist irgendwie. Tatsächlich dient diese Funktion zum Ersetzen einer anderen Art von verschachtelten IF-Formeln.
Im Gegensatz zur IFS-Funktion, die Werte basierend auf TRUE, FALSE; die SWITCH-Funktion gibt Werte basierend auf VALUES zurück, die vom Ausdruck zurückgegeben werden.
| =SCHALTEN (Ausdruck, Wert1,Ergebnis1, [Standard oder Wert2,Ergebnis2],… ) |
Ausdruck: Dies kann jeder gültige Ausdruck sein, der einige Werte zurückgibt. Ein Zellbezug, eine Formel oder ein statischer Wert.
Wert1,Ergebnis1: Wert und Ergebnis werden gepaart. Wenn der vom . zurückgegebene WertAusdruck ist value1, dann wird result1 zurückgegeben.
[Standard oder Wert2,Ergebnis2]: Wenn Sie einen Standardwert zurückgeben möchten, definieren Sie ihn hier. Ansonsten definieren Sie value2 und result2. Es ist optional.
Zum Beispiel, wenn Sie eine Formel haben, die die Namen von Tieren zurückgibt. Abhängig vom zurückgegebenen Namen des Tieres möchten Sie nun den Signaturton dieses Tieres zurückgeben.
| =SCHALTER (A1, "Hund","Bow Wow", "Cat","Miau", "Spricht") |
Die SWITCH-Funktion habe ich hier ausführlich erklärt.
5. Die FILTER-Funktion
Die FILTER-Funktion wird verwendet, um Daten nach bestimmten Kriterien zu filtern. Wir haben die Filteroption von der Registerkarte Start in Excel verwendet. Die FILTER-Funktion funktioniert genauso wie die Filteroption. Es gibt nur die gefilterten Daten mithilfe einer Funktion zurück. Diese gefilterten Daten können als Datenquelle für andere Formeln verwendet werden.
Die Syntax der FILTER-Funktion lautet:
| =FILTER(array,einschließen,[if_leer]) |
Anordnung: Dies ist das Array, das Sie filtern möchten. Es kann eindimensional oder zweidimensional sein.
Enthalten:Es ist der Filter, den Sie in das Array einfügen möchten. Wie, Farben = "rot".
[if_leer]:Dies ist optional. Definieren Sie einen beliebigen Text oder Ausdruck, wenn der Filter nichts zurückgibt.
Die folgende Formel gibt alle Früchte zurück, deren Farbe rot ist.
| =FILTER(fruits,color="red" ,"no Fruits found") |
Frucht und Farbe sind hier benannte Bereiche, die Namen der Früchte bzw. deren Farben enthalten.
Hier können Sie sich ausführlich über die FILTER-Funktion informieren.
6. Die SORT-Funktion
In Excel 2016 und älter war es wirklich schwierig, mithilfe einer Formel ein sortiertes Array zu erhalten. Dieser Vorgang wird in Excel 2019 und 365 vereinfacht.
Das Excel 2019 führt die Funktion SORT ein. Die SORT-Funktion sortiert das angegebene Array in aufsteigender oder absteigender Reihenfolge nach der angegebenen Spalte/Zeile.
Die Syntax der SORT-Funktion lautet:
| =SORT(array,[sort_index],[sort_order],[by_col]) |
Anordnung:Es ist die Referenz des Arrays oder Bereichs, die Sie sortieren möchten.
[sort_index]:Die Spaltennummer im zweidimensionalen Array, nach der Sie den Bereich sortieren möchten. Standardmäßig ist es 1.
[Sortierreihenfolge]:Die Reihenfolge, nach der Sie das Array sortieren möchten. Für aufsteigend ist es 1 und für absteigend ist es -1. Standardmäßig ist es 1.
[by_col]:Setzen Sie True(1), wenn Sie ein horizontales Array sortieren möchten. Standardmäßig ist es False(0) für vertikale Daten.
Angenommen, Sie möchten Werte im Bereich A2:A11 aufsteigend sortieren. dann wird die Formel sein.
| =SORTIEREN(A2:A11) |
Die SORT-Funktion habe ich hier ausführlich erklärt.
7. Die SORTBY-Funktion
Die SORTBY-Funktion ähnelt der SORT-Funktion. Der einzige Unterschied besteht darin, dass das Sortierarray nicht Teil des sortierten Arrays in der Funktion SORTBY sein muss.
| =SORTIEREN(Array,Sortier_Array1,[Reihenfolge],…) |
Anordnung:Dies ist das Array, das Sie sortieren möchten.
Sortierung_array1:Dies ist das Array, nach dem Sie das Array sortieren möchten. Die Dimension dieses Arrays sollte mit der kompatibel sein Array.
[Auftrag]:Optional. Setzen Sie ihn auf -1, wenn die Reihenfolge absteigend sein soll. Standardmäßig ist es aufsteigend(1).
Nehmen wir an, Sie möchten den Bereich A2:A11 nach Bereich B2:B11 in absteigender Reihenfolge sortieren. Dann lautet die Formel in Excel 2019 oder 365:
| =SORTIEREN (A2:A11,B2:B11,-1) |
Die SORTBY-Funktion habe ich hier ausführlich erklärt.
8. Die EINZIGARTIGE Funktion
In Excel 2016 und älter haben wir eine Reihe von Funktionen in Kombination verwendet, um alle eindeutigen Werte aus der angegebenen Liste zu erhalten. Die verwendete Formel ist ziemlich komplex und schwer zu verstehen.
Excel 2019 und 365 führen eine einfache UNIQUE-Funktion ein, die alle eindeutigen Werte aus einem bestimmten Array zurückgibt.
Die Syntax der UNIQUE-Funktion lautet:
| =EINZIGARTIG(array,[by_col],[exactly_once]) |
Array: Das Array, aus dem Sie eindeutige Werte extrahieren möchten:
[by_col]: Setzen Sie es auf TRUE(1), wenn das Array horizontal ist. Standardmäßig ist es FALSE für vertikale Daten.
[exakt_einmal]: Setzen Sie es auf TRUE(1), wenn Sie Werte extrahieren möchten, die nur einmal im Array vorkommen. Standardmäßig ist es FALSE(0), um alle eindeutigen Werte zu extrahieren.
Nehmen wir an, ich möchte nur eine Instanz jedes Werts aus dem Bereich A2: A11 abrufen, dann lautet die Formel:
| =EINZIGARTIG(A2:A11) |
Um mehr über die UNIQUE-Funktion im Detail zu erfahren, klicken Sie hier.
9. Die SEQUENCE-Funktion
Um eine Zahlenfolge in Excel 2016 und älter zu erhalten, verwenden wir eine Kombination von Funktionen. Die Lösung funktioniert, ist aber komplex.
Das Excel 2019 und 365 bietet die Lösung in Form der SEQUENCE-Funktion. Die Sequenzfunktion gibt einfach die Reihe der Zahl zurück.
Die Syntax der SEQUENCE-Funktion lautet:
| =SEQUENCE(Zeilen,[Spalten],[Start],[Schritt]) |
Reihen:Die Anzahl der Zeilen, auf die Sie die Sequenz verteilen möchten.
[Säule]:Die Anzahl der Spalten, auf die Sie die Sequenz verteilen möchten. Die Zahlen füllen zuerst die Spalten und dann die Zeilen. Die Spalte ist optional. Standardmäßig ist es 1.
[Anfang]:Optional. Die Startnummer der Sequenz. Standardmäßig ist es 1.
[Schritt]:Dies ist die Inkrementnummer für die nächste Nummer. Standardmäßig ist es 1.
Das einfache Beispiel besteht darin, eine Reihe von 1 bis 10 zu erhalten. Die Formel lautet:
| =SEQUENZ(10) |
Um die SEQUENCE-Funktion in Excel 365 im Detail zu verstehen, lesen Sie dies.
10. Die RANDARRAY-Funktion
Dies ist eine weitere dynamische Array-Formel, die ein Array von Zufallszahlen zurückgibt. Es ist eine Kombination aus RAND- und RANDBETWEEN-Funktion. Sie können gebrochene Zufallszahlen oder ganze Zahlen erhalten. Sie können die gewünschte Anzahl von Zufallszahlen angeben. Auch Zeilen und Spalten, in denen Sie diese Zahlen verteilen möchten.
Die Syntax der RANDARRAY-Funktion lautet:
| =RANDARRAY([Zeilen],[Spalten],[min],[max],[integer]) |
Alle Argumente in dieser Funktion sind optional. Standardmäßig arbeitet es als RAND-Funktion.
[Reihen]:Die Anzahl der Zahlen, die Sie vertikal möchten (Anzahl der Zeilen, die Sie ausfüllen möchten).
[Säulen]:Die Anzahl der Zahlen, die Sie horizontal möchten (Anzahl der Spalten, die Sie ausfüllen möchten).
[Mindest]:Die Startnummer oder der Mindestwert der Zufallszahl/en.
[max]:Der maximale Bereich der Nummer.
[ganze Zahl]:Setzen Sie es auf true, wenn die Zufallszahlen ganze Zahlen sein sollen. Standardmäßig ist es false und gibt zufällige Bruchzahlen zurück.
Die folgende Funktion gibt zeilenweise fünf zufällige Bruchzahlen zurück:
| =RANDARRAY(5) |
Lesen Sie hier mehr über die RANDARRAY-Funktion.
11. Die CONCAT-Funktion
In Excel 2016 und älter ist es nicht einfach, mehr als eine Zelle oder einen Bereich mit einer Formel zu verketten.
Beim Excel 2019 und 365 wird das Problem mit der Funktion CONCAT gelöst. Die Funktion kann mehrere Zellen und Bereiche als Argumente annehmen.
Die Syntax der CONCAT-Funktion lautet:
| =CONCAT(text1,[text2],… ) |
Text 1 : Bei text1 kann es sich um einen beliebigen Text oder Bereich handeln, den Sie verketten möchten.
[Text2]: Dies ist optional. Dies kann auch ein beliebiger Text oder Bereich sein.
Nehmen wir an, wenn Sie jede Zelle im Bereich A2: A11 verketten möchten, lautet die Formel
| =KONKAT(A2:A11) |
Um die CONCAT-Funktion im Detail zu erkunden, klicken Sie hier.
12. Die TEXTJOIN-Funktion
Die obige Funktion verkettet alle Zellen in einem Bereich, aber sie verkettet die Zellen nicht mit einem bestimmten Trennzeichen. Nehmen wir an, wenn Sie eine Datei für das CSV-Format vorbereiten, müssen Sie die Zellen mit Kommas verketten. In diesem Fall schlagen die Funktionen CONCATENATE und CONCAT fehl.
Hier wirkt die Funktion TEXTJOIN wunderbar und verkettet die angegebenen Texte mit dem angegebenen Trennzeichen.
| =TEXTJOIN(Trennzeichen, ignore_empty_cells,text1,[text2],… ) |
Trennzeichen:Dies ist das Trennzeichen, das Sie als Trennzeichen zwischen einzelnen Texten verwenden möchten. Es kann ein Komma (,), Semikolon (;) oder irgendetwas sein, sogar nichts.
Ignore_empty_cells:Dies ist eine binäre Variable. Setzen Sie es auf TRUE, wenn Sie leere Zellen in Bereichen ignorieren möchten, andernfalls auf FALSE, um die leeren Zellen einzuschließen.
Text 1:Dies ist der Text, den Sie verbinden möchten. Das können einzelne Texte, Zellen oder ganze Bereiche sein.
Nehmen wir an, ich möchte den Bereich A2: A11 mit einem Komma verketten und die leeren Zellen ignorieren.
| =TEXTJOIN(",",1,A2:A11) |
Um diese Funktion im Detail zu verstehen, klicken Sie hier.
Dieser Artikel war nur eine Einführung in die neue Funktion von Excel 365 und 2019. Diese Funktionen habe ich in separaten Artikeln ausführlich erläutert. Sie können auf die Links zu jeder Funktion im Artikel klicken, um die Funktion vollständig zu verstehen. Es gibt andere Funktionen wie XLOOKUP, die noch nicht freigegeben sind.
Wenn Sie Zweifel in Bezug auf Excel- oder VBA-Themen haben, fragen Sie im Kommentarbereich unten nach. Sagen Sie uns, wie wir uns verbessern können. Wir freuen uns über Ihren Vorschlag und freuen uns, von Ihnen zu hören.
VBA-Funktion erstellen, um Array zurückzugeben | Um ein Array von einer benutzerdefinierten Funktion zurückzugeben, müssen wir es deklarieren, wenn wir die UDF benennen.
Arrays in Excel Formul|Erfahren Sie, was Arrays in Excel sind.
So erstellen Sie eine benutzerdefinierte Funktion über VBA | Erfahren Sie, wie Sie benutzerdefinierte Funktionen in Excel erstellen
Verwenden einer benutzerdefinierten Funktion (UDF) aus einer anderen Arbeitsmappe mit VBA in Microsoft Excel | Verwenden Sie die benutzerdefinierte Funktion in einer anderen Arbeitsmappe von Excel
Fehlerwerte von benutzerdefinierten Funktionen mit VBA in Microsoft Excel zurückgeben | Erfahren Sie, wie Sie Fehlerwerte von einer benutzerdefinierten Funktion zurückgeben können
Populäre Artikel:
Excel-Tabelle in mehrere Dateien basierend auf Spalten mit VBA aufteilen | Dieses VBA-Code-Split-Excel-Blatt basiert auf eindeutigen Werten in einer angegebenen Spalte. Laden Sie die Arbeitsdatei herunter.
Deaktivieren von Warnmeldungen mit VBA in Microsoft Excel 2016 | Um Warnmeldungen zu deaktivieren, die den laufenden VBA-Code unterbrechen, verwenden wir die Application-Klasse.
Hinzufügen und Speichern einer neuen Arbeitsmappe mit VBA in Microsoft Excel 2016 | Um Arbeitsmappen mit VBA hinzuzufügen und zu speichern, verwenden wir die Klasse Workbooks. Workbooks.Add fügt jedoch problemlos neue Arbeitsmappen hinzu…