Cet épisode montre trois façons de faire des totaux cumulés.
Un total cumulé est, pour une liste de valeurs numériques, une somme des valeurs de la première ligne à la ligne du total cumulé. Les utilisations courantes d'un total courant sont dans un registre de chéquier ou une feuille de comptabilité. Il existe de nombreuses façons de créer un total en cours, dont deux sont décrites ci-dessous.
La technique la plus simple consiste, sur chaque ligne, à ajouter le total cumulé de la ligne ci-dessus à la valeur de la ligne. La première formule de la ligne 2 est donc:
=SUM(D1,C2)
La raison pour laquelle nous utilisons la fonction SOMME est que, dans la première ligne, nous regardons l'en-tête de la ligne ci-dessus. Si nous utilisons la formule la plus simple et la plus intuitive, =D1+C2une erreur sera générée car la valeur de l'en-tête est du texte plutôt que numérique. La magie est que la fonction SOMME ignore les valeurs de texte, qui sont ajoutées en tant que valeurs nulles. Lorsque la formule est copiée sur toutes les lignes dans lesquelles un total cumulé est souhaité, les références de cellule sont ajustées en conséquence:
L'autre technique utilise également la fonction SOMME, mais chaque formule additionne toutes les valeurs de la première ligne à la ligne affichant le total cumulé. Dans ce cas, nous utilisons un signe dollar ($) pour faire de la première cellule de la référence une référence absolue, ce qui signifie qu'elle n'est pas ajustée lors de la copie:
Les deux techniques ne sont pas affectées par le tri et la suppression de lignes mais, lors de l'insertion de lignes, la formule doit être copiée dans les nouvelles lignes.
Excel 2007 a introduit le tableau qui est une réimplémentation de la liste dans Excel 2003. Les tableaux ont introduit un certain nombre de fonctionnalités très utiles pour les tableaux de données telles que le formatage, le tri et le filtrage. Avec l'introduction des tableaux, nous avons également reçu une nouvelle façon de référencer les parties d'un tableau. Ce nouveau style de référencement est appelé référencement structuré.
Pour convertir l'exemple ci-dessus en un tableau, nous sélectionnons les données que nous voulons inclure dans le tableau et appuyez sur Ctrl + T.Après avoir affiché une invite nous demandant de confirmer la plage du tableau et s'il existe ou non des en-têtes existants, Excel convertit les données dans un tableau formaté:
Notez que les formules que nous avons entrées précédemment restent les mêmes.
L'une des fonctionnalités utiles offertes par les tableaux est la mise en forme automatique et la maintenance des formules lorsque les lignes sont ajoutées, supprimées, triées et filtrées. C'est la maintenance des formules en particulier sur laquelle nous allons nous concentrer et qui peut poser problème. Pour que les tableaux fonctionnent pendant leur manipulation, Excel utilise des colonnes calculées qui sont des colonnes avec des formules telles que la colonne D dans l'exemple ci-dessus. Lorsque de nouvelles lignes sont insérées sont ajoutées en bas, Excel remplit automatiquement les nouvelles lignes avec la formule «par défaut» pour cette colonne. Le problème avec l'exemple ci-dessus est qu'Excel se confond avec les formules standard et ne les gère pas toujours correctement. Ceci est rendu évident lorsque de nouvelles lignes sont ajoutées au bas du tableau (en sélectionnant la cellule en bas à droite du tableau et en appuyant sur TAB):
Cette lacune est résolue en utilisant le nouveau référencement structuré. Le référencement structuré élimine le besoin de référencer des cellules spécifiques à l'aide du style de référencement A1 ou R1C1 et utilise à la place des noms de colonne et d'autres mots-clés pour identifier et référencer les parties d'un tableau. Par exemple, pour créer la même formule de total cumulé utilisée ci-dessus mais en utilisant le référencement structuré, nous avons:
=SUM(INDEX((Sales),1):(@Sales))
Dans cet exemple, nous avons une référence au nom de la colonne, «Ventes», avec le signe arobase (@) pour référencer la ligne dans la colonne dans laquelle se trouve la formule, également appelée ligne courante.
Pour implémenter le premier exemple ci-dessus où nous avons ajouté la valeur totale cumulée de la ligne précédente au montant des ventes de la ligne actuelle, vous pouvez utiliser la fonction OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Si les montants utilisés pour calculer le total courant sont dans deux colonnes, par exemple une pour «Débits» et une pour «Crédits», alors la formule est:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Ici, nous utilisons la fonction INDEX pour localiser les cellules de crédit et de débit de la première ligne, et pour additionner la colonne entière jusqu'aux valeurs de la ligne actuelle. Le total cumulé est la somme de tous les crédits jusqu'à et y compris la ligne actuelle moins la somme de tous les débits jusqu'à et y compris la ligne actuelle.
Pour plus d'informations sur les références structurées en particulier et les tableaux en général, nous vous recommandons le livre Excel Tables: un guide complet pour la création, l'utilisation et l'automatisation de listes et de tableaux par Zack Barresse et Kevin Jones.
Lorsque j'ai demandé aux lecteurs de voter pour leurs conseils préférés, les tables étaient populaires. Merci à Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel et Paul Peton d'avoir suggéré cette fonctionnalité. Peter Albert a rédigé le conseil bonus sur les références lisibles. Zack Barresse a écrit le conseil bonus Running Totals. Quatre lecteurs ont suggéré d'utiliser OFFSET pour créer des plages en expansion pour les graphiques dynamiques: Charley Baak, Don Knowles, Francis Logan et Cecelia Rieb. Les tableaux font maintenant la même chose dans la plupart des cas.
Regarder la vidéo
- Cet épisode montre trois façons d'effectuer des totaux cumulés
- La première méthode a une formule différente dans la ligne 2 que toutes les autres lignes
- La première méthode est = Gauche dans la ligne 2 et = Gauche + Haut dans les lignes 3 à N
- Si vous essayez d'utiliser la même formule, vous obtenez une erreur #Value avec = Total + Number
- La méthode 2 utilise
=SUM(Up,Left)ou=SUM(Previous Total,This Row Amount) - SUM ignore le texte afin que vous n'obteniez pas d'erreur VALUE
- La méthode 3 utilise une plage en expansion:
=SUM(B$2:B2) - L'extension des gammes est cool mais lente
- Lire le livre blanc de Charles Williams sur Excel Formula Speed
- La troisième méthode pose un problème lorsque vous utilisez Ctrl + T et ajoutez de nouvelles lignes
- Excel ne sait pas comment écrire la formule
- Les solutions de contournement nécessitent une certaine connaissance du référencement structuré dans les tableaux
- La solution de contournement 1 est la lenteur
=SUM(INDEX((Qty),1):(@Qty)) - La solution de contournement 2 est le volatile
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) fait référence à la quantité sur cette ligne
- (Qté) fait référence à toutes les valeurs de quantité
Transcription vidéo
Learn Excel for Podcast, Episode 2004 - Totaux en cours
Je diffuserai tout ce livre en podcast. Cliquez sur ce que je dans le coin supérieur droit pour vous abonner.
Salut, bon retour sur le netcast de la mystique. Je suis Bill Jelen. Maintenant ce sujet dans le livre, j'ai été contribué par mon ami Zach Parise. Parlez de tableaux Excel, Zach est l'expert mondial des tableaux Excel. Il a écrit un livre sur les tableaux Excel, mais parlons d'abord de l'exécution des totaux non dans les tableaux.
Donc, quand je pense aux totaux cumulés, il y a trois façons différentes de faire les totaux cumulés, et la façon dont j'ai toujours commencé est dans la première ligne, vous dites simplement, apportez la valeur. Donc égal à tout ce qui est à ma gauche. Très bien donc ce format ici est juste = B2. Ce sont tous du texte de formule ici dans le coin droit, donc vous voyez ce que nous utilisons, puis à partir de là, c'est une simple petite formule égale à la valeur précédente, plus la valeur actuelle à droite et copiez-la vers le bas , mais vous savez maintenant, nous avons ce problème qui nécessitait deux formules différentes et vous savez que dans une situation parfaite, vous avez exactement la même formule tout en bas, et la raison pour laquelle nous devons avoir une formule différente dans la première ligne est que lorsque vous essayez d'ajouter égal 7 plus le mot total, c'est une erreur de valeur,mais le bon ouvrier ici, n'est pas seulement d'utiliser left plus up, mais d'utiliser = (SUM) de la valeur précédente plus la quantité dans cette ligne, et voir que certains sont assez loin pour ignorer les textes. Droite pour cela permet la même formule. jusqu'au bout.
D'accord, c'était quand je commençais à utiliser Excel, j'utilisais cela et puis j'ai découvert la plage en expansion, la plage en expansion dit que nous allons faire L $ 2: L2 et ce qui se passe, c'est que cela commence toujours à la ligne 2, mais ensuite il descend à la ligne actuelle. Donc, quand vous regardez comment cela fonctionne quand il est copié, nous avons toujours commencé la ligne 2, mais nous descendons à la ligne actuelle et c'est devenu ma méthode préférée. J'étais comme, oh, c'est tellement plus sophistiqué et quand nous allons dans les options Excel, allez dans l'onglet Formules et choisissez R1C1 dans le style de référence. Très bien, R1C1, toutes ces formules sont exactement les mêmes tout en bas. Je ne sais pas si vous comprenez R1C1, il est juste bon de savoir que nous avons des formules R1C1 identiques tout en bas.
Retournons. Donc, cette méthode ici est la méthode que j'ai aimée, jusqu'à ce que Charles Williams, un MBP Excel d'Angleterre, qui a un article étonnant sur la vitesse de la formule, la vitesse de la formule Excel, a complètement démystifié cette méthode. Cette méthode, disons que vous avez 10000 lignes, chaque formule regarde deux références. Vous regardez donc 20000 références, mais celle-ci, celle-ci, celle de deux, celle de trois, celle de quatre, celle de cinq, la dernière de 10000 références, et c'est horriblement plus lent et j'ai donc arrêté d'utiliser cette méthode.
Ensuite, je continue à lire Zack dans le livre de Kevin Jones sur les tableaux Excel et je découvre encore un autre problème avec cette méthode. L'une des fonctionnalités utiles offertes par les tableaux est donc «l'ajout, la suppression, le tri et le filtrage des lignes de mise en forme automatique et de maintenance des formules». Très bien, c'est une citation de son livre. Et pour ajouter une ligne à un tableau, il vous suffit d'aller dans la toute dernière cellule du tableau et d'appuyer sur l'onglet. Donc tout fonctionne ici. Nous en sommes à 70, c'est génial, puis A104 et je vais mettre un 100 ici. D'accord, alors que 70 devrait changer en 170 et c'est le cas, mais ce 70 n'aurait pas dû changer du tout. Bon 68 + 2 n'est pas un 170. Je le referai. Un 104 et mettre une autre centaine dans le dernier est juste. Ces deux n'ont pas raison. Très bien, nous avons donc une situation étrange que si vousSi vous utilisez cette formule et que vous convertissez en table, vous commencez à ajouter des lignes, le total courant ne fonctionnera pas. À quel point est-ce mauvais?
D'accord, alors Zack propose deux solutions de contournement et les deux nécessitent un peu de connaissances sur le fonctionnement des références de structure. Nous allons juste avoir une nouvelle colonne ici et si je voulais faire quantité, quantité égale, c'est vrai, de sorte que = (@ Qty) indique la quantité dans cette ligne. Oh cool, eh bien, il y a un autre type de référence où nous utilisons la quantité sans le @. Regarde ça. Donc = SUM (INDEX ((Qty), 1: (@ Qty)) signifie toutes les quantités et nous allons dire que nous voulons SUM à partir de la première quantité, donc (INDEX ((Qty), 1 dit le première valeur ici, jusqu'à la quantité de lignes actuelle, et cela utilise une version vraiment spéciale de l'index, lorsque l'index est suivi de deux points, il se transforme en référence de cellule. D'accord, cette solution de contournement viole malheureusement la règle de Charles Williams de, nous 'Vous allez devoir regarder chaque référence, et donc quand vous aurez 10 000 lignes, cela ira vraiment très lentement.
Zach a une autre solution de contournement qui ne viole pas le problème de Charles Williams, mais utilise le redoutable OFFSET. OFFSET est une fonction volatile, donc chaque fois que vous calculez quelque chose, OFFSET va recalculer et tout ce qui se trouve en aval de l'OFFSET va recalculer. C'est juste un excellent moyen de complètement, complètement bousiller vos formules, et ce que cela fait, dit-il, nous prenons le total de cette ligne, en remontant d'une ligne, sur zéro colonne et donc ce que cela fait, c'est dire: prenez le total de la ligne précédente, puis nous y ajoutons la quantité de cette ligne. Bon, donc, maintenant, tout est à la recherche de deux références à chaque fois, mais malheureusement, l'OFFSET introduit des fonctions volatiles.
Eh bien, là vous l'avez, plus que vous n'avez jamais voulu en savoir sur les totaux en cours. Je suppose que mon opinion finale ici est d'utiliser cette méthode, car elle n'en a que deux. Même formule tout en bas et vos références de table structurées fonctionneront.
Pour cette exploration et 39 autres très bons conseils, consultez ce livre XL, les 40 meilleurs conseils Excel de tous les temps.
Récapitulatif de cet épisode, nous avons parlé de trois façons de faire des totaux cumulés. La première méthode a une formule différente, ligne 2, de toutes les autres lignes. C'est égal à gauche dans la ligne 2, puis égal à gauche plus haut dans les lignes 3 à N, mais si vous essayez d'utiliser simplement la même formule, égal à gauche plus haut, tout en bas, comment vous allez obtenir une erreur #Value . Donc = SUM (Up, Left), qui est le total précédent, plus cette feuille de route, qui fonctionne très bien, pas d'erreurs de valeur et ensuite la plage en expansion que j'utilise pour aimer. Ils sont cool, mais jusqu'à ce que je lis le livre blanc de Charles Williams sur la forme Excel de la vitesse. Puis j'ai commencé à détester ces références en expansion. Il a également un problème lorsque vous utilisez CTRL T et ajoutez de nouvelles lignes. Excel ne peut pas comprendre comment développer cette formule, comment ajouter de nouvelles lignes. J'adore cette astuce, allez à la toute dernière cellule du tableau et appuyez sur Tab,cela ajoutera une nouvelle ligne, puis nous avons parlé d'un référencement structuré, où nous utilisons la quantité dans cette ligne, puis toutes les quantités. = SOMME (OFFSET ((@ Total), - 1,00, (@ Qté)).
D'accord, je veux remercier Zach pour cette astuce. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2004.xlsx
