Cette question revient une fois par décennie: pouvez-vous faire une médiane dans un tableau croisé dynamique. Traditionnellement, la réponse était non. Je me souviens qu'en 2000, j'ai embauché le MVP Excel Juan Pablo Gonzalez pour écrire une macro géniale qui créait des rapports qui ressemblaient à des tableaux croisés dynamiques mais qui avaient des médianes.
Ainsi, quand Alex, lors de mon séminaire Houston Power Excel, a posé des questions sur la création de médianes, j'ai rapidement répondu «non». Mais alors… je me suis demandé si DAX avait une fonction médiane. Une recherche rapide sur Google et oui! Il existe une fonction DAX pour la médiane.
Que faut-il pour utiliser DAX dans un tableau croisé dynamique? Vous avez besoin de la version Windows d'Excel exécutant Excel 2013 ou une version plus récente.
Voici mon ensemble de données très simple que je vais utiliser pour tester le calcul des médianes des tableaux croisés dynamiques. Vous pouvez voir que la médiane de Chicago devrait être de 5000 et la médiane de Cleveland devrait être de 17000. Dans le cas de Chicago, il y a cinq valeurs, donc la médiane sera la troisième valeur la plus élevée. Mais pour l'ensemble des données, il y a 12 valeurs. Cela signifie que la médiane se situe entre 11 000 et 13 000. Excel calcule la moyenne de ces deux valeurs pour renvoyer 12 000.
Pour commencer, sélectionnez une cellule dans vos données et appuyez sur Ctrl + T pour formater les données sous forme de tableau.
Ensuite, choisissez Insertion, Tableau croisé dynamique. Dans la boîte de dialogue Insérer un tableau croisé dynamique, cochez la case Ajouter ces données au modèle de données.
Dans les champs du tableau croisé dynamique, choisissez Région et district. Mais ne choisissez pas les ventes. Au lieu de cela, cliquez avec le bouton droit sur l'en-tête du tableau et choisissez Nouvelle mesure. "Mesure" est un nom sophistiqué pour un champ calculé. Les mesures sont plus puissantes que les champs calculés dans les tableaux croisés dynamiques classiques.
Dans la boîte de dialogue Définir la mesure, remplissez les quatre entrées ci-dessous:
- Nom de la mesure: médiane des ventes
- Formule
=MEDIAN((Sales)) - Format numérique: nombre
- Décimales: 0
Après avoir créé la mesure, elle est ajoutée à la liste des champs, mais vous devez choisir l'entrée pour l'ajouter à la zone Valeurs du tableau croisé dynamique. Comme vous pouvez le voir ci-dessous, le tableau croisé dynamique calcule correctement les médianes.
Regarder la vidéo
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2197: Médiane dans un tableau croisé dynamique.
Je dois vous dire que je suis extrêmement excité à ce sujet. quand j'étais à Houston pour y faire un séminaire Power Excel, Alex a levé la main et a dit: "Hé, y a-t-il un moyen de faire une médiane dans un tableau croisé dynamique?" Non, vous ne pouvez pas faire de médiane dans un tableau croisé dynamique. Je me souviens qu'il y a 25 ans, mon bon ami, Juan Pablo Gonzales, a en fait apporté une macro pour faire l'équivalent de la médiane, sans utiliser de tableau croisé dynamique - ce n'est tout simplement pas possible.
Mais j'ai eu une étincelle. J'ai dit: "Attendez une seconde", et j'ouvre un navigateur, et je recherche "DAX median", et, bien sûr, il y a une fonction MEDIAN dans DAX, ce qui signifie que nous pouvons résoudre ce problème.
Très bien, donc, pour utiliser DAX, vous devez être dans Excel 2013 ou Excel 2016, ou dans Excel 2010 et disposer du complément Power Pivot; vous n'avez pas besoin de l'onglet Power Pivot, nous devons simplement avoir le modèle de données. Bien? Je vais donc choisir ces données, je vais en faire un tableau avec Ctrl + T, et ils lui donnent un nom sans imagination de "Table 4". Dans votre cas, il peut s'agir du "Tableau 1". Et nous allons insérer un tableau croisé dynamique, ajouter ces données au modèle de données, cliquer sur OK et nous allons choisir Régional sur le côté gauche, mais pas Ventes. Au lieu de cela, je souhaite créer une nouvelle mesure calculée. Je viens donc ici à la table et je clique avec le bouton droit de la souris et dis, Ajouter une mesure. Et cette mesure s'appellera "Médiane des ventes", et la formule sera: = MEDIANE. Appuyez sur l'onglet et choisissez Ventes,parenthèse fermante. Je peux choisir cela comme un nombre avec zéro décimale, utiliser un séparateur de mille et cliquer sur OK. Et, voyons, notre nouveau champ est ajouté ici, nous devons le cocher pour l'ajouter, et il indique que la médiane pour le Midwest est de 12000.
Maintenant, vérifions cela. Donc, ici, tout le Midwest, la médiane de l'ensemble des ensembles de données entre 11 000 et 13 000. Donc, c'est en moyenne 11 et 13, ce qui est exactement ce que la médiane ferait dans Excel ordinaire. Maintenant, ajoutons le district et il indique que la médiane des 5 000 de Chicago, la médiane de Cleveland est de 17 000; Total du Midwest et total général 12 000. Tout cela est correct. C'est vraiment génial? Enfin, médiane dans un tableau croisé dynamique, grâce à un champ calculé, ou mesure, comme on l'appelle, et au modèle de données.
Maintenant, beaucoup de mes conseils préférés - les conseils pour mon séminaire Power Excel en direct - dans ce livre LIVe, les 54 meilleurs conseils de tous les temps. Cliquez sur ce "I" dans le coin supérieur droit pour en savoir plus sur le livre.
Bien. Récapitulation: pouvez-vous faire une médiane dans un tableau croisé dynamique? Oh non, oui, oui vous pouvez, grâce au modèle de données. Vous pouvez créer une médiane; Ctrl + T pour transformer vos données en un tableau; Insérer un tableau croisé dynamique; et cochez cette case, ajoutez ces données au modèle de données; cliquez avec le bouton droit sur le nom de la table et choisissez une nouvelle mesure, et la mesure sera = MEDIAN ((Ventes)). C'est génial.
Merci à Alex d'être venu à mon séminaire et d'avoir posé cette question, merci à vous d'être passé. Je vous verrai la prochaine fois pour une autre diffusion sur Internet.
Télécharger le fichier Excel
Pour télécharger le fichier excel: pivot-table-median.xlsx
