Може ли Excel да отпечата текуща сума в долния колонтитул за всяка страница? Той не е вграден, но кратък макрос ще реши проблема.
Гледам видео
- Цел: Отпечатайте категория с общо съдържание и% от категорията в долната част на всяка отпечатана страница
- Проблем: нищо в потребителския интерфейс на Excel не може да покаже на формула, че сте в долната част на отпечатана страница
- Да, можете да "видите" прекъсванията на страницата, но формулите не могат да ги видят
- Възможно решение: Използвайте макрос
- Стратегия: Добавете текущия сбор и% от категорията за всеки ред. Скриване на всички редове.
- Текущо общо за категория Формула:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % от формулата на категорията:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Ако вашата работна книга е запазена като XLSX, направете Save As, за да запишете като XLSM
- Ако никога не сте използвали макроси, променете защитата на макросите
- Ако никога не сте използвали макроси, покажете раздела за програмисти
- Преминете към VBA
- Поставете модул
- Въведете кода
- Присвояване на този макрос на форма
- Когато размерът на страницата се промени, стартирайте макроса за нулиране
Видео транскрипция
Научете Excel от Подкаст, Епизод 2058: Текущо общо в края на всяка страница
Хей, добре дошъл в мрежата, аз съм Бил Джелен. Днешният въпрос, изпратен от Wiley: Wiley иска да покаже текущ общ приход и процент на категория в последния ред на всяка отпечатана страница. И така, Wiley е отпечатал доклади тук с тонове и тонове записи, множество страници за всяка категория там в колона A. И когато стигнем до края на страницата за печат, Wiley търси обща сума тук, която показва общите приходи текущо общо в тази категория и след това процент от категорията. И така, можете да видите, че там сме на 9,7%, когато отида на страница 2 - 21.1, страница 3 - 33.3 и т.н. И при прекъсването на страницата, където приключваме с категория А, общ сбор за категорията и общ сбор 100%. Добре, и когато Уайли ме попита за това, аз бях като, "О, не, ние не … там"няма начин в долния колонтитул да се сложи текущ сбор. " Добре, това разбира се е ужасна евтина измама и насърчавам всеки, който гледа това в YouTube, ако имате по-добър начин, моля, непременно го споменете в коментарите, нали? И така, идеята ми е точно там, в колони G и H, за да скрия текущия сбор и процента на категорията във всеки отделен ред. Добре и тогава използваме макрос, за да открием дали сме в края на страницата.отново в края на страницата.отново в края на страницата.
Добре, така че двете формули, които искаме тук, казват, хей, ако тази категория е равна на предишната категория. Така че, ако A6 = A5, тогава вземете сумата на тези приходи, така че това е във F6 и предишното текущо общо там в G5. Тъй като тук използвам функцията SUM, това не греши, ако някога се опитаме да добавим текущо общо. В противен случай просто ще бъдем в съвсем нова категория, така че когато превключим от А в Б, просто ще вземем СУМАТА на стойността вляво от нас, което бих могъл просто да поставя F6 там. Но тук сме, знаете ли, твърде късно. И тогава процент от категорията, този ще бъде ужасно неефективен. Вземаме приходите от този ред, разделени на сумата от всички приходи, където категорията е равна на A6. Това са всички категории,това е категорията в този ред и след това добавете съответната клетка от всички редове. Разбира се, $ знаци - 1, 2, 3, 4 $ знаци там. Няма знаци за $ в A6 и знаци за 4 $ там. Добре, и ние ще покажем това число като число, може би разделител 1000, щракнете върху OK и след това тук като процент с един десетичен знак като този. Добре и ще копираме тази формула във всички клетки. БАМ, така, добре. Но сега целта тук е да се уверим, че виждаме тези суми едва когато стигнем до прекъсването на страницата. Добре, точно там е. Това е автоматично прекъсване на страницата и след това по-късно, когато превключим от края на А в Б, ръчно прекъсване на страницата. И така, това ръчно прекъсване на страницата тук е различно от автоматичното прекъсване на страницата.и ще покажем това число като число, може би разделител 1000, щракнете върху OK и след това тук като процент с един десетичен знак като този. Добре и ще копираме тази формула във всички клетки. БАМ, така, добре. Но сега целта тук е да се уверим, че виждаме тези суми едва когато стигнем до прекъсването на страницата. Добре, точно там е. Това е автоматично прекъсване на страницата и след това по-късно, когато превключим от края на А в Б, ръчно прекъсване на страницата. И така, това ръчно прекъсване на страницата тук е различно от автоматичното прекъсване на страницата.и ще покажем това число като число, може би разделител 1000, щракнете върху OK и след това тук като процент с един десетичен знак като този. Добре и ще копираме тази формула във всички клетки. БАМ, така, добре. Но сега целта тук е да се уверим, че виждаме тези суми едва когато стигнем до прекъсването на страницата. Добре, точно там е. Това е автоматично прекъсване на страницата и след това по-късно, когато превключим от края на А в Б, ръчно прекъсване на страницата. И така, това ръчно прекъсване на страницата тук е различно от автоматичното прекъсване на страницата.Но сега целта тук е да се уверим, че виждаме тези суми едва когато стигнем до прекъсването на страницата. Добре, точно там е. Това е автоматично прекъсване на страницата и след това по-късно, когато превключим от края на А в Б, ръчно прекъсване на страницата. И така, това ръчно прекъсване на страницата тук е различно от автоматичното прекъсване на страницата.Но сега целта тук е да се уверим, че виждаме тези суми едва когато стигнем до прекъсването на страницата. Добре, точно там е. Това е автоматично прекъсване на страницата и след това по-късно, когато превключим от края на А в Б, ръчно прекъсване на страницата. И така, това ръчно прекъсване на страницата тук е различно от автоматичното прекъсване на страницата.
Добре, сега ще забележите тук, че този файл се записва като XLSX файл, защото така Excel иска да записва файлове. XLSX е типът счупен файл, който не позволява макроси, нали? Най-лошият тип файл в света. Така че, не пропускайте тази или тази стъпка. Цялата ви работа от тук и навън ще бъде загубена. Запазване като и ще запазим не като работна книга на Excel, а като работна книга с активиран макрос или като двоична работна книга или като XLS. Ще отида с Macro-Enabled Workbook. Ако не направите тази стъпка, вие сте на път да загубите останалата част от работата, която вършите. Добре, и тогава, ако никога преди не сте изпълнявали макроси, ще щракнем с десния бутон и ще кажем Персонализиране на лентата. Тук в дясната страна изберете полето за програмист, което ще ви даде раздел за програмисти. След като имате раздела за програмисти, можем да преминем към Macro Security,по подразбиране ще бъде тук Деактивирайте всички макроси и не ми казвайте, че сте деактивирали всички макроси. Искате да преминете към втория, по този начин, когато отворим файла, ще кажем: „Хей, тук има макроси. Създадохте ли тези? Добре ли сте с това? ” И можете да кажете: Активирайте макросите. Добре, щракнете върху OK.
Сега ще преминем към редактора на visual basic. Ако никога преди не сте използвали Visual Basic, ще започнете с този напълно сив екран, отидете на View и Project Explorer. Ето списък на всички отворени работни книги. Така че имам добавката Solver, моята лична работна книга за макроси и ето работната книга, по която работя. Уверете се, че тази работна книга е избрана, направете Вмъкване, Модул. Вмъкнете, модулът ще получи тук хубаво голямо празно, бяло платно. Добре и тогава ще въведете този код. Добре сега, тук използваме обект, наречен HPageBreak, хоризонтално прекъсване на страницата. И тъй като не използвам това много, трябваше да го декларирам тук като променлива, като HPB на обект, по този начин ще мога да видя избора, който ми е достъпен във всеки един. Добре,разберете къде е последният ред с данни днес, така че използвам колона A, отивам в края на колона A - A1048576. Това е L тук, а не 1, това е L. Всеки го прецаква. L като в Excel. Звучи като Excel. Вземи го? Excel нагоре. Така че, отидете на A1048576, натиснете клавиша End и стрелката нагоре, за да стигнете до последния ред. Разберете кой ред е това. И след това в колони G и H и ако гледате това, трябва да разгледате данните си в Excel и да разберете къде са вашите две нови колони, добре. Не знам колко колони имате. Може би новите ви колони са свършили в I и J, или може би са в C и D. Не знам, разберете къде са те и ще скрием всички тези редове, добре. Така че в моя случай започваше от G6, това е първото място, където имаме номер,:H и след това обединявам последния ред, който имаме днес, използвайки числов формат от три точки и запетая, които ще скрият данните.
Добре, тогава този следващ, научих този следващ от таблото за съобщения. Ако не поставите активния прозорец в режим Преглед на прекъсване на страницата, преди да стартирате този код, този код няма да работи. Той работи за някои от прекъсванията на страницата, но не за всички прекъсвания на страниците, така че трябва временно да показвате прекъсванията на страниците. И след това цикъл тук: За всеки това е моята обектна променлива - HPB в ActiveSheet.HPageBreaks. Разберете последния ред, нали? Така че за този обект, за прекъсване на страницата, разберете местоположението, разберете реда. И това всъщност е първият ред на следващата страница, така че трябва да извадя 1 от това, добре. И тогава тук, признавам, че това е невероятно евтино, отидете на колона 7, която е колона G, променете NumberFormat да бъде валута, точно от този ред. И след това отидете на колона 8, която е H, и я променете на процент и преминете следващата.И накрая, излезте хоризонтално или преглед на прекъсване на страницата и се върнете в нормален изглед.
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.
О, хей, искам да ти благодаря, че се отби. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2058.xlsm
