विषय-सूची
पिवट तालिकाएं एक्सेल में सबसे शक्तिशाली उपकरणों में से एक है। वे आपको केवल कुछ माउस क्लिक के साथ बड़ी मात्रा में डेटा के विभिन्न सारांशों का विश्लेषण और योग करने की अनुमति देते हैं। इस लेख में, हम पिवट टेबल से परिचित होंगे, समझेंगे कि वे क्या हैं, उन्हें बनाना और अनुकूलित करना सीखें।
यह लेख एक्सेल 2010 का उपयोग करते हुए लिखा गया था। पिवोटटेबल्स की अवधारणा पिछले कुछ वर्षों में ज्यादा नहीं बदली है, लेकिन जिस तरह से आप उन्हें बनाते हैं वह एक्सेल के प्रत्येक नए संस्करण में थोड़ा अलग है। यदि आपके पास एक्सेल का संस्करण 2010 नहीं है, तो तैयार रहें कि इस आलेख के स्क्रीनशॉट आपकी स्क्रीन पर दिखाई देने वाले से भिन्न होंगे।
इतिहास का हिस्सा
स्प्रेडशीट सॉफ्टवेयर के शुरुआती दिनों में, लोटस 1-2-3 रूल बॉल। इसका प्रभुत्व इतना पूर्ण था कि लोटस के विकल्प के रूप में अपने स्वयं के सॉफ़्टवेयर (एक्सेल) को विकसित करने के लिए माइक्रोसॉफ्ट के प्रयास समय की बर्बादी की तरह लग रहे थे। अब 2010 के लिए तेजी से आगे! एक्सेल स्प्रैडशीट्स पर हावी है, जो लोटस कोड ने अपने इतिहास में कभी किया है, और अभी भी लोटस का उपयोग करने वाले लोगों की संख्या शून्य के करीब है। यह कैसे हो सकता है? घटनाओं के इस तरह के नाटकीय मोड़ का कारण क्या था?
विश्लेषक दो मुख्य कारकों की पहचान करते हैं:
- सबसे पहले, लोटस ने फैसला किया कि विंडोज नामक यह नया जीयूआई प्लेटफॉर्म सिर्फ एक गुजरने वाला सनक था जो लंबे समय तक नहीं टिकेगा। उन्होंने लोटस 1-2-3 (लेकिन केवल कुछ वर्षों के लिए) का एक विंडोज़ संस्करण बनाने से इनकार कर दिया, यह भविष्यवाणी करते हुए कि उनके सॉफ़्टवेयर का डॉस संस्करण सभी उपभोक्ताओं की आवश्यकता होगी। माइक्रोसॉफ्ट ने विशेष रूप से विंडोज़ के लिए एक्सेल को स्वाभाविक रूप से विकसित किया है।
- दूसरा, माइक्रोसॉफ्ट ने एक्सेल में पिवोटटेबल्स नामक एक टूल पेश किया जो लोटस 1-2-3 में उपलब्ध नहीं था। एक्सेल के लिए विशेष रूप से पिवोटटेबल्स इतने अधिक उपयोगी साबित हुए कि लोगों ने लोटस 1-2-3 के साथ जारी रखने के बजाय नए एक्सेल सॉफ्टवेयर सूट के साथ रहना पसंद किया, जो उनके पास नहीं था।
PivotTables ने, सामान्य रूप से Windows की सफलता को कम करके आंकने के साथ, लोटस 1-2-3 के लिए डेथ मार्च खेला और Microsoft Excel की सफलता की शुरुआत की।
पिवट टेबल क्या हैं?
तो, पिवोटटेबल्स क्या हैं, यह बताने का सबसे अच्छा तरीका क्या है?
सरल शब्दों में, पिवट टेबल कुछ डेटा के सारांश हैं, जो इस डेटा के विश्लेषण को सुविधाजनक बनाने के लिए बनाए गए हैं। मैन्युअल रूप से बनाए गए योगों के विपरीत, Excel PivotTables इंटरैक्टिव हैं। एक बार बनाने के बाद, आप उन्हें आसानी से संशोधित कर सकते हैं यदि वे वह चित्र नहीं देते जिसकी आप आशा कर रहे थे। केवल कुछ माउस क्लिक के साथ, योग को फ़्लिप किया जा सकता है ताकि कॉलम शीर्षक पंक्ति शीर्षक बन जाएं और इसके विपरीत। पिवट टेबल से आप कई काम कर सकते हैं। पिवट टेबल की सभी विशेषताओं को शब्दों में वर्णित करने की कोशिश करने के बजाय, इसे व्यवहार में प्रदर्शित करना आसान है ...
PivotTables के साथ आप जिस डेटा का विश्लेषण करते हैं वह यादृच्छिक नहीं हो सकता। यह कच्चा कच्चा डेटा होना चाहिए, जैसे किसी प्रकार की सूची। उदाहरण के लिए, यह पिछले छह महीनों में कंपनी द्वारा की गई बिक्री की सूची हो सकती है।
नीचे दिए गए चित्र में दिखाए गए डेटा को देखें:
ध्यान दें कि यह कच्चा कच्चा डेटा नहीं है, क्योंकि इसे पहले ही संक्षेप में प्रस्तुत किया जा चुका है। सेल बी3 में हमें 30000 डॉलर दिखाई देते हैं, जो संभवत: जनवरी में जेम्स कुक द्वारा किया गया कुल परिणाम है। तब मूल डेटा कहां है? 30000 डॉलर का आंकड़ा कहां से आया? बिक्री की मूल सूची कहाँ से है जिससे यह मासिक योग प्राप्त हुआ है? यह स्पष्ट है कि किसी ने पिछले छह महीनों के सभी बिक्री डेटा को व्यवस्थित और क्रमबद्ध करने और इसे कुल योग तालिका में बदलने का बहुत अच्छा काम किया है। आपको क्या लगता है इसमें कितना समय लगा? घंटा? दस बजे?
तथ्य यह है कि ऊपर दी गई तालिका पिवट टेबल नहीं है। इसे कहीं और संग्रहीत कच्चे डेटा से हाथ से तैयार किया गया था और इसे संसाधित करने में कम से कम कुछ घंटे लगे। बस कुछ ही सेकंड में पिवट टेबल का उपयोग करके ऐसी सारांश तालिका बनाई जा सकती है। आइए जानें कैसे…
यदि हम मूल बिक्री सूची पर वापस जाते हैं, तो यह कुछ इस तरह दिखाई देगा:
आपको आश्चर्य हो सकता है कि पिवट टेबल की मदद से ट्रेडों की इस सूची से और कुछ ही सेकंड में, हम एक्सेल में एक मासिक बिक्री रिपोर्ट बना सकते हैं, जिसका हमने ऊपर विश्लेषण किया था। हाँ, हम वह और भी बहुत कुछ कर सकते हैं!
पिवट टेबल कैसे बनाएं?
सबसे पहले, सुनिश्चित करें कि आपके पास एक्सेल शीट में कुछ स्रोत डेटा है। वित्तीय लेनदेन की सूची सबसे विशिष्ट होती है। वास्तव में, यह किसी भी चीज़ की सूची हो सकती है: कर्मचारी संपर्क विवरण, सीडी संग्रह, या आपकी कंपनी का ईंधन खपत डेटा।
तो, हम एक्सेल शुरू करते हैं … और ऐसी सूची लोड करते हैं …
इस सूची को एक्सेल में खोलने के बाद, हम पिवट टेबल बनाना शुरू कर सकते हैं।
इस सूची से किसी भी सेल का चयन करें:
फिर टैब पर निवेशन (सम्मिलित करें) कमांड चुनें पिवट तालिका (पिवट तालिका):
एक डायलॉग बॉक्स दिखाई देगा पिवट टेबल बनाएं (एक पिवट टेबल बनाना) आपके लिए दो प्रश्नों के साथ:
- नई पिवट तालिका बनाने के लिए किस डेटा का उपयोग करना है?
- पिवट टेबल कहां लगाएं?
चूंकि पिछले चरण में हमने पहले ही सूची कक्षों में से एक का चयन कर लिया है, एक पिवट तालिका बनाने के लिए पूरी सूची स्वचालित रूप से चुनी जाएगी। ध्यान दें कि हम एक अलग श्रेणी, एक अलग तालिका, और यहां तक कि कुछ बाहरी डेटा स्रोत जैसे एक्सेस या एमएस-एसक्यूएल डेटाबेस तालिका का चयन कर सकते हैं। इसके अलावा, हमें यह चुनने की जरूरत है कि नई पिवट टेबल को कहां रखा जाए: नई शीट पर या मौजूदा में से किसी एक पर। इस उदाहरण में, हम विकल्प चुनेंगे - नई वर्कशीट (एक नई शीट के लिए):
एक्सेल एक नई शीट बनाएगा और उस पर एक खाली पिवट टेबल रखेगा:
जैसे ही हम पिवट टेबल में किसी भी सेल पर क्लिक करते हैं, एक और डायलॉग बॉक्स दिखाई देगा: PivotTable फ़ील्ड सूची (पिवट टेबल फील्ड)।
डायलॉग बॉक्स के शीर्ष पर फ़ील्ड की सूची मूल सूची से सभी शीर्षकों की एक सूची है। स्क्रीन के निचले भाग में चार खाली क्षेत्र आपको PivotTable को यह बताने की अनुमति देते हैं कि आप डेटा को कैसे सारांशित करना चाहते हैं। जब तक ये क्षेत्र खाली हैं, टेबल में भी कुछ नहीं है। हमें बस इतना करना है कि शीर्षकों को शीर्ष क्षेत्र से नीचे खाली क्षेत्रों में खींचें। उसी समय, हमारे निर्देशों के अनुसार, एक पिवट तालिका स्वचालित रूप से उत्पन्न होती है। यदि हम कोई गलती करते हैं, तो हम शीर्षकों को निचले क्षेत्र से हटा सकते हैं या उन्हें बदलने के लिए दूसरों को खींच सकते हैं।
क्षेत्र मान (अर्थ) शायद चारों में सबसे महत्वपूर्ण है। इस क्षेत्र में कौन सा शीर्षक रखा गया है यह निर्धारित करता है कि कौन सा डेटा सारांशित किया जाएगा (योग, औसत, अधिकतम, न्यूनतम, आदि) ये लगभग हमेशा संख्यात्मक मान होते हैं। इस क्षेत्र में किसी स्थान के लिए एक उत्कृष्ट उम्मीदवार शीर्षक के तहत डेटा है मूल्य (लागत) हमारी मूल तालिका की। इस शीर्षक को क्षेत्र में खींचें मान (मान):
कृपया ध्यान दें कि शीर्षक मूल्य अब एक चेकमार्क के साथ चिह्नित किया गया है, और क्षेत्र में मान (मान) एक प्रविष्टि दिखाई दी है राशि का योग (राशि फ़ील्ड राशि), यह दर्शाता है कि कॉलम मूल्य सारांश पेश करना।
यदि हम पिवट टेबल को ही देखें, तो हमें कॉलम से सभी मानों का योग दिखाई देगा मूल्य मूल तालिका।
तो, हमारी पहली पिवट टेबल बनाई गई है! सुविधाजनक, लेकिन विशेष रूप से प्रभावशाली नहीं। हम शायद अपने डेटा के बारे में वर्तमान की तुलना में अधिक जानकारी प्राप्त करना चाहते हैं।
आइए मूल डेटा की ओर मुड़ें और एक या अधिक स्तंभों की पहचान करने का प्रयास करें जिनका उपयोग इस योग को विभाजित करने के लिए किया जा सकता है। उदाहरण के लिए, हम अपनी पिवट टेबल इस तरह से बना सकते हैं कि बिक्री की कुल राशि की गणना प्रत्येक विक्रेता के लिए अलग-अलग की जाए। वे। कंपनी में प्रत्येक विक्रेता के नाम और उनकी कुल बिक्री राशि के साथ पंक्तियों को हमारी धुरी तालिका में जोड़ा जाएगा। इस परिणाम को प्राप्त करने के लिए, बस शीर्षक को खींचें बिक्री व्यक्ति (बिक्री प्रतिनिधि) क्षेत्र के लिए पंक्ति सूचक (स्ट्रिंग्स):
यह और दिलचस्प हो जाता है! हमारी PivotTable आकार लेने लगी है…
लाभ देखें? कुछ ही क्लिक में, हमने एक तालिका बनाई जिसे मैन्युअल रूप से बनाने में बहुत लंबा समय लगता।
हम और क्या कर सकते हैं? खैर, एक मायने में हमारी पिवट टेबल तैयार है। हमने मूल डेटा का एक उपयोगी सारांश बनाया है। महत्वपूर्ण जानकारी पहले ही प्राप्त हो चुकी है! इस लेख के शेष भाग में, हम अधिक जटिल PivotTables बनाने के कुछ तरीके देखेंगे, साथ ही उन्हें अनुकूलित करने का तरीका जानेंगे।
पिवोटटेबल सेटअप
सबसे पहले, हम एक द्वि-आयामी पिवट तालिका बना सकते हैं। आइए कॉलम हेडिंग का उपयोग करके ऐसा करते हैं भुगतान का तरीका (भुगतान का तरीका)। बस शीर्षक खींचें भुगतान का तरीका क्षेत्र के लिए कॉलम लेबल (कॉलम):
हमें परिणाम मिलता है:
बहुत मस्त लग रहा है!
अब एक त्रि-आयामी तालिका बनाते हैं। ऐसी तालिका कैसी दिखेगी? आइए देखते हैं…
ड्रैग हैडर पैकेज (जटिल) क्षेत्र के लिए रिपोर्ट फ़िल्टर (फ़िल्टर):
ध्यान दें कि वह कहाँ है ...
यह हमें "किस हॉलिडे कॉम्प्लेक्स के लिए भुगतान किया गया था" के आधार पर रिपोर्ट को फ़िल्टर करने का अवसर देता है। उदाहरण के लिए, हम विक्रेताओं द्वारा और सभी परिसरों के लिए भुगतान विधियों द्वारा, या कुछ माउस क्लिक में, पिवट तालिका के दृश्य को बदल सकते हैं और केवल उन लोगों के लिए एक ही ब्रेकडाउन दिखा सकते हैं जिन्होंने कॉम्प्लेक्स का आदेश दिया था सनसीकर्स.
तो, अगर आप इसे सही ढंग से समझते हैं, तो हमारी पिवट टेबल को त्रि-आयामी कहा जा सकता है। आइए सेट करना जारी रखें…
यदि यह अचानक पता चलता है कि केवल चेक और क्रेडिट कार्ड द्वारा भुगतान (यानी, कैशलेस भुगतान) को पिवट टेबल में प्रदर्शित किया जाना चाहिए, तो हम शीर्षक के प्रदर्शन को बंद कर सकते हैं रोकड़ (नकद)। इसके लिए बगल में कॉलम लेबल नीचे तीर पर क्लिक करें और ड्रॉप-डाउन मेनू में बॉक्स को अनचेक करें रोकड़:
आइए देखें कि हमारी पिवट टेबल अब कैसी दिखती है। जैसा कि आप देख सकते हैं, कॉलम रोकड़ उससे गायब हो गया।
एक्सेल में पिवोटटेबल्स को फॉर्मेट करना
PivotTables स्पष्ट रूप से एक बहुत शक्तिशाली उपकरण है, लेकिन अभी तक परिणाम थोड़े सादे और उबाऊ लगते हैं। उदाहरण के लिए, हम जो संख्याएँ जोड़ते हैं वे डॉलर की मात्रा की तरह नहीं दिखती हैं - वे केवल संख्याएँ हैं। आइए इसे ठीक करें।
ऐसी स्थिति में आप जो करने के आदी हैं, वह करना आकर्षक है और बस पूरी तालिका (या पूरी शीट) का चयन करें और वांछित प्रारूप सेट करने के लिए टूलबार पर मानक संख्या स्वरूपण बटन का उपयोग करें। इस दृष्टिकोण के साथ समस्या यह है कि यदि आप भविष्य में कभी भी पिवट तालिका की संरचना को बदलते हैं (जो 99% संभावना के साथ होता है), स्वरूपण खो जाएगा। हमें इसे (लगभग) स्थायी बनाने का एक तरीका चाहिए।
सबसे पहले, आइए पाते हैं प्रविष्टि राशि का योग in मान (मान) और उस पर क्लिक करें। दिखाई देने वाले मेनू में, आइटम का चयन करें मान फ़ील्ड सेटिंग्स (मान फ़ील्ड विकल्प):
एक डायलॉग बॉक्स दिखाई देगा मान फ़ील्ड सेटिंग्स (मान फ़ील्ड विकल्प)।
बटन को क्लिक करे संख्या स्वरूप (नंबर फॉर्मेट), एक डायलॉग बॉक्स खुलेगा। प्रारूप प्रकोष्ठों (सेल प्रारूप):
सूची से वर्ग (संख्या प्रारूप) चुनें लेखांकन (वित्तीय) और दशमलव स्थानों की संख्या शून्य पर सेट करें। अब कुछ बार दबाएं OKहमारी पिवट टेबल पर वापस जाने के लिए।
जैसा कि आप देख सकते हैं, संख्याओं को डॉलर की मात्रा के रूप में स्वरूपित किया गया है।
जब हम फ़ॉर्मेटिंग में होते हैं, तो चलिए संपूर्ण PivotTable के लिए फ़ॉर्मेट सेट करते हैं। इसे करने बहुत सारे तरीके हैं। हम उसी का उपयोग करते हैं जो सरल है …
दबाएं पिवोटटेबल टूल्स: डिज़ाइन (पिवोटटेबल्स के साथ काम करना: कंस्ट्रक्टर):
अगला, अनुभाग के निचले दाएं कोने में तीर पर क्लिक करके मेनू का विस्तार करें पिवोटटेबल शैलियाँ (पिवोटटेबल शैलियाँ) इनलाइन शैलियों का व्यापक संग्रह देखने के लिए:
कोई भी उपयुक्त शैली चुनें और परिणाम को अपनी पिवट तालिका में देखें:
एक्सेल में अन्य पिवटटेबल सेटिंग्स
कभी-कभी आपको दिनांक के अनुसार डेटा फ़िल्टर करने की आवश्यकता होती है। उदाहरण के लिए, हमारे ट्रेडों की सूची में कई तारीखें हैं। एक्सेल डेटा को दिन, महीने, साल आदि के आधार पर समूहबद्ध करने के लिए एक उपकरण प्रदान करता है। आइए देखें कि यह कैसे किया जाता है।
पहले प्रविष्टि को हटा दें। भुगतान का तरीका क्षेत्र से कॉलम लेबल (कॉलम)। ऐसा करने के लिए, इसे वापस शीर्षकों की सूची में खींचें, और इसके स्थान पर, शीर्षक को स्थानांतरित करें दिनांक बुक किया गया (बुकिंग की तारीख):
जैसा कि आप देख सकते हैं, इसने अस्थायी रूप से हमारी पिवट टेबल को बेकार कर दिया। एक्सेल ने प्रत्येक तिथि के लिए एक अलग कॉलम बनाया जिस पर एक व्यापार किया गया था। नतीजतन, हमें एक बहुत विस्तृत तालिका मिली!
इसे ठीक करने के लिए, किसी भी तिथि पर राइट-क्लिक करें और संदर्भ मेनू से चयन करें समूह (समूह):
ग्रुपिंग डायलॉग बॉक्स दिखाई देगा। हम चुनते हैं महीने (महीने) और क्लिक करें OK:
वोइला! यह तालिका बहुत अधिक उपयोगी है:
वैसे, यह तालिका लेख की शुरुआत में दिखाई गई तालिका के लगभग समान है, जहां बिक्री के योग को मैन्युअल रूप से संकलित किया गया था।
एक और बहुत महत्वपूर्ण बिंदु है जिसे आपको जानना आवश्यक है! आप एक नहीं, बल्कि पंक्ति (या कॉलम) शीर्षकों के कई स्तर बना सकते हैं:
... और यह इस तरह दिखेगा ...
कॉलम हेडिंग (या यहां तक कि फिल्टर) के साथ भी ऐसा ही किया जा सकता है।
आइए तालिका के मूल रूप में वापस आएं और देखें कि रकम के बजाय औसत कैसे प्रदर्शित किया जाए।
आरंभ करने के लिए, पर क्लिक करें राशि का योग और दिखाई देने वाले मेनू से चुनें मान फ़ील्ड सेटिंग्स (मान फ़ील्ड विकल्प):
सूचि मूल्य फ़ील्ड को संक्षेप में प्रस्तुत करें (ऑपरेशन) डायलॉग बॉक्स में मान फ़ील्ड सेटिंग्स (मान फ़ील्ड विकल्प) चुनें औसत (औसत):
साथ ही, जब तक हम यहां हैं, चलो बदलते हैं प्रचलित नाम (कस्टम नाम) के साथ राशि का औसत (राशि फ़ील्ड राशि) कुछ छोटा करने के लिए। इस क्षेत्र में कुछ इस तरह दर्ज करें औसत:
दबाएँ OK और देखो क्या होता है। ध्यान दें कि सभी मान योग से औसत में बदल गए हैं, और टेबल हेडर (ऊपरी बाएँ सेल में) बदल गया है औसत:
आप चाहें तो राशि, औसत और संख्या (बिक्री) को तुरंत एक पिवट टेबल में रख सकते हैं।
यह कैसे करना है, इस पर एक चरण-दर-चरण मार्गदर्शिका है, जो एक खाली पिवट तालिका से शुरू होती है:
- ड्रैग हैडर बिक्री व्यक्ति (बिक्री प्रतिनिधि) क्षेत्र के लिए कॉलम लेबल (कॉलम)।
- शीर्षक को तीन बार खींचें मूल्य (लागत) क्षेत्र के लिए मान (मान)।
- पहले क्षेत्र के लिए मूल्य शीर्षक को में बदलें कुल (राशि), और इस क्षेत्र में संख्या स्वरूप है लेखांकन (वित्तीय)। दशमलव स्थानों की संख्या शून्य है।
- दूसरा क्षेत्र मूल्य नाम averagई, इसके लिए ऑपरेशन सेट करें औसत (औसत) और इस क्षेत्र में संख्या स्वरूप भी बदल जाता है लेखांकन (वित्तीय) शून्य दशमलव स्थानों के साथ।
- तीसरे क्षेत्र के लिए मूल्य एक शीर्षक सेट करें गिनती और उसके लिए एक ऑपरेशन - गिनती (मात्रा)
- में कॉलम लेबल (कॉलम) फ़ील्ड स्वचालित रूप से बनाई गई मूल्य (Σ मान) - इसे क्षेत्र में खींचें पंक्ति सूचक (पंक्तियाँ)
यहाँ हम इसके साथ समाप्त करेंगे:
कुल राशि, औसत मूल्य और बिक्री की संख्या - सभी एक पिवट टेबल में!
निष्कर्ष
Microsoft Excel में पिवट टेबल में बहुत सारी सुविधाएँ और सेटिंग्स होती हैं। इतने छोटे से लेख में, वे उन सभी को कवर करने के करीब भी नहीं हैं। पिवट टेबल की सभी संभावनाओं का पूरी तरह से वर्णन करने के लिए एक छोटी सी किताब या एक बड़ी वेबसाइट की आवश्यकता होगी। बोल्ड और जिज्ञासु पाठक पिवट टेबल की खोज जारी रख सकते हैं। ऐसा करने के लिए, पिवट तालिका के लगभग किसी भी तत्व पर राइट-क्लिक करें और देखें कि कौन से फ़ंक्शन और सेटिंग्स खुलती हैं। रिबन पर आपको दो टैब मिलेंगे: पिवोटटेबल टूल्स: विकल्प (विश्लेषण) और डिज़ाइन (निर्माता)। गलती करने से न डरें, आप हमेशा PivotTable को हटा सकते हैं और फिर से शुरू कर सकते हैं। आपके पास एक ऐसा अवसर है जो लंबे समय तक डॉस और लोटस 1-2-3 के उपयोगकर्ताओं के पास कभी नहीं था।