In diesem Artikel erfahren Sie, wie Sie einen Preis aus einer Liste abrufen, die sowohl den Kategorie- als auch den Artikelkriterien in Excel entspricht.
Szenario:
Es ist einfach, einen Wert mit einem Kriterium in einer Tabelle zu finden, dafür könnten wir einfach SVERWEIS verwenden. Wenn Sie jedoch kein einzelnes Spaltenkriterium in Ihren Daten haben und mehrere Spaltenkriterien durchsuchen müssen, um einem Wert zu entsprechen, hilft SVERWEIS nicht.
Allgemeine Formel für Kategorie und Artikel
| =INDEX(lookup_range,MATCH(1,INDEX((item1=item_range) * (category2=category_range),0,1),0)) |
lookup_range: Dies ist der Bereich, aus dem Sie den Wert abrufen möchten.
Kriterien1, Kriterien2, Kriterien N: Dies sind die Kriterien, die Sie in Bereich1, Bereich2 und Bereich N abgleichen möchten. Sie können bis zu 270 Kriterien - Bereichspaare haben.
Range1, range2, rangeN : Dies sind die Bereiche, in denen Sie Ihre jeweiligen Kriterien erfüllen
Beispiel :
All dies kann verwirrend sein zu verstehen. Lassen Sie uns anhand eines Beispiels verstehen, wie die Funktion verwendet wird. Hier haben wir eine Datentabelle. Ich möchte den Namen des Kunden mit Datum der Buchung, Erbauer und Bereich abrufen. Hier habe ich also drei Kriterien und einen Nachschlagebereich.
Schreiben Sie diese Formel in Zelle I4 und drücken Sie die Eingabetaste.
| =INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)) |
Wir wissen bereits, wie die INDEX- und MATCH-Funktionen in EXCEL funktionieren, daher werden wir dies hier nicht erklären. Wir werden über den Trick sprechen, den wir hier verwendet haben.
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16): Der Hauptteil ist dieser. Jeder Teil dieser Anweisung gibt ein Array von true false zurück.
Wenn boolesche Werte multipliziert werden, geben sie ein Array von 0 und 1 zurück. Die Multiplikation funktioniert als UND-Operator. Hense, wenn alle Werte wahr sind, gibt es nur 1 zurück, sonst 0
(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16) Dies wird insgesamt zurückkehren
Was übersetzt in
| {0;0;0;0;0;0;0;1;0;0;0;0;0;0;0} |
INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): INDEX Funktion gibt das gleiche Array zurück ({0;0;0;0;0; 0;0;1;0;0;0;0;0;0;0}) zur MATCH-Funktion als Lookup-Array.
MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1): Die MATCH-Funktion sucht nach 1 im Array {0;0;0; 0;0;0;0;1;0;0;0;0;0;0;0}. Und gibt die Indexnummer der ersten 1 im Array zurück, die hier 8 ist.
INDEX(E2:E16,MATCH(1,INDEX((I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0,1),0)): Schließlich gibt INDEX zurück der Wert aus dem angegebenen Bereich (E2:E16) am gefundenen Index (8).
Wenn Sie konsequent STRG + SHIFT + ENTER drücken können, können Sie die innere INDEX-Funktion eliminieren. Schreiben Sie einfach diese Formel und drücken Sie STRG + UMSCHALT + EINGABETASTE.
Formel
| =INDEX(E2:E16,MATCH(1,(I1=A2:A16)*(I2=B2:B16)*(I3=C2:C16),0)) |
Generische Array-Formel für die Suche nach mehreren Kriterien
| =INDEX(lookup_range,MATCH(1,(criteria1 =range1)*(criteria2=range2)*(criteriaN=rangeN),0)) |
Hier sind alle Beobachtungsnotizen mit der Formel in Excel
Anmerkungen :
- Verwenden Sie Vlookup, wenn Sie ein Kriterium erfüllen müssen, dies ist gängige Praxis.
- Sie können dem Lookup-Array weitere Zeilen und Spalten hinzufügen.
- Textargumente müssen in Anführungszeichen ("") angegeben werden.
- Die SVERWEIS-Tabelle muss das lookup_array ganz links oder in der ersten Spalte haben.
- Der col-Index darf nicht 1 sein, da es sich um das Lookup-Array handelt
- Das Argument 0 wird für den genauen Übereinstimmungswert verwendet. Verwenden Sie 1 für den ungefähren Übereinstimmungswert.
- Die Funktion gibt den Fehler #N/A zurück, wenn der Lookup-Wert im Lookup-Array nicht gefunden wird. Fangen Sie also ggf. den Fehler ab.
Ich hoffe, dieser Artikel zum Abrufen eines Preises aus einer Liste, die sowohl den Kategorie- als auch den Artikelkriterien in Excel entspricht, ist erklärend. Weitere Artikel zum Berechnen von Werten und zugehörigen Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site.
So rufen Sie den neuesten Preis in Excel ab : Es ist üblich, Preise in jedem Unternehmen zu aktualisieren und die Verwendung der neuesten Preise für jeden Kauf oder Verkauf ist ein Muss. Um den aktuellen Preis aus einer Liste in Excel abzurufen, verwenden wir die LOOKUP-Funktion. Die LOOKUP-Funktion ruft den neuesten Preis ab.
SVERWEIS-Funktion zum Berechnen der Note in Excel : Um die Noten zu berechnen, sind IF und IFS nicht die einzigen Funktionen, die Sie verwenden können. Der SVERWEIS ist für solche bedingten Berechnungen effizienter und dynamischer. Um Noten mit SVERWEIS zu berechnen, können wir diese Formel verwenden.
17 Dinge über Excel SVERWEIS : SVERWEIS wird am häufigsten zum Abrufen übereinstimmender Werte verwendet, aber SVERWEIS kann noch viel mehr. Hier sind 17 Dinge über SVERWEIS, die Sie wissen sollten, um sie effektiv zu nutzen.
LOOKUP den ersten Text aus einer Liste in Excel : Die SVERWEIS-Funktion funktioniert gut mit Platzhalterzeichen. Wir können dies verwenden, um den ersten Textwert aus einer bestimmten Liste in Excel zu extrahieren. Hier ist die generische Formel.
LOOKUP-Datum mit letztem Wert in der Liste : Um das Datum abzurufen, das den letzten Wert enthält, verwenden wir die LOOKUP-Funktion. Diese Funktion sucht nach der Zelle, die den letzten Wert in einem Vektor enthält, und verwendet dann diese Referenz, um das Datum zurückzugeben.
Populäre Artikel :
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgaben in Excel schneller. Diese Verknüpfungen helfen Ihnen, Ihre Arbeitseffizienz in Excel zu steigern.
So verwenden Sie die SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.