La idea del consejo de esta semana surgió de una conversación con el Dr. M, autor del excelente boletín semanal de consejos Quicken.
Amo Quicken, pero ciertamente tiene sus molestias. Tengo un informe memorizado en Quicken con categorías al lado y meses en la parte superior. Quicken ofrece la oportunidad de imprimir este informe, pero, por supuesto, siempre uso el comando Copiar para copiar el informe al portapapeles y luego uso Editar> Pegar en Excel para copiar el informe al portapapeles. Esta función es mucho más rápida que la opción más antigua (y aún disponible) de imprimir en un archivo .prn.
Aquí es donde surgen las molestias. Primero, Quicken no se molestó en copiar los encabezados de las columnas con el informe. Entonces, debo ingresar manualmente los nombres de los meses en Excel. Suficientemente simple. En segundo lugar, las categorías que se copian en el portapapeles incluyen un formato de esquema molesto para categorías y subcategorías.
Quicken le permite utilizar categorías y subcategorías para clasificar sus gastos. En el informe de la izquierda, los gastos automotrices se desglosan además por seguros, gasolina, reparaciones y placas. Quizás hice un mal trabajo al configurar mis categorías, pero encuentro que tengo algunas categorías en las que quiero ver el detalle de la subcategoría y otras categorías en las que preferiría ver el total de la categoría.
También me gustaría poder ordenar este informe en Excel. Sería útil ordenarlo por gasto total y luego ordenarlo por categoría. Claro, podría usar deshacer, pero me gustaría categorías que sean de naturaleza alfabética. En resumen, no me importa el formato de esquema que usa Quicken.
Los lectores de mucho tiempo recordarán mi desdén por el formato de esquema utilizado por las tablas dinámicas en Rellenar celdas en blanco de la tabla dinámica con la sugerencia Ir a especial. Tenemos la misma situación aquí. Si el informe Quicken es simplemente un paso intermedio y desea poder ordenar por categoría, el formato de esquema es horrible. Después de ordenar por totales y luego por categoría, la categoría Auto: Seguros se clasificará incorrectamente en la sección "I" del informe. En el caso de las categorías en las que guardo solo el total, se clasificarán incorrectamente en la sección "T" del informe.
Había dos utilidades que pensé que aliviarían esta situación. La utilidad uno se llama colapso. Cuando se invoca, esta macro contraerá una subcategoría en una sola línea con un nombre de categoría adecuado. En el ejemplo anterior, ejecutar la macro mientras el puntero de celda está en cualquier lugar de las filas 34 a 38 reemplazará la categoría en A38 con "Computadora" y eliminará las filas 34 a 37.
La utilidad dos es para las categorías en las que me gustaría ver el detalle de la subcategoría, pero no necesito el encabezado, la línea de subtotal punteada ni el total de la categoría. Esta utilidad se llama Rellenar. Encontrará el nombre de categoría adecuado y antepondrá cada subcategoría con la categoría. En el ejemplo anterior, ejecutar la macro mientras el puntero de celda está en cualquier lugar de las filas 24 a 30 dará como resultado que las celdas A25: A28 se cambien a un formato como "Auto: Seguro". Se eliminarán las filas 24, 29 y 30.
A la derecha, está mi versión mejorada del informe. Al asignar Contraer y Rellenar a las teclas de acceso rápido, pude realizar estos cambios con solo unas pocas pulsaciones de teclas. Ahora es fácil ordenar el informe, sabiendo que el informe puede volver a su secuencia original clasificando la categoría.
Si es nuevo en las macros, revise Introducción al Editor de VBA de Excel.
Una vez que copie la macro, puede asignar una tecla de acceso rápido siguiendo estos pasos:
- En el menú Herramientas, elija Macros y luego Macro
- Resalte la macro de relleno. Haga clic en Opciones. En el campo Atajo, ingrese cualquier letra. Uso f para Rellenar. Haga clic en Aceptar
- Resalte la macro de colapso. Haga clic en Opciones. Elija una letra para un atajo, pero manténgase alejado de c, ya que Ctrl + c es el atajo común
- para Editar> Copiar. Haga clic en Aceptar
- Cierre el cuadro de diálogo macro con Cancelar.
Como parte de su búsqueda para diseñar un complemento de un día, el pasante de verano, Anhtuan Do, creó las siguientes 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
