Kontonummer erkennen und Werte zusammenfassen

[StefanExcelt]

Hallo Zusammen :) 
Nachdem ich mich nun über einen längeren Zeitraum immer wieder mit einem Problem beschäftigt habe und einfach nicht auf die Lösung komme hoffe ich auf eure Hilfe.

Folgende Situation: Regelmäßig werden monatlich Reports in einem Online Service als CSV erstellt. Grundsätzlich geschieht das meiste was bei den Berechnungen dieser CSVs passieren soll bereits im Code. Eine Funktion soll nun aber direkt mit Excel gelöst werden. Ich lade mir also diese Reports herunter und kopiere sie in eine dafür vorbereitete Excel (in der Realtität besteht diese Excel dann aus mehreren Mappen die viele Reports zusammenfasst).
Es handelt es sich um einen Report in dem die offenen Posten eines Debitors aufzufinden sind. Angenommen wir haben 500 Einträge (also 500 Rechnungen die noch nicht beglichen worden sind) mit Spalten wie Kontonummer, Rechnungsnummer, Datum der Fälligkeit, offener Betrag usw:

Ich möchte nun eine weitere Spalte hinzufügen, nämlich die "Aufsummierung aller einzelnen offenen Posten, je Kontonummer". Quasi was in die Richtung SUMMEWENN.

Das bedeutet ich möchte dass der Reihenfolge nach immer geguckt wird ob die Kontonummer weitere Male existiert (theoretisch kann eine Kontonummer beliebig viele offene Posten haben) und jedes Mal wenn er die Kontonummer findet in der Spalte "offener Betrag" den Betrag nimmt und aufaddiert und die Gesamtsumme anschließend in der Aufsummierung ausgibt. Ich habe eine kleine Beispieldatei mit Testdaten angelegt die das Ganze etwas anschaulicher machen soll. 


  Testdaten.xlsx (Größe: 9,25 KB / Downloads: 18)

Bedingungen:
Der Radius des Suchkriteriums soll sich selber nach unten erweitern können solange bis er auf eine leere Zeile stößt (Kontonummer ist immer gegeben).

Vielen Dank und viele Grüße
Stefan

[RPP63]

Grundsätzlich geschieht das meiste was bei den Berechnungen dieser CSVs passieren soll bereits im Code.

Moin!
Eine CSV ist eine reine Textdatei (auch wenn sie in Excel geöffnet werden kann)!
Darin kann nichts berechnet werden, geschweige denn Code vorhanden sein.
Ich habe mir die Datei (noch) nicht angesehen, schätze aber mal, dass dies eine Aufgabe für ein Pivot-Table ist.
Gruß Ralf

[WillWissen]

Moin,

wenn ich deine Beispieldatei als "Endprodukt" deiner .csv ansehe, dann würde ich das mit ein paar Änderungen so lösen:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Kontonummer	HS1	Rechnungsnummer	Datum	Status	Fälligkeit	Anzahl der Tage der Überfälligkeit	Bezahlmethode	Gesamtbetrag aller offenen Forderungen	Offener Betrag	Konten	Offener Betrag pro Konto	Währung	Buchungstext	Abschlags -anzahlung	Kommentar
2	1007	1								-              0,50 €	1007	0,20 €
3	1006	1								70,00 €	1006	100,00 €
4	1006	2								30,00 €	1008	-                                    15,50 €
5	1008	1								-              0,60 €	1009	185,00 €
6	1009	1								155,00 €	1005	189,70 €
7	1005	1								189,70 €	1001	75,60 €
8	1007	2								0,70 €	1010	20,00 €
9	1008	2								-            14,90 €	1004	-                                       8,90 €
10	1001	1								70,00 €	1003	11,70 €
11	1009	2								30,00 €		-   €
12	1010	1								20,00 €		-   €
13	1001	2								5,60 €		-   €
14	1004	1								-              8,90 €		-   €
15	1003	1								11,70 €		-   €

Zelle	Formel
B2	=ZÄHLENWENN($A$2:A2;A2)
K2	=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE([Kontonummer])/([HS1]=1);ZEILE(A1));1);"")
L2	=SUMMEWENN([Kontonummer];[@Konten];[Offener Betrag])

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

1. Nur eine Überschrift und Tabelle als intelligente Tabelle formatieren.
2. Hilfsspalte (HS1) einfügen - kann ausgeblendet werden
3. Kontonummer-Unikatliste einfügen
4. Spalte für die Aufrechnung

Quelle: Andreas Thehos (https://thehosblog.com/2013/05/02/excel-...auflisten/)

[RPP63]

Ich bleibe bei der Pivot:
Kto in Zeilen,
offen in Werte,
offen zusammenfassen nach Summe,
fettich!
Ohne eine Formel, erstellt in 10 bis 15 Sekunden!
Kto-Nr als nettes Gimmick sortiert.

Gruß Ralf

[StefanExcelt]

Vielen Dank für eure Hilfe bis hierhin. Ich kopiere Inhalte aus dem heruntergeladenen Report in eine normale Excel-Tabelle. In der CSV werden Datensätze einfach zusammengefasst. 

Ich bleibe bei der Pivot:
Kto in Zeilen,
offen in Werte,
offen zusammenfassen nach Summe,
fettich!
Ohne eine Formel, erstellt in 10 bis 15 Sekunden!
Kto-Nr als nettes Gimmick sortiert.

Gruß Ralf

Kriege ich so ehrlich gesagt nicht hin? Geht es etwas genauer? Und dabei sucht immer den jeweiligen Wert zur passenden Kontonummer raus?

wenn ich deine Beispieldatei als "Endprodukt" deiner .csv ansehe, dann würde ich das mit ein paar Änderungen so lösen:

Danke, sieht schon gut aus. Tatsächlich hat man aber in Spalte J immer den gleichen Wert stehen, das bedeutet dass er dort erst einen Weet einsetzt wenn er alle Zeilen berücksichtigt hat.

[WillWissen]

Hallo Stefan,

Tatsächlich hat man aber in Spalte J immer den gleichen Wert stehen, das bedeutet dass er dort erst einen Weet einsetzt wenn er alle Zeilen berücksichtigt hat.

erklär mir das bitte etwas genauer, das verstehe ich nicht.

[BoskoBiati]

Hi,


Pivot, nur drei bis fünf Klicks:

Arbeitsblatt mit dem Namen 'Tabelle1'
	O	P
2	Zeilenbeschriftungen	Summe von Offener Betrag
3	1001	75,6
4	1003	11,7
5	1004	-8,9
6	1005	189,7
7	1006	100
8	1007	0,2
9	1008	-15,5
10	1009	185
11	1010	20
12	Kontonummer des Debitors des Debitors	0
13	Gesamtergebnis	557,8

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Formel:

Arbeitsblatt mit dem Namen 'Tabelle1'
	N	O
4	Konto	Offen
5	1001	75,6
6	1003	11,7
7	1004	-8,9
8	1005	189,7
9	1006	100
10	1007	0,2
11	1008	-15,5
12	1009	185
13	1010	20

Zelle	Formel
N5	=MIN(A4:A17)
N6	=WENNFEHLER(AGGREGAT(15;6;$A$4:$A$17/($A$4:$A$17>N5);1);"")
O5	=SUMMEWENN($A$4:$A$17;N5;$I$4:$I$17)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2010

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[StefanExcelt]

Erweitert sich die Pivot von alleine oder müsste der Bereich immer wieder manuell neu festgelegt werden (bei einem neuen Report dann)?

[WillWissen]

Hallo Stefan,

du musst die PT aktualisieren ==> Rechtsklick in die PT ==> Daten aktualisieren.

[RPP63]

Wandle die Original-Tabelle in eine Liste um (Strg+T oder Strg+L)
Dann wächst der Auswertebereich automatisch mit.
Die Pivot aktualisiert sich nicht selbständig.
Allerdings geht das ratz-fatz mittels Rechtsklick in den Bereich, aktualisieren.