Чи може Excel надрукувати загальний підсумок у колонтитулі для кожної сторінки? Він не вбудований, але короткий макрос вирішить проблему.
Переглянути відео
- Мета: роздрукувати загальну категорію та% категорії внизу кожної друкованої сторінки
- Проблема: ніщо в інтерфейсі користувача Excel не може дати формулі зрозуміти, що ви знаходитесь внизу друкованої сторінки
- Так, ви можете "бачити" розриви сторінок, але формули не бачать їх
- Можливе рішення: Використовуйте макрос
- Стратегія: додайте загальну суму та% категорії для кожного рядка. Сховати на всіх рядах.
- Загальна сума для формули категорії:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % формули категорії:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Якщо ваша книга зберігається як XLSX, виконайте функцію Зберегти як, щоб зберегти як XLSM
- Якщо ви ніколи не використовували макроси, змініть захист макросів
- Якщо ви ніколи не використовували макроси, відкрийте вкладку Розробник
- Перейдіть на VBA
- Вставте модуль
- Введіть код
- Призначте цей макрос фігурі
- Коли розмір сторінки змінюється, запустіть макрос скидання
Стенограма відео
Дізнайтеся Excel з подкасту, епізод 2058: Підсумок у кінці кожної сторінки
Привіт, ласкаво просимо назад до трансляції, я Білл Джелен. Сьогоднішнє запитання, надіслане Wiley: Wiley хоче показати загальний дохід та відсоток категорії в останньому рядку кожної друкованої сторінки. Отже, Вайлі надрукував тут звіти з тоннами і тоннами записів, декілька сторінок для кожної категорії там, у стовпці А. І коли ми спустимось до кінця сторінки для друку, Вайлі шукає тут загальну суму, яка показує загальний дохід, загальна сума в межах цієї категорії, а потім відсоток категорії. Отже, ви бачите, що ми там на рівні 9,7%, коли я переходжу на сторінку 2 - 21.1, сторінку 3 - 33.3 тощо. І на розриві сторінки, де ми закінчимо з категорією А, загальний підсумок для категорії та загальний підсумок 100%. Добре, і коли Вілі запитав мене про це, я сказав: "О ні, ми не … там"s немає можливості в нижньому колонтитулі поставити поточний підсумок. " Добре, отже, це, правда, жахлива дешева обмана, і я закликаю тих, хто дивиться це на YouTube, якщо у вас є кращий спосіб, будь-ласка, будь-ласка, згадайте це в коментарях, добре? Отже, моя ідея - просто там, у стовпцях G і H, приховати загальну суму та відсоток категорії у кожному окремому рядку. Добре, і тоді ми використовуємо макрос, щоб визначити, чи знаходимось ми в кінці сторінки.в кінці сторінки.в кінці сторінки.
Добре, так дві формули, які ми хочемо тут сказати, привіт, якщо ця категорія дорівнює попередній категорії. Отже, якщо A6 = A5, тоді візьміть суму цього доходу, значить, це у F6 і попередній загальний підсумок там у G5. Тепер, оскільки я використовую тут функцію SUM, це не помиляється, якщо ми коли-небудь спробуємо додати запущений підсумок. В іншому випадку ми просто будемо знаходитись у новій категорії, тому, коли ми переходимо з А на В, ми просто беремо СУММ значення зліва від нас, яке я міг би просто поставити туди F6. Але ось ми, знаєте, запізно. І тоді відсоток категорії, цей буде жахливо неефективним. Ми беремо дохід у цьому рядку, розділений на суму всіх доходів, де категорія дорівнює A6. Отже це всі категорії,це категорія в цьому рядку, а потім додайте відповідну комірку з усіх рядків. Звичайно, знаки $ - там знаки 1, 2, 3, 4 $. У A6 немає знаків $, а там знаків 4 $. Гаразд, і ми покажемо це число як цифру, можливо, роздільник 1000, натисніть кнопку ОК, а потім тут у відсотках з таким десятковим знаком. Гаразд, і ми скопіюємо цю формулу до всіх комірок. БАМ, ось так, гаразд. Але зараз мета полягає в тому, щоб переконатися, що ми бачимо ці підсумки лише тоді, коли дійдемо до розриву сторінки. Гаразд, саме там. Це автоматичний розрив сторінки, а потім пізніше, коли ми переходимо з кінця А на В, розрив сторінки вручну. Отже, цей ручний розрив сторінки тут відрізняється від автоматичного розриву сторінки.і ми покажемо це число як Число, можливо, роздільник 1000, натисніть ОК, а потім тут у відсотках з таким десятковим знаком. Гаразд, і ми скопіюємо цю формулу до всіх комірок. БАМ, ось так, гаразд. Але зараз мета полягає в тому, щоб переконатися, що ми бачимо ці підсумки лише тоді, коли дійдемо до розриву сторінки. Гаразд, саме там. Це автоматичний розрив сторінки, а потім пізніше, коли ми переходимо з кінця А на Б, розрив сторінки вручну. Отже, цей ручний розрив сторінки тут відрізняється від автоматичного розриву сторінки.і ми покажемо це число як Число, можливо, роздільник 1000, натисніть ОК, а потім тут у відсотках з таким десятковим знаком. Гаразд, і ми скопіюємо цю формулу до всіх комірок. БАМ, ось так, гаразд. Але зараз мета полягає в тому, щоб переконатися, що ми бачимо ці підсумки лише тоді, коли дійдемо до розриву сторінки. Гаразд, саме там. Це автоматичний розрив сторінки, а потім пізніше, коли ми переходимо з кінця А на В, розрив сторінки вручну. Отже, цей ручний розрив сторінки тут відрізняється від автоматичного розриву сторінки.Але зараз мета полягає в тому, щоб переконатися, що ми бачимо ці підсумки лише тоді, коли дійдемо до розриву сторінки. Гаразд, саме там. Це автоматичний розрив сторінки, а потім пізніше, коли ми переходимо з кінця А на В, розрив сторінки вручну. Отже, цей ручний розрив сторінки тут відрізняється від автоматичного розриву сторінки.Але зараз мета полягає в тому, щоб переконатися, що ми бачимо ці підсумки лише тоді, коли дійдемо до розриву сторінки. Гаразд, саме там. Це автоматичний розрив сторінки, а потім пізніше, коли ми переходимо з кінця А на Б, розрив сторінки вручну. Отже, цей ручний розрив сторінки тут відрізняється від автоматичного розриву сторінки.
Добре, зараз ви тут помітите, що цей файл зберігається як файл XLSX, оскільки саме так Excel хоче зберігати файли. XLSX - це тип пошкодженого файлу, який не дозволяє макроси, правда? Найгірший тип файлу у світі. Отже, не пропускайте цей або цей крок. Вся ваша робота звідси і звідси буде втрачена. Збережи як, і ми збираємося зберігати не як книгу Excel, а як книгу з підтримкою макросів, або як двійкову книгу, або як XLS. Я збираюся піти з робочою книжкою з підтримкою макросів. Якщо ви не зробите цього кроку, ви збираєтесь втратити решту роботи, яку ви робите. Добре, і тоді, якщо ви ніколи раніше не запускали макроси, ми клацнемо правою клавішею миші та скажемо Налаштувати стрічку. Тут, праворуч, поставте прапорець для розробника, який відкриє вам вкладку розробника. Коли у вас з’явиться вкладка Розробник, ми можемо перейти до Macro Security,за замовчуванням це буде тут. Вимкніть усі макроси і не кажіть мені, що ви відключили цілі макроси. Ви хочете перейти до другого, таким чином, коли ми відкриємо файл, ми скажемо: “Гей, тут макроси. Ви створили їх? Ви з цим все в порядку? " І ви можете сказати, увімкніть макроси. Добре, натисніть OK.
Тепер ми перейдемо до редактора Visual Basic. Якщо ви ніколи раніше не використовували Visual Basic, ви почнете з цього повністю сірого екрана, перейдіть до View та Project Explorer. Ось список усіх відкритих книг. Отже, у мене є надбудова Solver, моя особиста книга макросів, і ось книга, над якою я працюю. Переконайтеся, що вибрано цю книгу, виконайте Вставка, Модуль. Вставте, модуль отримає тут гарне велике порожнє біле полотно. Добре, і тоді ви збираєтеся ввести цей код. Гаразд, зараз ми використовуємо об’єкт, який називається HPageBreak, горизонтальний розрив сторінки. І оскільки я не так багато цього використовую, мені довелося оголосити це тут як змінну, як об'єкт HPB, таким чином я міг би бачити варіанти, доступні мені в кожному. Добре,з’ясуйте, де сьогодні знаходиться останній рядок із даними, тому я використовую стовпець A, переходжу до кінця стовпця A - A1048576. Тут тут L, а не 1, це L. Це всі закручують. L як в Excel. Це звучить як Excel. Зрозумів? Excel вгору. Отже, перейдіть до A1048576, натисніть клавішу закінчення та клавішу зі стрілкою вгору, щоб перейти до останнього рядка. З’ясуйте, який це рядок. А потім у стовпцях G і H, і якщо ви переглядаєте це, вам потрібно поглянути на ваші дані Excel і зрозуміти, де знаходяться ваші два нові стовпці, добре. Не знаю, скільки у вас стовпців. Можливо, ваші нові стовпці закінчені в I та J, а може, вони в C та D. Я не знаю, з’ясуйте, де вони знаходяться, і ми збираємось приховати всі ці рядки, добре. Отже, у моєму випадку це починалося з G6, це перше місце, де ми маємо номер:H, а потім я об'єдную останній рядок, який ми маємо сьогодні, використовуючи числовий формат із трьох крапок з комою, який буде приховувати дані.
Добре, тоді цей наступний, я дізнався цей наступний з дошки оголошень. Якщо ви не переведете активне вікно в режим попереднього перегляду сторінки перед запуском цього коду, цей код не буде працювати. Це працює для деяких розривів сторінки, але не для всіх розривів сторінки, тому вам доведеться тимчасово відображати розриви сторінки. А потім цикл тут: Для кожного це моя змінна об’єкта - HPB In 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
