Bilde Summe nur für angegebene Referenznummer oder Zeile

Forumthread: Bilde Summe nur für angegebene Referenznummer oder Zeile

05.11.2024 14:58:04

Roman

Hallo zusammen,

ich bin neu hier und habe daher noch nicht alle Forumkniffe (Formatierungen, ...) drauf und bitte das vorab zu entschuldigen.

Folgende Situation bereitet mir gerade Kopfzerbrechen:
ich habe eine Liste in der Form:


    A      B        C           D          E

1   #    Stk.     Betrag     Referenz    Bilanz

2   1      10    -10,00 €

3   2      10    -20,00 €

4   3       5     30,00 €      1            ?

5   4      10    -40,00 €

6   5      10    -50,00 €

7   6      10     70,00 €      4            ?

8   7       5     80,00 €      5            ?

9   8      10     90,00 €      1,5          ?

Die Liste stellt stark vereinfacht eine kleine Börsenhistorie da; negative Werte sind der Kaufwert; positive die Verkaufswerte. Die Referenz verbindet Verkäufe mit Käufen anhand der manuellen Nummerierung. #3 könnte das Guthaben/der Gewinn von 30 Euro bei 10 Euro Einsatz aus #1 sein. Durch Teilverkäufe und summierte Käufe können die Referenzen sowohl aus mehreren Angaben bestehen (#8 wäre ein Verkaufserlös aus den Teilkäufen #1 und #5), aber auch mehrfach vorkommen (in #3 wurde beispielsweise nur die Hälfte aus #1 verkauft und in #8 die andere Hälfte).

Was ich möchte ist das Aufsummieren der Beträge aus Spalte Betrag sowie aller angegebenen Beträge aus den referenzierten Zeilen. Das Ergebnis gehört in die Spalte Bilanz. Mein Hauptproblem ist dabei das Zerlegen der referenzierten Nummern (Trenner muss nicht das Komma sein) und dynamische Bilden einer Summenformel, so dass z.B.


E4=C4+C2

E9=C9+C2+C6

Wären die Referenzen immer nur eine Zahl, könnte ich mit SVERWEIS den stets 2. Summanden auslesen, aber hier sind noch eine Zeichenkettenfunkion sowie das dynamische Generieren weiterer Summanden erforderlich, wo mir noch ein Ansatz fehlt; womöglich ein Split des Strings auf mehrere Spalten und dann per Teilfunktion oder so was bauen...
Hat dazu jemand eine Idee? Kann das ohne Makro funktionieren?

Ich bin für jede Hilfe sehr dankbar!

Viele Grüße
Roman

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Bilde Summe nur für angegebene Referenznummer oder Zeile

05.11.2024 17:30:51

Yal

Moin Roman,

ich habe deine Liste in einer Tabelle umgewandelt: Menü "Einfügen", "Tabelle".
Dann ist die Formel in Spalte Bilanz:
=[@Betrag]+SUMMEWENN(['#];[@Referenz];[Betrag])

Wenn man diese Ergebnis nur in den Zeilen haben möchte, wo eine Referenz vorliegt, muss noch einen WENN dazu.

VG
Yal

Doch nicht so einfach

05.11.2024 18:03:43

Yal

Hallo,

ich habe den "1,5" übersehen.
Hier einen Weg über Power Query.

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

Pflege in der blauen Tabelle, Ergebnis in der grünen, die mit Rechtsklick und "Aktualisieren" aktualisiert wird.

VG
Yal

AW: Doch nicht so einfach

07.11.2024 10:03:42

Roman

Hallo Yal,

besten Dank für deinen Vorschlag. Ohne Scripting geht das dann offenbar doch nicht (so einfach).
Ich habe mir daher einen anderen Weg gebaut, und zwar einen, bei dem ich gar keine "Referenzen" benutze (die ohnehin aufwendig händisch zu ermitteln waren), sondern nur noch die jeweils vorhandene Stückzahl der Aktien mitrechne.
Dadurch ergibt sich:


     A              B         C            D             E

1 Transaktion    Stück     WKN/ISIN    Bestand   Änderung_Transaktion

2    Kauf           10       DE123         10              10

3    Kauf           10       US234         10              10

4    Kauf            5       DE123         15               5

5    Verkauf         3       DE123         12              -3

6    Verkauf        10       US234          0             -10

7    Verkauf        12       DE123          0             -12

In einer Zusatzspalte lasse ich die Stückzahl bei Kauf als Positivwert, bei Verkauf als Negativwert:


E2:=WENN(A2="Kauf";B2;B2*(-1))

Für den Bestand nehme ich SUMMEWENN:


D2:=SUMMEWENN($C$2:$C2;C2;$E$2:$E2)

Beide Formeln einfach nach unten ziehen und fertig.
So kann ich beliebig Aktien ein- und ausbuchen und sollte ganz zum Schluss für alle WKNs stets wieder auf 0 kommen :-).
Das ganze mit Datum und Kurswerten versehen ergibt dann eine ganz brauchbare Orderhistorie.

Viele Grüße
Roman

AW: Doch nicht so einfach

07.11.2024 10:35:46

Yal

Hallo Roman,

ja, sieht besser aus. Eigentlich ist es nichts anderes als ein bewertetes Lagerbestand.
Ein Bestand ist nichts anderes als die Summe der Eingänge (positiv) und Ausgänge (negativ) zu einem gegebenen Zeitpunkt (vom Anfang bis). Das kann man ganz gut mit einer Pivottable machen (dann hat man keine Formel mehr). Somit kannst Du den Bestand in Stückzahl und Gesamtwert auswerten. Der Wert pro Stück ist dann Gesamtwert Bestand durch Stückzahl Bestand.

VG
Yal

AW: Bilde Summe nur für angegebene Referenznummer oder Zeile

05.11.2024 17:39:42

Rolf

Hallo Roman,

in Deiner Version kannst Du die Spalte D z.B. mit XMLFILTERN aufsplitten.
Wenn ich Dich richtig verstehe, dann
teste mal in E3: (Formel als Matrixformel eintragen Strg+Shift+Enter)

=$C3+WENNFEHLER(SUMME(INDEX($C$3:$C$9;XMLFILTERN(""&WECHSELN($D3;",";"")&"";"//y")));0)

und runterziehen.

Deinen letzten Teil mit SVERWEIS und dem stets 2. Summanden hab ich nicht kapiert.

Gruß Rolf

AW: Bilde Summe nur für angegebene Referenznummer oder Zeile

05.11.2024 18:04:09

Rudi Maintaire

Hallo,
F2: =GLÄTTEN(TEIL(WECHSELN($D2;",";WIEDERHOLEN(" ";99));SPALTE(A$2)*99-98;99))
und so weit nach rechts kopieren, wie Referenzen vorkommen können. In der Formel F:H

E2: =SUMME(C2;SUMMEWENN($A$2:$A$9;F2:H2;$C$2:$C$9))
und nach unten kopieren. Evtl. in deiner Version als Matrixformel.

Gruß
Rudi

Beliebteste Forumthreads (12 Monate)

Bilde Summe nur für angegebene Referenznummer oder Zeile

Forumthread: Bilde Summe nur für angegebene Referenznummer oder Zeile

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread