एक्सेल में पिवट टेबल कैसे बनाएं। डेटा के साथ कैसे काम करें

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

स्रोत डेटा के लिए आवश्यकताएँ.

तो, किसी भी धुरी तालिका का आधार डेटा की एक सही ढंग से निर्मित सरणी है - "सही तालिका"। नीचे दिए गए चित्र में आप सही ढंग से निर्मित डेटा सरणी का एक उदाहरण देख सकते हैं:

आइए देखें कि वास्तव में इस तालिका की "शुद्धता" क्या है? सत्यता यह है कि:

• प्रत्येक कॉलम में केवल एक ही प्रकार का डेटा, कॉलम होता है एकॉलम में केवल तिथियाँ मेंकॉलम में केवल दस्तावेज़ साथकेवल ग्राहक, पैसे में पैसा, आपूर्तिकर्ताओं में आपूर्तिकर्ता, श्रेणियों में उत्पाद श्रेणियाँ इत्यादि... ऐसी तालिका में हम बहुत आसानी से उपयोग कर सकते हैं फ़िल्टर;
• एक ही प्रकार के डेटा वाले कॉलम दोहराए नहीं जाते हैं;
• तालिका में कुल पंक्तियाँ नहीं हैं, केवल "स्वच्छ डेटा" है;
• तालिका में पाठ डेटा में कोई खाली कक्ष नहीं हैं; प्रत्येक पंक्ति में ग्राहक, उत्पाद, आपूर्तिकर्ता, प्रबंधक, इत्यादि का नाम शामिल है...

वैसे, किसी सरणी में, इससे कोई फर्क नहीं पड़ता कि कॉलम किस क्रम में स्थित हैं और डेटा किस फ़ील्ड द्वारा क्रमबद्ध किया गया है। यह किसी भी तरह से पिवट टेबल के निर्माण को प्रभावित नहीं करता है।

और यहां एक "गलत टेबल" का उदाहरण दिया गया है, जिसमें से, यदि आप क्रैक भी करते हैं, तो पिवट टेबल नहीं बनाई जाएगी, और यदि बनाई भी जाती है, तो डेटा के साथ काम करना पूरी तरह से असंभव होगा... हमारा " पसंदीदा" लेखांकन प्रणालियाँ आमतौर पर हमें ऐसी तालिकाओं से "प्रसन्न" करती हैं, जो हमें रिपोर्ट के रूप में देती हैं जो आगे के विश्लेषण के लिए पूरी तरह से अनुपयुक्त हैं:

यहां "अमान्य सरणी" का एक और उदाहरण दिया गया है:

यहाँ "अनियमितताओं" का एक पूरा समूह है:

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

लेकिन यह सब एक अलग बातचीत के लिए है, यदि आप सीखना चाहते हैं कि ऐसी "टेढ़ी तालिकाओं" को जल्दी से "सही सरणियों" में कैसे परिवर्तित किया जाए, तो लेख पढ़ें: "1सी या एसएपी रिपोर्ट से जल्दी से पिवट टेबल कैसे बनाएं?" और "पिवट टेबल के लिए किसी टेबल को जल्दी से ऐरे में कैसे बदलें?"

दरअसल, पिवोट टेबल का निर्माण:

"सही सरणी" लें, कर्सर को सरणी के किसी भी सेल में रखें, मुख्य मेनू में "सम्मिलित करें" टैब चुनें, बाएं कोने में, "टेबल्स" अनुभाग में, "पिवट टेबल" बटन पर क्लिक करें:

खुलने वाले "पिवट टेबल बनाएं" संवाद बॉक्स में, "ओके" पर क्लिक करें:

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

आइए पिवोट टेबल स्थापित करना शुरू करें। हम किस प्रकार की कटौती चाहते हैं? प्रबंधक द्वारा उत्पाद श्रेणियाँ - कृपया। माउस को "श्रेणी" फ़ील्ड पर ले जाएँ, बाएँ माउस बटन से उस पर क्लिक करें और उसे "ROWS" फ़ील्ड पर खींचें। "प्रबंधक" फ़ील्ड को "कॉलम" में खींच लिया जाता है, और "राशि" को "मान" में खींच लिया जाता है:

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

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

आपका निदेशक आपको क्षेत्र के अनुसार सभी वस्तुओं की बिक्री पर एक संक्षिप्त रिपोर्ट बनाने का निर्देश देता है। समस्या को निम्नानुसार हल किया जा सकता है।

सबसे पहले, आइए एक टेबल लेआउट बनाएं, यानी एक हेडर जिसमें उत्पादों और क्षेत्रों के लिए अद्वितीय मान शामिल हों। आइए उत्पाद कॉलम की एक प्रतिलिपि बनाएँ और डुप्लिकेट हटाएँ। फिर, इन्सर्ट स्पेशल का उपयोग करके, हम कॉलम को एक पंक्ति में स्थानांतरित करते हैं। हम क्षेत्रों के साथ भी ऐसा ही करते हैं, केवल ट्रांसपोज़िंग के बिना। हमें रिपोर्ट का शीर्षक प्राप्त होगा.

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

आपने कार्य पूरा कर लिया है और निदेशक को रिपोर्ट दिखाएँ। मेज को देखते हुए, वह एक साथ कई बेहतरीन विचार उत्पन्न करता है।

— क्या राजस्व पर नहीं, बल्कि लाभ पर रिपोर्ट बनाना संभव है?

— क्या उत्पादों को पंक्तियों द्वारा और क्षेत्रों को स्तंभों द्वारा दिखाना संभव है?

— क्या प्रत्येक प्रबंधक के लिए अलग से ऐसी तालिकाएँ बनाना संभव है?

भले ही आप एक अनुभवी एक्सेल उपयोगकर्ता हों, नई रिपोर्ट बनाने में बहुत समय लगेगा। इसका तात्पर्य संभावित त्रुटियों का उल्लेख नहीं करना है। हालाँकि, यदि आप पिवट टेबल जानते हैं, तो उत्तर है: हाँ, मुझे 5 मिनट चाहिए, शायद कम।

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

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

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

अक्सर यहां कुछ भी बदलने की जरूरत नहीं पड़ती. ओके पर क्लिक करने के बाद, एक खाली पिवोटटेबल लेआउट के साथ एक नई एक्सेल शीट बनाई जाएगी।

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

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

पिवट टेबल में 4 क्षेत्र होते हैं, जो पैनल के नीचे स्थित होते हैं: मान, पंक्तियाँ, कॉलम, फ़िल्टर। आइए उनके उद्देश्य पर करीब से नज़र डालें।

मूल्यों की श्रृंखला- यह पिवट तालिका का केंद्रीय भाग है जिसमें मान चयनित विधि का उपयोग करके स्रोत डेटा को एकत्रित करके प्राप्त किए जाते हैं।
ज्यादातर मामलों में, एकत्रीकरण होता है योग. यदि चयनित फ़ील्ड में सभी डेटा संख्यात्मक प्रारूप में है, तो एक्सेल एक डिफ़ॉल्ट योग निर्दिष्ट करेगा। यदि स्रोत डेटा में कम से कम एक टेक्स्ट या खाली सेल है, तो योग के बजाय इसकी गणना की जाएगी मात्राकोशिकाएं. हमारे उदाहरण में, प्रत्येक कोशिका संबंधित क्षेत्र के सभी संगत उत्पादों का योग है।

आप PivotTable सेल में अन्य गणना विधियों का उपयोग कर सकते हैं। इसके लगभग 20 प्रकार हैं (औसत, न्यूनतम मूल्य, अनुपात, आदि)। गणना पद्धति को बदलने के कई तरीके हैं। सबसे सरल है पिवट टेबल में ही वांछित फ़ील्ड के किसी भी सेल पर राइट-क्लिक करना और एक अलग एकत्रीकरण विधि का चयन करना।

रेखा क्षेत्र- पंक्तियों के नाम जो सबसे बाएं कॉलम में स्थित हैं। ये सभी चयनित फ़ील्ड (कॉलम) के अद्वितीय मान हैं। पंक्ति क्षेत्र में कई फ़ील्ड हो सकते हैं, फिर तालिका बहु-स्तरीय हो जाती है। उत्पाद के नाम, महीने, क्षेत्र आदि जैसे गुणात्मक चर आमतौर पर यहां रखे जाते हैं।

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

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

निर्दिष्ट क्षेत्रों में फ़ील्ड जोड़कर और हटाकर, आप अपने डेटा के किसी भी टुकड़े को सेकंडों में अनुकूलित कर सकते हैं।

आइए देखें कि यह क्रिया में कैसे काम करता है। अभी के लिए, आइए वही तालिका बनाएं जो SUMIFS फ़ंक्शन का उपयोग करके पहले ही बनाई गई थी। ऐसा करने के लिए, क्षेत्र में खींचें मानफ़ील्ड "राजस्व", क्षेत्र में स्ट्रिंग्स"क्षेत्र" फ़ील्ड (बिक्री क्षेत्र) को खींचें कॉलम- "उत्पाद"।

परिणामस्वरूप, हमें एक वास्तविक पिवट तालिका प्राप्त होती है।

इसे बनाने में सचमुच 5-10 सेकंड का समय लगा। मौजूदा पिवट टेबल को संशोधित करना भी आसान है। आइए देखें कि निर्देशक की इच्छाएं कैसे आसानी से हकीकत में तब्दील हो जाती हैं।

आइए राजस्व को लाभ से बदलें।

माउस को खींचकर उत्पादों और क्षेत्रों की अदला-बदली भी की जा सकती है।

पिवट तालिकाओं को फ़िल्टर करने के लिए कई उपकरण उपलब्ध हैं। इस मामले में, हम बस "प्रबंधक" फ़ील्ड को फ़िल्टर क्षेत्र में रखेंगे।

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

आरंभिक डेटा

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

1. डेटा के लिए सबसे अच्छा प्रारूप एक्सेल स्प्रेडशीट है। यह अच्छा है क्योंकि प्रत्येक फ़ील्ड का एक नाम होता है और जब नई पंक्तियाँ जोड़ी जाती हैं, तो वे स्वचालित रूप से पिवट तालिका में शामिल हो जाती हैं।

2. कॉलम में समूहों को दोहराने से बचें। उदाहरण के लिए, सभी तिथियां एक फ़ील्ड में होनी चाहिए, और विभाजित नहीं होनी चाहिए, उदाहरण के लिए, महीने के अनुसार अलग-अलग कॉलम में।

3. अंतराल और खाली कोशिकाओं को हटा दें, अन्यथा यह पंक्ति विश्लेषण से बाहर हो सकती है।

4. फ़ील्ड पर उचित फ़ॉर्मेटिंग लागू करें. संख्याएँ संख्यात्मक प्रारूप में होनी चाहिए, तारीखें तारीखें होनी चाहिए। अन्यथा, समूहीकरण और गणितीय प्रसंस्करण के दौरान समस्याएँ उत्पन्न होंगी। लेकिन एक्सेल यहां आपकी मदद करेगा, क्योंकि... यह डेटा फॉर्मेट को काफी अच्छी तरह से निर्धारित करता है।

सामान्य तौर पर, कुछ आवश्यकताएँ होती हैं, लेकिन आपको उन्हें जानना चाहिए।

यदि आप स्रोत में परिवर्तन करते हैं (उदाहरण के लिए, नई पंक्तियाँ जोड़ना), तो पिवट तालिका तब तक नहीं बदलेगी जब तक आप इसे दाएँ माउस बटन का उपयोग करके ताज़ा नहीं करते

या
टैब में कमांड के माध्यम से डेटा - सभी को ताज़ा करें.

यह जानबूझकर किया गया था क्योंकि पिवट टेबल रैम में बहुत अधिक जगह लेती है। इसलिए, कंप्यूटर संसाधनों का अधिक आर्थिक रूप से उपयोग करने के लिए, काम सीधे स्रोत के साथ नहीं, बल्कि कैश के साथ किया जाता है, जहां स्रोत डेटा का एक स्नैपशॉट स्थित होता है।

इस बुनियादी स्तर पर भी पिवट तालिकाओं का उपयोग करके, आप बड़ी मात्रा में डेटा को संसाधित करने की गति और गुणवत्ता में उल्लेखनीय वृद्धि कर सकते हैं। यदि कुछ भी अस्पष्ट रह गया हो तो टिप्पणी में लिखें।

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

इस लेख को लिखते समय, मैंने एक्सेल 2010 का उपयोग किया। पिवट टेबल की अवधारणा कई वर्षों से काफी हद तक अपरिवर्तित रही है, लेकिन उन्हें बनाने का तरीका एक्सेल के प्रत्येक नए संस्करण में थोड़ा अलग है। यदि आपके पास 2010 के अलावा एक्सेल का कोई अन्य संस्करण है, तो तैयार रहें कि इस लेख में स्क्रीनशॉट आपकी स्क्रीन पर दिखाई देने वाले स्क्रीनशॉट से भिन्न होंगे।

थोड़ा इतिहास

स्प्रेडशीट बनाने के लिए कार्यक्रमों के विकास की शुरुआत में, लोटस के नियम 1-2-3 थे। इसका प्रभुत्व इतना पूर्ण था कि लोटस के विकल्प के रूप में अपना स्वयं का सॉफ़्टवेयर (एक्सेल) विकसित करने का माइक्रोसॉफ्ट का प्रयास समय की बर्बादी लग रहा था। अब 2010 की ओर तेजी से आगे बढ़ें! स्प्रेडशीट उद्योग में एक्सेल का प्रभुत्व लोटस के इतिहास में उससे कहीं अधिक है, और लोटस का उपयोग करने वाले लोगों की संख्या अभी भी शून्य के करीब पहुंच रही है। ऐसा कैसे हो सकता है? घटनाओं के इतने नाटकीय मोड़ का कारण क्या था?

विश्लेषक दो मुख्य कारकों की पहचान करते हैं:

• सबसे पहले, लोटस ने निर्णय लिया कि विंडोज़ नामक यह नया जीयूआई प्लेटफ़ॉर्म केवल एक प्रचलित सनक है जो लंबे समय तक नहीं टिकेगा। उन्होंने लोटस 1-2-3 का विंडोज़ संस्करण बनाने से इनकार कर दिया (लेकिन केवल कुछ वर्षों के लिए), यह अनुमान लगाते हुए कि उनके सॉफ़्टवेयर का डॉस संस्करण वह सब कुछ था जिसकी उपभोक्ताओं को कभी भी आवश्यकता होगी। बेशक, माइक्रोसॉफ्ट ने एक्सेल को विशेष रूप से विंडोज़ के लिए विकसित किया है।
• दूसरे, माइक्रोसॉफ्ट ने एक्सेल में पिवोट टेबल नामक एक टूल विकसित किया, जो लोटस 1-2-3 में उपलब्ध नहीं था। पिवट टेबल, एक एक्सेल एक्सक्लूसिव, इतनी अविश्वसनीय रूप से उपयोगी थी कि लोग लोटस 1-2-3 से चिपके रहने के बजाय नए एक्सेल सॉफ्टवेयर को सीखने के इच्छुक थे, जिसमें वे नहीं थे।

पिवट तालिकाओं ने, सामान्य रूप से विंडोज़ की सफलता को कम करके आंकने के साथ, लोटस 1-2-3 के लिए डेथ मार्च खेला और माइक्रोसॉफ्ट एक्सेल की सफलता की शुरुआत को चिह्नित किया।

पिवोट टेबल्स क्या हैं?

तो यह बताने का सबसे अच्छा तरीका क्या है कि पिवट टेबल क्या हैं?

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

PivotTables के साथ आप जिस डेटा का विश्लेषण करते हैं वह किसी भी प्रकार का डेटा नहीं हो सकता है। यह कच्चा डेटा होना चाहिए, किसी प्रकार की सूची की तरह। उदाहरण के लिए, यह पिछले छह महीनों में कंपनी में पूरी हुई बिक्री की सूची हो सकती है।

नीचे दिए गए चित्र में दिखाए गए डेटा को देखें:

कृपया ध्यान दें कि ये कच्चे डेटा नहीं हैं क्योंकि इनका सारांश पहले ही दिया जा चुका है। सेल बी3 में हम $30,000 देखते हैं, जो संभवतः जेम्स कुक द्वारा जनवरी में कमाया गया कुल योग है। तो फिर स्रोत डेटा कहां है? 30,000 डॉलर का आंकड़ा कहां से आया? वह मूल बिक्री सूची कहां है जिससे यह मासिक कुल प्राप्त हुआ था? यह स्पष्ट है कि किसी ने पिछले छह महीनों के सभी बिक्री डेटा को व्यवस्थित और क्रमबद्ध करने और उसे हमारे द्वारा देखी जाने वाली सारांश तालिका में बदलने के लिए बहुत प्रयास किया है। आपको क्या लगता है इसमें कितना समय लगा? घंटा? दस बजे?

तथ्य यह है कि उपरोक्त तालिका सारांश तालिका नहीं है। इसे अन्यत्र संग्रहीत कच्चे डेटा से मैन्युअल रूप से बनाया गया था और इसे संसाधित करने में कम से कम कुछ घंटे लगे। यह बिल्कुल उसी प्रकार की सारांश तालिका है जिसे पिवट तालिकाओं का उपयोग करके केवल कुछ सेकंड खर्च करके बनाया जा सकता है। आइए जानें कैसे...

यदि हम मूल बिक्री सूची पर वापस जाएं, तो यह कुछ इस तरह दिखाई देगी:

आपको आश्चर्य हो सकता है कि व्यापारिक लेनदेन की इस सूची से, पिवट तालिकाओं का उपयोग करके और कुछ ही सेकंड में, हम एक्सेल में एक मासिक बिक्री रिपोर्ट बना सकते हैं, जिसकी हमने ऊपर चर्चा की थी। हाँ, हम यह और बहुत कुछ कर सकते हैं!

पिवट टेबल कैसे बनाएं?

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

तो, आइए एक्सेल लॉन्च करें... और इस सूची को लोड करें...

एक बार जब हम इस सूची को एक्सेल में खोल लेते हैं, तो हम एक पिवट टेबल बनाना शुरू कर सकते हैं।

इस सूची में से कोई भी सेल चुनें:

फिर टैब पर डालना(सम्मिलित करें) कमांड का चयन करें पिवट तालिका(सार तालिका):

एक डायलॉग बॉक्स दिखाई देगा पिवोटटेबल बनाएं(एक पिवोटटेबल बनाना) आपके लिए दो प्रश्नों के साथ:

• नई पिवट तालिका बनाने के लिए मुझे किस डेटा का उपयोग करना चाहिए?
• मुझे पिवट टेबल कहाँ रखनी चाहिए?

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

एक्सेल एक नई वर्कशीट बनाएगा और उस पर एक खाली पिवट टेबल रखेगा:

जैसे ही हम पिवट टेबल में किसी भी सेल पर क्लिक करते हैं, एक और डायलॉग बॉक्स दिखाई देगा: पिवोटटेबल फ़ील्ड सूची(पिवोट टेबल फ़ील्ड्स)।

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

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

कृपया ध्यान दें कि शीर्षक मात्राअब एक चेक मार्क के साथ चिह्नित किया गया है, और क्षेत्र में मान(मान) प्रविष्टि दिखाई दी राशि का योग(राशि फ़ील्ड द्वारा योग), यह दर्शाता है कि कॉलम मात्रासारांश पेश करना

यदि हम पिवट टेबल को ही देखें तो हमें कॉलम के सभी मानों का योग दिखाई देगा मात्रामूल तालिका.

तो, हमारी पहली पिवट टेबल बन गई है! सुविधाजनक, लेकिन विशेष रूप से प्रभावशाली नहीं. हम संभवतः अपने डेटा के बारे में वर्तमान से अधिक जानकारी चाहते हैं।

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

यह और अधिक दिलचस्प होता जा रहा है! हमारी धुरी तालिका आकार लेना शुरू कर रही है...

क्या आपको लाभ दिखता है? कुछ ही क्लिक में, हमने एक तालिका बनाई जिसे मैन्युअल रूप से बनाने में बहुत लंबा समय लगेगा।

इसके अलावा हम और क्या कर सकते हैं? खैर, एक तरह से हमारी पिवट टेबल पहले से ही तैयार है। हमने कच्चे डेटा का एक उपयोगी सारांश बनाया है। महत्वपूर्ण जानकारी पहले ही प्राप्त हो चुकी है! इस लेख के बाकी हिस्से में, हम अधिक जटिल पिवट टेबल बनाने के कुछ तरीकों पर गौर करेंगे और सीखेंगे कि उन्हें कैसे अनुकूलित किया जाए।

पिवट टेबल की स्थापना

सबसे पहले, हम एक द्वि-आयामी पिवट तालिका बना सकते हैं। आइए कॉलम हेडर का उपयोग करके ऐसा करें भुगतान विधि(भुगतान विधि)। बस शीर्षक खींचें भुगतान विधिक्षेत्र के लिए कॉलम लेबल(कॉलम):

हमें परिणाम मिलता है:

बहुत बढ़िया लग रहा है!

आइए अब एक त्रि-आयामी तालिका बनाएं। ऐसी तालिका कैसी दिख सकती है? आइए एक नजर डालते हैं...

शीर्षक खींचें पैकेट(जटिल) क्षेत्र के लिए रिपोर्ट फ़िल्टर(फ़िल्टर):

ध्यान दें कि वह कहाँ समाप्त हुआ...

इससे हमें "किस अवकाश परिसर के लिए भुगतान किया गया" विशेषता के आधार पर रिपोर्ट को फ़िल्टर करने का अवसर मिलता है। उदाहरण के लिए, हम सभी कॉम्प्लेक्स के लिए विक्रेताओं और भुगतान विधियों द्वारा ब्रेकडाउन देख सकते हैं, या कुछ क्लिक में हम सारांश तालिका का दृश्य बदल सकते हैं और केवल उन लोगों के लिए वही ब्रेकडाउन दिखा सकते हैं जिन्होंने कॉम्प्लेक्स का ऑर्डर दिया था। सनसीकर्स.

तो, अगर आप इसे सही ढंग से समझते हैं, तो हमारी धुरी तालिका को त्रि-आयामी कहा जा सकता है। आइए कॉन्फ़िगर करना जारी रखें...

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

आइए देखें कि हमारी पिवट टेबल अब कैसी दिखती है। जैसा कि आप देख सकते हैं, कॉलम नकदउससे गायब हो गया.

एक्सेल में पिवोट टेबल्स को फ़ॉर्मेट करना

जाहिर है, पिवोटटेबल्स एक बहुत शक्तिशाली उपकरण है, लेकिन अब तक परिणाम थोड़े सादे और उबाऊ लगते हैं। उदाहरण के लिए, हम जो संख्याएँ जोड़ते हैं, वे डॉलर की रकम की तरह नहीं दिखतीं—वे केवल संख्याएँ हैं। आइए इसे ठीक करें.

ऐसी स्थिति में सामान्य क्रियाएं करना आकर्षक होता है और बस संपूर्ण तालिका (या संपूर्ण शीट) का चयन करें और वांछित प्रारूप सेट करने के लिए टूलबार पर मानक संख्या स्वरूपण बटन का उपयोग करें। इस दृष्टिकोण के साथ समस्या यह है कि यदि आप भविष्य में कभी भी पिवट टेबल की संरचना बदलते हैं (और ऐसा होने की 99% संभावना है), तो स्वरूपण खो जाएगा। हमें इसे (लगभग) स्थायी बनाने का एक तरीका चाहिए।

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

एक डायलॉग बॉक्स दिखाई देगा मान फ़ील्ड सेटिंग्स(मूल्य फ़ील्ड पैरामीटर)।

बटन को क्लिक करे संख्या स्वरूप(संख्या प्रारूप), एक संवाद बॉक्स प्रकट होता है प्रारूप कोशिकाएं(सेल प्रारूप):

सूची से वर्ग(संख्या प्रारूप) का चयन करें लेखांकन(वित्तीय) और दशमलव स्थानों की संख्या शून्य पर सेट करें। अब कई बार दबाएं ठीक हैहमारी धुरी तालिका पर वापस जाने के लिए।

जैसा कि आप देख सकते हैं, संख्याएँ अंततः डॉलर राशि के रूप में स्वरूपित की गईं।

अब जब हमने फ़ॉर्मेटिंग पूरी कर ली है, तो आइए संपूर्ण पिवट टेबल के लिए फ़ॉर्मेट सेट करें। इसे करने बहुत सारे तरीके हैं। आइए सरलतम का उपयोग करें...

टैब खोलें पिवोटटेबल उपकरण: डिज़ाइन(पिवट तालिकाओं के साथ कार्य करना: डिज़ाइनर):

किसी भी उपयुक्त शैली का चयन करें और परिणाम को अपनी पिवट तालिका में देखें:

एक्सेल में पिवट टेबल के लिए अन्य सेटिंग्स

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

सबसे पहले, प्रविष्टि हटाएँ. भुगतान विधिक्षेत्र से कॉलम लेबल(कॉलम)। ऐसा करने के लिए, इसे शीर्षकों की सूची में वापस खींचें, और इसके स्थान पर, शीर्षक को स्थानांतरित करें दिनांक बुक किया गया(बुक करने की तिथि):

जैसा कि आप देख सकते हैं, इसने अस्थायी रूप से हमारी पिवट टेबल को बेकार कर दिया है। एक्सेल ने प्रत्येक तारीख के लिए एक अलग कॉलम बनाया, जिस दिन कोई व्यापार किया गया था। परिणामस्वरूप, हमें एक बहुत विस्तृत तालिका मिली!

इसे ठीक करने के लिए, किसी भी तारीख पर राइट-क्लिक करें और संदर्भ मेनू से चयन करें समूह(समूह):

समूहीकरण संवाद बॉक्स प्रकट होता है. हम चुनते हैं महीने(महीने) और दबाएँ ठीक है:

वोइला! यह तालिका अधिक उपयोगी है:

वैसे, यह तालिका लेख की शुरुआत में दिखाई गई तालिका के लगभग समान है, जहां बिक्री परिणाम मैन्युअल रूप से संकलित किए गए थे।

एक और बहुत महत्वपूर्ण बिंदु है जिसे आपको जानना आवश्यक है! आप एक नहीं, बल्कि पंक्ति (या स्तंभ) शीर्षकों के कई स्तर बना सकते हैं:

...और यह इस तरह दिखेगा...

यही काम कॉलम हेडर (या फ़िल्टर) के साथ भी किया जा सकता है।

आइए मूल तालिका दृश्य पर वापस लौटें और देखें कि योग के बजाय औसत मान कैसे प्रदर्शित करें।

आरंभ करने के लिए, पर क्लिक करें राशि का योगऔर दिखाई देने वाले मेनू से, चुनें मान फ़ील्ड सेटिंग्स(मूल्य फ़ील्ड विकल्प):

सूची में मान फ़ील्ड को संक्षेप में प्रस्तुत करें(ऑपरेशन) डायलॉग बॉक्स में मान फ़ील्ड सेटिंग्स(मूल्य फ़ील्ड विकल्प) चुनें औसत(औसत):

साथ ही, जब तक हम यहां हैं, आइए बदलें प्रचलित नाम(कस्टम नाम) के साथ राशि का औसत(राशि फ़ील्ड द्वारा मात्रा) कुछ छोटी चीज़ के लिए। इस फ़ील्ड में कुछ इस तरह दर्ज करें औसत:

क्लिक ठीक हैऔर देखो क्या हुआ. ध्यान दें कि सभी मान कुल से औसत में बदल गए हैं, और तालिका का शीर्षक (ऊपरी बाएँ कक्ष में) बदल गया है औसत:

यदि आप चाहें, तो आप तुरंत योग, औसत और मात्रा (बिक्री) को एक धुरी तालिका में रख सकते हैं।

इसे कैसे करें, इसके बारे में एक चरण-दर-चरण मार्गदर्शिका यहां दी गई है, जिसकी शुरुआत एक खाली पिवट तालिका से होगी:

1. शीर्षक खींचें विक्रेता(बिक्री प्रतिनिधि) क्षेत्र के लिए कॉलम लेबल(कॉलम)।
2. शीर्षक को तीन बार खींचें मात्रा(लागत) क्षेत्र के लिए मान(मान)।
3. पहले क्षेत्र के लिए मात्रानाम बदलें कुल(राशि), और इस क्षेत्र में संख्या प्रारूप है लेखांकन(वित्तीय)। दशमलव स्थानों की संख्या शून्य है.
4. दूसरा क्षेत्र मात्रानाम औसतई, इसके लिए ऑपरेशन सेट करें औसत(औसत) और इस फ़ील्ड में संख्या प्रारूप को बदलें लेखांकन(वित्तीय) दशमलव स्थानों की संख्या शून्य के बराबर।
5. तीसरे क्षेत्र के लिए मात्राशीर्षक सेट करें गिनती करनाऔर उसके लिए एक ऑपरेशन - गिनती करना(मात्रा)
6. क्षेत्र में कॉलम लेबल(कॉलम) फ़ील्ड स्वचालित रूप से बनाई गई Σमूल्य(Σ मान) - इसे क्षेत्र में खींचें पंक्ति लेबल(स्ट्रिंग्स)

अंत में हमें यही मिलता है:

कुल, औसत और बिक्री की संख्या - सब कुछ एक धुरी तालिका में!

निष्कर्ष

माइक्रोसॉफ्ट एक्सेल में पिवोट टेबल में कई, कई फ़ंक्शन और सेटिंग्स शामिल हैं। इतना छोटा लेख उन सभी को कवर करने के करीब भी नहीं पहुंच सकता। पिवट टेबल की सभी क्षमताओं का पूरी तरह से वर्णन करने के लिए एक छोटी किताब या एक बड़ी वेबसाइट की आवश्यकता होगी। बहादुर और जिज्ञासु पाठक धुरी तालिकाओं का अन्वेषण जारी रख सकते हैं। ऐसा करने के लिए, बस पिवट टेबल के लगभग किसी भी तत्व पर राइट-क्लिक करें और देखें कि कौन से फ़ंक्शन और सेटिंग्स खुलती हैं। रिबन पर आपको दो टैब मिलेंगे: पिवोटटेबल उपकरण: विकल्प(विश्लेषण) और डिज़ाइन(निर्माता)। गलती करने से न डरें; आप कभी भी पिवट टेबल को हटा सकते हैं और दोबारा शुरू कर सकते हैं। आपके पास एक ऐसा अवसर है जो लंबे समय तक DOS और लोटस 1-2-3 उपयोगकर्ताओं के पास कभी नहीं था।

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

आपको बस मौजूदा पंक्तियों और स्तंभों में से पंक्तियों और स्तंभों के लिए आवश्यक शीर्षलेखों का चयन करना है। आप संपूर्ण पिवट तालिका पर या केवल पंक्तियों और स्तंभों के नाम में दर्शाए गए मानों पर भी फ़िल्टर लागू कर सकते हैं।

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

कैसे बनाये

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

पिवट टेबल बनाने के लिए, मूल से किसी भी सेल का चयन करें, फिर "इन्सर्ट" टैब पर जाएं और बटन पर क्लिक करें "पिवट तालिका".

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

खुली एक्सेल वर्कबुक में एक नई शीट बनाई जाएगी, जिस पर अभी भी खाली पिवट टेबल रखी जाएगी।

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

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

खेतों को कैसे व्यवस्थित करें

अब आइए तय करें कि हम डेटा का विश्लेषण किस सिद्धांत पर करेंगे। उदाहरण के लिए, आपको यह पता लगाना होगा कि किस विक्रेता ने प्रत्येक माह कौन सा उत्पाद और कितनी राशि में बेचा।

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

पंक्तियों के लिए, "उत्पाद" चुनें। इसी प्रकार, वांछित फ़ील्ड को क्षेत्र में खींचें "पंक्ति शीर्षक".

कृपया ध्यान दें कि आप एक क्षेत्र में एकाधिक फ़ील्ड सम्मिलित कर सकते हैं। उदाहरण के लिए, पंक्तियों में हम उत्पादों का चयन करेंगे और कीमत का संकेत देंगे। उत्पाद ड्रॉप-डाउन सूची बन गए हैं जो कीमत प्रदर्शित करते हैं। यदि आप पहले कीमत और फिर उत्पाद निर्दिष्ट करते हैं, तो ड्रॉप-डाउन सूची कीमत बन जाएगी। यहां फ़ील्ड का क्रम मायने रखता है.

यदि हमारे पास मूल तालिका में "इकाइयाँ" कॉलम था। फिर यह सारांश तालिका दिखाएगी कि किस विक्रेता ने, किस महीने में, एक निश्चित कीमत पर माल की कितनी इकाइयाँ बेचीं।

क्षेत्र के लिए "स्तंभ शीर्षक""दिनांक" फ़ील्ड खींचें. हर दिन की बिक्री प्रदर्शित करने के लिए नहीं, बल्कि, उदाहरण के लिए, महीने के अनुसार, किसी भी तारीख पर राइट-क्लिक करें और मेनू से "समूह" चुनें।

तालिका निम्नलिखित रूप लेगी.

अब "राशि" फ़ील्ड को "मान" क्षेत्र में खींचें।

जैसा कि आप देख सकते हैं, केवल संख्याएँ प्रदर्शित की गईं, हालाँकि मूल श्रेणी में इस कॉलम के लिए कोशिकाओं का संख्यात्मक प्रारूप निर्धारित किया गया था; यह मौद्रिक या वित्तीय भी हो सकता है। "दिनांक" कॉलम में, सेल प्रारूप भी उपयुक्त था - दिनांक।

इसे ठीक करने के लिए, सारांश में कक्षों की वांछित श्रेणी का चयन करें और दायाँ माउस बटन दबाएँ। फिर मेनू से चयन करें "संख्या स्वरूप".

अगली विंडो में, "न्यूमेरिकल" चुनें, आप बॉक्स को चेक कर सकते हैं "बिट समूह विभाजक"और "ओके" पर क्लिक करें।

डेटा के साथ कैसे काम करें

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

आप पंक्तियों और स्तंभों के लिए फ़िल्टर का भी उपयोग कर सकते हैं. उदाहरण में, ये उत्पाद और महीने हैं। उदाहरण के लिए, सूट और पैंट के बक्सों की जांच करके, आप यह पता लगा सकते हैं कि वे सभी विक्रेताओं द्वारा या किसी विशिष्ट विक्रेता द्वारा कितने में बेचे गए थे।

मान क्षेत्र में, आप फ़ील्ड के लिए सेटिंग्स कॉन्फ़िगर कर सकते हैं। उदाहरण मूल्यों का योग प्रदर्शित करता है: रोमा ने फरवरी में 1,800.00 की राशि में शर्ट बेचीं। देखते हैं कितने टुकड़े हैं. लाइन पर बायाँ-क्लिक करें "क्षेत्रानुसार योग..."और मेनू से चयन करें "मूल्य फ़ील्ड विकल्प".

अगली विंडो में, सूची से "मात्रा" चुनें, जो आपके मामले के लिए विशेष रूप से उपयुक्त है उसे चुनें, और "ओके" पर क्लिक करें।

अब, मूल्यों को देखते हुए, हम समझ सकते हैं कि रोमा ने फरवरी में दो शर्ट बेचीं।

आइए अब महीने के हिसाब से तालिका के लिए सामान्य फ़िल्टर बनाएं। हम क्षेत्र बदलते हैं: "रिपोर्ट फ़िल्टर" में हम "दिनांक" फ़ील्ड को अंदर खींचते हैं "स्तंभ शीर्षक"- "सेल्समैन" .

ऐसी सारांश तालिका दिखाएगी कि प्रत्येक विक्रेता ने पूरी अवधि या किसी विशिष्ट महीने के लिए कितना और किस प्रकार का सामान बेचा।

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

हम सभी जिस समस्या का सामना कर रहे हैं वह डेटा की कमी नहीं है; इसके विपरीत - ये बड़ी मात्रा में डेटा हैं! यही कारण है कि मैं एक बेहतरीन एक्सेल फ़ंक्शन का उपयोग करने की वकालत करता हूं - पिवट तालिकाएंअपने डेटा का सारांश और विश्लेषण करने के लिए।

PivotTables की शक्ति को देखना उतना ही सरल है जितना मूल डेटा की PivotTable के किसी संस्करण से तुलना करना; बस कुछ ही क्लिक के साथ, मैं राज्य के अनुसार किसी वस्तु की औसत कीमत देखने में सक्षम हो गया।

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

डेटा के साथ बेहतर काम के लिए हम इस पाठ को अपने अनुसार बनाएंगे। मैं आपको अपनी पांच पसंदीदा पिवोट टेबल सर्वोत्तम प्रथाएं दिखाऊंगा।

इस पूरे ट्यूटोरियल में मैं इस पेज पर माइक्रोसॉफ्ट द्वारा उपलब्ध कराए गए नमूना डेटा का उपयोग करूंगा। इस डेटा का उपयोग उदाहरणों को फिर से बनाने या मेरे द्वारा प्रदर्शित कार्यों का परीक्षण करने के लिए करें।

एक्सेल में उन्नत पिवोटटेबल तकनीकों का उपयोग कैसे करें (लघु वीडियो)

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

एक्सेल में 5 उन्नत पिवट टेबल ट्रिक्स

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

1. अनुभाग

स्लाइस- एक्सेल पिवट टेबल में शामिल डेटा को परिष्कृत करने के लिए एक पॉइंट-एंड-क्लिक टूल। एक स्लाइसर डालें और आप पिवट टेबल में शामिल डेटा को आसानी से बदल सकते हैं।

इस उदाहरण में मैंने प्रकार के लिए एक स्लाइस डाला है वस्तु. मैं क्लिक करने के बाद बैग, पिवट तालिका तालिका में केवल इस पैरामीटर को दिखाती है।

कई बार मैं डैशबोर्ड रिपोर्ट विकसित करता हूं जिनका उपयोग अन्य लोग करेंगे। स्लाइसर जोड़ने से अंतिम उपयोगकर्ता को रिपोर्ट को अपनी पसंद के अनुसार अनुकूलित करने में मदद मिल सकती है।

स्लाइसर जोड़ने के लिए, पिवट तालिका में क्लिक करें और टैब ढूंढें विश्लेषणएक्सेल रिबन पर.

इन कॉलमों को शामिल करने के लिए कई बॉक्स चेक करें, जिनमें से प्रत्येक एक स्लाइस होगा।

पकड़ना Ctrlएकाधिक स्लाइसर आइटम का चयन करने के लिए कीबोर्ड पर, जिसमें PivotTable डेटा के रूप में एक कॉलम से एकाधिक चयन शामिल होंगे।

2. समयरेखा

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

अनुभाग पर जाएँ विश्लेषण करें > टाइमलाइन सम्मिलित करेंटाइमलाइन जोड़ने के लिए, एक विशेष प्रकार का स्लाइसर जो दिनांक के आधार पर उन्नत एक्सेल पिवोटटेबल में शामिल डेटा का प्रबंधन करता है।

युक्ति: यदि यह सुविधा आपके लिए काम नहीं करती है, तो सुनिश्चित करें कि आपके स्रोत डेटा में दिनांक के रूप में स्प्रेडशीट में दिनांक स्वरूपित किया गया है।

टाइमलाइन जोड़ने के लिए, सुनिश्चित करें कि आपने पिवोटटेबल का चयन किया है (इसके अंदर क्लिक करें) और फिर बटन पर क्लिक करें विश्लेषण करें > टाइमलाइन सम्मिलित करेंएक्सेल रिबन पर. पॉप-अप विंडो में, दिनांक कॉलम (या एकाधिक कॉलम) चुनें और क्लिक करें ठीक हैएक समयरेखा बनाने के लिए.

पिवोटटेबल के लिए एक विशिष्ट समय सीमा का चयन करने के लिए टाइमलाइन विंडो में क्लिक करें और खींचें।

एक बार टाइमलाइन डालने के बाद, आप पिवट टेबल में जो शामिल है उसे बदलने के लिए टाइमलाइन पर हैंडल को क्लिक और स्थानांतरित कर सकते हैं।

आप निचले दाएं कोने में ड्रॉप-डाउन सूची पर क्लिक करके टाइमलाइन के काम करने के तरीके को बदल सकते हैं। उदाहरण के लिए, आप विशिष्ट तिथियों के बजाय त्रैमासिक या वार्षिक डेटा दिखाने के लिए समयरेखा बदल सकते हैं।

3. सारणीबद्ध दृश्य

Excel में PivotTables का मानक दृश्य एक झरने जैसा दिखता है; जैसे ही आप अधिक फ़ील्ड को पंक्तियों पर खींचते हैं, एक्सेल डेटा में अधिक "परतें" बनाता है।

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

चुनना डिज़ाइनर > रिपोर्ट लेआउट > सारणीबद्ध रूप में दिखाएंअपनी पिवट टेबल के साथ एक नियमित टेबल की तरह काम करने के लिए।

आपको तालिका दृश्य का उपयोग क्यों करना चाहिए? अपने PivotTable डेटा को क्लासिक सारणीबद्ध शैली में लाने से आप अधिक आसानी से डेटा फ़ार्मुलों को दर्ज कर सकेंगे या उन्हें एक अलग रिपोर्ट में सम्मिलित कर सकेंगे।

अपनी PivotTable को मानक पंक्तियों और स्तंभों जैसा दिखाने के लिए तालिका दृश्य का उपयोग करें।

अधिकांश समय, मैं एक्सेल के टेबल व्यू का उपयोग करना पसंद करता हूँ। यह एक मानक स्प्रेडशीट की तरह दिखता है और इसमें सूत्र लिखना और डेटा के साथ काम करना आसान है। मैं यह दृश्य भी ले सकता हूं और इसे एक नए टैब में पेस्ट कर सकता हूं।

4. परिकलित फ़ील्ड

परिकलित फ़ील्ड्स PivotTable में एक कॉलम जोड़ने का एक तरीका है जो स्रोत डेटा में नहीं है। आप काम करने के लिए पूरी तरह से नए क्षेत्र बनाने के लिए मानक गणित परिचालनों का उपयोग कर सकते हैं। दो मौजूदा कॉलम लें और पूरी तरह से नए कॉलम बनाने के लिए गणित का उपयोग करें।

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

परिकलित फ़ील्ड के साथ काम करना शुरू करने के लिए, पिवोटटेबल के अंदर क्लिक करके शुरुआत करें और फिर रिबन पर खोजें विश्लेषण. मेनू पर क्लिक करें फ़ील्ड, तत्व और सेटऔर फिर चुनें परिकलित फ़ील्ड.

उपयोग विश्लेषण > फ़ील्ड, सदस्य और सेट > परिकलित फ़ील्डपरिकलित फ़ील्ड को अपने PivotTable में सम्मिलित करने के लिए।

नई पॉप-अप विंडो में, परिकलित फ़ील्ड को एक नाम देकर प्रारंभ करें। मेरे मामले में मैं इसे कॉल करूंगा कुल ऑर्डर. कुल ऑर्डर मूल्य प्रत्येक इकाई की कीमत से गुणा की गई मात्रा है। फिर मैंने इस विंडो में फ़ील्ड की सूची में पहले शीर्षक फ़ील्ड (मात्रा) पर डबल-क्लिक किया।

कुल ऑर्डर लागत की गणना करने के लिए, मैंने मौजूदा इकाई मूल्य फ़ील्ड को गुणा किया ( यूनिट मूल्य) मात्रा फ़ील्ड पर ( मात्रा).

इस फ़ील्ड का नाम जोड़ने के बाद, मैं एक गुणन चिन्ह जोड़ूँगा * , और फिर कुल मात्रा (मात्रा) पर डबल-क्लिक करें। आइए आगे बढ़ें और क्लिक करें ठीक है.

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

यदि आप दो-स्तंभ अंकगणित का उपयोग नहीं करना चाहते हैं, तो आप परिकलित फ़ील्ड में अपने स्वयं के अंकगणितीय मान भी दर्ज कर सकते हैं। उदाहरण के लिए, यदि मैं प्रत्येक ऑर्डर के लिए केवल 5% बिक्री कर जोड़ना चाहता हूं, तो मैं गणना क्षेत्र में निम्नलिखित लिख सकता हूं:

बिक्री कर सहित लागत की गणना करने के लिए मैंने कुल ऑर्डर लागत को 1.05 से गुणा किया; आप मौजूदा फ़ील्ड के साथ संख्यात्मक मानों का उपयोग कर सकते हैं।

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

जाओ सम्मिलित करें >इस सुविधा को आज़माने के लिए.

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

पॉप-अप विंडो में स्रोत डेटा से पिवट टेबल बनाने के लिए कई विकल्प होते हैं। एक्सेल द्वारा प्रदान किए गए अनुशंसित पिवोटटेबल विकल्पों को देखने के लिए इस विंडो के बाईं ओर थंबनेल पर क्लिक करें।

पिवोटटेबल अनुशंसा सुविधा एक क्लिक में आपके डेटा का विश्लेषण करने के लिए कई विकल्प प्रदान करती है।

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

साथ ही, मुझे डेटा अन्वेषण उपकरण के रूप में यह सुविधा पसंद है। जब मैं डेटा की खोज शुरू करता हूं तो अगर मुझे नहीं पता कि मैं क्या ढूंढ रहा हूं, तो अनुशंसित एक्सेल पिवोट टेबल अक्सर मेरी तुलना में अधिक जानकारीपूर्ण होते हैं!

दोहराएँ और सीखते रहें (और भी अधिक एक्सेल पाठों के साथ)

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

ये पाठ एक्सेल और पिवोट टेबल्स के साथ आपके कौशल को अगले स्तर तक ले जाएंगे। उनकी बाहर जांच करो:

• ExcelZoo ने अपने लेख, पिवट टेबल्स में महारत हासिल करने के लिए 10 पाठ (अंग्रेजी में) में पिवट टेबल तकनीकों का एक शानदार अवलोकन दिया है।
• हमने Envato Tuts+ में शुरुआती ट्यूटोरियल के साथ पिवट टेबल को कवर किया है।
• माइक्रोसॉफ्ट एक्सेल के आसान परिचय के लिए, हमारी ट्यूटोरियल श्रृंखला देखें।

आप अब भी पिवट टेबल के बारे में क्या जानना चाहते हैं? इस ट्यूटोरियल के नीचे टिप्पणियों में मुझे अपने विचार या प्रश्न बताएं।