Bolehkah Excel mencetak jumlah berjalan di footer untuk setiap halaman? Ia tidak terbina dalam, tetapi makro pendek akan menyelesaikan masalahnya.
Tonton Video
- Matlamat: Jumlah kategori cetakan berjalan dan% Kategori di bahagian bawah setiap halaman bercetak
- Masalah: tiada apa-apa di antara muka pengguna Excel yang dapat memberitahu formula bahawa anda berada di bahagian bawah halaman bercetak
- Ya, anda dapat "melihat" jeda halaman, tetapi formula tidak dapat melihatnya
- Penyelesaian yang mungkin: Gunakan makro
- Strategi: Tambahkan jumlah dan% kategori yang berjalan untuk setiap baris. Sembunyikan pada semua baris.
- Jumlah Berjalan untuk Formula Kategori:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - % Formula Kategori:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Sekiranya buku kerja anda disimpan sebagai XLSX, lakukan Save As untuk disimpan sebagai XLSM
- Sekiranya anda tidak pernah menggunakan makro, ubah keselamatan makro
- Sekiranya anda tidak pernah menggunakan makro, tunjukkan tab Pembangun
- Tukar ke VBA
- Masukkan modul
- Taipkan kodnya
- Tetapkan makro itu ke bentuk
- Semasa saiz halaman berubah, jalankan makro tetapan semula
Transkrip Video
Belajar Excel dari Podcast, Episode 2058: Jumlah Berjalan di Akhir Setiap Halaman
Hei, selamat datang kembali ke netcast, saya Bill Jelen. Soalan hari ini yang dihantar oleh Wiley: Wiley ingin menunjukkan jumlah pendapatan dan peratusan kategori yang berjalan dalam baris terakhir setiap halaman yang dicetak. Oleh itu, Wiley telah mencetak laporan di sini dengan banyak catatan, banyak halaman untuk setiap kategori di sana di Lajur A. Dan ketika kita sampai ke akhir halaman cetak, Wiley mencari jumlah di sini yang menunjukkan jumlah pendapatan, jumlah keseluruhan dalam kategori ini dan kemudian peratusan kategori. Oleh itu, anda dapat melihat bahawa kami berada di 9.7% di sana, ketika saya pergi ke halaman 2 - 21.1, halaman 3 - 33.3 dan seterusnya. Dan pada jeda halaman di mana kita selesai dengan kategori A, jumlah keseluruhan untuk kategori dan jumlah keseluruhan 100%. Baiklah, dan ketika Wiley bertanya tentang ini, saya seperti, "Oh tidak, kami tidak- di sana 'tidak ada cara di footer untuk meletakkan jumlah berjalan. " Baiklah, jadi ini diakui sebagai penipu murah yang mengerikan dan saya mendorong sesiapa sahaja yang menontonnya di YouTube, jika anda mempunyai cara yang lebih baik, sila nyatakan itu dalam komen, baiklah? Oleh itu, idea saya hanya terdapat di Lajur G dan H, untuk menyembunyikan jumlah berjalan dan peratusan kategori dalam setiap baris. Baiklah, dan kemudian kita menggunakan makro untuk mengesan sama ada kita berada di hujung halaman.kembali di hujung halaman.kembali di hujung halaman.
Baiklah, jadi dua formula yang kita mahu di sini katakan, hei, jika kategori ini sama dengan kategori sebelumnya. Jadi jika A6 = A5 maka ambil SUM dari hasil ini, jadi itu adalah di F6 dan jumlah sebelumnya berjalan di sana di G5. Sekarang, kerana saya menggunakan fungsi SUM di sini, ini tidak salah sekiranya kita pernah mencuba dan menambahkan jumlah berjalan. Jika tidak, kita hanya akan berada di kategori yang baru jadi apabila kita beralih dari A ke B, kita hanya akan mengambil SUM dari nilai di sebelah kiri kita, yang mana saya boleh meletakkan F6 di sana. Tetapi di sini kita sudah terlambat. Dan peratusan kategori, yang satu ini akan menjadi sangat tidak cekap. Kami mengambil pendapatan pada baris ini dibahagi dengan SUM dari semua hasil di mana kategorinya sama dengan A6. Jadi ini semua kategori,ini adalah kategori dalam baris ini dan kemudian tambahkan sel yang sesuai dari semua baris. Sudah tentu, tanda $ - 1, 2, 3, 4 $ tanda di sana. Tiada tanda $ di A6 dan tanda $ 4 di sana. Baiklah, dan kami akan menunjukkan nombor ini sebagai Nombor, mungkin 1000 pemisah, klik OK, dan kemudian di sini sebagai peratusan dengan satu perpuluhan seperti itu. Baiklah, dan kami akan menyalin formula ini ke semua sel. BAM, seperti itu, baiklah. Tetapi sekarang tujuannya adalah untuk memastikan bahawa kita hanya melihat jumlah itu ketika kita sampai ke halaman rehat. Baiklah, ada di sana. Itulah jeda halaman automatik dan kemudian kemudian apabila kita beralih dari akhir A ke B, jeda halaman manual. Jadi, jeda halaman manual ini di sini berbeza daripada jeda halaman automatik.dan kami akan menunjukkan nombor ini sebagai Nombor, mungkin 1000 pemisah, klik OK, dan kemudian di sini sebagai peratusan dengan satu perpuluhan seperti itu. Baiklah, dan kami akan menyalin formula ini ke semua sel. BAM, seperti itu, baiklah. Tetapi sekarang tujuannya adalah untuk memastikan bahawa kita hanya melihat jumlah itu ketika kita sampai ke halaman rehat. Baiklah, ada di sana. Itulah jeda halaman automatik dan kemudian kemudian apabila kita beralih dari akhir A ke B, jeda halaman manual. Jadi, jeda halaman manual di sini berbeza daripada jeda halaman automatikdan kami akan menunjukkan nombor ini sebagai Nombor, mungkin 1000 pemisah, klik OK, dan kemudian di sini sebagai peratusan dengan satu perpuluhan seperti itu. Baiklah, dan kami akan menyalin formula ini ke semua sel. BAM, seperti itu, baiklah. Tetapi sekarang tujuannya adalah untuk memastikan bahawa kita hanya melihat jumlah itu ketika kita sampai ke halaman rehat. Baiklah, ada di sana. Itulah jeda halaman automatik dan kemudian kemudian apabila kita beralih dari akhir A ke B, jeda halaman manual. Jadi, jeda halaman manual ini di sini berbeza daripada jeda halaman automatik.Tetapi sekarang tujuannya adalah untuk memastikan bahawa kita hanya melihat jumlah itu ketika kita sampai ke halaman rehat. Baiklah, ada di sana. Itulah jeda halaman automatik dan kemudian kemudian apabila kita beralih dari akhir A ke B, jeda halaman manual. Jadi, jeda halaman manual ini di sini berbeza daripada jeda halaman automatik.Tetapi sekarang tujuannya adalah untuk memastikan bahawa kita hanya melihat jumlah itu ketika kita sampai ke halaman rehat. Baiklah, ada di sana. Itulah jeda halaman automatik dan kemudian kemudian apabila kita beralih dari akhir A ke B, jeda halaman manual. Jadi, jeda halaman manual di sini berbeza daripada jeda halaman automatik
Baiklah, sekarang anda akan melihat di sini bahawa fail ini disimpan sebagai fail XLSX kerana itulah cara Excel mahu menyimpan fail. XLSX adalah jenis fail yang rosak yang tidak membenarkan makro, bukan? Jenis fail terburuk di dunia. Jadi, jangan melangkau langkah ini atau ini. Semua kerja anda dari sana sini akan hilang. Simpan Sebagai, dan kami akan menyimpan bukan sebagai buku kerja Excel tetapi sebagai Buku Kerja yang Diaktifkan Makro atau sebagai Buku Kerja Binari atau sebagai XLS. Saya akan menggunakan Buku Kerja Makro-Enabled. Sekiranya anda tidak melakukan langkah itu, anda akan kehilangan sisa pekerjaan yang anda lakukan. Baiklah, dan jika anda tidak pernah menjalankan makro sebelumnya, kita akan mengklik kanan dan mengatakan Sesuaikan Pita. Di sini di sebelah kanan, pilih kotak untuk Pembangun, yang akan memberi anda tab Pembangun. Sebaik sahaja anda mempunyai tab Pembangun, kita boleh pergi ke Macro Security,secara lalai ia akan berada di sini Lumpuhkan semua makro, dan jangan bilang bahawa anda telah melumpuhkan keseluruhan makro. Anda mahu beralih ke yang kedua, dengan cara itu ketika kita membuka fail, kita akan berkata, "Hei, ada makro di sini. Adakah anda membuat ini? Adakah anda baik-baik saja dengan ini? " Dan anda boleh mengatakan, Dayakan makro. Baiklah, klik OK.
Sekarang, kita akan beralih ke editor asas visual. Sekiranya anda tidak pernah menggunakan asas visual sebelumnya, anda akan memulakan dengan skrin kelabu sepenuhnya ini, pergi ke View dan Project Explorer. Berikut adalah senarai semua buku kerja yang terbuka. Oleh itu, saya mempunyai Solver Add-in, buku kerja makro peribadi saya dan inilah buku kerja yang sedang saya jalankan. Pastikan buku kerja ini dipilih, lakukan Insert, Module. Masukkan, Modul akan mendapat kanvas putih kosong yang besar di sini. Baiklah, dan kemudian anda akan memasukkan kod ini. Baiklah sekarang, kami menggunakan objek di sini yang disebut HPageBreak, pecahan halaman mendatar. Dan kerana saya tidak banyak menggunakan ini, saya harus menyatakannya di sini sebagai pemboleh ubah, sebagai objek HPB, dengan cara itu saya dapat melihat pilihan yang tersedia untuk saya masing-masing. Baiklah,cari di mana baris terakhir dengan data hari ini jadi saya menggunakan Lajur A, saya akan sampai ke akhir Lajur A - A1048576. Ini adalah L di sini dan bukan 1, ini adalah L. Semua orang mengacaukannya. Seperti dalam Excel. Kedengarannya seperti Excel. Dapatkannya? Excel ke atas. Jadi, pergi ke A1048576, tekan kekunci Tamat, dan kekunci Anak panah Atas untuk sampai ke baris terakhir. Cari baris apa itu. Dan kemudian di Lajur G dan H, dan jika anda menonton ini, anda perlu melihat data Excel anda dan mengetahui di mana dua lajur baru anda, baiklah. Saya tidak tahu berapa banyak ruangan yang anda ada. Mungkin lajur baru anda berakhir di I dan J, atau mungkin di C dan D. Saya tidak tahu, cari tahu di mana mereka berada dan kita akan menyembunyikan semua baris itu, baiklah. Jadi dalam kes saya, ia bermula dari G6, itulah tempat pertama di mana kami mempunyai nombor,:H dan kemudian saya menyambung baris terakhir yang kita ada hari ini menggunakan format nombor tiga titik koma yang akan menyembunyikan data.
Baiklah, selepas ini, saya belajar yang seterusnya dari papan mesej. Sekiranya anda tidak meletakkan tetingkap aktif dalam mod Pratonton Pecah Halaman sebelum anda menjalankan kod ini, kod ini tidak akan berfungsi. Ia berfungsi untuk beberapa jeda halaman tetapi tidak semua jeda halaman, jadi anda harus memaparkan jeda halaman buat sementara waktu. Dan kemudian gelung di sini: Untuk Masing-masing, ini adalah pemboleh ubah objek saya- HPB In ActiveSheet.HPageBreaks. Cari baris terakhir, baiklah? Jadi untuk objek ini, untuk jeda halaman, cari lokasi, cari baris. Dan ini sebenarnya baris pertama halaman seterusnya jadi saya mesti tolak 1 dari itu, baiklah. Dan di sini, saya akui ini sangat murah, pergi ke Lajur 7 yang merupakan Lajur G, ubah NomborFormat menjadi mata wang, begitu sahaja. Dan kemudian pergi ke Lajur 8 yang merupakan H dan ubahnya menjadi peratusan dan seterusnya.Akhirnya, keluar mendatar atau pratonton jeda halaman dan kembali ke paparan biasa.
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.
Oh hei, saya ingin mengucapkan terima kasih kerana berhenti. Kami akan berjumpa anda di lain kali untuk siaran net lain dari.
Muat turun fail
Muat turun fail sampel di sini: Podcast2058.xlsm
