Czy program Excel może wydrukować sumę bieżącą w stopce dla każdej strony? Nie jest wbudowane, ale krótkie makro rozwiąże problem.
Obejrzyj wideo
- Cel: Wydrukuj całkowitą liczbę kategorii i% kategorii na dole każdej drukowanej strony
- Problem: nic w interfejsie użytkownika programu Excel nie może poinformować formuły, że znajdujesz się na dole drukowanej strony
- Tak, możesz „zobaczyć” podziały stron, ale formuły ich nie widzą
- Możliwe rozwiązanie: użyj makra
- Strategia: dodaj sumę bieżącą i% kategorii dla każdego wiersza. Ukryj we wszystkich wierszach.
- Suma bieżąca dla formuły kategorii:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % formuły kategorii:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Jeśli skoroszyt jest zapisany jako XLSX, wykonaj polecenie Zapisz jako, aby zapisać jako XLSM
- Jeśli nigdy nie korzystałeś z makr, zmień zabezpieczenia makr
- Jeśli nigdy nie korzystałeś z makr, pokaż kartę Deweloper
- Przejdź na VBA
- Wstaw moduł
- Wpisz kod
- Przypisz to makro do kształtu
- W miarę zmiany rozmiaru strony uruchom makro resetowania
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2058: Running Total na końcu każdej strony
Hej, witaj z powrotem w netcastie, jestem Bill Jelen. Dzisiejsze pytanie wysłane przez Wiley: Wiley chce pokazać bieżącą sumę przychodów i procent kategorii w ostatnim wierszu każdej drukowanej strony. Więc Wiley wydrukował tutaj raporty z tonami rekordów, wiele stron dla każdej kategorii w kolumnie A. A kiedy dojdziemy do końca drukowanej strony, Wiley szuka tutaj sumy pokazującej całkowity dochód, suma bieżąca w tej kategorii, a następnie procent kategorii. I tak, widzisz, że mamy tam 9,7%, kiedy przejdę do strony 2 - 21.1, strony 3 - 33.3 i tak dalej. A na końcu strony, gdzie skończymy z kategorią A, suma całkowita dla kategorii i suma całkowita 100%. W porządku, a kiedy Wiley zapytał mnie o to, pomyślałem: „O nie, nie… tam”w stopce nie ma możliwości umieszczenia sumy bieżącej ”. W porządku, więc to wprawdzie okropny tani cheat i zachęcam każdego, kto ogląda to na YouTube, jeśli masz lepszy sposób, proszę bardzo, wspomnij o tym w komentarzach, dobrze? Tak więc mój pomysł jest po prostu w kolumnach G i H, aby ukryć bieżącą sumę i procent kategorii w każdym wierszu. W porządku, a następnie używamy makra, aby wykryć, czy jesteśmy na końcu strony.ponownie na końcu strony.ponownie na końcu strony.
W porządku, więc dwie formuły, które tu chcemy, mówią: hej, jeśli ta kategoria jest równa poprzedniej kategorii. Więc jeśli A6 = A5, to weź SUMĘ tego przychodu, więc to jest w F6, a poprzednia suma bieżąca tam w G5. Ponieważ używam tutaj funkcji SUMA, nie spowoduje to błędu, jeśli kiedykolwiek spróbujemy dodać bieżącą sumę. W przeciwnym razie będziemy znajdować się w zupełnie nowej kategorii, więc kiedy przełączymy się z A na B, weźmiemy SUMĘ wartości po lewej stronie nas, którą mogłem po prostu umieścić tam F6. Ale oto jesteśmy, wiesz, za późno. A potem procent kategorii, ta będzie okropnie nieefektywna. Przychód z tego wiersza podzielony przez SUMĘ wszystkich dochodów, w przypadku których kategoria jest równa A6. Więc to są wszystkie kategorie,to jest kategoria w tym wierszu, a następnie dodaj odpowiednią komórkę ze wszystkich wierszy. Oczywiście znaki $ - 1, 2, 3, 4 $ tam. Brak znaków $ na A6 i 4 $. W porządku, pokażemy tę liczbę jako liczbę, może separator 1000, kliknij OK, a następnie tutaj jako procent z jednym miejscem po przecinku. W porządku, skopiujemy tę formułę do wszystkich komórek. BAM, w porządku. Ale teraz celem jest upewnienie się, że widzimy te sumy tylko wtedy, gdy dojdziemy do podziału strony. W porządku, jest właśnie tam. To jest automatyczny podział strony, a później, kiedy przechodzimy od końca A do B, ręczny podział strony. Tak więc ten ręczny podział strony różni się od automatycznego podziału strony.a pokażemy tę liczbę jako liczbę, może separator 1000, kliknij OK, a następnie tutaj jako procent z jednym miejscem po przecinku. W porządku, skopiujemy tę formułę do wszystkich komórek. BAM, w porządku. Ale teraz celem jest upewnienie się, że widzimy te sumy tylko wtedy, gdy dojdziemy do podziału strony. W porządku, jest właśnie tam. To jest automatyczny podział strony, a później, kiedy przechodzimy od końca A do B, ręczny podział strony. Tak więc ten ręczny podział strony różni się od automatycznego podziału strony.a pokażemy tę liczbę jako liczbę, może separator 1000, kliknij OK, a następnie tutaj jako procent z jednym miejscem po przecinku. W porządku, skopiujemy tę formułę do wszystkich komórek. BAM, w porządku. Ale teraz celem jest upewnienie się, że widzimy te sumy tylko wtedy, gdy dojdziemy do podziału strony. W porządku, jest właśnie tam. To jest automatyczny podział strony, a później, kiedy przechodzimy od końca A do B, ręczny podział strony. Tak więc ten ręczny podział strony różni się od automatycznego podziału strony.Ale teraz celem jest upewnienie się, że widzimy te sumy tylko wtedy, gdy dojdziemy do podziału strony. W porządku, jest właśnie tam. To jest automatyczny podział strony, a później, kiedy przechodzimy od końca A do B, ręczny podział strony. Tak więc ten ręczny podział strony różni się od automatycznego podziału strony.Ale teraz celem jest upewnienie się, że widzimy te sumy tylko wtedy, gdy dojdziemy do podziału strony. W porządku, jest właśnie tam. To jest automatyczny podział strony, a później, kiedy przechodzimy od końca A do B, ręczny podział strony. Tak więc ten ręczny podział strony różni się od automatycznego podziału strony.
W porządku, teraz zauważysz, że ten plik jest zapisywany jako plik XLSX, ponieważ w ten sposób program Excel chce zapisywać pliki. XLSX to uszkodzony typ pliku, który nie zezwala na makra, prawda? Najgorszy typ pliku na świecie. Więc nie pomijaj tego ani tego kroku. Cała twoja praca stąd i poza nią zostanie utracona. Zapisz jako, a zamierzamy zapisać nie jako skoroszyt programu Excel, ale jako skoroszyt z włączoną obsługą makr lub jako skoroszyt binarny lub jako XLS. Zamierzam przejść do skoroszytu z włączoną obsługą makr. Jeśli nie wykonasz tego kroku, stracisz resztę wykonanej pracy. W porządku, a jeśli nigdy wcześniej nie uruchamiałeś makr, klikniemy prawym przyciskiem myszy i powiemy Dostosuj Wstążkę. Tutaj, po prawej stronie, wybierz pole Developer, które otworzy zakładkę Developer. Gdy masz już zakładkę Developer, możemy przejść do Macro Security,domyślnie będzie tutaj. Wyłącz wszystkie makra i nie mów mi, że wyłączyłeś wszystkie makra. Chcesz przełączyć się na drugą, w ten sposób, kiedy otworzymy plik, powiemy: „Hej, tu są makra. Czy to stworzyłeś? Czy zgadzasz się z tym? ” I możesz powiedzieć, Włącz makra. W porządku, kliknij OK.
Teraz przejdziemy do edytora Visual Basic. Jeśli nigdy wcześniej nie korzystałeś z Visual Basic, zaczniesz od tego całkowicie szarego ekranu, przejdź do View i Project Explorer. Oto lista wszystkich otwartych skoroszytów. Mam więc dodatek Solver, mój osobisty skoroszyt makr, a oto skoroszyt, nad którym pracuję. Upewnij się, że ten skoroszyt jest zaznaczony, wykonaj Wstaw, Moduł. Wstaw, moduł otrzyma tutaj ładne, duże, białe płótno. W porządku, a następnie wpiszesz ten kod. W porządku, używamy tutaj obiektu o nazwie HPageBreak, poziomego podziału strony. A ponieważ nie używam tego często, musiałem zadeklarować to tutaj jako zmienną, jako obiekt HPB, dzięki czemu będę mógł zobaczyć opcje, które są dla mnie dostępne w każdym z nich. W porządku,dowiedzieć się, gdzie jest dzisiaj ostatni wiersz z danymi, więc używam kolumny A, idę do końca kolumny A - A1048576. To jest tutaj L, a nie 1, to jest L. Wszyscy to schrzanili. L jak w Excelu. Brzmi jak Excel. Zdobyć? Excel up. A więc przejdź do A1048576, naciśnij klawisz End i klawisz strzałki w górę, aby przejść do ostatniego wiersza. Dowiedz się, który to wiersz. A potem w kolumnach G i H, a jeśli to oglądasz, musisz spojrzeć na dane Excela i dowiedzieć się, gdzie są twoje dwie nowe kolumny, w porządku. Nie wiem, ile masz kolumn. Może twoje nowe kolumny są skończone w I i J, a może są w C i D. Nie wiem, dowiedz się, gdzie to jest i zamierzamy ukryć wszystkie te wiersze, w porządku. Więc w moim przypadku zaczęło się od G6, to pierwsze miejsce, w którym mamy liczbę:H, a następnie konkatenuję ostatni wiersz, który mamy dzisiaj, używając formatu liczbowego trzech średników, który ukryje dane.
W porządku, następny, nauczyłem się następnego z forum dyskusyjnego. Jeśli nie umieścisz aktywnego okna w trybie podglądu podziału strony przed uruchomieniem tego kodu, ten kod nie będzie działał. Działa w przypadku niektórych podziałów stron, ale nie wszystkich podziałów stron, więc musisz tymczasowo wyświetlić podziały stron. A potem pętla tutaj: Dla każdego, to jest moja zmienna obiektu - HPB In ActiveSheet.HPageBreaks. Znajdź ostatni wiersz, dobrze? Więc w przypadku tego obiektu określ położenie podziału strony, ustal wiersz. To jest właściwie pierwszy wiersz następnej strony, więc muszę odjąć 1 od tego, dobrze. A potem przyznaję, że jest to niesamowicie tanie, przejdź do kolumny 7, która jest kolumną G, zmień NumberFormat na walutę, tylko z tego wiersza. A następnie przejdź do kolumny 8, która jest H, zmień ją na procent i przejdź dalej.Na koniec zamknij podgląd poziomy lub podziału strony i wróć do normalnego widoku.
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.
Hej, chcę ci podziękować za zatrzymanie się. Do zobaczenia następnym razem z kolejnym netcastem od.
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2058.xlsm
