Excel 2010 पिवोट टेबल्स को कॉन्फ़िगर कैसे करें

15 में से 01

अंतिम परिणाम

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

माइक्रोसॉफ्ट एक्सेल 2010 (एक्सेल 2010 पिवोट टेबल के साथ) एसक्यूएल सर्वर 2008 आर 2, शेयरपॉइंट 2010 और मुफ्त माइक्रोसॉफ्ट एक्सेल 2010 ऐड-ऑन "पावरपिवॉट" के साथ-साथ एक उच्च अंत व्यवसाय खुफिया और रिपोर्टिंग समाधान हुआ है।

इस ट्यूटोरियल में एक सरल SQL क्वेरी के साथ एक SQL Server 2008 R2 डेटाबेस से जुड़े Excel 2010 PivotTable के साथ एक सीधी आगे परिदृश्य शामिल है। मैं विज़ुअल फ़िल्टरिंग के लिए स्लाइसर का भी उपयोग कर रहा हूं जो एक्सेल 2010 में नया है। मैं निकट भविष्य में Excel 2010 के लिए PowerPivot में डेटा विश्लेषण अभिव्यक्तियों (DAX) का उपयोग करके अधिक जटिल बीआई तकनीकों को कवर करूंगा। माइक्रोसॉफ्ट एक्सेल 2010 की यह नवीनतम रिलीज आपके उपयोगकर्ता समुदाय के लिए एक वास्तविक मूल्य प्रदान कर सकती है।

15 में से 02

पिवट टेबल डालें

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

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

15 में से 03

पिन सर्वर को SQL सर्वर से कनेक्ट करें (या अन्य डेटाबेस)

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

इस ट्यूटोरियल के मामले में, मैं SQL सर्वर 2008 आर 2 (एसक्यूएल एक्सप्रेस मुक्त संस्करण) से कनेक्ट कर रहा हूं।

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

आपको PivotTable फॉर्म (ए) बनाएं पर वापस कर दिया जाएगा। ओके पर क्लिक करें।

15 में से 04

पिवोट टेबल अस्थायी रूप से एसक्यूएल टेबल से जुड़ा हुआ है

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

15 में से 05

खुला कनेक्शन गुण

पिवोटटेबल के लिए डेटा चुनना शुरू करने से पहले, हमें कनेक्शन को SQL क्वेरी में बदलना होगा। सुनिश्चित करें कि आप विकल्प टैब पर हैं और डेटा अनुभाग से डेटा बदलें ड्रॉप डाउन पर क्लिक करें। कनेक्शन गुण चुनें।

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

15 में से 06

प्रश्न के साथ कनेक्शन गुण अद्यतन करें

कमांड प्रकार को तालिका से SQL में बदलें और अपने SQL क्वेरी के साथ मौजूदा कमांड टेक्स्ट को ओवरराइट करें। यहां एडवेंचरवर्क्स नमूना डेटाबेस से बनाई गई क्वेरी है:

बिक्री चुनें। सेल्सऑर्डर हैडर.SalesOrderID,
Sales.SalesOrderHeader.OrderDate,
Sales.SalesOrderHeader.ShipDate,
Sales.SalesOrderHeader.Status,
Sales.SalesOrderHeader.SubTotal,
Sales.SalesOrderHeader.TaxAmt,
Sales.SalesOrderHeader.Freight,
Sales.SalesOrderHeader कुल मिलाकर,
Sales.SalesOrderDetail.SalesOrderDetailID,
Sales.SalesOrderDetail.OrderQty,
Sales.SalesOrderDetail.UnitPrice,
Sales.SalesOrderDetail.LineTotal,
उत्पादन। उत्पाद नाम,
Sales.vIividualividualCustomer.StateProvinceName, Sales.vIividividualCustomer.CountryRegionName,
बिक्री। ग्राहक। ग्राहक टाइप,
उत्पादन। उत्पाद। लिस्टप्रिस,
प्रोडक्शन। प्रोडक्ट। प्रोडक्टलाइन,
प्रोडक्शन। प्रोडक्ट्सबब्सिन। नाम के रूप में उत्पाद श्रेणी
Sales.SalesOrderDetail INNER सेमिन बिक्री में शामिल हों। SalesOrderHeader चालू
Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderID
INNER जॉइन प्रोडक्शन। बिक्री पर उत्पाद। SalesOrderDetail.ProductID =
प्रोडक्शन.प्रोडक्ट। प्रोडक्टिड इनर जॉइन सेल्स। ग्राहक चालू
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID और
Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID इनर जॉइन
Sales.vIndividualCustomer बिक्री पर। ग्राहक। ग्राहक_ =
Sales.vIividividualCustomer.CustomerID इनर जॉइन
प्रोडक्शन। प्रॉडक्ट्सब्यूब्रेरी ऑन प्रोडक्शन। प्रोडक्ट। प्रोडक्ट्सब्यूक्लिड आईडी =
Production.ProductSubcategory.ProductSubcategoryID

ओके पर क्लिक करें।

15 में से 07

कनेक्शन चेतावनी प्राप्त करें

आपको एक माइक्रोसॉफ्ट एक्सेल चेतावनी संवाद बॉक्स प्राप्त होगा। ऐसा इसलिए है क्योंकि हमने कनेक्शन की जानकारी बदल दी है। जब हमने मूल रूप से कनेक्शन बनाया, तो उसने जानकारी को बाहरी .ODC फ़ाइल (ओडीबीसी डेटा कनेक्शन) में सहेजा। कार्यपुस्तिका में डेटा .ODC फ़ाइल के समान था जब तक कि हम तालिका कमांड प्रकार से SQL कमांड प्रकार में चरण # 6 में परिवर्तित नहीं हुए। चेतावनी आपको बता रही है कि डेटा अब सिंक में नहीं है और कार्यपुस्तिका में बाहरी फ़ाइल का संदर्भ हटा दिया जाएगा। यह ठीक है। हाँ पर क्लिक करें।

15 में से 08

Pivot तालिका क्वेरी के साथ SQL सर्वर से जुड़ा हुआ है

यह रिक्त पिवोटटेबल के साथ Excel 2010 कार्यपुस्तिका पर वापस ले जाता है। आप देख सकते हैं कि उपलब्ध फ़ील्ड अब अलग हैं और SQL क्वेरी में फ़ील्ड से मेल खाते हैं। अब हम पिवोटटेबल में फ़ील्ड जोड़ने शुरू कर सकते हैं।

15 में से 09

पिवोट टेबल में फ़ील्ड जोड़ें

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

15 में से 10

तिथि फ़ील्ड के लिए समूह जोड़ें

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

15 में से 11

मूल्यों से ग्रुपिंग चुनें

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

महीनों और वर्षों पर क्लिक करें और ठीक क्लिक करें।

15 में से 12

पिवोट टेबल साल और महीनों तक समूहित

जैसा कि आप उपर्युक्त छवि में देख सकते हैं, डेटा को साल पहले और फिर महीने तक समूहीकृत किया जाता है। प्रत्येक में प्लस और माइनस साइन होता है जो आपको डेटा को देखना चाहते हैं, इस पर निर्भर करता है कि आप विस्तार और पतन कर सकते हैं।

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

15 में से 13

स्लाइसर डालें (एक्सेल 2010 में नया)

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

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

15 में से 14

उपयोगकर्ता फ्रेंडली स्लाइसर के साथ पिवट टेबल

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

15 में से 15

स्लाइसर से मूल्य चुनें जो अपडेट पिवोट टेबल

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

स्लाइसर की शुरूआत ने वास्तव में पिवोटटेबल्स की उपयोगिता में सुधार किया है और एक्सेल 2010 को पेशेवर व्यावसायिक खुफिया उपकरण होने के करीब ले जाया गया है। PivotTables Excel 2010 में काफी सुधार हुआ है और जब नई PowerPivot के साथ संयुक्त एक बहुत उच्च प्रदर्शन विश्लेषणात्मक वातावरण बनाता है।