क्या एक्सेल प्रत्येक पृष्ठ के लिए पाद में कुल चल रहा है? यह बिल्ट-इन नहीं है, लेकिन शॉर्ट मैक्रो समस्या को हल करेगा।
वीडियो देखेंा
- लक्ष्य: प्रत्येक मुद्रित पृष्ठ के निचले भाग में श्रेणी का कुल और% चल रहा है
- समस्या: एक्सेल यूजर इंटरफेस में कुछ भी एक सूत्र को नहीं बता सकता है कि आप एक मुद्रित पृष्ठ के नीचे हैं
- हां, आप पृष्ठ को "देख" सकते हैं, लेकिन सूत्र उन्हें नहीं देख सकते
- संभव समाधान: एक मैक्रो का उपयोग करें
- रणनीति: प्रत्येक पंक्ति के लिए दौड़ने की कुल और% श्रेणी जोड़ें। सभी पंक्तियों पर छुपाएं।
- श्रेणी फॉर्मूला के लिए कुल रनिंग:
=IF(A6=A5,SUM(F6,G5),SUM(F6)) - श्रेणी फॉर्मूला का%:
=G6/SUMIF($A$6:$A$2844,A6,$F$6:$F$2844) - यदि आपकी कार्यपुस्तिका को XLSX के रूप में सहेजा गया है, तो XLSM के रूप में सहेजने के लिए Save As करें
- यदि आपने कभी मैक्रोज़ का उपयोग नहीं किया है, तो मैक्रो सुरक्षा बदलें
- यदि आपने कभी मैक्रोज़ का उपयोग नहीं किया है, तो डेवलपर टैब दिखाएं
- VBA पर स्विच करें
- एक मॉड्यूल डालें
- कोड टाइप करें
- उस मैक्रो को किसी आकृति में असाइन करें
- जैसे ही पृष्ठ का आकार बदलता है, रीसेट मैक्रो चलाएँ
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2058: प्रत्येक पृष्ठ के अंत में कुल रनिंग
अरे, नेटकास्ट में वापस स्वागत है, मैं बिल जेलन हूं। विली द्वारा भेजा गया आज का प्रश्न: विली प्रत्येक मुद्रित पृष्ठ की अंतिम पंक्ति में चल रहे कुल राजस्व और श्रेणी का प्रतिशत दिखाना चाहता है। इसलिए, विली ने टोंस और रिकॉर्ड्स के साथ यहां रिपोर्टें छापी हैं, कॉलम ए में प्रत्येक श्रेणी के लिए कई पृष्ठ हैं और जब हम प्रिंट पेज के अंत में उतरते हैं, तो विली यहां कुल राजस्व की तलाश में है जो कुल राजस्व दिखाता है, इस श्रेणी के अंदर कुल चल रहा है और फिर श्रेणी का प्रतिशत। और इसलिए, आप देख सकते हैं कि हम 9.7% पर हैं, जब मैं पेज 2 - 21.1, पेज 3 - 33.3 और इतने पर जाता हूं। और पृष्ठ विराम पर जहां हम श्रेणी ए के साथ संपन्न होते हैं, श्रेणी के लिए भव्य कुल और भव्य कुल 100%। ठीक है, और जब विली ने मुझसे इस बारे में पूछा, तो मैं ऐसा था, "ओह नहीं, हम नहीं- वहाँ 'पाद में कोई रास्ता नहीं चल रहा है। ठीक है, इसलिए यह वास्तव में एक भयानक सस्ता धोखा है और मैं YouTube पर इसे देखने वाले किसी भी व्यक्ति को प्रोत्साहित करता हूं, यदि आपके पास बेहतर तरीका है, तो कृपया सभी तरीकों से, टिप्पणी में उल्लेख करें? और इसलिए, मेरा विचार केवल कॉलम जी और एच में है, प्रत्येक एकल पंक्ति में चल रहे कुल और श्रेणी के प्रतिशत को छिपाने के लिए। ठीक है, और फिर हम यह पता लगाने के लिए एक मैक्रो का उपयोग करते हैं कि क्या हम पृष्ठ के अंत में हैं।पृष्ठ के अंत में पुनः।पृष्ठ के अंत में पुनः।
ठीक है, इसलिए दो सूत्र जो हम यहां चाहते हैं, कहते हैं, अरे, अगर यह श्रेणी पिछली श्रेणी के बराबर है। इसलिए यदि A6 = A5 इस राजस्व का SUM लेते हैं, तो यह F6 में है और पिछले कुल जी 5 में चल रहा है। अब, क्योंकि मैं यहाँ SUM फ़ंक्शन का उपयोग कर रहा हूं, यह त्रुटि नहीं है कि क्या हम कभी प्रयास करेंगे और कुल रनिंग जोड़ेंगे। अन्यथा, हम बस एक नई श्रेणी में जा रहे हैं, इसलिए जब हम ए से बी पर स्विच करते हैं, तो हम बस वैल्यू के एसयूएम को हमारे बाईं ओर ले जाएंगे, जिसे मैं सिर्फ एफ 6 में रख सकता था। लेकिन यहाँ हम हैं, आप जानते हैं, बहुत देर हो चुकी है। और फिर श्रेणी का प्रतिशत, यह एक बहुत ही अयोग्य होने जा रहा है। हम सभी राजस्व के SUM द्वारा विभाजित इस पंक्ति पर राजस्व लेते हैं जहां श्रेणी A6 के बराबर है। तो ये सभी श्रेणियां हैं,इस पंक्ति में यह श्रेणी है और फिर सभी पंक्तियों से संबंधित सेल जोड़ें। बेशक, $ संकेत - 1, 2, 3, 4 $ संकेत वहाँ। A6 में कोई $ संकेत नहीं है और वहां 4 $ संकेत हैं। ठीक है, और हम इस संख्या को एक संख्या के रूप में दिखाएंगे, शायद 1000 विभाजक, ठीक पर क्लिक करें, और फिर यहाँ एक दशमलव स्थान जैसे प्रतिशत के रूप में। ठीक है, और हम इस सूत्र को सभी कक्षों में कॉपी कर लेंगे। BAM, जैसा है, ठीक है। लेकिन अब यहाँ लक्ष्य यह सुनिश्चित करना है कि हम केवल उन योगों को देखें जब हम पेज को तोड़ते हैं। ठीक है, यह वहीं है। यह एक स्वचालित पेज ब्रेक है और फिर बाद में जब हम ए से बी के अंत में स्विच करते हैं, तो एक मैनुअल पेज ब्रेक। इसलिए, यह मैनुअल पेज ब्रेक यहां एक ऑटोमैटिक पेज ब्रेक से अलग है।और हम इस संख्या को एक संख्या के रूप में दिखाएंगे, शायद 1000 विभाजक, ठीक पर क्लिक करें, और फिर यहाँ एक दशमलव स्थान जैसे प्रतिशत के रूप में। ठीक है, और हम इस सूत्र को सभी कक्षों में कॉपी कर लेंगे। BAM, जैसा है, ठीक है। लेकिन अब यहाँ लक्ष्य यह सुनिश्चित करना है कि हम केवल उन योगों को देखें जब हम पेज को तोड़ते हैं। ठीक है, यह वहीं है। यह एक स्वचालित पेज ब्रेक है और फिर बाद में जब हम ए से बी के अंत में स्विच करते हैं, तो एक मैनुअल पेज ब्रेक। इसलिए, यह मैनुअल पेज ब्रेक यहां एक ऑटोमैटिक पेज ब्रेक से अलग है।और हम इस संख्या को एक संख्या के रूप में दिखाएंगे, शायद 1000 विभाजक, ठीक पर क्लिक करें, और फिर यहाँ एक दशमलव स्थान जैसे प्रतिशत के रूप में। ठीक है, और हम इस सूत्र को सभी कक्षों में कॉपी कर लेंगे। BAM, उस तरह, ठीक है। लेकिन अब यहाँ लक्ष्य यह सुनिश्चित करना है कि हम केवल उन योगों को देखें जब हम पेज को तोड़ते हैं। ठीक है, यह वहीं है। यह एक स्वचालित पेज ब्रेक है और फिर बाद में जब हम ए से बी के अंत में स्विच करते हैं, तो एक मैनुअल पेज ब्रेक। इसलिए, यह मैनुअल पेज ब्रेक यहां एक ऑटोमैटिक पेज ब्रेक से अलग है।लेकिन अब यहाँ लक्ष्य यह सुनिश्चित करना है कि हम केवल उन योगों को देखें जब हम पेज को तोड़ते हैं। ठीक है, यह वहीं है। यह एक स्वचालित पेज ब्रेक है और फिर बाद में जब हम ए से बी के अंत में स्विच करते हैं, तो एक मैनुअल पेज ब्रेक। इसलिए, यह मैनुअल पेज ब्रेक यहां एक ऑटोमैटिक पेज ब्रेक से अलग है।लेकिन अब यहाँ लक्ष्य यह सुनिश्चित करना है कि हम केवल उन योगों को देखें जब हम पेज को तोड़ते हैं। ठीक है, यह वहीं है। यह एक स्वचालित पेज ब्रेक है और फिर बाद में जब हम ए से बी के अंत में स्विच करते हैं, तो एक मैनुअल पेज ब्रेक। इसलिए, यह मैनुअल पेज ब्रेक यहां एक ऑटोमैटिक पेज ब्रेक से अलग है।
ठीक है, अब आप यहाँ देखेंगे कि यह फ़ाइल XLSX फ़ाइल के रूप में सहेजी गई है क्योंकि एक्सेल कैसे फ़ाइलों को सहेजना चाहता है। XLSX टूटी हुई फ़ाइल प्रकार है जो मैक्रोज़ की अनुमति नहीं देता है, है ना? दुनिया में सबसे खराब फ़ाइल प्रकार। तो, इस चरण को छोड़ें या इसे न करें। यहां और बाहर से आपके सभी काम खो जाएंगे। इस रूप में सहेजें, और हम एक्सेल वर्कबुक के रूप में नहीं बल्कि मैक्रो-एनेबल्ड वर्कबुक के रूप में या बाइनरी वर्कबुक के रूप में या एक्सएलएस के रूप में सहेजने जा रहे हैं। मैं मैक्रो-सक्षम वर्कबुक के साथ जाने वाला हूं। यदि आप वह कदम नहीं उठाते हैं, तो आप जो काम करते हैं, उसका बाकी हिस्सा खो देते हैं। ठीक है, और फिर, यदि आपने पहले कभी मैक्रोज़ नहीं चलाया है, तो हम राइट-क्लिक करने जा रहे हैं और रिबन को कस्टमाइज़ करें। यहां राइट-साइड में, डेवलपर के लिए बॉक्स चुनें, जिससे आपको एक डेवलपर टैब मिलेगा। आपके पास डेवलपर टैब होने के बाद, हम मैक्रो सिक्योरिटी में जा सकते हैं,डिफ़ॉल्ट रूप से यह यहाँ होने जा रहा है सभी मैक्रोज़ को अक्षम करें, और मुझे यह न बताएं कि आपने पूरे मैक्रोज़ को अक्षम कर दिया है। आप दूसरे को स्विच करना चाहते हैं, इस तरह जब हम फ़ाइल खोलते हैं, तो हम कहेंगे, "अरे, यहाँ मैक्रोज़ है। क्या आपने इन्हें बनाया? क्या आप इसके साथ ठीक हैं? ” और आप कह सकते हैं, मैक्रो को सक्षम करें। ठीक है, ठीक क्लिक करें।
अब, हम विज़ुअल बेसिक एडिटर पर स्विच करने जा रहे हैं। यदि आपने पहले कभी किसी विज़ुअल बेसिक का उपयोग नहीं किया है, तो आप इस पूरी तरह से ग्रे स्क्रीन के साथ शुरू करने जा रहे हैं, व्यू और प्रोजेक्ट एक्सप्लोरर पर जाएं। यहां सभी खुली कार्यपुस्तिकाओं की एक सूची दी गई है। इसलिए मेरे पास सॉल्वर ऐड-इन, मेरी व्यक्तिगत मैक्रो वर्कबुक और यहां वह वर्कबुक है जिस पर मैं काम कर रहा हूं। सुनिश्चित करें कि यह कार्यपुस्तिका चयनित है, सम्मिलित करें, मॉड्यूल करें। सम्मिलित करें, मॉड्यूल को यहां एक अच्छा बड़ा खाली, सफेद कैनवास मिलेगा। ठीक है, और फिर आप इस कोड में टाइप करने जा रहे हैं। अब ठीक है, हम यहां एक ऑब्जेक्ट का उपयोग कर रहे हैं जिसे HPageBreak कहा जाता है, एक क्षैतिज पृष्ठ विराम। और क्योंकि मैं इसका बहुत अधिक उपयोग नहीं करता, इसलिए मुझे इसे यहां एक चर के रूप में घोषित करना था, एक वस्तु एचपीबी के रूप में, इस तरह मैं उन विकल्पों को देख पाऊंगा जो मुझे हर एक में उपलब्ध हैं। ठीक है,पता करें कि डेटा के साथ अंतिम पंक्ति आज कहां है इसलिए मैं कॉलम A का उपयोग कर रहा हूं, मैं कॉलम A - A1048576 के अंत में जा रहा हूं। यह यहाँ एक L है और 1 नहीं है। यह L है। सभी पर शिकंजा कसा हुआ है। एक्सेल में एल। यह एक्सेल की तरह लगता है। उसे ले लो? एक्सेल। तो, A1048576 पर जाएं, अंतिम पंक्ति को पाने के लिए अंत कुंजी और अप एरो कुंजी दबाएं। पता लगाएँ कि क्या पंक्ति है। और फिर कॉलम जी और एच में, और यदि आप इसे देख रहे हैं, तो आपको अपने एक्सेल डेटा पर एक नज़र डालने और यह पता लगाने की ज़रूरत है कि आपके दो नए कॉलम कहां हैं, ठीक है। मुझे नहीं पता कि आपके पास कितने कॉलम हैं। हो सकता है कि आपके नए कॉलम I और J में खत्म हो गए हों, या हो सकता है कि वे C और D में हों। मुझे नहीं पता, वे कहां हैं और हम उन सभी पंक्तियों को छिपाने जा रहे हैं, ठीक है। तो मेरे मामले में, यह G6 से शुरू हो रहा था, यह पहली जगह है जहाँ हमारे पास एक नंबर है:एच और फिर मैं अंतिम पंक्ति को सम्मिलित कर रहा हूं जो आज हम तीन अर्धविरामों के एक नंबर प्रारूप का उपयोग कर रहे हैं जो डेटा छिपाएगा।
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
