Zusammenfassung
In diesem Leitfaden erfahren Sie, wie Sie ein Ungleichgewicht in den MindBridge-Ergebnissen am besten beheben können, wenn Ihre HB-Datei formatiert ist und problemlos in MindBridge aufgenommen werden kann.
Dies ist eine fortgeschrittene und umfassende Komplettlösung. Falls Sie dies noch nicht getan haben, empfehlen wir Ihnen, die Lösungen auszuprobieren, die in Beträge oder Soll und Haben sind beim Importieren eines Hauptbuchs in MindBridge nicht saldiert zu finden sind.
Lösung
Betrachten Sie den folgenden Auszug aus einer Hauptbuchdatei:
Zunächst werden wir kurz darauf eingehen, wie Sie diese Datei formatieren. Wenn Sie möchten, können Sie diesen Teil überspringen und direkt zur Fehlerbehebung des Ungleichgewichts übergehen.
Die Probleme:
- Diese Datei ist nach Konten gruppiert: nur eine Zeile enthält Kontoinformationen (Zeilen 6, 10, 169), gefolgt von allen Transaktionen für diese Zeile (Zeilen 12-167, 171 aufwärts) (verstößt gegen Zeilenanforderung 1).
- Die Kontonummer steht normalerweise in Spalte B (Zeilen 10 und 169), manchmal aber auch in Spalte A (Zeile 6) (Verstoß gegen die Spaltenanforderung 1) (Die Kontobeschreibung steht jedoch immer in Spalte E für Kontozeilen)
- Es gibt nicht benötigte Gesamt-/Zwischensummenzeilen (9, 168) und leere Zeilen unter den Kontonummernzeilen (verstößt gegen die Gesamtanforderung 2)
Die Lösung (Formatierung erforderlich):
- Gruppierung nach Konto aufheben (ziehen Sie die Kontoinformationen nach unten in jede Transaktionszeile)
- Schließen Sie unwesentliche Gesamt-/Zwischensummen-/Leerzeilen (Zeilen 7-9, 11, 168-170) und unwesentliche Zeilen (Zeilen 1-4) oberhalb der Kopfzeile (Zeile 5) aus.
Wir fügen am Ende der Datei eine Spalte „Kontonummer“ hinzu, um die Kontonummer aus den Kontozeilen zu extrahieren und sie nach unten zu ziehen:
Kontonummer: Zelle K6 und darunter
=IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,IF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,K5))
-
SEARCH("????-??-??",B6), SEARCH("????-??-??",A6)die Position in der Zeichenfolge B6 oder A6 finden, an der eine Zeichenfolge mit dem Ausdruck "????-??-??" erscheint. Wenn er nicht gefunden wird, wird ein Fehler (#VALUE!) zurückgegeben. - der Ausdruck "????-??????" verwendet das Platzhalterzeichen ?, das mit jedem einzelnen Zeichen übereinstimmt. Somit bedeutet "????-??????" vier beliebige einzelne Zeichen, gefolgt von -, gefolgt von zwei beliebigen einzelnen Zeichen, gefolgt von -, gefolgt von zwei beliebigen einzelnen Zeichen. Zweck ist es zu prüfen, ob eine Kontonummer in der Zelle (B6 oder A6) gefunden wird.
- Das NOT(ISERROR()) um diese SEARCH()-Ausdrücke zeigt nur an, ob die Zellen B6 oder A6 Kontonummern enthalten (d. h., das Muster "????-??-??" wurde gefunden). Somit bedeutet
IF(NOT(ISERROR(SEARCH("????-??-??",B6))),B6,B6 zurückzugeben, wenn B6 eine Kontonummer ist, und dasIF(NOT(ISERROR(SEARCH("????-??-??",A6))),A6,das darauf folgt, bedeutet sonst, wenn A6 eine Kontonummer ist, A6 zurückgeben, und schließlich bedeutet das K5 am Ende ansonsten K5 zurückgeben (die Kontonummer, die in der Zeile darüber berechnet wurde, d. h. das ist, was die gefundenen Kontonummern nach unten zieht)
=IF(OR(NOT(ISERROR(SEARCH("????-??-??",B6))),NOT(ISERROR(SEARCH("????-??-??",A6)))),E6,L5)
Filtern, um nur Transaktionszeilen beizubehalten
Bei Prüfung der Spalte B (Datum) mit aktivierten Filtern sehen wir, dass es die Daten 2020 und 1900 gibt, der Rest sind Kontonummern:
Es stellt sich heraus, dass die Daten von 1900 in dieser Datei auch Datenmüll sind, den wir nicht behalten wollen, also werden wir sie im nächsten Schritt ebenfalls ausschließen.
Kopieren und Einfügen von Werten in eine neue Arbeitsmappe
Beachten Sie, dass alle Daten für 2020 die Transaktionszeilen sind, und wählen Sie nur die Daten für 2020 aus:
Markieren Sie dann die Kopfzeile und die ausgewählten Transaktionszeilen (d. h. in der Filteransicht ab Zeile 5 – Sie können in der Zelle A5 beginnen, Strg-Umschalt-Ende drücken, um an das Ende der Datei zu gelangen, und die Position nach Bedarf anpassen), kopieren Sie sie und fügen Sie dann spezielle Werte in eine neue Arbeitsmappe ein.
Fehlerbehebung des Ungleichgewichts
Wir haben nun eine gut formatierte Datei, die wie folgt aussieht:
Aber wir stellen fest, dass der Saldo nicht ausgeglichen ist, weil die Sollbeträge nicht mit den Habenbeträgen übereinstimmen!
Kontozwischensummen extrahieren
Glücklicherweise stellen wir fest, dass diese Datei Kontozwischensummen enthält (Zeilen, die wie Zeile 168 im ersten Bild oben auf dieser Seite aussehen). Wir erkennen diese Zwischensummenzeilen daran, dass das Datum (Spalte B) leer ist und die Nettoveränderung (Spalte I) eine Zahl ist (d. h. nicht leer).
Zurück in der ursprünglichen HB-Datei, in der wir die Formel zur Spalte K hinzugefügt haben, um die Kontonummer zu extrahieren und nach unten zu ziehen, und mit immer noch aktivierten Filtern, filtern wir nach Datum (Spalte B) ist leer und Spalte I ist nicht leer:
Beachten Sie, dass die tatsächlichen Spalten für diese Kontozwischensummenzeilen nicht mit den Spaltenüberschriften übereinstimmen – sie haben ein etwas anderes Format als die Transaktionszeilen, nämlich eine nach links verschobene Spalte – der Anfangssaldo steht tatsächlich in Spalte E und der Endsaldo in Spalte I. Aber da wir die Zwischensummen für den Import in MindBridge nicht formatieren, ist das in Ordnung. Wir müssen nur die von uns benötigten Spalten (Spalten E-I und K) kopieren und in ein neues Arbeitsblatt in der Arbeitsmappe mit dem formatierten Hauptbuch einfügen, das wir zur Fehlerbehebung des Ungleichgewichts verwenden werden.
Wir werden diese Arbeitsblatt Zwischensummen benennen und die Kopfzeilen umbenennen, um Verwechslungen zu vermeiden:
Pivot-Tabelle über die formatierten Hauptbuchdaten
Als Nächstes erstellen wir in einem neuen Arbeitsblatt eine Pivot-Tabelle mit den formatierten Hauptbuchdaten, in der wir die Soll- und Habenbeträge nach Kontonummern summieren:
- Wählen Sie alle formatierten Daten aus (Sie können am Anfang beginnen und Strg-Umschalt-Ende drücken, um an das Ende der Datei zu gelangen, und die Position nach Bedarf anpassen), gehen Sie zu Einfügen -> Tabellen -> PivotTable.
Die Tabelle/Bereich: zeigt automatisch den Bereich an, den Sie für die Daten ausgewählt haben (in unserem Fall haben wir das Arbeitsblatt mit den formatierten GL-Daten „formatedGL“ (formatiertes HB) genannt und die Daten reichen von A1 bis G16519).
- Lassen Sie das Optionsfeld „Neues Arbeitsblatt“ aktiviert (was standardmäßig der Fall sein sollte), und klicken Sie danach auf OK.
Dies führt Sie zu einem neuen Arbeitsblatt, das wie folgt aussieht:
Wir möchten Soll und Haben nach Kontonummer summieren.
- Blättern Sie in der Liste FIELD NAME (Feldname) nach unten und ziehen Sie die „Kontonummer“ in den Bereich Zeilen:
- Ziehen Sie dann Soll und Haben in den Bereich Werte:
- Jetzt sollte Ihre Pivot-Tabelle wie folgt aussehen:
Nachschlagen und Vergleichen mit Kontozwischensummen
Nun möchten wir diese Kontosummen mit den Summen vergleichen, die wir aus dem ursprünglichen Hauptbuch extrahiert und im Arbeitsblatt Zwischensummen gespeichert haben:
Kontozwischensummen: Formel für die Soll-Spalte
=SUMIF(subtotals!$G:$G,$A4,subtotals!B:B)
Erinnern Sie sich daran, dass im Arbeitsblatt Zwischensummen die Spalte G die Kontonummer und die Spalten B und C die Beträge für Soll und Haben enthalten:
Spalte A im Arbeitsblatt der Pivot-Tabelle enthält die Kontonummern. Diese SUMIF-Formel ruft also die Soll- und Habensummen aus dem Arbeitsblatt „Zwischensummen“ für jede Kontonummer in der Pivot-Tabelle ab.
Kontozwischensummen: Formel für die Haben-Spalte (einfach die Formel für die Soll-Spalte kopieren und einfügen)
=SUMIF(subtotals!$G:$G,$A4,subtotals!C:C)
Geben Sie abschließend die Formeln in den Spalten F und G ein:
=B4-D4 =C4-E4
Wählen Sie die Spalten D-G und fügen Sie Filter hinzu:
Wir sehen, dass es einige Unterschiede in der Soll-Spalte gibt:
Jetzt haben wir Kontonummern identifiziert, die wir auf Unterschiede untersuchen müssen.
Vergleich von Zwischensummen mit formatiertem HB ausgehend von den Zwischensummen
Beachten Sie, dass Kontonummern in der ursprünglichen HB-Zwischensummenliste, die nicht in dem formatierten HB enthalten sind, hier nicht angezeigt werden, aber für die Unterschiede verantwortlich sein könnten. Um diese zu erkennen, können Sie dem Zwischensummen-Arbeitsblatt SUMIF()-Formeln hinzufügen, die sich auf das formatierte Hauptbuch beziehen, um die Kontosummen aus dem formatierten Hauptbuch zu ziehen:
Möchten Sie sonst noch etwas wissen? Sie können auch mit uns chatten oder eine Anfrage für weitere Unterstützung zusenden.