Nora tient un journal météorologique depuis des années. Chaque feuille du classeur est une année distincte, la colonne A sur chaque feuille étant les dates de l’année et la colonne B étant la quantité de précipitations ce jour-là, le cas échéant. Nora aimerait créer un graphique montrant les précipitations de cette année par rapport aux précipitations de l’année dernière à ce jour. Elle se demande s’il existe un moyen pour que ce graphique fasse automatiquement référence aux valeurs de précipitations correctes pour les deux années en fonction de la date d’aujourd’hui.

Ce besoin peut être satisfait de plusieurs façons, en fonction exactement de ce que vous voulez réaliser. Si vous souhaitez simplement comparer les précipitations de cette année aux précipitations de l’année dernière, par date, vous pouvez facilement le faire en définissant des plages nommées dynamiques qui définissent les données que vous souhaitez utiliser.

Pour commencer, supposons que vos données pour 2015 se trouvent dans une feuille de calcul nommée 2015 et vos données pour 2016 (jusqu’à présent) se trouvent dans une feuille de travail nommée 2016.

Sur chaque feuille de calcul, la ligne 1 contient des en-têtes, ce qui signifie que vos dates commencent réellement dans la cellule A2 et vos lectures de précipitations dans la cellule B2.

Suivez ces étapes pour configurer les plages:

  1. Affichez l’onglet Formules du ruban.

  2. Dans le groupe Noms définis, cliquez sur l’outil Gestionnaire de noms. Excel affiche la boîte de dialogue Gestionnaire de noms.

  3. Cliquez sur le bouton Nouveau. Excel affiche la boîte de dialogue Nouveau nom. (Voir la figure 1.)

  4. Dans le champ Nom, entrez le nom CurrentYear (notez qu’il s’agit d’un seul mot, sans espaces).

  5. Dans le champ Se réfère à, entrez la formule suivante:

  6. Cliquez sur le bouton OK pour terminer la création de la plage nommée. La nouvelle plage doit apparaître dans la boîte de dialogue Gestionnaire de noms.

  7. Cliquez à nouveau sur le bouton Nouveau. Excel affiche (à nouveau) la boîte de dialogue Nouveau nom.

  8. Dans le champ Nom, entrez le nom PreviousYear (notez qu’il s’agit, encore une fois, d’un seul mot, sans espaces).

  9. Dans le champ Se réfère à, entrez la formule suivante:

  10. Cliquez sur le bouton OK. La nouvelle plage doit apparaître dans la boîte de dialogue Gestionnaire de noms.

  11. Cliquez une troisième fois sur le bouton Nouveau. Excel affiche la boîte de dialogue Nouveau nom.

  12. Dans le champ Nom, saisissez le nom Dates.

  13. Dans le champ Se réfère à, entrez la formule suivante:

  14. Cliquez sur le bouton Fermer pour fermer la boîte de dialogue Gestionnaire de noms.

Une fois les plages définies, vous pouvez maintenant créer le graphique en utilisant ces plages:

  1. Affichez l’onglet Insertion du ruban.

  2. Dans le groupe Graphiques, cliquez sur l’option Colonne. Excel affiche une liste déroulante.

  3. Cliquez une fois sur le format de la colonne en cluster. (Il s’agit de la première option sous l’en-tête Colonne 2D.) Excel crée un objet de graphique vide dans votre feuille de calcul.

  4. Cliquez avec le bouton droit sur l’objet de graphique. Excel affiche un menu contextuel.

  5. Choisissez Sélectionner les données dans le menu contextuel. Excel affiche la boîte de dialogue Sélectionner la source de données, qui doit être complètement vide car vous n’avez ajouté aucune plage de données au graphique.

  6. Sur le côté gauche de la boîte de dialogue, sous les entrées de légende (série)

en-tête, cliquez sur le bouton Ajouter. Excel affiche la boîte de dialogue Modifier la série. (Voir la figure 2.)

  1. Dans le champ Nom de la série, saisissez 2015.

  2. Dans le champ Valeurs de la série, saisissez ceci: « 2015 »! PreviousYear.

  3. Cliquez sur le bouton OK. La série de données apparaît maintenant dans la boîte de dialogue Sélectionner la source de données.

  4. Cliquez à nouveau sur le même bouton Ajouter sur lequel vous avez cliqué à l’étape 6. Excel affiche à nouveau la boîte de dialogue Modifier la série de données.

  5. Dans le champ Nom de la série, saisissez 2016.

  6. Dans le champ Valeurs de la série, saisissez ceci: ‘2016’! CurrentYear.

  7. Cliquez sur le bouton OK. Cette deuxième série de données apparaît désormais dans la boîte de dialogue Sélectionner la source de données.

  8. Sur le côté droit de la boîte de dialogue, sous Horizontal (Catégorie)

En-tête Étiquettes d’axe, cliquez sur le bouton Modifier. Excel affiche la boîte de dialogue Étiquettes d’axe. (Voir la figure 3.)

  1. Dans la plage d’étiquettes de l’axe, entrez ceci: ‘2016’! Dates.

  2. Cliquez sur le bouton OK. La boîte de dialogue Sélectionner la source de données doit être entièrement remplie avec les informations nécessaires. (Voir la figure 4.)

  3. Cliquez sur OK pour fermer la boîte de dialogue Sélectionner la source de données.

Votre graphique mis à jour, affichant uniquement les dates jusqu’à la date d’aujourd’hui, devrait maintenant être visible. Vous pouvez continuer à formater le graphique, comme vous le souhaitez.

(Par exemple, vous souhaiterez probablement formater les dates dans le graphique afin qu’elles n’incluent pas une année.) De plus, le graphique est dynamique, de sorte que lorsque vous ouvrez le classeur demain, il reflète un jour de plus qu’il ne l’a aujourd’hui.

Une autre façon de gérer cela consiste à reconsidérer la façon dont vous stockez vos données.

Au lieu de stocker toutes vos lectures de précipitations sur des feuilles de calcul distinctes (par année), placez-les toutes sur une seule feuille de calcul. Étant donné qu’Excel peut gérer plus d’un million de lignes de données dans une feuille de calcul, vous ne rencontrerez aucune limitation pratique. (Un million de lignes représente bien plus de 2 700 ans.)

Maintenant, sur une feuille de calcul différente, vous pouvez utiliser deux formules matricielles pour calculer les précipitations cumulées pour les deux années, à ce jour. La formule matricielle suivante fournira les précipitations de l’année précédente:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW())-1,1,1),IF(Data!A2:A1000<=EDATE(NOW(),-12),1,0)))

Cela suppose que les lectures de précipitations d’origine se trouvent sur une feuille de calcul nommée Data et qu’elles ne s’étendent pas au-delà de 1000 lignes. (Vous pouvez modifier l’un ou l’autre, si nécessaire.) Pour obtenir les précipitations à ce jour pour cette année, vous pouvez utiliser cette formule matricielle:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW()),1,1),IF(Data!A2:A1000<=NOW(),1,0)))

Rappelez-vous: ce sont deux formules matricielles, elles doivent donc être saisies en utilisant Ctrl + Maj + Entrée. La valeur unique renvoyée par chaque formule représente les précipitations cumulées de chaque année à ce jour. Ces deux valeurs peuvent ensuite être utilisées dans n’importe quel graphique de votre choix.

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (13427) s’applique à Microsoft Excel 2007, 2010, 2013 et 2016.