☰

Summe der VLookups für jeden Wert in einer Zeile

tag-icon • tag-icon microsoft-excel worksheet-function spreadsheet google-spreadsheets
Summe der VLookups für jeden Wert in einer Zeile

Es sollte eine Möglichkeit geben, dies zu tun, und dennoch habe ich Schwierigkeiten ...

==Die Daten==

Blatt1

        A           B
+---------------+-------+
| Fishy_Product | Price |
+---------------+-------+
| Cod           | $9.99 |
| Trout         | $8.99 |
| Haddock       | $6.99 |
| Kippers       | $3.99 |
+---------------+-------+

Tabelle 2

       A            B         C       D        E
+------------+-------------+-----+--------+---------+
|            | Total Spend | Cod | Trout  | Haddock |
+------------+-------------+-----+--------+---------+
| Customer 1 | ?           |   1 |        |       3 |
| Customer 2 | ?           |     |      2 |         |
| Customer 3 | ?           |   2 |      2 |       2 |
+------------+-------------+-----+--------+---------+

==Die Herausforderung==

Für jeden Kunden muss ich berechnenGesamtausgabenindem ich 'Käufe' in Tabelle2 mit 'Produkt/Preis' in Tabelle1 multipliziere. Ich könnte zum Beispiel berechnenGesamtausgabenfür Kunde 1 etwa wie: =(C2*vlookup(c1,'Sheet1!A:B,2,0))+(D2*vlookup(D1,'Sheet1!A:B,2,0))+(E2*vlookup(E1,'Sheet1!A:B,2,0))usw.

ABERZu Sheet1 werden ständig neue Produkte hinzugefügt.

Sobald „Kippers“ als Spalte F hinzugefügt wird, muss ich manuell zurückgehen und die gesamte Formel aktualisieren. (Beachten Sie, dass jedes Mal, wenn ein neues Produkt zu Blatt1 hinzugefügt wird, es aufgrund der verwendeten Formel automatisch als neue Spalte in Blatt2 angezeigt wird.) Abgesehen von meiner persönlichen Faulheit besteht die Sorge darin, dass ich möglicherweise nicht erkenne, wann Aktualisierungen erforderlich sind, und mit falschen Gesamtwerten arbeite.

Gibt es eine Möglichkeit zu berechnenGesamtausgabenohne die Formel jedes Mal manuell aktualisieren zu müssen, wenn ein neues Element/eine neue Spalte hinzugefügt wird?

Vielen Dank im Voraus an alle, die mir dabei helfen könnten.

PS Ich sollte hinzufügen, dass ich in Google Sheets arbeite

Antwort1

SUMPRODUCT()Verwenden Sie für Ihre Berechnungen einfach :

Bildbeschreibung hier eingeben

Formel in B8:

=SUMPRODUCT((A$2:A$5=C$7:E$7)*(B$2:B$5*C8:E8))

Ziehen Sie nach unten, aber beachten Sie, dass es nicht automatisch aktualisiert wird, wenn Produkte hinzugefügt werden.

Zur Klarstellung: Die Reihenfolge der Produkte ist irrelevant:

Bildbeschreibung hier eingeben

Die gleichen Ergebnisse.

Antwort2

Mit kleinen Änderungen in der Datenausrichtung kann dieses Problem gelöst werden:

:Vorbehalt:

Die gezeigte Methode funktioniert sowohl mit Google Sheet als auch mit Excel.

Bildbeschreibung hier eingeben

  • Formel in Zelle Blatt 2 H19 und nach unten ausfüllen:

     =IFERROR(SUMPRODUCT(VLOOKUP(Sheet1!G13,$G$19:$K$22,{3,4,5},FALSE))*Sheet1!H13,"")

Achtung

Wenn SUMPRODUCT nur ein Argument hat, verhält es sich ähnlich wie SUM. Wenn SUMPRODUCT jedoch mehrere Argumente hat, gibt es die Summe des Produkts seiner Argumente zurück. Hier wird mit einem einzelnen Argument gearbeitet.

{3,4,5} sind Spalten I, J und K im Datenbereich $G$19:$K$22 und sind anpassbar.

Passen Sie die Zellbezüge in der Formel nach Bedarf an.

Antwort3

Sie wählen einen großen Bereich für die VLookup-Tabelle aus, um alle Elemente unterzubringen. Nehmen wir beispielsweise 5000 Zeilen. Jetzt sortieren Sie jedes Mal, wenn neue Elemente hinzugefügt werden, die beiden Spalten A und B erneut nach A, die Elemente enthalten. Sobald Sie SORTIEREN, verhält es sich korrekt, also kein Problem.

Danke.

verwandte Informationen