यह सवाल हर दशक में एक बार सामने आता है: क्या आप एक पिवट टेबल में मेडियन कर सकते हैं। परंपरागत रूप से, उत्तर नहीं था मुझे 2000 में वापस याद है जब मैंने एक्सेल एमवीपी जुआन पाब्लो गोंजालेज को एक भयानक मैक्रो लिखने के लिए काम पर रखा था, जो रिपोर्ट बनाई गई थी जो पिवट टेबल की तरह दिखती थी, लेकिन मेडियन थे।
इसलिए जब मेरे ह्यूस्टन पावर एक्सेल सेमिनार में एलेक्स ने मध्यस्थ बनाने के बारे में पूछा, तो मुझे "नहीं" कहने की जल्दी थी। लेकिन फिर … मुझे आश्चर्य है कि अगर DAX में एक मध्ययुगीन फ़ंक्शन था। एक त्वरित Google खोज और हाँ! मेडियन के लिए एक DAX फ़ंक्शन है।
धुरी तालिका में DAX का उपयोग करने में क्या लगता है? आपको Excel 2013 या नए चल रहे Excel के Windows संस्करण की आवश्यकता है।
यहां मेरा बहुत ही सरल डेटा सेट है जिसका उपयोग मैं यह जांचने के लिए करूंगा कि पिवट टेबल मेडियंस की गणना कैसे की जाए। आप देख सकते हैं कि शिकागो के लिए मंझला 5000 होना चाहिए और क्लीवलैंड के लिए मंझला 17000 होना चाहिए। शिकागो के मामले में, पांच मूल्य हैं, इसलिए मेडियन 3 उच्चतम मूल्य होगा। लेकिन पूरे डेटा सेट के लिए, 12 मान हैं। इसका मतलब है कि मंझला कहीं 11000 से 13000 के बीच है। 12000 लौटाने के लिए एक्सेल का औसत उन दो मूल्यों से है।
शुरू करने के लिए, अपने डेटा में एक सेल का चयन करें और डेटा को तालिका के रूप में प्रारूपित करने के लिए Ctrl + T दबाएं।
फिर, सम्मिलित करें, धुरी तालिका चुनें। PivotTable डायलॉग सम्मिलित करें में, डेटा मॉडल में यह डेटा जोड़ें के लिए बॉक्स चुनें।
धुरी तालिका फ़ील्ड में, क्षेत्र और जिला चुनें। लेकिन सेल्स का चुनाव न करें। इसके बजाय, तालिका शीर्षक पर राइट-क्लिक करें और नया माप चुनें। "माप" एक परिकलित फ़ील्ड के लिए एक फैंसी नाम है। नियमित धुरी तालिकाओं में गणना किए गए क्षेत्रों की तुलना में उपाय अधिक शक्तिशाली हैं।
उपाय को परिभाषित करें संवाद में, नीचे दी गई चार प्रविष्टियाँ भरें:
- उपाय का नाम: बिक्री का माध्यिका
- सूत्र
=MEDIAN((Sales)) - संख्या स्वरूप: संख्या
- दशम स्थान: ०
माप बनाने के बाद, इसे फ़ील्ड की सूची में जोड़ा जाता है, लेकिन आपको इसे पिवट टेबल के मान क्षेत्र में जोड़ने के लिए प्रविष्टि का चयन करना होगा। जैसा कि आप नीचे देख सकते हैं, धुरी तालिका सही ढंग से माध्यकों की गणना कर रही है।
वीडियो देखेंा
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2197: एक पिवट टेबल में मेडियन।
मुझे आपको बताने के लिए मिला, मैं इस बारे में बहुत उत्साहित हूं। जब मैं ह्यूस्टन में एक पॉवर एक्सेल सेमिनार कर रहा था, और एलेक्स ने हाथ उठाते हुए कहा, "अरे, क्या पिवट टेबल में माध्यिका करने का कोई तरीका है?" नहीं, आप पिवट टेबल में माध्यिका नहीं कर सकते। मुझे याद है कि 25 साल पहले, मेरे अच्छे दोस्त, जुआन पाब्लो गोंजालेस, वास्तव में एक मैडियन के बराबर करने के लिए एक मैक्रो लाए थे, एक पिवट टेबल का उपयोग नहीं करते थे - यह सिर्फ संभव नहीं है।
लेकिन मुझे चिंगारी लगी थी। मैंने कहा, "एक सेकंड रुको," और मैं एक ब्राउज़र खोलता हूं, और मैं "DAX मंझला" खोजता हूं, और, निश्चित रूप से पर्याप्त है, DAX में एक मीडिया फ़ंक्शन है, जिसका अर्थ है कि हम इस समस्या को हल कर सकते हैं।
ठीक है, इसलिए, DAX का उपयोग करने के लिए, आपको Excel 2013 या Excel 2016, या Excel 2010 में होना चाहिए, और Power Pivot ऐड-इन होना चाहिए; आपके पास पावर पिवट टैब नहीं है, हमारे पास सिर्फ डेटा मॉडल होना चाहिए। ठीक है? इसलिए मैं इस डेटा को चुनने जा रहा हूं, मैं इसे Ctrl + T के साथ तालिका में बनाने जा रहा हूं, और वे इसे "तालिका 4" का अकल्पनीय नाम देते हैं। आपके मामले में, यह "तालिका 1" हो सकती है। और हम एक पिवट टेबल डालेंगे, इस डेटा को डेटा मॉडल में जोड़ेंगे, ठीक पर क्लिक करेंगे, और हम बाएं हाथ की ओर क्षेत्रीय को चुनने जा रहे हैं, लेकिन बिक्री नहीं। इसके बजाय, मैं एक नया परिकलित माप बनाना चाहता हूं। इसलिए मैं यहां टेबल पर आया और मैंने राइट-क्लिक किया और कहा, Add मेजर। और इस उपाय को "मेडियन ऑफ़ सेल्स" कहा जाने वाला है, और सूत्र का अर्थ है: = मेडियन। वहां टैब दबाएं और बिक्री चुनें,बंद कोष्ठक। मैं इसे शून्य दशमलव स्थानों की संख्या के रूप में चुन सकता हूं, एक हजार विभाजक का उपयोग कर सकता हूं और ओके पर क्लिक कर सकता हूं। और, देखते हैं, हमारे नए क्षेत्र को यहां जोड़ा गया है, हमें इसे जोड़ने के लिए चेक-मार्क करना होगा, और यह कहता है कि मिडवेस्ट के लिए मंझला 12,000 है।
अब चलो कि जाँच करें। तो, यहाँ, पूरे मिडवेस्ट के, पूरे डेटा का माध्य 11,000 और 13,000 के बीच सेट है। तो यह औसतन 11 और 13 है, जो कि नियमित एक्सेल में औसत दर्जे का होता है। अब इसमें जिला जोड़ते हैं और यह कहते हैं कि शिकागो का 5,000, क्लीवलैंड का मंझला 17,000 है; मिडवेस्ट कुल और भव्य कुल 12,000। यह सब सही है। कितना भयानक है? अंत में, एक धुरी तालिका में माध्यिका, परिकलित फ़ील्ड या माप के लिए धन्यवाद, जैसा कि इसे कहा जाता है, और डेटा मॉडल।
अब, मेरे पसंदीदा पावर टिप्स- मेरे लाइव पावर एक्सेल सेमिनार के लिए कई टिप्स- इस किताब में LIVe, द 54 ग्रेटेस्ट टिप्स ऑफ ऑल टाइम। पुस्तक के बारे में अधिक पढ़ने के लिए शीर्ष दाएं कोने में उस "I" पर क्लिक करें।
ठीक है। लपेटो-अप: क्या आप एक धुरी तालिका में एक मंझला कर सकते हैं? अरे नहीं, हां, हां आप कर सकते हैं, डेटा मॉडल के लिए धन्यवाद। आप एक माध्यिका बना सकते हैं; आपके डेटा को तालिका में बनाने के लिए Ctrl + T; धुरी तालिका डालें; और उस बॉक्स को चेक करें, इस डेटा को डेटा मॉडल में जोड़ें; तालिका नाम पर राइट-क्लिक करें और नया उपाय चुनें, और माप = MEDIAN ((बिक्री)) होगा। यह विस्मयकारी है।
एलेक्स को मेरे सेमिनार में दिखाने के लिए और उस सवाल को पूछने के लिए धन्यवाद, द्वारा रोकने के लिए धन्यवाद। मैं आपको अगली बार एक और नेटकास्ट से देखूंगा।
एक्सेल फ़ाइल डाउनलोड करें
एक्सेल फ़ाइल डाउनलोड करने के लिए: पिवट-टेबल-मेडियन.एक्सएलएक्स
