Suivre les progrès vers un objectif - Articles TechTV

Avec la nouvelle année à venir, vous voudrez peut-être suivre les progrès vers une sorte d'objectif. Ce graphique sympa dans Excel contient des éléments qui indiquent si vous êtes sur la bonne voie pour atteindre un objectif. Dites que vous voulez courir 250 milles ce trimestre.

La ligne noire est le but
La ligne bleue est la ligne "sur la bonne voie" - vous devez courir 2,7 miles par jour pour atteindre l'objectif
La ligne verte correspond aux miles parcourus
La ligne rouge est la prédiction de l'endroit où vous terminerez le trimestre, en fonction de votre progression jusqu'à présent. La ligne rouge est de loin la partie la plus cool du graphique.

Voici comment créer le graphique.

Entrez ces en-têtes en haut d'une feuille de calcul. Dans la cellule A5, entrez la première date du graphique.
Mettez le pointeur de la cellule en A5. Vous remarquerez qu'il y a un point carré dans le coin inférieur droit de A5. C'est la poignée de remplissage. Avec la souris, saisissez la poignée de recopie et faites-la glisser vers le bas. Regardez l'info-bulle jusqu'à ce que vous voyiez que vous avez fait glisser suffisamment loin pour inclure la dernière date du trimestre.
La formule pour B2 est =COUNT(A5:A200). Cela comptera le nombre de jours dans votre plage.
La formule pour B3 est =B1/B2. Cela calcule la distance à parcourir chaque jour pour atteindre l'objectif.
Utilisez la colonne B pour entrer dans quelle mesure vous avez couru chaque jour.
Bien que la colonne B contienne vos données réelles, elle ne convient pas pour créer le graphique. Vous allez en fait créer une toute nouvelle plage de graphiques dans les colonnes C: F qui contiendra les éléments nécessaires au graphique.
La colonne C contiendra des dates. Si un axe de votre plage de graphiques contient des nombres ou des dates, vous devez laisser la cellule supérieure gauche de la plage de graphiques vide.
Il s'agit d'un bogue étrange dans Excel - laisser cette cellule vide permettra à Intellisense d'Excel de reconnaître la plage de graphique plus tard. Donc, laissez la cellule C5 vide et entrez les en-têtes pour la distance, le suivi et l'objectif dans D5: F5.
La formule en C5 est =A5.
Sélectionnez la cellule C5. Dans le menu, sélectionnez Formater les cellules. Sous l'onglet Numérique, choisissez la catégorie Date, puis le format 3/14.
Sélectionnez la cellule C5. Faites glisser la poignée de recopie vers le bas pour copier cette formule. Faites glisser vers le bas aussi loin que vous avez des données dans la colonne A.
Pour les colonnes D et E, les formules de la ligne 5 seront différentes de celles des lignes 6 et ci-dessous. Les formules de la ligne 5 fourniront un début numérique. Les formules de la ligne 6 et ci-dessous incrémenteront ces valeurs numériques de la ligne 5. La formule pour D5 est =B5- cela copiera simplement la distance du premier jour dans la plage du graphique.
Dans une étape ultérieure, Excel ajoutera une courbe de tendance basée sur les valeurs de la colonne D. Si vous copiez simplement D5 vers le bas, vous vous retrouveriez avec des zéros pour les dates futures. Les zéros invalideront la courbe de tendance. L'astuce est que vous devez remplir les dates futures avec des valeurs d'erreur # N / A.
En anglais, alors, la formule dit essentiellement: "Si nous avons exécuté aujourd'hui, ajoutez la valeur d'aujourd'hui au total d'hier. Sinon, mettez un N / A dans cette cellule.

Pour obtenir ce résultat, entrez =IF(NOT(ISBLANK(B6)),D5+B6,NA())dans la cellule D6.
Sélectionnez la cellule D6. Double-cliquez sur la poignée de recopie pour copier cette formule vers le bas. L'astuce du double-clic fonctionne car la colonne à gauche de cette cellule contient des valeurs qui guideront Excel jusqu'où la formule doit être copiée.
La formule dans E5 doit copier le par jour à partir de B3. Entrez =B3dans E5.
La formule pour le reste de E doit ajouter B3 au jour précédent. La formule pour E6 pourrait être =E5+B3mais cette formule ne fonctionnerait pas lorsque vous copiez la cellule vers les autres lignes. Vous voulez toujours que la formule ajoute B3 à la cellule ci-dessus.
Ainsi, utilisez =E5+$B$3dans E6. Les signes dollar garantissent que lorsque vous copiez la formule, elle pointe toujours vers B3.
Remettez le pointeur de la cellule sur E6 et double-cliquez sur la poignée de recopie pour copier la formule vers le bas.
La formule pour l'ensemble de la colonne F est =$B$1. Cette plage sera utilisée pour dessiner la ligne de but noire sur le graphique. Voici une astuce pour saisir cette formule. Commencez en F5. Tapez le signe égal. À l'aide de la souris, touchez B1. Ensuite, tapez la touche F4 pour insérer les signes dollar.
Appuyez sur Ctrl + Entrée pour accepter la formule et rester dans la cellule actuelle. Vous pouvez maintenant double-cliquer sur la poignée de recopie pour copier la formule vers le bas.
Enfin, vous êtes prêt à créer le graphique. Sélectionnez la plage de C4: F94. Utilisez Insertion - Graphique dans le menu ou cliquez sur l'icône Assistant Graphique dans la barre d'outils standard.
À l'étape 1, choisissez un graphique en courbes.
Le graphique nécessitera beaucoup de personnalisation, mais rien de tout cela ne se produit dans l'assistant, alors cliquez simplement sur Terminer pour créer ce graphique horrible.
Cliquez avec le bouton droit sur l'arrière-plan gris et choisissez Format de la zone de tracé.
Choisissez Effets de remplissage.
Configurez un dégradé à 2 couleurs comme indiqué ici.
Cliquez avec le bouton droit sur la ligne jaune de la série Objectif. Choisissez Formater la série de données.
Choisissez une ligne bleue épaisse pour la série de buts.
Répétez les étapes 23 à 24 pour changer la ligne de trace en une fine ligne noire.
Il est difficile de formater la série Distance, car il n'y a que 2 points et il est presque impossible de voir. S'il est difficile de cliquer avec le bouton droit sur un élément du graphique, vous pouvez utiliser la liste déroulante de la barre d'outils Graphique.
Une fois la série de distances sélectionnée, utilisez l'icône Propriétés pour ouvrir la boîte de dialogue Format de la série sélectionnée.
Ajoutez un marqueur à cette boîte de dialogue. Changez la couleur en bleu foncé et l'épaisseur de la ligne en moyenne.
Pour ajouter la courbe de tendance, vous devrez pouvoir faire un clic droit sur la série de distances. C'est presque impossible à faire pour le moment. Alors - je vous suggère de "tricher". Entrez un faux point de données de 20 dans la cellule B7. Cela vous permettra de faire un clic droit sur la série de distances. Choisissez Ajouter une courbe de tendance.
Dans la boîte de dialogue Ligne de tendance, choisissez la Ligne de tendance linéaire.
Sélectionnez la cellule B7 et tapez la touche Suppr pour y effacer la fausse valeur. Vous aurez maintenant la courbe de tendance dessinée sous la forme d'une ligne noire épaisse.
Cliquez avec le bouton droit sur la ligne de tendance et choisissez Formater la ligne de tendance.
Remplacez la courbe de tendance par une ligne rouge brisée avec un poids fin.
Sous l'onglet Options du Format de la courbe de tendance, modifiez le nom de la série en Prédiction

Vous avez maintenant un graphique complet. Si vous manquez une journée de course, entrez un zéro dans la colonne B. Si vous manquez quelques jours, la ligne de prédiction rouge indiquera que vous n'êtes pas sur la bonne voie pour atteindre l'objectif.