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.
Please also mark the comments that contributed to the solution of the article
Content-ID: 668259
Url: https://administrator.de/contentid/668259
Printed on: October 15, 2024 at 05:10 o'clock
6 Comments
Latest comment
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.
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
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
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.