Angenommen, Sie haben eine Liste von Produkten mit ihrem Preis in einer Excel-Tabelle. Jetzt müssen Sie Bundles erstellen, die verschiedene Produkte aus verfügbaren Artikeln enthalten können. Jetzt möchten Sie am Ende den Gesamtpreis jedes Pakets berechnen. Einmal können Sie dies manuell tun, aber wenn dies Ihre regelmäßige Aufgabe ist, ist es besser, diese Aufgabe mit einigen eleganten Formeln zu automatisieren. Und dafür ist dieser Artikel. Wir lernen, wie Sie den Preis von Bundles oder Artikelgruppen nach einer Formel berechnen.
Generische Formel:
| =SUMMENPRODUKT(price_range,--(check_range="y")) |
Preisklasse:Es ist der Bereich, der den Preis der Produkte enthält.
check_range: Es ist der Bereich, in dem wir unseren Scheck platzieren wollen. Wenn ein Produkt Teil des Bündels ist, setzen wir y in den Querschnitt von Bündel und Produkt.
"ja"= Dies ist der Scheck, den wir setzen, um ein Produkt in das Paket aufzunehmen.
Lassen Sie uns ein Beispiel haben, um das Konzept zu klären.
Beispiel: Erstellen Sie eine Formel für die Preisfindung von Paketen in Excel.
Wir nehmen das gleiche Szenario, das wir am Anfang besprochen haben. Wir haben die Tabelle im Bereich B2:F9 vorbereitet. Lassen Sie uns die Variablen identifizieren, die wir benötigen.
Preisklasse:Die Preisspanne ist C2:C9. Da es fest ist, können wir entweder den Bereich benannt oder den absoluten Bezug davon verwenden. In diesem Beispiel verwende ich die absolute Referenz $C$2:$C$9.
Prüfbereich:Dies ist der Bereich, der Schecks enthält (die Bündelspalte). Sie sind D3:D9, E3:E9 und F3:f9.
Setzen wir diese Werte in die generische Formel ein.
Schreiben Sie diese Formel in D10, um den Paketpreis zu berechnen.
| =SUMMENPRODUKT($C$3:$C$9,--(D3:D9="y")) |
Drücke Enter. Sie haben die Bündelkosten von Bündel 1 in Zelle D10 berechnet. Kopieren Sie diese Formel in benachbarte Zellen, um den Preis aller Bündel zu berechnen.
Wie funktioniert es?
Die Formel funktioniert von innen nach außen. So zuerst --(D3:D9="y") wird gelöst.
Dies gibt ein Array von 1 und 0 als zurück. 1 für jedes y und 0 alles andere im Bereich D3:D9.
| {1;1;0;1;0;1;0} |
Als nächstes wird $C$3:$C$9 in ein Array umgewandelt, das den Preis jedes Artikels/Produkts enthält.
| {100;200;20;10;12;15;25} |
Das hat jetzt die SUMMENPRODUKT-Funktion in sich.
| =SUMMENPRODUKT({100;200;20;10;12;15;25},{1;1;0;1;0;1;0}) |
Jetzt multipliziert die Funktion SUMPRODUCT jeden Wert in einem Array mit demselben indizierten Array in einem anderen Array und summiert schließlich diese Werte. Dies bedeutet, dass jeder Preis, der in einem anderen Array mit 0 übereinstimmt, in 0 umgewandelt wird. {100;200;0;10;0;15;0}. Nun wird dieses Array summiert. Das ergibt 325 für Bündel 1. Dasselbe wird für alle Bündel gemacht.
Alternativformel:
Die alternative Formel ist natürlich die SUMIF- und SUMIFS-Funktion.
| =SUMIF(D3:D9,"y",$C$3:$C$9) |
und
| =SUMIFS($C$3:$C$9,D3:D9,"y") |
Dies sind die klassischen Antworten, aber die SUMPRODUCT-Formel ist auch schneller und ausgefallener.
Also ja, Leute, so können Sie den Preis des Pakets in Excel einfach berechnen. Ich hoffe, es war erklärend genug. Wenn ich einen Punkt übersehen habe oder Sie Zweifel an diesem Artikel oder anderen Zweifeln im Zusammenhang mit Excel haben, fragen Sie ihn im Kommentarbereich unten.
Zählen Sie die Gesamtzahl der Übereinstimmungen in zwei Bereichen in Excel | Erfahren Sie, wie Sie mit der SUMPROUDCT-Funktion die Gesamtzahl der Übereinstimmungen in zwei Bereichen zählen.
SUMIFS mit UND-ODER-Logik | Die SUMIFS können auch mit ODER-Logik verwendet werden. Die von SUMIFS verwendete Standardlogik ist UND-Logik.
SUMMENPRODUKT mit IF-Logik | Erfahren Sie, wie Sie SUMPRODUCT mit IF-Logik verwenden, ohne die IF-Funktion in der Formel zu verwenden.
50 Excel-Kurzbefehle zur Steigerung Ihrer Produktivität | Werden Sie schneller bei Ihrer Aufgabe. Mit diesen 50 Tastenkombinationen arbeiten Sie noch schneller mit Excel.
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.
ZÄHLENWENN in Excel 2016 | Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um einen bestimmten Wert zu zählen. Die Countif-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 unter bestimmten Bedingungen zusammenzufassen.