Ideja za ovotjedni savjet potekla je iz razgovora s dr. M, autorom sjajnog tjednog biltena Quicken tips.
Volim Quicken, ali sigurno ima svojih smetnji. Imam određeno napamet naučeno izvješće u Quickenu s kategorijama sa strane i mjesecima koji prelaze vrh. Quicken nudi priliku za ispis ovog izvješća, ali naravno, uvijek samo koristim naredbu Kopiraj da kopiram izvješće u međuspremnik, a zatim koristim Uredi> Zalijepi u Excelu da kopiram izvješće u međuspremnik. Ova je značajka mnogo brža od starije (i još uvijek dostupne) mogućnosti ispisa u datoteku .prn.
Ovdje se pojavljuju smetnje. Prvo, Quicken se nije potrudio kopirati naslove stupaca s izvješćem. Dakle, ručno moram unijeti nazive mjeseci u Excel. Dovoljno jednostavno. Drugo, kategorije koje se kopiraju u međuspremnik uključuju dosadan oblik obrisa za kategorije i podkategorije.
Quicken vam omogućuje upotrebu kategorija i potkategorija za klasifikaciju troškova. U izvješću slijeva, automobilski troškovi dodatno se dijele na osiguranje, benzin, popravke i registarske tablice. Možda sam loše obavio posao postavljanja svojih kategorija, ali smatram da imam neke kategorije u kojima želim vidjeti detalje potkategorije i druge kategorije u kojima bih radije samo vidio ukupnu kategoriju.
Također bih volio imati ovo izvješće u programu Excel. Bilo bi korisno sortirati ga prema ukupnom trošku, a zatim sortirati prema kategoriji. Svakako, mogao bih upotrijebiti poništavanje, ali želio bih kategorije koje su abecedne prirode. Ukratko, nije me briga za format obrisa koji koristi Quicken.
Dugo će se čitatelji sjećati mojeg prezira prema formatu obrisa koji koriste zaokretne tablice u Punjenje praznih ćelija zaokretne tablice pomoću posebnog savjeta Idi na. Ovdje imamo istu situaciju. Ako je Quickenov izvještaj samo posredni korak i želite razvrstati po kategorijama, format obrisa je užasan. Nakon razvrstavanja po ukupnim brojevima, a zatim po kategorijama, kategorija Auto: osiguranje pogrešno će se sortirati u odjeljak "I" izvješća. Za kategorije u kojima zadržavam samo ukupan iznos, oni će biti pogrešno poredani u odjeljak "T" izvješća.
Dvije su komunalne službe za koje sam mislio da će olakšati ovu situaciju. Uslužni program jedan naziva se kolaps. Kad se pozove, ova će makronaredba sažeti potkategoriju u jedan redak s odgovarajućim nazivom kategorije. U gornjem primjeru, izvođenje makronaredbe dok je pokazivač na ćeliji bilo gdje u redovima 34 do 38 zamijenit će kategoriju u A38 s "Računalo" i izbrisati retke 34 do 37.
Uslužni program dva namijenjen je kategorijama u kojima bih želio vidjeti detalje potkategorije, ali ne trebaju naslov, isprekidana crta podzbroja niti ukupan broj kategorija. Ovaj uslužni program zove se Ispunite. Pronaći će pravi naziv kategorije i dodati svaku potkategoriju u kategoriju cateogry. U gornjem primjeru, izvođenje makronaredbe dok je pokazivač na ćeliji bilo gdje u redovima od 24 do 30 rezultirat će promjenom ćelija A25: A28 u format poput "Auto: Osiguranje". Redovi 24, 29 i 30 bit će izbrisani.
S desne strane je moja poboljšana verzija izvještaja. Dodjeljivanjem kolapsa i popunjavanja vrućim tipkama uspio sam izvršiti ove promjene sa samo nekoliko tipki. Sada je lako razvrstati izvješće, znajući da se izvješće može vratiti u izvorni slijed sortiranjem kategorije.
Ako ste novi u makronaredbama, pregledajte Predstavljanje programa Excel VBA Editor.
Nakon što kopirate makronaredbu, možete dodijeliti interventnu tipku slijedeći ove korake:
- U izborniku Alati odaberite Makronaredbe, a zatim Makronaredbe
- Označite makronaredbu Fill. Kliknite Opcije. U polje Prečac unesite bilo koje slovo. Za punjenje koristim f. Kliknite U redu
- Označite makronaredbu kolapsa. Kliknite Opcije. Odaberite slovo za prečac, ali klonite se c, jer su Ctrl + c uobičajeni prečaci
- za Uredi> Kopiraj. Kliknite U redu
- Zatvorite dijaloški okvir makronaredbe s Odustani.
Kao dio svoje potrage za dizajniranjem dnevnog dodatka, ljetne pripravnice, Anhtuan Do kreirao je sljedeće makronaredbe.
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
