Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1624to1628
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Summewenn und Sverweis
03.06.2018 10:35:26
Lukas
Hallo zusammen,
ich würde gerne aus einer Tabelle nach bestimmten Kriterien sie Summe ziehen. Dabei muss aber ein Ursprungswert nach einem Verteilungsschlüssel verteilt werden.
Es gibt also eine Tabelle, die die Produkte A, B, C aufgelistet hat mit einem Wert. In dieser Tabelle ist nach jedem Wert ein Verteilungsschlüssel V1 und V2 angegeben, der den Wert nach Gebieten D, E, F verteilen soll.
Anhand dieser Angabe soll die Formel je nach Produkt- und Verteilungsangabe die Summe ziehen.
z. B. Produkt A und Gebiet D:
100*30/120+400*20/90=113,8888
Ich hatte an Summewenn*Sverweis als Array-Formel gedacht, aber das funktioniert nicht...
hat jemand von euch eine Idee?
https://www.herber.de/bbs/user/121937.xlsx
Vielen Dank im Vorraus und viele Grüße
Lukas

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summewenn und Sverweis
03.06.2018 14:11:49
Besserwisser
Hallo Lukas,
vielleicht geht's kürzer, aber immerhin eine Lösung.
M3: =SUMME(($B$2:$B$7=$L3)*$C$2:$C$7*MMULT(--(WENN($D$2:$D$7=MTRANS($F$3:$F$4);MTRANS(G$3:G$4/MMULT($G$3:$I$4;{1;1;1}))));{1;1}))
mit STRG/UMSCHALT/ENTER abschließen und nach unten und rechts kopieren.
Viel Spaß
Christian N.
AW: Summewenn und Sverweis
03.06.2018 14:17:51
Besserwisser
Ergänzung: hab das Feld J4 mit 0 ergänzt (anstatt LEER)
sieht ja äußerst schlank aus (gegenüber mir)
03.06.2018 15:01:42
WF
Hi,
in dieser Formel kommt aber die Spalte L vor: da steht nichts.
Das Ergebnis ist also null.
?
WF
AW: sieht ja äußerst schlank aus (gegenüber mir)
03.06.2018 15:31:11
Besserwisser
Hallo WF,
ja, Deine Formel ist recht umfangreich geworden.
Gruß
Christian
eine Bombenlegerformel
03.06.2018 14:57:03
WF
Hi,
in N3 folgende Arrayformel:
{=INDEX($H$3:$J$4;VERGLEICH(INDEX($D:$D;KKLEINSTE(WENN($B$1:$B$9=$M3;ZEILE($1:$9));1));$G$3:$G$4;0); VERGLEICH(N$2;$H$2:$J$2;0))*INDEX($C:$C;KKLEINSTE(WENN($B$1:$B$9=$M3;ZEILE($1:$9));1)) /SUMMENPRODUKT(($G$3:$G$4=INDEX($D:$D;KKLEINSTE(WENN($B$1:$B$9=$M3;ZEILE($1:$9));1)))*$H$3:$J$4) +INDEX($H$3:$J$4;VERGLEICH(INDEX($D:$D;KKLEINSTE(WENN($B$1:$B$9=$M3;ZEILE($1:$9));2));$G$3:$G$4;0); VERGLEICH(N$2;$H$2:$J$2;0))*INDEX($C:$C;KKLEINSTE(WENN($B$1:$B$9=$M3;ZEILE($1:$9));2)) /SUMMENPRODUKT(($G$3:$G$4=INDEX($D:$D;KKLEINSTE(WENN($B$1:$B$9=$M3;ZEILE($1:$9));2)))*$H$3:$J$4) }
Salut WF
Anzeige
AW: Summewenn und Sverweis
03.06.2018 15:24:04
Besserwisser
Hallo Lukas,
nun optimiert und mit richtigen Bezügen!
N3:
=SUMME(($B$2:$B$7=$M3)*$C$2:$C$7*VERWEIS($D$2:$D$7;$G$3:$G$4;H$3:H$4/MMULT(WENN($H$3:$J$4""; $H$3:$J$4;0);{1;1;1})))
STRG/UMSCHALT/Enter
Viel Spaß
Gruß
Christian N.
das passt jetzt
03.06.2018 15:44:11
WF
ich hatte mich vergalloppiert.
WF
AW: geht noch einfacher und zudem auch ohne {} ...
03.06.2018 20:15:01
neopa
Hallo Christian,
... denn es lässt sich WENN() und der Vergleich mit "" sowie dadurch auch der Matrixformeleingabeabschluss einsparen.
Folgende Formel in N3 und diese nach rechts und unten kopieren:
=SUMMENPRODUKT(($B$2:$B$7=$M3)*$C$2:$C$7*VERWEIS($D$2:$D$7;$G$3:$G$4;H$3:H$4/MMULT($H$3:$J$4+0;{1;1;1})))
Gruß Werner
.. , - ...
Anzeige
AW: geht noch einfacher und zudem auch ohne {} ...
03.06.2018 21:06:21
Besserwisser
Hallo Werner,
schön!
Gruß
Christian N.
AW: geht noch einfacher und zudem auch ohne {} ...
04.06.2018 19:26:52
Lukas
hey,
super das bringt mich schon enorm weiter.
Ist aber richtig,dass die Verteilungschlüssel alpabetisch sortiert sein müssen, oder?
In der Originaldatei habe ich ca. 70 Schlüssel, die nicht sortiert waren, da kommt dann unsinn raus. Nachdem ich sortiert habe, passte es.
wie bekomm ich es denn geregelt, wenn ein Wert kein Verteilungschlüssel hat? dann müsste in der Matrix quasi eine null stehen. Aktuell gibt die formel dann aber den nächstgelegenen/kleineren Schlüssel aus...
Gruß
Lukas
AW: zu Deinen zwei Ergänzungsfragen ...
05.06.2018 08:54:31
neopa
Hallo Lukas,
... Deine erste Frage hast Du Dir ja schon selbst beantwortet. Die VERWEIS()-Formel erfordert zwingend eine aufwärts sortierte Reihenfolge der Daten in Spalte G. Dazu sieh auch die MSO-Hilfe zur Funktion SVERWEIS().
Deine zweite Frage ist insofern etwas widersprüchlich, als dass Deine Aussage: "Aktuell gibt die Formel dann aber den nächstgelegenen/kleineren Schlüssel aus" für die Formel nur dann zutreffend ist, wenn in Spalte D ein Schlüsselwert steht, den es in der Spalte G nicht gibt und gleichzeitig in einer Sortierreihenfolge der Schlüsselwerte in Spalte G als letzter Wert stehen würde. Also z.B. "X1" aber nicht z.B. "U1"
Um den allgemeinen Fall (kein oder ein beliebiger Wert in Spalte D den es im Spaltenbereich G nicht gibt) auch noch korrekt zu berechnen kommt man wohl ohne "Kopfstände zu machen" an einer klassischen Matrixformel kaum vorbei.
Deshalb dafür folgender Matrix-Formelvorschlag:

{=SUMME(($B$2:$B$7=$M3)*$C$2:$C$7
*WENNFEHLER(VERWEIS($D$2:$D$7;$G$3:$G$4;H$3:H$4/MMULT($H$3:$J$4+0;{1;1;1}));0))}

Gruß Werner
.. , - ...
Anzeige

314 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige