Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1784to1788
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
Inhaltsverzeichnis

Summen ziehen aus einer Tabelle

Summen ziehen aus einer Tabelle
30.09.2020 16:00:25
Olli
Hallo zusammen,
ich eine Tabelle mit verschiedenen Datumsangaben (taggenau) und direkt darunter Werte (IRR Berechnung). Ich hätte gerne eine Formel mit der ich die Tabelle nach einem Jahr durchsuchen kann (unterteilt nach negativ oder positiv) und die Summe für ein Jahr erhalte.
Ich habe ein simplifiziertes Beispiel angehängt und würde dort bsp. gerne die Summe aller positiven Zahlungen 2017 erhalten.
Vielen Dank für Eure Unterstützung!
LG,
Olli
https://www.herber.de/bbs/user/140550.xlsx

28
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Summen ziehen aus einer Tabelle
30.09.2020 16:23:55
UweD
Hallo
=SUMMENPRODUKT(($C$18:$F$100>=DATWERT("01.01.2017"))*($C$18:$F$1000)*($C$19:$F$101)) 

die 100 bzw 101 sind nur Beispielhaft. Bereich so klein wie möglich halten
LG UweD
AW: oder ...
30.09.2020 16:35:33
neopa
Hallo Oli,
... etwas kürzer so:
=SUMMENPRODUKT((C19:Z99&gt0)*(B18:B98&gt0)*C19:Z99*(C18:Z98&gt="1.1.17"+0)*(C18:Z98&lt="31.12.17"+0))
Gruß Werner
.. , - ...
AW: oder ...
30.09.2020 16:59:52
UweD
den B:B Teil brauchst du nicht.
Also noch kürzer
=SUMMENPRODUKT((C19:Z99>0)*C19:Z99*(C18:Z98>="1.1.17"+0)*(C18:Z98

Anzeige
AW: doch wird schon gebraucht, denn ...
30.09.2020 17:14:54
neopa
Hallo Uwe,
... zu addierende Werte könnten die Größe einer Datumszahl des Jahres haben, welches auszuwerten ist.
Gruß Werner
.. , - ...
AW: doch wird schon gebraucht, denn ...
30.09.2020 17:58:38
Olli
Hallo,
großartig, vielen Dank!
Viele Grüße,
Olli
AW: bitteschön, doch siehe auch unten owT
30.09.2020 18:00:45
neopa
Gruß Werner
.. , - ...
Folgefrage:)
30.09.2020 19:59:42
Olli
Hallo nochmal,
ich bin noch am optimieren und frage mich, ob ich aus der anhängten Tabelle von heute Nachmittag auch eine Zahlenreihe aller Zahlungen sortiert nach Datum erstellen kann (als Formatvorlage für die XINTZINSFUSS des gesamten Portfolios) oder ob es die Möglichkeit gibt die XINTZINSFUSS auf eine Tabelle zu beziehen.
LG,
Olli
Anzeige
Mit xl365 ohne Hilfsspalten möglich, ...
01.10.2020 08:41:41
lupo1
... aber hier keine Antwort, da der Frager die fehlende Relevanz der Excelversion behauptet.
Man sollte schon immer vorsichtig sein, was man schreibt.
AW: wäre auch in älteren XL-Versionen möglich, ...
01.10.2020 09:24:55
neopa
Hallo Oli,
... mit Formeln zu realisieren, wenn es sich nicht gerade um eine Massendatenauswertung handelt.
2 Formeln sollten dann reichen. Doch unklar ist momentan noch, welche XL-Version Du wirklich einsetzt.
Gruß Werner
.. , - ...
Also ok: Für alle Versionen!
01.10.2020 11:14:56
lupo1
=INDEX(MTRANS(C18:F25);REST(ZEILE(1:16)-1;4)+1;SPALTE(A:B))
zeigt den XINTZINSFUSS-konformen Aufbau in zwei Spalten.
Dieser funktioniert sofort in XL365/XLWeb; in älteren Versionen wird er als 16x2-Array abgeschlossen.
_______________________
=XINTZINSFUSS(
INDEX(MTRANS(C18:F25);REST(ZEILE(1:16)-1;4)+1;2);
INDEX(MTRANS(C18:F25);REST(ZEILE(1:16)-1;4)+1;1))

ist die direkte Formel für den Zinsfuß (liegt bei etwas über 50%). In älteren Versionen muss diese Formel als Matrixformel abgeschlossen werden. XL365-Funktionen sind keine enthalten.
Anzeige
AW: Also ok: Für alle Versionen!
01.10.2020 17:28:49
Olli
Es ist Excel 2019. Leider klappt es nicht in der Beispieltabelle, von dem Original ganz zu Schweigen - habe einen Wert-Fehler.
AW: in Deiner XL-Version ...
01.10.2020 17:52:39
neopa
Hallo Oli,
... geht das von lupo vorgeschlagene sicherlich auch. Doch würde ich nachfolgende 2 Formeln für die Auflistung vornehmen, die ohne Matrixformelabschluss auskommt und die Daten auch gleich sortiert ausgibt.
Z.B. in AD2:
=WENNFEHLER(AGGREGAT(15;6;C$18:Z$99/(B$18:B$99&gt0)/(C$18:Z$99&gt0);ZEILE(B1));"")
und in AE2:
=WENN(AD2="";"";AGGREGAT(14;6;C$19:Z$99/(B$19:B$99=0)/(C$18:Z$98=AD2);ZÄHLENWENN(AD$2:AD2;AD2)))
beide Formeln weit genug nach unten ziehend kopieren.
Diese Datenliste kannst Du dann weiter auswerten.
Gruß Werner
.. , - ...
Anzeige
Sortierung v.XINTZINSFUSS-Daten nicht erforderlich
01.10.2020 19:12:08
lupo1
AW: mag ja sein, ...
01.10.2020 19:32:22
neopa
Hallo Lupo,
... ich hab mich noch nicht mit XINTZINSFUSS() beschäftigt und werde es auch nicht tun, da diese Funktion mich nicht interessiert.
Da Oli mehr Daten hat als in seiner Beispieldatei, wollte ich ihm aber einen Weg aufzeigen, wie er seine Daten in seiner listen kann ohne eine fixe plurale Matrixformel.
Gruß Werner
.. , - ...
Die AGGREGAT-Bomben zu empfehlen, ...
01.10.2020 19:43:38
lupo1
... ist kein guter Rat.
Der Frager sollte sich lieber dafür interessieren, wie man die bloße Umgruppierung von Daten als Array abschließt. Solange, wie er sich keine aktuelle Version leisten möchte.
Und Du solltest so fair sein, zuzugeben, dass man meine Formel genauso einzeln und als normale Formel eingeben und rechts-runter-ziehen kann, statt dass man den AGGREGAT-Hokuspokus verfolgen muss:
AA1[:AB16]: =INDEX(MTRANS(C18:F25);REST(ZEILE(A1)-1;4)+1;SPALTE(A1))
Ist doch viel kürzer ... und vor allem nur eine Formel! Du brauchst hingegen zwei.
Bei meiner direkten XINTZINSFUSS()-Formel braucht es noch nicht einmal Hilfszellen. Dass Dich XINTZINSFUSS nicht interessiert, gibt Dir nicht das Recht, Dein AGGREGAT in irgendeiner Weise hochzuloben. Es geht einzig und allein um Datenumgruppierung via INDEX.
Anzeige
richtig MTRANS($C$18:$F$25) statt MTRANS(C18:F25)
01.10.2020 19:45:01
lupo1
AW: ich hab eine sortierte Listung aufzeigt owT
01.10.2020 19:57:20
neopa
Gruß Werner
.. , - ...
War nicht gefragt. Thema verfehlt. Setzen, 6.
01.10.2020 20:01:36
lupo1
AW: offensichtlich hast Du nicht gelesen ...
01.10.2020 20:27:30
neopa
Hallo,
... dass Oli schrieb: "... frage mich, ob ich aus der angehängten Tabelle von heute Nachmittag auch eine Zahlenreihe aller Zahlungen sortiert nach Datum erstellen kann"
Gruß Werner
.. , - ...
Das rechtfertigt den AGGREGAT-Klopper nicht.
01.10.2020 20:42:58
lupo1
Und für XINTZINSFUSS ist es nicht erforderlich, da i.Ggs zu IKV() die Datümer explizit schon in der Funktion verlangt werden.
Mit kostenlosem XLWeb /GDocs sowie kostenpflichtigem xl365 steht =SORTIEREN() zur Verfügung.
Man kann die INDEX-Formeln außerdem plätten und in alten Excel-Versionen händisch sortieren. Beim nächsten Eingeben beachtet man dann einfach die korrekte Reihenfolge, was jeder normale Mensch von vornherein täte, wenn er es denn braucht.
Die größte Schwäche der 2 völlig verschiedenen AGGREGATe (i.Ggs. zu nur 1 INDEX-Formel) ist aber, dass sie inhaltlich aus irgendeinem Grunde nicht die Datensätze zusammenhängend zurückgeben könnten; einfach deshalb, weil ihre Logik auseinanderliegt.
Anzeige
AW: Sortierung v.XINTZINSFUSS-Daten nicht erforderlich
01.10.2020 20:28:43
Olli
Moin Werner,
vielen Dank, damit ist mein zweites Problem gelöst und die Datei nun hübsch!
Viele Grüße,
Olli
ja, hübsch. Und von Dir verstanden! Glückwunsch!
01.10.2020 20:44:53
Dir
AW: aber etwas kürzer geht es trotzdem noch ...
30.09.2020 17:57:22
neopa
Hallo,
... =SUMMENPRODUKT(C19:Z99*(C19:Z99&gt0)*(C18:Z98&gt="1.17"+0)*(C18:Z98&lt="1.18"-1)*(B18:B98&gt0))
Gruß Werner
.. , - ...
=SUMME(C9:Z99*(C9:Z99>0)*(ABS(C8:Z98-42918)<183))
30.09.2020 23:01:51
lupo1
AW: ja und nein, abgesehen davon, dass ...
01.10.2020 09:21:18
neopa
Hallo lupo,
... dass Du die Datenauswertung etwas gegenüber dem IST versetzt hast, hättest Du dazu schreiben sollen, dass diese Formel so nur für aktuellste XL-Versionen gilt, die automatisch Matrixformeln als solche erkennen. In älteren Versionen muss die Eingabe nämlich zwingend als Matrixformel abgeschlossen werden.
Außerdem hast Du wohl meinen Hinweis an Uwe überlesen, denn die Spalte B sollte mE in die Berechnung einbezogen werden, um sicher zu gehen.
Damit eine leichtere Anpassung an die Auswertung für andere Jahre erfolgen kann, sollte man die Formel mE wie folgt schreiben:
{=SUMME(C19:Z99*(C19:Z99&gt0)*(ABS(C18:Z98-"2.7.17")&lt183)*(B19:B99=""))}
Gruß Werner
.. , - ...
Anzeige
Sicherheit ist eine Zier ...
01.10.2020 11:00:45
lupo1
... doch angesichts von Cashflow-Beträgen um die 100 geht es ohne ihr.
Grundsätzlich hast Du damit natürlich recht.
Das mit SUMME und { } ist richtig; die Wdh. dessen dürfen aber gern andere übernehmen.
AW: zumindest sollte darauf verwiesen werden ...
01.10.2020 11:47:47
neopa
Hallo
... und da die angegebenen Beiträge wie fiktive Beispieldaten aussehen, sollte man sich nicht darauf verlassen, dass nicht gänzlich andere sind, also auch Daten aus dem Bereich der Datumsgrößenwerte.
Gruß Werner
.. , - ...
AW: Summen ziehen aus einer Tabelle
01.10.2020 20:25:51
Daniel
Hi
ist zwar nicht ganz das optimale für eine Auswertung, aber es geht sogar mit einem einfachen SummeWenns, sofern sich die Zahlungsbeträge und Ganzahlwert des Datums nicht überschneiden:
=SUMMEWENNS(C19:F25;C19:F25;">0";C18:F24;">="&DATUM(2017;1;1);C18:F24;"
beachte den Versatz der Zellbereiche, durch diesen Trick liegen Datum und Betrag für Excel in der gleichen Zeile und können so zugeordnet werden.
Liegen die Beträge von der Höhe her im Bereich der Datumswerte, dann kann man wie schon gezeigt mit Summenprodukt auswerten.
ich würde hier über REST oder ISTGERADE/ISTUNGERADE sicherstellen, dass nur jede zweite Zeile in die Berechnung eingeht:
=SUMMENPRODUKT(C19:F25;(C19:F25>0)*(ISTUNGERADE(ZEILE(C19:F25))*(C18:F24>=DATUM(2017;1;1)) *(C18:F24
Gruß Daniel
Anzeige

300 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige