Liste der 10 wichtigsten erweiterten Excel-Formeln und -Funktionen
# 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 Mitarbeiter-ID stehen.
- In den duplizierten 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 angegeben haben, unterschiedlich sein (im obigen Fall das Ergebnis 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 in der 5 zu erhalten ten Beobachtung, Unten finden Sie 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 eine andere Zeile in die 5 einfügen th Reihe, wird die Formel „Chandan Kale.“ Zurückkehren 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 sich eine bestimmte Zeichenfolge oder Nummer im angegebenen Bereich befindet. Im folgenden Beispiel versuchen wir, "Rajesh Ved" in der Spalte "Name des Mitarbeiters" zu finden.
Die Formel wäre wie folgt:
Die MATCH-Funktion würde 5 als Wert zurückgeben.
Das 3 rd 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 basierend auf einer Einschränkung für ein vorhandenes Feld zu erstellen. 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 ODER-Funktion auch in Excel anstelle von UND verwenden, wenn wir 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 COUNTIF anstelle von SUMIF verwendet wird.
# 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, um einen bestimmten Teilstring aus einem bestimmten String zu extrahieren. Es könnte 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 RIGHT-Formel in Excel ist ebenfalls dieselbe. Es ist nur so, dass wir den Charakter von rechts von der Zeichenfolge betrachten würden. 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 SUMME oder DURCHSCHNITT könnte den Berechnungen einen dynamischen Touch verleihen. Es wird am besten in Fällen verwendet, in denen fortlaufende Zeilen in eine vorhandene Datenbank eingefügt werden. OFFSET Excel bietet 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.
