L'idea per il suggerimento di questa settimana è venuta conversando con il Dr. M, autore della fantastica newsletter settimanale sui suggerimenti Quicken.
Adoro Quicken, ma sicuramente ha i suoi fastidi. Ho un certo rapporto memorizzato in Quicken con le categorie in basso e i mesi in alto. Quicken offre l'opportunità di stampare questo rapporto, ma ovviamente utilizzo sempre il comando Copia per copiare il rapporto negli appunti e quindi uso Modifica> Incolla in Excel per copiare il rapporto negli appunti. Questa funzione è molto più veloce della precedente (e ancora disponibile) opzione di stampa su un file .prn.
Qui è dove spuntano i fastidi. Innanzitutto, Quicken non si è preoccupato di copiare le intestazioni delle colonne con il report. Quindi, devo inserire manualmente i nomi dei mesi in Excel. Abbastanza semplice. In secondo luogo, le categorie che vengono copiate negli appunti includono un fastidioso formato di struttura per categorie e sottocategorie.
Quicken ti consente di utilizzare categorie e sottocategorie per classificare le tue spese. Nel rapporto a sinistra, le spese automobilistiche sono ulteriormente suddivise da assicurazione, benzina, riparazioni e targhe. Forse ho fatto un pessimo lavoro nell'impostare le mie categorie, ma trovo che ho alcune categorie in cui voglio vedere i dettagli della sottocategoria e altre categorie in cui preferirei vedere solo il totale delle categorie.
Vorrei anche poter ordinare questo rapporto in Excel. Sarebbe utile ordinarlo per spesa totale, quindi riordinarlo per categoria. Certo, potrei usare Annulla, ma vorrei categorie di natura alfabetica. In breve, non mi interessa il formato dello schema utilizzato da Quicken.
I lettori di lunga data ricorderanno il mio disprezzo per il formato struttura utilizzato dalle tabelle pivot in Compila celle vuote tabella pivot utilizzando Vai a suggerimento speciale. Abbiamo la stessa situazione qui. Se il rapporto Quicken è solo un passaggio intermedio e vuoi essere in grado di ordinare per categoria, il formato della struttura è orribile. Dopo l'ordinamento per totali e quindi per categoria, la categoria Auto: Insurance verrà ordinata in modo errato nella sezione "I" del report. Per le categorie in cui tengo solo il totale, verranno ordinate in modo errato nella sezione "T" del rapporto.
C'erano due utilità che pensavo avrebbero alleviato questa situazione. L'utilità uno si chiama collasso. Quando viene richiamata, questa macro comprimerà una sottocategoria in una singola riga con un nome di categoria appropriato. Nell'esempio precedente, l'esecuzione della macro mentre il puntatore di cella si trova ovunque nelle righe dalla 34 alla 38 sostituirà la categoria in A38 con "Computer" ed eliminerà le righe dalla 34 alla 37.
L'utilità due è per le categorie in cui vorrei vedere i dettagli della sottocategoria, ma non è necessario l'intestazione, la linea del totale parziale tratteggiata né il totale della categoria. Questa utility si chiama Fill. Troverà il nome corretto della categoria e anteporrà a ciascuna sottocategoria la categoria. Nell'esempio precedente, l'esecuzione della macro mentre il puntatore di cella si trova in un punto qualsiasi delle righe dalla 24 alla 30 comporterà la modifica delle celle A25: A28 in un formato come "Auto: Insurance". Le righe 24, 29 e 30 verranno eliminate.
A destra, c'è la mia versione migliorata del rapporto. Assegnando Comprimi e Riempi ai tasti di scelta rapida, sono stato in grado di apportare queste modifiche con poche sequenze di tasti. Ora è facile ordinare il rapporto, sapendo che il rapporto può tornare alla sua sequenza originale ordinando la categoria.
Se non conosci le macro, consulta Presentazione dell'editor VBA di Excel.
Dopo aver copiato la macro, puoi assegnare un tasto di scelta rapida seguendo questi passaggi:
- Dal menu Strumenti, scegli Macro, quindi Macro
- Evidenzia la macro Riempimento. Fare clic su Opzioni. Nel campo Collegamento, inserisci una lettera qualsiasi. Uso f per Fill. Fare clic su OK
- Evidenzia la macro di compressione. Fare clic su Opzioni. Scegli una lettera come scorciatoia, ma stai lontano da c, poiché Ctrl + c è la scorciatoia comune
- per Modifica> Copia. Fare clic su OK
- Chiudi la finestra di dialogo della macro con Annulla.
Come parte della sua ricerca per progettare un componente aggiuntivo al giorno, lo stagista estivo di Anhtuan Do ha creato le seguenti macro.
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
