Fragen? Wir haben die Antworten.

Leitfaden zur Datenformatierung: Einfaches Auflösen der Gruppierung mehrerer Spalten, Ausfüllen leerer Kontonummern (gängiges QuickBooks-Beispiel)

  • Aktualisiert

Zusammenfassung

Lernen Sie, wie Sie die Gruppierung einer Datendatei aufheben können, indem Sie mehrere Spalten ausfüllen, um eine von MindBridge unterstützte Datei zu erzeugen. Dies ist ein häufiges Problem bei vielen Datenexporten aus Buchhaltungssystemen wie QuickBooks. 

Diese Methode kann sogar verwendet werden, wenn nicht alle Spalten in der ersten Zeile der Gruppe einen Wert haben, und um leere Kontonummern auszufüllen, wenn zwar die Kontobeschreibung, nicht aber die Kontonummer vorhanden ist. 

Hinweis: Versuchen Sie, wenn möglich, zuerst einen Direktverbinder einzurichten oder lassen Sie Ihren Kunden eine Verbindung herstellen, um die Daten direkt in MindBridge aufzunehmen. Oder folgen Sie diesen Anweisungen, um die Daten aus QuickBooks Online oder QuickBooks Desktop zu exportieren, damit Sie die Gruppierung gar nicht erst aufheben müssen. Diese Anleitung ist für Situationen gedacht, in denen es nicht möglich ist, einen Direktverbinder einzurichten oder einen besseren Export zu erhalten.


Lösung

Betrachten Sie den folgenden Auszug aus einer QuickBooks-Hauptbuchdatei:

mceclip1.png

Die Probleme

  1. Diese Datei ist nach Transaktion gruppiert; Datum, Transaktionsart, Nr. und Name sind nur in der ersten Zeile der Transaktionsgruppe vorhanden und müssen nach unten gezogen werden.
  2. Konto-Nr. ist nicht immer vorhanden (siehe zum Beispiel Zeilen 8, 13, 17, 25), aber Konto schon. Das Konto kann der Konto-ID zugeordnet werden, aber die Kontonummer nicht, es sei denn, die Leerstellen werden ausgefüllt.
  3. Es gibt nicht benötigte Zwischensummenzeilen für Transaktionen (9, 14, 19, 26)

Die Lösung (Formatierung erforderlich)

  1. Ziehen Sie Datum, Transaktionsart, Nr. und Name nach unten in jede Transaktionszeile.
  2. Füllen Sie das leere Feld „Kontonummer“ mit dem Wert in Konto
  3. Schließen Sie unwesentliche Gesamt-/Zwischensummen-/Leerzeilen (9, 14, 19, 26) und unwesentliche Zeilen (Zeilen 1-4) oberhalb der Kopfzeile (Zeile 5) aus.

Wir werden die überarbeiteten Spalten an das Ende der Datei setzen.

  • Datum und Transaktionsart sind immer in der ersten Zeile der Transaktionsgruppe vorhanden, aber Nr. und Name sind manchmal für eine Gruppe leer. Siehe die 3. und 4. Transaktionsgruppe, die in den Zeilen 16 und 21 beginnen.
  • Unsere Logik besteht darin, den Wert aus der ursprünglichen Spalte zu nehmen, wenn wir uns in der ersten Zeile der Transaktion befinden, oder den vorherigen Wert, den wir gefunden haben, wenn wir uns nicht in der ersten Zeile der Transaktion befinden.

Überarbeitetes Datum: Zelle L6 und darunter

mceclip3.png

=IF(LEN($B6)>0,IF(LEN(B6)>0,B6,""),L5)
  • LEN($B6)>0
    testet, ob es einen Wert in Spalte B gibt, indem geprüft wird, ob die Länge größer als 0 ist. Da, wie wir festgestellt haben, das Datum immer in der ersten Zeile der Transaktionsgruppe steht, dient dies als Überprüfung, ob wir uns in der ersten Zeile der Transaktion befinden. Wir setzen $ vor das B, um das Kopieren der Formel in die übrigen Spalten (Transaktionsart, Nr., Name) zu erleichtern, da wir immer das Vorhandensein von Datum überprüfen werden, um festzustellen, ob wir uns in der ersten Zeile der Transaktion befinden.
  • IF(LEN(B6)>0,B6,"")
    gibt das Datum zurück, wenn es vorhanden ist, "" (leere Zeichenfolge), wenn es nicht vorhanden ist. Dies kann vereinfacht werden, indem einfach B6 für die Spalte Datum verwendet wird, da das Datum immer in der ersten Zeile der Transaktionsgruppe vorhanden ist, aber diese Logik wird verwendet, um die gleichen Formeln für Nr. und Name zu kopieren und einzufügen, die, wie oben erwähnt, nicht immer vorhanden sind. Da Excel beim Verweis auf eine leere Zelle 0 anstelle eines Leerzeichens anzeigt, müssen wir dem Programm sagen, dass es "" zurückgeben soll, damit es eine leere Zelle anzeigt.

Hinweis: Die obigen Formeln, die auf leere Zeichenfolgen prüfen, gehen davon aus, dass die leer aussehenden Zellen in Excel tatsächlich leer sind. Wenn diese leer erscheinenden Zellen tatsächlich Leerzeichen, geschützte Leerzeichen oder Phantomdaten enthalten, ersetzen Sie alle Instanzen von B6 innerhalb der Funktion LEN() durch:

TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," ")))
  • TRIM() entfernt Leerzeichen.
  • CHAR(160) steht für das geschützte Leerzeichen, und die SUBSTITUTE()-Formel um dieses Zeichen herum entfernt die geschützten Leerzeichen (Hinweis: Verwenden Sie auf einem Mac CHAR(202) statt CHAR(160)).

Die endgültige Formel würde also lauten:

=IF(LEN(TRIM(CLEAN(SUBSTITUTE($B6,CHAR(160)," "))))>0,IF(LEN(TRIM(CLEAN(SUBSTITUTE(B6,CHAR(160)," "))))>0,B6,""),L5)

 

Überarbeitete Transaktionsart, Nr., Name

Kopieren Sie die für das überarbeitete Datum gefundene Formel, fügen Sie sie ein und ziehen Sie sie nach unten.

mceclip4.png

Die Formeln in den Zellen M6, N6 und O6 sollten die Folgenden sein:

=IF(LEN($B6)>0,IF(LEN(C6)>0,C6,""),M5)
=IF(LEN($B6)>0,IF(LEN(D6)>0,D6,""),N5)
=IF(LEN($B6)>0,IF(LEN(F6)>0,F6,""),O5)

Überarbeitete Konto-Nr.

Unsere Logik zum Ausfüllen der Leerstellen für die Konto-Nr. in Spalte P besteht darin, die ursprüngliche Konto-Nr. aus Spalte H zu nehmen, wenn sie vorhanden ist (Länge ist größer als 0), Konto aus Spalte I, wenn sie nicht vorhanden ist (Länge ist nicht größer als 0):

mceclip5.png

=IF(LEN(H6)>0,H6,I6)

Filtern, um nur Transaktionszeilen beizubehalten

Wir beobachten, dass jede Transaktionszeile in der Originaldatei ein Konto hat. Filtern Sie bei aktivierten Filtern Leerzeichen aus Spalte I (Konto) heraus:

mceclip6.png

Kopieren und Einfügen von Werten in eine neue Arbeitsmappe

Markieren Sie dann die Kopfzeile und die ausgewählten Transaktionszeilen (d. h. in der Filteransicht ab Zeile 5 – Sie können in der Zelle B5 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.


Möchten Sie sonst noch etwas wissen?

Teilen Sie Ihre Gedanken und Fragen mit der Community! Sie können auch mit uns chatten oder eine Anfrage für weitere Unterstützung zusenden.


Ähnliche Artikel

War dieser Beitrag hilfreich?