Bisakah Excel mencetak total berjalan di footer untuk setiap halaman? Ini bukan built-in, tetapi makro pendek akan menyelesaikan masalah.
Menonton video
- Sasaran: Mencetak kategori berjalan total dan% dari Kategori di bagian bawah setiap halaman yang dicetak
- Masalah: tidak ada antarmuka pengguna Excel yang dapat memberi tahu rumus bahwa Anda berada di bagian bawah halaman yang dicetak
- Ya, Anda dapat "melihat" pemisah halaman, tetapi rumus tidak dapat melihatnya
- Solusi yang memungkinkan: Gunakan makro
- Strategi: Tambahkan total lari dan% kategori untuk setiap baris. Sembunyikan di semua baris.
- Total 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) - Jika buku kerja Anda disimpan sebagai XLSX, lakukan Save As untuk menyimpan sebagai XLSM
- Jika Anda belum pernah menggunakan makro, ubah keamanan makro
- Jika Anda belum pernah menggunakan makro, Perlihatkan tab Pengembang
- Beralih ke VBA
- Sisipkan modul
- Ketik kodenya
- Tetapkan makro itu ke bentuk
- Saat ukuran halaman berubah, jalankan makro reset
Transkrip Video
Belajar Excel dari Podcast, Episode 2058: Menjalankan Total di Akhir Setiap Halaman
Hai, selamat datang kembali di netcast, saya Bill Jelen. Pertanyaan hari ini dikirim oleh Wiley: Wiley ingin menunjukkan total pendapatan berjalan dan persentase kategori di baris terakhir dari setiap halaman yang dicetak. Jadi, Wiley telah mencetak laporan di sini dengan berton-ton catatan, banyak halaman untuk setiap kategori di sana di Kolom A. Dan ketika kita turun ke akhir halaman cetak, Wiley mencari total di sini yang menunjukkan pendapatan total, menjalankan total dalam kategori ini dan kemudian persentase kategori. Jadi, Anda dapat melihat 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, total keseluruhan untuk kategori dan total keseluruhan 100%. Baiklah, dan ketika Wiley bertanya kepada saya tentang hal ini, saya seperti, "Oh tidak, kami tidak- ada 'Tidak ada cara di footer untuk memasukkan total berjalan. " Baiklah, jadi ini memang cheat murahan yang mengerikan dan saya mendorong siapa saja yang menonton ini di YouTube, jika Anda memiliki cara yang lebih baik, silakan sebutkan di komentar, oke? Jadi, ide saya ada di Kolom G dan H, untuk menyembunyikan total berjalan dan persentase kategori di setiap baris. Baiklah, lalu kita menggunakan makro untuk mendeteksi apakah kita berada di akhir halaman.kembali di akhir halaman.kembali di akhir halaman.
Baiklah, jadi dua rumus yang kita inginkan di sini mengatakan, hei, jika kategori ini sama dengan kategori sebelumnya. Jadi jika A6 = A5 maka ambil SUM dari pendapatan ini, jadi itu di F6 dan total berjalan sebelumnya ada di G5. Sekarang, karena saya menggunakan fungsi SUM di sini, ini tidak akan menghasilkan kesalahan jika kita akan mencoba dan menambahkan total yang berjalan. Jika tidak, kita hanya akan berada di kategori baru jadi ketika kita beralih dari A ke B, kita hanya akan mengambil JUMLAH nilai di sebelah kiri kita, yang bisa saja saya taruh F6 di sana. Tapi di sinilah kita, Anda tahu, sudah terlambat. Dan persentase kategori, yang satu ini akan menjadi sangat tidak efisien. Kami mengambil pendapatan di baris ini dibagi dengan SUM dari semua pendapatan di mana kategorinya sama dengan A6. Jadi ini semua kategorinya,ini adalah kategori di baris ini dan kemudian menambahkan sel yang sesuai dari semua baris. Tentu saja, $ tanda - 1, 2, 3, 4 $ tanda di sana. Tidak ada $ masuk di A6 dan $ 4 masuk di sana. Baiklah, dan kami akan memperlihatkan angka ini sebagai Angka, mungkin 1000 pemisah, klik OK, lalu di sini sebagai persentase dengan satu tempat desimal seperti itu. Baiklah, dan kami akan menyalin rumus ini ke semua sel. BAM, seperti itu, baiklah. Tapi sekarang tujuan di sini adalah untuk memastikan bahwa kita hanya melihat total itu ketika kita sampai ke jeda halaman. Baiklah, itu di sana. Itu adalah jeda halaman otomatis dan kemudian saat kita beralih dari ujung A ke B, hentian halaman manual. Jadi, hentian halaman manual di sini berbeda dari hentian halaman otomatis.dan kami akan menampilkan angka ini sebagai Angka, mungkin 1000 pemisah, klik OK, lalu di sini sebagai persentase dengan satu tempat desimal seperti itu. Baiklah, dan kami akan menyalin rumus ini ke semua sel. BAM, seperti itu, baiklah. Tetapi sekarang tujuan di sini adalah untuk memastikan bahwa kita hanya melihat totalnya ketika kita sampai pada jeda halaman. Baiklah, itu di sana. Itu adalah jeda halaman otomatis dan kemudian saat kita beralih dari ujung A ke B, hentian halaman manual. Jadi, hentian halaman manual di sini berbeda dari hentian halaman otomatis.dan kami akan menampilkan angka ini sebagai Angka, mungkin 1000 pemisah, klik OK, lalu di sini sebagai persentase dengan satu tempat desimal seperti itu. Baiklah, dan kami akan menyalin rumus ini ke semua sel. BAM, seperti itu, baiklah. Tetapi sekarang tujuan di sini adalah untuk memastikan bahwa kita hanya melihat total tersebut ketika kita sampai ke jeda halaman. Baiklah, itu di sana. Itu adalah jeda halaman otomatis dan kemudian saat kita beralih dari ujung A ke B, hentian halaman manual. Jadi, hentian halaman manual di sini berbeda dari hentian halaman otomatis.Tetapi sekarang tujuan di sini adalah untuk memastikan bahwa kita hanya melihat totalnya ketika kita sampai pada jeda halaman. Baiklah, itu di sana. Itu adalah jeda halaman otomatis dan kemudian saat kita beralih dari ujung A ke B, hentian halaman manual. Jadi, hentian halaman manual di sini berbeda dari hentian halaman otomatis.Tetapi sekarang tujuan di sini adalah untuk memastikan bahwa kita hanya melihat total tersebut ketika kita sampai ke jeda halaman. Baiklah, itu di sana. Itu adalah hentian halaman otomatis dan kemudian saat kita beralih dari ujung A ke B, hentian halaman manual. Jadi, hentian halaman manual di sini berbeda dari hentian halaman otomatis.
Baiklah, sekarang Anda akan melihat di sini bahwa file ini disimpan sebagai file XLSX karena begitulah cara Excel menyimpan file. XLSX adalah jenis file rusak yang tidak mengizinkan makro, bukan? Jenis file terburuk di dunia. Jadi, jangan lewatkan langkah ini atau ini. Semua pekerjaan Anda dari sini dan keluar akan hilang. Simpan Sebagai, dan kami akan menyimpan bukan sebagai buku kerja Excel tetapi sebagai Buku Kerja dengan Makro atau sebagai Buku Kerja Biner atau sebagai XLS. Saya akan pergi dengan Buku Kerja Macro-Enabled. Jika Anda tidak melakukan langkah itu, Anda akan kehilangan sisa pekerjaan yang Anda lakukan. Baiklah, dan kemudian, jika Anda belum pernah menjalankan makro sebelumnya, kita akan mengklik kanan dan mengucapkan Kustomisasi Pita. Di sini, di sisi kanan, pilih kotak untuk Pengembang, yang akan memberi Anda tab Pengembang. Setelah Anda memiliki tab Pengembang, kita dapat pergi ke Keamanan Makro,secara default akan ada di sini Nonaktifkan semua makro, dan jangan beri tahu saya bahwa Anda telah menonaktifkan seluruh makro. Anda ingin beralih ke yang kedua, dengan begitu saat kita membuka file, kita akan berkata, “Hei, ada makro di sini. Apakah Anda membuat ini? Apakah kamu baik-baik saja dengan ini? ” Dan Anda bisa berkata, Aktifkan makro. Oke, klik Oke.
Sekarang, kita akan beralih ke editor visual basic. Jika Anda belum pernah menggunakan visual basic sebelumnya, Anda akan mulai dengan layar abu-abu ini, buka View dan Project Explorer. Berikut adalah daftar semua workbook terbuka. Jadi saya memiliki Add-in Solver, buku kerja makro pribadi saya, dan inilah buku kerja yang sedang saya kerjakan. Pastikan buku kerja ini dipilih, lakukan Sisipkan, Modul. Sisipkan, Modul akan mendapatkan kanvas putih kosong besar yang bagus di sini. Baiklah, lalu Anda akan mengetikkan kode ini. Baiklah sekarang, kami menggunakan objek di sini bernama HPageBreak, hentian halaman horizontal. Dan karena saya tidak banyak menggunakan ini, saya harus mendeklarasikannya di sini sebagai variabel, sebagai objek HPB, dengan cara itu saya akan dapat melihat pilihan yang tersedia bagi saya di masing-masing. Baik,mencari tahu di mana baris terakhir dengan data hari ini jadi saya menggunakan Kolom A, saya akan ke akhir Kolom A - A1048576. Ini adalah L di sini dan bukan 1, ini adalah L. Semua orang mengencangkannya. L seperti di Excel. Kedengarannya seperti Excel. Mendapatkan? Unggul. Jadi, pergi ke A1048576, tekan tombol Putus, dan tombol Panah Atas untuk sampai ke baris terakhir. Cari tahu baris apa itu. Lalu di Kolom G dan H, dan jika Anda menonton ini, Anda perlu melihat data Excel Anda dan mencari tahu di mana dua kolom baru Anda, oke. Saya tidak tahu berapa banyak kolom yang Anda miliki. Mungkin kolom baru Anda ada di I dan J, atau mungkin di C dan D. Saya tidak tahu, cari tahu di mana itu dan kita akan menyembunyikan semua baris itu, oke. Jadi dalam kasus saya, itu dimulai dari G6, itulah tempat pertama kami memiliki nomor,:H dan kemudian saya menggabungkan baris terakhir yang kita miliki hari ini menggunakan format angka tiga titik koma yang akan menyembunyikan data.
Baiklah, selanjutnya yang ini, saya mempelajari yang berikutnya dari papan pesan. Jika Anda tidak meletakkan jendela aktif dalam mode Pratinjau Hentian Halaman sebelum Anda menjalankan kode ini, kode ini tidak akan berfungsi. Ini berfungsi untuk beberapa hentian halaman tetapi tidak semua Hentian halaman, jadi Anda harus menampilkan Hentian halaman untuk sementara. Dan kemudian loop di sini: Untuk Setiap, ini adalah variabel objek saya- HPB Di ActiveSheet.HPageBreaks. Cari tahu baris terakhir, oke? Jadi untuk objek ini, untuk hentian halaman, cari tahu lokasinya, cari tahu barisnya. Dan ini sebenarnya baris pertama dari halaman berikutnya jadi saya harus mengurangi 1 dari itu, oke. Dan kemudian di sini, saya akui ini sangat murah, pergi ke Kolom 7 yang merupakan Kolom G, ubah NumberFormat menjadi mata uang, hanya dari baris itu. Dan kemudian pergi ke Kolom 8 yang merupakan H dan mengubahnya menjadi persentase dan lanjutkan berikutnya.Terakhir, keluar horizontal atau pratinjau hentian halaman dan kembali ke tampilan normal.
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 sudah mampir. Sampai jumpa di lain waktu untuk netcast lain dari.
Unduh berkas
Unduh file contoh di sini: Podcast2058.xlsm
