Erweiterte Excel-Formeln (Inhaltsverzeichnis)
- Einführung in erweiterte Formeln in Excel
- Beispiele für erweiterte Formeln in Excel
Einführung in erweiterte Formeln in Excel
Sobald Sie die mittlere Stufe in Excel erreicht haben, müssen Sie hart arbeiten, um zur höheren Stufe aufzusteigen. Um zum fortgeschrittenen Level aufzusteigen, müssen Sie einige der häufig verwendeten fortgeschrittenen Formeln kennen. In diesem Artikel werde ich die 10 wichtigsten fortgeschrittenen Formeln behandeln, die alle Excel-Lerner kennen müssen. Folgen Sie diesem Artikel, um zu lernen und zu erkunden.
Beispiele für erweiterte Formeln in Excel
Lassen Sie uns anhand einiger Beispiele verstehen, wie die erweiterten Formeln in Excel verwendet werden.
Sie können diese Excel-Vorlage für erweiterte Formeln hier herunterladen - Excel-Vorlage für erweiterte FormelnBeispiel # 1 - Partielle VLOOKUP Funktion
Es muss eine Situation aufgetreten sein, in der VLOOKUP einen Fehler auslöst, selbst wenn der Suchwert eine geringfügige Abweichung aufweist. Wenn Sie beispielsweise nach dem Gehalt für den Namen Abhishek Sinha suchen und nur Abhishek haben, kann VLOOKUP Ihnen die Daten nicht abrufen.
Wenn Sie jedoch ein Sternchen an einem der Enden des Suchwerts verwenden, können Sie die Daten für einen Teilsuchwert abrufen.
In Tabelle 1 habe ich vollen Namen und Gehalt, in Tabelle 2 habe ich nur teilweise Namen und muss das Gehalt jedes Mitarbeiters finden.
Schritt 1: Öffnen Sie die VLOOKUP-Formel in Zelle E3. Bevor Sie den Suchwert auswählen, setzen Sie einen Stern (*) neben den Suchwert.
Schritt 2: Wie gewohnt können Sie die VLOOKUP-Formel jetzt vervollständigen und wir werden die Ergebnisse haben.
Nach Verwendung der obigen Formel wird die Ausgabe unten angezeigt.
Dieselbe Formel wird in anderen Zellen verwendet.
Beispiel # 2 - COUNTIFS mit Operator Symbolen
Sie müssen die COUNTIF & IFS-Funktion verwendet haben, um die Dinge in der Liste zu zählen. Wir können auch basierend auf Operatorsymbolen wie größer als (>) kleiner als (<) und Gleichheitszeichen (=) zählen.
Schauen Sie sich zum Beispiel die folgenden Daten an. Wenn Sie die Gesamtzahl der Rechnungen für die Region SÜDEN nach dem 10. Januar 2018 zählen möchten, wie werden Sie gezählt?
Beispiel # 3 - WENN Bedingung mit UND & ODER Bedingungen
Logische Funktionen sind Teil unserer täglichen Aktivitäten. Sie müssen sie meistern, um zum nächsten Level zu gelangen. Wenn Sie den Bonus auf der Grundlage mehrerer Bedingungen berechnen, müssen Sie die UND- oder ODER-Bedingung mit der WENN-Bedingung verschachteln, um die Aufgabe auszuführen.
Angenommen, Sie müssen den Bonusbetrag für jede Abteilung basierend auf der Abteilung und den Dienstjahren berechnen, für die Sie diese Funktionen benötigen. Basierend auf den folgenden Kriterien müssen wir den Bonus berechnen.
Wenn der Dienst länger als 4 Jahre dauert und die Abteilung entweder einen Verkaufs- oder Supportbonus von 50000 oder einen Bonus von 25000 aufweist.
Wenden Sie die folgende Formel an, um den Bonusbetrag zu erhalten.
Nach Verwendung der obigen Formel wird die unten gezeigte Ausgabe angezeigt.
Dieselbe Formel gilt für die Zellen E3 bis E9.
Beispiel # 4 - TEXT-Funktion, die Sie dynamisiert
Angenommen, Sie führen eine tägliche Verkaufstabelle und müssen diese jeden Tag aktualisieren. Am Anfang der Tabelle befindet sich eine Überschrift mit dem Titel "Konsolidierte Verkaufsdaten von TT-MM-JJJJ bis TT-MM-JJJJ". Wenn die Tabelle aktualisiert wird, müssen Sie das Überschriftsdatum ändern. Ist es nicht eine frustrierende Aufgabe, immer wieder dasselbe zu tun? Wir können diese Überschrift dynamisieren, indem wir TETX-, MIN- und Max-Funktionen zusammen mit dem verketteten Operatorsymbol und (&) verwenden.
Beispiel # 5 - INDEX + MATCH + MAX, um die höchste Verkaufsperson zu finden
Sie haben eine Liste der Verkaufspersonen und der Verkäufe, die sie gegen ihren Namen getätigt haben. Woran erkennt man, wer der beste oder höchste Verkäufer in der Liste ist? Natürlich haben wir mehrere andere Techniken, um das Ergebnis zu ermitteln, aber diese Funktion kann den höchsten Verkäufer des Loses zurückgeben.
Wenden Sie die folgende Funktion an, um den höchsten Verkäufernamen zu erhalten.
Nach Verwendung der obigen Formel wird die unten gezeigte Ausgabe angezeigt.
Beispiel 6 - Ermitteln Sie die Anzahl der eindeutigen Werte aus der Liste
Wenn Sie viele doppelte Werte haben und wissen müssen, wie viele eindeutige Werte es gibt, wie sagen Sie dies? Sie können dies erkennen, indem Sie den doppelten Wert entfernen. Dies ist jedoch nicht die dynamische Methode, um die Anzahl der eindeutigen Werte zu ermitteln.
Mit dieser Array-Funktion können wir die eindeutigen Werte aus dem Los ermitteln.
Wenden Sie die folgende Formel an, um eine Liste der eindeutigen Werte zu erhalten.
Beispiel # 7 - Benannten Bereich verwenden, um das Dropdown dynamisch zu machen
Wenn Sie häufig mit einer Dropdown-Liste arbeiten und die Dropdown-Liste auf den neuesten Stand bringen, müssen Sie zum Löschen oder Hinzufügen von Werten zum Quelllistenbereich zurückkehren. Danach müssen Sie zu den Dropdown-Zellen zurückkehren und den Bereich der Dropdown-Liste erneut aktualisieren.
Wenn Sie jedoch einen benannten Bereich für Ihre Dropdown-Liste erstellen können, können Sie die Dropdown-Liste dynamisch und aktuell gestalten.
Erstellen Sie einen Namensbereich, wie in der folgenden Abbildung gezeigt.
Gehen Sie nun zur Dropdown-Zelle und öffnen Sie das Dropdown-Dialogfeld.
Drücken Sie in der Quelle die Taste F3, um alle definierten Namen anzuzeigen. Wählen Sie den Namen Ihrer Dropdown-Liste aus.
Ok, eine Dropdown-Liste ist fertig und aktualisiert die Werte automatisch, wenn sich der Dropdown-Listenbereich ändert.
Beispiel # 8 - Fehlerwerte mit der IFERROR Funktion beseitigen
Ich bin sicher, Sie haben Fehlerwerte bei der Arbeit mit VLOOKUP, Divisionsberechnungen festgestellt. Der Umgang mit diesen Fehlerwerten ist mühsam. Mit der IFERROR-Funktion in der Formel können wir diese Fehlerwerte beseitigen.
Nach Verwendung der obigen Formel wird die unten gezeigte Ausgabe angezeigt.
Dieselbe Formel wird in anderen Zellen verwendet.
Beispiel 9 - Verwenden Sie die PMT-Funktion, um Ihr eigenes EMI-Diagramm zu erstellen
Heutzutage ist die EMI-Option für uns alle keine seltsame Sache. In Excel können wir unser eigenes EMI-Diagramm mithilfe der PMT-Funktion schätzen. Führen Sie die folgenden Schritte aus, um ein eigenes Diagramm zu erstellen.
Wenden Sie die folgende Formel in Zelle B4 an, um die EMI-Menge zu erhalten.
Beispiel # 10 - INDEX + MATCH als Alternative zur VLOOKUP-Funktion
Ich hoffe, Ihnen ist die Einschränkung der VLOOKUP-Funktion bekannt. Eine Haupteinschränkung ist, dass VLOOKUP die Daten von links nach rechts und nicht von rechts nach links abrufen kann. Nicht alle Zeitdaten sind gut organisiert und einsatzbereit. Häufig befindet sich die gesuchte Wertespalte auf der linken Seite des Suchwerts, sodass VLOOKUP in diesen Fällen keine Hilfe bietet.
Eine Kombination der INDEX + MATCH-Funktion dient als Alternative zur VLOOKUP-Funktion.
Nach Verwendung der obigen Formel wird die Ausgabe unten angezeigt.
Dieselbe Formel wird in anderen Zellen verwendet.
Basierend auf der Produkt-ID müssen wir Verkaufswerte extrahieren. In der Haupttabelle befindet sich die Produkt-ID auf der rechten Seite der Verkaufsspalte. Daher kann VLOOKUP die Daten nicht abrufen. Verwenden Sie die folgende Formel, um die Daten abzurufen.
Empfohlene Artikel
Dies ist eine Anleitung zu erweiterten Formeln in Excel. Hier diskutieren wir, wie erweiterte Formeln in Excel verwendet werden, zusammen mit praktischen Beispielen und einer herunterladbaren Excel-Vorlage. Sie können auch unsere anderen Artikelvorschläge durchgehen -
- Wie benutze ich die Excel INDEX Funktion?
- Erweitertes Excel | Datenbankfunktion
- Beispiele für TRIM Formula in Excel
- Anleitung zur Excel OFFSET Formel