Summenprodukt für Matrix mit Suchbedingungen Größer als
#1
Hallo liebe Forumsmitglieder,

anbei eine Beispieldatei mit einer für mich kniffligen Aufgabe. 
In Tabelle 1 habe ich eine Spalte A mit Zonen und eine Spalte B mit Gewicht.

Dazu gibt es eine Preismatrix im anderen Tabellenblatt.

Ich möchte gerne aufgrund des Gewichtes und der Zone den richtigen Schnittpunkt in der
Preismatrix ermitteln.

Ich war nicht untätig Wink eine Summenprodukt Formel wie in C3 bereits ausgegeben, damit arbeite ich öfter und
das ist kein Problem...wenn, wie hier, der Wert in der Matrix 1:1 wiederzufinden ist. Im Beispiel habe ich die Formel
dafür in C3 schon stehen, das funktioniert natürlich recht einfach. Da der Wert in der Preismatrix immer für 100 KG gilt und
hier 1.700 KG stehen, habe ich hinten das Ergebnis durch 100 dividiert und mit dem Gewicht multipliziert. Korrektes Ergebnis.

Nun seht ihr die Fragezeichen ab C4 und darunter. Die Herausforderung ist, einen nicht eindeutig zu findenden Wert mit der
Formel zu ermitteln. 
Als Beispiel: Die Zahl 3.112 gibt es in der Preismatrix nicht als eindeutigen Wert. Hier ist die Regel, immer
wenn ein Wert erreicht wurde springt der Preis auf die höhere KG-Einheit. Also bei dem Beispiel C4 wäre der Preis für 3.112 KG
der Wert bei 4.000 KG. 

Ein Wert von 101 KG würde entsprechend auch aufgerundet auf den 200 KG Preis springen.
Unter 100 KG wird der Preis mit dem Wert aus Zeile 5 berechnet.

Ich wäre sehr dankbar, wenn jemand einen Weg kennt, wie man in der Matrix in der o.g. Logik die Formel ergänzt.
Vielleicht ist Summenprodukt da ja auch nicht mehr hilfreich und es gibt eine andere Lösung?

Vielen Dank im Voraus für eure immer guten Ideen und Hilfe.
Liebe Grüße, Excelmonsta


Angehängte Dateien
.xlsx   Summenprodukt für Matrix mit Bedingungen Größer als .xlsx (Größe: 12,48 KB / Downloads: 6)
Antworten Top
#2
Hola,
das jeweilige Gewicht erhältst du mit:
Code:
=MINWENNS(Preismatrix!$A$6:$A$23;Preismatrix!$A$6:$A$23;">="&B3)
Gruß,
steve1da
[-] Folgende(r) 1 Nutzer sagt Danke an steve1da für diesen Beitrag:
  • Excelmonsta
Antworten Top
#3
Hallo E...,

ich habs so verstanden:

=XVERWEIS(B8;Preismatrix!$A$5:$A$23;XVERWEIS(A8;Preismatrix!$B$4:$K$4;Preismatrix!$B$5:$K$23;;0;2);;1;2)*B8/100


ps. Auch die möglichen Kombinationen aus dem 5. und 6. Parameter machen den Xverweis mächtiger(bzw.häufig einfacher) als die S/Wverweise und Kombinationen aus Index und Vergleich.


Angehängte Dateien
.xlsx   Summenprodukt für Matrix mit Bedingungen Größer als .xlsx (Größe: 14,04 KB / Downloads: 2)
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





[-] Folgende(r) 1 Nutzer sagt Danke an Ego für diesen Beitrag:
  • Excelmonsta
Antworten Top
#4
Hi,

hier mal mit 3Möglichkeiten:


.xlsx   Summenprodukt für Matrix mit Bedingungen Größer als .xlsx (Größe: 14,83 KB / Downloads: 4)
Gruß

Edgar

Meine Antworten sind freiwillig und ohne Gewähr!
Über Rückmeldungen würde ich mich freuen.
[-] Folgende(r) 1 Nutzer sagt Danke an BoskoBiati für diesen Beitrag:
  • Excelmonsta
Antworten Top
#5
Hallo an euch beide,

super - das ist ja toll. Vielen Dank für die Unterstützung. 
@Steve1da: Eigentlich wollte ich tatsächlich direkt den Preis finden, der dann der von dir ermittelten KG-Grenze entspricht.
Trotzdem ist die Formel eine große Hilfe, denn ich könnte die Formel jetzt nutzen als "Hilfsspalte" und dann auf das Gewicht
mit Summenprodukt sauber den Gesamtpreis ermitteln. Von daher hilft mir das weiter. Danke.

@Ego
Die Formel ist auch super, eigentlich eine Top Lösung. Ich hatte mich erst gewundert warum die Formel bei mir nicht funktioniert,
dann habe ich festgestellt, dass der XVERWEIS erst ab Excel 2019 funktioniert. Das habe ich leider noch nicht, trotzdem superinteressant.
Den Verweis kannte ich noch nicht und der ist natürlich mächtig! Vielen Dank.

Liebe Grüße, Excelmonsta
Antworten Top
#6
Zitat:@Steve1da: Eigentlich wollte ich tatsächlich direkt den Preis finden, der dann der von dir ermittelten KG-Grenze entspricht.

Das ist mir schon klar. Das war auch eher als Hilfe gedacht, die du direkt in deine Summenprodukt Formel an Stelle von =Tabelle1!$B3 einbaust, ganz ohne Hilfsspalte.
[-] Folgende(r) 1 Nutzer sagt Danke an steve1da für diesen Beitrag:
  • Excelmonsta
Antworten Top
#7
Hallo Edgar,

super - vielen Dank. Das ist natürlich die Lösung in einer Formel. Ganz toll, habe die Formel sogar
"grob" verstanden Smile
Der XVERWEIS ist auch ein toller Verweis, der funktioniert aber leider nicht bei mir mit der Excel-Version.
Aber deine Lösung nutze ich jetzt, die ist perfekt.

Herzlichen Dank

@steve1da

Ah, alles klar - super. Habe ich jetzt verstanden Smile
Danke nochmal für die Erläuterung.

LG
Antworten Top
#8
Hallo E...,

in deinem Profil steht Office 365.
helmut

Für mich ist die Möglichkeit in Excel an Zellen und Bereichen Namen zu vergeben die wichtigste Funktionalität.
Sie macht Formeln und den VBA-code verständlicher. Für Makros gilt die Regel: "Nur über benannte Bereiche auf den Inhalt der Zellen zugreifen."
Und wofür sind Regeln da? Um nachzudenken bevor man sie bricht.





Antworten Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste