Šios savaitės patarimo idėja kilo kalbantis su daktare M, puikaus savaitinio „Quicken tips“ naujienlaiškio autore.
Aš myliu Quickeną, bet jis tikrai turi savo susierzinimų. Turiu tam tikrą atmintinę ataskaitą „Quicken“ su kategorijomis šone ir mėnesiais per viršų. „Quicken“ siūlo galimybę atsispausdinti šią ataskaitą, tačiau, žinoma, aš visada naudoju komandą „Kopijuoti“, kad nukopijuočiau ataskaitą į mainų sritį, o tada naudoju Redaguoti> Įklijuoti „Excel“, kad nukopijuočiau ataskaitą į mainų sritį. Ši funkcija yra daug greitesnė nei senesnio (ir vis dar pasiekiamo) spausdinimo į .prn failo parinktį.
Čia atsiranda susierzinimai. Pirma, Quickenas nesivargino nukopijuoti stulpelių antraščių su ataskaita. Taigi, rankiniu būdu turiu įvesti mėnesių pavadinimus programoje „Excel“. Pakankamai paprasta. Antra, kategorijos, kurios nukopijuojamos į mainų sritį, apima erzinantį kategorijų ir pakategorių metmenų formatą.
„Quicken“ leidžia naudoti kategorijas ir subkategorijas klasifikuojant išlaidas. Kairėje esančioje ataskaitoje automobilių išlaidos dar suskirstytos pagal draudimą, benziną, remontą ir valstybinius numerius. Galbūt aš prastai dirbau nustatydamas savo kategorijas, bet manau, kad turiu keletą kategorijų, kuriose norėčiau pamatyti subkategorijos detales, o kitas - kur norėčiau, kad būtų rodoma tik visa kategorija.
Taip pat norėčiau, kad galėčiau surūšiuoti šią ataskaitą „Excel“. Būtų naudinga rūšiuoti pagal visas išlaidas, tada rūšiuoti atgal pagal kategorijas. Aišku, galėčiau naudoti anuliavimą, bet norėčiau abėcėlės pobūdžio kategorijų. Trumpai tariant, man nerūpi „Quicken“ naudojamas kontūro formatas.
Ilgą laiką skaitytojai prisimins mano panieką apybraižų formato, kurį naudoja „Pivot“ lentelės užpildymo lentelės tuščios ląstelės, naudodamiesi „Patekti į specialųjį“ patarimu, forma. Čia yra ta pati situacija. Jei Quicken ataskaita yra tik tarpinis žingsnis ir norite sugebėti rūšiuoti pagal kategorijas, metmenų formatas yra siaubingas. Rūšiavus pagal sumas ir po kategorijos, „Auto: Insurance“ kategorija bus neteisingai surūšiuota į ataskaitos skyrių „I“. Kategorijoms, kuriose laikau tik bendrą sumą, jos bus neteisingai surūšiuotos į ataskaitos skiltį „T“.
Buvo dvi komunalinės paslaugos, kurios, mano manymu, palengvins šią situaciją. Pirmasis naudingumas vadinamas žlugimu. Kai bus iškviesta, ši makrokomanda subkategoriją sutrauks į vieną eilutę su tinkamu kategorijos pavadinimu. Aukščiau pateiktame pavyzdyje, paleidus makrokomandą, kai langelio žymeklis yra bet kurioje 34–38 eilutėje, A38 kategorija bus pakeista „Kompiuteriu“ ir ištrinta 34–37 eilutės.
Antrasis įrankis skirtas toms kategorijoms, kuriose norėčiau pamatyti išsamią subkategorijos informaciją, tačiau nereikia nei antraštės, nei brūkšninės tarpinės sumos eilutės, nei bendros kategorijos. Šis įrankis vadinamas Užpildyti. Jis suras tinkamą kategorijos pavadinimą ir prieš kiekvieną subkategoriją įrašys prieš cateogry. Aukščiau pateiktame pavyzdyje vykdant makrokomandą, kai langelio žymeklis yra bet kurioje 24–30 eilutėje, langeliai A25: A28 bus pakeisti į tokį formatą kaip „Auto: Insurance“. 24, 29 ir 30 eilutės bus ištrintos.
Dešinėje yra mano patobulinta pranešimo versija. Priskirdamas „Sutraukti“ ir „Užpildyti“ karštiems klavišams, galėjau atlikti šiuos pakeitimus tik keliais klavišų paspaudimais. Dabar lengva rūšiuoti ataskaitą, žinant, kad ataskaita gali grįžti į pradinę seką rūšiuodama kategoriją.
Jei makrokomandas naudojate pirmą kartą, peržiūrėkite „Excel VBA Editor“ pristatymą.
Nukopijavę makrokomandą, galite priskirti greitąjį klavišą atlikdami šiuos veiksmus:
- Meniu Įrankiai pasirinkite Makrokomandos, tada Makrokomanda
- Pažymėkite užpildymo makrokomandą. Spustelėkite Parinktys. Lauke Spartusis klavišas įveskite bet kurią raidę. Užpildymui naudoju f. Spustelėkite Gerai
- Pažymėkite sutraukimo makrokomandą. Spustelėkite Parinktys. Pasirinkite raidę, kad gautumėte nuorodą, tačiau laikykitės atokiau nuo c, nes „Ctrl“ + c yra įprastas spartusis klavišas
- redaguoti> Kopijuoti. Spustelėkite Gerai
- Uždarykite makrokomandos langą naudodami „Atšaukti“.
Vasaros praktikantas, siekdamas sukurti priedą, Anhtuan Do sukūrė šias makrokomandas.
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
