Die Idee für den Tipp dieser Woche kam aus einem Gespräch mit Dr. M, Autor des großartigen wöchentlichen Quicken-Tipps-Newsletters.
Ich liebe Quicken, aber es hat sicherlich seine Belästigungen. Ich habe einen bestimmten auswendig gelernten Bericht in Quicken mit Kategorien an der Seite und Monaten an der Spitze. Quicken bietet die Möglichkeit, diesen Bericht zu drucken, aber natürlich verwende ich immer nur den Befehl Kopieren, um den Bericht in die Zwischenablage zu kopieren, und verwende dann Bearbeiten> Einfügen in Excel, um den Bericht in die Zwischenablage zu kopieren. Diese Funktion ist viel schneller als der ältere (und noch verfügbare) Druck in eine PRN-Dateioption.
Hier tauchen die Belästigungen auf. Erstens machte sich Quicken nicht die Mühe, die Spaltenüberschriften mit dem Bericht zu kopieren. Daher muss ich die Monatsnamen manuell in Excel eingeben. Einfach genug. Zweitens enthalten die Kategorien, die in die Zwischenablage kopiert werden, ein nerviges Gliederungsformat für Kategorien und Unterkategorien.
Mit Quicken können Sie Kategorien und Unterkategorien verwenden, um Ihre Ausgaben zu klassifizieren. Im Bericht links sind die Kfz-Ausgaben weiter nach Versicherungen, Benzin, Reparaturen und Kennzeichen aufgeschlüsselt. Vielleicht habe ich meine Kategorien schlecht eingerichtet, aber ich finde, dass ich einige Kategorien habe, in denen ich die Details der Unterkategorie sehen möchte, und andere Kategorien, in denen ich es vorziehen würde, nur die Kategoriesumme zu sehen.
Ich möchte diesen Bericht auch in Excel sortieren können. Es wäre nützlich, es nach Gesamtkosten zu sortieren und dann nach Kategorien zurückzusortieren. Sicher, ich könnte Rückgängig machen, aber ich möchte Kategorien, die alphabetischer Natur sind. Kurz gesagt, das von Quicken verwendete Gliederungsformat interessiert mich nicht.
Langjährige Leser werden sich an meine Verachtung für das Gliederungsformat erinnern, das von Pivot-Tabellen unter Füllen von Pivot-Tabellen-Leerzellen mit dem Tipp Gehe zu Spezial verwendet wird. Wir haben hier die gleiche Situation. Wenn der Quicken-Bericht nur ein Zwischenschritt ist und Sie nach Kategorien sortieren möchten, ist das Gliederungsformat schrecklich. Nach dem Sortieren nach Summen und dann nach Kategorie wird die Kategorie Auto: Versicherung im Abschnitt "I" des Berichts falsch sortiert. Für die Kategorien, in denen ich nur die Gesamtsumme behalte, werden sie falsch in den Abschnitt "T" des Berichts sortiert.
Es gab zwei Dienstprogramme, von denen ich dachte, dass sie diese Situation erleichtern würden. Dienstprogramm eins heißt Zusammenbruch. Beim Aufrufen reduziert dieses Makro eine Unterkategorie in eine einzelne Zeile mit einem richtigen Kategorienamen. Wenn Sie im obigen Beispiel das Makro ausführen, während sich der Zellenzeiger an einer beliebigen Stelle in den Zeilen 34 bis 38 befindet, wird die Kategorie in A38 durch "Computer" ersetzt und die Zeilen 34 bis 37 gelöscht.
Dienstprogramm zwei ist für die Kategorien vorgesehen, in denen ich das Detail der Unterkategorie sehen möchte, aber weder die Überschrift, die gestrichelte Zwischensummenlinie noch die Kategoriesumme benötige. Dieses Dienstprogramm heißt Füllen. Es findet den richtigen Kategorienamen und stellt jeder Unterkategorie die Kateogrie voran. Wenn Sie im obigen Beispiel das Makro ausführen, während sich der Zellenzeiger an einer beliebigen Stelle in den Zeilen 24 bis 30 befindet, werden die Zellen A25: A28 in ein Format wie "Auto: Versicherung" geändert. Die Zeilen 24, 29 und 30 werden gelöscht.
Rechts ist meine verbesserte Version des Berichts. Durch Zuweisen von Reduzieren und Füllen zu Tastenkombinationen konnte ich diese Änderungen mit nur wenigen Tastenanschlägen vornehmen. Es ist jetzt einfach, den Bericht zu sortieren, da Sie wissen, dass der Bericht durch Sortieren der Kategorie zu seiner ursprünglichen Reihenfolge zurückkehren kann.
Wenn Sie mit Makros noch nicht vertraut sind, lesen Sie Einführung in den Excel VBA-Editor.
Sobald Sie das Makro kopiert haben, können Sie einen Hotkey zuweisen, indem Sie die folgenden Schritte ausführen:
- Wählen Sie im Menü "Extras" die Option "Makros" und dann "Makro"
- Markieren Sie das Füllmakro. Klicken Sie auf Optionen. Geben Sie im Feld Verknüpfung einen beliebigen Buchstaben ein. Ich benutze f für Fill. OK klicken
- Markieren Sie das Reduktionsmakro. Klicken Sie auf Optionen. Wählen Sie einen Buchstaben für eine Verknüpfung, aber halten Sie sich von c fern, da Strg + c die häufigste Verknüpfung ist
- für Bearbeiten> Kopieren. OK klicken
- Schließen Sie den Makrodialog mit Abbrechen.
Im Rahmen seiner Suche nach einem Add-In für einen Tag, dem Sommerpraktikanten, erstellte Anhtuan Do die folgenden Makros.
Option Explicit Dim Flag, Flag2 As Boolean 'Flags to keep the Find Procedures running Dim HeaderRow, TotalRow As Integer 'Number of the Header and Total Rows Dim Counter As Integer 'Counter to ensure searches are relative to activecell Dim TempString, TempTest As String 'Strings that are used as temporary holders to compare Dim CategoryName As String 'Name of the category currently in Sub Collapse() 'To collapse the rows, run this macro Flag = False Flag2 = False Counter = -1 'Continue looping until HeaderRow is found Do Until Flag = True Counter = Counter + 1 Call FindHeader Loop 'Create CategoryName CategoryName = Left(CategoryName, Len(CategoryName) - 1) CategoryName = Trim(CategoryName) 'Assign HeaderRow HeaderRow = ActiveCell.Row - Counter Counter = 0 'Continue looping until TotalRow is found Do Until Flag2 = True Counter = Counter + 1 Call FindTotal Loop 'Assign TotalRow TotalRow = ActiveCell.Row + Counter 'Replace "TOTAL Category" with "Category" Cells(TotalRow, 1).Value = " " & CategoryName 'Deleting sub-category rows Rows(HeaderRow & ":" & TotalRow - 1).Delete Shift:=xlUp End Sub Sub Fill() 'To add the CategoryName to each of the types, run this macro Dim i As Integer Flag = False Flag2 = False Counter = -1 'Continue looping until HeaderRow is found Do Until Flag = True Counter = Counter + 1 Call FindHeader Loop 'Create CategoryName CategoryName = Left(CategoryName, Len(CategoryName) - 1) CategoryName = Trim(CategoryName) 'Assign HeaderRow HeaderRow = ActiveCell.Row - Counter Counter = 0 'Continue looping until TotalRow is found Do Until Flag2 = True Counter = Counter + 1 Call FindTotal Loop 'Assign TotalRow TotalRow = ActiveCell.Row + Counter 'Adding CategoryName and colon to the start of each sub category For i = HeaderRow + 1 To TotalRow - 2 TempString = Trim(Cells(i, 1).Value) Cells(i, 1).Value = " " & CategoryName & ": " & TempString Next i 'Deleting Header and Total Row Rows(TotalRow & ":" & TotalRow - 1).Delete Shift:=xlUp Rows(HeaderRow).Delete Shift:=xlUp End Sub Sub FindHeader() Dim i As Integer TempString = Cells(ActiveCell.Row - Counter, 1).Value 'Checking to see if in first row If (ActiveCell.Row - Counter) = 1 Then MsgBox "You are not in a collapsable row" End If 'Checking to see if in a valid row 'If it reaches a TOTAL before reaching a colon, then error If Left(Trim(TempString), 5) = "TOTAL" Then MsgBox "You are not in a collapsable row" End If 'Checking the String for a colon For i = 1 To Len(TempString) TempTest = Mid(TempString, i, 1) If TempTest = ":" Then CategoryName = TempString Flag = True Exit For End If Next i End Sub Sub FindTotal() Dim i As Integer 'Finding the TOTAL Row TempString = Cells(ActiveCell.Row + Counter, 1).Value TempString = Trim(TempString) If TempString = "TOTAL " & CategoryName Then Flag2 = True End If End Sub
