À l'aide QUARTILE dans un tableau croisé dynamique Excel pour résumer les données par sous-populations

J'ai un grand tableau de données dans une feuille de calcul Excel que, pour l'essentiel, peut être considéré comme un ensemble de valeurs pour les individus identifiés comme appartenant à différentes sous-populations:

  IndivID   SubPopID  Value
     1          A       33.56
     2          E       42.31
     3          D       16.35
     4          A       50.59
     5          F       80.63
     ...

Il y a plus de 10 000 personnes dans ce tableau, et plus de 50 sous-populations.

Je voudrais calculer le 5-nombre résumé (Min, Q1, Médiane, Q3, Max) pour chacune des sous-populations.

Min et Max sont facile dans un tableau croisé dynamique. Mais Q1, Q2 (médiane) et Q3 ne semble pas être possible.

Question

Est-il un moyen de calculer les quartiles de chaque sous-population dans un tableau croisé dynamique dans Excel?

La réponse devrait ressembler à ceci:

SubPopID      Min     Q1     Q2        Q3      Max
   A         3.23    12.06   20.35   28.29     50.59
   B 
   C
   ...

OriginalL'auteur Assad Ebrahim | 2012-11-18

  1. 16

    Mon expérience, c'est que vous ne pouvez pas faire percentiles dans un tableau croisé dynamique. J'ai juste fait une recherche sur le web et ne vois rien à contredire. Je pense que le mieux que vous pouvez faire est d'avoir votre source de données dans un tableau afin de pouvoir les consulter de manière dynamique, et ensuite utiliser le percentile de la fonction dans une autre table.

    J'ai vu un article sur l'aide de PowerPivot, mais qui est seulement disponible dans Excel 2010, et il a l'air dur. J'ai 2010, et lorsqu'ils sont confrontés à un problème similaire, j'ai quand même décidé à aller la route je suggère ici.

    MODIFIER: Explication de mon alternative proposée:

    En réponse à votre question dans les commentaires, voici comment je voudrais calculer les percentiles à partir d'un tableau de données:

    À l'aide QUARTILE dans un tableau croisé dynamique Excel pour résumer les données par sous-populations

    Il repose sur une formule unique, qui est la même pour chaque cellule à partir de F2 à H6. C'est un la formule de tableau, pour signifier qu'il est entré avec Ctrl-Shift-Enter. Il utilise une instruction if pour filtre sur les différentes SubPopIDs. Voici la formule pour F2:

    =PERCENTILE(IF(Table1[SubPopID]=F$1,Table1[Value],""),$E2/100)
    C'était ma crainte. Aucun de mes recherches sur le web rien trouvé de valeur pour Excel 2007. Pour Excel 2010, cependant, la AGGREGATE( ) fonction semblait très prometteuse, car elle a un quartile de l'option built-in. (Malheureusement, mon client n'utilise pas de Excel2010 et n'a pas de plans de mise à niveau de leur organisation de façon imminente). Je vais garder cet actif pour un couple de jours, et j'espère que nous sommes trompe et que quelqu'un a trouvé une solution intelligente.
    Doug, pourriez-vous élaborer sur ce que tu veux dire par "source de données dans une table et percentile de la fonction dans une autre table"? (Ne vois pas comment je pourrais utiliser Excel " QUARTILE () la fonction d'un sous-ensemble de lignes, par exemple, que sur le SubPopID=A dans l'exemple). Thx.
    Veuillez voir mon edit. Il s'appuie sur une formule de tableau avec un IF Déclaration.
    Génial! Qui fonctionne parfaitement. BTW les captures d'écran et des liens ont été à la fois instrumentale d'être en mesure de reproduire cette approche. Merci beaucoup!
    Vous êtes les bienvenus!

    OriginalL'auteur Doug Glancy

  2. 6

    Si vous avez besoin de vérifier une condition pour plus d'une colonne (a=1 ET b=2) vous pouvez étendre votre réseau en utilisant le caractère * 

    =PERCENTILE((IF((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Value]),""),$E2/100)
    Bonjour. Je pense que votre formule pourrait avoir un problème -- parenthèses semblent mal placés.
    oui ce doit être le code =PERCENTILE(SI((Table1[SubPopID]=[condition1]) * (Table1[SubPopID2]=[condition2]),Table1[Valeur]),""),$E2/100

    OriginalL'auteur zer0links

  3. 1

    J'ai trouvé ce réponse où une solution est décrite.

    Il utilise la même approche que décrit par Doug Glancy, mais l'associer à un champ calculé dans le tableau croisé dynamique. La réponse donne un exemple de fichier excel.

    OriginalL'auteur Alban