यह एपिसोड रनिंग योग करने के तीन तरीके दिखाता है।
एक रनिंग टोटल, संख्यात्मक मानों की सूची के लिए, पहली पंक्ति से मानों का योग है जो रनिंग टोटल की पंक्ति में है। एक रनिंग टोटल के सामान्य उपयोग चेकबुक रजिस्टर या अकाउंटिंग शीट में होते हैं। रनिंग टोटल-टू बनाने के कई तरीके हैं जिनका वर्णन नीचे दिया गया है।
प्रत्येक पंक्ति में सबसे सरल तकनीक है, पंक्ति में ऊपर की पंक्ति से चल रहे कुल को जोड़ दें। तो पंक्ति 2 में पहला सूत्र है:
=SUM(D1,C2)
SUM फ़ंक्शन का उपयोग करने का कारण यह है, क्योंकि पहली पंक्ति में, हम शीर्ष पंक्ति में शीर्ष लेख को देख रहे हैं। यदि हम सरल, अधिक सहज सूत्र का उपयोग करते हैं =D1+C2तो एक त्रुटि उत्पन्न होगी क्योंकि हेडर का मूल्य पाठ बनाम संख्यात्मक है। जादू यह है कि SUM फ़ंक्शन पाठ मानों को अनदेखा करता है, जिन्हें शून्य मानों के रूप में जोड़ा जाता है। जब सूत्र को उन सभी पंक्तियों में कॉपी किया जाता है जिसमें एक चलने वाला कुल वांछित होता है, तो सेल संदर्भ को तदनुसार समायोजित किया जाता है:
दूसरी तकनीक भी SUM फ़ंक्शन का उपयोग करती है, लेकिन प्रत्येक सूत्र पहली पंक्ति से सभी मानों को चलाता है जो चल रहे कुल को प्रदर्शित करता है। इस मामले में हम एक संदर्भ में पहली सेल बनाने के लिए एक डॉलर के संकेत ($) का उपयोग करते हैं एक निरपेक्ष संदर्भ जिसका अर्थ है कि इसे कॉपी किए जाने पर समायोजित नहीं किया जाता है:
पंक्तियों को छांटने और हटाने से दोनों तकनीक अप्रभावित हैं लेकिन, पंक्तियों को सम्मिलित करते समय, सूत्र को नई पंक्तियों में कॉपी करना होगा।
Excel 2007 ने तालिका को पेश किया जो Excel 2003 में सूची का पुन: कार्यान्वयन है। टेबल्स ने डेटा तालिकाओं जैसे स्वरूपण, छंटाई और छानने के लिए बहुत उपयोगी सुविधाओं की शुरुआत की। टेबल्स की शुरुआत के साथ हमें एक टेबल के भागों को संदर्भित करने का एक नया तरीका भी प्रदान किया गया था। इस नई संदर्भ शैली को संरचित संदर्भ कहा जाता है।
उपरोक्त उदाहरण को तालिका में परिवर्तित करने के लिए, हम उस डेटा का चयन करते हैं जिसे हम तालिका में शामिल करना चाहते हैं और Ctrl + T दबाएं। एक संकेत प्रदर्शित करने के बाद हमें तालिका की सीमा की पुष्टि करने के लिए कहें और मौजूदा हेडर हैं या नहीं, एक्सेल डेटा को रूपांतरित करता है। एक स्वरूपित तालिका में:
ध्यान दें कि हम पहले दर्ज किए गए फॉर्मूले वही हैं।
उपयोगी सुविधाओं में से एक टेबल्स की पेशकश स्वचालित स्वरूपण और सूत्र रखरखाव है क्योंकि पंक्तियों को जोड़ा जाता है, हटाया जाता है, सॉर्ट किया जाता है, और फ़िल्टर किया जाता है। यह विशेष रूप से फार्मूला रखरखाव है जिस पर हम ध्यान केंद्रित करेंगे और जो समस्याग्रस्त हो सकता है। जोड़तोड़ करते समय टेबल्स को काम पर रखने के लिए, एक्सेल गणना किए गए कॉलम का उपयोग करता है जो उपरोक्त उदाहरण में कॉलम डी जैसे फॉर्मूले वाले कॉलम हैं। जब नई पंक्तियों को नीचे डाला जाता है, तो Excel स्वचालित रूप से उस कॉलम के लिए "डिफ़ॉल्ट" सूत्र के साथ नई पंक्तियों को पॉप्युलेट करता है। उपरोक्त उदाहरण के साथ समस्या यह है कि एक्सेल मानक फ़ार्मुलों के साथ भ्रमित हो जाता है और हमेशा उन्हें सही ढंग से संभाल नहीं पाता है। यह स्पष्ट किया जाता है जब नई पंक्तियों को तालिका के नीचे जोड़ा जाता है (तालिका में नीचे दाएं सेल का चयन करके और टैब दबाकर):
इस कमी को नए संरचित संदर्भित का उपयोग करके हल किया जाता है। संरचित संदर्भित ए 1 या आर 1 सी 1 संदर्भ शैली का उपयोग करके विशिष्ट कोशिकाओं को संदर्भित करने की आवश्यकता को समाप्त करता है और इसके बजाय तालिका के भागों की पहचान करने और संदर्भ देने के लिए स्तंभ नामों और अन्य कीवर्ड का उपयोग करता है। उदाहरण के लिए, ऊपर चल रहे समान सूत्र बनाने के लिए लेकिन हमारे पास संरचित संदर्भों का उपयोग करना:
=SUM(INDEX((Sales),1):(@Sales))
इस उदाहरण में, हमारे पास कॉलम नाम, बिक्री के साथ-साथ साइन (@) के संदर्भ में कॉलम में पंक्ति को संदर्भित करने के लिए है जिसमें सूत्र स्थित है जिसे वर्तमान पंक्ति के रूप में भी जाना जाता है।
ऊपर दिए गए पहले उदाहरण को लागू करने के लिए जहां हमने मौजूदा कुल मूल्य को चालू पंक्ति में बिक्री राशि में जोड़ा था, आप OFFSET फ़ंक्शन का उपयोग कर सकते हैं:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
यदि चल रहे कुल की गणना के लिए उपयोग की जाने वाली राशियाँ दो स्तंभों में हैं, उदाहरण के लिए "डेबिट" के लिए एक और "क्रेडिट" के लिए एक, तो सूत्र है:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
यहां हम पहली पंक्ति की क्रेडिट और डेबिट कोशिकाओं का पता लगाने के लिए INDEX फ़ंक्शन का उपयोग कर रहे हैं, और वर्तमान पंक्ति के मानों सहित पूरे कॉलम को समेटते हैं। रनिंग टोटल सभी क्रेडिट का योग है और वर्तमान पंक्ति को शामिल करता है और वर्तमान पंक्ति सहित सभी डेबिट का योग कम होता है।
विशेष रूप से संरचित संदर्भों और सामान्य रूप से टेबल्स के बारे में अधिक जानकारी के लिए, हम बुक करते हैं एक्सेल टेबल्स: एक पूरी गाइड बनाने के लिए, ज़ैक बैरी और केविन जोन्स द्वारा सूची और टेबल्स बनाने, उपयोग करने और स्वचालित करने के लिए।
जब मैंने पाठकों से अपने पसंदीदा सुझावों के लिए वोट करने के लिए कहा, तो टेबल लोकप्रिय थे। इस सुविधा का सुझाव देने के लिए पीटर अल्बर्ट, स्नोरे ईकलैंड, नैन्सी फेडेरिस, कॉलिन माइकल, जेम्स ई। मोएड, कीयूर पटेल और पॉल पेटन का धन्यवाद। पीटर अल्बर्ट ने पठनीय संदर्भ बोनस टिप लिखा। ज़ैक बैरी ने रनिंग टोटल्स बोनस टिप लिखा। चार पाठकों ने गतिशील चार्ट के लिए विस्तार श्रृंखला बनाने के लिए OFFSET का उपयोग करने का सुझाव दिया: चार्ली बाक, डॉन नोल्स, फ्रांसिस लोगान और सेसिलिया रीब। टेबल्स अब ज्यादातर मामलों में एक ही काम करते हैं।
वीडियो देखेंा
- यह एपिसोड रनिंग योग करने के तीन तरीके दिखाता है
- पहली विधि में पंक्ति 2 में सभी अन्य पंक्तियों की तुलना में एक अलग सूत्र है
- पहली विधि है = पंक्ति 2 में वाम और = बाईं ओर पंक्ति 3 में N के माध्यम से
- यदि आप समान सूत्र का उपयोग करने का प्रयास करते हैं, तो आपको = कुल + संख्या के साथ # त्रुटि त्रुटि मिलती है
- विधि 2 उपयोग करता है
=SUM(Up,Left)या=SUM(Previous Total,This Row Amount) - SUM पाठ को अनदेखा करता है ताकि आपको VALUE त्रुटि न मिले
- विधि 3 एक विस्तार श्रेणी का उपयोग करती है:
=SUM(B$2:B2) - विस्तार श्रेणियां शांत हैं लेकिन वे धीमी हैं
- एक्सेल फॉर्मूला स्पीड पर चार्ल्स विलियम्स श्वेतपत्र पढ़ें
- तीसरी विधि एक समस्या है जब आप Ctrl + T का उपयोग करते हैं और नई पंक्तियाँ जोड़ते हैं
- एक्सेल सूत्र लिखने का तरीका पता नहीं लगा सकता
- कार्यबल को टेबल्स में संरचित संदर्भित के कुछ ज्ञान की आवश्यकता होती है
- वर्कअराउंड 1 धीमा है
=SUM(INDEX((Qty),1):(@Qty)) - वर्कअराउंड 2 अस्थिर है
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) इस पंक्ति पर Qty को संदर्भित करता है
- (मात्रा) सभी मात्रा मूल्यों को संदर्भित करता है
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट के लिए एक्सेल सीखें, एपिसोड 2004 - रनिंग टोटल
मैं इस पूरी किताब को पॉडकास्ट करूंगा। सदस्यता लेने के लिए मैं शीर्ष दाएँ हाथ के कोने पर क्लिक करता हूँ।
अरे वापस मिस्टिक सेल नेटकास्ट में आपका स्वागत है। मैं बिल जेलन हूं। अब पुस्तक में इस विषय पर, मुझे मेरे मित्र ज़च पारिज ने योगदान दिया। एक्सेल तालिकाओं की बात करें, तो ज़च एक्सेल तालिकाओं पर दुनिया का विशेषज्ञ है। उन्होंने एक्सेल टेबल के बारे में एक पुस्तक लिखी है, लेकिन पहले आइए टेबल्स में नहीं चल रहे योगों के बारे में बात करते हैं।
इसलिए जब मैं रनिंग योग के बारे में सोचता हूं, तो चल रहे कुल योग करने के तीन अलग-अलग तरीके होते हैं, और जिस तरह से मैं हमेशा से शुरू करता हूं वह पहली पंक्ति में है जो आप अभी कहते हैं, मान को ऊपर लाएं। तो जो भी मेरे बायीं ओर है, उसके बराबर। ठीक है, इसलिए यहां यह प्रारूप सिर्फ = B2 है। ये सभी सूत्र पाठ यहाँ दाहिने हाथ के कोने पर हैं ताकि आप देख रहे हैं कि हम क्या उपयोग कर रहे हैं, और फिर वहाँ से नीचे की ओर, यह पिछले मूल्य के बराबर का एक साधारण सा सूत्र है, साथ ही वर्तमान मूल्य सही है और इसे नीचे कॉपी करें , लेकिन आप अब जानते हैं, हमारे पास यह समस्या है कि इसके लिए दो अलग-अलग फ़ार्मुलों की आवश्यकता होती है और आप एक सही स्थिति में जानते हैं कि आपके पास बिल्कुल वही फॉर्मूला है जो नीचे है, और इसका कारण यह है कि हमारे पास पहली पंक्ति में एक अलग फॉर्मूला है जब आप कोशिश करते हैं और बराबर 7 जोड़ते हैं, तो कुल शब्द यह एक मूल्य त्रुटि है,लेकिन कूल वर्कर यहाँ है, न केवल लेफ्ट प्लस अप का उपयोग करना, बल्कि पिछले मूल्य का = (एसयूएम) इस पंक्ति में मात्रा का उपयोग करना, और कुछ को टेक्स्ट्स को नजरअंदाज करना पर्याप्त है। ठीक है कि एक ही सूत्र की अनुमति देता है। नीचे तक।
ठीक है कि जब मैं एक्सेल का उपयोग कर रहा था, तब मैं उसका उपयोग कर रहा था और फिर मैंने विस्तार रेंज की खोज की, विस्तार रेंज का कहना है कि हम L $ 2: L2 करने जा रहे हैं और क्या होता है यह हमेशा पंक्ति 2 से शुरू होता है, लेकिन फिर यह वर्तमान पंक्ति के लिए नीचे जा रहा है। इसलिए जब आप देखते हैं कि यह कैसे काम करता है जब इसकी नकल होती है, तो हमने हमेशा पंक्ति 2 की शुरुआत की, लेकिन हम वर्तमान पंक्ति में चले गए और यह हमारी पसंदीदा पद्धति बन गई। मैं ऐसा था, ओह, यह बहुत अधिक परिष्कृत है और जब हम एक्सेल विकल्प में जाते हैं, तो फॉर्मूला टैब पर जाएं और संदर्भ शैली में R1C1 चुनें। ठीक देखें, R1C1, ये सभी फॉर्मूले बिल्कुल वैसे ही हैं जैसे नीचे हैं। मुझे नहीं पता कि आप R1C1 को समझते हैं, यह जानना अच्छा है कि हमारे पास समान R1C1 फॉर्मूले हैं।
चलो वापस चलते हैं। तो यहाँ पर यह विधि वह विधि है जो मुझे पसंद आई, जब तक कि चार्ल्स विलियम्स, इंग्लैंड के एक एक्सेल एमबीपी, जिनके पास फार्मूला स्पीड, एक्सेल फॉर्मूला स्पीड पर एक अद्भुत पेपर है, ने इस पद्धति को पूरी तरह से डिबार कर दिया। यह विधि, मान लें कि आपके पास 10,000 पंक्तियाँ हैं, हर एक सूत्र दो संदर्भों को देख रहा है। तो आप 20,000 संदर्भ देख रहे हैं, लेकिन यह एक, यह दो देख रहा है, यह तीन देख रहा है, यह चार देख रहा है, यह पांच देख रहा है और अंतिम 10,000 संदर्भ देख रहा है, और यह बहुत धीमा है और इसलिए मैंने इस पद्धति का उपयोग करना बंद कर दिया।
फिर मैं एक्सेल तालिकाओं के बारे में केविन जोन्स की पुस्तक में जैक को पढ़ने के लिए जाता हूं और मुझे इस पद्धति के साथ एक और समस्या का पता चलता है। तो उपयोगी सुविधाओं में से एक, जो टेबल ऑफ़र प्रदान करता है, वह है 'स्वचालित स्वरूपण और सूत्र maintenances पंक्तियों को जोड़ा, हटाया, सॉर्ट किया गया और फ़िल्टर किया जाता है'। ठीक है कि उनकी पुस्तक का एक उद्धरण है। और एक तालिका में एक पंक्ति जोड़ने के लिए आप बस मेज पर बहुत अंतिम सेल में जाते हैं और टैब दबाते हैं। इसलिए यहां सब कुछ काम कर रहा है। हम नीचे 70 सही है कि भयानक है और फिर A104 और मैं एक 100 यहाँ डाल देंगे। ठीक है, इसलिए कि 70 को 170 में बदलना चाहिए और ऐसा होता है, लेकिन यह 70 बिल्कुल नहीं बदलना चाहिए था। ठीक 68 + 2 एक 170 नहीं है। मैं इसे फिर से करूँगा। एक 104 और आखिरी में एक और शतक लगाना सही है। ये दोनों सही नहीं हैं। ठीक है, इसलिए हमारे पास कुछ अजीब स्थिति है कि अगर आप 'इस फॉर्मूले का उपयोग करके आप तालिका में पंक्तियों को जोड़ना शुरू करते हैं, चल रहा कुल काम नहीं होने वाला है। कितना बुरा है?
ठीक है, इसलिए ज़ैक दो काम के आसपास की पेशकश करता है और दोनों को थोड़े से ज्ञान की आवश्यकता होती है कि संरचना कैसे काम करती है। हम यहाँ एक नया कॉलम लाने जा रहे हैं और अगर मैं मात्रा, बराबर मात्रा, सही करना चाहता था, तो इस पंक्ति में = (@ Qty) मात्रा कहता है। ओह, अच्छा, एक और तरह का संदर्भ है जहां हम @ के बिना Qty का उपयोग करते हैं। इसकी जांच करें। तो = SUM (INDEX ((Qty)), 1: (@ Qty) का मतलब सभी मात्राओं से है और हम यह कहना चाहते हैं कि हम पहली मात्रा से SUM चाहते हैं, इसलिए (INDEX (Qty), 1 कहते हैं। पहले मूल्य यहाँ, नीचे वर्तमान पंक्ति मात्रा के लिए, और यह सूचकांक के एक बहुत ही विशेष संस्करण का उपयोग कर रहा है, जब सूचकांक एक बृहदान्त्र द्वारा पीछा किया जाता है, यह वास्तव में एक सेल संदर्भ में बदल जाता है। ठीक है, तो यह समाधान दुर्भाग्य से चार्ल्स विलियम्स नियम का उल्लंघन है। हम, 'हर एक संदर्भ को देखना होगा, और इसलिए जब आपको इसकी 10,000 पंक्तियाँ मिलेंगी तो यह वास्तव में धीमी गति से चलने वाली है।
Zach में एक और समाधान है जो चार्ल्स विलियम्स समस्या का उल्लंघन नहीं करता है, लेकिन यह खतरनाक OFFSET का उपयोग कर रहा है। OFFSET एक अस्थिर कार्य है जिससे हर बार जब आप कुछ की गणना करते हैं, तो OFFSET पुनर्गणना करने जा रहा है और OFFSET के पुनर्गणना के लिए लाइन से सब कुछ नीचे है। यह पूरी तरह से आपके सूत्रों को पूरी तरह से पूरी तरह से पेंच करने का एक शानदार तरीका है, और यह क्या कर रहा है, यह कह रहा है, हम इस पंक्ति से कुल ले रहे हैं, एक पंक्ति ऊपर जा रहे हैं, शून्य स्तंभों पर और इसलिए वह जो कह रहा है: पिछली पंक्ति से कुल को पकड़ो और फिर हम इसे इस पंक्ति से मात्रा जोड़ रहे हैं। ठीक है, इसलिए, अब यह हर बार दो संदर्भों को देख रहा है, लेकिन दुर्भाग्य से OFFSET अस्थिर कार्यों को शुरू कर रहा है।
वैसे, आपके पास यह है, इससे अधिक आप रनिंग टोटल के बारे में जानना चाहते थे। मुझे लगता है कि इस पद्धति का उपयोग करने के लिए मेरी अंतिम राय है, क्योंकि यह केवल दो दिखती है। नीचे सभी तरह से सूत्र और आपके संरचित तालिका संदर्भ काम करेंगे।
इस अन्वेषण और 39 अन्य अच्छे सुझावों के लिए, इस पुस्तक एक्सएल को देखें, जो अब तक के 40 सबसे बड़े एक्सेल टिप्स हैं।
इस एपिसोड के लिए रिकैप हमने रनिंग योग करने के तीन तरीकों के बारे में बात की। पहली विधि में अन्य सभी पंक्तियों की तुलना में एक अलग सूत्र, पंक्ति 2 है। यह पंक्ति 2 में बराबर बाएँ और फिर N के माध्यम से पंक्तियों 3 में बराबर बाएँ ऊपर है, लेकिन यदि आप कोशिश करते हैं और बस उसी सूत्र का उपयोग करते हैं, तो समान बाएँ ऊपर, सभी तरह से नीचे, कैसे आप एक # त्रुटि प्राप्त करने जा रहे हैं । तो = एसयूएम (अप, लेफ्ट), जो कि पिछले कुल है, साथ ही यह रोडमैप है, जो शानदार काम करता है, कोई वैल्यू एरर्स नहीं है और फिर विस्तार की सीमा जो मैं प्यार करने के लिए उपयोग करता हूं। वे शांत हैं, लेकिन जब तक मैं एक्सेल के रूप में चार्ल्स विलियम्स का श्वेत पत्र पढ़ता हूं। फिर मुझे इन विस्तार संदर्भों से नफरत होने लगी। जब आप CTRL T का उपयोग करते हैं और नई पंक्तियाँ जोड़ते हैं तो यह भी एक समस्या है। एक्सेल यह पता नहीं लगा सकता कि उस सूत्र का विस्तार कैसे किया जाए, नई पंक्तियों को कैसे जोड़ा जाए। मुझे यह टिप पसंद है कि तालिका में बहुत अंतिम सेल पर जाएं और टैब दबाएं,यह एक नई पंक्ति जोड़ देगा और फिर हमने कुछ संरचित संदर्भों के बारे में बात की, जहां हम इस पंक्ति में मात्रा और फिर सभी मात्राओं का उपयोग कर रहे हैं। = SUM (OFFSET ((@ कुल), - 1,00, (@ Qty))।
ठीक है, मैं उस टिप को योगदान देने के लिए Zach को धन्यवाद देना चाहता हूं। मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2004.xlsx
