John a une énorme feuille de calcul qui comprend des données quotidiennes couvrant environ soixante ans. Il souhaite proposer une formule qui calculera les valeurs médianes pour chaque mois de la période couverte par les données.

Avant de proposer une solution, il est préférable de formuler quelques hypothèses. Pour les besoins de cette astuce, supposons que les données quotidiennes se trouvent dans les colonnes A et B. Dans la colonne A se trouvent les dates associées aux données et dans la colonne B, les valeurs de données réelles pour chacune de ces dates. En outre, les cellules A1 et B1 contiennent des en-têtes pour chaque colonne. Cela signifie que les données réelles se situent approximativement dans la plage A2: B22000.

Pour faciliter l’utilisation de vos formules, vous devez définir des noms pour les données des colonnes A et B. Sélectionnez la plage de dates (par exemple, A2: A22000) et attribuez-lui un nom tel que AllDates. Sélectionnez la plage de données correspondantes (par exemple, B2: B22000) et utilisez le même processus pour créer un nom tel que AllData.

Vous pouvez utiliser des formules matricielles pour calculer les valeurs médianes réelles. Cela implique de configurer une autre table de données pour contenir les médianes. Placez des en-têtes tels que «Mois» dans la cellule E1 et «Médiane» dans la cellule F1. Dans la cellule E2, placez le premier jour du premier mois de votre ensemble de données, tel que 1/1/1940. Dans la cellule E3, mettez une date un mois plus tard, comme le 01/02/1940. Sélectionnez ces deux cellules (E2: E3) et faites glisser la poignée de recopie vers le bas pour le nombre de mois souhaité dans le tableau de données.

S’il n’y a pas de blanc dans vos soixante ans de données, entrez la formule suivante dans la cellule F2:

=MEDIAN(IF(DATE(YEAR(AllDates),MONTH(AllDates),1)=E2,AllData))

Finalisez la formule en appuyant sur Ctrl + Maj + Entrée, ce qui indique à Excel qu’il s’agit d’une formule matricielle. Vous pouvez ensuite copier la formule de F2 dans chacune des cellules de la colonne F qui a un mois correspondant dans la colonne E. La formule analyse les dates de la colonne B, et si l’année et le mois sont égaux à la date que vous avez mise dans la cellule E2 , puis la médiane est calculée à partir de tous les points de données correspondants.

S’il y a des blancs dans vos soixante ans de données (une date dans la colonne A sans valeur correspondante dans la colonne B), la formule traite le blanc comme s’il s’agissait d’une valeur nulle. Si vous avez des blancs, cela peut entraîner des médianes asymétriques. Pour contourner ce problème, vous pouvez utiliser une formule matricielle différente qui vérifie et ignore les valeurs vides:

=MEDIAN(IF((DATE(YEAR(AllDates),MONTH(AllDates),1)=E2)*ISNUMBER(AllData),AllData))

Il y a une mise en garde à l’utilisation de formules matricielles de cette manière. Si vous avez soixante ans de données, avec environ 22 000 valeurs individuelles, cela fait encore beaucoup de mois: environ 720 d’entre eux. Cela signifie que vous créez 720 formules matricielles, chacune analysant 22 000 valeurs de données pour arriver à une réponse. C’est beaucoup de calculs en cours, vous remarquerez donc un ralentissement de la réactivité d’Excel chaque fois qu’il recalcule la feuille de calcul.

Si la lenteur devient un problème, vous pouvez envisager de réorganiser vos données d’origine afin que chaque ligne de la feuille de calcul représente un mois individuel. La colonne A pourrait contenir le mois de la ligne (1/1/1940, 2/1/1940, 3/1/1940, etc.) et les colonnes B: AF correspondraient aux jours 1 à 31 pour chaque mois. Les cellules qui se croisent dans le tableau peuvent alors contenir le point de données pour chaque jour du mois et vous pouvez utiliser la fonction MEDIANE dans la colonne AG pour calculer la médiane de chaque mois. Il en résulte toujours 720 formules, mais ce sont des formules régulières dont chacune n’a besoin que de traiter l’équivalent d’un mois de données au lieu des formules matricielles qui doivent chacune traiter soixante ans de données. Le résultat est des calculs beaucoup plus rapides.

Bien sûr, pour la plupart des gens, l’idée de réorganiser une telle quantité de données est suffisante pour vous tenir éveillé la nuit. Au lieu de cela, vous pouvez adopter une approche totalement différente pour analyser les données. Cette approche est possible car une médiane est une fonction statistique très facile à calculer manuellement. Vous triez simplement votre ensemble de données et, si le nombre d’éléments dans l’ensemble de données est impair, sélectionnez l’élément du milieu. Si le nombre d’éléments est pair, vous prenez la moyenne des deux éléments du milieu.

Pour se préparer à analyser les données, il y a plusieurs choses à faire.

Tout d’abord, il sera utile d’avoir un moyen d’identifier de manière unique le mois de chaque point de données. Dans la cellule C2, placez la formule suivante:

=100*Year(A2)+Month(A2)

Il en résulte une valeur telle que 194001, 194002, 194003, etc. stockée dans la colonne C. Il s’agit de la valeur du mois unique. Maintenant, vous devez trier les données par colonne C, puis par colonne B. Les deux tris doivent être dans l’ordre croissant, de sorte que vous vous retrouvez avec vos données d’abord triées par année / mois, puis par valeur dans l’année / mois.

Ensuite, vous devez ajouter des sous-totaux à vos données. Choisissez Sous-totaux dans le menu Données, qui affiche la boîte de dialogue Sous-total. Vous voulez ajouter un sous-total à chaque changement dans la colonne C. La fonction à utiliser est Count, et vous voulez ajouter le sous-total à la colonne B. Lorsque vous cliquez sur OK, vous vous retrouvez avec 720 sous-totaux, un pour chaque mois dans les données plage, chacun indiquant le nombre d’éléments de données au cours de ce mois.

Pour obtenir les médianes, ajoutez une formule à la cellule D2:

IF(RIGHT(B2,5)="Count", IF(MOD(C2,2)=1, INDIRECT("B"&(ROW()-1)-C2/2+1/2), (INDIRECT("B"&(ROW()-1)-C2/2)+INDIRECT("B"&(ROW()-1)-C2/2+1))/2), "")

La formule examine ce qui se trouve dans la cellule B2, et si elle contient le mot «Count», alors elle sait qu’il s’agit d’une ligne de sous-total. Dans ce cas, il vérifie si le décompte dans la cellule C2 est pair ou impair. Si c’est impair, alors la fonction INDIRECT est utilisée pour saisir quelle que soit la valeur médiane du mois. S’il est pair, les deux valeurs moyennes du mois sont additionnées et divisées en deux.

Le résultat est que vous avez maintenant des valeurs médianes pour chaque mois, dans la même ligne que vos sous-totaux. Ainsi, vous pouvez réduire le contour des données (cliquez sur les signes plus dans la zone de contour à gauche de vos données)

de sorte que vous n’affichez que ces lignes de sous-total.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (12727) s’applique à Microsoft Excel 2007, 2010, 2013, 2016, 2019 et Excel dans Office 365. Vous pouvez trouver une version de cette astuce pour l’ancienne interface de menu d’Excel ici:

lien: / excel-Deriving_Monthly_Median_Values ​​[Dérivation des valeurs médianes mensuelles].