6
IBAN s Summieren
Hallo,
ich möchte eine Prüfsumme aus mehreren IBAN Bankverbindungen erstellen.
Die IBAN stehen nach Norm mit DE und Leerzeichen, diese müssen vorher entfernt werden.
Die IBANs stehen in einer Excel Tabelle in einem Bereich X33 bis x88 .
Ich würde entweder gerne stumpf alle IBANS zusammenrechnen wollen oder alle Einzelziffern summieren.
Bei der Variante wo ich alle zusammenzähle scheitere ich an der Ziffernlänge weil Excel ab 15 Ziffer 0 anzeigt.
Hier habe ich diese Formel :
Wenn ich die einzelnen Ziffern addieren möchte scheitere ich an einer Bereichsformel.
Bei einzelnennung der Spalten/Zeilen funktioniert es. Hier habe ich bislang dies :
Vielleicht kann mir hier jemand helfen ? Vielen Dank.
ich möchte eine Prüfsumme aus mehreren IBAN Bankverbindungen erstellen.
Die IBAN stehen nach Norm mit DE und Leerzeichen, diese müssen vorher entfernt werden.
Die IBANs stehen in einer Excel Tabelle in einem Bereich X33 bis x88 .
Ich würde entweder gerne stumpf alle IBANS zusammenrechnen wollen oder alle Einzelziffern summieren.
Bei der Variante wo ich alle zusammenzähle scheitere ich an der Ziffernlänge weil Excel ab 15 Ziffer 0 anzeigt.
Hier habe ich diese Formel :
=SUMME(WENN(X33:X88<>"";WERT(WECHSELN(WECHSELN(X33:X88;"DE";"");" ";""));0)) Wenn ich die einzelnen Ziffern addieren möchte scheitere ich an einer Bereichsformel.
Bei einzelnennung der Spalten/Zeilen funktioniert es. Hier habe ich bislang dies :
=SUMMENPRODUKT(TEIL(WECHSELN(WECHSELN(X41;"DE";"");" ";"");ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(WECHSELN(X41;"DE";"");" ";""))));1)*1)+SUMMENPRODUKT(TEIL(WECHSELN(WECHSELN(X49;"DE";"");" ";"");ZEILE(INDIREKT("1:"&LÄNGE(WECHSELN(WECHSELN(X49;"DE";"");" "; ""))));1)*1)+.........; Vielleicht kann mir hier jemand helfen ? Vielen Dank.
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 668259
Url: https://administrator.de/contentid/668259
Ausgedruckt am: 21.11.2024 um 19:11 Uhr
6 Kommentare
Neuester Kommentar
Vielleicht beschreibst du mal, was dir das genau bringen soll? Das klingt für mich erstmal nach Blödsinn. Jeder Zahlendreher wirkt sich nicht auf eine Addition aller Ziffern aus, was genau willst du damit erreichen?
Die IBAN hat übrigens eine Prüfziffer, anhand derer man jede IBAN validieren kann (zumindest die meisten Fehler sollte man damit erkennen). Wenn es darum geht über die IBAN-Kombination irgendwie eine Checksumme zu bilden würde sich ein Hash anbieten.
Die IBAN hat übrigens eine Prüfziffer, anhand derer man jede IBAN validieren kann (zumindest die meisten Fehler sollte man damit erkennen). Wenn es darum geht über die IBAN-Kombination irgendwie eine Checksumme zu bilden würde sich ein Hash anbieten.
5
Es geht mir nicht um die Richtigkeit der IBAN sondern um den Vergleich von zwei Ausgaben aus zwei unterschiedlichen Systemen. Um zu prüfen ob die Bankverbindungen aller in der Liste von System 1 zu System 2 identisch ist.
Moin,
dann Exportiere beide Listen in eine Text-Datei/ CSV, nachdem du sie auf-/ absteigend sortiert hast und nutze Winmerge (als Beispiel) für den Vergleich.
Winmerge zeigt dir direkt, ob links/ rechts Einträge fehlen.
Oder du nutzt die Powershell (Anmerkung: in der ersten Zeile jeder CSV steht die Spaltenüberschrift "IBAN"):
In Anlehnung an: https://stackoverflow.com/questions/71943478/windows-powershell-how-to-c ...
Um zu prüfen ob die Bankverbindungen aller in der Liste von System 1 zu System 2 identisch ist.
dann Exportiere beide Listen in eine Text-Datei/ CSV, nachdem du sie auf-/ absteigend sortiert hast und nutze Winmerge (als Beispiel) für den Vergleich.
Winmerge zeigt dir direkt, ob links/ rechts Einträge fehlen.
Oder du nutzt die Powershell (Anmerkung: in der ersten Zeile jeder CSV steht die Spaltenüberschrift "IBAN"):
$filea = Import-Csv C:\Powershell\TestCSVs\iban1.csv
$fileb = Import-Csv C:\Powershell\TestCSVs\iban2.csv
Compare-Object $filea $fileb -Property IBAN | export-csv C:\Powershell\TestCSVs\out.csv -NoTypeInformation
Oder du nutzt EXCEPT, wenn die Quelle SQL ist. Es gibt auf jeden Fall eine ganze Reihe an besseren Möglichkeiten als eine Addition.
Moin.
Was bringt die Summen-Prüfung? Inwieweit hilft es dir weiter zu wissen, dass die Listen von Inhalt her ungleich sind, d.h. dass mindestens eine von den 56 IBAN abweicht?
Du hast beide IBAN-Listen bereits in Excel vorliegen. Der Vergleich kann in dem Fall mit dem SVERWEIS visualisiert werden.
In Spalte A packst du die IBAN aus System 1, in Spalte B kommen die IBAN aus System 2, in Spalte C kommt:
Gruß
JoeToe
Was bringt die Summen-Prüfung? Inwieweit hilft es dir weiter zu wissen, dass die Listen von Inhalt her ungleich sind, d.h. dass mindestens eine von den 56 IBAN abweicht?
Du hast beide IBAN-Listen bereits in Excel vorliegen. Der Vergleich kann in dem Fall mit dem SVERWEIS visualisiert werden.
In Spalte A packst du die IBAN aus System 1, in Spalte B kommen die IBAN aus System 2, in Spalte C kommt:
=WENN(ISTNV(SVERWEIS(A1;B:B;1;FALSCH));"IBAN in Spalte_02 NICHT vorhanden";"IBAN auch in Spalte_02 vorhanden") Gruß
JoeToe
1
Und jetzt meine fünf Pfennige für den Ring:
1.
Und zwar über Power Query in Excel - dabei nimmst Du die Listen von System1 und System 2 als getrennte Tabellen(blätter) auf. Dann legst Du über Power Query eine neue Abfrage an, bei der Du diese beiden Tabellen über "Abfrage zusammenführen" miteinander über einen vollständigen Join in Beziehung setzt.
Wenn die beiden Tabellen eine ID für jede Datenzeile haben und diese ID bei beiden Tabellen dieselbe Bankverbindung betrifft, dann stützt sich der Join allein auf diese ID. Andernfalls nimmst Du die relevanten Datenspalten - ohne die IBAN - als Join-Grundlage. Man könnt auch anhand dieser relevanten Datenspalten zunächst über eine Zwischenabfrage eine Gesamtliste der Bankverbindungen (Liste 2 an Liste 1 anfügen und dann gruppieren) generieren, eine Indexspalte hinzufügen und schließlich den Datenzeilen in den beiden Tabellen die passenden ID's zuzuweisen. Die zugewiesenen ID's sind dann abermals die Join-Grundlage.
Im nächsten Schritt kannst Du die IBAN-Spalten der beiden Tabellen - am besten mit einer hinzugefügten Spalte als Ergebnisspeicher - vergleichen. Auf diese Weise findest Du nicht nur abweichende / fehlende Werte, sondern auch diejenigen Datenzeilen einer der beiden Tabellen, für die es gar kein Gegenstück in der anderen Tabelle gibt. Die übereinstimmenden Datenzeilen können dann weggefiltert werden.
Der Ergebnis- und Erkenntnisgehalt dürfte auf diesem Weg so mit am größten sein, zumal das Abfrageergebnis wieder als neue Tabelle vorliegt und somit auch weitergehende Bearbeitungsschritte ermöglicht.
Übrigens ließe sich das Befüllen der beiden Tabellen mit aktuellen Daten und die anschließende Aktualisierung der Abfrage(n) mit einem Makro vollautomatisieren.
2.
Wenn die beiden Listen ohnehin aus einer SQL-Datenbank kommen, dann ist die Verwendung von Excel großer Blödsinn, weil das in der Datenbank viel direkter und schneller realisierbar ist. Natürlich macht man das in der Datenbank ebenfalls über Abfragen. Ist die Quelle keine SQL-Datenbank, dann ist der Ansatz von Punkt 1. eine Art Simulation, als ob man in einer Datenbank arbeiten würde.
Viele Grüße
HansDampf06
PS: Mein Credo: Wenn Datenauswertung von Listen, Tabellen etc. , dann möglichst immer eine Datenbank(simulation) benutzen, weil es unter dem Strich in der Regel am effizientesten ist.
1.
Und zwar über Power Query in Excel - dabei nimmst Du die Listen von System1 und System 2 als getrennte Tabellen(blätter) auf. Dann legst Du über Power Query eine neue Abfrage an, bei der Du diese beiden Tabellen über "Abfrage zusammenführen" miteinander über einen vollständigen Join in Beziehung setzt.
Wenn die beiden Tabellen eine ID für jede Datenzeile haben und diese ID bei beiden Tabellen dieselbe Bankverbindung betrifft, dann stützt sich der Join allein auf diese ID. Andernfalls nimmst Du die relevanten Datenspalten - ohne die IBAN - als Join-Grundlage. Man könnt auch anhand dieser relevanten Datenspalten zunächst über eine Zwischenabfrage eine Gesamtliste der Bankverbindungen (Liste 2 an Liste 1 anfügen und dann gruppieren) generieren, eine Indexspalte hinzufügen und schließlich den Datenzeilen in den beiden Tabellen die passenden ID's zuzuweisen. Die zugewiesenen ID's sind dann abermals die Join-Grundlage.
Im nächsten Schritt kannst Du die IBAN-Spalten der beiden Tabellen - am besten mit einer hinzugefügten Spalte als Ergebnisspeicher - vergleichen. Auf diese Weise findest Du nicht nur abweichende / fehlende Werte, sondern auch diejenigen Datenzeilen einer der beiden Tabellen, für die es gar kein Gegenstück in der anderen Tabelle gibt. Die übereinstimmenden Datenzeilen können dann weggefiltert werden.
Der Ergebnis- und Erkenntnisgehalt dürfte auf diesem Weg so mit am größten sein, zumal das Abfrageergebnis wieder als neue Tabelle vorliegt und somit auch weitergehende Bearbeitungsschritte ermöglicht.
Übrigens ließe sich das Befüllen der beiden Tabellen mit aktuellen Daten und die anschließende Aktualisierung der Abfrage(n) mit einem Makro vollautomatisieren.
2.
Wenn die beiden Listen ohnehin aus einer SQL-Datenbank kommen, dann ist die Verwendung von Excel großer Blödsinn, weil das in der Datenbank viel direkter und schneller realisierbar ist. Natürlich macht man das in der Datenbank ebenfalls über Abfragen. Ist die Quelle keine SQL-Datenbank, dann ist der Ansatz von Punkt 1. eine Art Simulation, als ob man in einer Datenbank arbeiten würde.
Viele Grüße
HansDampf06
PS: Mein Credo: Wenn Datenauswertung von Listen, Tabellen etc. , dann möglichst immer eine Datenbank(simulation) benutzen, weil es unter dem Strich in der Regel am effizientesten ist.
