Идеята за съвет от тази седмица дойде от разговор с д-р М, автор на страхотния седмичен бюлетин за съвети Quicken.
Обичам Quicken, но със сигурност има своите досади. Имам определен запомнен отчет в Quicken с категории надолу и месеци, преминаващи отгоре. Quicken предлага възможност за отпечатване на този отчет, но разбира се, винаги използвам само командата Copy, за да копирам отчета в клипборда и след това използвам Edit> Paste в Excel, за да копирам отчета в клипборда. Тази функция е много по-бърза от по-старата (и все още налична) опция за печат в .prn файл.
Тук се появяват досадите. Първо, Quicken не си направи труда да копира заглавията на колоните с отчета. И така, ръчно трябва да въведа имената на месеците в Excel. Достатъчно просто. Второ, категориите, които се копират в клипборда, включват досаден контурен формат за категории и подкатегории.
Quicken ви позволява да използвате категории и подкатегории, за да класифицирате разходите си. В доклада вляво автомобилните разходи се разпределят допълнително от застраховки, бензин, ремонти и регистрационни табели. Може би се справих лошо с настройването на моите категории, но откривам, че имам някои категории, в които искам да видя подробности за подкатегорията и други категории, в които бих предпочел просто да видя общата категория.
Бих искал също така да мога да сортирам този отчет в Excel. Би било полезно да го сортирате по общ разход, след което да го сортирате обратно по категории. Разбира се, бих могъл да използвам отмяна, но бих искал категории, които имат азбучен характер. Накратко, не ми пука за формата на контура, използван от Quicken.
Дълго време читателите ще запомнят пренебрежението ми към формата на контурите, използван от обобщените таблици в Попълнете празни клетки на обобщената таблица, използвайки специален съвет. Тук имаме същата ситуация. Ако отчетът за ускоряване е само междинна стъпка и искате да можете да сортирате по категории, форматът на контурите е ужасен. След сортиране по общо и след това по категория, категорията Авто: Застраховка ще бъде сортирана погрешно в раздела "I" на отчета. За категориите, в които запазвам само общата сума, те ще бъдат сортирани неправилно в раздела „Т“ на отчета.
Имаше две помощни програми, които мислех, че ще облекчат тази ситуация. Помощна програма се нарича колапс. При извикване този макрос ще свие подкатегория в един ред с правилно име на категория. В горния пример стартирането на макроса, докато указателят на клетка е някъде в редове 34 до 38, ще замени категорията в A38 с "Компютър" и ще изтрие редове 34 до 37.
Помощна програма две е за категориите, където бих искал да видя подробности за подкатегорията, но не се нуждаят от заглавието, прекъснатата междинна линия, нито общата категория. Тази помощна програма се нарича Fill. Той ще намери правилното име на категорията и ще добави към всяка подкатегория префикса с cateogry. В горния пример, стартирането на макроса, докато указателят на клетка е навсякъде в редове от 24 до 30, ще доведе до промяна на клетки A25: A28 във формат като „Auto: Insurance“. Редове 24, 29 и 30 ще бъдат изтрити.
Вдясно е моята подобрена версия на доклада. Като присвоих Collapse and Fill на горещите клавиши, успях да направя тези промени само с няколко натискания на клавиши. Вече е лесно да сортирате отчета, знаейки, че отчетът може да се върне към първоначалната си последователност чрез сортиране на категорията.
Ако не сте запознати с макроси, прегледайте Въвеждане на Excel VBA Editor.
След като копирате макроса, можете да зададете горещ клавиш, като изпълните следните стъпки:
- От менюто Инструменти изберете Макроси, след което Макрос
- Маркирайте макроса Fill. Щракнете върху Опции. В полето за пряк път въведете произволна буква. Използвам f за запълване. Щракнете върху OK
- Маркирайте макроса за свиване. Щракнете върху Опции. Изберете буква за пряк път, но стойте далеч от c, тъй като Ctrl + c е често срещаният пряк път
- за Редактиране> Копиране. Щракнете върху OK
- Затворете диалоговия прозорец на макроса с Отказ.
Като част от стремежа си да проектира добавка за деня, летен стажант, Anhtuan Do създаде следните макроси.
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
