मूल्यों में पाठ के साथ पिवट तालिका

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

आइए इस सीमा के आसपास जाने की कोशिश करें और इसी तरह की स्थिति में "दो बैसाखी" के साथ आएं।

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

सुविधा के लिए, आइए कमांड का उपयोग करके प्रारंभिक डेटा "स्मार्ट" के साथ तालिका बनाएं होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें) और उसे एक नाम दो प्रसव टैब निर्माता (डिज़ाइन). भविष्य में, यह जीवन को सरल करेगा, क्योंकि। तालिका और उसके स्तंभों के नाम का सीधे सूत्रों में उपयोग करना संभव होगा।

विधि 1. सबसे आसान - पावर क्वेरी का उपयोग करें

एक्सेल में डेटा लोड करने और बदलने के लिए पावर क्वेरी एक सुपर शक्तिशाली उपकरण है। यह ऐड-इन 2016 से डिफ़ॉल्ट रूप से एक्सेल में बनाया गया है। यदि आपके पास एक्सेल 2010 या 2013 है, तो आप इसे अलग से डाउनलोड और इंस्टॉल कर सकते हैं (पूरी तरह से मुफ्त)।

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

यदि पावर क्वेरी का उपयोग करना संभव नहीं है, तो आप अन्य तरीकों से जा सकते हैं - पिवट टेबल या फ़ार्मुलों के माध्यम से। 

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

आइए अपनी मूल तालिका में एक और कॉलम जोड़ें, जहां एक साधारण सूत्र का उपयोग करके हम तालिका में प्रत्येक पंक्ति की संख्या की गणना करते हैं:

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

अब, एक मानक तरीके से, हम अपने डेटा के आधार पर वांछित प्रकार की पिवट टेबल बनाएंगे, लेकिन मूल्य क्षेत्र में हम फ़ील्ड को छोड़ देंगे रेखा संख्या हम जो चाहते हैं उसके बजाय कंटेनर:

चूंकि हमारे पास एक ही महीने में एक ही शहर में कई कंटेनर नहीं हैं, इसलिए हमारा सारांश, वास्तव में, राशि नहीं, बल्कि उन कंटेनरों की लाइन नंबर देगा जिनकी हमें आवश्यकता है।

इसके अतिरिक्त, आप टैब पर भव्य और उप-योग को बंद कर सकते हैं कंस्ट्रक्टर - सामान्य योग и सबटोटल (डिजाइन - ग्रैंड टोटल, सबटोटल) और उसी स्थान पर बटन के साथ सारांश को अधिक सुविधाजनक तालिका लेआउट में बदलें मॉकअप की रिपोर्ट करें (रिपोर्ट लेआउट).

इस प्रकार, हम पहले से ही परिणाम के आधे रास्ते पर हैं: हमारे पास एक तालिका है, जहां शहर और महीने के चौराहे पर, स्रोत तालिका में एक पंक्ति संख्या होती है, जहां कंटेनर कोड की हमें आवश्यकता होती है।

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

समारोह IF (अगर), इस मामले में, जाँचता है कि सारांश में अगला सेल खाली नहीं है। यदि खाली है, तो एक खाली टेक्स्ट स्ट्रिंग "" आउटपुट करें, यानी सेल को खाली छोड़ दें। यदि खाली नहीं है, तो कॉलम से निकालें कंटेनर स्रोत तालिका प्रसव फ़ंक्शन का उपयोग करके पंक्ति संख्या द्वारा सेल सामग्री सूचकांक (अनुक्रमणिका).

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

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

... केवल कॉलम को संदर्भित करने की आवश्यकता है कंटेनर निरपेक्ष था (साधारण "गैर-स्मार्ट" तालिकाओं के लिए $ संकेतों के साथ एक संदर्भ की तरह) और हमारे सूत्र को दाईं ओर कॉपी करते समय पड़ोसी स्तंभों पर नहीं खिसका।

भविष्य में, स्रोत तालिका में डेटा बदलते समय प्रसव, हमें अपने सहायक सारांश को लाइन नंबरों के साथ उस पर राइट-क्लिक करके और कमांड चुनकर अपडेट करना याद रखना चाहिए अपडेट करें और सहेजें (ताज़ा करें).

विधि 3. सूत्र

इस पद्धति के लिए मध्यवर्ती पिवट तालिका और मैन्युअल अद्यतन के निर्माण की आवश्यकता नहीं है, लेकिन एक्सेल के "भारी हथियार" का उपयोग करता है - फ़ंक्शन सारांश (एसयूएमआईएफएस). सारांश में पंक्ति संख्याओं को देखने के बजाय, आप इस सूत्र का उपयोग करके उनकी गणना कर सकते हैं:

कुछ बाहरी भारीपन के साथ, वास्तव में, यह चयनात्मक योग फ़ंक्शन के लिए एक मानक उपयोग का मामला है सारांशए जो दिए गए शहर और महीने के लिए पंक्ति संख्याओं का योग करता है। फिर, चूंकि हमारे पास एक ही महीने में एक ही शहर में कई कंटेनर नहीं हैं, हमारा कार्य वास्तव में राशि नहीं, बल्कि लाइन नंबर देगा। और फिर फ़ंक्शन पिछली विधि से पहले से ही परिचित है सूचकांक आप कंटेनर कोड भी निकाल सकते हैं:

बेशक, इस मामले में, आपको अब सारांश को अपडेट करने के बारे में सोचने की ज़रूरत नहीं है, लेकिन बड़ी तालिकाओं पर, फ़ंक्शन सारांश उल्लेखनीय रूप से धीमा हो सकता है। फिर आपको फ़ार्मुलों के स्वचालित अद्यतन को बंद करना होगा, या पहली विधि का उपयोग करना होगा - एक पिवट टेबल।

यदि सारांश की उपस्थिति आपकी रिपोर्ट के लिए बहुत उपयुक्त नहीं है, तो आप इसमें से पंक्ति संख्याओं को सीधे अंतिम तालिका में नहीं निकाल सकते हैं, जैसा कि हमने किया था, लेकिन फ़ंक्शन का उपयोग करके प्राप्त करें।पिवट।टेबल।डेटा (प्राप्त करें। धुरी। डेटा). यह कैसे करें यहां पाया जा सकता है।

• पिवट टेबल का उपयोग करके रिपोर्ट कैसे बनाएं
• पिवट टेबल में कैलकुलेशन कैसे सेट करें
• SUMIFS, COUNTIFS, आदि के साथ चुनिंदा गिनती।