Myšlienka tohto týždňa na tip vyplynula z rozhovoru s Dr. M., autorom skvelého týždenníka Newsletter Quicken tips.
Milujem Quicken, ale určite má svoje nepríjemnosti. Mám určitú správu naspamäť v Quickene s kategóriami dole a mesiacmi cez vrchol. Aplikácia Quicken ponúka možnosť vytlačiť túto správu, ale samozrejme vždy pomocou príkazu Kopírovať správu skopírujem do schránky a potom pomocou príkazov Upraviť> Prilepiť v programe Excel skopírujem správu do schránky. Táto funkcia je oveľa rýchlejšia ako staršia (a stále dostupná) možnosť tlače do súboru .prn.
Tu nastanú nepríjemnosti. Quicken sa spočiatku neobťažoval kopírovať nadpisy stĺpcov so správou. Takže manuálne musím zadať názvy mesiacov v programe Excel. Dosť jednoduché. Po druhé, kategórie, ktoré sa skopírujú do schránky, obsahujú nepríjemný formát obrysov pre kategórie a podkategórie.
Aplikácia Quicken umožňuje klasifikovať výdavky pomocou kategórií a podkategórií. V správe vľavo sú výdavky na automobilový priemysel ďalej rozdelené podľa poistenia, benzínu, opráv a ŠPZ. Možno som zle nastavil svoje kategórie, ale zistil som, že mám niektoré kategórie, v ktorých chcem vidieť detail podkategórie, a ďalšie kategórie, v ktorých by som najradšej videl iba celkovú kategóriu.
Tiež by som chcel mať možnosť tento prehľad zoradiť v programe Excel. Bolo by užitočné triediť ich podľa celkových výdavkov a potom späť podľa kategórií. Iste, mohol by som použiť späť, ale chcel by som kategórie, ktoré majú abecedný charakter. Stručne povedané, nezaujíma ma formát osnovy používaný programom Quicken.
Čitatelia si dlho budú pamätať moje pohŕdanie formátom osnovy, ktorý používajú kontingenčné tabuľky v tipe Vyplňte prázdne bunky kontingenčnej tabuľky pomocou tipu Prejsť na špeciálnu. Máme tu rovnakú situáciu. Ak je správa Quicken iba medzikrokom a chcete byť schopní triediť podľa kategórie, formát obrysu je strašný. Po zoradení podľa celkových súm a potom podľa kategórie bude kategória Auto: Poistenie nesprávne roztriedená do časti „I“ prehľadu. Pre kategórie, kde ponechám iba celkový počet, budú nesprávne zoradené do časti „T“ prehľadu.
Myslel som, že existujú dve pomôcky, ktoré by túto situáciu uľahčili. Úžitkovému sa hovorí kolaps. Po vyvolaní toto makro zbalí podkategóriu do jedného riadku so správnym názvom kategórie. Vo vyššie uvedenom príklade sa pri spustení makra, keď je ukazovateľ bunky kdekoľvek v riadkoch 34 až 38, nahradí kategória v A38 výrazom „Počítač“ a odstránia sa riadky 34 až 37.
Obslužný program dva je určený pre kategórie, kde by som rád videl podrobnosti podkategórie, ale nepotrebujem nadpis, prerušovanú medzisúčet ani celkovú kategóriu. Tento nástroj sa nazýva Vyplniť. Nájde správny názov kategórie a pred každú podkategóriu uvedie kategórie. V príklade vyššie bude mať spustenie makra, zatiaľ čo ukazovateľ bunky kdekoľvek v riadkoch 24 až 30, za následok zmeny buniek A25: A28 na formát ako napríklad „Auto: Poistenie“. Riadky 24, 29 a 30 sa odstránia.
Vpravo je moja vylepšená verzia správy. Priradením klávesových skratiek Zbaliť a Vyplniť som mohol vykonať tieto zmeny iba niekoľkými stlačeniami klávesov. Teraz je ľahké prehľad zoradiť s vedomím, že sa prehľad môže vrátiť do pôvodnej sekvencie zoradením kategórie.
Ak ste v oblasti makier nováčikom, prečítajte si tému Predstavujeme editor Excel VBA.
Po skopírovaní makra môžete priradiť klávesovú skratku podľa týchto pokynov:
- V ponuke Nástroje vyberte položku Makrá a potom Makro
- Zvýraznite makro Vyplniť. Kliknite na Možnosti. Do poľa Skratka zadajte ľubovoľné písmeno. Na vyplnenie používam f. Kliknite na tlačidlo OK
- Zvýraznite makro zbalenia. Kliknite na Možnosti. Vyberte písmeno pre skratku, ale držte sa ďalej od písmena c, pretože Ctrl + c je bežná skratka
- pre Úpravy> Kopírovať. Kliknite na tlačidlo OK
- Zatvorte dialógové okno makra príkazom Zrušiť.
V rámci svojej snahy navrhnúť letný stážista denného doplnku vytvoril Anhtuan Do nasledujúce makrá.
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
