एक्सेल में कुल चल रहा है

विधि 1. सूत्र

आइए सबसे सरल विकल्प - फ़ार्मुलों के साथ, वार्म अप के लिए शुरू करें। यदि हमारे पास इनपुट के रूप में दिनांक द्वारा क्रमबद्ध एक छोटी तालिका है, तो एक अलग कॉलम में चल रहे कुल की गणना करने के लिए, हमें एक प्राथमिक सूत्र की आवश्यकता है:

यहां मुख्य विशेषता एसयूएम फ़ंक्शन के अंदर सीमा का मुश्किल निर्धारण है - सीमा की शुरुआत के संदर्भ को पूर्ण (डॉलर के संकेतों के साथ), और अंत तक - सापेक्ष (डॉलर के बिना) बनाया गया है। तदनुसार, जब सूत्र को पूरे कॉलम में कॉपी किया जाता है, तो हमें एक विस्तार सीमा मिलती है, जिसका योग हम गणना करते हैं।

इस दृष्टिकोण के नुकसान स्पष्ट हैं:

• तालिका को तिथि के अनुसार क्रमबद्ध किया जाना चाहिए।
  
• डेटा के साथ नई पंक्तियाँ जोड़ते समय, सूत्र को मैन्युअल रूप से विस्तारित करना होगा।

विधि 2. पिवट टेबल

यह विधि थोड़ी अधिक जटिल है, लेकिन बहुत अधिक सुखद है। और विस्तार करने के लिए, आइए एक और गंभीर समस्या पर विचार करें - डेटा की 2000 पंक्तियों की एक तालिका, जहां दिनांक कॉलम द्वारा कोई सॉर्टिंग नहीं है, लेकिन दोहराव हैं (यानी हम एक ही दिन में कई बार बेच सकते हैं):

हम अपनी मूल तालिका को "स्मार्ट" (डायनेमिक) कीबोर्ड शॉर्टकट में बदलते हैं कंट्रोल+T या टीम होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें), और फिर हम कमांड के साथ उस पर एक पिवट टेबल बनाते हैं सम्मिलित करें - पिवोटटेबल (सम्मिलित करें - पिवट टेबल). हम सारांश में दिनांक को पंक्तियों के क्षेत्र में रखते हैं, और मूल्य क्षेत्र में बेचे गए माल की संख्या:

कृपया ध्यान दें कि यदि आपके पास एक्सेल का काफी पुराना संस्करण नहीं है, तो तिथियां स्वचालित रूप से वर्षों, तिमाहियों और महीनों के आधार पर समूहीकृत हो जाती हैं। यदि आपको एक अलग समूह की आवश्यकता है (या इसकी बिल्कुल भी आवश्यकता नहीं है), तो आप इसे किसी भी तिथि पर राइट-क्लिक करके और कमांड का चयन करके ठीक कर सकते हैं। ग्रुप / अनग्रुप (समूह / असमूहीकृत).

यदि आप अवधि के अनुसार परिणामी योग और चल रहे कुल दोनों को एक अलग कॉलम में देखना चाहते हैं, तो यह फ़ील्ड को मान क्षेत्र में फेंकने के लिए समझ में आता है बिक फिर से फ़ील्ड का डुप्लिकेट प्राप्त करने के लिए - इसमें हम रनिंग टोटल के डिस्प्ले को चालू करेंगे। ऐसा करने के लिए, फ़ील्ड पर राइट-क्लिक करें और कमांड चुनें अतिरिक्त गणना – संचयी कुल (मानों को इस रूप में दिखाएं - रनिंग टोटल):

वहां आप प्रतिशत के रूप में बढ़ते योग का विकल्प भी चुन सकते हैं, और अगली विंडो में आपको उस क्षेत्र का चयन करना होगा जिसके लिए संचय जाएगा - हमारे मामले में, यह दिनांक फ़ील्ड है:

इस दृष्टिकोण के लाभ:

• बड़ी मात्रा में डेटा जल्दी से पढ़ा जाता है।
• मैन्युअल रूप से कोई सूत्र दर्ज करने की आवश्यकता नहीं है।
• स्रोत डेटा में परिवर्तन करते समय, सारांश को सही माउस बटन या डेटा के साथ अपडेट करने के लिए पर्याप्त है - सभी को ताज़ा करें आदेश।

नुकसान इस तथ्य से आता है कि यह एक सारांश है, जिसका अर्थ है कि आप इसमें जो कुछ भी चाहते हैं वह नहीं कर सकते हैं (पंक्तियां डालें, सूत्र लिखें, कोई आरेख बनाएं, आदि) अब काम नहीं करेगा।

विधि 3: पावर क्वेरी

आइए अपनी "स्मार्ट" तालिका को स्रोत डेटा के साथ Power Query क्वेरी संपादक में कमांड का उपयोग करके लोड करें डेटा - टेबल/रेंज से (डेटा - टेबल/रेंज से). एक्सेल के नवीनतम संस्करणों में, वैसे, इसका नाम बदल दिया गया था - अब इसे कहा जाता है पत्तों के साथ (शीट से):

फिर हम निम्नलिखित कदम उठाएंगे:

1. आदेश के साथ तालिका को आरोही क्रम में दिनांक कॉलम द्वारा क्रमबद्ध करें छोटे से बड़े क्रम में क्रमबद्ध करें तालिका शीर्षलेख में फ़िल्टर ड्रॉप-डाउन सूची में।

2. थोड़ी देर बाद, चल रहे कुल की गणना करने के लिए, हमें क्रमिक पंक्ति संख्या के साथ एक सहायक कॉलम की आवश्यकता होती है। आइए इसे कमांड के साथ जोड़ें कॉलम जोड़ें - इंडेक्स कॉलम - 1 . से (कॉलम जोड़ें - इंडेक्स कॉलम - 1 से).

3. इसके अलावा, चल रहे कुल की गणना करने के लिए, हमें कॉलम के संदर्भ की आवश्यकता है बिक, जहां हमारा सारांशित डेटा निहित है। पावर क्वेरी में, कॉलम को सूचियां (सूची) भी कहा जाता है और इसका लिंक प्राप्त करने के लिए, कॉलम हेडर पर राइट-क्लिक करें और कमांड का चयन करें विस्तृतीकरण (विवरण दिखाएँ). हमें जिस व्यंजक की आवश्यकता है वह पिछले चरण के नाम से युक्त सूत्र पट्टी में दिखाई देगा #"सूचकांक जोड़ा गया", जहां से हम टेबल और कॉलम का नाम लेते हैं [बिक्री] इस तालिका से वर्गाकार कोष्ठकों में:

आगे उपयोग के लिए इस अभिव्यक्ति को क्लिपबोर्ड पर कॉपी करें।

4. अनावश्यक अधिक अंतिम चरण हटाएं बिक और इसके बजाय कमांड के साथ चल रहे कुल की गणना के लिए एक परिकलित कॉलम जोड़ें एक कॉलम जोड़ना - कस्टम कॉलम (कॉलम जोड़ें — कस्टम कॉलम). हमें जो सूत्र चाहिए वह इस तरह दिखेगा:

यहाँ समारोह सूची। रेंज मूल सूची लेता है (स्तंभ [बिक्री]) और उसमें से तत्वों को निकालता है, पहले से शुरू (सूत्र में, यह 0 है, क्योंकि Power Query में नंबरिंग शून्य से शुरू होती है)। पुनर्प्राप्त करने के लिए तत्वों की संख्या वह पंक्ति संख्या है जिसे हम कॉलम से लेते हैं [अनुक्रमणिका]. तो पहली पंक्ति के लिए यह फ़ंक्शन केवल कॉलम का पहला सेल लौटाता है बिक. दूसरी पंक्ति के लिए - पहले से ही पहले दो सेल, तीसरे के लिए - पहले तीन, आदि।

खैर, फिर समारोह सूची। योग निकाले गए मानों का योग करता है और हम प्रत्येक पंक्ति में पिछले सभी तत्वों का योग प्राप्त करते हैं, अर्थात संचयी कुल:

यह इंडेक्स कॉलम को हटाने के लिए बनी हुई है जिसकी हमें अब आवश्यकता नहीं है और परिणामों को एक्सेल में होम - क्लोज एंड लोड टू कमांड के साथ वापस अपलोड करें।

समस्या सुलझ गई है।

फास्ट एंड फ्यूरियस

सिद्धांत रूप में, इसे रोका जा सकता था, लेकिन मरहम में एक छोटी सी मक्खी है - हमने जो अनुरोध बनाया है वह कछुए की गति से काम करता है। उदाहरण के लिए, मेरे सबसे कमजोर पीसी पर, केवल 2000 पंक्तियों की एक तालिका को 17 सेकंड में संसाधित किया जाता है। क्या होगा यदि अधिक डेटा है?

गति बढ़ाने के लिए, आप विशेष List.Buffer फ़ंक्शन का उपयोग करके बफरिंग का उपयोग कर सकते हैं, जो इसे दी गई सूची (सूची) को RAM में एक तर्क के रूप में लोड करता है, जो भविष्य में इस तक पहुंच को बहुत तेज करता है। हमारे मामले में, #“जोड़ा गया सूचकांक” [बिक्री] सूची को बफ़र करना समझ में आता है, जिसे हमारी 2000-पंक्ति तालिका की प्रत्येक पंक्ति में चल रहे कुल की गणना करते समय Power Query को एक्सेस करना होता है।

ऐसा करने के लिए, मुख्य टैब पर Power Query संपादक में, Power Query में निर्मित M भाषा में हमारी क्वेरी का स्रोत कोड खोलने के लिए उन्नत संपादक बटन (होम - उन्नत संपादक) पर क्लिक करें:

और फिर वहां एक वेरिएबल वाली लाइन जोड़ें मेरी सूची, जिसका मान बफरिंग फ़ंक्शन द्वारा लौटाया जाता है, और अगले चरण में हम कॉल को इस चर के साथ सूची में बदल देते हैं:

इन परिवर्तनों को करने के बाद, हमारी क्वेरी काफी तेज हो जाएगी और 2000-पंक्ति तालिका के साथ केवल 0.3 सेकंड में सामना करेगी!

एक और बात, है ना? मैं

• परेटो चार्ट (80/20) और इसे एक्सेल में कैसे बनाया जाए
• टेक्स्ट में कीवर्ड खोज और Power Query में क्वेरी बफ़रिंग