मूल्यांमधील मजकूरासह मुख्य सारणी

पिव्होट टेबल्स प्रत्येकासाठी चांगली आहेत – ते त्वरीत गणना करतात, आणि लवचिकपणे कॉन्फिगर केले जातात, आणि आवश्यक असल्यास, डिझाइन त्यांच्यामध्ये सुंदरपणे जोडले जाऊ शकते. परंतु मलममध्ये काही माशी देखील आहेत, विशेषतः, सारांश तयार करण्यास असमर्थता, जेथे मूल्य क्षेत्रामध्ये संख्या नसावी, परंतु मजकूर.

चला या मर्यादेवर जाण्याचा प्रयत्न करूया आणि अशाच परिस्थितीत “कपल क्रॅच” घेऊन येऊ.

समजा आमची कंपनी आपली उत्पादने आमच्या देश आणि कझाकस्तानमधील अनेक शहरांमध्ये कंटेनरमध्ये वाहतूक करते. कंटेनर महिन्यातून एकापेक्षा जास्त वेळा पाठवले जात नाहीत. प्रत्येक कंटेनरमध्ये अल्फान्यूमेरिक क्रमांक असतो. प्रारंभिक डेटा म्हणून, एक मानक सारणी सूची वितरण आहे, ज्यामधून प्रत्येक शहराला आणि प्रत्येक महिन्याला पाठवलेल्या कंटेनरची संख्या स्पष्टपणे पाहण्यासाठी तुम्हाला काही प्रकारचा सारांश तयार करणे आवश्यक आहे:

सोयीसाठी, कमांड वापरून आगाऊ "स्मार्ट" डेटासह टेबल बनवू मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित) आणि तिला एक नाव द्या वितरण टॅब रचनाकार (डिझाइन). भविष्यात, हे जीवन सोपे करेल, कारण. सारणीचे नाव आणि त्याचे स्तंभ थेट सूत्रांमध्ये वापरणे शक्य होईल.

पद्धत 1. सर्वात सोपी - पॉवर क्वेरी वापरा

Power Query हे Excel मध्ये डेटा लोड करण्यासाठी आणि रूपांतरित करण्यासाठी एक अतिशय शक्तिशाली साधन आहे. हे अॅड-इन एक्सेलमध्ये 2016 पासून डीफॉल्टनुसार तयार केले गेले आहे. तुमच्याकडे एक्सेल 2010 किंवा 2013 असल्यास, तुम्ही ते स्वतंत्रपणे डाउनलोड आणि स्थापित करू शकता (पूर्णपणे विनामूल्य).

संपूर्ण प्रक्रियेचे, स्पष्टतेसाठी, मी खालील व्हिडिओमध्ये चरण-दर-चरण विश्लेषण केले:

पॉवर क्वेरी वापरणे शक्य नसल्यास, तुम्ही इतर मार्गांनी जाऊ शकता - मुख्य सारणी किंवा सूत्राद्वारे. 

पद्धत 2. सहायक सारांश

आपल्या मूळ सारणीमध्ये आणखी एक स्तंभ जोडू या, जेथे साधे सूत्र वापरून आपण सारणीतील प्रत्येक पंक्तीची संख्या मोजतो:

साहजिकच, -1 आवश्यक आहे, कारण आमच्या टेबलमध्ये एक-लाइन हेडर आहे. जर तुमची सारणी शीटच्या सुरूवातीस नसेल, तर तुम्ही थोडे अधिक क्लिष्ट, परंतु सार्वत्रिक सूत्र वापरू शकता जे वर्तमान पंक्ती आणि सारणी शीर्षलेखातील फरकांची गणना करते:

आता, मानक पद्धतीने, आम्ही आमच्या डेटावर आधारित इच्छित प्रकारची पिव्होट टेबल तयार करू, परंतु मूल्य फील्डमध्ये आम्ही फील्ड टाकू. ओळ क्रमांक आम्हाला पाहिजे त्याऐवजी कंटेनर:

आमच्याकडे एकाच महिन्यात एकाच शहरात अनेक कंटेनर नसल्यामुळे, आमचा सारांश, प्रत्यक्षात रक्कम नाही तर आम्हाला आवश्यक असलेल्या कंटेनरचे लाइन क्रमांक देईल.

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

अशा प्रकारे, आम्ही आधीच निकालाच्या अर्ध्या मार्गावर आहोत: आमच्याकडे एक टेबल आहे जिथे, शहर आणि महिन्याच्या छेदनबिंदूवर, स्त्रोत सारणीमध्ये एक पंक्ती क्रमांक आहे, जिथे आम्हाला कंटेनर कोड आवश्यक आहे.

आता सारांश कॉपी करूया (त्याच शीटवर किंवा दुसर्‍या) आणि मूल्ये म्हणून पेस्ट करू आणि नंतर मूल्य क्षेत्रामध्ये आमचे सूत्र प्रविष्ट करा, जे सारांशमध्ये आढळलेल्या लाइन क्रमांकाद्वारे कंटेनर कोड काढेल:

कार्य IF (तर), या प्रकरणात, सारांशातील पुढील सेल रिक्त नाही हे तपासते. रिक्त असल्यास, रिक्त मजकूर स्ट्रिंग “” आउटपुट करा, म्हणजे सेल रिक्त सोडा. रिकामे नसल्यास, स्तंभातून काढा कंटेनर स्रोत सारणी वितरण फंक्शन वापरून पंक्ती क्रमांकानुसार सेल सामग्री INDEX (INDEX).

दुहेरी शब्द हा कदाचित इथे फारसा स्पष्ट नसलेला मुद्दा आहे कंटेनर सूत्र मध्ये. लेखनाचा असा विचित्र प्रकार:

पुरवठा[[कंटेनर]:[कंटेनर]]

… फक्त स्तंभाचा संदर्भ देण्यासाठी आवश्यक आहे कंटेनर निरपेक्ष होते (सामान्य "नॉन-स्मार्ट" सारण्यांसाठी $ चिन्हांसह संदर्भाप्रमाणे) आणि आमचे सूत्र उजवीकडे कॉपी करताना शेजारच्या स्तंभांवर सरकले नाही.

भविष्यात, स्रोत सारणीमध्ये डेटा बदलताना वितरण, त्यावर उजवे-क्लिक करून आणि कमांड निवडून आम्ही आमचा सहाय्यक सारांश रेखा क्रमांकांसह अद्यतनित करणे लक्षात ठेवले पाहिजे. अपडेट आणि सेव्ह करा (रिफ्रेश).

पद्धत 3. सूत्रे

या पद्धतीसाठी इंटरमीडिएट पिव्होट टेबल तयार करणे आणि मॅन्युअल अपडेट करणे आवश्यक नाही, परंतु एक्सेलचे "हेवी वेपन" - फंक्शन वापरते. SUMMESLIMN (SUMIFS). सारांशात पंक्ती क्रमांक पाहण्याऐवजी, तुम्ही हे सूत्र वापरून त्यांची गणना करू शकता:

काही बाह्य जडपणासह, खरेतर, निवडक समेशन फंक्शनसाठी हे मानक वापराचे प्रकरण आहे SUMMESLIMNA जो दिलेल्या शहरासाठी आणि महिन्यासाठी पंक्ती क्रमांकांची बेरीज करतो. पुन्हा, आमच्याकडे एकाच महिन्यात एकाच शहरात अनेक कंटेनर नसल्यामुळे, आमचे कार्य खरेतर रक्कम नाही तर रेखा क्रमांक स्वतःच देईल. आणि नंतर मागील पद्धतीपासून आधीच परिचित फंक्शन INDEX तुम्ही कंटेनर कोड देखील काढू शकता:

अर्थात, या प्रकरणात, आपल्याला यापुढे सारांश अद्यतनित करण्याबद्दल विचार करण्याची आवश्यकता नाही, परंतु मोठ्या टेबलवर, कार्य सुमेस्ली लक्षणीय मंद असू शकते. मग तुम्हाला सूत्रांचे स्वयंचलित अपडेटिंग बंद करावे लागेल किंवा पहिली पद्धत वापरावी लागेल - एक मुख्य सारणी.

जर तुमच्या अहवालासाठी सारांशाचा देखावा फारसा योग्य नसेल, तर तुम्ही त्यामधून पंक्ती क्रमांक थेट अंतिम तक्त्यामध्ये काढू शकता, जसे आम्ही केले नाही, परंतु फंक्शन वापरून. GET.PIVOT.TABLE.DATA (GET.PIVOT.DATA). हे कसे करायचे ते येथे आढळू शकते.

• मुख्य सारणी वापरून अहवाल कसा तयार करायचा
• मुख्य सारण्यांमध्ये गणना कशी सेट करावी
• SUMIFS, COUNTIFS, इ. सह निवडक मोजणी.