Excel può stampare un totale parziale nel piè di pagina per ogni pagina? Non è integrato, ma una breve macro risolverà il problema.
Guarda un video
- Obiettivo: stampa il totale corrente della categoria e la% della categoria nella parte inferiore di ogni pagina stampata
- Problema: nulla nell'interfaccia utente di Excel può far sapere a una formula che sei in fondo a una pagina stampata
- Sì, puoi "vedere" le interruzioni di pagina, ma le formule non possono vederle
- Possibile soluzione: utilizzare una macro
- Strategia: aggiungi il totale parziale e la% della categoria per ogni riga. Nascondi su tutte le righe.
- Totale parziale per formula di categoria:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % della formula di categoria:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Se la tua cartella di lavoro viene salvata come XLSX, esegui Salva con nome per salvare come XLSM
- Se non hai mai utilizzato le macro, modifica la sicurezza delle macro
- Se non hai mai utilizzato le macro, mostra la scheda Sviluppatore
- Passa a VBA
- Inserisci un modulo
- Digita il codice
- Assegna quella macro a una forma
- Man mano che le dimensioni della pagina cambiano, eseguire la macro di ripristino
Trascrizione del video
Impara Excel da Podcast, episodio 2058: totale parziale alla fine di ogni pagina
Ehi, bentornato al netcast, sono Bill Jelen. Domanda di oggi inviata da Wiley: Wiley vuole mostrare un totale parziale delle entrate e la percentuale di categoria nell'ultima riga di ogni pagina stampata. Quindi, Wiley ha stampato rapporti qui con tonnellate e tonnellate di record, più pagine per ogni categoria laggiù nella colonna A. E quando arriviamo alla fine della pagina di stampa, Wiley sta cercando un totale qui che mostra le entrate totali, totale corrente all'interno di questa categoria e quindi percentuale della categoria. E così, puoi vedere che siamo al 9,7% lì, quando vado a pagina 2 - 21.1, pagina 3 - 33.3 e così via. E all'interruzione di pagina in cui finiamo con la categoria A, il totale generale per la categoria e il totale generale 100%. Va bene, e quando Wiley mi ha chiesto di questo, ho pensato, "Oh no, noi non - lì"Non è possibile inserire un totale parziale nel piè di pagina. " Va bene, quindi questo è certamente un orribile imbroglio economico e incoraggio chiunque lo guardi su YouTube, se hai un modo migliore, per favore, menzionalo nei commenti, va bene? E così, la mia idea è solo là fuori nelle colonne G e H, per nascondere il totale parziale e la percentuale di categoria in ogni singola riga. Va bene, quindi utilizziamo una macro per rilevare se siamo alla fine della pagina.re alla fine della pagina.re alla fine della pagina.
Va bene, quindi le due formule che vogliamo qui dicono, ehi, se questa categoria è uguale alla categoria precedente. Quindi, se A6 = A5, prendi la SOMMA di queste entrate, quindi è in F6 e il totale corrente precedente è in G5. Ora, poiché qui sto usando la funzione SUM, questo non si verifica se dovessimo mai provare ad aggiungere il totale parziale. Altrimenti, saremo solo in una categoria nuova di zecca, quindi quando passeremo da A a B, prenderemo semplicemente la SOMMA del valore a sinistra di noi, che avrei potuto mettere F6 lì. Ma eccoci qui, sai, troppo tardi. E poi la percentuale della categoria, questa sarà orribilmente inefficiente. Prendiamo le entrate su questa riga divise per la SOMMA di tutte le entrate in cui la categoria è uguale ad A6. Quindi queste sono tutte le categorie,questa è la categoria in questa riga e quindi somma la cella corrispondente da tutte le righe. Ovviamente, i segni $ - 1, 2, 3, 4 $ lì. Nessun segno $ in A6 e 4 segni $ lì. Va bene, e mostreremo questo numero come numero, forse un separatore 1000, fare clic su OK, e quindi qui come percentuale con una cifra decimale come quella. Va bene, e copieremo questa formula su tutte le celle. BAM, così, va bene. Ma ora l'obiettivo qui è assicurarci di vedere quei totali solo quando arriviamo all'interruzione di pagina. Va bene, è proprio lì. Questa è un'interruzione di pagina automatica e poi, più tardi, quando si passa dalla fine di A a B, un'interruzione di pagina manuale. Quindi, questa interruzione di pagina manuale qui è diversa da un'interruzione di pagina automatica.e mostreremo questo numero come un numero, forse un separatore 1000, fare clic su OK, e quindi qui come percentuale con una cifra decimale come quella. Va bene, e copieremo questa formula su tutte le celle. BAM, così, va bene. Ma ora l'obiettivo qui è assicurarci di vedere quei totali solo quando arriviamo all'interruzione di pagina. Va bene, è proprio lì. Questa è un'interruzione di pagina automatica e poi, più tardi, quando si passa dalla fine di A a B, un'interruzione di pagina manuale. Quindi, questa interruzione di pagina manuale qui è diversa da un'interruzione di pagina automatica.e mostreremo questo numero come un numero, forse un separatore 1000, fare clic su OK, e quindi qui come percentuale con una cifra decimale come quella. Va bene, e copieremo questa formula su tutte le celle. BAM, così, va bene. Ma ora l'obiettivo qui è assicurarci di vedere quei totali solo quando arriviamo all'interruzione di pagina. Va bene, è proprio lì. Questa è un'interruzione di pagina automatica e poi, più tardi, quando si passa dalla fine di A a B, un'interruzione di pagina manuale. Quindi, questa interruzione di pagina manuale qui è diversa da un'interruzione di pagina automatica.Ma ora l'obiettivo qui è assicurarci di vedere quei totali solo quando arriviamo all'interruzione di pagina. Va bene, è proprio lì. Questa è un'interruzione di pagina automatica e poi, più tardi, quando si passa dalla fine di A a B, un'interruzione di pagina manuale. Quindi, questa interruzione di pagina manuale qui è diversa da un'interruzione di pagina automatica.Ma ora l'obiettivo qui è assicurarci di vedere quei totali solo quando arriviamo all'interruzione di pagina. Va bene, è proprio lì. Questa è un'interruzione di pagina automatica e poi, più tardi, quando si passa dalla fine di A a B, un'interruzione di pagina manuale. Quindi, questa interruzione di pagina manuale qui è diversa da un'interruzione di pagina automatica.
Va bene, ora noterai qui che questo file viene salvato come file XLSX perché è così che Excel vuole salvare i file. XLSX è il tipo di file danneggiato che non consente le macro, giusto? Il peggior tipo di file al mondo. Quindi, non saltare questo passaggio o questo. Tutto il tuo lavoro da qui in avanti andrà perso. Salva con nome e salveremo non come una cartella di lavoro di Excel ma come una cartella di lavoro abilitata per macro o come cartella di lavoro binaria o come XLS. Vado con la cartella di lavoro con attivazione macro. Se non fai questo passaggio, stai per perdere il resto del lavoro che svolgi. Va bene, e poi, se non hai mai eseguito macro prima, faremo clic con il pulsante destro del mouse e diremo Personalizza la barra multifunzione. Qui sul lato destro, scegli la casella per Sviluppatore, che ti porterà una scheda Sviluppatore. Una volta che hai la scheda Sviluppatore, possiamo andare a Macro Security,per impostazione predefinita sarà qui Disabilita tutte le macro e non dirmi che hai disabilitato tutte le macro. Vuoi passare al secondo, in questo modo quando apriamo il file diremo: "Ehi, ci sono delle macro qui. Li hai creati tu? Stai bene con questo? " E puoi dire: Abilita le macro. Va bene, fai clic su OK.
Ora passeremo all'editor visual basic. Se non hai mai utilizzato un visual basic prima, inizierai con questa schermata completamente grigia, vai su Visualizza e Esplora progetti. Ecco un elenco di tutte le cartelle di lavoro aperte. Quindi ho il componente aggiuntivo Risolutore, la mia cartella di lavoro macro personale ed ecco la cartella di lavoro su cui sto lavorando. Assicurati che questa cartella di lavoro sia selezionata, fai Inserisci, Modulo. Inserisci, il modulo avrà qui una bella tela bianca e vuota. Va bene, e poi digiterai questo codice. Va bene ora, stiamo usando un oggetto qui chiamato HPageBreak, un'interruzione di pagina orizzontale. E poiché non lo uso molto, ho dovuto dichiararlo qui come una variabile, come un oggetto HPB, in questo modo avrei potuto vedere le scelte che ho a disposizione in ognuno di essi. Tutto a posto,capire dove si trova l'ultima riga con i dati oggi, quindi sto usando la colonna A, vado alla fine della colonna A - A1048576. Questa è una L qui e non un 1, questa è una L. Tutti rovinano tutto. L come in Excel. Sembra Excel. Prendilo? Migliora. Quindi, vai a A1048576, premi il tasto Fine e il tasto Freccia su per arrivare all'ultima riga. Scopri di che riga si tratta. E poi nelle colonne G e H, e se stai guardando questo, devi dare un'occhiata ai tuoi dati di Excel e capire dove sono le tue due nuove colonne, va bene. Non so quante colonne hai. Forse le tue nuove colonne sono finite in I e J, o forse sono in C e D. Non so, cerca di capire dove sono e nasconderemo tutte quelle righe, va bene. Quindi nel mio caso, è stato a partire da G6, che è il primo posto in cui abbiamo un numero,:H e poi sto concatenando l'ultima riga che abbiamo oggi usando un formato numerico di tre punti e virgola che nasconderà i dati.
Bene, allora questo prossimo, ho imparato questo prossimo dalla bacheca. Se non metti la finestra attiva in modalità Anteprima interruzioni di pagina prima di eseguire questo codice, questo codice non funzionerà. Funziona per alcune interruzioni di pagina ma non per tutte, quindi è necessario visualizzare temporaneamente le interruzioni di pagina. E poi un ciclo qui: For Each, questa è la mia variabile oggetto: HPB In ActiveSheet.HPageBreaks. Trova l'ultima riga, va bene? Quindi per questo oggetto, per l'interruzione di pagina, calcola la posizione, calcola la riga. E questa è in realtà la prima riga della pagina successiva, quindi devo sottrarre 1 da quella, va bene. E poi qui, ammetto che questo è incredibilmente economico, vai alla colonna 7 che è la colonna G, cambia il NumberFormat in valuta, solo di quella riga. E poi vai alla colonna 8 che è H e cambiala in una percentuale e vai avanti.Infine, esci dall'anteprima orizzontale o da un'interruzione di pagina e torna alla visualizzazione normale.
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 ehi, voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2058.xlsm
