Ideea pentru sfatul acestei săptămâni a venit din conversația cu Dr. M, autorul grozavului buletin informativ săptămânal Quicken.
Îmi place Quicken, dar cu siguranță are supărările sale. Am un anumit raport memorat în Quicken cu categorii în jos și luni trecând de sus. Quicken oferă posibilitatea de a imprima acest raport, dar, desigur, folosesc întotdeauna comanda Copiere pentru a copia raportul în clipboard și apoi folosesc Edit> Paste in Excel pentru a copia raportul în clipboard. Această caracteristică este mult mai rapidă decât cea mai veche (și încă disponibilă) tipărire într-o opțiune de fișier .prn.
Aici apar supărările. În primul rând, Quicken nu s-a deranjat să copieze titlurile coloanelor cu raportul. Deci, trebuie să introduc manual numele lunilor în Excel. Destul de simplu. În al doilea rând, categoriile care sunt copiate în clipboard includ un format de schiță enervant pentru categorii și subcategorii.
Quicken vă permite să utilizați categorii și subcategorii pentru a vă clasifica cheltuielile. În raportul din stânga, cheltuielile cu automobilele sunt descompuse în continuare prin asigurări, benzină, reparații și plăcuțe de înmatriculare. Poate că am făcut o treabă slabă în ceea ce privește configurarea categoriilor mele, dar constat că am câteva categorii în care vreau să văd detaliile subcategoriei și alte categorii în care aș prefera să văd doar categoria totală.
Aș dori, de asemenea, să pot sorta acest raport în Excel. Ar fi util să-l sortați după cheltuieli totale, apoi să îl sortați după categorie. Sigur, aș putea folosi anularea, dar aș dori categorii de natură alfabetică. Pe scurt, nu-mi pasă de formatul de schiță folosit de Quicken.
Cititorii de multă vreme își vor aminti disprețul meu pentru formatul de schiță folosit de tabelele pivot în Completarea tabelelor pivot cu celule goale folosind sfatul Go To Special. Avem aceeași situație aici. Dacă raportul Quicken este doar un pas intermediar și doriți să puteți sorta după categorie, formatul conturului este oribil. După sortarea după totaluri și apoi după categorie, categoria Auto: Asigurări va fi sortată greșit în secțiunea „I” a raportului. Pentru categoriile în care păstrez doar totalul, acestea vor fi sortate incorect în secțiunea „T” a raportului.
Există două utilități pe care le-am crezut că vor ușura această situație. Utilitatea unu se numește colaps. Când este invocată, această macrocomandă va restrânge o subcategorie într-o singură linie cu un nume de categorie adecvat. În exemplul de mai sus, rularea macro în timp ce indicatorul de celulă se află oriunde în rândurile 34 până la 38 va înlocui categoria din A38 cu „Computer” și va șterge rândurile 34 până la 37.
Utilitatea doi este pentru categoriile în care aș dori să văd detaliile subcategoriei, dar nu au nevoie de titlu, linia subtotală punctată și nici totalul categoriei. Acest utilitar se numește Fill. Acesta va găsi numele categoriei corespunzătoare și va prefixa fiecare subcategorie cu categoria. În exemplul de mai sus, rularea macro în timp ce indicatorul de celulă se află oriunde în rândurile de la 24 la 30 va duce la modificarea celulelor A25: A28 într-un format precum „Auto: Asigurare”. Rândurile 24, 29 și 30 vor fi șterse.
În dreapta, este versiunea mea îmbunătățită a raportului. Prin atribuirea Collapse și Fill la tastele rapide, am reușit să fac aceste modificări cu doar câteva apăsări de tastă. Acum este ușor să sortați raportul, știind că raportul poate reveni la secvența originală prin sortarea categoriei.
Dacă nu sunteți nou în macro-uri, consultați Introducerea editorului Excel VBA.
După ce copiați macro-ul, puteți atribui o tastă rapidă urmând acești pași:
- Din meniul Instrumente, alege Macro apoi Macro
- Evidențiați macrocomanda Fill. Faceți clic pe Opțiuni. În câmpul Comandă rapidă, introduceți orice literă. Folosesc f pentru Fill. Faceți clic pe OK
- Evidențiați macrocomanda de colaps. Faceți clic pe Opțiuni. Alegeți o literă pentru o comandă rapidă, dar stați departe de c, deoarece Ctrl + c este comanda rapidă obișnuită
- pentru Editare> Copiere. Faceți clic pe OK
- Închideți fereastra de dialog macro cu Anulare.
Ca parte a încercării sale de a proiecta un supliment pentru o zi, stagiarul de vară, Anhtuan Do a creat următoarele macro-uri.
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
