Pomysł na wskazówkę na ten tydzień zrodził się z rozmowy z dr M., autorem wielkiego cotygodniowego biuletynu z poradami Quicken.
Uwielbiam Quicken, ale z pewnością ma swoje irytujące cechy. Mam zapamiętany raport w Quicken z kategoriami na dole i miesiącami na górze. Quicken oferuje możliwość wydrukowania tego raportu, ale oczywiście zawsze używam polecenia Kopiuj, aby skopiować raport do schowka, a następnie używam Edycja> Wklej w programie Excel, aby skopiować raport do schowka. Ta funkcja jest znacznie szybsza niż starsza (i nadal dostępna) opcja drukowania do pliku .prn.
Tutaj pojawiają się irytacje. Po pierwsze, Quicken nie zadał sobie trudu, aby skopiować nagłówki kolumn z raportem. Dlatego muszę ręcznie wprowadzić nazwy miesięcy w programie Excel. Wystarczająco proste. Po drugie, kategorie kopiowane do schowka zawierają denerwujący format konspektu dla kategorii i podkategorii.
Quicken umożliwia klasyfikowanie wydatków za pomocą kategorii i podkategorii. W raporcie po lewej stronie wydatki motoryzacyjne są dodatkowo podzielone na ubezpieczenie, benzynę, naprawy i tablice rejestracyjne. Być może wykonałem kiepską robotę przy ustawianiu kategorii, ale uważam, że mam kilka kategorii, w których chcę zobaczyć szczegóły podkategorii, i inne kategorie, w których wolałbym zobaczyć tylko sumę kategorii.
Chciałbym również móc sortować ten raport w programie Excel. Przydałoby się posortować go według całkowitego kosztu, a następnie posortować według kategorii. Jasne, mógłbym cofnąć, ale wolałbym kategorie, które mają charakter alfabetyczny. Krótko mówiąc, nie obchodzi mnie format konspektu używany przez Quicken.
Czytelnicy od dłuższego czasu będą pamiętać moją pogardę dla formatu konspektu używanego przez tabele przestawne w Wypełnij puste komórki tabeli przestawnej za pomocą wskazówki Przejdź do specjalnej. Mamy tutaj taką samą sytuację. Jeśli raport Quicken jest tylko krokiem pośrednim i chcesz mieć możliwość sortowania według kategorii, format konspektu jest okropny. Po posortowaniu według sum, a następnie według kategorii, kategoria Auto: ubezpieczenie zostanie błędnie posortowana w sekcji „I” raportu. W przypadku kategorii, w których trzymam tylko sumę, zostaną one nieprawidłowo posortowane w sekcji „T” raportu.
Są dwa narzędzia, które moim zdaniem złagodzą tę sytuację. Użyteczność nazywa się upadkiem. Po wywołaniu to makro zwinie podkategorię w jeden wiersz z odpowiednią nazwą kategorii. W powyższym przykładzie uruchomienie makra, gdy wskaźnik komórki znajduje się w dowolnym miejscu w wierszach od 34 do 38, spowoduje zastąpienie kategorii w A38 wartością „Komputer” i usunięcie wierszy od 34 do 37.
Użyteczność druga jest przeznaczona dla kategorii, w których chciałbym zobaczyć szczegóły podkategorii, ale nie potrzebuję nagłówka, przerywanej sumy częściowej ani sumy kategorii. To narzędzie nazywa się Fill. Znajdzie właściwą nazwę kategorii i poprzedzi każdą podkategorię kategorią. W powyższym przykładzie uruchomienie makra, gdy wskaźnik komórki znajduje się w dowolnym miejscu w wierszach od 24 do 30, spowoduje zmianę komórek A25: A28 na format „Auto: ubezpieczenie”. Wiersze 24, 29 i 30 zostaną usunięte.
Po prawej stronie jest moja ulepszona wersja raportu. Przypisując Zwiń i Wypełnij do klawiszy skrótu, mogłem wprowadzić te zmiany za pomocą zaledwie kilku naciśnięć klawiszy. Teraz można łatwo posortować raport, wiedząc, że raport może powrócić do swojej pierwotnej kolejności poprzez posortowanie kategorii.
Jeśli nie masz doświadczenia z makrami, przeczytaj artykuł Wprowadzenie do edytora Excel VBA.
Po skopiowaniu makra możesz przypisać klawisz skrótu, wykonując następujące czynności:
- Z menu Narzędzia wybierz Makra, a następnie Makro
- Podświetl makro Wypełnienie. Kliknij Opcje. W polu Skrót wprowadź dowolną literę. Używam f do wypełnienia. Kliknij OK
- Podświetl makro zwijania. Kliknij Opcje. Wybierz literę dla skrótu, ale trzymaj się z daleka od c, ponieważ Ctrl + c to powszechny skrót
- dla Edycja> Kopiuj. Kliknij OK
- Zamknij okno makra przyciskiem Anuluj.
W ramach swoich dążeń do zaprojektowania dodatku na jeden dzień, letni stażysta, Anhtuan Do stworzył następujące makra.
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
