In diesem Artikel erfahren Sie, wie Sie mithilfe der SUMMENPRODUKT-Funktion in Excel genaue Übereinstimmungen nachschlagen.
Szenario:
In einfachen Worten, bei der Arbeit mit Datentabellen müssen wir manchmal Werte mit Groß-/Kleinschreibung in Excel suchen. Lookup-Funktionen wie SVERWEIS- oder MATCH-Funktionen finden die genaue Übereinstimmung nicht, da es sich bei diesen Funktionen nicht um Groß-/Kleinschreibung handelt. Angenommen, Sie arbeiten mit Daten, bei denen 2 Namen aufgrund der Groß-/Kleinschreibung unterschieden werden, dh Jerry & JERRY sind zwei verschiedene Vornamen. Sie können Aufgaben wie Operationen über mehrere Bereiche hinweg ausführen, indem Sie die unten erläuterte Formel verwenden.
Wie löst man das Problem?
Für dieses Problem müssen wir die SUMMENPRODUKT-Funktion & EXAKT-Funktion verwenden. Jetzt machen wir eine Formel aus der Funktion. Hier erhalten wir eine Tabelle und wir müssen Werte finden, die der genauen Übereinstimmung in der Tabelle in Excel entsprechen. Die SUMPRODUCT-Funktion gibt die SUMME der entsprechenden TRUE-Werte (als 1) zurück und ignoriert Werte, die FALSE-Werten (als 0) im zurückgegebenen Array entsprechen
Generische Formel:
| = SUMPRODUCT ( -- ( EXACT ( lookup_value , lookup_array ) ) , (return_array ) ) |
lookup_value : nachzuschlagender Wert.
lookup_array : Lookup-Array für den Lookup-Wert.
return_array : Array, zurückgegebener Wert, der dem Lookup-Array entspricht.
-- : Negation (--) char ändert Werte, TRUEs oder 1s in FALSE oder 0s und FALSE oder 0s in TRUEs oder 1s.
Beispiel:
All dies kann verwirrend sein zu verstehen. Lassen Sie uns diese Formel testen, indem wir sie im unten gezeigten Beispiel ausführen. Hier haben wir Daten mit Menge und Preis der Produkte, die an den Daten empfangen wurden. Wenn ein Produkt zweimal gekauft wird, hat es 2 Namen. Hier müssen wir die Anzahl der Artikel für alle wichtigen Artikel ermitteln. Dafür haben wir 2 Listen als empfangene Liste und das Wesentliche in einer Spalte für die Formel benötigt. Jetzt verwenden wir die folgende Formel, um die Menge der "Milch" aus der Tabelle zu erhalten.
Verwenden Sie die Formel:
| = SUMMENPRODUKT ( -- ( GENAU ( F5 , B3:B11 ) ), ( C3:C11 ) ) |
F6 : Wert in F6-Zelle suchen
B3:B11: Array nachschlagen ist Items
C3:C11: Rückgabe-Array ist Menge
-- : Negation (--) char ändert Werte, TRUEs oder 1s in FALSE oder 0s und FALSE oder 0s in TRUEs oder 1s.
Hier wird der Bereich als Zellbezug angegeben. Drücken Sie die Eingabetaste, um die Menge zu erhalten.
Wie Sie sehen, ist 58 die Menge, die dem Wert "Milch" in der Zelle B5 entspricht, nicht der "Milch" in der Zelle B9. Sie müssen nach dem Wert "Milch" suchen, wenn Sie die Menge "54" in der Zelle C9 benötigen.
Sie können den Preis, der dem Wert "Milch" entspricht, einfach mit der unten gezeigten Formel nachschlagen.
Verwenden Sie die Formel:
| = SUMMENPRODUKT ( -- ( GENAU ( F5 , B3:B11 ) ) , ( D3:D11 ) ) |
F6: Wert in F6-Zelle suchen
B3:B11: Array nachschlagen ist Items
D3:D11: Rückgabe-Array ist Preis
Hier haben wir die 58 ist der Preis, der dem Wert "Milch" in der Zelle B5 entspricht, nicht der "Milch" in der Zelle B9. Sie müssen nach dem Wert "Milk" suchen, wenn Sie den Preis "15,58" in der Zelle D9 benötigen.
Eindeutige Werte im Lookup-Array
Ebenso können Sie die eindeutigen Werte mithilfe der Formel ermitteln. Hier als Beispiel der Lookup-Wert "EGGS".
Im oben gezeigten Bild haben wir die Werte erhalten, die dieselbe Formel anpassen. Das Problem tritt jedoch auf, wenn es einen eindeutigen Wert hat und Sie nicht nach dem richtigen Nachschlagewert suchen. Wie hier mit dem Wert "Brot" in der Formel, um in der Tabelle nachzuschlagen.
Die Formel gibt 0 als Menge und Preis zurück, aber das bedeutet nicht, dass die Menge und der Preis von Brot 0 sind. Es ist nur so, dass die Formel 0 als Wert zurückgibt, wenn der gesuchte Wert nicht gefunden wird. Verwenden Sie diese Formel also nur, um die genaue Übereinstimmung zu ermitteln.
Sie können auch genaue Übereinstimmungen mit der INDEX- und MATCH-Funktion in Excel suchen. Erfahren Sie mehr darüber, wie Sie die Groß-/Kleinschreibung mit der Funktion INDEX & MATCH in Excel durchführen. Sie können auch mit den Platzhaltern in Excel nach Teilübereinstimmungen suchen.
Hier sind einige Beobachtungsnotizen, die unten gezeigt werden.
Anmerkungen:
- Die Formel funktioniert nur mit nur, um die genaue Übereinstimmung zu suchen.
- Die Funktion SUMMENPRODUKT betrachtet nicht numerische Werte als 0.
- Die Funktion SUMMENPRODUKT betrachtet den logischen Wert TRUE als 1 und False als 0.
- Das Argumentarray muss dieselbe Größe haben, sonst gibt die Funktion einen Fehler zurück.
- Die SUMPRODUCT-Funktion gibt den Wert zurück, der den TRUE-Werten im zurückgegebenen Array entspricht, nachdem einzelne Produkte in das entsprechende Array aufgenommen wurden.
- Operatoren wie gleich ( = ), kleiner gleich ( <= ), größer als ( > ) oder ungleich ( ) kann innerhalb einer angewendeten Formel nur mit Zahlen ausgeführt werden.
Ich hoffe, dieser Artikel zum Suchen nach einer genauen Übereinstimmung mit der SUMPRODUCT-Funktion in Excel ist erklärend. Weitere Artikel zum Thema Zählen von Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie es mit Ihren fristarts 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 verwenden Sie die SUMMENPRODUKT-Funktion in Excel: Gibt die SUMME nach der Multiplikation von Werten in mehreren Arrays in Excel zurück.
Verwenden Sie INDEX und MATCH, um den Wert zu suchen : Die INDEX-MATCH-Formel wird verwendet, um dynamisch und präzise einen Wert in einer gegebenen Tabelle zu suchen. Dies ist eine Alternative zur SVERWEIS-Funktion und überwindet die Unzulänglichkeiten der SVERWEIS-Funktion.
Nach OFFSET-Gruppen in Zeilen und Spalten summieren : Die Funktion OFFSET kann verwendet werden, um Zellengruppen dynamisch zu summieren. Diese Gruppen können sich an einer beliebigen Stelle im Arbeitsblatt befinden.
So rufen Sie jeden N-ten Wert in einem Bereich in Excel ab: Mit der OFFSET-Funktion von Excel können wir Werte aus abwechselnden Zeilen oder Spalten abrufen. Diese Formel verwendet die ROW-Funktion, um durch Zeilen zu wechseln, und die COLUMN-Funktion, um in Spalten zu wechseln.
So verwenden Sie die OFFSET-Funktion in Excel : Die OFFSET-Funktion ist eine leistungsstarke Excel-Funktion, die von vielen Benutzern unterschätzt wird. Aber Experten kennen die Leistungsfähigkeit der OFFSET-Funktion und wie können wir diese Funktion verwenden, um einige magische Aufgaben in der Excel-Formel zu erledigen. Hier sind die Grundlagen der OFFSET-Funktion.
So verwenden Sie Platzhalter in Excel : Zählen Sie Zellen, die mit Phrasen übereinstimmen, indem Sie die Platzhalter in Excel verwenden
populäre Artikel
50 Excel-Shortcut zur Steigerung Ihrer Produktivität : Erledigen Sie Ihre Aufgabe schneller. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
Wie benutzt manDie SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die zum Nachschlagen von Werten aus verschiedenen Bereichen und Blättern in Excel verwendet wird.
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 in Excel zu zählen. Die ZÄHLENWENN-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte mit bestimmten Bedingungen zu summieren.