Может ли 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 хочет показать промежуточную сумму дохода и процентную долю категории в последней строке каждой напечатанной страницы. Итак, Wiley напечатал здесь отчеты с тоннами и тоннами записей, несколько страниц для каждой категории там, в столбце A. И когда мы дойдем до конца страницы для печати, Wiley ищет здесь итоговую сумму, которая показывает общий доход, промежуточный итог внутри этой категории, а затем процент от категории. Итак, вы можете увидеть, что у нас там 9,7%, когда я перехожу к странице 2 - 21.1, странице 3 - 33.3 и так далее. И в разрыве страницы, где мы закончили с категорией A, общей суммой для категории и общей суммой 100%. Хорошо, и когда Уайли спросил меня об этом, я подумала: «О нет, мы не … там»нельзя в нижнем колонтитуле указать промежуточную сумму ». Хорошо, это, по общему признанию, ужасный дешевый обман, и я призываю всех, кто смотрит это на YouTube, если у вас есть лучший способ, пожалуйста, во что бы то ни стало, упомяните это в комментариях, хорошо? Итак, моя идея просто изложена в столбцах G и H, чтобы скрыть промежуточную сумму и процентную долю категории в каждой отдельной строке. Хорошо, а затем мы используем макрос, чтобы определить, дошли ли мы до конца страницы.повторно в конце страницы.повторно в конце страницы.
Хорошо, поэтому две формулы, которые мы хотим здесь сказать, говорят: «Эй, если эта категория равна предыдущей». Итак, если A6 = A5, тогда возьмите СУММУ этого дохода, так что это в F6, а предыдущая промежуточная сумма там в G5. Теперь, поскольку я использую здесь функцию СУММ, это не приведет к ошибке, если мы когда-нибудь попытаемся добавить промежуточную сумму. В противном случае мы просто окажемся в совершенно новой категории, поэтому, когда мы переключаемся с A на B, мы просто берем СУММУ значения слева от нас, которое я мог бы просто поместить туда F6. Но вот мы, понимаете, опоздали. А затем процент от категории, этот будет ужасно неэффективным. Мы берем доход в этой строке, деленный на СУММУ всех доходов, где категория равна A6. Итак, это все категории,это категория в этой строке, а затем сложите соответствующую ячейку из всех строк. Конечно, знаки $ - там 1, 2, 3, 4 знака $. Знаков $ в A6 и знаков $ 4 нет. Хорошо, и мы покажем это число в виде числа, может быть, разделителя 1000, нажмите ОК, а затем здесь в процентах с одним десятичным знаком. Хорошо, и мы скопируем эту формулу во все ячейки. БАМ, вот так, ладно. Но теперь цель состоит в том, чтобы убедиться, что мы видим эти итоги только тогда, когда дойдем до разрыва страницы. Хорошо, это прямо здесь. Это автоматический разрыв страницы, а затем, когда мы переключаемся с конца A на B, разрыв страницы вручную. Итак, этот ручной разрыв страницы здесь отличается от автоматического разрыва страницы.и мы покажем это число как число, может быть разделитель 1000, нажмите ОК, а затем здесь в процентах с одним десятичным знаком вроде этого. Хорошо, и мы скопируем эту формулу во все ячейки. БАМ, вот так, ладно. Но теперь цель состоит в том, чтобы убедиться, что мы видим эти итоги только тогда, когда дойдем до разрыва страницы. Хорошо, это прямо здесь. Это автоматический разрыв страницы, а затем, когда мы переключаемся с конца A на B, разрыв страницы вручную. Таким образом, этот ручной разрыв страницы здесь отличается от автоматического разрыва страницы.и мы покажем это число как число, может быть разделитель 1000, нажмите ОК, а затем здесь в процентах с одним таким десятичным знаком. Хорошо, и мы скопируем эту формулу во все ячейки. БАМ, вот так, ладно. Но теперь цель состоит в том, чтобы убедиться, что мы видим эти итоги только тогда, когда дойдем до разрыва страницы. Хорошо, это прямо здесь. Это автоматический разрыв страницы, а затем, когда мы переключаемся с конца A на B, разрыв страницы вручную. Таким образом, этот ручной разрыв страницы здесь отличается от автоматического разрыва страницы.Но теперь цель состоит в том, чтобы убедиться, что мы видим эти итоги только тогда, когда дойдем до разрыва страницы. Хорошо, это прямо здесь. Это автоматический разрыв страницы, а затем, когда мы переключаемся с конца A на B, разрыв страницы вручную. Таким образом, этот ручной разрыв страницы здесь отличается от автоматического разрыва страницы.Но теперь цель состоит в том, чтобы убедиться, что мы видим эти итоги только тогда, когда дойдем до разрыва страницы. Хорошо, это прямо здесь. Это автоматический разрыв страницы, а затем, когда мы переключаемся с конца A на B, разрыв страницы вручную. Итак, этот ручной разрыв страницы здесь отличается от автоматического разрыва страницы.
Хорошо, теперь вы заметите, что этот файл сохранен как файл XLSX, потому что именно так Excel хочет сохранять файлы. XLSX - это испорченный тип файлов, который не поддерживает макросы, верно? Худший тип файла в мире. Так что не пропускайте ни этот шаг, ни этот. Вся ваша работа отсюда и дальше будет потеряна. Сохранить как, и мы собираемся сохранить не как книгу Excel, а как книгу с поддержкой макросов, или как двоичную книгу, или как XLS. Я собираюсь использовать книгу с поддержкой макросов. Если вы не сделаете этого шага, вы потеряете всю остальную работу, которую делаете. Хорошо, а затем, если вы никогда раньше не запускали макросы, мы собираемся щелкнуть правой кнопкой мыши и сказать «Настроить ленту». Здесь, с правой стороны, выберите поле для разработчика, чтобы открыть вкладку «Разработчик». Когда у вас есть вкладка Разработчик, мы можем перейти в Macro Security,по умолчанию он будет здесь. Отключите все макросы и не говорите мне, что вы отключили все макросы. Вы хотите переключиться на второй, чтобы при открытии файла мы говорили: «Привет, здесь есть макросы. Вы их создали? Ты с этим согласен? И вы можете сказать: Включите макросы. Хорошо, нажмите ОК.
Теперь мы перейдем к визуальному базовому редактору. Если вы никогда раньше не использовали Visual Basic, вы начнете с этого полностью серого экрана, перейдите в View и Project Explorer. Вот список всех открытых книг. Итак, у меня есть надстройка Solver, моя личная книга макросов, и вот книга, над которой я работаю. Убедитесь, что эта книга выбрана, выполните Insert, Module. Insert, Module получит здесь красивый большой чистый белый холст. Хорошо, а затем вы собираетесь ввести этот код. Хорошо, сейчас мы используем объект под названием HPageBreak, горизонтальный разрыв страницы. И поскольку я не использую его часто, мне пришлось объявить его здесь как переменную, как объект HPB, чтобы я мог видеть варианты, доступные мне в каждом из них. Хорошо,выяснить, где находится последняя строка с данными сегодня, поэтому я использую столбец A, я перехожу к концу столбца A - A1048576. Здесь буква L, а не 1, это L. Все лажают. L как в Excel. Похоже на Excel. Возьми? Превосходно. Итак, перейдите к A1048576, нажмите клавишу End и клавишу со стрелкой вверх, чтобы перейти к последней строке. Выясните, что это за строка. А затем в столбцах G и H, и если вы смотрите это, вам нужно взглянуть на свои данные Excel и выяснить, где находятся ваши два новых столбца, хорошо. Я не знаю, сколько у вас столбцов. Может быть, ваши новые столбцы закончились в I и J, или, может быть, они в C и D. Я не знаю, выясните, где они находятся, и мы собираемся скрыть все эти строки, хорошо. Итак, в моем случае это началось с G6, это первое место, где у нас есть номер:H, а затем я объединяю последнюю строку, которая у нас есть сегодня, используя числовой формат из трех точек с запятой, который скроет данные.
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.
Ой, я хочу поблагодарить тебя за то, что заглянули. Увидимся в следующий раз на другой сетевой трансляции от.
Скачать файл
Загрузите образец файла здесь: Podcast2058.xlsm
