Microsoft Excel में Pivot Tables के साथ कार्य करना

PivotTables Microsoft Excel की सबसे शक्तिशाली विशेषताओं में से एक है। वे बड़ी मात्रा में डेटा का विश्लेषण करने और बस कुछ ही माउस क्लिक में सारांशित करने की अनुमति देते हैं। इस लेख में, हम PivotTables की खोज करते हैं, समझते हैं कि वे क्या हैं, और उन्हें बनाना और अनुकूलित करना सीखें.

नोट: यह लेख एक्सेल 2010 (बीटा) का उपयोग करके लिखा गया है। PivotTable की अवधारणा पिछले कुछ वर्षों में बदल गई है, लेकिन एक्सेल के लगभग हर पुनरावृत्ति में एक बनाने की विधि बदल गई है। यदि आप एक्सेल का एक संस्करण का उपयोग कर रहे हैं जो कि 2010 नहीं है, तो इस आलेख में देखे गए लोगों से अलग-अलग स्क्रीन की अपेक्षा करें.

थोड़ा इतिहास

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

उद्योग विश्लेषकों ने इसे दो कारकों में डाल दिया: सबसे पहले, लोटस ने फैसला किया कि "विंडोज" नामक यह फैंसी नया जीयूआई प्लेटफॉर्म एक गुजरने वाली सनक थी जो कभी भी दूर नहीं होगी। उन्होंने लोटस 1-2-3 (कुछ वर्षों के लिए, वैसे भी) का एक विंडोज संस्करण बनाने से इनकार कर दिया, यह भविष्यवाणी करते हुए कि सॉफ्टवेयर के उनके डॉस संस्करण सभी को कभी भी आवश्यकता होगी। Microsoft, स्वाभाविक रूप से, विशेष रूप से विंडोज के लिए एक्सेल विकसित किया है। दूसरे, Microsoft ने Excel के लिए एक सुविधा विकसित की जो लोटस ने 1-2-3 में प्रदान नहीं की, अर्थात् पिवट तालिकाएं.  एक्सेल के लिए विशेष रूप से PivotTables सुविधा को इतना कंपित करने वाला उपयोगी माना गया था कि लोग एक प्रोग्राम (1-2-3) के साथ स्टिक के बजाय एक पूरे नए सॉफ्टवेयर पैकेज (एक्सेल) को सीखने के लिए तैयार थे जो यह नहीं था। यह एक विशेषता, विंडोज की सफलता की गलतफहमी के साथ, लोटस 1-2-3 के लिए मौत की घंटी, और माइक्रोसॉफ्ट एक्सेल की सफलता की शुरुआत थी।.

PivotTables को समझना

तो क्या एक PivotTable है, बिल्कुल?

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

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

नीचे दिखाए गए डेटा की जांच करें:

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

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

यदि हम बिक्री लेनदेन की मूल सूची को ट्रैक करने के लिए थे, तो यह कुछ इस तरह दिख सकता है:

आपको यह जानकर आश्चर्य हो सकता है कि, Excel की PivotTable सुविधा का उपयोग करके, हम केवल कुछ माउस क्लिक के साथ, कुछ सेकंड में एक के समान मासिक बिक्री सारांश बना सकते हैं। हम ऐसा कर सकते हैं - और भी बहुत कुछ!

PivotTable कैसे बनाएँ

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

तो हम एक्सेल शुरू करते हैं ... और हम ऐसी सूची लोड करते हैं ...

एक बार जब हमारे पास एक्सेल में सूची खुली होती है, तो हम PivotTable बनाना शुरू करने के लिए तैयार होते हैं.

सूची के भीतर किसी एक एकल कक्ष पर क्लिक करें:

फिर, से सम्मिलित करें टैब पर क्लिक करें पिवट तालिका आइकन:

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

नई वर्कशीट हमारे लिए बनाई गई है, और उस वर्कशीट पर एक रिक्त PivotTable बनाया गया है:

एक अन्य बॉक्स भी दिखाई देता है: द PivotTable फ़ील्ड सूची.  जब भी हम PivotTable (ऊपर) के भीतर किसी भी सेल पर क्लिक करते हैं, तो यह फ़ील्ड सूची दिखाई जाएगी:

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

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

ध्यान दें कि (ए) फ़ील्ड की सूची में "राशि" फ़ील्ड अब टिक गई है, और "राशि का योग" को जोड़ा गया है मान बॉक्स, यह दर्शाता है कि राशि कॉलम को अभिव्यक्त किया गया है.

यदि हम स्वयं PivotTable की जांच करते हैं, तो हम वास्तव में कच्चे डेटा वर्कशीट से सभी "राशि" मानों का योग पाते हैं:

हमने अपना पहला PivotTable बनाया है! काम, लेकिन विशेष रूप से प्रभावशाली नहीं है। यह संभव है कि हमें अपने डेटा में थोड़ी अधिक जानकारी चाहिए.

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

अभी व, अंत में, चीजें दिलचस्प होने लगती हैं! हमारा PivotTable आकार लेना शुरू कर देता है ... .

कुछ क्लिकों के साथ हमने एक तालिका बनाई है जिसे मैन्युअल रूप से करने में लंबा समय लगेगा.

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

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

जो इस तरह दिखता है:

मिलने लगा बहुत ठंडा!

चलो इसे बनाते हैं तीन-आयामी तालिका। ऐसी तालिका संभवतः क्या दिख सकती है? अच्छा चलो देखते हैं…

"पैकेज" कॉलम / शीर्षक को ड्रैग करें रिपोर्ट फ़िल्टर डिब्बा:

ध्यान दें कि यह कहाँ समाप्त होता है ... .

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

और इसलिए, यदि आप इसके बारे में सही तरीके से सोचते हैं, तो हमारी PivotTable अब तीन आयामी है। चलो अनुकूलित करते रहें ...

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

आइए देखें कि ऐसा क्या दिखता है ... जैसा कि आप देख सकते हैं, "कैश" चला गया है.

प्रारूपण

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

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

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

मान फ़ील्ड सेटिंग्स बॉक्स प्रकट होता है.

दबाएं संख्या स्वरूप बटन, और मानक स्वरूप कक्ष बॉक्स प्रकट होता है:

वहाँ से वर्ग सूची, चयन (कहें) लेखांकन, और दशमलव स्थानों की संख्या 0. क्लिक करें ठीक PivotTable पर वापस जाने के लिए कुछ समय…

जैसा कि आप देख सकते हैं, संख्याओं को डॉलर की मात्रा के रूप में सही ढंग से स्वरूपित किया गया है.

जब हम प्रारूपण के विषय पर हैं, तो हम पूरे PivotTable को प्रारूपित करते हैं। ऐसा करने के कुछ तरीके हैं। चलो एक साधारण का उपयोग करें ...

दबाएं PivotTable उपकरण / डिज़ाइन टैब:

फिर नीचे के दाईं ओर स्थित तीर छोड़ें PivotTable शैलियाँ निर्मित शैलियों का एक विशाल संग्रह देखने के लिए सूची:

ऐसा कोई भी चुनें जो अपील करता है, और अपने PivotTable में परिणाम देखें:

अन्य विकल्प

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

सबसे पहले, "भुगतान विधि" कॉलम को हटा दें कॉलम लेबल बॉक्स (बस इसे फ़ील्ड सूची में वापस खींचें), और इसे "दिनांक बुक" कॉलम के साथ बदलें:

जैसा कि आप देख सकते हैं, यह हमारे PivotTable को तुरंत बेकार बना देता है, हमें प्रत्येक तिथि के लिए एक कॉलम देता है कि एक लेनदेन हुआ है - एक बहुत बड़ी तालिका!

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

ग्रुपिंग बॉक्स दिखाई देता है। हम चयन करते हैं महीने और ठीक पर क्लिक करें:

देखा! ए बहुत अधिक उपयोगी तालिका:

(संयोग से, यह तालिका इस लेख की शुरुआत में दिखाए गए मूल अक्षर के समान है - मूल बिक्री सारांश जो मैन्युअल रूप से बनाया गया था।)

एक और अच्छी बात यह है कि आप पंक्ति शीर्षकों (या स्तंभ शीर्षों) के एक से अधिक सेट कर सकते हैं:

... जो इस तरह दिखता है ... .

आप कॉलम शीर्षकों के साथ एक समान काम कर सकते हैं (या फ़िल्टर भी रिपोर्ट कर सकते हैं).

चीजों को फिर से सरल रखते हुए, आइए देखें कि कैसे साजिश की जाए औसतन मूल्यों, बजाय सुन्न मूल्यों.

सबसे पहले, "राशि का योग" पर क्लिक करें और चुनें मान फ़ील्ड सेटिंग ... प्रकट होने वाले संदर्भ-मेनू से:

में द्वारा मान फ़ील्ड को सारांशित करें में सूची मान फ़ील्ड सेटिंग्स बॉक्स, चयन करें औसत:

जब हम यहाँ हैं, तो आइए बदलते हैं अनुकूल नाम, "राशि का औसत" से कुछ अधिक संक्षिप्त। "औसत" जैसी किसी चीज़ में टाइप करें:

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

अगर हम चाहें, तो हम एक ही धुरी पर भी रकम, औसत और मायने रखता है (मायने रखता है कि कितनी बिक्री हुई)!

यहाँ कुछ इस तरह से प्राप्त करने के चरण हैं (खाली PivotTable से शुरू):

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

यहाँ हम क्या के साथ अंत है:

एक ही PivotTable पर कुल, औसत और गिनती!

निष्कर्ष

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

यदि आप Office 2007 में काम कर रहे हैं, तो आप एक्सेल 2007 में PivotTable बनाने के तरीके के बारे में हमारे लेख की जाँच कर सकते हैं.

हमने एक एक्सेल वर्कबुक को शामिल किया है जिसे आप अपने PivotTable कौशल का अभ्यास करने के लिए डाउनलोड कर सकते हैं। इसे 97 से एक्सेल के सभी संस्करणों के साथ काम करना चाहिए.

हमारे अभ्यास एक्सेल वर्कबुक डाउनलोड करें