Idén till veckans tips kom från samtal med Dr. M, författare till det fantastiska nyhetsbrevet Quicken tips.
Jag älskar Quicken, men det har verkligen sina irritationer. Jag har en viss memorerad rapport i Quicken med kategorier längs sidan och månader som går överst. Quicken erbjuder möjlighet att skriva ut den här rapporten, men jag använder naturligtvis alltid bara kommandot Kopiera för att kopiera rapporten till Urklipp och sedan använda Redigera> Klistra in i Excel för att kopiera rapporten till Urklipp. Den här funktionen är mycket snabbare än den äldre (och fortfarande tillgängliga) skriv ut till ett .prn-filalternativ.
Här är där irritationer dyker upp. För det första brydde sig inte Quicken om att kopiera kolumnrubrikerna med rapporten. Så jag måste manuellt ange månadsnamnen i Excel. Enkelt nog. För det andra innehåller kategorierna som kopieras till Urklipp ett irriterande dispositionsformat för kategorier och underkategorier.
Quicken låter dig använda kategorier och underkategorier för att klassificera dina utgifter. I rapporten till vänster bryts bilutgifterna ytterligare av försäkringar, bensin, reparationer och registreringsskyltar. Kanske gjorde jag ett dåligt jobb med att ställa in mina kategorier, men jag tycker att jag har några kategorier där jag vill se underkategoridetaljer och andra kategorier där jag föredrar att bara se kategorin totalt.
Jag skulle också vilja kunna sortera den här rapporten i Excel. Det skulle vara användbart att sortera det efter total kostnad och sedan sortera tillbaka efter kategori. Visst, jag kunde använda ångra, men jag skulle vilja ha kategorier som är alfabetiska. Kort sagt, jag bryr mig inte om det dispositionsformat som Quicken använder.
Långtidsläsare kommer ihåg min förakt för det översiktsformat som används av pivottabeller i Fyll i pivottabell tomma celler med Gå till specialtips. Vi har samma situation här. Om Quicken-rapporten bara är ett mellansteg och du vill kunna sortera efter kategori är konturformatet hemskt. Efter att ha sorterat efter totala och sedan efter kategori, sorteras kategorin Auto: Försäkring felaktigt i avsnittet "I" i rapporten. För de kategorier där jag bara behåller summan sorteras de felaktigt i avsnittet "T" i rapporten.
Det fanns två verktyg som jag trodde skulle underlätta denna situation. Utility ett kallas kollaps. När det här makrot anropas kommer en underkategori att kollapsas till en rad med ett korrekt kategorinamn. I exemplet ovan kommer att köra makrot medan cellpekaren finns någonstans i raderna 34 till 38 att ersätta kategorin i A38 med "Dator" och radera raderna 34 till 37.
Verktyg två är för de kategorier där jag vill se underkategoridetaljerna, men behöver inte rubriken, den streckade delsumman eller kategorin totalt. Detta verktyg heter Fill. Den hittar rätt kategorinamn och prefixar varje underkategori med kateogrin. I exemplet ovan kommer körning av makrot medan cellpekaren finns någonstans i raderna 24 till 30 att cellerna A25: A28 ändras till ett format som "Auto: Försäkring". Rad 24, 29 och 30 raderas.
Till höger finns min förbättrade version av rapporten. Genom att tilldela Snabbtangenterna Dölj och fyll kunde jag göra dessa ändringar med bara några tangenttryckningar. Det är nu enkelt att sortera rapporten, med vetskap om att rapporten kan återgå till sin ursprungliga sekvens genom att sortera kategorin.
Om du inte är ny på makron, gå igenom Introduktion till Excel VBA Editor.
När du har kopierat makrot kan du tilldela en snabbtangent genom att följa dessa steg:
- Från verktygsmenyn väljer du makron och sedan makro
- Markera Fill macro. Klicka på Alternativ. Ange valfri bokstav i fältet Genväg. Jag använder f för Fill. Klicka på OK
- Markera kollapsmakrot. Klicka på Alternativ. Välj ett brev för en genväg, men håll dig borta från c, eftersom Ctrl + c är den vanliga genvägen
- för Redigera> Kopiera. Klicka på OK
- Stäng makrodialogen med Avbryt.
Som en del av hans strävan att utforma ett tillägg på en dag, sommarstudent, skapade Anhtuan Do följande makron.
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
