Excel peut-il imprimer un total cumulé dans le pied de page de chaque page? Ce n'est pas intégré, mais une courte macro résoudra le problème.
Regarder la vidéo
- Objectif: Imprimer le total cumulé de la catégorie et le% de la catégorie au bas de chaque page imprimée
- Problème: rien dans l'interface utilisateur d'Excel ne peut faire savoir à une formule que vous êtes au bas d'une page imprimée
- Oui, vous pouvez "voir" les sauts de page, mais les formules ne peuvent pas les voir
- Solution possible: utilisez une macro
- Stratégie: ajoutez le total cumulé et le% de la catégorie pour chaque ligne. Cacher sur toutes les lignes.
- Total cumulé pour la formule de catégorie:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % de la formule de catégorie:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Si votre classeur est enregistré au format XLSX, effectuez un Enregistrer sous pour enregistrer au format XLSM
- Si vous n'avez jamais utilisé de macros, modifiez la sécurité des macros
- Si vous n'avez jamais utilisé de macros, affichez l'onglet Développeur
- Passer à VBA
- Insérer un module
- Entrez le code
- Attribuer cette macro à une forme
- À mesure que la taille de la page change, exécutez la macro de réinitialisation
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2058: Total cumulé à la fin de chaque page
Salut, bon retour sur le netcast, je suis Bill Jelen. La question d'aujourd'hui envoyée par Wiley: Wiley veut afficher un total cumulé de revenus et un pourcentage de catégorie dans la dernière ligne de chaque page imprimée. Donc, Wiley a imprimé des rapports ici avec des tonnes et des tonnes d'enregistrements, plusieurs pages pour chaque catégorie là-bas dans la colonne A. Et quand nous arrivons à la fin de la page d'impression, Wiley cherche ici un total qui montre le revenu total, total cumulé dans cette catégorie, puis pourcentage de la catégorie. Et donc, vous pouvez voir que nous sommes à 9,7% là-bas, quand je vais à la page 2 - 21.1, page 3 - 33.3 et ainsi de suite. Et au saut de page où nous en avons terminé avec la catégorie A, le total général pour la catégorie et le total général 100%. D'accord, et quand Wiley m'a posé des questions à ce sujet, je me suis dit: «Oh non, nous ne sommes pas là»Il n'y a aucun moyen dans le pied de page de mettre un total cumulé. » D'accord, c'est donc une triche horrible et bon marché et j'encourage tous ceux qui regardent ça sur YouTube, si vous avez un meilleur moyen, s'il vous plaît, mentionnez-le dans les commentaires, d'accord? Et donc, mon idée est juste là-bas dans les colonnes G et H, pour masquer le total cumulé et le pourcentage de catégorie dans chaque ligne. D'accord, puis nous utilisons une macro pour détecter si nous sommes à la fin de la page.re à la fin de la page.re à la fin de la page.
D'accord, donc les deux formules que nous voulons ici disent, hé, si cette catégorie est égale à la catégorie précédente. Donc, si A6 = A5, prenez la SOMME de ces revenus, donc c'est dans F6 et le total cumulé précédent là-haut dans G5. Maintenant, parce que j'utilise la fonction SOMME ici, cela ne fait pas d'erreur si nous essayons un jour d'ajouter le total cumulé. Sinon, nous allons simplement être dans une toute nouvelle catégorie, donc lorsque nous passerons de A à B, nous prendrons simplement la SOMME de la valeur à gauche de nous, que j'aurais pu simplement mettre F6. Mais nous voilà, vous savez, trop tard. Et puis le pourcentage de la catégorie, celui-ci va être horriblement inefficace. Nous prenons les revenus de cette ligne divisés par la somme de tous les revenus où la catégorie est égale à A6. Donc ce sont toutes les catégories,il s'agit de la catégorie de cette ligne, puis additionnez la cellule correspondante de toutes les lignes. Bien sûr, $ signes - 1, 2, 3, 4 signes $ là-bas. Aucun signe $ dans A6 et 4 $ signes là-dedans. Très bien, et nous afficherons ce nombre sous forme de nombre, peut-être 1000 séparateur, cliquez sur OK, puis ici sous forme de pourcentage avec une décimale comme ça. Très bien, et nous allons copier cette formule dans toutes les cellules. BAM, comme ça, d'accord. Mais maintenant, l'objectif ici est de nous assurer que nous ne voyons ces totaux que lorsque nous arrivons au saut de page. D'accord, c'est juste là. C'est un saut de page automatique et plus tard, lorsque nous passons de la fin de A à B, un saut de page manuel. Donc, ce saut de page manuel est ici différent d'un saut de page automatique.et nous montrerons ce nombre sous forme de nombre, peut-être 1000 séparateur, cliquez sur OK, puis ici sous forme de pourcentage avec une décimale comme ça. Très bien, et nous allons copier cette formule dans toutes les cellules. BAM, comme ça, d'accord. Mais maintenant, le but ici est de nous assurer que nous ne voyons ces totaux que lorsque nous arrivons au saut de page. D'accord, c'est juste là. C'est un saut de page automatique, puis plus tard, lorsque nous passons de la fin de A à B, un saut de page manuel. Donc, ce saut de page manuel est ici différent d'un saut de page automatique.et nous montrerons ce nombre sous forme de nombre, peut-être 1000 séparateur, cliquez sur OK, puis ici sous forme de pourcentage avec une décimale comme ça. Très bien, et nous allons copier cette formule dans toutes les cellules. BAM, comme ça, d'accord. Mais maintenant, l'objectif ici est de nous assurer que nous ne voyons ces totaux que lorsque nous arrivons au saut de page. D'accord, c'est juste là. C'est un saut de page automatique, puis plus tard, lorsque nous passons de la fin de A à B, un saut de page manuel. Donc, ce saut de page manuel est ici différent d'un saut de page automatique.Mais maintenant, l'objectif ici est de nous assurer que nous ne voyons ces totaux que lorsque nous arrivons au saut de page. D'accord, c'est juste là. C'est un saut de page automatique et plus tard, lorsque nous passons de la fin de A à B, un saut de page manuel. Donc, ce saut de page manuel est ici différent d'un saut de page automatique.Mais maintenant, le but ici est de nous assurer que nous ne voyons ces totaux que lorsque nous arrivons au saut de page. D'accord, c'est juste là. C'est un saut de page automatique, puis plus tard, lorsque nous passons de la fin de A à B, un saut de page manuel. Donc, ce saut de page manuel est ici différent d'un saut de page automatique.
Très bien, vous remarquerez maintenant que ce fichier est enregistré en tant que fichier XLSX car c'est ainsi qu'Excel souhaite enregistrer les fichiers. XLSX est le type de fichier cassé qui n'autorise pas les macros, non? Le pire type de fichier au monde. Alors, ne sautez pas cette étape ou ceci. Tout votre travail d'ici et d'ailleurs sera perdu. Enregistrer sous, et nous allons enregistrer non pas en tant que classeur Excel mais en tant que classeur prenant en charge les macros ou en tant que classeur binaire ou en tant que XLS. Je vais utiliser le classeur prenant en charge les macros. Si vous ne faites pas cette étape, vous êtes sur le point de perdre le reste du travail que vous faites. D'accord, et si vous n'avez jamais exécuté de macros auparavant, nous allons faire un clic droit et dire Personnaliser le ruban. Ici, dans la partie droite, choisissez la case Développeur, qui vous donnera un onglet Développeur. Une fois que vous avez l'onglet Développeur, nous pouvons accéder à Macro Security,par défaut, ce sera ici. Désactivez toutes les macros et ne me dites pas que vous avez désactivé toutes les macros. Vous voulez passer au second, de cette façon lorsque nous ouvrons le fichier, nous dirons: «Hé, il y a des macros ici. Les avez-vous créés? Êtes-vous d'accord avec ça? » Et vous pouvez dire, activez les macros. Très bien, cliquez sur OK.
Maintenant, nous allons passer à l'éditeur Visual Basic. Si vous n'avez jamais utilisé de base visuelle auparavant, vous allez commencer avec cet écran complètement gris, allez dans Affichage et l'Explorateur de projets. Voici une liste de tous les classeurs ouverts. J'ai donc le complément Solver, mon classeur de macros personnelles et voici le classeur sur lequel je travaille. Assurez-vous que ce classeur est sélectionné, effectuez Insertion, Module. Insérer, Module obtiendra une belle grande toile blanche vierge ici. Très bien, puis vous allez taper ce code. Très bien maintenant, nous utilisons ici un objet appelé HPageBreak, un saut de page horizontal. Et comme je ne l'utilise pas beaucoup, j'ai dû le déclarer ici en tant que variable, en tant qu'objet HPB, de cette façon je pourrais voir les choix qui s'offrent à moi dans chacun d'eux. Bien,déterminer où se trouve la dernière ligne avec des données aujourd'hui, donc j'utilise la colonne A, je vais à la fin de la colonne A - A1048576. C'est un L ici et non un 1, c'est un L. Tout le monde fout ça en l'air. L comme dans Excel. Cela ressemble à Excel. Trouver? Exceller. Alors, allez à A1048576, appuyez sur la touche Fin et la touche Flèche haut pour accéder à la dernière ligne. Déterminez de quelle ligne il s'agit. Et puis dans les colonnes G et H, et si vous regardez cela, vous devez jeter un œil à vos données Excel et déterminer où se trouvent vos deux nouvelles colonnes, d'accord. Je ne sais pas combien de colonnes vous avez. Peut-être que vos nouvelles colonnes sont terminées en I et J, ou peut-être qu'elles sont en C et D. Je ne sais pas, trouvez où elles se trouvent et nous allons cacher toutes ces lignes, d'accord. Donc dans mon cas, c'était à partir du G6, c'est le premier endroit où nous avons un numéro,:H et puis je concatène la dernière ligne que nous avons aujourd'hui en utilisant un format numérique de trois points-virgules qui cachera les données.
Très bien, alors celui-ci, j'ai appris celui-ci sur le forum. Si vous ne placez pas la fenêtre active en mode Aperçu des sauts de page avant d'exécuter ce code, ce code ne fonctionnera pas. Cela fonctionne pour certains des sauts de page mais pas tous les sauts de page, vous devez donc afficher temporairement les sauts de page. Et puis une boucle ici: For Each, c'est ma variable objet - HPB In ActiveSheet.HPageBreaks. Trouvez la dernière rangée, d'accord? Donc, pour cet objet, pour le saut de page, déterminez l'emplacement, déterminez la ligne. Et c'est en fait la première ligne de la page suivante, donc je dois soustraire 1 de cela, d'accord. Et puis ici, j'admets que c'est incroyablement bon marché, allez à la colonne 7 qui est la colonne G, changez le NumberFormat en devise, juste de cette ligne. Et puis allez à la colonne 8 qui est H et changez-la en pourcentage et passez ensuite.Enfin, quittez l'aperçu horizontal ou d'un saut de page et revenez à l'affichage normal.
Alright, so that's our code. I will File, Close and Return to Microsoft Excel. I want an easy way to run this, so I'm going to Insert, choose a nice shape here. I will choose a rounded rectangle, draw my right- around a rectangle in, Page layout, go to Effects, choose the effects for Office 2007. And then here on the Format tab we have a nice way to add some glow to that, alright.
So we've created a button, I'm just going to say Reset Page Breaks Totals, we’re going to center that, so on the Home tab, Center Vertically, Center Horizontally, increase the height and then right-click, Assign Macro and say that we're adding it to FindAllPageBreaksTwo, click OK. Alright, and then you see we have all of our totals here and I reset page break totals, and now it's - The totals are still there, the formula is still there but it's hidden them except for on the rows that is the last page break.
Alright now, I just want you to notice here that we're in A46 and A93. Page breaks are funny things. If you change the margins around a little bit, if you change the header and footer, then the page break is going to move to a new spot. So, we'll go to Wide margins, and the page break moved in those numbers in the wrong spot. Also print titles on what- rows 124 to appear at the top of each page which means that we're going to have less rows, and so then again, now my totals are in completely the wrong place that's why I need that button back here to reset page break totals and you will see that now, this cell in Row 45 and this cell in Row 86, that's in a new place.
Alright, so today's question from Wiley. We want to print category running total and % of Category at the bottom of each printed page. There's nothing in the Excel user interface, they can let a formula know that you're at the bottom of the printed page. Yeah, you can see the page breaks but the formulas can’t see them. So, one possible solution, and I'm welcoming others in the YouTube comments, use a macro. So add the running total and % of category for each row, hide all those rows. Here's the two formulas that we used: Saved As to save the workbook as XLSM or your macros will not be allowed to run next time. They'll actually- You'll lose your macros. If you've never used macros: change the macro security, show the Developer tab, switch to VBA, insert a module, type the code and then assign that macro to a shape. As the page size changes, reset the macro. And you will have a cheap solution to what Wiley is trying to do.
Oh hé, 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: Podcast2058.xlsm
