Supposons que vous ayez une énorme feuille de calcul contenant toutes les lectures de précipitations pour un lieu donné au cours des cent dernières années. Dans les cellules A2: A37987, vous avez les dates allant du 1er janvier 1903 au 31 décembre 2006.

Dans les cellules B2: B37987, vous avez les mesures pour chaque date. En outre, certaines des mesures peuvent être nulles (s’il n’y a pas de pluie pour la journée) ou vides (si aucune lecture n’a été prise ce jour-là). Avec toutes ces informations, vous souhaitez calculer la moyenne des précipitations historiques pour un jour donné de l’année.

Une solution implique l’utilisation de formules matricielles, comme détaillé ici:

  1. Sélectionnez toutes les cellules de la colonne A qui contiennent des dates et attribuez à cette plage le nom Dates.

  2. Sélectionnez toutes les cellules de la colonne B qui contiennent des données sur les précipitations et attribuez à cette plage le nom de précipitations.

  3. Dans la colonne D, à partir de la cellule D2, placez tous les jours de l’année. Vous devriez vous retrouver avec D2 à D366 rempli de dates.

  4. Dans la cellule E2, entrez la formule matricielle suivante (terminez la formule en appuyant sur Maj + Ctrl + Entrée). Le résultat de la formule est la somme de toutes les cellules de la plage de précipitations, pour la date spécifiée dans la cellule D2.

=SUM((MONTH(Dates)=MONTH(D2))(DAY(Dates)=DAY(D2))Rainfall)

Dans la cellule F2, entrez la formule matricielle suivante (terminez la formule en appuyant sur Maj + Ctrl + Entrée). Le résultat de la formule est le nombre de cellules de la plage de précipitations, pour la date de la cellule D2, qui contiennent une valeur.

=SUM((MONTH(Dates)=MONTH(D2))(DAY(Dates)=DAY(D2))(Rainfall<>""))

Dans la cellule G2, entrez la formule régulière suivante. Il s’agit de votre moyenne pour la date dans la cellule D2.

=IF(F2<>0,E2/F2,"")

Sélectionnez la plage E2: G2 et copiez vers le bas pour toutes les dates indiquées dans la colonne D.

Cette approche fonctionne, mais le calcul prend un certain temps. En effet, vous avez effectivement entré 730 formules matricielles, chacune vérifiant plus de 37 000 cellules. C’est beaucoup de travail, et par conséquent il peut sembler que votre machine s’est « bloquée » après avoir terminé l’étape 7. Elle ne s’est pas bloquée; il faudra juste un certain temps pour terminer les calculs.

Pour réduire le nombre de calculs à effectuer, vous pouvez utiliser une variante des étapes ci-dessus. Suivez les étapes 1 à 3, comme indiqué, puis placez la formule matricielle suivante dans la cellule E2:

=AVERAGE(IF(ISNUMBER(Dates)ISNUMBER(Rainfall)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

Vous pouvez ensuite copier la formule vers le bas pour toutes les dates indiquées dans la colonne D.

Le résultat de cette formule est la pluviométrie moyenne réelle, la même que celle indiquée dans la colonne G dans l’approche précédente.

Cette formule fonctionne en raison de la façon dont l’arithmétique booléenne fonctionne dans Excel. La fonction ISNUMBER renvoie True ou False et les comparaisons (MONTH et DAY) retournent True ou False. Ces résultats sont tous multipliés les uns par rapport aux autres, ce qui donne Vrai uniquement si tous les tests individuels sont Vrai. Ce n’est que s’ils sont tous vrais que la moyenne des précipitations pour cette date particulière sera calculée.

Vous pouvez réduire encore plus les frais généraux de calcul en supprimant simplement tout le tableau qui calcule les moyennes pour chaque jour de l’année. Avec vos dates et précipitations dans les colonnes A et B, procédez comme suit:

  1. Sélectionnez toutes les cellules de la colonne A contenant des dates et attribuez à cette plage le nom Dates.

  2. Sélectionnez toutes les cellules de la colonne B contenant des données sur les précipitations et attribuez à cette plage le nom de précipitations.

  3. Dans la cellule D2, placez la date pour laquelle vous souhaitez vérifier les précipitations moyennes. (L’année n’est pas importante; seuls le mois et le jour sont utilisés dans le calcul.)

  4. Entrez la formule suivante dans la cellule E2:

=AVERAGE(IF(ISNUMBER(Dates)ISNUMBER(Rainfall)(MONTH(Dates)=MONTH(D2))*(DAY(Dates)=DAY(D2)),Rainfall))

C’est ça. Maintenant, vous pouvez changer la date dans la cellule D2 comme vous le souhaitez, et la cellule E2 indiquera toujours les précipitations moyennes pour cette date. La formule de la cellule E2 est la même que la formule utilisée dans la dernière approche; la différence est que vous ne le calculez pas pour tous les jours d’une année et que le calcul est donc beaucoup plus rapide.

Une autre approche implique l’utilisation des capacités de filtrage d’Excel.

Cependant, avant de pouvoir les utiliser correctement, vous devez créer une colonne qui affiche uniquement le mois et le jour de chaque date de vos données. Utilisez cette formule dans la cellule C2:

=MONTH(A2) & "-" & DAY(A2)

Maintenant, activez le filtrage automatique (Données | Filtre | Filtre automatique) et utilisez la liste déroulante en haut de la nouvelle colonne pour sélectionner la date pour laquelle vous voulez une moyenne. Vous utilisez ensuite la formule suivante, placée dans n’importe quelle cellule souhaitée, pour afficher la pluviométrie moyenne pour la date sélectionnée:

=SUBTOTAL(1,B:B)

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (2350) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Calculating_Averages_by_Date [Calcul des moyennes par date].