Liste der 10 wichtigsten erweiterten Excel-Formeln und -Funktionen
Sie können diese erweiterte Excel-Formelvorlage hier herunterladen - erweiterte Excel-Formelvorlage# 1 - VLOOKUP-Formel in Excel
Diese erweiterte Excel-Funktion ist eine der am häufigsten verwendeten Formeln in Excel. Dies liegt hauptsächlich an der Einfachheit dieser Formel und ihrer Anwendung beim Nachschlagen eines bestimmten Werts aus anderen Tabellen, die eine gemeinsame Variable in diesen Tabellen haben. Angenommen, Sie haben zwei Tabellen mit Details zum Mitarbeitergehalt und -namen eines Unternehmens, wobei die Mitarbeiter-ID eine primäre Spalte ist. Sie möchten das Gehalt aus Tabelle B in Tabelle A erhalten.
Sie können VLOOKUP wie folgt verwenden.
Dies führt zu der folgenden Tabelle, wenn wir diese erweiterte Excel-Formel in anderen Zellen der Spalte Mitarbeitergehalt anwenden.
Ziehen Sie die Formel in die restlichen Zellen.
Es gibt drei Hauptabgrenzungen von VLOOKUP:
- Sie können keine Primärspalte rechts neben der Spalte haben, für die Sie den Wert aus einer anderen Tabelle auffüllen möchten. In diesem Fall darf die Spalte Mitarbeitergehalt nicht vor der Mitarbeiter-ID stehen.
- Bei doppelten Werten in der Primärspalte in Tabelle B wird der erste Wert in die Zelle eingetragen.
- Wenn Sie eine neue Spalte in die Datenbank einfügen (z. B. eine neue Spalte vor dem Mitarbeitergehalt in Tabelle B einfügen), kann die Ausgabe der Formel basierend auf einer Position, die Sie in der Formel erwähnt haben, unterschiedlich sein (im obigen Fall die Ausgabe wäre leer)
# 2 - INDEX-Formel in Excel
Diese erweiterte Excel-Formel wird verwendet, um den Wert einer Zelle in einer bestimmten Tabelle durch Angabe der Anzahl der Zeilen, Spalten oder beider zu ermitteln. Ex. Um den Namen eines Mitarbeiters bei der 5. Beobachtung zu erhalten, finden Sie unten die Daten.
Wir können die erweiterte Excel-Formel wie folgt verwenden:
Dieselbe INDEX-Formel kann verwendet werden, um Werte entlang der Zeile abzurufen. Wenn Sie sowohl die Zeilen- als auch die Spaltennummer verwenden, sieht die Syntax folgendermaßen aus:
Die obige Formel würde "Rajesh Ved" zurückgeben.
Hinweis: Wenn Sie in der 5. Zeile eine weitere Zeile in die Daten einfügen, gibt die Formel "Chandan Kale" zurück. Daher würde die Ausgabe von Änderungen abhängen, die im Laufe der Zeit in der Datentabelle auftreten.
# 3 - MATCH-Formel in Excel
Diese erweiterte Excel-Formel gibt die Zeilen- oder Spaltennummer zurück, wenn eine Übereinstimmung mit einer bestimmten Zeichenfolge oder Nummer im angegebenen Bereich vorliegt. Im folgenden Beispiel versuchen wir, die Position von „Rajesh Ved“ in der Spalte Name des Mitarbeiters zu ermitteln.
Die Formel wäre wie folgt:
Die MATCH-Funktion würde 5 als Wert zurückgeben.
Das 3. Argument wird für die genaue Übereinstimmung verwendet. Sie können je nach Ihren Anforderungen auch +1 und -1 verwenden.
Hinweis: Sie können INDEX und MATCH kombinieren, um die Einschränkung von VLOOKUP zu überwinden.
# 4 - WENN UND Formel in Excel
Es gibt viele Fälle, in denen Flags basierend auf bestimmten Einschränkungen erstellt werden müssen. Wir alle kennen die grundlegende Syntax von IF. Wir verwenden diese erweiterte Excel-IF-Funktion, um ein neues Feld zu erstellen, das auf einer Einschränkung eines bereits vorhandenen Felds basiert. Was aber, wenn beim Erstellen eines Flags mehrere Spalten berücksichtigt werden müssen? Ex. Im folgenden Fall möchten wir alle Mitarbeiter kennzeichnen, deren Gehalt größer als 50.000, die Mitarbeiter-ID jedoch größer als 3 ist.
In solchen Fällen würden wir IF AND verwenden. Nachfolgend finden Sie den Screenshot dazu.
Es würde das Ergebnis als 0 zurückgeben.
Wir können viele Bedingungen oder Einschränkungen haben, um ein Flag basierend auf mehreren Spalten mit AND zu erstellen.
# 5 - WENN ODER Formel in Excel
In ähnlicher Weise können wir die OR-Funktion auch in Excel anstelle von AND verwenden, wenn wir nur eine der Bedingungen von vielen erfüllen müssen.
In den oben genannten Fällen wird, wenn eine der Bedingungen erfüllt ist, die Zelle mit 1 else 0 gefüllt. Wir können 1 oder 0 auch durch einige Teilzeichenfolgen mit doppelten Anführungszeichen ("") ersetzen.
# 6 - SUMIF-Formel in Excel
In einigen Analysen müssen Sie möglicherweise einige Beobachtungen filtern, wenn Sie die Summen- oder Zählfunktion anwenden. In solchen Fällen ist diese erweiterte Excel-SUMIF-Funktion in Excel zu unserer Rettung. Es filtert alle Beobachtungen basierend auf bestimmten Bedingungen, die in dieser erweiterten Excel-Formel angegeben sind, und fasst sie zusammen. Ex. Was ist, wenn wir die Summe der Gehälter nur der Mitarbeiter wissen möchten, deren Mitarbeiter-ID größer als 3 ist?
Durch Anwenden der SUMIFS-Formel:
Die Formel gibt die Ergebnisse als 322000 zurück.
Wir können auch die Anzahl der Mitarbeiter in der Organisation zählen, deren Mitarbeiter-ID größer als 3 ist, wenn wir COUNTIF anstelle von SUMIF verwenden.
# 7 - CONCATENATE Formel in Excel
Diese erweiterte Excel-Funktion ist eine der Formeln, die mit mehreren Varianten verwendet werden können. Diese erweiterte Excel-Formel hilft uns, mehrere Textzeichenfolgen zu einer Textzeichenfolge zusammenzufügen. Beispiel: Wenn wir die Mitarbeiter-ID und den Mitarbeiternamen in einer einzigen Spalte anzeigen möchten.
Wir können diese CONCATENATE-Formel verwenden, um das zu tun.
Die obige Formel führt zu "1Aman Gupta".
Wir können eine weitere Variante haben, indem wir einen einzelnen Bindestrich zwischen ID und NAME setzen. Ex. CONCATENATE (B3, "-", C3) führt zu "1-Aman Gupta". Wir können dies auch in VLOOKUP verwenden, wenn LOOKUP im Excel-Wert eine Mischung aus mehr als einer Variablen ist.
# 8 - Formel LINKS, MITTEL und RECHTS in Excel
Wir können diese erweiterte Excel-Formel verwenden, wenn wir einen bestimmten Teilstring aus einem bestimmten String extrahieren möchten. Die genannten Formeln können basierend auf unseren Anforderungen verwendet werden. Ex. Wenn wir die ersten 5 Zeichen aus dem Mitarbeiternamen extrahieren möchten, können wir die LEFT-Formel in Excel mit dem Spaltennamen und dem zweiten Parameter als 5 verwenden.
Die Ausgabe ist unten angegeben:
Die Anwendung der RECHTEN Formel in Excel ist ebenfalls dieselbe. Wir würden nur das Zeichen von rechts auf die Zeichenfolge betrachten. Im Fall einer MID-Funktion in Excel müssen wir jedoch die Startposition der erforderlichen Textzeichenfolge und die Länge der Zeichenfolge angeben.
# 9 - OFFSET-Formel in Excel
Diese erweiterte Excel-Funktion mit einer Kombination anderer Funktionen wie SUM oder AVERAGE kann hilfreich sein, um den Berechnungen einen dynamischen Touch zu verleihen. Es wird am besten in Fällen verwendet, in denen fortlaufende Zeilen in eine vorhandene Datenbank eingefügt werden. OFFSET Excel gibt uns einen Bereich, in dem Referenzzelle, Anzahl der Zeilen und Spalten angegeben werden müssen. Ex. Wenn wir den Durchschnitt der ersten 5 Mitarbeiter in dem Unternehmen berechnen möchten, in dem wir ein Gehalt von Mitarbeitern haben, das nach Mitarbeiter-ID sortiert ist, können wir Folgendes tun. Die folgende Berechnung gibt uns immer ein Gehalt.
- Es gibt uns die Summe der Gehälter der ersten 5 Mitarbeiter.
# 10 - TRIM-Formel in Excel
Diese erweiterte Excel-Formel wird verwendet, um unwichtige Leerzeichen aus dem Text zu entfernen. Ex. Wenn wir Leerzeichen am Anfang eines Namens entfernen möchten, können wir sie mithilfe der TRIM-Funktion in Excel wie folgt verwenden:
Die resultierende Ausgabe wäre "Chandan Kale" ohne Leerzeichen vor Chandan.
