Selle nädala näpunäite idee tuli vestlusest suurepärase iganädalase Quicken tips infolehe autori Dr. M-ga.
Ma armastan Quickenit, kuid kindlasti on sellel ka pahandusi. Mul on Quickenis kindel pähe kantud aruanne, kus kategooriad on allpool ja kuud üleval. Quicken pakub võimalust selle aruande printimiseks, kuid loomulikult kasutan aruande kopeerimiseks lõikelauale alati käsku Kopeeri ja seejärel arhiivi lõikelauale kopeerimiseks käsku Muuda> Kleebi Excelis. See funktsioon on palju kiirem kui vanem (ja endiselt saadaval) print .prn-faili printimiseks.
Siin ilmnevad pahandused. Esiteks ei vaevunud Quicken aruandega veergude päiseid kopeerima. Niisiis, pean kuude nimed Excelis käsitsi sisestama. Piisavalt lihtne. Teiseks sisaldavad kategooriad, mis kopeeritakse lõikelauale, kategooriate ja alamkategooriate tüütu kontuurivorming.
Quicken võimaldab teil kulude liigitamiseks kasutada kategooriaid ja alamkategooriaid. Vasakpoolses aruandes jaotatakse sõidukikulud kindlustuse, bensiini, remondi ja numbrimärkide kaupa. Võib-olla tegin oma kategooriate seadistamisel kehva tööd, kuid leian, et mul on mõned kategooriad, kus ma tahan näha alamkategooria üksikasju, ja muud kategooriad, kus ma eelistaksin lihtsalt näha kategooria koguarvu.
Samuti tahaksin, et saaksin selle aruande Excelis sortida. Kasulik oleks sorteerida see kogukulu järgi, seejärel sortida kategooria järgi. Muidugi, ma saaksin kasutada tagasivõtmist, kuid sooviksin kategooriaid, mis oleksid oma olemuselt tähestikulised. Lühidalt öeldes ei huvita mind Quicken'i kasutatav kontuurivorming.
Pikka aega mäletavad lugejad minu põlgust kontuurivormingu suhtes, mida pivot-tabelid kasutavad jaotises Pöördustabeli tühjade lahtrite täitmine, kasutades nuppu Mine spetsiaalselt. Meil on siin sama olukord. Kui Quickeni aruanne on lihtsalt vaheetapp ja soovite, et saaksite kategooria järgi sortida, on kontuurivorming kohutav. Pärast sortimist kogusummade ja kategooriate järgi sorteeritakse kategooria Auto: Kindlustus valesti aruande jaotisse "I". Kategooriate puhul, kus hoian ainult koguarvu, sorteeritakse need valesti aruande jaotisse T.
Seal oli kaks utiliiti, mis minu arvates leevendasid seda olukorda. Ühist utiliiti nimetatakse kollapsiks. Kui see käivitatakse, ahendab see makro alamkategooria üheks rea kategooria nimeks. Ülaltoodud näites asendab makro käivitamine ajal, kui kärjeosuti on kusagil ridades 34 kuni 38, asendatakse kategooria A38 kategoorias "Arvuti" ja kustutatakse read 34 kuni 37.
Teine utiliit on mõeldud kategooriatele, kus sooviksin näha alamkategooria üksikasju, kuid ei vaja pealkirja, katkendlikku vahesummat ega kategooria kogusummat. Selle utiliidi nimi on Täida. See leiab õige kategooria nime ja lisab igale alamkategooriale kateograafia. Ülaltoodud näites muudetakse makro käivitamine ajal, kui kärjepunkt on reas 24 kuni 30, lahtrid A25: A28 muudetakse selliseks vorminguks nagu "Auto: Kindlustus". Ridad 24, 29 ja 30 kustutatakse.
Paremal on minu aruande täiustatud versioon. Määrates kiirvõtmetele ahendamise ja täitmise, suutsin need muudatused teha vaid mõne klahvivajutusega. Aruannet on nüüd lihtne sorteerida, teades, et aruanne saab kategooria sorteerimisega tagasi oma algsesse järjestusse.
Kui olete makrode kasutamisel alles uus, lugege Exceli VBA-redaktori tutvustamine.
Pärast makro kopeerimist saate määrata kiirklahvi, toimides järgmiselt.
- Valige menüüst Tööriistad Makrod ja siis Makro
- Tõstke esile täitmise makro. Klõpsake nuppu Suvandid. Sisestage väljale Otsetee suvaline täht. Kasutan täitmiseks f. Klõpsake nuppu OK
- Tõstke esile ahendatud makro. Klõpsake nuppu Suvandid. Valige otsetee jaoks täht, kuid hoiduge c-st eemal, kuna Ctrl + c on tavaline otsetee
- menüü Redigeerimine> Kopeeri jaoks. Klõpsake nuppu OK
- Sulgege makrodialoog tühistamise abil.
Anhtuan Do lõi suvepraktikumi osana ühe lisandmooduli kujundamise püüdlustes järgmised makrod.
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
