Saldierung

Computer: Software & Programmierung Office Software
#1

Hallo zusammen,

ich habe ein Problem mit einer riesengroßen Excel Datei und ich möchte das Doing mit Excel beschleunigen. Ich weiß nur nicht wie.

Bsp:

Rechnungsnr Betrag
1… 100
1… -100
1… 150
1… -150
2… 30
2… -30
2… 100
2…- 80

Für Rechnungsnr 1 sieht man, daß sich der Betrag zu Null saldiert.
Für Rechnungsnr 2 ist die Saldierung nicht Null. Es bleiben 20 übrig.
Die Datei, die ich zu bearbeiten habe, ist 27.000 Zeilen groß.

Wenn ich eine Formel hätte, die mir anzeigt, daß Rechnungsnr 1 im Salo Null ergibt und das bei Rechnungsnr 2 der Saldo nicht Null ergibt, dann wäre mir schon sehr geholfen.

Vielen Dank für eure Hilfe im Voraus.

MfG
Dudel

#2

Hallo Dudel,

hier hilft Dir eine Pivottabelle (Menüeintrag glaube ich unter Extras [ich habe gerade kein Excel live am Rechner]) weiter.

MFG Georg V.

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

#3

Hallo Peter,

Bsp:

Rechnungsnr Betrag
1… 100
1… -100
1… 150
1… -150
2… 30
2… -30
2… 100
2…- 80

Wenn ich eine Formel hätte, die mir anzeigt, daß Rechnungsnr 1
im Salo Null ergibt und das bei Rechnungsnr 2 der Saldo nicht
Null ergibt, dann wäre mir schon sehr geholfen.

Die Pivottabelle, die Georg vorgeschlagen hat, ist zwar eine Lösung, die das elegant erledigt, aber die ist eher für Tabellen geeignet, in denen viele Spalten zu summieren sind. Bei dieser Struktur ist eine einfache Formel viel schneller erstellt.

Wenn du die Salden in einer separaten Tabelle (kann auch auf dem gleichen Blatt sein!) anzeigen willst, kann ich dir folgende Matrixformel empfehlen:
{=WENN(SUMME(WENN(D3=$A$4:blush:A$20;$B$4:blush:B$20))=0;„ok!“;SUMME(WENN(D3=$A$4:blush:A$20;$B$4:blush:B$20)))}
In der A-Spalte stehen die Rechnungsnummern, in B die Beträge. Achtung, diese Formel ist eine Matrixformel, die du mit STRG+SHIFT+ENTER abschließen musst! Wenn du Fragen hast, bitte melde dich.

MfG

Alex

#4

Hallo Dudel,

ich würde da mal ganz einfach rangehen,
nachher kannst du immer noch verbessern.
wenn in A2 die Rg-Nr. steht und
in B2 der Betrag steht,
dann schreibe einfach mal diese Formel in C2:

=SUMMEWENN($A$2:blush:A$27000;A2;$B$2:blush:B$27000)

und kopiere sie dann nach unten.

Gruß Finus

#5

Hallo Peter,

Rechnungsnr Betrag
1… 100
1… -100
1… 150
1… -150
2… 30
2… -30
2… 100
2…- 80

Für Rechnungsnr 1 sieht man, daß sich der Betrag zu Null
saldiert.
Für Rechnungsnr 2 ist die Saldierung nicht Null. Es bleiben 20
übrig.
Die Datei, die ich zu bearbeiten habe, ist 27.000 Zeilen groß.

Wenn ich eine Formel hätte, die mir anzeigt, daß Rechnungsnr 1
im Salo Null ergibt und das bei Rechnungsnr 2 der Saldo nicht
Null ergibt, dann wäre mir schon sehr geholfen.

Dafür bietet sich das Gruppieren an, dafür gibt es in Excel folgendes Menü:

-> Daten -> Teilergebnisse …
wähle bei Rechnungsnummer
wähle bei Summe
wähle bei Betrag
OK (dauert mitunter ein wenig bei vielen Zeilen)

Excel fügt Zwischensummen für die einzelnen Rechnungsnummern ein, die genauso wieder entfernt werden können.

Links von den Zeilenköpfen sind die Gruppierungszeichen, wenn du auf den Button mit der 2 klickst (ganz oben), hast du die gewünschten Salden.

Gruß
Marion

#6

Hallo Alex,

Wenn ich eine Formel hätte, die mir anzeigt, daß Rechnungsnr 1
im Salo Null ergibt und das bei Rechnungsnr 2 der Saldo nicht
Null ergibt, dann wäre mir schon sehr geholfen.

Die Pivottabelle, die Georg vorgeschlagen hat, ist zwar eine
Lösung, die das elegant erledigt, aber die ist eher für
Tabellen geeignet, in denen viele Spalten zu summieren sind.

Georgs Vorschlag, die PivotTab ist eine prima Lösung, man muss ja nicht alle Spalten in die Tabelle ziehen, sondern nur die mit den Rechnungsnummern (in den Zeilenbereich) und die mit den Beträgen (in den Datenbereich). Ist schnell gemacht.

Bei dieser Struktur ist eine einfache Formel viel schneller
erstellt.

Wenn du die Salden in einer separaten Tabelle (kann auch auf
dem gleichen Blatt sein!) anzeigen willst, kann ich dir
folgende Matrixformel empfehlen:
{=WENN(SUMME(WENN(D3=$A$4:blush:A$20;$B$4:blush:B$20))=0;„ok!“;SUMME(WENN(D3=$A$4:blush:A$20;$B$4:blush:B$20)))}
In der A-Spalte stehen die Rechnungsnummern, in B die Beträge.
Achtung, diese Formel ist eine Matrixformel, die du mit
STRG+SHIFT+ENTER abschließen musst! Wenn du Fragen hast, bitte
melde dich.

Also ich komme mit dieser „einfachen“ Matrix-Formel nicht zurecht. Vielleicht kann mir das mal einer erklären. Diese Matrix-Formeln sind für mich immer noch undurchschaubare Rätsel.

Und was steht in D3?
Und wie erhält man die Salden für z. B. 10.000 Rechnungsnummern? Muß ich dann diese Formel sooft kopieren oder …?

Gruß
Marion

#7

OT Matrixformeln

Also ich komme mit dieser „einfachen“ Matrix-Formel nicht
zurecht. Vielleicht kann mir das mal einer erklären. Diese
Matrix-Formeln sind für mich immer noch undurchschaubare
Rätsel.

Hi Marion,
das beruhigt mich irgendwie, daß du da auch rätselst, diese ganzen Matrixformeln wie z.B. bei http://wwww.excelformeln.de , da habe ich nie durchgeblickt, werde das auch nie tun und dabei arbeiten die nur mit so ca. 8 Funktionen,
aber irgendwas in mir hemmt mich das zu kapieren oder genauer gesagt kapieren zu wollen.
Ist wie mit Diagrammen und Pivottabellen, die mag ich auch nicht.
Weil ich sowieso eher von der Vba Schiene komme, kann ich da viel machen und eindeutig besser machen als Autofilter,Spezialfilter,Pivottabellen so anbieten, deshalb schweige ich zu Anfragen zu Pivottabellen weil ich die nicht nutze und letzlich keine Ahnung habe wie man die benutzt.
Gruß
Reinhard

#8

Hallo Marion,

Und was steht in D3?
Und wie erhält man die Salden für z. B. 10.000
Rechnungsnummern? Muß ich dann diese Formel sooft kopieren
oder …?

In D3 steht die Rechnungsnummer, die du dann runterziehen kannst. Die Matrixformel in E3 ebenfalls runterziehen und du hast nur noch die Salden, die nicht Null sind.

Gruß
Alex

#9

Hallo Marion,

Und was steht in D3?
Und wie erhält man die Salden für z. B. 10.000
Rechnungsnummern? Muß ich dann diese Formel sooft kopieren
oder …?

In D3 steht die Rechnungsnummer, die du dann runterziehen
kannst. Die Matrixformel in E3 ebenfalls runterziehen und du
hast nur noch die Salden, die nicht Null sind.

wenn ich das richtig verstanden habe, stehen in der Ausgangsliste in Spalte A Rechnungsnummern und in Spalte B verschiedene positive bzw. negative Beträge. Vermutlich handelt es sich um einen Download mit Rechnungsnummern und Beträgen, die aus Rechnungsbeträgen und Zahlungseingängen bestehen, wobei die Zahlungseingänge nicht unbedingt mit den Rechnungsbeträgen übereinstimmen (Teilzahlungen oder Zahlungseingänge für mehrere Rechnungen). Dann habe ich aber keine fortlaufenden Rechnungsnummern und die Rechnungsnummern redundieren evtl auch. Wie kann ich dann aber die Rechnungsnummern „runterziehen“ (und das für ein paar tausend Datensätzen *stöhn*).

Und muss ich genauso oft die Matrixformel nach unten ausfüllen???

Bin dabei, dass an einem Beispiel nach zu vollziehen, aber ich kriegs nicht hin.

Gruß
Marion

#10

Hallo Marion,

wenn ich das richtig verstanden habe, stehen in der
Ausgangsliste in Spalte A Rechnungsnummern und in Spalte B
verschiedene positive bzw. negative Beträge.

Genau, so ist es.

Dann habe ich aber keine fortlaufenden Rechnungsnummern und die Rechnungsnummern redundieren evtl auch. Wie kann ich dann aber die
Rechnungsnummern „runterziehen“ (und das für ein paar tausend
Datensätzen *stöhn*).

Die Rechnungsnummern sind verschieden häufig anzutreffen, aber das spielt keine Rolle. Du schreibst einfach die erste in die erste Zeile und ziehst so weit runter, soweit du Nummern hast. Soweit musst du dann auch die Formel runterziehen.

Gruß

Alex

#11

Hallo Alex,

Die Rechnungsnummern sind verschieden häufig anzutreffen, aber
das spielt keine Rolle. Du schreibst einfach die erste in die
erste Zeile und ziehst so weit runter, soweit du Nummern hast.
Soweit musst du dann auch die Formel runterziehen.

das mit den Rechnungsnummern klappt nicht bei mir, ich hab in einem solchen Download mit etwas über 16000 Datensätzen die Rechnungsnummern extra von Standard (Text) auf Zahlen formatiert. Wenn ich dann die Rechnungsnummern „runterziehe“, erhalte ich auch für Nummern, die es in der Ausgangsliste nicht gibt eine OK-Meldung. Das bedeutet aber, dass ich durch diese Matrixformel viele zusätzliche Einträge erhalte - vielleicht 50000 oder noch mehr.

Ich hab mal meine Testdatei hochgeladen,
http://www.badongo.com/file/3388672

Vielleicht kannst du mir ja noch einen Tipp geben, zumindest ist das Beispiel einfach, um es nach zu vollziehen.
Vielleicht kann ich heute ja doch noch ein Geheimnis ergründen. :wink:

Ich würd ja gern mal diese Matrixformel ergründen, aber in diesem Fall erscheint es mir doch am besten, die von Georg vorgeschlagene Möglichkeit mit der PivotTab zu verwenden. Geht super fix.

Die Lösung über die Teilergebnisse dauert leider auch ca. 2 min.

Gruß
Marion

#12

Hallo Reinhard,

das beruhigt mich irgendwie, daß du da auch rätselst, diese
ganzen Matrixformeln wie z.B. bei http://wwww.excelformeln.de
, da habe ich nie durchgeblickt, werde das auch nie tun und
dabei arbeiten die nur mit so ca. 8 Funktionen,
aber irgendwas in mir hemmt mich das zu kapieren oder genauer
gesagt kapieren zu wollen.

ja, so geht es mir auch - genauso - nur hab ich zur Zeit noch den Ehrgeiz, das Rätsel zu knacken.

Ist wie mit Diagrammen und Pivottabellen, die mag ich auch
nicht.

Aber da gibt es die Assistenten, da braucht man nicht wirklich was zu wissen, man macht es ein paar mal, dann kennt man sie alle :wink:

Weil ich sowieso eher von der Vba Schiene komme,

das ist für mich das nächste Rätsel, aber das verstehe ich und kann deshalb schon selbst was bauen - da fehlt mir nur das Wissen und die Erfahrung

naja

mit der Zeit …

Lieben Gruß
Marion

#13

Hallo Marion,

das mit den Rechnungsnummern klappt nicht bei mir, ich hab in
einem solchen Download mit etwas über 16000 Datensätzen die
Rechnungsnummern extra von Standard (Text) auf Zahlen
formatiert. Wenn ich dann die Rechnungsnummern „runterziehe“,
erhalte ich auch für Nummern, die es in der Ausgangsliste
nicht gibt eine OK-Meldung.

Dieses Problem kannst du mit einem Filter umgehen: Daten - Filter - Spezialfilter. Dann Keine Duplikate und kopieren nach auswählen und du hast die bereinigte Liste. Dann funktioniert auch die Formel so, wie sie sollte.

Gruß Alex

#14

ich habe ein Problem mit einer riesengroßen Excel Datei und
ich möchte das Doing mit Excel beschleunigen. Ich weiß nur
nicht wie.

Hi Peter,
ist dir das Makro schnell genug?
Es stellt in Tabelle2 die Rechnungsnummern aus Tabelle1 dar und dahinter die Saldi.
Andere Darstellungsformen wie farbige Zellen wenn der Saldo einer Rechnungsnummer nicht 0 ist o.ä. sind leicht anzupassen.

Sub MeinSaldo()
Dim wsZiel As Worksheet, Zeile1 As Long, ZeileZiel As Long
Set wsZiel = Worksheets("Tabelle2")
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
wsZiel.Cells.ClearContents
ZeileZiel = 1
With Worksheets("Tabelle1")
 wsZiel.Cells(ZeileZiel, 1) = .Cells(1, 1)
 wsZiel.Cells(ZeileZiel, 2) = .Cells(1, 2)
 For Zeile1 = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
 If .Cells(Zeile1, 1) = .Cells(Zeile1 - 1, 1) Then
 wsZiel.Cells(ZeileZiel, 2) = wsZiel.Cells(ZeileZiel, 2) + .Cells(Zeile1, 2)
 Else
 ZeileZiel = ZeileZiel + 1
 wsZiel.Cells(ZeileZiel, 2) = .Cells(Zeile1, 2)
 wsZiel.Cells(ZeileZiel, 1) = .Cells(Zeile1, 1)
 End If
 Next Zeile1
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Gruß
Reinhard

Bsp:

Rechnungsnr Betrag
1… 100
1… -100
1… 150
1… -150
2… 30
2… -30
2… 100
2…- 80

Für Rechnungsnr 1 sieht man, daß sich der Betrag zu Null
saldiert.
Für Rechnungsnr 2 ist die Saldierung nicht Null. Es bleiben 20
übrig.
Die Datei, die ich zu bearbeiten habe, ist 27.000 Zeilen groß.

Wenn ich eine Formel hätte, die mir anzeigt, daß Rechnungsnr 1
im Salo Null ergibt und das bei Rechnungsnr 2 der Saldo nicht
Null ergibt, dann wäre mir schon sehr geholfen.

Vielen Dank für eure Hilfe im Voraus.

MfG
Dudel

#15

Hallo Alex,

Dieses Problem kannst du mit einem Filter umgehen: Daten -
Filter - Spezialfilter. Dann Keine Duplikate und
kopieren nach auswählen und du hast die bereinigte
Liste. Dann funktioniert auch die Formel so, wie sie sollte.

ich dank dir für deine Mühe, aber leider überzeugt mich das nicht. Alles viel zu umständlich, da werd ich wohl nie hinter das Geheimnis der Matrixformeln steigen, weil eine PivotTab die Aufgabe einfach superschnell und flexibel löst.

Gruß
Marion