Arrêtez d'écrire des formules de pourcentage dans Excel, utilisez-la à la place

Calculer des pourcentages dans Excel signifie souvent se battre avec des références de cellules absolues et des symboles de division. Mais pourquoi faire ce travail quand un outil intégré peut le faire à votre place ? En utilisant l'option PivotTable Show Values ​​As, vous pouvez calculer des ratios complexes en quelques secondes sans taper une seule formule.

Le problème avec les formules de pourcentage manuelles

Et pourquoi le menu Afficher les valeurs sous est la voie à suivre

Les formules constituent l'épine dorsale d'Excel, mais elles constituent également le moyen le plus simple d'encombrer une feuille de calcul propre.

L'ajout de colonnes de pourcentage distinctes crée un bruit visuel qui détourne l'attention de vos données. Même avec des tableaux Excel et des références structurées, vous devez toujours écrire et maintenir la logique, en gérant souvent une valeur Grand Total qui peut renvoyer des résultats incorrects, en particulier lorsque des filtres sont appliqués. Les références manuelles de cellules sont également fragiles. Si votre structure change, une formule comme =A2/$B$20 peut pointer vers la mauvaise cellule ou renvoyer un #DIV/0 ! erreur.

Les tableaux croisés dynamiques contournent largement ces problèmes en utilisant Show Values ​​As, une fonctionnalité intégrée qui transforme les nombres bruts en ratios sans ajouter une seule colonne supplémentaire à vos données source. Étant donné que cette analyse se trouve dans une feuille de calcul distincte, vos données originales restent intactes. Mieux encore, vous ne comptez pas sur des formules de cellule à cellule fragiles qui peuvent échouer si une ligne est supprimée ou une plage est triée : la logique est intégrée au tableau croisé dynamique lui-même.

En rapport

Mon astuce Excel incontournable pour analyser rapidement des ensembles de données volumineuses

Les tableaux croisés dynamiques sont un outil sous-estimé dans Microsoft Excel.

L'exemple d'ensemble de données

Une base cohérente pour nos exemples

Pour démontrer l'outil Afficher les valeurs en tant que, j'utiliserai le tableau Excel suivant (T_SalesData) avec les dates, les régions, les commerciaux, les catégories de produits et les revenus. Chacun des trois exemples ci-dessous commence par un nouveau tableau croisé dynamique, afin que vous puissiez voir à quel point Afficher les valeurs comme les outils peuvent transformer les mêmes informations brutes en informations uniques.

À l'aide d'un tableau Excel (Ctrl+T) car la source de données est essentielle pour un flux de travail dynamique. Contrairement à une plage standard, elle s'agrandit automatiquement lorsque vous ajoutez de nouvelles lignes. Étant donné que votre tableau croisé dynamique est lié au nom du tableau plutôt qu'aux coordonnées de cellule fixes, il inclut toujours de nouvelles lignes dans sa plage source une fois actualisée.

Exemple 1 : Calcul de la contribution et de la distribution

Visualiser l’impact de chaque partie sur l’ensemble

Afficher les valeurs comme vous permet de voir comment chaque sous-catégorie contribue au total sans créer une seule formule.

Le scénario : Vous souhaitez connaître le montant en dollars pour chaque domaine, ainsi que son pourcentage du chiffre d’affaires total de l’entreprise.

Tout d'abord, vous devez créer un tableau croisé dynamique à partir de votre tableau :

1. Cliquez n'importe où dans le tableau Excel et dans le Insérer onglet, cliquez sur Tableau croisé dynamique.

2. Assurez-vous que le nom de votre table (T_SalesData) est dans le Tableau/Plage champ, et Nouvelle feuille de travail est coché, puis cliquez sur D'ACCORD.

3. Dans le Champs du tableau croisé dynamique volet, faites glisser Région dans le Lignes zone pour créer un Étiquettes de ligne colonne de votre tableau croisé dynamique.

4. Traîner Revenu dans le Valeurs zone deux fois pour créer deux colonnes identiques.

Vous êtes maintenant prêt à calculer les proportions :

1. Clic droit n'importe quel numéro dans le Somme des revenus2 colonne et sélectionnez Afficher les valeurs en % du total général.
2. Double-cliquez l'en-tête de cette colonne pour la renommer Pourcentage du total.

Vous pouvez désormais voir les montants en dollars côte à côte avec leurs homologues en pourcentage. Cela montre quelles régions pilotent votre entreprise sans qu'un seul symbole de division n'encombre votre feuille de source.

Si vous ajoutez ultérieurement un nouvel enregistrement de ventes à votre table source, il suffit clic droit votre tableau croisé dynamique et sélectionnez Rafraîchir. Excel analysera automatiquement le tableau développé et mettra à jour vos totaux et proportions.

Exemple 2 : Comparaison des tendances et des performances au fil du temps

Suivi de la croissance et de la variance sans formules de soustraction

Au lieu d'écrire une formule « nouveau moins ancien divisé par ancien » dans Excel, vous pouvez utiliser la logique intégrée pour calculer automatiquement la variance.

Le scénario : Vous souhaitez connaître la croissance des ventes d’un mois à l’autre pour déterminer si vos revenus ont tendance à augmenter ou à diminuer.

En supposant que votre tableau croisé dynamique soit déjà configuré à partir de l'exemple 1 ci-dessus, vous devez d'abord effacer les champs existants en les faisant glisser hors du volet Champs du tableau croisé dynamique.

Ensuite, ajoutez vos nouveaux champs :

1. Lieu Date dans le Lignes zone. Excel 2016 et versions ultérieures (y compris Microsoft 365) regroupent automatiquement les dates en mois dans le tableau croisé dynamique, créant des champs tels que Mois, Jours et Années dans la liste des champs. Si vous utilisez une ancienne version, cliquez avec le bouton droit sur la date et sélectionnez Groupe > Mois.

2. Traîner Revenu dans le Valeurs zone deux fois.

Vous êtes maintenant prêt à appliquer le calcul de la variance :

1. Clic droit une valeur dans le Somme des revenus2 colonne.

2. Dirigez-vous vers Afficher les valeurs sous la forme > % de différence par rapport à….

3. Dans le menu contextuel, définissez le Champ de base au champ mois groupé (tel que Mois (Date)) et le Article de base à (précédent). Ensuite, cliquez sur D'ACCORD.

4. Double-cliquez l'en-tête pour le renommer % de croissance mensuelle.

Votre tableau croisé dynamique affiche désormais clairement vos tendances de croissance ainsi que les montants en dollars.

Vous remarquerez que la première ligne de votre % de croissance mensuelle la colonne est vide. Ne vous inquiétez pas, votre tableau croisé dynamique n'est pas cassé. Puisque vous avez sélectionné (précédent) en tant qu'élément de base, Excel recherche les données du mois précédant votre première entrée. Cependant, comme il n’y a pas de mois précédent, Excel laisse la première cellule vide.

En rapport

Le formatage des nombres sous forme de pourcentages dans Excel est frustrant, mais voici une solution simple

Transformez des chiffres Excel en pourcentages en quelques secondes seulement.

Exemple 3 : Analyse d'une distribution imbriquée avec des totaux parents

Voir les partages internes dans une catégorie spécifique

Afficher les valeurs comme d'Excel est idéal pour les données imbriquées, car il calcule des pourcentages en fonction des groupes parents au lieu de l'ensemble de données complet.

Le scénario : Vous souhaitez connaître les revenus de chaque commercial et le pourcentage du total de sa catégorie parent qu'il représente dans chaque groupe de catégories.

Voici comment procéder après avoir réinitialisé votre tableau croisé dynamique en faisant glisser les champs hors du Lignes et Valeurs domaines :

1. Traîner Catégorie dans le Lignes zone, puis placez Représentant des ventes en dessous. Ensuite, bougez Revenu dans le Valeurs zone deux fois.

2. Clic droit n'importe quel numéro dans le Somme des revenus2 colonne et sélectionnez Afficher les valeurs en % du total de la ligne parent.

3. Renommez l'en-tête de votre colonne de pourcentage % de la catégorie.

Vous pouvez désormais voir exactement comment les performances sont réparties au sein de chaque gamme de produits. Par exemple, Miller représente 46,15 % des revenus de la catégorie Matériel, même si son chiffre d'affaires total dans cette catégorie ne représente qu'une fraction des ventes globales de l'entreprise. Cela fournit une vue beaucoup plus nuancée des performances qu’une simple liste plate ne le pourrait jamais.

Le calcul des proportions en pourcentage sans formules manuelles permet de garder vos classeurs propres et vos résultats fiables. Cette approche automatisée garantit qu'à mesure que vos données augmentent, votre analyse reste précise et facile à gérer. Le menu contextuel comprend d'autres options Afficher les valeurs sous, mais les trois présentées ici sont les plus utiles pour l'analyse. Pour compléter cette configuration sans formule, rendez vos rapports encore plus interactifs en ajoutant des filtres et des trancheurs de chronologie à votre tableau croisé dynamique, ce qui donne un tableau de bord véritablement dynamique.

Système d'exploitation

Windows, macOS, iPhone, iPad, Android

Essai gratuit

1 mois

Microsoft 365 inclut l'accès aux applications Office telles que Word, Excel et PowerPoint sur jusqu'à cinq appareils, 1 To de stockage OneDrive et bien plus encore.