Microsoft Excel

Herbers Excel/VBA-Archiv

Bedingtes summieren

Betrifft: Bedingtes summieren von: Basti
Geschrieben am: 07.09.2020 09:18:17

Hallo alle zusammen,

anbei hab ich euch eine Beispieldatei hochgeladen bei der ich einfach nicht weiter komme. Die Logik im Kopf funktioniert so:
In Zelle F1 soll die Order No. aus Spalte A ausgegeben werden wenn ein bestimmter summierter Wert in Spalte E überschritten wird. Wie so oft funktioniert es im Kopf ganz einfach er sollte in Spalte E einfach immer weiter addieren bis der Grenzwert überschritten wird und dann die dazugehörige Order No. aus Spalte A auslesen und in Zelle F1 ausgeben. Aber in der Praxis sieht die Sache dann leider nicht so einfach aus. Am liebsten wäre mir eine Lösung rein in Excel, sollte sich sowas aber nur mit VBA realisieren lassen würde ich da auch nicht Nein sagen.

Ich hoffe Ihr könnt verstehen was ich meine und mir vielleicht etwas weiter helfen

https://www.herber.de/bbs/user/140081.xlsx

Liebe Grüße Basti

Betrifft: Summe bis ein Betrag überschritten wird
von: WF
Geschrieben am: 07.09.2020 10:07:17

Hi,

Arrayformel - Dein "rot" beginnt in dieser Zeile:
{=MAX((SUMMEWENN(A3:A99;"<="&A3:A99;E3:E99)<=B1)*A3:A99)}
Salut WF

Betrifft: AW: Summe bis ein Betrag überschritten wird
von: Basti
Geschrieben am: 07.09.2020 10:29:42

Hi,
schon mal danke für den Ansatz WF, leider nimmt er die Arrayformel die ich in F1 eingefügt habe nicht als Dynamisch, denn wenn sich nun die Werte in Spalte E ändern hat er immer noch den zuletzt ermittelten Wert der Formel und behält diesen. Mein "rot" ist ja nur als kleine Hilfestellung über eine bedingte Formatierung entstanden. Oder stelle ich mich nun grade einfach zu blöd an und habe das sprichwörtliche "Brett vorm Kopf". Kurze Aufschlüsselung: in F1 möchte ich die Bestellnummer aus Spalte A eingetragen bekommen wenn mit der Wertsumme der Aufträge aus Spalte E der Grenzwert in diesem Beispiel 10.000,00 überschritten wird. Die Arrayformel gibt mir allerdings nur die Zeile in der der Wert ermittelt war wieder als 20 als der Wert in Zeile 20 erreicht wurde.
Ich hoffe man kann verstehen was ich hier zu Bildschirm gebracht habe.

LG Basti

Betrifft: Da hast Du Recht
von: WF
Geschrieben am: 07.09.2020 10:50:17

Hi,

die Crux sind die Leerzellen in Spalte A
Mal auf die Schnelle: schreib in Hilfsspalte R ab R3 die Zahlen 3 - 4 - 5 usw. runter
{=MAX((SUMMEWENN(R3:R99;"<="&R3:R99;E3:E99)<=B1)*R3:R99)}

WF

Betrifft: AW: Da hast Du Recht
von: Basti
Geschrieben am: 07.09.2020 11:00:48

Hi WF,

gut mit der Hilfspalte klappt es nun schon mal das er es aktualisiert allerdings wäre es jetzt noch schön wenn er mir in F1 nicht die Zeilennummer wenn es erreicht ist ausgibt sondern die Bestellnummer die in Spalte A der jeweiligen Zeile steht. Damit wäre es dann perfekt.

Aber das hat mir schon mal sehr geholfen Ich tüftle auch schon die ganze Zeit daran rum aber es ist einfach schon zu lange her das ich mich mit Excel so intensiv beschäftigt habe, in der heutigen Zeit wird einem das meiste ja schon von sämtlichen anderen Programmen abgenommen.

LG Basti

Betrifft: das ist ja nun einfach
von: WF
Geschrieben am: 07.09.2020 11:25:44

{=MAX(A1:INDIREKT("A"&MAX((SUMMEWENN(R3:R99;"<="&R3:R99;E3:E99)<=B1)*R3:R99)))}

WF

Betrifft: AW: eine Lösungsformel ohne Hilfsspalte ...
von: neopa C
Geschrieben am: 07.09.2020 11:49:15

Hallo Basti,

... wäre dann z.B. mit Matrixformeleingabeabschluss wie folgt möglich

{=VERWEIS(999;A1:INDEX(A:A;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(E3;;;ZEILE(A1:A99)))<=B1);ZEILE(A3:A99)))))}

Gruß Werner
.. , - ...

Betrifft: AW: Bedingtes summieren
von: Basti
Geschrieben am: 08.09.2020 09:18:54

Hallo alle zusammen,

da bin Ich auch schon wieder, die Lösungen gestern waren schon überragend und ich wollte mich dafür nochmal bedanken aber nach dem ersten Gestern hab ich festgestellt das ich in meiner Überlegung etwas entscheidendes vergessen habe. und zwar Spalte "M" die "received Payments" gegen zu rechnen um das "Creditlimit" zu beeinflussen. Vielleicht mag mir ja jemand noch mal helfen das einzubauen in die Formel von

SF:
{=MAX(A1:INDIREKT("A"&MAX((SUMMEWENN(R3:R99;"<="&R3:R99;E3:E99)<=B1)*R3:R99)))}

oder von
Neopa:
{=VERWEIS(999;A1:INDEX(A:A;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(E3;;;ZEILE(A1:A99)))<=B1); ZEILE(A3:A99))))) }
Und in Zelle H1 sollte nach Möglichkeit der ausgelesene Wert um den das "Creditlimit" mit der "Order" überstiegen wird eingetragen werden.

anbei stelle ich euch nochmal die Excel zur Verfügung.

https://www.herber.de/bbs/user/140111.xlsx

Liebe Grüße und noch Mal ganz Viel Dank
Basti

Betrifft: AW: Dein neues Anliegen interpretiere ich ...
von: neopa C
Geschrieben am: 08.09.2020 10:18:28

Hallo Basti,

... so, dass in meiner Formel lediglich der Wert aus M4 dem Wert in B1 dazu addiert werden muss.

Also so: =VERWEIS(... <=B1+M4)...)

Oder gibt es mehrere Werte in Spalte M?

Gruß Werner
.. , - ...

Betrifft: AW: Dein neues Anliegen interpretiere ich ...
von: Basti
Geschrieben am: 08.09.2020 10:22:56

Hallo Werner,

Der Grundgedanke ist richtig aber natürlich wird es in Spalte M wohl mehrere Werte geben. bzgl. Teilzahlung usw. mit nur einem Wert wäre es relativ einfach und die Werte sollten dann nicht dazu addiert werden sondern subtrahiert, da sich durch eine beglichene Rechnung natürlich das Kreditlimit wieder erhöht.

trotzdem schon mal danke.

LG Basti

Betrifft: AW: dazu nachgefragt ...
von: neopa C
Geschrieben am: 08.09.2020 10:33:48

Hallo Basti

... wieso hast Du Deine bedingte Formatierung nach Order Nr 18 anzeigen lassen. Dies wäre nur der Fall, wenn Du den Wert in Spalte M zu B1 addierst.

Wenn es aber subtrahiert werden soll, dann ist jeder Wert in Spalte M als Minus-Wert zu berücksichtigen?

Gruß Werner
.. , - ...

Betrifft: AW: dazu nachgefragt ...
von: Basti
Geschrieben am: 08.09.2020 10:40:10

Hallo Werner,

Ja der Wert in Spalte M ist grundlegend negativ zu betrachten da es sich um eingehende Zahlungen zu den in Spalte J befindlichen Rechnungen handelt und somit natürlich die Differenz bis zum erreichen des Limits wieder erhöhen.

Und ja ich bin im Moment wie gesagt noch im Aufbau und tüfteln war im ersten Moment froh das ich mich überhaupt wieder an halbwegs etwas zu den Möglichkeiten von Excel erinnert habe.

Gruß Basti

Betrifft: AW: in dem Fall ...
von: neopa C
Geschrieben am: 08.09.2020 10:54:20

Hallo Basti,

... würde ich der Einfachheit halber doch eine Hilfsspalte einführen.
In S3: =[@[Total Orders]]-[@[received payments]]

Und dann in H1 einfach nachfolgende kleine Formeländerung

=VERWEIS(999;A1:INDEX(A:A;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(S3;;;ZEILE(A1:A99))) <=B1); ZEILE(A3:A99))))) }
Gruß Werner
.. , - ...

Betrifft: AW: in dem Fall ...
von: Basti
Geschrieben am: 08.09.2020 11:44:38

Hallo Werner,

da hab ich mich glaube ich etwas unklar Ausgedrückt, in Zelle H1 hätte ich gerne den Betrag ausgegeben um den Das Kreditlimit mit der vorher ermittelten Bestellung überschritten wurde.

anbei nochmal die Excel Datei mit einem laienhaften Beispiel.

https://www.herber.de/bbs/user/140117.xlsx

Liebe Grüße Basti

Betrifft: AW: das ist schon was anderes, wenn auch ...
von: neopa C
Geschrieben am: 08.09.2020 13:16:20

Hallo Basti,

... mit einem gleichen Lösungsansatz realisierbar. Nachfolgend hab ich in der Datei die Hilfsspalte R gelöscht, so dass meine Hilfsspalte nunmehr die Spalte R ist.

Dann so:

Arbeitsblatt mit dem Namen 'Tabelle1'
 H
1832,75 €
20,01 €

ZelleFormel
H1{=INDEX(E:E;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(R4;;;ZEILE(A1:A99))) <=B2); ZEILE(A4:A99)))+1)}
H2{=SUMME(E4:INDEX(E:E;MAX(WENN((TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(R4;;;ZEILE(A1:A99))) <=B2); ZEILE(A4:A99)))+1))-B2}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg


Gruß Werner
.. , - ...

Betrifft: AW: Ergänzung ...
von: neopa C
Geschrieben am: 08.09.2020 13:29:22

Hallo nochmal,

... die Formel in H1: sollte so definiert werden {=WENN(R64<B2;"";...)} und dementsprechend auch in H2: {=WENN(H1="";"";...)}

Gruß Werner
.. , - ...

Betrifft: AW: Ergänzung ...
von: Basti
Geschrieben am: 08.09.2020 13:47:09

Hallo Werner,

ganz ganz lieben Dank für Die Lösung. Das ist wirklich Spitze was da aufs Papier gebracht wurde und funktioniert Super.

Liebe Grüße Basti

Betrifft: AW: danke für die "Blumen" owT
von: neopa C
Geschrieben am: 08.09.2020 13:51:06

Gruß Werner
.. , - ...

Beiträge aus dem Excel-Forum zum Thema "Bedingtes summieren"