Office: Werte gegeneinander ausgleichen

Hallo zusammen,

ich benötige eure Hilfe. Im Anhang findet ihre eine Excelliste. Ich möchte wissen,welcher Wert die Differenz von -1700 ausmacht. Im Beispiel habe ich die Zahl manuell gelb markiert. Dies soll allerdings automatisch erfolgen. Alle Werte die sich gegeneinander ausgleichen,sollen entsprechend nicht markiert werden. Auch nicht Teilsummen z.B. -10,-10,+20, da diese Werte sich auch ausgleichen. Vielleicht kann mir jemand mit einem Makro helfen.

Vielen Dank

Gruss
TruckerFranz78

:)

 

Hallo TruckerFranz,

Es wäre hilfreich, wenn Du Deine Beispieldatei gerade für diese Fälle um ein paar typische Beispiel (ausgleichend und nicht ausgleichend), mit unterschiedlichen Farben markiert, ergänzen würdest.

 

Hallo,

ich habe ein paar Beispielwerte, die sich ausgleichen rot markiert. Diese Werte sollen nicht hervorgehoben werden.

Es soll auschliesslich die Zahl markiert werden, die keinen Gegenpart hat. In diesem Fall die gelbmarkierte -1700.

Danke für eure Mühen.

Gruss
Truckerfranz78

 

Wie sollen denn die Teilsummen bitte kontrolliert werden? Da gibt es ja unendlich viele Kombinationsmöglichkeiten und Excel würde sich zu Tode rechnen. Und gelb markiert ist -1007 und nicht -1700.

 

Hallo Berni,

ich habe schonmal ein Makro dazu bekommen. Dieses funktioniert aber nicht korrekt. Es markiert teilweise Werte die sich ausgleichen. Aber vielleicht hilft es ja trotzdem weiter:

Sub Betraege_Abgleichen()
Dim BTR As Range
Dim i As Long, j As Long, n As Long
Set BTR = Application.Selection
n = BTR.Count
ReDim gef(n) As Boolean
For i = 1 To n
For j = (i + 1) To n
If Not gef(i) And BTR(i).Value = (BTR(j).Value * -1) Then
gef(i) = True
gef(j) = True
Exit For
End If
Next j
If Not gef(i) Then BTR(i).Interior.Color = 65535
Next i
Set BTR = Nothing
End Sub

Gruss
TruckerFranz78

 

Hallo Truckerfranz,

Diese sind doch nicht das Problem! Siehe dazu den Beitrag von MisterBurns!!!
Und für diese Teilsummen hast Du gerade mal ein einfaches Beispiel markiert.

Vermutlich aus einem anderen Forum; dann könntest Du ja auch dort weitermachen.

 

Ohne Teilsummenbetrachtung (= gleiche Minus- und Plus-Beträge): Wenn man die Abs-Beträge sortiert, geht das schon ganz nett.

Mit Teilsummenbetrachtung: Da gibt es evtl. was von Excelformeln.de. Allerdings auch mit einer Mengenbegrenzung.

 

Die gleichen Plus/Minusbeträge sind ja mit Zählenwenn simpel, aber Teilsummen bei 700 oder mehr Einträgen? Na viel Spaß...

 

Da ist schon viel früher Schluss (als bei 700), aufgrund "exponenzieller Exponenzierung".

 

Hallo,

anbei eine mit Power Query erstellte Lösung.

 

Hallo WS-53,

vielen Dank für deine Lösung. Wie aktualisiert sich das PQ Result, wenn ich zb eine neue Zahlenreihe in die Input Tabelle einfügen möchte ?

Gruss
TruckerFranz

 

Hallo TruckerFranz,

Cursor irgendwo in die Ergebnistabelle > rechte Maustaste > Aktualisieren.

Über: Daten > Abfragen und Verbindungen, siehst du rechts auch die Abfragen, die du öffnen und bearbeiten kannst.

Du siehst dann 3 Abfragen, die bei der Aktualisierung automatisch in der angezeigten Reihenfolge ablaufen. In der Ersten Abfrage (Tabelle1), habe ich den Schritt "Added Custom" mittels einer If-Abfrage im Funktionseditor für die Auswahl: Spalte hinzufügen > Benutzerdefinierte Spalte, definiert. Alles andere ist nur zusammengeklickt.

Wenn du mal die Funktionsweise von Power Query verstanden hast, wirst du eststellen, dass du mit sehr wenig Aufwand sehr viel erreichen kannst.

Und für Neueinsteiger, die bisher weder VBA noch Power Query beherschen, kann ich nur zum wiederholten Male bemerken, dass sich Power Query viel schneller erlernen lässt.

Aber abgesehen davon, dass du PQ für viel mehr als nur, wie wohl ursprünglich vorgeshen, ETL (Extraktion > Transformation > Load) verwenden kannst, kann es natürlich in Summe nur einen Teil dessen, was du mit VBA machen kannst.

Aber wenn ich eine Fragestellung mit PQ realisieren kann, dann ziehe ich es gegeüber einer VBA-Lösung vor.

Kommt aber auch immer wieder vor, dass ich vor lauter PQ-Begeisterung übersehe, dass eine Formellösung noch einfacher gewesen wäre.







den Befehle

 

Hallo TruckerFranz,

da du bei Beruf "Buchhaltung" angibst.

Als mein Arbeitgeber vor langer Zeit die Finanzsoftware von Steeb eingesetzt hat, da gab es auch schon den automatischen OP-Ausgleich aufgrund von Zahlungseingängen. Dieser hat aber nicht funktioniert, wenn die Kunden Akonto-Zahlungen geleistet haben.

Dafür habe wir uns dann die Funktion programmieren lassen, dass bei Akonto-Zahlungen, die offenen Posten von alt nach jung so lange ausgeglichen wurden bis nichts mehr übrig war. Da dies i.d.R. nie restfrei aufging, wurde am Ende der offenen Posten, der nicht mehr vollständig ausgeglichen werden konnten, nur mit dem verbleibenden Rest ausgeglichen und für den verbleibenden Rest der Forderung ein neuer Beleg generiert. Der Kunde wurde dann, mit einem automatisch generierten Brief, darüber informiert, wie seine Akonto-Zahlung verwendet wurde.

Mit dieser Funktionserweiterung war dann das automatische Ausziffern wesentlich effektiver.

 

\@WS-53,

Das nenne ich pragmatische Lösung und das ohne "exponenzielle Exponenzierung"!

 

Hallo Aloys78,

der Firma Steeb, die ja dann später von SAP übernommen wurde, hatte damals die von uns entwickelte Idee, so gut gefallen, dass sie diese zum Standard erklärten und somit für uns diese Programmierung kostenfrei war.

Ich hatte dies damals nur mitbekommen, da unser heutiger Weltkonzern (aktuell >300.000 MA), damals noch eine kleine Klitsche war und ich mit dem Kollegen, der EDV-seitig die Buchhaltung betreute, ein Büro teilte.

Der Kollege hat mir dann aber auch beigebracht, wie wie in Cobol für den Zugriff in eine sortierte Tabelle, eine binäre Suche programmieren kann. Das ist heute in ABAP, vermutlich auch in vielen anderen Programmiersprachen, viel einfacher, da muss für den Select Single nur der Zusatz "binary Search" verwendet werden.