मानहरूमा पाठ भएको पिभोट तालिका

पिभोट तालिकाहरू सबैका लागि राम्रो छन् - तिनीहरू छिट्टै गणना गर्छन्, र लचिलो रूपमा कन्फिगर गरिएका छन्, र आवश्यक भएमा, डिजाइनलाई तिनीहरूमा सुन्दर ढंगले घाउ गर्न सकिन्छ। तर त्यहाँ मलममा केही फ्लाईहरू पनि छन्, विशेष गरी, सारांश सिर्जना गर्न असक्षमता, जहाँ मान क्षेत्रमा संख्याहरू समावेश हुँदैन, तर पाठ।

यस सीमाको वरिपरि प्राप्त गर्ने प्रयास गरौं र यस्तै परिस्थितिमा "बैसाखीको जोडी" लिएर आउनुहोस्।

मानौं हाम्रो कम्पनीले आफ्ना उत्पादनहरू कन्टेनरमा हाम्रो देश र काजाकिस्तानका धेरै शहरहरूमा ढुवानी गर्छ। कन्टेनरहरू महिनामा एक पटक भन्दा बढी पठाइँदैन। प्रत्येक कन्टेनरमा अल्फान्यूमेरिक नम्बर हुन्छ। प्रारम्भिक डेटाको रूपमा, त्यहाँ एक मानक तालिका सूची वितरणहरू छन्, जसबाट तपाईंले प्रत्येक शहर र प्रत्येक महिनामा पठाइएका कन्टेनरहरूको संख्या स्पष्ट रूपमा हेर्नको लागि केही प्रकारको सारांश बनाउन आवश्यक छ:

सुविधाको लागि, आदेश प्रयोग गरेर प्रारम्भिक डाटा "स्मार्ट" संग तालिका बनाउनुहोस् गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा) र उनको नाम दिनुहोस् वितरण ट्याब कन्स्ट्रक्टर (डिजाईन)। भविष्यमा, यसले जीवनलाई सरल बनाउनेछ, किनभने। यो तालिकाको नाम र यसको स्तम्भहरू सिधै सूत्रहरूमा प्रयोग गर्न सम्भव हुनेछ।

विधि १. सबैभन्दा सजिलो - पावर क्वेरी प्रयोग गर्नुहोस्

Power Query Excel मा डाटा लोड र रूपान्तरणको लागि एक सुपर शक्तिशाली उपकरण हो। यो एड-इन 2016 देखि पूर्वनिर्धारित रूपमा एक्सेलमा बनाइएको छ। यदि तपाईंसँग एक्सेल 2010 वा 2013 छ भने, तपाईं यसलाई अलग रूपमा डाउनलोड र स्थापना गर्न सक्नुहुन्छ (पूर्ण रूपमा निःशुल्क)।

सम्पूर्ण प्रक्रिया, स्पष्टताको लागि, मैले निम्न भिडियोमा चरण-दर-चरण विश्लेषण गरें:

यदि यो Power Query प्रयोग गर्न सम्भव छैन भने, त्यसपछि तपाइँ अन्य तरिकाहरू जान सक्नुहुन्छ - पिभोट तालिका वा सूत्रहरू मार्फत। 

विधि २. सहायक सारांश

हाम्रो मूल तालिकामा अर्को स्तम्भ थपौं, जहाँ एक साधारण सूत्र प्रयोग गरेर हामी तालिकामा प्रत्येक पङ्क्तिको संख्या गणना गर्छौं:

स्पष्ट रूपमा, -1 आवश्यक छ, किनकि हामीसँग हाम्रो तालिकामा एक-लाइन हेडर छ। यदि तपाइँको तालिका पानाको सुरुमा छैन भने, तपाइँ अलि बढी जटिल, तर विश्वव्यापी सूत्र प्रयोग गर्न सक्नुहुन्छ जुन हालको पङ्क्ति र तालिका हेडरको संख्यामा भिन्नता गणना गर्दछ:

अब, मानक तरिकामा, हामी हाम्रो डेटाको आधारमा इच्छित प्रकारको पिभोट तालिका बनाउनेछौं, तर मान फिल्डमा हामी फिल्ड छोड्नेछौं। लाइन नम्बर हामीले चाहेको कुराको सट्टा कन्टेनर:

हामीसँग एउटै शहरमा एकै महिनामा धेरै कन्टेनरहरू नभएकोले, हाम्रो सारांशले वास्तवमा रकम होइन, तर हामीलाई चाहिने कन्टेनरहरूको लाइन नम्बर दिनेछ।

थप रूपमा, तपाईं ट्याबमा भव्य र उप-योगहरू बन्द गर्न सक्नुहुन्छ कन्स्ट्रक्टर - सामान्य योग и उपोटोटलहरू (डिजाइन - ग्रान्ड टोटल, सबटोटल) र उही ठाउँमा बटनको साथ थप सुविधाजनक तालिका लेआउटमा सारांश स्विच गर्नुहोस् नक्कली रिपोर्ट गर्नुहोस् (रिपोर्ट लेआउट).

यसरी, हामी नतिजाको आधा बाटोमा छौं: हामीसँग एउटा तालिका छ जहाँ, शहर र महिनाको चौराहेमा, स्रोत तालिकामा पङ्क्ति नम्बर छ, जहाँ हामीलाई आवश्यक कन्टेनर कोड छ।

अब सारांश प्रतिलिपि गरौं (एउटै पाना वा अर्कोमा) र यसलाई मानहरूको रूपमा टाँस्नुहोस्, र त्यसपछि हाम्रो सूत्रलाई मान क्षेत्रमा प्रविष्ट गर्नुहोस्, जसले सारांशमा फेला परेको लाइन नम्बरद्वारा कन्टेनर कोड निकाल्नेछ:

समारोह IF (यदि), यस अवस्थामा, सारांशमा अर्को कक्ष खाली छैन भनी जाँच गर्दछ। यदि खाली छ भने, त्यसपछि खाली पाठ स्ट्रिङ "" आउटपुट गर्नुहोस्, अर्थात् सेल खाली छोड्नुहोस्। यदि खाली छैन भने, त्यसपछि स्तम्भबाट निकाल्नुहोस् कंटेनर स्रोत तालिका वितरण प्रकार्य प्रयोग गरेर पङ्क्ति नम्बर द्वारा सेल सामग्री INDEX (INDEX).

सायद यहाँ एक मात्र धेरै स्पष्ट बिन्दु दोहोरो शब्द हो कंटेनर सूत्र मा। लेखनको यस्तो अनौठो रूप:

आपूर्ति [[कन्टेनर]:[कन्टेनर]]

… केवल स्तम्भ सन्दर्भ गर्न आवश्यक छ कंटेनर निरपेक्ष थियो (सामान्य "गैर-स्मार्ट" तालिकाहरूको लागि $ चिन्हहरूको सन्दर्भ जस्तै) र हाम्रो सूत्र दायाँ तिर प्रतिलिपि गर्दा छिमेकी स्तम्भहरूमा चिप्लिएन।

भविष्यमा, स्रोत तालिकामा डाटा परिवर्तन गर्दा वितरण, हामीले यसमा दायाँ क्लिक गरेर र कमाण्ड छनोट गरेर लाइन नम्बरहरूको साथ हाम्रो सहायक सारांश अद्यावधिक गर्न सम्झनुपर्छ। अद्यावधिक र बचत गर्नुहोस् (रिफ्रेस).

विधि 3. सूत्रहरू

यो विधिलाई मध्यवर्ती पिभोट तालिका र म्यानुअल अपडेट गर्ने आवश्यकता पर्दैन, तर एक्सेलको "भारी हतियार" - प्रकार्य प्रयोग गर्दछ। SUMMESLIMN (SUMIFS)। सारांशमा पङ्क्ति नम्बरहरू खोज्नुको सट्टा, तपाईंले तिनीहरूलाई यो सूत्र प्रयोग गरेर गणना गर्न सक्नुहुन्छ:

केहि बाह्य ठुलोपन संग, वास्तव मा, यो चयन योग प्रकार्य को लागी एक मानक प्रयोग मामला हो SUMMESLIMNA जसले दिइएको सहर र महिनाको लागि पङ्क्ति संख्याहरू जोड्छ। फेरि, हामीसँग एकै महिनामा एउटै शहरमा धेरै कन्टेनरहरू नभएकोले, हाम्रो कार्यले वास्तवमा रकम होइन, तर रेखा नम्बर आफैं दिनेछ। र त्यसपछि प्रकार्य पहिले नै अघिल्लो विधिबाट परिचित छ INDEX तपाईं कन्टेनर कोडहरू पनि निकाल्न सक्नुहुन्छ:

अवश्य पनि, यस अवस्थामा, तपाईंले अब सारांश अद्यावधिक गर्ने बारे सोच्नु पर्दैन, तर ठूला टेबलहरूमा, प्रकार्य। सुमेस्ली उल्लेखनीय ढिलो हुन सक्छ। त्यसोभए तपाईंले सूत्रहरूको स्वचालित अद्यावधिक बन्द गर्नुपर्नेछ, वा पहिलो विधि - पिभोट तालिका प्रयोग गर्नुहोस्।

यदि सारांशको उपस्थिति तपाईको रिपोर्टको लागि धेरै उपयुक्त छैन भने, तपाईले पङ्क्ति नम्बरहरू निकाल्न सक्नुहुन्छ अन्तिम तालिकामा सीधै, हामीले जस्तै होइन, तर प्रकार्य प्रयोग गरेर। GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA)। यो कसरी गर्ने यहाँ फेला पार्न सकिन्छ।

• पिभोट तालिका प्रयोग गरेर रिपोर्ट कसरी बनाउने
• पिभोट तालिकाहरूमा गणना कसरी सेटअप गर्ने
• SUMIFS, COUNTIFS, इत्यादि संग चयनात्मक गणना।