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