Az e heti tipp ötlete Dr. M-lel, a nagy heti Quicken tips hírlevelünk írójával folytatott beszélgetésből származott.
Szeretem a Quicken-t, de minden bizonnyal megvannak a bosszúságai. Van egy bizonyos memorizált jelentésem a Quicken-ben, kategóriákkal lefelé és hónapokkal a tetején. A Quicken felajánlja a jelentés kinyomtatásának lehetőségét, de természetesen mindig csak a Másolás paranccsal másolom a jelentést a vágólapra, majd a Szerkesztés> Beillesztés az Excel alkalmazásban másolom a jelentést a vágólapra. Ez a szolgáltatás sokkal gyorsabb, mint a régebbi (és még mindig elérhető) .prn fájlbe nyomtatás.
Itt bukkannak fel a bosszúságok. Először Quicken nem vette a fáradságot, hogy lemásolja az oszlopfejléceket a jelentéssel. Tehát kézzel kell beírnom a hónap nevét az Excelbe. Elég egyszerű. Másodszor, a vágólapra másolt kategóriák egy idegesítő vázlatformátumot tartalmaznak a kategóriákhoz és alkategóriákhoz.
A Quicken lehetővé teszi kategóriák és alkategóriák használatát a kiadások osztályozásához. A bal oldali jelentésben az autóipari költségeket tovább bontják biztosítások, benzin, javítások és rendszámok. Talán rosszul végeztem a kategóriáim beállítását, de azt tapasztaltam, hogy vannak olyan kategóriáim, ahol szeretném látni az alkategória részleteit, és más kategóriákat, ahol inkább csak a kategóriák összesítését látnám.
Szeretném ezt a jelentést is rendezni az Excelben. Hasznos lenne összköltség szerint rendezni, majd kategóriák szerint rendezni vissza. Persze, használhatnám a visszavonást, de szeretnék olyan kategóriákat, amelyek ábécé jellegűek. Röviden: nem érdekel a Quicken által használt vázlatformátum.
Sokáig az olvasók emlékezni fognak a megvetésemre a pivot táblák által használt körvonal formátumban a Töltsd ki az elfordulási táblázat üres celláit a Ugrás a speciálisra tipp segítségével. Itt ugyanez a helyzet. Ha a Quicken jelentés csupán egy közbenső lépés, és kategóriák szerint kíván rendezni, akkor a vázlat formátuma szörnyű. Az összesítés, majd kategória szerinti rendezés után az Auto: Biztosítás kategóriát rosszul rendezi a jelentés "I" szakaszába. Azoknál a kategóriáknál, ahol csak az összesítést tartom meg, helytelenül rendezik a jelentés "T" szakaszába.
Két olyan segédprogram volt, amelyről azt gondoltam, hogy megkönnyíti ezt a helyzetet. Az egyik segédprogramot összeomlásnak nevezzük. Meghívásakor ez a makró egy alkategóriát egy sorba sorol, megfelelő kategórianévvel. A fenti példában a makró futtatása, miközben a cella mutató a 34–38. Sorban van, az A38 kategóriáját „Számítógép” -re cseréli, és a 34–37. Sorokat törli.
A második segédprogram azokhoz a kategóriákhoz tartozik, ahol szeretném látni az alkategória részleteit, de nincs szükségük sem a címsorra, sem a szaggatott részösszegre, sem a kategória összesítésére. Ezt a segédprogramot Fillnek hívják. Megtalálja a megfelelő kategória nevét, és minden alkategóriát előtaggal lát el a cateogry-val. A fenti példában a makró futtatása, miközben a cella mutató a 24-30 közötti sorokban van, az A25: A28 cellákat olyan formátumra változtatja, mint az "Auto: Insurance". A 24., 29. és 30. sort töröljük.
A jobb oldalon található a jelentés továbbfejlesztett változata. Az Összecsukás és Kitöltés gyorsbillentyűkhöz rendelésével ezeket a változtatásokat csak néhány billentyűleütéssel tudtam végrehajtani. Most már könnyű rendezni a jelentést, tudva, hogy a jelentés a kategória rendezésével visszatérhet az eredeti sorrendjéhez.
Ha még nem ismeri a makrókat, olvassa el az Excel VBA szerkesztő bemutatását.
Miután átmásolta a makrót, az alábbi lépések végrehajtásával rendelhet gyorsbillentyűt:
- Az Eszközök menüben válassza a Makrók, majd a Makró lehetőséget
- Jelölje ki a Kitöltés makrót. Kattintson az Opciók elemre. A Parancsikon mezőbe írjon be bármilyen betűt. Az F-t a Fill-hez használom. Kattintson az OK gombra
- Jelölje ki az összecsukási makrót. Kattintson az Opciók elemre. Válasszon egy betűt a parancsikonhoz, de távol tartsa magát a c-től, mivel a Ctrl + c a közös parancsikon
- a Szerkesztés> Másolás lehetőségre. Kattintson az OK gombra
- Zárja be a makró párbeszédpanelt a Mégse gombbal.
Anhtuan Do nyári gyakornokának részeként egy napi kiegészítés megtervezése céljából a következő makrókat készítette.
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
