Summe und Summanden

Karin · 29. März 2010 um 17:37

Liebe Experten,

gibt es eine Lösung für men Problem?

Ich habe in Spalte H viele verschiedene Zahlen und in Spalte I Summen aus verschiedenen Zahlen aus Spalte H - allerdings unsortiert.

Nun suche ich nach einer Lösung, dass ich auf eine dieser Summen in Spalte I klicke (oder auf eine Formel in der Spalte daneben) und Excel mir sagt (oder mit Pfeil markiert), welches die Summanden für jene Summe sein könnte. Die Anzahl der Summanden ist nicht begrenzt (kann in meinem Fall zwischen 1 und 30 liegen)

Beispiel:

____H______I
1___3,5
2___4,7
3___6,9
4___3,8
5__12,7
6___9,9
7__________20,3_______= Summe aus H1, H3, H6

Für eine Lösung wäre ich sehr dankbar!!!

Herzliche Grüße
Karin

Reinhard_fb8cb7 · 29. März 2010 um 21:24

Nun suche ich nach einer Lösung, dass ich auf eine dieser
Summen in Spalte I klicke (oder auf eine Formel in der Spalte
daneben) und Excel mir sagt (oder mit Pfeil markiert), welches
die Summanden für jene Summe sein könnte. Die Anzahl der
Summanden ist nicht begrenzt (kann in meinem Fall zwischen 1
und 30 liegen)

Hallo Karin,

stell dich in die Zelle in I und drücke F2.

Alternativ, Extras–Detektiv—Spur zum Vorgänger.

Gruß
Reinhard

Helmut_Taunus · 29. März 2010 um 22:41

Hallo,
klick in die Zelle der Summe, siehst Du oben in der Eingabezeile die Formel, zum Lesen. Klickst Du nun in diese Zeile, werden die Summanden in der Zeile und in der Tabelle farbig markiert.
Kannst die Markierung eines Summanden in der Tabelle anfassen und verschiebend veraendern.
Geht in Ecxel und in Openoffice Calc.
Gruss Helmut

Karin · 30. März 2010 um 07:57

Hallo Reinhard,

ich habe mich leider ungenau ausgedrückt. Die Werte in Spalte I und H sind „reale Zahlen“, in Spalte H sind keine Formeln hinterlegt. Excel sollte mir sagen, aus welchen Summanden die Zahl in Spalte H zusammen gesetzt sein könnte (es könnte auch mehrere Lösungen geben).

Hintegrund: Ich habe auf einem Bankkonto Zahlungseingänge (aufsaldiert) (Spalte H) und einzelne Gegenbuchungen (Spalte I). Allerdings sind wohl ein paar Fehler drin und die Summe der einzelnen Buchungen entsprechen nicht der Summe der Zahlungseingänge.

Neuer Versuch?

Liebe Grüße
Karin

Karin · 30. März 2010 um 07:58

Danke für deine Antwort Helmut, aber leider löst das nicht mein Problem.Siehe Antwort an Reinhard.

Liebe Grüße
Karin

Anonym_dccfd98ba8f6 · 30. März 2010 um 13:50

Moin,

gibt es eine Lösung für men Problem?

Schon, aber die ist nicht praktikabel ohne einen Rechnerpark…

Nun suche ich nach einer Lösung, dass ich auf eine dieser
Summen in Spalte I klicke (oder auf eine Formel in der Spalte
daneben) und Excel mir sagt (oder mit Pfeil markiert), welches
die Summanden für jene Summe sein könnte. Die Anzahl der
Summanden ist nicht begrenzt (kann in meinem Fall zwischen 1
und 30 liegen)

Die einzige Möglichkeit ist, knallhart alle möglichen Kombinationen durchrechnen zu lassen. Prinzipiell mit einem Makro kein Thema, Reinhard ist da Spezi, nur gibt es grob 1 Billion Möglichkeiten. Da dürfte das Makro ziemlich lange laufen…

Gruß

Kubi

Karin · 30. März 2010 um 15:08

Hi Kubi,

ich habe es befürchtet … wobei mein Rechner es gewohnt ist, hart zu schuften Er musste schon mal ca. 30 Stunden für eine Excel-Neuberechnung durchrattern (weil ich manche Berechnungen ganz profan mit Summenprodukt berechnen muss, statt so elegante Makro-Lösungen wie Reinhard zu produzieren *neidigguck*)

Viele Grüße
Karin

Reinhard_fb8cb7 · 30. März 2010 um 20:17

Hallo karin,

Ich habe in Spalte H viele verschiedene Zahlen und in Spalte I
Summen aus verschiedenen Zahlen aus Spalte H - allerdings
unsortiert.

„unsortiert“ irritiert mich. Es kann zwar sein, daß man ggfs H sortiert, bzw. eine Kopie von H sortiert um die Daten im Orginalzustand zu belassen, um damit die Laufzeit einer Vba-prozedur zu verkürzen die dann keine H-Werte überprüft, die größer als die Summe in I sind.
Naja, viel Zeitgewinn bringt das nicht *schätz*, egal, Kleinvieh macht auch Mist, grad bei Kombinatorik ist das Haupptziel die Laufzeit zu verkürzen.

Code um alle Möglichkeiten durchzuspielen ist schnell geschrieben, allerdings ohne Gray-Computer läuft der dann auch 30, 300, 3000 Stunden

Nun suche ich nach einer Lösung, dass ich auf eine dieser
Summen in Spalte I klicke (oder auf eine Formel in der Spalte
daneben)

Ist die Formel in der Zelle daneben wichtig für das Problem?

und Excel mir sagt (oder mit Pfeil markiert), welches
die Summanden für jene Summe sein könnte. Die Anzahl der
Summanden ist nicht begrenzt (kann in meinem Fall zwischen 1
und 30 liegen)

Wie kommt es zu so einer Tabellenstruktur?
Marion, die hier leider ( nur noch sehr wenig schreibt, erzählte mir mal von einem ihrer Kunden, der in Excel ellenlange Zahlenkolonnen eintippt, dann den taschenrechner zückt, alles addiert und das Ergebnis dann unter die Liste in Excel schreibt.
Machst du da sowas ähnliches? )

7__________20,3_______= Summe aus H1, H3, H6

Für eine Lösung wäre ich sehr dankbar!!!

Und wenns für 20,3 mehrere Lösungen gibt?

Lade mal eine Kopie deiner Originaldatei hoch, alles außer H und I kannste ja wohl löschen, außer die o.g. Formel in der Nachbarzelle, J?, spielt eine Rolle.

Und wenn das häufiger vorkommt, also immer neue Werete in H und I, mit wievielen Einträgen in H und I muß man in etwa rechnen, sind Einträge in H doppelt?

Und, um etwige Fehler melden zu können, muß jeder Wert aus H in einer Summe von I vorkommen, darf dies aber nur einmal tun, sodaß die Summen von H und I gleich sein sollten?

Quasi eine Meldung von nichtgenutzten Werten aus H.

Zum Mappe hochladen, nimm rapidshare, siehe FAQ:2606

Gruß
Reinhard

Reinhard_fb8cb7 · 30. März 2010 um 20:58

ich habe es befürchtet … wobei mein Rechner es gewohnt ist,
hart zu schuften Er musste schon mal ca. 30 Stunden für
eine Excel-Neuberechnung durchrattern (weil ich manche
Berechnungen ganz profan mit Summenprodukt berechnen muss,
statt so elegante Makro-Lösungen wie Reinhard zu produzieren
*neidigguck*)

Hallo Karin,

danke dir und Kubi für die netten Worte über mich *freu*

In einem muß ich dich leicht korrigieren. Die meisten Funktionen in Excel sind kompilierte C++ oder C# Funktioenen.
Vba kompiliert kaum was, die Masse der Codezeilen wird interpretiert.
Also während der Code-Abarbeitung Zeile für Zeile ausgelesen, in Maschinencode (Assembler) umgewandelt und ausgeführt.

Also ist Vba viel langsamer als Excelfunktionen weil dort der Code schon kompiliert vorliegt, also in Maschinensprache (Assembler).

Man spricht von Faktor bis zu 1.000- 10.000 mal schneller. Gut, konnte ich so noch nie nachmessen, siehe nachstehenden Code.

Dies gilt aber nicht für alle Funktionen *glaub*, Arrayformeln sind grottenlangsam.
Aber da bin ich auf dünnem Eis weil ich nicht weiß ob Arrayformelnersatz durch Vba schneller ist.
Und „Summenprodukt“, da weiß ich auch nicht ob es viel schneller als Vba ist.

Teste mal in einer leeren Mappe diesen Code:
(das Ergebnis täuscht, fairerweise muß man von der Zeit die Excel braucht die Zeit abziehen die es brauchte um die Zellen in C mit Formeln zu füllen)

Und die Ergebnisse sind natürlich auch davon abhängig was Windows oder Fremdprogramme in der Laufzeit des Codes grad so machen. Und das ist auch wenn an scih nix am PC gemacht wird gewaltig viel, mit regmon.exe und filemon.exe, beides Freeware von MS sieht man das.

Sub Test()
Dim TVba As Double, TExcel As Double, N As Long
Application.ScreenUpdating = False
TVba = Timer
For N = 1 To Rows.Count 'Vba klappert alle Zellen ab
 Cells(N, 2) = IIf(Cells(N, 1) = "", 0, 1)
Next N
TVba = Timer - TVba
TExcel = Timer
' C wird komplett mit der Formel erst befüllt, dann berechnet.
Range("C1:C" & Rows.Count).FormulaLocal = "=Wenn(A1="""";0;1)"
TExcel = Timer - TExcel
Application.ScreenUpdating = True
MsgBox "Vba = " & TVba & " sec , Excel = " & TExcel & " sec"
End Sub

Gruß
Reinhard

Thomas_Ramel · 31. März 2010 um 14:29

Gürzi Karin

wobei mein Rechner es gewohnt ist, hart zu schuften
Er musste schon mal ca. 30 Stunden für
eine Excel-Neuberechnung durchrattern (weil ich manche
Berechnungen ganz profan mit Summenprodukt berechnen muss

Hast Du für sowas auch schon mal eine Pivot-Tabelle versucht?

Das dürfte bei komplexen Vergleichen und grossen Datenmengen erheblich schneller sein als eine Matrixformel.

–
Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Karin · 31. März 2010 um 19:22

Hallo Reinhard,

danke für dein Hilfsangebot. Ich hab jetzt den VBA-code Brain eingesetzt und im try-and-error-Verfahren die Konten abgestimmt.

Liebe Grüße - und der nächste Hilfeschrei kommt bestimmt

Karin

Karin · 31. März 2010 um 19:23

Hallo Thomas,

Pivot ist für mich noch ein unbeschriebenes Blatt. Aber ich werde mich da mal etwas einlesen, bin ja lernfähig

Liebe Grüße
Karin

Thomas_Ramel · 31. März 2010 um 20:50

Grüezi Karin

Pivot ist für mich noch ein unbeschriebenes Blatt. Aber ich
werde mich da mal etwas einlesen, bin ja lernfähig

Fein - und bei Fragen einfach Fragen, denn:

„Bei W-W-W werden Sie geholfen!“

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Marion_L_we · 2. April 2010 um 01:58

Hallo Karin,

Hintegrund: Ich habe auf einem Bankkonto Zahlungseingänge
(aufsaldiert) (Spalte H) und einzelne Gegenbuchungen (Spalte
I). Allerdings sind wohl ein paar Fehler drin und die Summe
der einzelnen Buchungen entsprechen nicht der Summe der
Zahlungseingänge.

gerade bei diesem Hintergrund wäre die Vorgehensweise nicht sinnvoll. Was ist, wenn z.B. der Überweiser 7 Beträge aufsummiert, einen davon aber gekürzt hat um Skonto z. B. oder er hat einen Zahlendreher drin oder statt …,43 nur …,40 mitgerechnet. Evtl bekommst Du eine passende Summe, möglicherweise gleichst du dann aber aufgrund dieser "passenden Summierung die falschen Posten aus. So kriegst du nie Ordnung in deine Buchhaltung. Wichtig: immer gleichlautend wie der Überweiser buchen. Das schnellste ist dabei immer noch den Überweiser anzurufen und um eine Avis zu bitten oder einfach zu fragen, wie sich die Überweisungsumme zusammensetzt.

Lieben Gruß aus Konstanz
Marion