Kas Excel saab printida iga lehe jalusesse jooksva summa? See pole sisseehitatud, kuid probleemi lahendab lühike makro.
Vaata videot
- Eesmärk: prinditava kategooria kogusumma ja kategooria% printimine iga prinditud lehe lõpus
- Probleem: miski Exceli kasutajaliideses ei saa valemile teada anda, et olete prinditud lehe lõpus
- Jah, saate lehe katkestusi "näha", kuid valemid neid ei näe
- Võimalik lahendus: kasutage makrot
- Strateegia: lisage iga rea jooksev kogusumma ja kategooria%. Peida kõigil ridadel.
- Kategooriavalemi kogusumma jooksmine:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % kategooria valemist:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Kui teie töövihik on salvestatud kui XLSX, tehke XLSM-na salvestamiseks käsk Salvesta nimega
- Kui te pole kunagi makrosid kasutanud, muutke makroturvalisust
- Kui te pole kunagi makrosid kasutanud, kuvage vahekaart Arendaja
- Lülitu VBA-le
- Sisestage moodul
- Sisestage kood
- Määrake makro kujundile
- Kui lehe suurus muutub, käivitage lähtestusmakro
Video ärakiri
Õppige Exceli programmi Podcast, osa 2058: Kokku jooksmine iga lehe lõpus
Tere, tere tulemast tagasi netisaate juurde, ma olen Bill Jelen. Tänane küsimus, mille saatis Wiley: Wiley soovib näidata iga trükitud lehe viimases reas jooksvat tulu ja kategooria protsenti. Niisiis, Wiley on siia printinud aruandeid tonnide ja tonnide kirjetega, mitu lehte iga kategooria jaoks seal veerus A. Ja kui jõuame prinditava lehe lõpuni, otsib Wiley siin kokku, mis näitab kogutulu, selle kategooria koguarv ja seejärel kategooria protsent. Ja nii näete, et seal on 9,7%, kui lähen lehekülgedele 2 - 21,1, lehele 3 - 33,3 ja nii edasi. Ja lehepausil, kus saame A-kategooriaga hakkama, kategooria kokkuvõte ja 100%. Olgu, ja kui Wiley minult selle kohta küsis, olin ma nagu: "Oh ei, me ei tee-seal"ei saa mingil juhul jalusesse panna jooksva summa. " Hästi, nii et see on tõepoolest jube odav pettus ja ma julgustan kõiki, kes seda YouTube'is vaatavad, kui teil on parem viis, siis palun kõigi vahenditega, mainige seda kommentaarides, eks? Ja minu idee on lihtsalt veergudes G ja H peita jooksev kogusumma ja kategooria protsent igas reas. Olgu, siis tuvastame makro abil, kas oleme lehe lõpus.re lehe lõpus.re lehe lõpus.
Olgu, nii ütlevad kaks valemit, mida me siin tahame öelda: hei, kui see kategooria on eelmise kategooriaga võrdne. Nii et kui A6 = A5, siis võtke selle tulu summa, nii et see oleks F6-s ja eelmine jooksev kogusumma seal G5-s. Kuna ma kasutan siin funktsiooni SUM, ei tee see viga, kui proovime kunagi jooksva summa lisada. Vastasel juhul oleme lihtsalt uhiuues kategoorias, nii et kui me läheme A-lt B-le üle, siis võtame lihtsalt meist vasakule jääva väärtuse SUM, mille oleksin võinud lihtsalt F6 sinna panna. Aga siin me oleme, teate, liiga hilja. Ja siis kategooria protsent, see on jube ebaefektiivne. Selle rea tulud jagatakse kõigi tulude summaga, kui kategooria on võrdne A6-ga. Nii et need on kõik kategooriad,see on selle rea kategooria ja liidake seejärel vastav laht kõigist ridadest. Muidugi, $ märgid - 1, 2, 3, 4 $ märke seal. A6-s ei ole $ märke ja seal 4 $ märke. Hästi, ja me näitame seda numbrit arvuna, võib-olla 1000 eraldajana, klõpsake nuppu OK ja seejärel siin ühe kümnendkoha protsendina. Hästi, ja kopeerime selle valemi kõikidesse lahtritesse. BAM, niimoodi, korras. Kuid nüüd on siin eesmärk veenduda, et näeme neid summasid ainult siis, kui jõuame lehepausile. Hästi, see on sealsamas. See on automaatne lehepaus ja siis hiljem, kui lülitame A-otsast B-le, käsitsi lehe katkemise. Nii et see manuaalne leheküljepaus on teistsugune kui automaatne leheküljepaus.ja me näitame seda arvu arvuna, võib-olla 1000 eraldajana, klõpsake nuppu OK ja seejärel siin protsendina ühe kümnendkoha täpsusega. Hästi, ja kopeerime selle valemi kõikidesse lahtritesse. BAM, niimoodi, korras. Kuid nüüd on siin eesmärk veenduda, et näeme neid summasid ainult siis, kui jõuame lehepausile. Hästi, see on sealsamas. See on automaatne lehelõik ja siis hiljem, kui lülitame A-otsast B-le, käsitsi leheküljepaus. Nii et see manuaalne leheküljepaus on teistsugune kui automaatne leheküljepaus.ja me näitame seda arvu arvuna, võib-olla 1000 eraldajana, klõpsake nuppu OK ja seejärel siin protsendina ühe kümnendkoha täpsusega. Hästi, ja kopeerime selle valemi kõikidesse lahtritesse. BAM, niimoodi, korras. Kuid nüüd on siin eesmärk veenduda, et näeme neid summasid ainult siis, kui jõuame lehepausile. Hästi, see on sealsamas. See on automaatne lehelõik ja siis hiljem, kui lülitame A-otsast B-le, käsitsi leheküljepaus. Nii et see manuaalne leheküljepaus on teistsugune kui automaatne leheküljepaus.Kuid nüüd on siin eesmärk veenduda, et näeme neid summasid ainult siis, kui jõuame lehepausile. Hästi, see on sealsamas. See on automaatne lehepaus ja siis hiljem, kui lülitame A-otsast B-le, käsitsi lehe katkemise. Nii et see manuaalne leheküljepaus on teistsugune kui automaatne leheküljepaus.Kuid nüüd on siin eesmärk veenduda, et näeme neid summasid ainult siis, kui jõuame lehepausile. Hästi, see on sealsamas. See on automaatne lehepaus ja siis hiljem, kui lülitame A-otsast B-le, käsitsi lehe katkemise. Nii et see manuaalne leheküljepaus on teistsugune kui automaatne leheküljepaus.
Hästi, nüüd märkate siin üleval, et see fail on salvestatud XLSX-failina, sest nii soovib Excel faile salvestada. XLSX on katkine failitüüp, mis ei võimalda makrod, eks? Halvim failitüüp maailmas. Niisiis, ärge jätke seda ega seda sammu vahele. Kõik teie tööd siit ja väljast lähevad kaduma. Salvesta nimega ja me ei salvesta Exceli töövihikuna, vaid makrotoega töövihikuna või binaarse töövihikuna või XLS-i. Lähen makrotoega töövihikuga. Kui te seda sammu ei tee, kaotate kogu ülejäänud töö, mille teete. Olgu, ja kui te pole kunagi varem makrosid käitanud, siis paremklõpsake ja ütleme Kohanda linti. Valige siin paremas servas ruut Arendaja jaoks, mis annab teile vahekaardi Arendaja. Kui teil on vahekaart Arendaja, võime minna Macro Security juurde,vaikimisi on see siin üleval. Keela kõik makrod ja ära ütle mulle, et oled kogu makrod keelanud. Tahate minna teisele, sel viisil ütleme faili avades: „Hei, siin on makrod. Kas lõite need? Kas teil on sellega kõik korras? " Ja võite öelda: lubage makrod. Hästi, klõpsake nuppu OK.
Nüüd läheme üle visuaalsele põhiredaktorile. Kui te pole kunagi varem visuaalset põhiteavet kasutanud, alustate selle täiesti halli ekraaniga, minge vaate ja projektiuurija juurde. Siin on kõigi avatud töövihikute loend. Nii et mul on Solveri lisandmoodul, minu isiklik makro töövihik ja siin on töövihik, millega ma töötan. Veenduge, et see töövihik on valitud, tehke Lisa, Moodul. Insert, moodul saab siit kena suure tühja valge lõuendi. Olgu, siis sisestate selle koodi. Hästi, me kasutame siin objekti nimega HPageBreak, horisontaalset leheküljevahet. Ja kuna ma seda eriti ei kasuta, pidin selle siin muutujana, objekti HPB-na deklareerima, nii näeksin igas valikus mulle kättesaadavaid valikuid. Hästi,aru saada, kus on viimane rida andmetega täna, nii et ma kasutan veergu A, ma lähen veeru A lõppu - A1048576. See on siin L ja mitte 1, see on L. Kõik kruvivad selle üles. L nagu Excelis. See kõlab nagu Excel. Saate aru? Exceli üles. Niisiis, minge aadressile A1048576, vajutage viimasele reale pääsemiseks klahvi Lõpeta ja Nool üles. Mõelge välja, mis rida see on. Ja siis veergudes G ja H ja kui te seda vaatate, peate heitma pilgu oma Exceli andmetele ja välja selgitama, kus on teie kaks uut veergu. Ma ei tea, mitu veergu teil on. Võib-olla on teie uued veerud I-s ja J-s või võib-olla C-s ja D-s. Ma ei tea, mõelge välja, kus need asuvad ja peidame kõik need read hästi. Nii et minu puhul algas see G6-st, see on esimene koht, kus meil on number:H ja siis ühendan viimast rida, mis meil täna on, kasutades kolme semikooloniga numbrivormingut, mis peidab andmed.
Olgu, siis see järgmine, õppisin selle järgmise teadetetahvlilt. Kui te ei pane aktiivset akent enne selle koodi käivitamist lehelõike eelvaate režiimi, ei tööta see kood. See töötab mõnel lehepausil, kuid mitte kõigil lehepausidel, seega peate lehepausid ajutiselt kuvama. Ja siis silmus siin: Igaühe jaoks on see minu objektimuutuja - HPB ActiveSheetis.HPageBreaks. Saate aru viimasest reast, eks? Nii et selle objekti jaoks, leheküljepausiks, selgitage välja asukoht, mõelge välja rida. Ja see on tegelikult järgmise lehe esimene rida, nii et pean sellest lahutama 1, olgu. Ja siin, ma tunnistan, et see on uskumatult odav, minge 7. veergu, mis on veerg G, muutke NumberFormat valuutaks, just sellest reast. Ja siis minge 8. veergu, mis on H, muutke see protsendiks ja minge edasi.Lõpuks väljuge horisontaalsest või lehelõike eelvaatusest ja minge tagasi tavavaatesse.
Hästi, nii et see on meie kood. Failin, sulgen ja pöördun tagasi Microsoft Exceli juurde. Ma tahan lihtsat viisi selle käivitamiseks, nii et ma kavatsen sisestada, valida siin kena kuju. Valin ümardatud ristküliku, joonistan oma parempoolse ristküliku ümber lehe paigutuse, minge jaotisse Efektid, valin Office 2007 efektid. Ja siis siin vahekaardil Vorming on meil mõnus viis lisada sellele sära, eks .
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 hei, ma tahan tänada teid peatumast. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2058.xlsm
