मुख्य सारणी कशी बदलायची. वेळ मालिका तयार करणे आणि गटबद्ध करणे

हे पोस्ट Excel मध्ये पिव्होट टेबलसह काम करण्यासाठी सोप्या आणि मोहक साधनांचा संग्रह सादर करते. इंग्रजीत टिप्स आणि ट्रिक्स कशाला म्हणतात. थोडा वेळ घ्या आणि येथे टिपा वाचा. कोणास ठाऊक, कदाचित तुम्हाला अशा प्रश्नाचे उत्तर सापडेल जे तुम्हाला बर्याच काळापासून त्रास देत आहे?

टीप 1: मुख्य सारण्या आपोआप अपडेट करा

काहीवेळा तुम्हाला मुख्य सारण्या आपोआप अपडेट करायच्या असतात. समजा तुम्ही व्यवस्थापकासाठी मुख्य सारणी तयार केली आहे. जोपर्यंत व्यवस्थापक तुम्हाला त्याच्या लॅपटॉपवर प्रवेश करण्याची परवानगी देत ​​नाही तोपर्यंत तुम्ही ते नियमितपणे अपडेट करू शकाल अशी शक्यता नाही. सक्षम केले जाऊ शकते स्वयंचलित अद्यतनमुख्य सारणी, जे प्रत्येक वेळी कार्यपुस्तिका उघडल्यानंतर कार्यान्वित केले जाईल:

1. PivotTable वर उजवे क्लिक करा आणि आत संदर्भ मेनूआयटम निवडा PivotTable पर्याय.
2. दिसणाऱ्या डायलॉग बॉक्समध्ये PivotTable पर्यायटॅब निवडा डेटा.
3. बॉक्स चेक करा फाइल उघडताना रिफ्रेश करा.

तांदूळ. 1. पर्याय सक्षम करा फाइल उघडताना रिफ्रेश करा

चेकबॉक्स फाइल उघडताना रिफ्रेश कराप्रत्येक मुख्य सारणीसाठी स्वतंत्रपणे सेट केले पाहिजे.

नोट डाउनलोड करा किंवा फॉरमॅट करा, उदाहरणे फॉरमॅटमध्ये (फाइलमध्ये VBA कोड आहे).

टीप २: सर्व वर्कबुक पिव्हट टेबल्स एकाच वेळी अपडेट करा

वर्कबुकमध्ये अनेक पिव्होट टेबल्स असल्यास, त्यांना एकाच वेळी अपडेट करणे समस्याप्रधान असू शकते. या अडचणींवर मात करण्याचे अनेक मार्ग आहेत:

पद्धत 1: तुम्ही प्रत्येक मुख्य सारणी सेट करू शकता जी कार्यपुस्तिका उघडता तेव्हा स्वयंचलितपणे अद्यतनित होण्यासाठी (तपशीलांसाठी टीप 1 पहा).

पद्धत 3: मागणीनुसार वर्कबुकमधील सर्व पिव्हट टेबल अपडेट करण्यासाठी VBA कोड वापरा. या दृष्टिकोनामध्ये वर्कबुक ऑब्जेक्टची RefreshAll पद्धत वापरणे समाविष्ट आहे. हे तंत्र वापरण्यासाठी, तयार करा नवीन मॉड्यूलआणि खालील कोड प्रविष्ट करा:

सब रिफ्रेश_सर्व()

हे वर्कबुक.सर्व रिफ्रेश करा

टीप 3: डेटा आयटम यादृच्छिक क्रमाने क्रमवारी लावा

अंजीर मध्ये. आकृती 2 पिव्होटटेबलमध्ये कोणत्या प्रदेशांमध्ये प्रदर्शित केले जातात ते डीफॉल्ट क्रम दर्शविते. प्रदेशांमध्ये वर्गीकरण केले आहे अक्षर क्रमानुसार: पश्चिम, उत्तर, मध्यपश्चिम आणि दक्षिण. तुमच्या कॉर्पोरेट धोरणांना आधी पश्चिम क्षेत्र, त्यानंतर मध्यपश्चिम, उत्तर आणि दक्षिण प्रदेश प्रदर्शित करणे आवश्यक असल्यास, मॅन्युअल क्रमवारी लावा. सेल C4 मध्ये फक्त मिडवेस्ट प्रविष्ट करा आणि की दाबा प्रविष्ट करा. प्रदेशांची क्रमवारी बदलेल.

टीप 4: पिव्होटटेबलला हार्डकोड मूल्यांमध्ये रूपांतरित करा

पिव्होट टेबल तयार करण्याचा उद्देश डेटा सारांशित करणे आणि योग्य स्वरूपात प्रदर्शित करणे हा आहे. मुख्य सारणीसाठी स्त्रोत डेटा स्वतंत्रपणे संग्रहित केला जातो, जो काही ओव्हरहेडचा परिचय देतो. PivotTable ला मूल्यांमध्ये रूपांतरित केल्याने तुम्हाला मूळ डेटा किंवा PivotTable कॅशेमध्ये प्रवेश न करता त्याचे परिणाम वापरता येतात. तुम्ही PivotTable कसे रूपांतरित करता ते संपूर्ण टेबल प्रभावित झाले आहे की त्याचा काही भाग यावर अवलंबून आहे.

PivotTable चा भाग रूपांतरित करण्यासाठी, या चरणांचे अनुसरण करा:

1. कॉपी करण्यासाठी PivotTable डेटा निवडा, उजवे-क्लिक करा आणि संदर्भ मेनूमधून निवडा कॉपी करा(किंवा तुमच्या कीबोर्डवर Ctrl+C टाइप करा).
2. वर्कशीटवर कुठेही उजवे-क्लिक करा आणि संदर्भ मेनूमधून कमांड निवडा घाला(किंवा Ctrl+V टाइप करा).

तुम्हाला संपूर्ण पिव्होटटेबल रूपांतरित करायचे असल्यास, या चरणांचे अनुसरण करा:

1. संपूर्ण मुख्य सारणी निवडा, उजवे-क्लिक करा आणि संदर्भ मेनूमधून निवडा कॉपी करा. जर मुख्य सारणीमध्ये FILTERS क्षेत्र नसेल, तर तुम्ही वापरू शकता कीबोर्ड शॉर्टकट Ctrl+Shift+*.
2. शीटवर कुठेही उजवे-क्लिक करा आणि संदर्भ मेनूमधून पर्याय निवडा विशेष घाला.
3. एक पर्याय निवडा मूल्येआणि क्लिक करा ठीक आहे.

PivotTable रूपांतरित करण्यापूर्वी, उप-टोटल काढून टाकणे चांगली कल्पना आहे कारण ते स्टँड-अलोन डेटासेटमध्ये विशेषतः उपयुक्त नाहीत. सर्व उप बेरीज हटवण्यासाठी, मेनू डिझायनर वर जा -> सबटोटल -> सबटोटल दर्शवू नका. विशिष्ट उप बेरजे काढण्यासाठी, ज्या सेलमध्ये उप बेरजे काढली जातात त्यावर उजवे-क्लिक करा. संदर्भ मेनूमधील आयटम निवडा फील्ड पर्यायआणि डायलॉग बॉक्समध्ये फील्ड पर्यायअध्यायात परिणामरेडिओ बटण निवडा नाही. बटण क्लिक केल्यानंतर ठीक आहेउपटोटल हटविली जातील.

टीप 5. भरणे रिक्त पेशीलाइन फील्डमध्ये

PivotTable रूपांतरित केल्यानंतर, वर्कशीट केवळ मूल्येच दाखवत नाही तर PivotTable ची संपूर्ण डेटा रचना देखील प्रदर्शित करते. उदाहरणार्थ, अंजीर मध्ये दर्शविलेले डेटा. 3 टॅब्युलर स्वरूपात लेआउटसह मुख्य सारणीच्या आधारावर प्राप्त केले गेले.

तांदूळ. 3. डाव्या बाजूला रिकामे सेल न भरता हे रूपांतरित पिव्होट टेबल वापरणे समस्याप्रधान आहे

कृपया लक्षात ठेवा की फील्ड प्रदेशआणि विक्री बाजारजेव्हा डेटा मुख्य सारणीच्या ROWS क्षेत्रामध्ये असतो तेव्हा उपस्थित असलेली समान पंक्ती रचना जतन करते. एक्सेल 2013 मध्ये आहे जलद मार्ग ROW क्षेत्रातील सेल मूल्यांसह भरणे. मुख्य सारणी भागात क्लिक करा आणि नंतर मेनूमधून नेव्हिगेट करा कन्स्ट्रक्टर -> अहवाल लेआउट -> (चित्र 4). त्यानंतर तुम्ही पिव्होट टेबलचे मूल्यांमध्ये रूपांतर करू शकता, जे तुम्हाला रिक्त स्थानांशिवाय डेटाचे सारणी देईल.

तांदूळ. 4. कमांड वापरल्यानंतर सर्व घटक लेबलांची पुनरावृत्ती करासर्व रिकाम्या पेशी भरल्या आहेत

टीप 6: PivotTable मध्ये अंकीय फील्ड रँकिंग करा

मोठ्या संख्येने डेटा घटक असलेल्या फील्डची क्रमवारी आणि रँकिंग करण्याच्या प्रक्रियेत, विश्लेषण केलेल्या डेटा घटकाची संख्यात्मक श्रेणी निश्चित करणे नेहमीच सोपे नसते. शिवाय, पिव्होट टेबलचे मूल्यांमध्ये रूपांतर केल्यास, पूर्णांक फील्डमध्ये प्रदर्शित केलेल्या प्रत्येक डेटा घटकाला संख्यात्मक रँक नियुक्त केल्यास, व्युत्पन्न केलेल्या डेटा सेटचे विश्लेषण मोठ्या प्रमाणात सुलभ होईल. अंजीर मध्ये दर्शविल्याप्रमाणे एक मुख्य सारणी उघडा. 5. कृपया लक्षात घ्या की समान सूचक - फील्ड विक्री खंडानुसार रक्कम- दोनदा प्रदर्शित केले जाते. इंडिकेटरच्या दुसऱ्या उदाहरणावर उजवे-क्लिक करा आणि संदर्भ मेनूमधून कमांड निवडा अतिरिक्त गणना -> सर्वात मोठ्या ते सर्वात लहान क्रमवारी लावा(चित्र 6.)

रँक तयार केल्यानंतर, तुम्ही फील्ड लेबल्स आणि फॉरमॅटिंग सानुकूलित करू शकता (आकृती 14.9). परिणाम एक सुंदर रँक अहवाल असेल.

टीप 7: PivotTable अहवालाचा आकार कमी करा

जेव्हा तुम्ही PivotTable अहवाल व्युत्पन्न करता, तेव्हा Excel डेटाचा स्नॅपशॉट तयार करतो आणि तो PivotTable कॅशेमध्ये संग्रहित करतो. पिव्होट टेबल कॅशे हे मेमरीचे एक विशेष क्षेत्र आहे जे जलद प्रवेशासाठी डेटा स्त्रोताची प्रत संग्रहित करते. दुसऱ्या शब्दांत, एक्सेल डेटाची एक प्रत तयार करते आणि नंतर वर्कबुकशी संबंधित कॅशेमध्ये संग्रहित करते. मुख्य सारणी कॅशे वर्कफ्लो ऑप्टिमायझेशन प्रदान करते. मुख्य सारणीमध्ये केलेले कोणतेही बदल, जसे की फील्डचे स्थान बदलणे, नवीन फील्ड जोडणे, किंवा कोणतेही घटक लपवणे, जलद पूर्ण केले जातात आणि सिस्टम संसाधनांसाठी आवश्यकता अधिक माफक असतात. पिव्होट टेबल कॅशेचा मुख्य तोटा असा आहे की प्रत्येक वेळी तुम्ही सुरवातीपासून पिव्होट टेबल तयार करता तेव्हा ते वर्कबुक फाइलचा आकार दुप्पट करते.

मूळ डेटा हटवा.वर्कबुकमध्ये स्त्रोत डेटासेट आणि पिव्होट टेबल असल्यास, त्याचा फाइल आकार दुप्पट होतो. म्हणून, तुम्ही मूळ डेटा सुरक्षितपणे हटवू शकता आणि यामुळे तुमच्या मुख्य सारणीच्या कार्यक्षमतेवर अजिबात परिणाम होणार नाही. मूळ डेटा हटवल्यानंतर, कार्यपुस्तिका फाइलची संकुचित आवृत्ती जतन करण्याचे सुनिश्चित करा. तुम्ही स्रोत डेटा हटवल्यानंतर, तुम्ही नेहमीप्रमाणे PivotTable वापरू शकता. एकमेव समस्या अशी आहे की स्त्रोत डेटा गहाळ झाल्यामुळे मुख्य सारणी अद्यतनित केली जाऊ शकत नाही. तुम्हाला मूळ डेटा हवा असल्यास, बेरीज क्षेत्रातील पंक्ती आणि स्तंभाच्या छेदनबिंदूवर डबल-क्लिक करा (चित्र 7 मध्ये हा सेल B18 आहे). यामुळे Excel ला PivotTable कॅशेची सामग्री नवीन वर्कशीटमध्ये डंप करते.

टीप 8: स्वयंचलितपणे विस्तारित होणारी डेटा श्रेणी तयार करा

तुम्ही कदाचित एकापेक्षा जास्त वेळा अशा परिस्थितीत आला असाल जेव्हा तुम्हाला दररोज मुख्य सारणी अहवाल अपडेट करावे लागले. जेव्हा डेटा स्त्रोतामध्ये नवीन रेकॉर्ड्स सतत जोडल्या जात असतात तेव्हा याची आवश्यकता बहुतेकदा उद्भवते. अशा प्रकरणांमध्ये, नवीन पिव्होटटेबलमध्ये नवीन रेकॉर्ड जोडण्यापूर्वी तुम्हाला पूर्वी वापरलेल्या श्रेणीची पुन्हा व्याख्या करावी लागेल. PivotTable साठी मूळ डेटा श्रेणी पुन्हा परिभाषित करणे कठीण नाही, परंतु जेव्हा तुम्हाला ते वारंवार करावे लागते तेव्हा ते कंटाळवाणे होऊ शकते.

पिव्होट टेबल तयार करण्यापूर्वी मूळ डेटा रेंज टेबलमध्ये रूपांतरित करणे हा या समस्येवर उपाय आहे. ना धन्यवाद एक्सेल सारण्यातुम्ही एक नामांकित श्रेणी तयार करू शकता जी त्यात असलेल्या डेटाच्या प्रमाणानुसार आपोआप विस्तारू शकते किंवा संकुचित होऊ शकते. तुम्ही तुमच्या डेटा सेटमध्ये बदलांचा मागोवा घेण्याची अनुमती देऊन कोणताही घटक, चार्ट, पिव्होट सारणी किंवा फॉर्म्युला श्रेणीशी संबद्ध करू शकता.

वर्णन केलेल्या तंत्राची अंमलबजावणी करण्यासाठी, स्त्रोत डेटा निवडा आणि नंतर टॅबवर असलेल्या टेबल चिन्हावर क्लिक करा घाला(चित्र 8) किंवा Ctrl+T (T इंग्रजी) दाबा. क्लिक करा ठीक आहेउघडणाऱ्या विंडोमध्ये. लक्षात ठेवा की तुम्हाला PivotTable मधील स्त्रोत डेटाची श्रेणी ओव्हरराइड करण्याची आवश्यकता नसली तरीही, जेव्हा तुम्ही PivotTable मधील श्रेणीमध्ये स्त्रोत डेटा जोडता तेव्हा तुम्हाला बटण क्लिक करावे लागेल. अपडेट करा.

टीप 9: PivotTable वापरून नियमित सारण्यांची तुलना करा

करत असाल तर तुलनात्मक विश्लेषणदोन भिन्न सारण्या, पिव्होट टेबल वापरणे सोयीचे आहे, जे वेळेची लक्षणीय बचत करेल. समजा दोन टेबल्स आहेत जी 2011 आणि 2012 साठी ग्राहकांची माहिती प्रदर्शित करतात (चित्र 9). या सारण्यांचे लहान आकार येथे फक्त उदाहरणे म्हणून दिले आहेत. सराव मध्ये, खूप मोठे टेबल वापरले जातात.

तुलना प्रक्रिया एक सारणी तयार करते ज्यामधून एक मुख्य सारणी तयार केली जाते. तुमच्याकडे या सारण्यांशी संबंधित डेटा लेबल करण्याचा मार्ग असल्याची खात्री करा. विचाराधीन उदाहरणामध्ये, यासाठी स्तंभ वापरला आहे आर्थिक वर्ष(अंजीर 10). तुम्ही दोन टेबल्स एकत्र केल्यानंतर, नवीन पिव्होट टेबल तयार करण्यासाठी परिणामी एकत्रित डेटा सेट वापरा. PivotTable स्तंभ क्षेत्राचा टेबल टॅग म्हणून वापर करण्यासाठी PivotTable फॉरमॅट करा (टेबलचे मूळ दर्शविणारा अभिज्ञापक). अंजीर मध्ये दाखवल्याप्रमाणे. 11, वर्षे स्तंभ क्षेत्रात आहेत आणि ग्राहक माहिती पंक्ती क्षेत्रात आहे. डेटा क्षेत्रामध्ये प्रत्येक ग्राहकासाठी विक्रीचे प्रमाण असते.

टीप 10: PivotTable स्वयंचलितपणे फिल्टर करा

तुम्हाला माहिती आहे की, ऑटोफिल्टर मुख्य सारण्यांमध्ये वापरले जाऊ शकत नाहीत. तथापि, PivotTable मध्ये ऑटोफिल्टर्स सक्षम करण्याची युक्ती आहे. या तंत्राचा वापर करण्याचे तत्व म्हणजे माऊस पॉइंटरला पिव्होट टेबलच्या शेवटच्या शीर्षकाच्या उजवीकडे (चित्र 12 मधील सेल D3) ठेवणे आणि नंतर रिबनवर जा आणि कमांड निवडा. डेटा -> फिल्टर करा. आतापासून, तुमच्या PivotTable मध्ये एक ऑटोफिल्टर दिसेल! उदाहरणार्थ, तुम्ही सरासरीपेक्षा जास्त व्यवहार दर असलेले सर्व ग्राहक निवडू शकता. ऑटोफिल्टर मुख्य सारणीमध्ये विश्लेषणाचा अतिरिक्त स्तर जोडतात.

टीप 11: PivotTables मध्ये प्रदर्शित डेटासेट रूपांतरित करा

PivotTable मध्ये रूपांतरित केलेल्या स्त्रोत डेटासाठी सर्वोत्तम लेआउट हे सारणी मांडणी आहे. या प्रकारच्या लेआउटमध्ये खालील वैशिष्ट्ये आहेत: कोणत्याही रिक्त पंक्ती किंवा स्तंभ नाहीत, प्रत्येक स्तंभात शीर्षक आहे, प्रत्येक फील्डमध्ये प्रत्येक पंक्तीमध्ये एक संबंधित मूल्य आहे आणि स्तंभांमध्ये डेटाचे पुनरावृत्ती होणारे गट नाहीत. व्यवहारात, आम्हाला अनेकदा आकृतीमध्ये दर्शविल्याप्रमाणे डेटा संच आढळतात. 13. तुम्ही बघू शकता, महिन्याची नावे टेबलच्या वरच्या काठावर एका ओळीत दिसतात, स्तंभ लेबले आणि वास्तविक डेटा म्हणून दुहेरी कर्तव्य देतात. यासारख्या सारणीपासून तयार केलेल्या पिव्होट टेबलमध्ये, यामुळे 12 फील्ड व्यवस्थापित करावी लागतील, प्रत्येक वेगळ्या महिन्याचे प्रतिनिधित्व करेल.

या समस्येचे निराकरण करण्यासाठी, तुम्ही मध्यवर्ती पायरी म्हणून अनेक एकत्रित श्रेणींसह मुख्य सारणी वापरू शकता (अधिक तपशीलांसाठी, पहा). मॅट्रिक्स शैली असलेल्या डेटा सेटला मुख्य सारण्या तयार करण्यासाठी अधिक योग्य असलेल्या डेटा सेटमध्ये रूपांतरित करण्यासाठी, या चरणांचे अनुसरण करा:

पायरी 1: सर्व नॉन-कॉलम फील्ड एका कॉलममध्ये एकत्र करा.एकाधिक एकत्रित श्रेणींसह मुख्य सारण्या तयार करण्यासाठी, तुम्ही एकल आयाम स्तंभ तयार करणे आवश्यक आहे. या उदाहरणात, महिन्याच्या फील्डशी संबंधित नसलेली प्रत्येक गोष्ट एक परिमाण मानली जाते. त्यामुळे फील्ड विक्री बाजारआणि सेवेचे वर्णनएका स्तंभात एकत्र केले पाहिजे. एका स्तंभात फील्ड एकत्र करण्यासाठी, फक्त एक सूत्र प्रविष्ट करा जे परिसीमक म्हणून अर्धविराम वापरून दोन फील्ड एकत्र करते. नवीन स्तंभाला एक नाव द्या. प्रविष्ट केलेले सूत्र सूत्र बारमध्ये प्रदर्शित केले आहे (चित्र 14).

तांदूळ. 14. स्तंभ एकत्रीकरणाचा परिणाम विक्री बाजारआणि सेवेचे वर्णन

एकत्रित स्तंभ तयार केल्यानंतर, सूत्रांचे मूल्यांमध्ये रूपांतर करा. हे करण्यासाठी, नवीन तयार केलेला स्तंभ निवडा, Ctrl+C दाबा आणि नंतर कमांड चालवा घाला -> विशेष घाला -> मूल्ये. आता तुम्ही स्तंभ काढू शकता विक्री बाजारआणि सेवेचे वर्णन(अंजीर 15).

तांदूळ. 15. स्तंभ काढले विक्री बाजारआणि सेवेचे वर्णन

पायरी 2: एकाधिक एकत्रीकरण श्रेणीसह एक मुख्य सारणी तयार करा.आता आपल्याला बर्याच वापरकर्त्यांना परिचित असलेल्या कॉलवर कॉल करण्याची आवश्यकता आहे मागील आवृत्त्यामुख्य सारण्या आणि चार्टचा एक्सेल मास्टर. या विझार्डला कॉल करण्यासाठी, की संयोजन Alt+D+P दाबा. दुर्दैवाने, हा कीबोर्ड शॉर्टकट Excel 2013 च्या इंग्रजी आवृत्तीसाठी आहे. रशियन आवृत्तीते Alt+D+N या की संयोजनाशी संबंधित आहे. परंतु मला अज्ञात कारणांमुळे ते कार्य करत नाही. तथापि, तुम्ही पॅनेलवर चांगला जुना पिव्होट टेबल विझार्ड प्रदर्शित करू शकता द्रुत प्रवेश, सेमी. . विझार्ड सुरू केल्यानंतर, स्विच निवडा अनेक एकत्रीकरण श्रेणींमध्ये. क्लिक करा पुढील. स्विच सेट करा पृष्ठ फील्ड तयार कराआणि क्लिक करा पुढील. कार्यरत श्रेणी निश्चित करा आणि क्लिक करा तयार(तपशील बघा). तुम्ही एक मुख्य सारणी तयार कराल (आकृती 16).

पायरी 3: एकूण एकूण पंक्तीमधील पंक्ती आणि स्तंभाच्या छेदनबिंदूवर डबल-क्लिक करा.या टप्प्यावर, तुमच्याकडे एक सारांश सारणी असेल (चित्र 16), ज्यामध्ये अनेक एकत्रीकरण श्रेणी समाविष्ट आहेत, जे व्यावहारिकदृष्ट्या निरुपयोगी आहे. पंक्ती आणि एकूण स्तंभाच्या छेदनबिंदूवरील सेल निवडा आणि त्यावर डबल-क्लिक करा (आमच्या उदाहरणात, सेल N88). तुम्हाला एक नवीन शीट मिळेल, ज्याची रचना अंजीर मध्ये दर्शविलेल्या संरचनेसारखी आहे. 17. हे शीट खरेतर मूळ डेटाची ट्रान्सपोज केलेली आवृत्ती आहे.

पायरी 4. पंक्ती स्तंभ स्वतंत्र फील्डमध्ये विभाजित करणे.तो स्तंभ विभाजित करण्यासाठी राहते ओळस्वतंत्र फील्डमध्ये (मूळ संरचनेकडे परत या). स्तंभानंतर लगेच एक रिक्त स्तंभ जोडा ओळ. स्तंभ A हायलाइट करा आणि नंतर रिबन टॅबवर जा डेटाआणि बटणावर क्लिक करा स्तंभ मजकूर. स्क्रीनवर एक डायलॉग बॉक्स दिसेल स्तंभांमध्ये मजकूर वितरित करण्यासाठी विझार्ड. पहिल्या चरणात, रेडिओ बटण निवडा परिसीमकांसहआणि Next बटणावर क्लिक करा. पुढील चरणात, रेडिओ बटण निवडा अर्धविरामआणि क्लिक करा तयार. मजकूर फॉरमॅट करा, शीर्षक जोडा आणि Ctrl+T दाबून मूळ डेटा टेबलमध्ये बदला (चित्र 18).

तांदूळ. 18. हा डेटासेट मुख्य सारणी तयार करण्यासाठी आदर्श आहे (आकृती 13 शी तुलना करा)

टीप 12: PivotTable मध्ये दोन नंबर फॉरमॅटचा समावेश करणे

आता अशा परिस्थितीचा विचार करूया जिथे सामान्यीकृत डेटा सेट विश्लेषणासाठी सोयीस्कर पिव्होट टेबल तयार करणे कठीण करते. एक उदाहरण अंजीर मध्ये दर्शविले आहे. 19 टेबल, ज्यामध्ये प्रत्येक विक्री बाजारासाठी दोन भिन्न निर्देशक समाविष्ट आहेत. टीप स्तंभ D, जो निर्देशक ओळखतो.

जरी हे सारणी चांगल्या स्वरूपनाचे उदाहरण असू शकते, परंतु हे सर्व काही चांगले नाही. कृपया लक्षात घ्या की काही मेट्रिक्स संख्यात्मक स्वरूपात प्रदर्शित करणे आवश्यक आहे, तर काही टक्केवारीच्या स्वरूपात प्रदर्शित करणे आवश्यक आहे. पण मूळ डेटाबेसमध्ये फील्ड अर्थडबल प्रकार आहे. जेव्हा तुम्ही डेटासेटवरून PivotTable तयार करता, तेव्हा तुम्ही एकाच फील्डला दोन भिन्न संख्या स्वरूप नियुक्त करू शकत नाही अर्थ. येथे एक साधा नियम लागू होतो: एक फील्ड एक नंबर फॉरमॅटशी संबंधित आहे. नियुक्त करण्याचा प्रयत्न करत आहे संख्या स्वरूपफील्ड ज्याला टक्केवारी स्वरूप नियुक्त केले गेले आहे ते टक्केवारी मूल्ये नियमित संख्यांमध्ये बदलण्यास कारणीभूत होतील जे टक्के चिन्हाने समाप्त होतात (आकृती 20).

या समस्येचे निराकरण करण्यासाठी, एक सानुकूल क्रमांक स्वरूप वापरले जाते जे 1.5 पेक्षा मोठे कोणतेही मूल्य संख्या म्हणून स्वरूपित करते. मूल्य 1.5 पेक्षा कमी असल्यास, ते टक्केवारी म्हणून स्वरूपित केले जाते. डायलॉग बॉक्समध्ये सेल स्वरूपटॅब निवडा (सर्व स्वरूप)आणि शेतात प्रकारखालील फॉरमॅट स्ट्रिंग टाका (चित्र 21): [>=1.5]$# ##0; [<1,5]0,0%

तांदूळ. 21. सानुकूल क्रमांक स्वरूप लागू करा ज्यामध्ये 1.5 पेक्षा कमी संख्या टक्केवारी म्हणून स्वरूपित केली जाते.

प्राप्त परिणाम अंजीर मध्ये दर्शविले आहे. 22. तुम्ही बघू शकता, प्रत्येक निर्देशक आता योग्यरित्या फॉरमॅट केलेला आहे. अर्थात, या सल्ल्यामध्ये दिलेली कृती सार्वत्रिक नाही. उलट, ते कोणत्या दिशेने प्रयोग करायचे ते दाखवते.

टीप 13: मुख्य सारणीसाठी वारंवारता वितरण तयार करा

आपण कधीही Excel फंक्शन वापरून वारंवारता वितरण तयार केले असल्यास वारंवारता, तर तुम्हाला कदाचित माहित असेल की हे खूप कठीण काम आहे. शिवाय, डेटा श्रेणी बदलल्यानंतर, सर्वकाही पुन्हा सुरू करावे लागेल. या विभागात, तुम्ही साध्या पिव्होट टेबलचा वापर करून साधे वारंवारता वितरण कसे तयार करायचे ते शिकाल. प्रथम, त्याच्या पंक्ती क्षेत्रामध्ये डेटासह एक मुख्य सारणी तयार करा. अंजीरकडे लक्ष द्या. 23, जेथे ओळ क्षेत्रात एक फील्ड आहे खंड विक्री.

पंक्ती क्षेत्रातील कोणत्याही मूल्यावर उजवे-क्लिक करा आणि संदर्भ मेनूमधून पर्याय निवडा गट. डायलॉग बॉक्समध्ये गटबाजी(Fig. 24) पॅरामीटर्सची मूल्ये निर्धारित करतात जी वारंवारता वितरणाची सुरुवात, शेवट आणि पायरी निर्धारित करतात. ओके क्लिक करा.

तांदूळ. 24. डायलॉग बॉक्समध्ये गटबाजीवारंवारता वितरण पॅरामीटर्स कॉन्फिगर करा

तुम्ही मुख्य सारणीमध्ये फील्ड जोडल्यास ग्राहक(चित्र 25), आम्ही ऑर्डरच्या आकाराशी (डॉलर्समध्ये) ग्राहक व्यवहारांची वारंवारता वितरण प्राप्त करतो.

तांदूळ. 25. ऑर्डरच्या आकारानुसार (डॉलर्समध्ये) ग्राहक व्यवहारांचे वितरण आता तुमच्या हातात आहे.

या तंत्राचा फायदा असा आहे की इतर स्तंभांवर आधारित डेटा परस्पर फिल्टर करण्यासाठी पिव्होटटेबल अहवाल फिल्टर वापरला जाऊ शकतो, जसे की प्रदेशआणि विक्री बाजार. वापरकर्त्याकडे पंक्ती क्षेत्रातील कोणत्याही संख्येवर उजवे-क्लिक करून आणि नंतर पर्याय निवडून वारंवारता वितरण अंतराल द्रुतपणे कॉन्फिगर करण्याची क्षमता देखील आहे. गट. सादरीकरणाच्या स्पष्टतेसाठी, सारांश आकृती जोडली जाऊ शकते (चित्र 26).

टीप 14: वर्कबुकमधील शीटवर डेटाचा संच वितरित करण्यासाठी मुख्य सारणी वापरा

विश्लेषकांना बऱ्याचदा प्रत्येक क्षेत्रासाठी, बाजारासाठी, व्यवस्थापकासाठी वेगवेगळे PivotTable अहवाल तयार करावे लागतात. हे कार्य पूर्ण करण्यामध्ये सामान्यत: नवीन वर्कशीटमध्ये PivotTable कॉपी करणे आणि नंतर योग्य प्रदेश आणि व्यवस्थापक प्रतिबिंबित करण्यासाठी फिल्टर फील्ड बदलण्याची दीर्घ प्रक्रिया समाविष्ट असते. ही प्रक्रिया व्यक्तिचलितपणे केली जाते आणि प्रत्येक विश्लेषणासाठी पुनरावृत्ती केली जाते. परंतु सर्वसाधारणपणे, तुम्ही वैयक्तिक पिव्होट टेबल्सची निर्मिती Excel ला सोपवू शकता. पॅरामीटर लागू करण्याच्या परिणामी फिल्टर फील्ड क्षेत्रातील प्रत्येक घटकासाठी स्वतंत्र मुख्य सारणी स्वयंचलितपणे तयार केली जाते. हे वैशिष्ट्य वापरण्यासाठी, फक्त पिव्होटटेबल तयार करा ज्यामध्ये फिल्टर फील्ड समाविष्ट आहे (आकृती 27). पिव्होटटेबलमध्ये आणि टॅबवर कर्सर कुठेही ठेवा विश्लेषणसंघांच्या गटात मुख्य सारणीड्रॉप डाउन सूचीवर क्लिक करा पर्याय(अंजीर 28). नंतर बटणावर क्लिक करा अहवाल फिल्टर पृष्ठे दर्शवा.

तांदूळ. 28. बटणावर क्लिक करा अहवाल फिल्टर पृष्ठे दर्शवा

दिसणाऱ्या डायलॉग बॉक्समध्ये (चित्र 29), तुम्ही फिल्टर फील्ड निवडू शकता ज्यासाठी स्वतंत्र मुख्य सारण्या तयार केल्या जातील. योग्य फिल्टर फील्ड निवडा आणि क्लिक करा ठीक आहे.

तांदूळ. 29. डायलॉग बॉक्स अहवाल फिल्टर पृष्ठे प्रदर्शित करणे

फिल्टर फील्डच्या प्रत्येक घटकासाठी, एक मुख्य सारणी तयार केली जाईल, एका वेगळ्या शीटवर ठेवली जाईल (चित्र 30). लक्षात ठेवा की शीट लेबल्सना फिल्टर फील्ड घटकांप्रमाणेच नाव दिले जाते. कृपया लक्षात घ्या की पॅरामीटर फिल्टर पृष्ठे दर्शवाएका वेळी एक फिल्टर फील्डवर लागू केले जाऊ शकते.

टीप 15: वैयक्तिक वर्कबुकमध्ये डेटाचा संच वितरित करण्यासाठी मुख्य सारणी वापरा

टीप 14 मध्ये आम्ही वेगळे करण्यासाठी एक विशेष पर्याय वापरला मुख्य सारण्यावर्कबुकच्या वेगवेगळ्या शीटवर विक्री बाजारांद्वारे. जर तुम्हाला वाटणे आवश्यक असेल प्रारंभिक डेटावेगळ्या पुस्तकांमध्ये वेगवेगळ्या विक्री बाजारांसाठी, तुम्ही थोडा VBA कोड वापरू शकता. सुरू करण्यासाठी, तुम्ही फिल्टर करू इच्छित फील्ड फिल्टर फील्डमध्ये ठेवा. फील्ड ठेवा विक्रीचे प्रमाणमूल्यांच्या श्रेणीमध्ये (चित्र 31). खालील VBA कोड प्रत्येक FILTER घटक निवडतो आणि फंक्शनला कॉल करतो तपशील दाखवा, नवीन डेटा शीट तयार करणे. हे पत्रक नंतर नवीन वर्कबुकमध्ये जतन केले जाते

कोडVBA.

सब एक्सप्लोड टेबल()

PivotItem म्हणून PvtItem मंद करा

PivotTable म्हणून PvtTable मंद करा

पिव्होटफील्ड म्हणून स्ट्रफिल्ड मंद करा

‘स्क्रिप्टनुसार व्हेरिएबल्स बदलणे

ConststrFieldName = "विक्री बाजार" ‘<—Изменение имени поля

Const strTriggerRange = "A4" ‘<—Изменение диапазона триггера

मुख्य सारणीचे नाव बदला (आवश्यक असल्यास)

SetPvtTable = ActiveSheet.PivotTables(" PivotTable1") ‘<—Изменение названия сводной

'निवडलेल्या फील्डच्या प्रत्येक घटकातून लूप करा

PvtTable.PivotFields(strFieldName).PivotItems मधील प्रत्येक PvtItem साठी

PvtTable.PivotFields(strFieldName).CurrentPage = PvtItem.Name

श्रेणी(strTriggerRange).ShowDetail = खरे

‘तात्पुरत्या पत्रकाला नाव देणे

ActiveSheet.Name = "Tempsheet"

'नवीन वर्कबुकमध्ये डेटा कॉपी करणे आणि तात्पुरती शीट हटवणे

ActiveSheet.Cells.Copy

ActiveSheet.Paste

सेल.संपूर्ण कॉलम.ऑटोफिट

Application.DisplayAlerts = False

ActiveWorkbook.SaveAs_

फाइलनाव:=ThisWorkbook.Path & " \" & PvtItem.Name & " .xlsx "

ActiveWorkbook.बंद करा

पत्रके("टेम्पशीट").हटवा

Application.DisplayAlerts = खरे

हा कोड नवीन VBA मॉड्यूलमध्ये प्रविष्ट करा. खालील स्थिरांक आणि चलांची मूल्ये तपासा आणि आवश्यक असल्यास बदला:

• Const strFieldName. डेटा विभक्त करण्यासाठी वापरल्या जाणाऱ्या फील्डचे नाव. दुस-या शब्दात, हे एक फील्ड आहे जे फिल्टर/पिव्होट टेबल पेजेसमध्ये ठेवलेले असते.
• Const strTriggerRange. एक ट्रिगर सेल जो PivotTable डेटा क्षेत्रामधून एकल संख्या संचयित करतो. आमच्या बाबतीत, ट्रिगर सेल A4 आहे (चित्र 31 पहा).

VBA कोड कार्यान्वित केल्यामुळे, प्रत्येक विक्री बाजाराचा डेटा वेगळ्या वर्कबुकमध्ये जतन केला जाईल.

जेलेन, अलेक्झांडर यांच्या पुस्तकावर आधारित नोट लिहिली होती. . धडा 14.

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

पिव्होट टेबल्सना त्यांचे नाव फील्ड्स परस्पर ड्रॅग आणि ड्रॉप करण्याच्या त्यांच्या क्षमतेवरून प्राप्त होते, जे तुम्हाला डायनॅमिकपणे स्वरूप बदलण्याची परवानगी देते, तुम्हाला समान डेटा स्रोत वापरून संपूर्ण नवीन दृष्टीकोन देते. कृपया लक्षात घ्या की स्त्रोत डेटा स्वतः बदलत नाही आणि तुम्ही कोणत्या प्रकारचे डिस्प्ले निवडता यावर अवलंबून नाही. म्हणून, मुख्य सारण्यांसाठी आदर्श आहेत.

मुख्य सारणी रचना

पिव्होटटेबलमध्ये चार क्षेत्रे असतात: फिल्टर, स्तंभ, पंक्ती आणि मूल्ये. तुम्ही डेटा कुठे ठेवता यावर अवलंबून, मुख्य सारणीचे स्वरूप बदलेल. चला प्रत्येक क्षेत्राचे कार्य अधिक तपशीलवार पाहू.

मूल्यांची श्रेणी

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

नियमानुसार, गणना करणे आवश्यक असलेला डेटा या फील्डमध्ये ड्रॅग केला जातो - प्रदेशाचे एकूण क्षेत्र, सरासरी दरडोई उत्पन्न इ.

रेषा क्षेत्र

या फील्डमध्ये हस्तांतरित केलेला स्त्रोत डेटा मुख्य सारणीच्या डाव्या बाजूला ठेवला जातो आणि या फील्डच्या अद्वितीय मूल्यांचे प्रतिनिधित्व करतो. सामान्यतः, पंक्ती क्षेत्रामध्ये किमान एक फील्ड असते, जरी कोणतेही फील्ड नसणे शक्य आहे. चित्रात पिवळ्या रंगात चिन्हांकित.

गटबद्ध आणि वर्गीकृत करणे आवश्यक असलेला डेटा, जसे की काउंटीचे किंवा उत्पादनांचे नाव, सहसा येथे ठेवले जाते.

स्तंभ क्षेत्र

स्तंभ क्षेत्रामध्ये हेडर असतात, जे पिव्होटटेबलच्या शीर्षस्थानी असतात (हिरव्या रंगात लेबल केलेले). या उदाहरणात, स्तंभ क्षेत्रामध्ये काउंटी की मेट्रिक्सची एक अद्वितीय सूची आहे.

स्तंभ क्षेत्र डेटा मॅट्रिक्स तयार करण्यासाठी किंवा वेळ ट्रेंड दर्शवण्यासाठी आदर्श आहे.

फिल्टर क्षेत्र

पिव्होटटेबलच्या शीर्षस्थानी एक किंवा अधिक फील्ड (चित्रात तपकिरी) असलेले पर्यायी फिल्टर क्षेत्र आहे. उदाहरणामध्ये, देशाच्या लोकसंख्येच्या उत्पन्नाच्या श्रेणींमध्ये फिल्टर सेट केले आहे.

फिल्टरच्या निवडीनुसार, मुख्य सारणीचे स्वरूप बदलते. तुम्हाला वेगळे करायचे असल्यास किंवा त्याउलट, विशिष्ट डेटावर लक्ष केंद्रित करायचे असल्यास, तुम्हाला या फील्डमध्ये डेटा ठेवणे आवश्यक आहे.

मुख्य सारणी तयार करणे

आता तुम्हाला पिव्होट टेबलच्या संरचनेची कल्पना आली आहे, आम्ही ते तयार करणे सुरू करू शकतो.

तुम्ही उदाहरण फाइल डाउनलोड करू शकता

• स्रोत डेटासह टेबलमधील कोणत्याही सेलवर क्लिक करा (जो तुम्ही मुख्य सारणी तयार करण्यासाठी वापराल)
• टॅबवर जा घाला –> टेबल -> मुख्य सारणी,चित्रात दाखवल्याप्रमाणे.

• मुख्य सारणी तयार करणेआम्ही डेटा स्त्रोत आणि पिव्हट टेबल जिथं ठेवू इच्छितो ते ठिकाण ठरवतो. कृपया लक्षात घ्या की एक्सेल बाय डीफॉल्ट वर्तमान वर्कबुकमध्ये नवीन शीटवर अहवाल ठेवेल. स्थान बदलण्यासाठी, निवडा विद्यमान शीटवरआणि आवश्यक श्रेणी निर्दिष्ट करा.

• ओके क्लिक करा.

या टप्प्यावर, तुम्ही नवीन शीटवर रिक्त पिव्होटटेबल अहवाल तयार केला आहे.

मुख्य सारणी लेआउट

रिकाम्या पिव्होट टेबलच्या डावीकडे तुम्हाला एक डायलॉग बॉक्स दिसेल मुख्य सारणी फील्ड, चित्रावर दाखवल्याप्रमाणे.

पिव्होटटेबलच्या चार क्षेत्रांपैकी एका - फिल्टर, स्तंभ, पंक्ती किंवा मूल्यांमध्ये नामांकित श्रेणी ड्रॅग आणि ड्रॉप करून तुम्ही तुम्हाला PivotTable मध्ये आवश्यक फील्ड जोडू शकता.

कृपया लक्षात घ्या की जर डायलॉग बॉक्स मुख्य सारणी फील्डदिसत नाही, मुख्य सारणीमध्ये कुठेही उजवे-क्लिक करा आणि निवडा फील्डची सूची दर्शवा.

आता, आपण फील्ड ड्रॅग करणे सुरू करण्यापूर्वी, आपल्याला काय पहायचे आहे हे ठरवावे लागेल. या प्रश्नाचे उत्तर तुम्हाला कोणत्या क्षेत्रात कोणत्या क्षेत्रात ठेवायचे याची कल्पना देईल.

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

अहवालात जोडण्यासाठी फील्डसह सूचीमध्ये, फील्डच्या पुढील बॉक्समध्ये खूण करा फेडरल जिल्हा.आता परिसरात तारआणि फील्ड डेटा मुख्य सारणीमध्ये दिसला.


अहवालात जोडण्यासाठी फील्डसह सूचीमध्ये, मुख्य निर्देशकांपुढील बॉक्स चेक करा

कृपया लक्षात घ्या की जर आम्ही मजकूर मूल्यांसह फील्डच्या पुढील बॉक्स चेक केले तर, एक्सेल बाय डीफॉल्ट ही मूल्ये पंक्तींच्या क्षेत्रामध्ये, मूल्य क्षेत्रामध्ये संख्यात्मक मूल्यांसह ठेवते.

आम्ही एक साधी सारांश सारणी तयार केली आहे जी फेडरल जिल्ह्यांचे मुख्य निर्देशक प्रदर्शित करते आणि आम्हाला स्त्रोत डेटा सुधारित करण्यासाठी अतिरिक्त पावले उचलण्याची आवश्यकता नाही.

PivotTable संपादित करणे

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

पिव्होटटेबलमध्ये फिल्टर वापरणे

बऱ्याचदा आम्हाला वेगवेगळ्या प्रकारच्या डेटासाठी अहवाल तयार करावा लागतो, उदाहरणार्थ, केवळ विशिष्ट काउंटीचे विश्लेषण करा. स्त्रोत डेटा बदलण्यात वेळ वाया घालवण्याऐवजी, फिल्टर क्षेत्र वापरूया. फील्ड ड्रॅग करा फेडरल जिल्हाप्रदेशाला फिल्टर. आता तुम्ही इच्छित काउन्टीवर फिल्टर सेट करून मुख्य सारणीचे स्वरूप बदलू शकता.

PivotTable अपडेट करत आहे

कालांतराने, स्त्रोत डेटा बदलतो आणि त्यात नवीन पंक्ती आणि स्तंभ जोडले जातात. मुख्य सारणी अपडेट करण्यासाठी, कमांड वापरा अपडेट करा, हे करण्यासाठी, टेबलमध्ये कुठेही उजवे-क्लिक करा आणि निवडा अपडेट करा.

अशी परिस्थिती असते जेव्हा स्त्रोत डेटाची रचना बदलते, उदाहरणार्थ, आपल्याला डेटासह टेबलमध्ये नवीन पंक्ती जोडण्याची आवश्यकता असते. या प्रकारचा बदल डेटा स्रोताच्या श्रेणीवर परिणाम करेल आणि मुख्य सारणीला अहवाल दिला जाणे आवश्यक आहे. एक साधे अद्यतन कार्य करणार नाही या प्रकरणात, आपल्याला डेटा स्त्रोताची श्रेणी विस्तृत करण्याची आवश्यकता आहे.

मुख्य सारणीमध्ये कुठेही लेफ्ट-क्लिक करा. चला टॅबवर जाऊया पिव्होट टेबलसह कार्य करणे -> विश्लेषण –> माहितीचा स्रोत.

दिसणाऱ्या डायलॉग बॉक्समध्ये PivotTable डेटा स्रोत बदलाबदललेली डेटा श्रेणी सेट करा.

तळ ओळ

या लेखात एक साधी पिव्होट टेबल तयार करण्याचे उदाहरण पाहिले, ज्याच्या मदतीने आपण आवश्यक पॅरामीटर्सचे अधिक अचूक आणि तपशीलवार मूल्यांकन करू शकतो.

डेटा विश्लेषणासाठी एक्सेल स्प्रेडशीट्स हे एक शक्तिशाली साधन आहे. काहीवेळा तुम्हाला शेकडो किंवा हजारो नोंदी असलेल्या सारण्यांचे विश्लेषण करावे लागते. येथे "पिव्होट टेबल्स" नावाचे एक साधन आमच्या मदतीला येते, जे आम्हाला संपूर्ण डेटा ॲरे एका दृष्टीक्षेपात पाहण्याची परवानगी देते. पिव्होट टेबल्सचे काही पॅरामीटर्स बदलून, आम्हाला स्वारस्य असलेली माहिती आम्ही सहजपणे काढू शकतो.

" टॅबवर पिव्होट टेबल तयार करण्यासाठी घाला"गटात" टेबल्स» « बटणावर क्लिक करा मुख्य सारणी"(आकृती क्रं 1).

तांदूळ. 1 मुख्य सारणी तयार करा.

दिसत असलेल्या विंडोमध्ये, फील्डमध्ये " टेबल निवडा» किंवा तुमची संपूर्ण सारणी स्वयंचलितपणे निवडली जाईल. जर तुम्हाला विश्लेषणासाठी टेबलचा फक्त काही भाग निवडायचा असेल, तर इनपुट लाइनच्या शेवटी असलेल्या बटणावर क्लिक करा आणि टेबलचा इच्छित भाग निवडण्यासाठी माउस वापरा. निवडलेल्या श्रेणीच्या वरच्या ओळीत स्तंभांची नावे असणे आवश्यक आहे याकडे लक्ष देणे आवश्यक आहे, जेणेकरुन ज्या फील्डसाठी मुख्य सारणी अहवाल व्युत्पन्न केला जाईल अशा फील्डच्या नावांप्रमाणे त्यावर प्रक्रिया केली जाऊ शकते. व्यक्तिचलितपणे निवडल्यानंतर किंवा संपादित केल्यानंतर, इनपुट लाइनच्या शेवटी बटणावर पुन्हा क्लिक करा. मुख्य सारणी वर्तमान शीटवर प्रदर्शित केली जाऊ शकते किंवा तुम्ही एक नवीन निवडू शकता. पुढे, "क्लिक करा ठीक आहे"(चित्र 2).

तांदूळ. 2 PivotTable श्रेणी निवडा.

आता तुम्ही एक शीट उघडली आहे ज्यामध्ये एक मुख्य सारणी तयार केली आहे. मुख्य सारणीच्या उजवीकडे फील्डची सूची आहे (चित्र 3). फील्ड निवडून, आम्ही एक मुख्य सारणी तयार करतो आणि निवडीचा क्रम महत्वाचा आहे.

तांदूळ. 3 नवीन मुख्य सारणी.

प्रथम कोणती फील्ड निवडली यावर अवलंबून, मुख्य सारणीचे स्वरूप बदलेल. ज्या क्रमाने फील्ड निवडले आहेत ते तळाशी उजवीकडे "म्हणून प्रदर्शित केले आहे. ओळींची नावे».

तांदूळ. 4 व्युत्पन्न केलेले मुख्य सारणी.

Excel मधील पिव्होट टेबल्स तुम्हाला एका मोठ्या श्रेणीतील डेटाचे विश्लेषण करण्याची परवानगी देतात. शिवाय, विश्लेषणासाठी वापरले जाणारे स्त्रोत सारणी एकतर एक्सेलमध्ये किंवा इतर डेटाबेस किंवा इतर दस्तऐवजात तयार केली जाऊ शकते.

तुम्हाला फक्त विद्यमान असलेल्या पंक्ती आणि स्तंभांसाठी आवश्यक शीर्षलेख निवडण्याची आवश्यकता आहे. तुम्ही संपूर्ण पिव्होट टेबलवर किंवा फक्त पंक्ती आणि स्तंभांच्या नावांमध्ये दर्शविलेल्या मूल्यांवर फिल्टर लागू करू शकता.

आता एक्सेलमध्ये तयार करताना ज्या आवश्यकतांचे पालन केले पाहिजे त्याबद्दल. स्त्रोत डेटा सारणीच्या स्वरूपात सादर केला जाणे आवश्यक आहे, ज्यामध्ये स्तंभांसाठी शीर्षक असणे आवश्यक आहे, म्हणजेच शीर्षलेख. या हेतूंसाठी, एक्सेलमध्ये एक स्मार्ट टेबल तयार करणे योग्य आहे. त्यात रिकाम्या पंक्ती, स्तंभ किंवा सेल असू नयेत. कोणत्याही लपविलेल्या पंक्ती/स्तंभ किंवा विलीन केलेले सेल नसावेत.

कसे तयार करावे

आता पिव्होट टेबल तयार करण्याचे उदाहरण पाहू. समजा आमच्याकडे कपड्याच्या दुकानाच्या विक्रीचा डेटा आहे: कोणता विक्रेता, कोणता नंबर, त्याने कोणत्या वस्तू विकल्या आणि कोणत्या रकमेसाठी.

पिव्होट टेबल बनवण्यासाठी, मूळ सेलमधून कोणताही सेल निवडा, त्यानंतर "इन्सर्ट" टॅबवर जा आणि बटणावर क्लिक करा. "मुख्य सारणी".

खालील डायलॉग बॉक्स दिसेल. त्यामध्ये आपल्याला स्त्रोत डेटासह सारणीची श्रेणी किंवा नाव सूचित करणे आवश्यक आहे आणि येथे आपण ते दुसऱ्या स्त्रोतावरून निवडू शकता. त्यानंतर अहवाल तयार करण्यासाठी कोठे ठेवायचा हे मार्करने चिन्हांकित करा. चला ते नवीन शीटवर करूया. ओके क्लिक करा.

उघड्यावर एक्सेल वर्कबुकएक नवीन पत्रक तयार केले जाईल ज्यावर अद्याप रिक्त मुख्य सारणी ठेवली जाईल.

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

जोडलेले फील्ड चेक मार्कने चिन्हांकित केले जातील. सारणी भागात, तुमच्या स्रोत डेटाचे विश्लेषण करण्यासाठी सर्वात योग्य असा देखावा मिळवण्यासाठी तुम्ही त्यांची ठिकाणे बदलू शकता.

फील्डची व्यवस्था कशी करावी

आता आपण डेटाचे विश्लेषण कोणत्या तत्त्वावर करू ते ठरवू. उदाहरणार्थ, तुम्हाला कोणत्या विक्रेत्याने प्रत्येक महिन्यात कोणते उत्पादन आणि किती रकमेची विक्री केली हे शोधणे आवश्यक आहे.

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

ओळींसाठी, "उत्पादने" निवडा. त्याचप्रमाणे, इच्छित फील्ड क्षेत्रामध्ये ड्रॅग करा "लाइन शीर्षके".

कृपया लक्षात घ्या की तुम्ही एका क्षेत्रात अनेक फील्ड टाकू शकता. उदाहरणार्थ, ओळींमध्ये आम्ही उत्पादने निवडू आणि किंमत दर्शवू. उत्पादने ड्रॉप-डाउन सूची बनली आहेत जी किंमत प्रदर्शित करतात. आपण प्रथम किंमत आणि नंतर उत्पादने निर्दिष्ट केल्यास, ड्रॉप-डाउन सूची किंमत होईल. येथे फील्डचा क्रम महत्त्वाचा आहे.

आमच्याकडे मूळ सारणीमध्ये "युनिट्स" स्तंभ असल्यास. मग या सारांश तक्त्यामध्ये कोणत्या विक्रेत्याने, कोणत्या महिन्यात, किती युनिट्सच्या मालाची विशिष्ट किंमतीला विक्री केली हे दर्शवेल.

प्रदेशाला "स्तंभ शीर्षके""तारीख" फील्ड ड्रॅग करा. विक्री प्रदर्शित करण्यासाठी प्रत्येक दिवसासाठी नाही, परंतु, उदाहरणार्थ, महिन्यानुसार, कोणत्याही तारखेवर उजवे-क्लिक करा आणि मेनूमधून "गट" निवडा.

टेबल खालील फॉर्म घेईल.

आता "रक्कम" फील्ड "मूल्य" क्षेत्रात ड्रॅग करा.

जसे आपण पाहू शकता, फक्त संख्या प्रदर्शित केली गेली होती, जरी मूळ श्रेणीमध्ये या स्तंभासाठी सेलचे संख्यात्मक स्वरूप सेट केले गेले होते ते आर्थिक किंवा आर्थिक देखील असू शकते; "तारीख" स्तंभात, सेलचे स्वरूप देखील योग्य होते - तारीख.

याचे निराकरण करण्यासाठी, सारांशातील सेलची इच्छित श्रेणी निवडा आणि उजवे माउस बटण दाबा. नंतर मेनूमधून निवडा "नंबर फॉरमॅट".

पुढील विंडोमध्ये, "संख्यात्मक" निवडा, तुम्ही बॉक्स चेक करू शकता "बिट ग्रुप सेपरेटर"आणि "ओके" वर क्लिक करा.

डेटासह कसे कार्य करावे

आम्ही सर्व आवश्यक फील्ड विचारात घेतल्यानंतर, आम्ही Excel मध्ये पिव्होट टेबलसह कार्य करण्यास प्रारंभ करू शकतो. विक्रेता निवडणे: तुम्ही बॉक्स चेक करून एक, अनेक किंवा सर्व एकाच वेळी निवडू शकता "एकाहून अधिक आयटम निवडा".

तुम्ही पंक्ती आणि स्तंभांसाठी फिल्टर देखील वापरू शकता. उदाहरणार्थ, ही उत्पादने आणि महिने आहेत. उदाहरणार्थ, सूट आणि पँटसाठी बॉक्स चेक करून, तुम्ही ते सर्व विक्रेत्यांद्वारे किंवा विशिष्ट विक्रेत्याद्वारे किती किमतीला विकले गेले हे शोधू शकता.

मूल्य क्षेत्रामध्ये, तुम्ही फील्डसाठी सेटिंग्ज कॉन्फिगर करू शकता. उदाहरण मूल्यांची बेरीज दाखवते: रोमाने फेब्रुवारीमध्ये 1,800.00 मध्ये शर्ट विकले. किती तुकडे आहेत ते पाहू. ओळीवर लेफ्ट-क्लिक करा "क्षेत्रानुसार बेरीज..."आणि मेनूमधून निवडा "मूल्य क्षेत्र पर्याय".

पुढील विंडोमध्ये, सूचीमधून "प्रमाण" निवडा, तुमच्या केससाठी विशेषत: काय योग्य आहे ते निवडा आणि "ओके" क्लिक करा.

आता, मूल्ये पाहता, आम्ही समजू शकतो की रोमाने फेब्रुवारीमध्ये दोन शर्ट विकले.

आता टेबलसाठी महिन्यानुसार सामान्य फिल्टर बनवू. आम्ही क्षेत्रे बदलतो: "रिपोर्ट फिल्टर" मध्ये आम्ही "तारीख" फील्ड ड्रॅग करतो "स्तंभ शीर्षके"- "सेल्समन".

अशा सारांश सारणीमध्ये प्रत्येक विक्रेत्याने संपूर्ण कालावधीसाठी किंवा विशिष्ट महिन्यासाठी किती आणि कोणत्या प्रकारचा माल विकला हे दर्शवेल.

टेपकडे देखील लक्ष द्या. जेव्हा तुम्ही पिव्होट टेबलमधून कोणतेही सेल निवडता तेव्हा त्यावर एक टॅब दिसेल "पिव्होट टेबल्ससह कार्य करणे""पॅरामीटर्स" आणि "डिझायनर" या दोन सबटॅबसह.

माझ्या प्रॅक्टिसमध्ये, पिव्होट टेबलमध्ये पूर्वी गोळा केलेल्या डेटाचे विश्लेषण करण्याचे कार्य एकापेक्षा जास्त वेळा उद्भवले आहे. चला असे गृहीत धरू की विक्रीचे विश्लेषण करताना, तुम्ही खालील फॉरमॅटमध्ये सादर केलेला प्रारंभिक डेटा वापरला आहे (सारणी 1):

अशा डेटावर आधारित, Excel मध्ये पिव्होट टेबल तयार करणे सोपे आहे, जसे की (टेबल 2):

तुम्हाला (सारणी 3) सारणी मिळवण्यासाठी विक्रीच्या प्रमाणानुसार ग्राहकांचे वितरण करायचे असल्यास:

मग तुम्हाला अडचणी येतील, कारण तुम्ही स्त्रोत डेटावर आधारित अशी सारणी तयार करू शकणार नाही (सारणी 1).

किंवा मध्ये उदाहरण स्वरूपात लेख डाउनलोड करा

ही एक-वेळची प्रक्रिया असल्यास, आपण प्रथम एक टेबल तयार कराल. 2, आणि नंतर त्यावर आधारित टेबल तयार करा. 3. पण जर तुम्हाला टेबलला आधार द्यायचा असेल तर. स्रोत डेटा बदलते तेव्हा 3 अद्ययावत आहे (सारणी 1)!?

मला माहीत नाही मानक पद्धती Excel मध्ये जे तुम्हाला हे करण्याची परवानगी देते. दुर्दैवाने, PivotTable विझार्डमधील "दुसऱ्या PivotTable मधील डेटावर आधारित टेबल तयार करण्याचा" पर्याय कार्य करत नाही:

जेव्हा तुम्ही विझार्ड वापरता, तेव्हा नवीन मुख्य सारणी मूळ मुख्य सारणी सारख्या स्रोत डेटावर "आधारित" असते. सोयीसाठी, उदाहरणासह एक्सेल फाइल वापरा. "मानक" शीटवर जा. हे तपशीलवार विक्री डेटा प्रदान करते: ऑर्डर क्रमांक आणि तारीख, ऑर्डरमध्ये समाविष्ट असलेल्या वस्तू आणि वस्तूंचे प्रमाण

या डेटाच्या आधारे मानक पद्धतीने एक मुख्य सारणी तयार करा, आणि नंतर पिव्होट टेबल विझार्ड लाँच करा, "दुसऱ्या मुख्य सारणीमध्ये स्थित डेटावर आधारित टेबल तयार करा" पर्याय निवडा, "पुढील" क्लिक करा:

पायरी 2 मध्ये, तुम्ही नवीन सारणी तयार कराल त्या आधारावर निवडा:

हे पाहिले जाऊ शकते की दुसरा सारांश पहिल्या सारख्याच डेटावर "विश्वास ठेवतो":

तुम्ही प्रयोग करून खात्री करून घेऊ शकता की दुसऱ्या पिव्होट टेबलचा डेटा सेट पहिल्या पिव्होट टेबलच्या प्रकारावर अवलंबून नाही (म्हणजे, आम्ही पहिल्या टेबलमध्ये कोणत्या फील्डवर आणि कसे निवडले यावर), परंतु केवळ स्त्रोत डेटावर अवलंबून आहे. (स्तंभ A-D).

खरं तर, आम्ही पहिल्या पिव्होट टेबलची एक प्रत तयार केली आहे. त्यामुळे आमच्या समस्येचे निराकरण करण्यासाठी मानक एक्सेल पद्धती योग्य नाहीत. चला काही छोट्या युक्त्या वापरुया.

"ट्रिक" शीटवरील एक्सेल फाइलमध्ये एक उदाहरण दिले आहे. प्रथम, स्त्रोत डेटावर आधारित एक नामांकित डायनॅमिक श्रेणी तयार करू - ऑफसेट फंक्शन वापरून "आउट1" (हे कसे करायचे ते आपण पाहू शकता). स्रोत डेटा जोडताना नामित श्रेणी आम्हाला समस्यांपासून वाचवेल आणि आम्हाला फक्त "अपडेट" बटणावर क्लिक करून सर्व मुख्य सारण्या अद्यतनित करण्यास अनुमती देईल:

मुख्य सारणी तयार करताना, स्त्रोत डेटा "ref1" नावाची श्रेणी आहे हे निर्दिष्ट करा:

मुख्य सारणीसाठी, ग्रँड बेरीज बंद करा:

OFFSET फंक्शन वापरून, मुख्य सारणीसाठी एक नामित श्रेणी तयार करा:

म्हणूनच आम्ही बेरीज बंद केली - जेणेकरून ते या श्रेणीमध्ये बसणार नाहीत!

पिव्होट टेबल विझार्ड लाँच करा (क्विक ऍक्सेस टूलबारमधून) आणि "सूची किंवा एमएस एक्सेल डेटाबेसमधील डेटावर आधारित टेबल तयार करा" निवडा, "पुढील" क्लिक करा:

दुसऱ्या चरणात, दुसऱ्या पिव्होट टेबलसाठी स्त्रोत डेटा असलेली श्रेणी निर्दिष्ट करा - “sv1”, “पुढील” क्लिक करा:

पहिल्याच्या पुढे दुसरा सारांश ठेवा:

वैयक्तिक मूल्यांचे श्रेणींमध्ये गट करा आणि सारांश चार्ट तयार करा:

पंक्ती जोडून/काढून स्त्रोत डेटासह प्रयोग करा. प्रथम आणि द्वितीय मुख्य सारण्या अनुक्रमे अद्यतनित करणे पुरेसे आहे आणि केलेले सर्व बदल विचारात घेतले जातील.