A ideia para a dica desta semana veio de uma conversa com o Dr. M, autor do grande boletim informativo semanal Quicken Tips.
Eu amo Quicken, mas certamente tem seus aborrecimentos. Tenho um certo relatório memorizado no Quicken com categorias na lateral e meses no topo. Quicken oferece a oportunidade de imprimir este relatório, mas é claro, eu sempre uso o comando Copiar para copiar o relatório para a área de transferência e uso Editar> Colar no Excel para copiar o relatório para a área de transferência. Esse recurso é muito mais rápido do que a opção mais antiga (e ainda disponível) de impressão em um arquivo .prn.
É aqui que surgem os aborrecimentos. Primeiro, Quicken não se preocupou em copiar os cabeçalhos das colunas com o relatório. Portanto, devo inserir manualmente os nomes dos meses no Excel. Simples o suficiente. Em segundo lugar, as categorias que são copiadas para a área de transferência incluem um formato de esboço irritante para categorias e subcategorias.
Quicken permite que você use categorias e subcategorias para classificar suas despesas. No relatório à esquerda, os gastos automotivos são detalhados ainda por seguros, gasolina, consertos e placas. Talvez eu tenha feito um trabalho ruim ao configurar minhas categorias, mas descobri que tenho algumas categorias em que desejo ver os detalhes da subcategoria e outras categorias em que prefiro ver apenas o total da categoria.
Também gostaria de poder classificar este relatório no Excel. Seria útil classificá-lo pela despesa total e, em seguida, classificá-lo de volta por categoria. Claro, eu poderia desfazer, mas gostaria de categorias que sejam alfabéticas por natureza. Resumindo, não ligo para o formato de esboço usado pelo Quicken.
Os leitores de longa data se lembrarão do meu desdém pelo formato de esboço usado pelas tabelas dinâmicas na dica Preencher células em branco da tabela dinâmica usando Ir para a dica especial. Temos a mesma situação aqui. Se o relatório Quicken for meramente uma etapa intermediária e você quiser classificar por categoria, o formato da estrutura de tópicos é horrível. Depois de classificar por totais e depois por categoria, a categoria Automóvel: Seguro será mal classificada na seção "I" do relatório. Para as categorias nas quais mantenho apenas o total, elas serão classificadas incorretamente na seção "T" do relatório.
Havia dois utilitários que achei que facilitariam essa situação. A utilidade um é chamada de colapso. Quando chamada, esta macro recolherá uma subcategoria em uma única linha com um nome de categoria adequado. No exemplo acima, a execução da macro enquanto o ponteiro da célula está em qualquer lugar nas linhas 34 a 38 substituirá a categoria em A38 por "Computador" e excluirá as linhas 34 a 37.
O utilitário dois é para as categorias em que gostaria de ver os detalhes da subcategoria, mas não preciso do título, da linha de subtotal tracejada nem do total da categoria. Este utilitário é denominado Fill. Ele encontrará o nome da categoria adequado e prefixará cada subcategoria com a categoria. No exemplo acima, a execução da macro enquanto o ponteiro da célula está em qualquer lugar nas linhas 24 a 30 resultará nas células A25: A28 sendo alteradas para um formato como "Automático: Seguro". As linhas 24, 29 e 30 serão excluídas.
À direita, está minha versão aprimorada do relatório. Ao atribuir Recolher e Preencher às teclas de atalho, consegui fazer essas alterações com apenas alguns toques no teclado. Agora é fácil classificar o relatório, sabendo que o relatório pode retornar à sua sequência original classificando a categoria.
Se você é novo em macros, consulte Apresentando o Editor VBA do Excel.
Depois de copiar a macro, você pode atribuir uma tecla de atalho seguindo estas etapas:
- No menu Ferramentas, escolha Macros e Macro
- Destaque a macro de preenchimento. Clique em Opções. No campo Atalho, digite qualquer letra. Eu uso f para Preencher. Clique OK
- Realce a macro de recolhimento. Clique em Opções. Escolha uma letra para o atalho, mas fique longe de c, pois Ctrl + C é o atalho comum
- para Editar> Copiar. Clique OK
- Feche a caixa de diálogo da macro com Cancelar.
Como parte de sua busca para criar um suplemento por dia, estagiário de verão, Anhtuan Do criou as seguintes macros.
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
