Créer des totaux de course dans Excel est plus facile que vous ne le pensez
Les totaux de course montrent comment les chiffres se construisent sur une période prolongée, une entrée à la fois, vous permettant de voir les tendances et les modèles que les données brutes pourraient ne pas révéler. La création de totaux d'exécution dans Excel est simple, mais vous devez faire attention à utiliser la bonne méthode en fonction de la structure de vos données.
Sommaire
Créer des totaux de course dans des gammes régulières
Pour créer un total en cours d'exécution dans une gamme Excel qui n'est pas formatée comme une table Excel, vous devez utiliser une combinaison de références absolues et relatives.
Dans cet exemple, vous souhaitez que la colonne totale en cours d'exécution se mette à jour chaque fois que vous ajoutez une autre valeur à la colonne de profit.
Pour ce faire, dans la cellule C2, type:
=SUM($B$2:B2)
et appuyez sur Ctrl + Entrez pour commettre la formule et rester dans la même cellule.
Plutôt que de taper les symboles du dollar manuellement, placez votre curseur dans la référence cellulaire dans la formule et appuyez sur F4.
L'ajout de symboles d'un dollar à la première référence (connue sous le nom de référence absolue) le répare. En d'autres termes, chaque fois que la formule calcule le total de fonctionnement sur chaque ligne, elle prendra toujours cette cellule comme début des valeurs à résumer. D'un autre côté, la deuxième référence ne contient pas de symboles en dollars (connu sous le nom de référence relative), car chaque fois que vous reproduisez la formule à la ligne suivante, vous souhaitez qu'il prenne la figure correspondante.
Pour voir cela en action, cliquez d'abord et faites glisser la poignée de remplissage dans la cellule C2 vers la cellule C3.
Ensuite, sélectionnez la cellule C3 et notez que la formule dans la barre de formule est:
=SUM($B$2:B3)
La référence Absolute $ B à 2 $ est restée fixe, tandis que la référence relative B3 reprend la valeur suivante dans la colonne de profit pour mettre à jour le total de fonctionnement.
Vous pouvez maintenant cliquer et faire glisser la poignée de remplissage vers le bas de la plage afin que la formule soit prête à ramasser toutes les nouvelles valeurs que vous ajoutez aux lignes suivantes.
Cependant, il y a un problème avec cela en l'état: le total de course est répété même dans les lignes où il n'y a pas encore de chiffres de profit. Bien que ces totaux soient techniquement corrects, ils ont l'air désordonnés et peuvent provoquer une confusion.
Il existe différentes façons de résoudre ce problème, mais ma méthode préférée est d'utiliser la fonction IF. Voici la formule à taper dans la cellule C2:
=IF(ISNUMBER(B2),SUM($B$2:B2),"")
En d'autres termes, si la cellule B2 contient un nombre, vous voulez Excel pour calculer le total de fonctionnement dans la cellule C2 comme dans l'exemple ci-dessus. Cependant, si la cellule B2 ne contient pas de nombre, vous voulez que la cellule C2 reste vide.
Maintenant, lorsque vous cliquez et faites glisser la poignée de remplissage vers le bas de la plage, uniquement lorsqu'il y a un nombre dans une cellule dans la colonne B voyez-vous le total de course dans la cellule correspondante dans la colonne C.
Cliquez et faites glisser la poignée de remplissage plus bas que le bord inférieur actuel de la plage pour permettre une croissance potentielle.
Comme vous pouvez le voir dans la capture d'écran ci-dessous, lorsque j'ajoute une valeur à la cellule B10, les mises à jour totales en cours dans la cellule C10.
Création de totaux de course dans les tables Excel
Si, comme moi, vous préférez que vos données soient formatées sous forme de table Excel structurée, vous devrez utiliser une méthode différente pour créer un total en cours d'exécution. Plus précisément, il existe deux différences clés entre la création de totaux de fonctionnement dans les gammes non formatées et les tables formatées.
Tout d'abord, plutôt que d'utiliser des références cellulaires, vous devez utiliser des références structurées. Cela signifie que vous pouvez utiliser les en-têtes de colonne de votre table. Deuxièmement, si vous utilisez la même formule que dans les gammes régulières, Excel aura du mal à continuer correctement le total de fonctionnement si vous ajoutez de nouvelles lignes au bas de votre table.
Au lieu de cela, vous devez utiliser la fonction d'index. Dans la cellule C2, type:
=SUM(INDEX((Profit),1):(@Profit))
où Indice ((profit), 1 dit à Excel de démarrer le calcul dans la première ligne de la colonne de profit, et (@Profit) dit à Excel que la valeur finale à considérer dans le calcul se trouve dans la ligne relative actuelle de la même colonne.
Lorsque vous appuyez sur Entrée, la formule reproduira automatiquement le reste de la colonne C.
De plus, si vous ajoutez une nouvelle ligne au bas de votre table, la formule est automatiquement étendue vers le bas.
Cependant, comme avec l'exemple précédent, le total de course est répété dans les lignes où il n'y a pas encore de chiffres de profit, vous devez donc utiliser la fonction IF pour dire à Excel de retourner un blanc dans toutes les lignes où il n'y a pas de valeur dans la colonne de profit:
=IF(ISNUMBER((@Profit)),SUM(INDEX((Profit),1):(@Profit)),"")
Au lieu d'utiliser la fonction d'index, vous pouvez utiliser la fonction de prise avec exactement la même syntaxe. Alors que la fonction d'index renvoie une valeur à partir d'une ligne ou d'une colonne spécifiée de n'importe où dans un tableau, la fonction de prise renvoie un nombre spécifié de lignes ou de colonnes à partir du début ou de la fin d'un tableau. Cependant, contrairement à la fonction de prise, la fonction d'index est compatible avec les anciennes versions d'Excel.
Création de totaux de course dans les phoques
Les brochis sont un excellent moyen de résumer et de manipuler vos données pour une analyse rapide, donc l'ajout d'une colonne totale en cours est un excellent moyen de rendre vos données cumulatives encore plus utiles.
Pour créer le poivable, sélectionnez les cellules contenant les données brutes et dans l'onglet INSERT sur le ruban, cliquez sur la moitié supérieure du bouton « pivottable ».
Ensuite, dans la boîte de dialogue Table ou Range à partir de la table ou de la plage, vérifiez que la table ou la plage correcte est sélectionnée, décidez si vous souhaitez que le tiillonnable soit placé sur la même feuille de calcul que les données source ou une nouvelle feuille de calcul, puis cliquez sur « OK ».
Avoir votre poivrage dans une feuille de calcul distincte des données source aide à garder votre classeur Excel plus organisé si vous travaillez avec un ensemble de données particulièrement important. N'oubliez pas de renommer le nouvel onglet afin que vous puissiez facilement dire ce que contient chaque feuille de calcul.
Maintenant, cliquez et faites glisser le champ « Mois » dans la zone des lignes du volet Ticottable Fields, et le champ « Profit » dans la zone VALEUR.
Pour créer un total fonctionnel de la colonne de profit, vous devez d'abord créer une deuxième colonne dans le Ticottable contenant les mêmes données. Donc, cliquez et faites glisser le « profit » dans la zone de valeurs, exactement comme vous l'avez fait à l'étape précédente.
Ensuite, cliquez avec le bouton droit sur l'en-tête de la colonne de la deuxième colonne de profit, survolez « Afficher les valeurs comme » et cliquez sur « Exécuter le total ».
Ensuite, choisissez une option dans le menu déroulant de champ de base, puis cliquez sur « OK ».
Et là, vous l'avez – une colonne totale en cours d'exécution dans votre poivrage. La dernière chose que vous devez faire est de changer l'en-tête de la colonne vers Total de course de sorte que le poivrable est facile à lire.
Une fois que vous avez ajouté votre colonne totale en cours d'exécution, visualisez vos données en tant que graphique pour souligner davantage ses tendances et ses modèles. Pour ce faire, sélectionnez la colonne ou les colonnes que vous souhaitez visualiser et dans l'onglet Insérer du ruban, cliquez sur le graphique pertinent. Ici, j'ai créé un graphique combo qui affiche des totaux mensuels en tant que colonnes et le total en cours d'exécution comme une ligne. De cette façon, je peux comparer les données mensuelles et surveiller la vue d'ensemble en même temps.
