Bill a posé la question de cette semaine sur les données Excel redondantes.
Je construis une liste de transactions mensuelles dans Excel. À la fin du mois, je dois éliminer les données redondantes et obtenir un total par code de compte. Chaque code de compte peut apparaître plusieurs fois. Bill a ensuite décrit sa méthodologie Excel actuelle qui est similaire à la méthode 1 ci-dessous afin de proposer une liste unique de codes de compte, avec des plans pour utiliser une matrice de formules CSE pour obtenir les totaux. Il demande, y a-t-il un moyen plus simple d'arriver à une liste unique de codes de compte avec des totaux pour chaque compte?
C'est une question de vacances parfaite. Étant un utilisateur de Lotus depuis 15 ans, je reconnais la méthode de Bill comme la méthode classique pour la manipulation de données "rapide et sale" du bon vieux temps de la version 2.1 de Lotus. C'est une saison pour compter nos bénédictions. Lorsque vous pensez à cette question, vous vous rendez compte que les gens de Microsoft nous ont vraiment accordé un certain nombre d'outils au fil des ans. Si vous utilisez Excel 97, il existe au moins cinq méthodes pour effectuer cette tâche, qui sont toutes beaucoup plus faciles que la méthode classique décrite par Bill. Je proposerai un tutoriel sur les cinq méthodes cette semaine.
Mon ensemble de données simplifié comporte des numéros de compte dans la colonne A et des montants dans la colonne B. Les données vont de A2: B100. Ce n'est pas trié au début.
Méthode 1
Utilisez des instructions créatives If en conjonction avec Coller les valeurs spéciales pour trouver la réponse.
Compte tenu des nouveaux outils proposés par Excel, je ne recommande plus cette méthode. J'avais l'habitude de l'utiliser beaucoup avant que de meilleures choses arrivent et il y a encore des situations où cela est utile. Mon nom alternatif pour ceci est la méthode "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM". Voici les étapes.
- Triez les données par colonne A.
- Inventez une formule dans la colonne C qui gardera un total cumulé par compte. La cellule C2 est
=IF(A2=A1,C1+B2,B2). - Inventez une formule en D qui identifiera la dernière entrée pour un compte particulier. La cellule D2 est
=IF(A2=A3,FALSE,TRUE). - Copiez C2: D2 dans toutes vos lignes.
- Copie C2: D100. Faites une édition - PasteSpecial - Values sur C2: D100 pour changer les formules en valeurs.
- Trier par colonne D décroissante.
- Pour les lignes qui ont un VRAI dans la colonne D, vous avez une liste unique de numéros de compte dans A et le total cumulé final dans C.
Points positifs: C'est rapide. Tout ce dont vous avez besoin est un sens aigu de l'écriture des instructions IF.
Points négatifs: Il existe de meilleures façons.
Méthode 2
Utilisez Data Filter - Advanced Filter pour obtenir la liste des comptes uniques.
La question de Bill était vraiment de savoir comment obtenir une liste unique de numéros de compte afin qu'il puisse utiliser les formules du CSE pour obtenir les totaux. Il s'agit d'une méthode pour obtenir une liste des numéros de compte uniques.
- Mettez en surbrillance A1: A100
- Dans le menu, choisissez Données, Filtre, Filtre avancé
- Cliquez sur le bouton radio "Copier vers un autre emplacement".
- Cochez la case "Enregistrements uniques uniquement".
- Choisissez une section vide de la feuille de calcul dans laquelle vous souhaitez que la liste unique apparaisse. Entrez ceci dans le champ "Copier vers:". (Notez que ce champ est grisé jusqu'à ce que vous choisissiez "Copier vers un autre emplacement".
- Cliquez sur OK. Les numéros de compte uniques apparaîtront en F1.
- Entrez toutes les manipulations en aval, formules matricielles, etc. pour obtenir vos résultats.
Avantages: plus rapide que la méthode 1. Aucun tri requis.
Inconvénients: Les formules CSE requises après cela vous feront tourner la tête.
Méthode 3
Utilisez Data Consolidate.
Ma qualité de vie s'est améliorée lorsque Excel a proposé Data Consolidate. C'était GRAND! Cela prend 30 secondes pour le configurer, mais cela a entraîné la mort pour les DSUM et d'autres méthodes. Votre numéro de compte doit se trouver à gauche des champs numériques que vous voulez totaliser. Vous devez avoir des en-têtes au-dessus de chaque colonne. Vous devez attribuer un nom de plage au bloc rectangulaire de cellules qui comprend les numéros de compte dans la colonne de gauche et les en-têtes en haut. Dans ce cas, cette plage est A1: B100.
- Mettez en surbrillance A1: B100
- Attribuez un nom de plage à cette zone en cliquant dans la zone de nom (à gauche de la barre de formule) et en tapant un nom tel que "TotalMe". (Vous pouvez également utiliser Insertion - Nom).
- Placez le pointeur de cellule dans une section vide de la feuille de calcul.
- Choisir des données - Consolider
- Dans le champ de référence, saisissez le nom de la plage (TotalMe).
- Dans la section Utiliser les étiquettes dans, cochez la ligne du haut et la colonne de gauche.
- Cliquez sur OK
Avantages: C'est ma méthode préférée. Aucun tri requis. Le raccourci est alt-D N (nom de la plage) alt-T alt-L enter. Il est facilement évolutif. Si votre plage comprend 12 colonnes mensuelles, la réponse aura des totaux pour chaque mois.
Inconvénients: Si vous effectuez une autre consolidation des données sur la même feuille, vous devez effacer l'ancien nom de plage du champ Toutes les références à l'aide du bouton Supprimer. Le numéro de compte doit se trouver à gauche de vos données numériques. Il est légèrement plus lent que les tableaux croisés dynamiques, ce qui devient perceptible pour les ensembles de données avec plus de 10000 enregistrements.
Méthode 4
Utilisez les sous-totaux de données.
C'est une fonctionnalité intéressante. Parce que les données résultantes sont étranges à travailler, je les utilise moins souvent que Data Consolidate.
- Trier par colonne A par ordre croissant.
- Sélectionnez n'importe quelle cellule de la plage de données.
- Choisissez Données - Sous-totaux dans le menu.
- Par défaut, Excel propose de sous-totaliser la dernière colonne de vos données. Cela fonctionne dans cet exemple, mais vous devez souvent faire défiler la liste «Ajouter un sous-total à:» pour sélectionner les champs appropriés.
- Cliquez sur OK. Excel insérera une nouvelle ligne à chaque changement de numéro de compte avec un total.
Une fois que vous avez les sous-totaux, vous verrez un petit 123 apparaître sous la zone de nom. Cliquez sur 2 pour voir une seule ligne par compte avec les totaux. Lisez Copier les sous-totaux Excel pour une explication des étapes spéciales nécessaires pour les copier dans un nouvel emplacement. Cliquez sur 3 pour voir toutes les lignes. Avantages: Cool Feature. Idéal pour imprimer des rapports avec des totaux et des sauts de page après chaque section.
Inconvénients: les données doivent être triées en premier. Lent pour beaucoup de données. Vous devez utiliser Goto-Special-VisbileCellsOnly pour obtenir les totaux ailleurs. Vous devez utiliser Data-Subtotals-RemoveAll pour revenir à vos données d'origine.
Méthode 5
Utilisez un tableau croisé dynamique.
Les tableaux croisés dynamiques sont les plus polyvalents de tous. Vos données n'ont pas besoin d'être triées. Les colonnes numériques peuvent être à gauche ou à droite du numéro de compte. Vous pouvez facilement afficher les numéros de compte vers le bas ou sur la page.
- Sélectionnez n'importe quelle cellule de la plage de données.
- Choisissez Données - Tableau croisé dynamique dans le menu.
- Acceptez les valeurs par défaut à l'étape 1
- Assurez-vous que la plage de données à l'étape 2 est correcte (c'est généralement le cas)
- Si vous utilisez Excel 2000, cliquez sur le bouton Mise en page à l'étape 3. Les utilisateurs d'Excel 95 et 97 accèdent automatiquement à la mise en page à l'étape 3.
- Dans la boîte de dialogue de mise en page, faites glisser le bouton Compte depuis le côté droit de la boîte de dialogue et déposez-le dans la zone Ligne.
- Faites glisser le bouton Montant depuis le côté droit de la boîte de dialogue et déposez-le dans la zone Données.
- Les utilisateurs d'Excel 2000 cliquent sur OK, les utilisateurs d'Excel 95/97 cliquent sur Suivant.
- Spécifiez si vous souhaitez que les résultats soient dans une nouvelle feuille ou dans une section spécifique d'une feuille existante. En savoir plus sur les tableaux croisés dynamiques dans les astuces avancées de tableau croisé dynamique Excel.
- Les tableaux croisés dynamiques offrent des fonctionnalités incroyables et font de cette tâche un jeu d'enfant. Pour copier les résultats du tableau croisé dynamique, vous devez faire un Edit-PasteSpecial-Values, sinon Excel ne vous permettra pas d'insérer des lignes, etc.
Avantages: rapide, flexible, puissant. Rapide, même pour de nombreuses données.
Points négatifs: Un peu intimidant.
Bill a maintenant quatre nouvelles méthodes pour éliminer les données redondantes. Bien que ces méthodes ne soient pas disponibles depuis le début des temps, Lotus et Excel ont tous deux été de grands innovateurs pour nous apporter des moyens plus rapides d'accomplir cette tâche banale.
