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

2024 लेखक: Geoffrey Carr | [email protected]. अंतिम बार संशोधित: 2023-12-17 11:00

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

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

एक छोटा इतिहास

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

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

PivotTables को समझना

तो एक पिवोटटेबल क्या है, बिल्कुल?

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

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

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

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

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

अगर हम बिक्री लेनदेन की मूल सूची को ट्रैक करना चाहते थे, तो ऐसा कुछ ऐसा दिखाई दे सकता है:

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

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

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

सूची में किसी एक एकल सेल पर क्लिक करें:

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

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

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

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

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

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

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

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

अभी व आखिरकार, चीजें दिलचस्प हो रही हैं! हमारा पिवोटटेबल आकार लेना शुरू कर देता है …।

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

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

पाने के लिए शुरू हो रहा है बहुत ठंडा!

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

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

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

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

चलो देखते हैं कि यह कैसा दिखता है … जैसा कि आप देख सकते हैं, "नकद" चला गया है।

प्रारूपण

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

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

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

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

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

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

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

दबाएं पिवोटटेबल टूल्स / डिज़ाइन टैब:

फिर नीचे के दाएं तीर को छोड़ दें पिवोटटेबल शैलियाँ अंतर्निहित शैलियों का विशाल संग्रह देखने के लिए सूची:

अन्य विकल्प

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

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

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

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

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

इसके बारे में जागरूक होने वाली एक और अच्छी बात यह है कि आपके पास पंक्ति शीर्षलेखों (या कॉलम शीर्षलेख) के एक से अधिक सेट हो सकते हैं:

चीजों को फिर से सरल रखना, देखते हैं कि कैसे साजिश करना है औसतन मूल्यवान मूल्यों के बजाय मूल्य।

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

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

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

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

इस तरह कुछ ऐसा करने के लिए यहां दिए गए कदम हैं (एक खाली पिवोटटेबल से शुरू):

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

6. स्वचालित रूप से बनाए गए खींचें

   

   से क्षेत्र कॉलम लेबल सेवा मेरे पंक्ति सूचक

यहां हम क्या समाप्त करते हैं:

निष्कर्ष

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

यदि आप Office 2007 में काम कर रहे हैं, तो आप Excel 2007 में पिवोटटेबल बनाने के तरीके पर हमारे आलेख को देखना चाहेंगे।

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

हमारी प्रैक्टिस एक्सेल वर्कबुक डाउनलोड करें