Excel, her sayfanın altbilgisine değişen bir toplam yazdırabilir mi? Yerleşik değildir, ancak kısa bir makro sorunu çözecektir.
Video izle
- Hedef: Toplamda çalışan kategoriyi ve yazdırılan her sayfanın altına Kategori yüzdesini yazdırın
- Sorun: Excel kullanıcı arabirimindeki hiçbir şey, formüle yazdırılan bir sayfanın altında olduğunuzu bildiremez
- Evet, sayfa sonlarını "görebilirsiniz", ancak formüller onları göremez
- Olası çözüm: Bir makro kullanın
- Strateji: Her satır için değişen toplamı ve kategori yüzdesini ekleyin. Tüm satırlarda gizle.
- Kategori Formülü için Toplam Koşu:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - Kategori Formülü Yüzdesi:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - Çalışma kitabınız XLSX olarak kaydedilmişse, XLSM olarak kaydetmek için bir Farklı Kaydet yapın
- Hiç makro kullanmadıysanız, makro güvenliğini değiştirin
- Hiç makro kullanmadıysanız, Geliştirici sekmesini göster
- VBA'ya geç
- Bir modül ekle
- Kodu yazın
- Bu makroyu bir şekle atayın
- Sayfa boyutu değiştikçe, sıfırlama makrosunu çalıştırın
Video Metni
Podcast'ten Excel Öğrenin, Bölüm 2058: Her Sayfanın Sonunda Toplam Çalıştırma
Hey, İnternet'e tekrar hoş geldiniz, ben Bill Jelen. Wiley tarafından gönderilen bugünün sorusu: Wiley, yazdırılan her sayfanın son satırında değişen toplam gelir ve kategori yüzdesini göstermek istiyor. Wiley burada tonlarca kayıt içeren raporlar yazdırdı, her kategori için birden fazla sayfa Sütun A'da. Ve yazdırılan sayfanın sonuna geldiğimizde, Wiley burada toplam geliri gösteren bir toplam arıyor. bu kategorideki toplam çalışan ve ardından kategorinin yüzdesi. Ve böylece, sayfa 2 - 21.1, sayfa 3 - 33.3'e gittiğimde orada% 9.7'de olduğumuzu görebilirsiniz. Ve kategori A ile işimizi bitirdiğimiz sayfa sonunda, kategori için genel toplam ve genel toplam% 100. Pekala, Wiley bana bunu sorduğunda, "Oh hayır, biz orada değiliz 'Altbilgiye değişen toplam koymanın yolu yok. " Pekala, bu kuşkusuz korkunç ve ucuz bir hile ve bunu YouTube'da izleyen herkesi teşvik ediyorum, eğer daha iyi bir yolunuz varsa, lütfen bunu yorumlarda belirtin, tamam mı? Ve benim fikrim G ve H Sütunlarında, her bir satırdaki değişen toplamı ve kategori yüzdesini gizlemek. Pekala, sonra sayfanın sonunda olup olmadığımızı tespit etmek için bir makro kullanıyoruz.yeniden sayfanın sonunda.yeniden sayfanın sonunda.
Pekala, burada istediğimiz iki formül, hey, bu kategori bir önceki kategoriye eşitse. Yani A6 = A5 ise, bu gelirin TOPLAMINI alın, yani bu F6'da ve oradaki bir önceki hareketli toplam G5'te. Şimdi, burada TOPLA işlevini kullandığım için, çalışan toplamı eklemeye çalışırsak bu hata vermez. Aksi takdirde, sadece yepyeni bir kategoride olacağız, bu yüzden A'dan B'ye geçtiğimizde, değerin TOPLAMINI sol tarafımıza alacağız, ki bunu F6'yı oraya koyabilirdim. Ama işte buradayız, biliyorsun, çok geç. Ve sonra kategori yüzdesi, bu korkunç derecede verimsiz olacak. Bu satırdaki geliri, kategorinin A6'ya eşit olduğu tüm gelirlerin TOPLAMI'na bölünmesiyle alıyoruz. Yani bunlar tüm kategoriler,bu, bu satırdaki kategoridir ve ardından tüm satırlardan ilgili hücreyi ekleyin. Tabii ki, $ işaretleri - 1, 2, 3, 4 $ işaretleri var. A6'da $ işareti ve orada 4 $ işareti yok. Pekala, bu sayıyı bir Sayı olarak göstereceğiz, belki 1000 ayırıcı, Tamam'a tıklayın ve sonra burada bunun gibi tek ondalık basamaklı bir yüzde olarak. Pekala, bu formülü tüm hücrelere kopyalayacağız. BAM, bunun gibi, pekala. Ancak şimdi buradaki amaç, bu toplamları yalnızca sayfa sonuna geldiğimizde gördüğümüzden emin olmaktır. Pekala, işte orada. Bu otomatik bir sayfa sonu ve daha sonra A'nın sonundan B'ye geçiş yaptığımızda, manuel bir sayfa sonu. Dolayısıyla, buradaki bu kılavuz sayfa sonu, otomatik bir sayfa sonundan farklıdır.ve bu sayıyı bir Sayı olarak, belki 1000 ayırıcı olarak göstereceğiz, Tamam'a tıklayın ve sonra burada, bunun gibi bir ondalık basamağa sahip bir yüzde olarak. Pekala, bu formülü tüm hücrelere kopyalayacağız. BAM, bunun gibi, pekala. Ancak şimdi buradaki amaç, bu toplamları yalnızca sayfa sonuna geldiğimizde gördüğümüzden emin olmaktır. Pekala, işte orada. Bu otomatik bir sayfa sonu ve daha sonra A'nın sonundan B'ye geçiş yaptığımızda, manuel bir sayfa sonu. Dolayısıyla, buradaki bu kılavuz sayfa sonu, otomatik bir sayfa sonundan farklıdır.ve bu sayıyı bir Sayı olarak, belki 1000 ayırıcı olarak göstereceğiz, Tamam'a tıklayın ve sonra burada bunun gibi bir ondalık basamaklı yüzde olarak. Pekala, bu formülü tüm hücrelere kopyalayacağız. BAM, bunun gibi, pekala. Ancak şimdi buradaki amaç, bu toplamları yalnızca sayfa sonuna geldiğimizde gördüğümüzden emin olmaktır. Pekala, işte orada. Bu otomatik bir sayfa sonu ve daha sonra A'nın sonundan B'ye geçiş yaptığımızda, manuel bir sayfa sonu. Dolayısıyla, buradaki bu kılavuz sayfa sonu, otomatik bir sayfa sonundan farklıdır.Ancak şimdi buradaki amaç, bu toplamları yalnızca sayfa sonuna geldiğimizde gördüğümüzden emin olmaktır. Pekala, işte orada. Bu otomatik bir sayfa sonu ve daha sonra A'nın sonundan B'ye geçiş yaptığımızda, manuel bir sayfa sonu. Dolayısıyla, buradaki bu kılavuz sayfa sonu, otomatik bir sayfa sonundan farklıdır.Ancak şimdi buradaki amaç, bu toplamları yalnızca sayfa sonuna geldiğimizde gördüğümüzden emin olmaktır. Pekala, işte orada. Bu otomatik bir sayfa sonu ve daha sonra A'nın sonundan B'ye geçiş yaptığımızda, manuel bir sayfa sonu. Dolayısıyla, buradaki bu kılavuz sayfa sonu, otomatik bir sayfa sonundan farklıdır.
Pekala, şimdi burada bu dosyanın XLSX dosyası olarak kaydedildiğini fark edeceksiniz çünkü Excel bu şekilde dosyaları kaydetmek istiyor. XLSX, makrolara izin vermeyen bozuk dosya türüdür, değil mi? Dünyadaki en kötü dosya türü. Yani, bu adımı ya da bu adımı atlamayın. Buradan ve dışarıdaki tüm çalışmalarınız kaybolacak. Farklı Kaydet ve Excel çalışma kitabı olarak değil, Makro Etkin Çalışma Kitabı veya İkili Çalışma Kitabı veya XLS olarak kaydedeceğiz. Makro Etkin Çalışma Kitabı ile devam edeceğim. Bu adımı atmazsanız, yaptığınız işin geri kalanını kaybetmek üzeresiniz. Pekala, ve sonra, daha önce hiç makro çalıştırmadıysanız, sağ tıklayıp Şeridi Özelleştir diyeceğiz. Burada, sağ tarafta, size bir Geliştirici sekmesi sağlayacak Geliştirici kutusunu seçin. Geliştirici sekmesine sahip olduğunuzda, Makro Güvenlik'e gidebiliriz,varsayılan olarak burada yukarıda olacak Tüm makroları devre dışı bırakın ve bana tüm makroları devre dışı bıraktığınızı söylemeyin. İkincisine geçmek istiyorsunuz, bu şekilde dosyayı açtığımızda, "Hey, burada makrolar var. Bunları sen mi yarattın? Bununla iyi misin? " Makroları etkinleştir diyebilirsiniz. Pekala, Tamam'ı tıklayın.
Şimdi, görsel temel düzenleyiciye geçeceğiz. Daha önce hiç görsel temel kullanmadıysanız, bu tamamen gri ekranla başlayacaksınız, Görünüm ve Proje Gezgini'ne gidin. İşte tüm açık çalışma kitaplarının bir listesi. Bu yüzden Çözücü Eklentisine sahibim, kişisel makro çalışma kitabım ve işte üzerinde çalıştığım çalışma kitabı. Bu çalışma kitabının seçildiğinden emin olun, Ekle, Modül yapın. Ekle, Modül burada güzel, boş, beyaz bir tuval alacak. Pekala, o zaman bu kodu yazacaksınız. Pekala şimdi, burada yatay sayfa sonu olan HPageBreak adlı bir nesne kullanıyoruz. Ve bunu çok kullanmadığım için, onu burada bir değişken, bir HPB nesnesi olarak ilan etmek zorunda kaldım, böylece her birinde benim için mevcut olan seçenekleri görebilirdim. Peki,Verilerin bulunduğu son satırın bugün nerede olduğunu bulduğum için Sütun A'yı kullanıyorum, Sütun A - A1048576'nın sonuna gidiyorum. Bu burada bir L ve 1 değil, bu bir L. Herkes batırıyor. Excel'deki gibi L. Excel'e benziyor. Anla? Excel yukarı. Öyleyse, son satıra gitmek için A1048576'ya gidin, End tuşuna ve Yukarı Ok tuşuna basın. Hangi satırın olduğunu bul. Ve sonra G ve H Sütunlarında ve bunu izliyorsanız, Excel verilerinize bir göz atmanız ve iki yeni sütununuzun nerede olduğunu bulmanız gerekir, pekala. Kaç tane sütununuz olduğunu bilmiyorum. Belki yeni sütunlarınız I ve J'de bitmiştir, veya belki C ve D'dedirler. Bilmiyorum, bunların nerede olduğunu çözelim ve tüm bu satırları gizleyeceğiz, pekala. Benim durumumda, G6'dan başlıyordu, bir numaramızın olduğu ilk yer burası:H ve sonra bugün sahip olduğumuz son satırı, verileri gizleyecek üç noktalı virgülden oluşan bir sayı biçimi kullanarak birleştiriyorum.
Pekala, o zaman bir sonraki, bunu mesaj panosundan öğrendim. Bu kodu çalıştırmadan önce etkin pencereyi Sayfa Sonu Önizleme moduna getirmezseniz, bu kod çalışmayacaktır. Bazı sayfa sonları için çalışır ancak tüm sayfa sonları için işe yaramaz, bu nedenle sayfa sonlarını geçici olarak görüntülemeniz gerekir. Ve sonra burada bir döngü: Her biri için, bu benim nesne değişkenim - ActiveSheet.HPageBreaks'de HPB. Son sırayı çöz, tamam mı? Yani bu nesne için, sayfa sonu için, konumu ve satırı bulun. Ve bu aslında bir sonraki sayfanın ilk satırı, bu yüzden ondan 1 çıkarmalıyım, pekala. Ve sonra burada, bunun inanılmaz derecede ucuz olduğunu kabul ediyorum, Sütun G olan Sütun 7'ye gidin, Sayı Biçimi'ni para birimi olarak değiştirin, sadece o satırın. Ve sonra H olan Sütun 8'e gidin ve bunu bir yüzdeyle değiştirin ve bir sonraki adıma geçin.Son olarak, yatay veya sayfa sonu önizlemesinden çıkın ve normal görünüme geri dönün.
Pekala, bu bizim kodumuz. Dosyalama, Kapatma ve Microsoft Excel'e Döneceğim. Bunu çalıştırmanın kolay bir yolunu istiyorum, bu yüzden Ekleyeceğim, buradan güzel bir şekil seçeceğim. Yuvarlatılmış bir dikdörtgen seçeceğim, sağda bir dikdörtgeni içine çizeceğim, Sayfa düzeni, Efektler'e gidip Office 2007 efektlerini seçeceğim. Ve sonra burada, Biçim sekmesinde buna biraz ışıltı katmanın güzel bir yolu var, tamam .
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 hey, uğradığın için teşekkür ederim. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2058.xlsm
