L'idée du conseil de cette semaine est venue d'une conversation avec le Dr M, auteur du grand bulletin hebdomadaire de conseils Quicken.
J'adore Quicken, mais il a certainement ses ennuis. J'ai un certain rapport mémorisé dans Quicken avec des catégories sur le côté et des mois en haut. Quicken offre la possibilité d'imprimer ce rapport, mais bien sûr, j'utilise toujours simplement la commande Copier pour copier le rapport dans le presse-papiers, puis j'utilise Edition> Coller dans Excel pour copier le rapport dans le presse-papiers. Cette fonctionnalité est beaucoup plus rapide que l'ancienne option d'impression (et toujours disponible) dans un fichier .prn.
Voici où les ennuis surgissent. Premièrement, Quicken n'a pas pris la peine de copier les en-têtes de colonne avec le rapport. Donc, je dois entrer manuellement les noms de mois dans Excel. Assez simple. Deuxièmement, les catégories qui sont copiées dans le presse-papiers incluent un format de plan gênant pour les catégories et sous-catégories.
Quicken vous permet d'utiliser des catégories et sous-catégories pour classer vos dépenses. Dans le rapport de gauche, les dépenses automobiles sont en outre ventilées par assurance, essence, réparations et plaques d'immatriculation. Peut-être que j'ai mal configuré mes catégories, mais je trouve que j'ai certaines catégories dans lesquelles je veux voir le détail de la sous-catégorie et d'autres catégories où je préférerais simplement voir le total de la catégorie.
J'aimerais également pouvoir trier ce rapport dans Excel. Il serait utile de le trier par dépense totale, puis de le trier par catégorie. Bien sûr, je pourrais utiliser Annuler, mais je voudrais des catégories de nature alphabétique. En bref, je me fiche du format de contour utilisé par Quicken.
Les lecteurs de longue date se souviendront de mon mépris pour le format de plan utilisé par les tableaux croisés dynamiques dans Remplir les cellules vides du tableau croisé dynamique à l'aide de la pointe Aller à spécial. Nous avons la même situation ici. Si le rapport Quicken n'est qu'une étape intermédiaire et que vous voulez pouvoir trier par catégorie, le format du plan est horrible. Après avoir trié par totaux puis par catégorie, la catégorie Auto: Assurance sera mal triée dans la section «I» du rapport. Pour les catégories dont je ne garde que le total, elles seront incorrectement triées dans la section "T" du rapport.
Il y avait deux utilitaires qui, à mon avis, faciliteraient cette situation. La première utilité s'appelle l'effondrement. Lorsqu'elle est appelée, cette macro réduira une sous-catégorie en une seule ligne avec un nom de catégorie approprié. Dans l'exemple ci-dessus, l'exécution de la macro alors que le pointeur de cellule est n'importe où dans les lignes 34 à 38 remplacera la catégorie dans A38 par "Ordinateur" et supprimera les lignes 34 à 37.
L'utilitaire 2 est pour les catégories pour lesquelles je voudrais voir le détail de la sous-catégorie, mais je n'ai pas besoin de l'en-tête, de la ligne de sous-total en pointillés, ni du total de la catégorie. Cet utilitaire s'appelle Fill. Il trouvera le nom de catégorie approprié et préfixera chaque sous-catégorie avec la catégorie. Dans l'exemple ci-dessus, l'exécution de la macro alors que le pointeur de cellule est n'importe où dans les lignes 24 à 30 entraînera la modification des cellules A25: A28 en un format tel que "Auto: Assurance". Les lignes 24, 29 et 30 seront supprimées.
À droite, ma version améliorée du rapport. En attribuant Réduire et Remplir aux touches de raccourci, j'ai pu effectuer ces modifications en quelques frappes. Il est désormais facile de trier le rapport, sachant que le rapport peut revenir à sa séquence d'origine en triant la catégorie.
Si vous êtes nouveau dans les macros, consultez Présentation de l'éditeur Excel VBA.
Une fois que vous copiez la macro, vous pouvez attribuer une touche d'accès rapide en suivant ces étapes:
- Dans le menu Outils, choisissez Macros puis Macro
- Mettez en surbrillance la macro de remplissage. Cliquez sur Options. Dans le champ Raccourci, saisissez une lettre. J'utilise f pour Fill. Cliquez sur OK
- Mettez en surbrillance la macro de réduction. Cliquez sur Options. Choisissez une lettre pour un raccourci, mais restez à l'écart de c, car Ctrl + c est le raccourci commun
- pour Edition> Copier. Cliquez sur OK
- Fermez la boîte de dialogue de macro avec Annuler.
Dans le cadre de sa quête pour concevoir un complément d'un jour, le stagiaire d'été d'Anhtuan Do a créé les macros suivantes.
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
