2 façons de calculer le nombre distinct avec des tableaux croisés dynamiques
La ligne de fond : Apprenez deux façons de résoudre le défi de l’analyse de données, le calcul du nombre distinct, avec des tableaux croisés dynamiques.
Niveau de compétence : Intermédiaire
Tutoriel vidéo
Télécharger le fichier Excel
J’ai inclus le fichier original et le fichier de la solution pour que vous puissiez les télécharger ici :
Défi d’analyse des données.xlsx (16,1 KB)
Défi d’analyse de données – Solution tableau croisé dynamique FINAL.xlsx (118,4 KB)
Compter les rangs uniques
Dans ce billet, nous allons examiner deux façons différentes de faire un compte distinct à l’aide de tableaux croisés dynamiques. Ces deux méthodes ont été soumises comme solutions au défi d’analyse de données que vous pouvez trouver ici :
Défi d’analyse de données Excel
Pour résumer le défi, nous voulons créer un rapport récapitulatif du nombre de transactions par étape, mais il y a plusieurs lignes par transaction dans les données CRM. Nous devons donc trouver un moyen de créer un compte distinct (en comptant les lignes uniques) pour chaque deal afin de pouvoir les additionner.
Au fait, merci à tous ceux qui ont soumis une solution au défi des données ! Il y a eu beaucoup d’excellentes soumissions.
Solution #1 – Utiliser une colonne d’aide
La grande chose à propos de cette solution est qu’elle peut être utilisée dans n’importe quelle version d’Excel.
Commencez par transformer vos données en un tableau Excel. Pour ce faire, il suffit de sélectionner n’importe quelle cellule de l’ensemble de données, et de cliquer sur Format as Table dans l’onglet Accueil. Faites un clic droit sur le format de tableau que vous souhaitez et sélectionnez Appliquer et effacer le formatage.
Cliquez sur OK lorsque la fenêtre Format as Table apparaît.
Maintenant que vos données sont au format Tableau, ajoutez une colonne d’aide à droite du tableau et intitulez-la Deal Count. Utilisez la fonction COUNTIF, la plage étant la colonne ID de l’affaire et le critère étant la cellule de la colonne ID de l’affaire qui correspond à la ligne dans laquelle vous vous trouvez.
La formule renverra le nombre de lignes pour chaque numéro d’ID de l’affaire. Si nous divisons la formule par le nombre 1, nous obtiendrons des fractions dans chacune de ces cellules qui, une fois additionnées, compteront une entrée pour chaque transaction.
La modification de la formule peut être vue en vert ici :
=1/COUNTIF(,])
Maintenant que nous avons ces fractions qui nous donneront un compte distinct lorsque nous créerons notre tableau croisé dynamique, nous pouvons aller de l’avant et créer le tableau croisé dynamique en choisissant Tableau croisé dynamique dans l’onglet Insertion.
Pour créer notre rapport récapitulatif en utilisant le nouveau tableau croisé dynamique, mettez le stade des ventes dans la zone Rows et le nombre de transactions dans la zone Sum of Values.
Cela nous donnera le rapport récapitulatif que nous recherchons, avec un décompte des transactions dans chaque étape de vente.
L’avantage d’utiliser un tableau croisé dynamique est qu’à mesure que nous ajoutons ou supprimons des entrées de données sources, nous pouvons rafraîchir le tableau croisé dynamique ( Alt + F5 ) pour inclure ces modifications.
Solution # 2 – Utiliser Power Pivot
Cette solution n’est disponible que pour les versions d’Excel 2013 ou ultérieures pour Windows.
Nous voulons toujours que nos données soient formatées comme un tableau Excel, mais nous n’avons pas besoin d’une colonne d’aide pour cette solution.
Cette fois, lorsque nous créons notre tableau croisé dynamique, nous allons cocher la case qui dit Ajouter ce tableau au modèle de données. (Modèle de données est un autre terme pour PowerPivot.)
Lorsque vous construisez votre tableau croisé dynamique cette fois, vous allez faire glisser Deal ID vers la zone Somme des valeurs.
Ceci nous donne initialement des chiffres que nous ne voulons pas dans notre rapport sommaire. Pour corriger cela, nous voulons faire un clic droit sur l’en-tête de la colonne Somme des ID de transaction et sélectionner Paramètres du champ de valeur. Cela ouvrira une fenêtre où nous pouvons choisir Distinct Count comme type de calcul.
La fonction Distinct Count passe par la colonne Deal ID et nous donne un compte des valeurs uniques, de sorte que notre rapport sommaire ressemblera à celui de la solution #1.
Comparaison des deux solutions
Ces deux solutions sont excellentes car elles peuvent être rafraîchies lorsque de nouvelles données sont ajoutées à la table source.
L’avantage de la solution n°1 est qu’elle peut être réalisée dans n’importe quelle version d’Excel. Cela dit, si vous exécutez la version 2013 ou ultérieure sous Windows, la solution n° 2 est l’option supérieure. En effet, la solution #1 devient bancale lorsque vous essayez de filtrer les données vers le bas (par exemple, pour un certain produit) ou d’utiliser des slicers pour disséquer davantage les données.
Si vous souhaitez en savoir plus sur l’utilisation des tableaux croisés dynamiques, j’ai un article de blog distinct que vous pouvez consulter ici : Introduction aux tableaux croisés dynamiques et aux tableaux de bord.
Autres solutions
Il y avait beaucoup d’autres excellentes solutions au défi qui ont été soumises. Elles comprenaient l’utilisation de Power Query et de nouvelles fonctions dynamiques. Nous jetterons un coup d’œil à ces dernières dans de futurs billets, mais je voulais commencer par ces deux-là parce qu’elles étaient plus universelles en termes d’accès à la version d’Excel.
Les tableaux croisés dynamiques et les tableaux de bord sont des exemples de ces solutions.