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