Voiko Excel tulostaa jokaisen sivun alatunnisteesta juoksevan summan? Se ei ole sisäänrakennettu, mutta lyhyt makro ratkaisee ongelman.
Katso video
- Tavoite: Tulosta luokan loppusumma ja prosenttiosuus luokasta jokaisen tulostetun sivun lopussa
- Ongelma: mikään Excel-käyttöliittymässä ei voi antaa kaavan tietää, että olet tulostetun sivun alaosassa
- Kyllä, voit "nähdä" sivunvaihdot, mutta kaavat eivät näe niitä
- Mahdollinen ratkaisu: Käytä makroa
- Strategia: Lisää kullekin riville juokseva summa ja luokan prosenttiosuus. Piilota kaikilla riveillä.
- Luokkakaavan loppusumma:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % luokan kaavasta:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Jos työkirjasi on tallennettu nimellä XLSX, tallenna XLSM-muodossa valitsemalla Tallenna nimellä
- Jos et ole koskaan käyttänyt makroja, muuta makroturvaa
- Jos et ole koskaan käyttänyt makroja, näytä Kehittäjä-välilehti
- Vaihda VBA: han
- Aseta moduuli
- Kirjoita koodi
- Määritä kyseinen makro muotoon
- Kun sivun koko muuttuu, suorita nollausmakro
Videon transkriptio
Opi Excel Podcastista, jakso 2058: Kokonaiskäyttö kunkin sivun lopussa
Hei, tervetuloa takaisin netcastiin, olen Bill Jelen. Tämän päivän kysymys, jonka Wiley lähetti: Wiley haluaa näyttää juoksevan tulon ja luokan prosenttiosuuden jokaisen painetun sivun viimeisellä rivillä. Joten Wiley on tulostanut täällä raportteja tonnia ja tonnia tietueita, useita sivuja kullekin luokalle tuolla sarakkeessa A. Ja kun olemme tulossa tulostussivun loppuun, Wiley etsii täältä yhteensä, joka näyttää kokonaistulot juokseva kokonaismäärä tässä luokassa ja sitten prosenttiosuus luokassa. Ja niin näet, että olemme siellä 9,7%, kun menen sivuille 2 - 21,1, sivuille 3 - 33,3 ja niin edelleen. Ja sivunvaihdossa, jossa saamme aikaan luokan A, luokan loppusumman ja loppusumman 100%. Selvä, ja kun Wiley kysyi minulta tästä, olin kuin: "Voi ei, emme - siellä"ei millään tavalla alatunnisteessa laittaa juoksevaa kokonaismäärää. " Selvä, joten tämä on tosin kamala halpa huijaus ja kannustan kaikkia, jotka katselevat tätä YouTubessa, jos sinulla on parempi tapa, mainitse kaikin keinoin, mainitse se kommenteissa, okei? Ja niin, ideani on juuri siellä sarakkeissa G ja H, piilottaa jokaisen rivin juokseva summa ja luokan prosenttiosuus. Selvä, ja sitten käytämme makroa havaitaksemme, olemmeko sivun lopussa.re sivun lopussa.re sivun lopussa.
Selvä, joten haluamamme kaksi kaavaa sanovat: hei, jos tämä luokka on yhtä suuri kuin edellinen luokka. Joten jos A6 = A5, ota sitten tämän tulon summa, joten se on luvussa F6 ja edellinen juokseva summa siellä G5: ssä. Koska käytän SUM-funktiota täällä, tämä ei tee virheitä, jos yritämme koskaan lisätä juoksusumman. Muuten tulemme vain olemaan aivan uudessa luokassa, joten kun vaihdamme A: sta B: hen, otamme vain summan SUM meistä vasemmalle, jonka olisin voinut vain laittaa F6: n. Mutta täällä olemme tiedämme liian myöhään. Ja sitten prosenttiosuus luokasta, tämä tulee olemaan kauhean tehoton. Otamme tämän rivin tulot jaettuna kaikkien tulojen summalla, jos luokka on yhtä suuri kuin A6. Joten nämä ovat kaikki luokat,tämä on tämän rivin luokka ja lisää sitten vastaava solu kaikista riveistä. Tietysti $ -merkit - 1, 2, 3, 4 $ -merkit siellä. Ei $ -merkkejä A6: ssa ja 4 $ -merkkejä siellä. Selvä, ja näytämme tämän luvun numeroina, ehkä 1000 erottimena, napsauta OK ja sitten täällä prosentteina yhden desimaalin tarkkuudella. Selvä, ja kopioimme tämän kaavan alas kaikkiin soluihin. Tällainen BAM, okei. Mutta nyt tavoitteena on varmistaa, että näemme nämä summat vain päästäessäsi sivunvaihtoon. Selvä, se on tuolla. Se on automaattinen sivunvaihto ja sitten myöhemmin, kun vaihdamme A: n päästä B: hen, manuaalinen sivunvaihto. Joten tämä manuaalinen sivunvaihto on erilainen kuin automaattinen sivunvaihto.ja näytämme tämän luvun numeroina, ehkä 1000 erottimena, napsauta OK ja sitten täällä prosentteina yhdellä desimaalilla. Selvä, ja kopioimme tämän kaavan alas kaikkiin soluihin. Tällainen BAM, okei. Mutta nyt tavoitteena on varmistaa, että näemme nämä summat vain päästäessäsi sivunvaihtoon. Selvä, se on tuolla. Se on automaattinen sivunvaihto ja sitten myöhemmin, kun vaihdamme A: n päästä B: hen, manuaalinen sivunvaihto. Joten tämä manuaalinen sivunvaihto on erilainen kuin automaattinen sivunvaihto.ja näytämme tämän luvun numeroina, ehkä 1000 erottimena, napsauta OK ja sitten täällä prosentteina yhdellä desimaalilla. Selvä, ja kopioimme tämän kaavan alas kaikkiin soluihin. Tällainen BAM, okei. Mutta nyt tavoitteena on varmistaa, että näemme nämä summat vain päästäessäsi sivunvaihtoon. Selvä, se on tuolla. Se on automaattinen sivunvaihto ja sitten myöhemmin, kun vaihdamme A: n päästä B: hen, manuaalinen sivunvaihto. Joten tämä manuaalinen sivunvaihto on erilainen kuin automaattinen sivunvaihto.Mutta nyt tavoitteena on varmistaa, että näemme nämä summat vain päästäessäsi sivunvaihtoon. Selvä, se on tuolla. Se on automaattinen sivunvaihto ja sitten myöhemmin, kun vaihdamme A: n päästä B: hen, manuaalinen sivunvaihto. Joten tämä manuaalinen sivunvaihto on erilainen kuin automaattinen sivunvaihto.Mutta nyt tavoitteena on varmistaa, että näemme nämä summat vain päästäessäsi sivunvaihtoon. Selvä, se on tuolla. Se on automaattinen sivunvaihto ja sitten myöhemmin, kun vaihdamme A: n päästä B: hen, manuaalinen sivunvaihto. Joten tämä manuaalinen sivunvaihto on erilainen kuin automaattinen sivunvaihto.
Selvä, nyt huomaat täällä, että tämä tiedosto on tallennettu XLSX-tiedostona, koska näin Excel haluaa tallentaa tiedostoja. XLSX on rikki tiedostotyyppi, joka ei salli makroja, eikö? Pahin tiedostotyyppi maailmassa. Joten, älä ohita tätä vaihetta tai tätä. Kaikki työsi täältä ja ulos menetetään. Tallenna nimellä, emmekä aio tallentaa Excel-työkirjana, vaan makrokäyttöisenä työkirjana tai binaarisena työkirjana tai XLS-tiedostona. Aion mennä makrokäyttöisen työkirjan kanssa. Jos et tee sitä vaihetta, menetät loput tekemäsi työstä. Selvä, ja jos et ole koskaan aiemmin suorittanut makroja, napsautamme hiiren kakkospainikkeella ja sanomme Mukauta valintanauha. Valitse täältä oikeassa reunassa Kehittäjä-ruutu, joka antaa sinulle Kehittäjä-välilehden. Kun sinulla on Kehittäjä-välilehti, voimme siirtyä Macro Securityen,oletuksena se tulee olemaan täällä Poista käytöstä kaikki makrot, äläkä kerro minulle, että olet poistanut käytöstä kaikki makrot. Haluat siirtyä toiseen, tällä tavalla, kun avaamme tiedoston, sanomme: "Hei, täällä on makroja. Loitko nämä? Oletko kunnossa tässä? " Ja voit sanoa, Ota makrot käyttöön. Okei, napsauta OK.
Now, we're going to switch over to the visual basic editor. If you've never used a visual basic before, you’re going to start with this completely gray screen, go to View and the Project Explorer. Here's a list of all the open workbooks. So I have the Solver Add-in, my personal macro workbook and here's the workbook that I'm working on. Make sure that this workbook is selected, do Insert, Module. Insert, Module will get a nice big blank, white canvas here. Alright, and then you're going to type in this code. Alright now, we're using an object here called HPageBreak, a horizontal page break. And because I don't use this a lot, I had to declare it up here as a variable, as an object HPB, that way I would be able to see the choices that are available to me in each one. Alright, figure out where the last row with data is today so I'm using Column A, I'm going to the end of Column A - A1048576. This is an L here and not a 1, this is an L. Everyone screws that up. L as in Excel. It sounds like Excel. Get it? Excel up. So, go to A1048576, press the End key, and the Up Arrow key to get to the last row. Figure out what row that is. And then in Columns G and H, and if you're watching this, you need to take a look at your Excel data and figure out where your two new columns are, alright. I don't know how many columns you have. Maybe your new columns are over in I and J, or maybe they're in C and D. I don't know, figure out where those are and we're going to hide all of those rows, alright. So in my case, it was starting from G6, that's the first place where we have a number, :H and then I'm concatenating the last row that we have today using a number format of three semicolons that will hide the data.
Alright, then this next one, I learned this next one from the message board. If you don't put the active window in Page Break Preview mode before you run this code, this code will not work. It works for some of the page breaks but not all of the page breaks, so you have to temporarily display the page breaks. And then a loop here: For Each, this is my object variable- HPB In ActiveSheet.HPageBreaks. Figure out the last row, alright? So for this object, for the page break, figure out the location, figure out the row. And this is actually the first row of the next page so I have to subtract 1 from that, alright. And then here, I admit this is incredibly cheap, go out to Column 7 which is Column G, change the NumberFormat to be currency, just of that row. And then go out to Column 8 which is H and change it to a percentage and go next. Finally, exit horizontal or a page break preview and go back into normal view.
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.
Voi hei, haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2058.xlsm
