पद्धत 1. सूत्रे
चला, वार्मिंग अपसाठी, सर्वात सोप्या पर्यायासह - सूत्रांसह प्रारंभ करूया. जर आमच्याकडे इनपुट म्हणून तारखेनुसार क्रमवारी लावलेली लहान सारणी असेल, तर वेगळ्या कॉलममध्ये चालू असलेल्या एकूण संख्येची गणना करण्यासाठी, आम्हाला प्राथमिक सूत्र आवश्यक आहे:
येथे मुख्य वैशिष्ट्य म्हणजे SUM फंक्शनमधील श्रेणीचे अवघड निर्धारण - श्रेणीच्या सुरुवातीचा संदर्भ निरपेक्ष (डॉलर चिन्हांसह) आणि शेवटी - सापेक्ष (डॉलर्सशिवाय) केला जातो. त्यानुसार, संपूर्ण स्तंभात सूत्र खाली कॉपी करताना, आपल्याला एक विस्तारणारी श्रेणी मिळते, ज्याची आपण गणना करतो.
या पद्धतीचे तोटे स्पष्ट आहेत:
- टेबल तारखेनुसार क्रमवारी लावणे आवश्यक आहे.
- डेटासह नवीन पंक्ती जोडताना, सूत्र व्यक्तिचलितपणे वाढवावे लागेल.
पद्धत 2. मुख्य सारणी
ही पद्धत थोडी अधिक क्लिष्ट आहे, परंतु अधिक आनंददायी आहे. आणि आणखी वाढवण्यासाठी, चला अधिक गंभीर समस्येचा विचार करूया - डेटाच्या 2000 पंक्तींचा एक सारणी, जिथे तारीख स्तंभानुसार क्रमवारी लावली जात नाही, परंतु पुनरावृत्ती आहेत (म्हणजे आम्ही एकाच दिवशी अनेक वेळा विक्री करू शकतो):
आम्ही आमच्या मूळ सारणीला "स्मार्ट" (डायनॅमिक) कीबोर्ड शॉर्टकटमध्ये रूपांतरित करतो Ctrl+T किंवा संघ मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित), आणि मग आम्ही कमांडसह त्यावर एक पिव्होट टेबल तयार करतो घाला - PivotTable (घाला — मुख्य सारणी). आम्ही सारांशात पंक्ती क्षेत्रामध्ये तारीख आणि मूल्य क्षेत्रामध्ये विकल्या गेलेल्या वस्तूंची संख्या ठेवतो:
कृपया लक्षात घ्या की तुमच्याकडे Excel ची फारशी जुनी आवृत्ती नसल्यास, तारखा आपोआप वर्ष, तिमाही आणि महिन्यांनुसार गटबद्ध केल्या जातात. जर तुम्हाला वेगळ्या गटाची गरज असेल (किंवा अजिबात गरज नसेल), तर तुम्ही कोणत्याही तारखेला उजवे-क्लिक करून आणि कमांड निवडून त्याचे निराकरण करू शकता. गट / गट रद्द करा (गट / गट रद्द करा).
जर तुम्हाला पूर्णविरामानुसार परिणामी बेरीज आणि रनिंग बेरीज या दोन्ही गोष्टी वेगळ्या कॉलममध्ये पहायच्या असतील, तर फील्डला व्हॅल्यू एरियामध्ये टाकण्यात अर्थ आहे. विकले पुन्हा फील्डची डुप्लिकेट मिळविण्यासाठी - त्यात आपण धावण्याच्या बेरीजचे प्रदर्शन चालू करू. हे करण्यासाठी, फील्डवर उजवे-क्लिक करा आणि कमांड निवडा अतिरिक्त गणना – एकत्रित एकूण (मूल्ये म्हणून दर्शवा — चालू असलेली एकूण):
तेथे तुम्ही टक्केवारी म्हणून वाढत्या बेरीजचा पर्याय देखील निवडू शकता आणि पुढील विंडोमध्ये तुम्हाला ते फील्ड निवडणे आवश्यक आहे ज्यासाठी जमा होईल - आमच्या बाबतीत, ही तारीख फील्ड आहे:
या पद्धतीचे फायदेः
- मोठ्या प्रमाणात डेटा पटकन वाचला जातो.
- कोणतीही सूत्रे व्यक्तिचलितपणे प्रविष्ट करण्याची आवश्यकता नाही.
- स्त्रोत डेटामध्ये बदल करताना, उजव्या माऊस बटणासह किंवा डेटा - सर्व रिफ्रेश कमांडसह सारांश अद्यतनित करणे पुरेसे आहे.
तोटे या वस्तुस्थितीचे अनुसरण करतात की हा सारांश आहे, याचा अर्थ असा की आपण त्यात जे काही हवे ते करू शकत नाही (ओळी घाला, सूत्रे लिहा, कोणतेही आकृती तयार करा, इ.) यापुढे कार्य करणार नाही.
पद्धत 3: पॉवर क्वेरी
कमांड वापरून पॉवर क्वेरी क्वेरी एडिटरमध्ये स्त्रोत डेटासह आमचे "स्मार्ट" टेबल लोड करूया डेटा - सारणी/श्रेणीवरून (डेटा — सारणी/श्रेणीवरून). एक्सेलच्या नवीनतम आवृत्त्यांमध्ये, तसे, त्याचे नाव बदलले गेले - आता ते म्हणतात पाने सह (पत्रकावरून):
मग आम्ही खालील चरण करू:
1. कमांडसह तारखेच्या स्तंभानुसार चढत्या क्रमाने टेबलची क्रमवारी लावा चढत्या क्रमाने क्रमवारी लावा टेबल शीर्षलेखातील फिल्टर ड्रॉप-डाउन सूचीमध्ये.
2. थोड्या वेळाने, रनिंग टोटलची गणना करण्यासाठी, आम्हाला ऑर्डिनल पंक्ती क्रमांकासह एक सहायक स्तंभ आवश्यक आहे. चला कमांडसह जोडूया स्तंभ जोडा - अनुक्रमणिका स्तंभ - 1 पासून (स्तंभ जोडा — अनुक्रमणिका स्तंभ — ० पासून).
3. तसेच, रनिंग टोटलची गणना करण्यासाठी, आम्हाला स्तंभाचा संदर्भ आवश्यक आहे विकले, जिथे आमचा सारांशित डेटा आहे. पॉवर क्वेरीमध्ये, स्तंभांना सूची (सूची) असेही म्हणतात आणि त्याची लिंक मिळविण्यासाठी, स्तंभ शीर्षलेखावर उजवे-क्लिक करा आणि कमांड निवडा. तपशीलवार (तपशील दाखवा). आपल्याला आवश्यक असलेली अभिव्यक्ती फॉर्म्युला बारमध्ये दिसून येईल, ज्यामध्ये मागील चरणाचे नाव असेल #"इंडेक्स जोडले", जिथून आपण टेबल आणि स्तंभाचे नाव घेतो [विक्री] चौरस कंसात या टेबलमधून:
पुढील वापरासाठी क्लिपबोर्डवर ही अभिव्यक्ती कॉपी करा.
4. अनावश्यक अधिक शेवटची पायरी हटवा विकले आणि त्याऐवजी कमांडसह रनिंग टोटलची गणना करण्यासाठी एक गणना केलेला स्तंभ जोडा एक स्तंभ जोडणे - सानुकूल स्तंभ (स्तंभ जोडा — सानुकूल स्तंभ). आम्हाला आवश्यक असलेले सूत्र असे दिसेल:
येथे कार्य यादी.श्रेणी मूळ यादी घेते (स्तंभ [विक्री]) आणि त्यातून घटक काढतो, पहिल्यापासून सुरू होतो (सूत्रात, हे 0 आहे, कारण पॉवर क्वेरीमध्ये क्रमांकन शून्यापासून सुरू होते). पुनर्प्राप्त करण्‍याच्‍या घटकांची संख्या ही आपण स्‍तंभातून घेतलेली पंक्ती क्रमांक आहे [अनुक्रमणिका]. त्यामुळे पहिल्या पंक्तीसाठी हे फंक्शन कॉलमचा फक्त एक पहिला सेल मिळवते विकले. दुसऱ्या ओळीसाठी - आधीच पहिल्या दोन पेशी, तिसऱ्यासाठी - पहिले तीन इ.
बरं, मग फंक्शन यादी.सम काढलेल्या मूल्यांची बेरीज करतो आणि आम्हाला प्रत्येक पंक्तीमध्ये मागील सर्व घटकांची बेरीज मिळते, म्हणजे एकत्रित एकूण:
आम्हाला यापुढे गरज नसलेला इंडेक्स कॉलम हटवणे आणि होम – क्लोज आणि लोड टू कमांडसह निकाल एक्सेलवर अपलोड करणे बाकी आहे.
समस्या सुटली आहे.
फास्ट अँड फ्यूरियस
तत्वतः, हे थांबविले जाऊ शकते, परंतु मलममध्ये एक लहान माशी आहे - आम्ही तयार केलेली विनंती कासवाच्या वेगाने कार्य करते. उदाहरणार्थ, माझ्या सर्वात कमकुवत पीसीवर, केवळ 2000 पंक्तींच्या टेबलवर 17 सेकंदात प्रक्रिया केली जाते. अधिक डेटा असल्यास काय?
वेग वाढवण्यासाठी, तुम्ही विशेष List.Buffer फंक्शन वापरून बफरिंग वापरू शकता, जे RAM मध्ये युक्तिवाद म्हणून दिलेली सूची (सूची) लोड करते, ज्यामुळे भविष्यात त्याच्या प्रवेशास गती वाढते. आमच्या बाबतीत, आमच्या 2000-पंक्ती सारणीच्या प्रत्येक पंक्तीमध्ये चालू असलेल्या एकूण संख्येची गणना करताना पॉवर क्वेरीला प्रवेश करावा लागणारी #"जोडलेली अनुक्रमणिका"[विकलेली] सूची बफर करण्यात अर्थ आहे.
हे करण्यासाठी, मुख्य टॅबवरील पॉवर क्वेरी संपादकामध्ये, पॉवर क्वेरीमध्ये तयार केलेल्या एम भाषेत आमच्या क्वेरीचा स्त्रोत कोड उघडण्यासाठी प्रगत संपादक बटणावर क्लिक करा (होम – प्रगत संपादक)
आणि नंतर तेथे व्हेरिएबलसह एक ओळ जोडा मायलिस्ट, ज्याचे मूल्य बफरिंग फंक्शनद्वारे परत केले जाते आणि पुढील चरणावर आम्ही या व्हेरिएबलसह सूचीमध्ये कॉल बदलतो:
हे बदल केल्यानंतर, आमची क्वेरी लक्षणीयरीत्या वेगवान होईल आणि 2000-पंक्ती सारणीचा सामना फक्त 0.3 सेकंदात होईल!
दुसरी गोष्ट, बरोबर? 🙂
- Pareto चार्ट (80/20) आणि तो Excel मध्ये कसा तयार करायचा
- पावर क्वेरीमध्ये मजकूरातील कीवर्ड शोध आणि क्वेरी बफरिंग