Ideja za namig tega tedna je izhajala iz pogovora z dr. M, avtorjem odličnega tedenskega glasila Quicken tips.
Obožujem Quicken, vendar ima vsekakor svoje sitnosti. V Quickenu imam določeno zapomnjeno poročilo s kategorijami po mesecih in mesecih na vrhu. Quicken ponuja možnost tiskanja tega poročila, seveda pa vedno uporabim samo ukaz Kopiraj, da kopiram poročilo v odložišče, nato pa uporabim Uredi> Prilepi v Excelu, da kopiram poročilo v odložišče. Ta funkcija je veliko hitrejša od starejše (in še vedno na voljo) možnosti tiskanja v datoteko .prn.
Tu se pojavijo sitnosti. Prvič, Quicken se ni trudil kopirati naslovov stolpcev s poročilom. Torej, ročno moram v Excel vnesti imena mesecev. Dovolj preprosto. Drugič, kategorije, ki se kopirajo v odložišče, vključujejo moteč oris za kategorije in podkategorije.
Quicken omogoča uporabo kategorij in podkategorij za razvrščanje stroškov. V poročilu na levi so avtomobilski stroški dodatno razdeljeni na zavarovanja, bencin, popravila in registrske tablice. Mogoče sem slabo opravil nastavitev svojih kategorij, vendar se mi zdi, da imam nekatere kategorije, v katerih želim videti podrobnosti o podkategorijah in druge kategorije, kjer bi raje videl celotno kategorijo.
To poročilo bi rad tudi razvrstil v Excelu. Koristilo bi ga razvrstiti po skupnih stroških in nato razvrstiti po kategorijah. Seveda bi lahko uporabil razveljavitev, vendar bi želel kategorije, ki so abecedne narave. Skratka, vseeno mi je za orisno obliko, ki jo uporablja Quicken.
Dolgo časa se bodo bralci spominjali mojega prezira glede oblike orisa, ki jo uporabljajo vrtilne tabele v naslovu Izpolni prazne celice vrtilne tabele s pomočjo nasveta Go To Special. Tu imamo enako situacijo. Če je poročilo Quicken zgolj vmesni korak in želite razvrstiti po kategorijah, je oblika orisa grozljiva. Po razvrščanju po skupnih vsotah in nato po kategorijah bo kategorija Avto: zavarovanje napačno razvrščena v odsek "I" poročila. Za kategorije, v katerih hranim samo vsoto, bodo napačno razvrščene v odsek "T" poročila.
Mislila sem, da bi to olajšala dve pripomočki. Utility one se imenuje kolaps. Ko bo poklican, bo ta makro strnil podkategorijo v eno vrstico z ustreznim imenom kategorije. V zgornjem primeru bo izvajanje makra, medtem ko je kazalec celice kjer koli v vrsticah od 34 do 38, nadomestilo kategorijo v A38 z "Računalnik" in izbrisalo vrstice od 34 do 37.
Pomožni program dva je za kategorije, kjer bi rad videl podrobnosti o podkategoriji, vendar ne potrebujem naslova, črtkane vmesne črte niti skupne kategorije. Ta pripomoček se imenuje Fill. Poiskal bo ustrezno ime kategorije in pred podkategorijo predpono zapisal s kategorijo. V zgornjem primeru bo zagon makra, medtem ko je kazalec celice kjer koli v vrsticah od 24 do 30, povzročil spremembo celic A25: A28 v obliko, kot je »Samodejno: Zavarovanje«. Vrstice 24, 29 in 30 bodo izbrisane.
Na desni je moja izboljšana različica poročila. Z dodelitvijo bližnjic in izpolnitev bližnjičnim tipkam sem te spremembe lahko izvedel z nekaj pritiski tipk. Poročilo je zdaj enostavno razvrstiti, saj vemo, da se lahko poročilo vrne v prvotno zaporedje z razvrščanjem kategorije.
Če makrov še ne poznate, preglejte Uvod v urejevalnik Excel VBA.
Ko kopirate makro, lahko bližnjično tipko dodelite po naslednjih korakih:
- V meniju Orodja izberite Makri in nato Makro
- Označite makro Fill. Kliknite Možnosti. V polje Bližnjica vnesite poljubno črko. Za polnjenje uporabljam f. Kliknite V redu
- Označite strnjeni makro. Kliknite Možnosti. Za bližnjico izberite črko, vendar se izogibajte c, saj je Ctrl + c običajna bližnjica
- za Edit> Copy. Kliknite V redu
- Zaprite pogovorno okno makra z možnostjo Prekliči.
Kot del svojega prizadevanja za oblikovanje dodatka na dan, poletnega pripravnika, je Anhtuan Do ustvaril naslednje makre.
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
