Myšlenka na tento týdenní tip přišla z rozhovoru s Dr. M., autorem skvělého týdenního zpravodaje Quicken tips.
Miluji Quicken, ale určitě má své mrzutosti. Mám v Quickenu určitou memorovanou zprávu s kategoriemi dole a měsíci jdoucími přes vrchol. Quicken nabízí příležitost tuto zprávu vytisknout, ale samozřejmě vždy pomocí příkazu Kopírovat zkopíruji zprávu do schránky a poté pomocí příkazu Úpravy> Vložit v aplikaci Excel zkopíruji zprávu do schránky. Tato funkce je mnohem rychlejší než starší (a stále dostupná) možnost tisku do souboru .prn.
Zde se objevují nepříjemnosti. Nejprve se Quicken neobtěžoval kopírovat záhlaví sloupců se zprávou. Takže ručně musím zadat názvy měsíců v aplikaci Excel. Dost jednoduché. Za druhé, kategorie, které se zkopírují do schránky, obsahují nepříjemný formát osnovy pro kategorie a podkategorie.
Quicken vám umožňuje použít kategorie a podkategorie ke klasifikaci vašich výdajů. Ve zprávě vlevo jsou výdaje na automobilový průmysl dále rozděleny podle pojištění, benzínu, oprav a SPZ. Možná jsem špatně nastavil své kategorie, ale zjistil jsem, že mám nějaké kategorie, kde chci vidět detail podkategorie, a další kategorie, kde bych raději viděl pouze celkovou kategorii.
Také bych chtěl mít možnost třídit tuto zprávu v aplikaci Excel. Bylo by užitečné seřadit je podle celkových nákladů a potom je seřadit zpět podle kategorie. Jistě, mohu použít zpět, ale chtěl bych kategorie, které mají abecední povahu. Stručně řečeno, je mi jedno, jaký formát osnovy používá Quicken.
Čtenáři si dlouho pamatují moje pohrdání formátem osnovy používaným kontingenčními tabulkami v části Vyplnit prázdné buňky kontingenční tabulky pomocí tipu Přejít na speciální. Máme tu stejnou situaci. Pokud je zpráva Quicken pouze přechodným krokem a chcete ji třídit podle kategorie, formát osnovy je příšerný. Po seřazení podle součtů a poté podle kategorie bude kategorie Auto: Pojištění chybně seřazena do části „I“ sestavy. U kategorií, kde ponechám pouze součet, budou nesprávně seřazeny do části „T“ sestavy.
Myslel jsem, že tuhle situaci ulehčí dva nástroje. Utility se nazývá kolaps. Po vyvolání toto makro sbalí podkategorii do jednoho řádku se správným názvem kategorie. Ve výše uvedeném příkladu spuštění makra, když je ukazatel buňky kdekoli v řádcích 34 až 38, nahradí kategorii v A38 „Počítačem“ a odstraní řádky 34 až 37.
Utility two is for the groups where I would like to see the subcategory detail, but do not need the Head, the dashed subtotal line, ani the category total. Tento nástroj se nazývá Vyplnit. Najde správný název kategorie a před každou podkategorii přidá předponu. Ve výše uvedeném příkladu bude mít spuštění makra kdekoli ukazatel buňky kdekoli v řádcích 24 až 30 za následek změnu buněk A25: A28 na formát jako „Auto: Pojištění“. Řádky 24, 29 a 30 budou smazány.
Vpravo je moje vylepšená verze zprávy. Přiřazením Sbalit a Vyplnit horkým klávesám jsem tyto změny dokázal provést pouhými několika stisky kláves. Nyní je snadné sestavu seřadit, protože víte, že se sestava může vrátit do původní sekvence seřazením kategorie.
Pokud jste v makrech nováčkem, přečtěte si úvod do aplikace Excel VBA Editor.
Po zkopírování makra můžete přiřadit klávesovou zkratku pomocí následujících kroků:
- V nabídce Nástroje zvolte Makra a poté Makro
- Zvýrazněte makro Vyplnit. Klikněte na Možnosti. Do pole Zkratka zadejte libovolné písmeno. Pro výplň používám f. Klikněte na OK
- Zvýrazněte makro sbalení. Klikněte na Možnosti. Vyberte písmeno pro zkratku, ale držte se dál od c, protože Ctrl + c je běžná zkratka
- pro Úpravy> Kopírovat. Klikněte na OK
- Zavřete dialogové okno maker pomocí Storno.
V rámci své snahy navrhnout doplněk za den, letní stážista, vytvořil Anhtuan Do následující makra.
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
