Ідея для підказки цього тижня виникла внаслідок розмови з доктором М, автором чудового щотижневого бюлетеня "Поради щодо швидкості".
Я люблю Quicken, але це, безумовно, має свої прикрощі. У мене є певний заучений звіт у Quicken з категоріями внизу та місяцями, що йдуть вгорі. Quicken пропонує можливість роздрукувати цей звіт, але, звичайно, я завжди просто використовую команду «Копіювати», щоб скопіювати звіт у буфер обміну, а потім за допомогою «Редагувати> Вставити в Excel» скопіювати звіт у буфер обміну. Ця функція набагато швидша, ніж старіша (і все ще доступна) опція друку у файлі .prn.
Ось де з’являються прикрощі. По-перше, Quicken не потрудився скопіювати заголовки стовпців разом із звітом. Отже, я повинен вручну вводити назви місяців у Excel. Досить просто. По-друге, категорії, які копіюються в буфер обміну, містять надокучливий формат структури категорій та підкатегорій.
Quicken дозволяє використовувати категорії та підкатегорії для класифікації ваших витрат. У звіті ліворуч автомобільні витрати додатково розбиваються на страхування, бензин, ремонт та номерні знаки. Можливо, я погано спрацював із налаштуванням своїх категорій, але я виявляю, що у мене є деякі категорії, де я хочу бачити деталі підкатегорій та інші категорії, де я хотів би просто бачити загальну категорію.
Я також хотів би мати можливість сортувати цей звіт у Excel. Було б корисно відсортувати його за загальними витратами, а потім відсортувати за категоріями. Звичайно, я міг би скасувати скасування, але я хотів би категорій, що мають алфавітний характер. Коротше кажучи, я не дбаю про формат контуру, який використовує Quicken.
Читачі довгий час пам’ятатимуть моє презирство до формату контуру, який використовують зведені таблиці в Заповніть порожні клітинки зведеної таблиці за допомогою спеціальної підказки Перейти до. У нас така ж ситуація. Якщо звіт Quicken - це лише проміжний крок, і ви хочете мати можливість сортувати за категоріями, формат контуру жахливий. Після сортування за підсумками, а потім за категоріями, категорія Авто: Страхування буде неправильно відсортовано до розділу "Я" звіту. Для категорій, де я зберігаю лише загальну суму, вони будуть неправильно відсортовані в розділі "Т" звіту.
Були дві комунальні послуги, які я думав полегшити цю ситуацію. Утиліта одна називається колапсом. При виклику цей макрос згортає підкатегорію в один рядок із відповідною назвою категорії. У наведеному вище прикладі запуск макросу, коли покажчик комірки знаходиться де завгодно в рядках з 34 по 38, замінить категорію в A38 на "Комп’ютер" і видалить рядки з 34 по 37.
Друга утиліта призначена для категорій, де я хотів би бачити деталі підкатегорії, але не потребують заголовка, пунктирної проміжної суми та загальної категорії. Ця утиліта називається Fill. Він знайде відповідну назву категорії та додасть до кожної підкатегорії категорію. У наведеному вище прикладі запуск макросу, коли вказівник клітинки знаходиться де-небудь у рядках з 24 по 30, призведе до зміни клітинок A25: A28 на такий формат, як «Авто: страхування». Рядки 24, 29 та 30 буде видалено.
Справа - моя вдосконалена версія звіту. Присвоївши "Згорнути" та "Заливка" гарячим клавішам, я зміг внести ці зміни лише кількома натисканнями клавіш. Тепер сортувати звіт легко, знаючи, що звіт може повернутися до початкової послідовності шляхом сортування категорії.
Якщо ви не знайомі з макросами, ознайомтесь із представленням редактора Excel VBA.
Після копіювання макросу ви можете призначити гарячу клавішу, виконавши такі дії:
- У меню Інструменти виберіть Макроси, а потім Макрос
- Виділіть макрос «Заливка». Клацніть Параметри. У полі Ярлик введіть будь-яку букву. Я використовую f для заповнення. Клацніть OK
- Виділіть макрос згортання. Клацніть Параметри. Виберіть літеру для ярлика, але тримайтеся подалі від c, оскільки Ctrl + c є загальним ярликом
- для Редагувати> Копіювати. Клацніть OK
- Закрийте діалогове вікно макросу за допомогою Скасувати.
В рамках свого прагнення створити надбудову на день, літнього стажиста, Анхтуан До створив наступні макроси.
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
