5 nützliche erweiterte Excel-Formeln, die Sie kennen sollten
Bei fortgesetzter Nutzung Microsoft Excel verfolgen bzw Budgetierung Oder Inventarisierung, Sie werden wahrscheinlich viel über die verfügbaren Funktionen und ihre Formeln lernen. Aber etwas Neues zu entdecken, kann verwirrend oder einschüchternd sein. Dieser Leitfaden stellt einige nützliche Funktionen und ihre Formeln vor, wenn Sie bereit sind, in neue Gebiete einzutauchen. Hier sind 5 nützliche erweiterte Excel-Formeln, die Sie kennen sollten.
1. Suchen Sie die Werte in Ihrer Tabelle: XLOOKUP
Wenn Sie auf der Grundlage anderer Daten in einer großen Tabelle nach Daten suchen möchten, ist XLOOKUP Ihre erste Wahl. außer SVERWEIS , das nur von links nach rechts nach Werten suchen kann, kann XLOOKUP von links nach rechts oder von rechts nach links nach Werten suchen, was es zu einem flexibleren Werkzeug macht.
Hinweis: Zum jetzigen Zeitpunkt ist XLOOKUP nur für Abonnenten von Microsoft 365 verfügbar. Für andere Excel-Versionen sehen Sie sich die Kombination von INDEX und MATCH im nächsten Abschnitt an.
Die Funktionsformel lautet:
XLOOKUP(lookup_value, lookup_range, return_range, not_found, match_mode, search_mode)
Nur die ersten drei Argumente sind erforderlich. Nachfolgend finden Sie eine Beschreibung der einzelnen Argumente:
- Lookup-Wert: Der zu suchende Wert.
- Suchbereich: Der Bereich, der den zu suchenden Wert enthält.
- Rückgabebereich: Der Bereich, der den zurückzugebenden Wert enthält.
- Nicht gefunden: Der zurückzugebende Text, wenn der Wert nicht gefunden wird. „#N/A“ ist die Standardeinstellung, wenn es weggelassen wird.
- match_mode: Übereinstimmungstyp mit „0“ für eine exakte Übereinstimmung und „#N/A“ für nicht gefunden (Standard, wenn ausgelassen), „1“ für exakte Übereinstimmung und das nächstkleinere Element, wenn keine gefunden wurden, „-1“ für exakte Übereinstimmung und größer Element Next, wenn nicht gefunden, oder „2“, um mit einem Platzhalter mit einem Fragezeichen, Sternchen oder einer Tilde übereinzustimmen.
- Suchmodus: Suchmodus mit „1“, um beim ersten Element zu beginnen (Standard, wenn weggelassen), „-1“, um beim letzten Element zu beginnen, „2“, wenn „lookup_range“ in aufsteigender Reihenfolge ist, oder „-2“, wenn „lookup_range“ in ist absteigende Reihenfolge.
Wir suchen beispielsweise nach Verkäufen für Segment 2, also verwenden wir die folgende Formel:
=XLOOKUP(2,A19:A24,D19:D24)
Wenn Sie die Formel aufschlüsseln, ist 2 der „lookup_value“, A19:A24 ist der „lookup_range“ und D19:D24 ist das „return_range“-Argument.
Das Ergebnis, 74000, ist der korrekte entsprechende Wert für Sektor 2.
Wenn Sie etwas Dynamischeres wünschen, bei dem Sie nach einem Wert in einer Zelle statt nach einer Konstante suchen und dann das aktualisierte Ergebnis erhalten, wenn Sie den Wert in dieser Zelle ändern, sehen Sie sich das folgende Beispiel an:
Wir ersetzen „lookup_value“ 2 durch Zelle F19:
=XLOOKUP(F19,A19:A24,D19:D24)
Wenn wir 2 in Zelle F19 eingeben, erhalten wir das Ergebnis 74000, und wenn wir einen anderen Wert wie 5 eingeben, erhalten wir automatisch das aktualisierte Ergebnis, das 61000 ist.
Sie können das Argument einfügen "nicht gefunden" Sie müssen also nicht auf eine Fehlermeldung schauen, wenn keine Übereinstimmung gefunden wurde. Wir fügen dem Argument "nicht antworten" hinzu.
=XLOOKUP(F19,A19:A24,D19:D24,"No dice")
Wenn ein Wert in Zelle F19 eingegeben wird, der keine Übereinstimmung hat, sehen Sie Ihre Nachricht anstelle von „#NA“.
2. Suchen Sie den alternativen Wert in einem Blatt: INDEX und MATCH
Während XLOOKUP Ihnen eine leistungsstarke Möglichkeit zum Nachschlagen von Werten bietet, ist es derzeit nur für Microsoft 365 Excel-Abonnenten verfügbar. Wenn Sie eine andere Version von Excel verwenden, können Sie INDEX und MATCH verwenden, um dasselbe zu tun.
Mit der INDEX-Funktion wird der Wert in der Zelle basierend auf der Position zurückgegeben, die Sie in die Formel eingegeben haben. Die INDEX-Formel lautet:
INDEX(Bereich, Zeilennummer, Spaltennummer)
Mit der MATCH-Funktion wird die Position des Wertes zurückgegeben, den Sie in die Formel eingeben. Die MATCH-Formel lautet:
MATCH(Wert, Bereich, Übereinstimmungstyp)
Um diese beiden Funktionen und ihre Formeln zu kombinieren, fügen Sie die MATCH-Formel in die INDEX-Formel als Suchposition ein („row_number“ und „column_number“).
Unter Verwendung der gleichen Daten wie im obigen XLOOKUP-Beispiel möchten wir die Segmentumsätze sehen, die wir in Zelle F19 eingeben. Die Formel lautet:
=INDEX(D19:D24,MATCH(F19,A19:A24))
Um diese Formel aufzuschlüsseln, beginnen wir mit INDEX und seinem "range"-Argument, das D19:D24 ist. Dies ist der Bereich, der das gewünschte Ergebnis enthält.
Die MATCH-Formel verwendet F19 als „Wert“-Argument und A19:A24 als „Bereich“-Argument, das diesen Wert enthält.
Wenn wir in Zelle F4 eine 19 eingeben, erhalten wir das Ergebnis 75000, was korrekt ist.
Wenn wir in Zelle F19 einen anderen Sektor eingeben, z. B. 6, wird die Formel automatisch aktualisiert, um den korrekten Wert von 58000 anzugeben.
3. HINZUFÜGEN ODER ZÄHLEN MIT KRITERIEN: SUMMEWENN, SUMMEWENN, ZÄHLENWENN, ZÄHLENWENNS
Die Funktionen SUMME und ZÄHLEN in Excel dienen zum Addieren von Zahlen und Zählen von Zellen, sind jedoch auf einfache mathematische Operationen beschränkt. Variationen dieser Funktionen ermöglichen das Hinzufügen oder Zählen anhand von Kriterien. Sie können beispielsweise nur Zahlen größer als 12 hinzufügen oder Zellen zählen, die den Namen Bill enthalten.
Mit SUMMEWENN und ZÄHLENWENN können Sie mit einer Bedingung addieren oder zählen, und mit SUMMEWENNS und ZÄHLENWENNS können Sie mit mehreren Bedingungen addieren oder zählen. Unten aufgeführt ist die Syntax für jeden:
SUMME
SUMMEWENN(Bereich, Kriterien, Summenbereich)
COUNTIF
ZÄHLENWENN(Bereich, Bedingung)
SUMME
SUMME(Summenbereich, Bedingungsbereich1, Bedingung1, Bedingungsbereich2, Bedingung2,…)
ZÄHLER
ZÄHLENWENNS(Bedingungsbereich1, Bedingung1, Bedingungsbereich2, Bedingung2,…)
Unten finden Sie ein Beispiel für jede Funktion.
Um nur die Zahlen in unserem Bereich B2 bis B7 für Zahlen größer als 12 zu addieren, verwenden wir die Funktion SUMMEWENN und diese Formel:
=SUMMEWENN(B2:B7,">12")
Das Ergebnis ist 31, da die Formel 16 und 15 addiert hat, die einzigen beiden Zahlen im Datensatz, die größer als 12 sind.
Im folgenden Beispiel zählen wir Zellen mit Werten kleiner als 12 im selben Bereich, B2 bis B7, mit der folgenden Formel:
=ZÄHLENWENN(B2:B7,"<12")
Das Ergebnis dieser Formel ist 4, da dies die Anzahl der Zellen im Bereich mit Werten kleiner als 12 ist.
Nehmen wir es mit SUMME auf und verwenden zwei Bedingungen. Um die Zahlen in den Zellen B2 bis B7 nur für diejenigen hinzuzufügen, die unseren Newsletter (Y) in den Zellen C2 bis C7 abonniert haben und deren Stadt San Diego in den Zellen D2 bis D7 ist, verwenden wir die folgende Formel:
=SUMIFS(B2:B7,C2:C7,"Y",D2:D7,"San Diego")
Das Ergebnis dieser Formel ist 19, da nur zwei Kunden ein J für den Newsletter und ein San Diego für die Stadt haben. Bill Brown hat 11 und Sue Smith hat 8 für insgesamt 19.
Für ein Beispiel mit der COUNTIFS-Funktion zählen wir die Anzahl der Zellen mit den gleichen zwei oben beschriebenen Bedingungen. Wir zählen die Anzahl der Kunden, die den Newsletter abonniert haben (Y) und ihre Stadt San Diego.
=ZÄHLENWENNS(C2:C7,"Y",D2:D7,"San Diego")
Wie erwartet war die Punktzahl 2 für Bill Brown und Sue Smith, San Diegos einziges Newsletter-Abonnement.
4. Testdaten unter verschiedenen Bedingungen: IFS
Die IF-Funktion von Excel ist ein leistungsstarkes Tool zum Testen von Daten anhand von Bedingungen. Sie können es beispielsweise verwenden, um eine Buchstabennote für die numerische Punktzahl eines Schülers oder "Ja" anzuzeigen, wenn der Verkäufer genügend Einnahmen erzielt hat, um seinen Bonus zu verdienen, und "Nein", wenn dies nicht der Fall ist.
Das Problem mit der IF-Funktion besteht darin, dass Sie alle IF-Anweisungen miteinander kombinieren müssen, um Ihre Daten anhand mehrerer Kriterien zu testen. Um diese Gruppenverwirrung zu beheben, verwenden Sie die IFS-Funktion.
Ähnlich wie oben bei SUMMEN und ZÄHLENWENNS können Sie mit IFS mehrere Kriterien auf klare, leicht lesbare Weise zu Ihrer Formel hinzufügen.
Die Syntax ist IFS(test1, if_test1_true, test2, if_test2_true, ...), wobei Sie bis zu 127 verschiedene Benchmarks testen können.
Anhand eines unserer Beispielszenarien haben wir einfach angefangen, uns auf Belohnungen zu beziehen. Wenn der Betrag in Zelle B2 größer als 20000 ist, wird angezeigt "Ja". Wenn nicht, zeigen "Nein". Die Formel wird:
=IFS(B2>20000,"Ja",B2<20000,"Nein")
Wenn im ersten Test der Wert in B2 größer als 20000 ist, lautet das Ergebnis „ja“. Wenn im zweiten Test der Wert in B2 kleiner als 20000 ist, ist das Ergebnis wahr "Nein".
Glücklicherweise erhält unser Verkäufer seine Belohnung. Da es einen Umsatz von 21000 hat, wird es eingetragen "نعم" in der Bonusspalte.
Kopieren Sie die Formel, wenn Sie eine Liste wie in unserem Beispiel haben. Ziehen Sie das Ausfüllkästchen in der unteren rechten Ecke der Zelle zu den verbleibenden Zellen anderer Verkäufer. Die Formel wird automatisch aktualisiert, um die unterschiedlichen Zellbezüge aufzunehmen.
Als weiteres Beispiel von IFS haben wir eine Liste der Diensthabenden für jeden Wochentag. Wenn der aktuelle Tag in Zelle D2 angezeigt wird, wird der Name des entsprechenden Managers in Zelle E2 angezeigt. Die Formel wird:
=IFS(D2="Monday",B2,D2="Tuesday",B3,D2="Wednesday",B4,D2="Thursday",B5,D2="Friday",B6)
Dies bedeutet, dass, wenn der Wert in Zelle D2 Montag ist, der Name in Zelle B2 angezeigt wird, wenn der Wert in Zelle D2 Dienstag ist, der Name in Zelle B3 angezeigt wird, und wenn der Wert in Zelle D2 Mittwoch ist, der Name in Zelle angezeigt wird B3 usw.
Wenn Sie den Wochentag in Zelle D2 ändern, wird der entsprechende Name des diensthabenden Managers in Zelle E2 angezeigt.
Mit einem kleinen Arbeitsaufwand beim Erstellen der Formel im Voraus können Sie die Früchte einer gewissen Automatisierung in Ihrer Excel-Tabelle ernten.
5. Filtern nach mehreren Kriterien: filtern
Als Excel-Benutzer wissen Sie wahrscheinlich, dass die Anwendung über eine integrierte Filterfunktion verfügt. Wenn Sie Ihre Daten jedoch anhand einer Bedingung oder sogar mehrerer Kriterien filtern möchten, müssen Sie die Filterfunktion verwenden. Diese flexible Funktionalität und ihre Argumente können Ihnen dabei helfen, große Datensätze in nur wenigen Minuten einzugrenzen.
Die Syntax der Funktion lautet FILTER(range, range=criteria, if_empty), wobei nur die ersten beiden Argumente des Datensatzes plus die Kriterien und sein Containerbereich benötigt werden. Das dritte Argument ist nützlich, wenn Sie etwas Bestimmtes zurückgeben möchten, wenn die Formel kein Nullergebnis zurückgibt, z "Keine Daten verfügbar" أو "Nichts".
Es ist wichtig zu wissen, wie die FILTER-Funktion zuerst mit einer einzelnen Bedingung funktioniert, bevor Sie mehrere Kriterien hinzufügen. Beispielsweise können Sie die Daten in den Zellen A2 bis C9 nach Harold Hill filtern, die sich in B2 bis B9 befinden. Die Formel dafür lautet:
=FILTER(A2:C9,B2:B9="Harold Hill")
Wir haben zwei Ergebnisse für Harold Hill. einfach richtig?
Fügen wir der Formel weitere Terme hinzu. Mehrere Kriterien werden mit einem Sternchen (*) für UND und einem Pluszeichen (+) für ODER hinzugefügt.
Zum Beispiel filtern wir beides Harold Hill و Elektronik mit folgender Formel:
=FILTER(A2:C9,(B2:B9="Harold Hill")*(A2:A9="Electronics"))
Um die Formel zu teilen, ist A2:C9 der Datensatz, B2: B9 = "Harold Hill" ist die erste Bedingung, und das Sternchen steht für UND und A2: A9 = „Elektronik“ ist die zweite Bedingung.
Wir haben mit unserem Filter ein Ergebnis erhalten, bei dem die Formel den beiden Bedingungen Harold Hill und Electronics entsprechen muss.
In einem anderen Beispiel können Sie nach Kleidung oder Autos filtern:
=FILTER(A2:C9,(A2:A9="Apparel")+(A2:A9="Automotive"))
Dividiert durch diese Formel ist A2:C9 der Datensatz, A2: A9 = „Bekleidung“ ist die erste Bedingung, und das Pluszeichen steht für OR und A2: A9 = „Automatik“ ist die zweite Bedingung.
Dieses Mal haben wir zwei Ergebnisse erhalten, bei denen die Formel mit einer der beiden Bedingungen übereinstimmen muss – nicht mit beiden.
Häufig gestellte Fragen
F1: Wie finde ich die Formelstruktur einer Funktion direkt in Excel?
Die Antwort: Excel bietet ein integriertes Tool zum Auffinden der benötigten Funktionen. Damit können Sie die Syntax der Syntax dieser Funktion sehen.
Wählen Sie eine leere Zelle aus, wechseln Sie zur Registerkarte Formeln und wählen Sie auf der linken Seite des Menübands Funktion einfügen aus. Geben Sie den Job in das Suchfeld oben ein und klicken Sie auf Los. Wenn Sie die gewünschte Funktion sehen, wählen Sie sie aus, um ihre Beschreibung und Syntax unten im Feld anzuzeigen.
F2: Wie kann ich eine Formel debuggen, die nicht richtig funktioniert?
Die Antwort: Wählen Sie die Formel aus, die Sie erleben, und gehen Sie zur Registerkarte Formeln. Wählen Sie „Formatauswertung“ im Abschnitt „Formatüberwachung“ der Multifunktionsleiste.
Sie sehen Ihre Formel im Fenster Formel auswerten, wobei ein Teil davon unterstrichen ist. Klicken Sie auf "Auswerten", um die Berechnung des unterstrichenen Teils anzuzeigen. Setzen Sie diesen Vorgang fort, um durch jeden Teil der Formel zum Ergebnis zu gelangen. So können Sie sehen, wie die Formel verarbeitet wird und wo der Fehler liegen könnte.
F3: Wie finde ich heraus, warum ich einen Formelfehler erhalte?
Die Antwort: Wenn Sie einen Fehler in einer von Ihnen erstellten Formel sehen, können Sie zwei Dinge tun, um weitere Informationen zu erhalten. Klicken Sie zuerst auf die Fehlerschaltfläche, die neben der Zelle angezeigt wird, und wählen Sie dann Hilfe zu diesem Fehler aus, um weitere Details von Microsoft in der angezeigten Seitenleiste zu erhalten.
Wählen Sie alternativ Berechnungsschritte anzeigen, um das oben gezeigte Formelauswertungsfenster zu öffnen. Sie sehen das Ergebnis der Berechnung und den Prozess, um dorthin zu gelangen, was helfen sollte, die Ursache des Fehlers zu ermitteln.