महीने की तारीख - एक्सेल टिप्स

माह-दर-तारीख बिक्री को पिवट टेबल में कैसे दिखाया जाए। यह एक द्वंद्वयुद्ध एक्सेल एपिसोड है।

वीडियो देखेंा

बिल की विधि
एक MTD सूत्र के साथ एक सहायक सेल जोड़ें
=AND(MONTH(TODAY())=MONTH(A2),DAY(A2)<=DAY(TODAY()))
उस फ़ील्ड को Slicer के रूप में जोड़ें जहाँ = True
बोनस टिप: समूह दैनिक तिथियाँ वर्षों तक
GetPivotData से परहेज करते हुए पिवट तालिका के बाहर एक गणना जोड़ें
माइक का दृष्टिकोण:
Ctrl + T का उपयोग करके डेटा को तालिका में बदलें। इससे अधिक डेटा तालिका में जोड़ा जा सकता है और सूत्र अपडेट हो सकते हैं।
DATE, MONTH, DAY फ़ंक्शंस के साथ SUMIFS
F4 को तीन बार दबाने से सिर्फ कॉलम का संदर्भ बंद हो जाता है।
बाहर देखें - यदि आप तालिका सूत्र को बग़ल में खींचते हैं, तो कॉलम बदल जाते हैं। कॉपी और पेस्ट - कोई समस्या नहीं है
पाठ में नंबर 1 सम्मिलित करने के लिए TEXT (दिनांक, प्रारूप) का उपयोग करना

वीडियो ट्रांसक्रिप्ट

बिल जेलन: अरे, वापस स्वागत है। यह एक और द्वंद्वयुद्ध एक्सेल पॉडकास्ट का समय है। मैं बिल जेलन हूं। मैं एक्सेल फन से माइक गिर्विन से जुड़ूंगा।

यह हमारा एपिसोड 181: मंथ टू डेट पिवट टेबल है।

खैर, हे, आज का सवाल- इस द्वंद्व के लिए आज का विचार माइक द्वारा भेजा गया है। वह कहते हैं, "क्या आप एक पिवट टेबल में मंथ टू डेट रिपोर्ट बना सकते हैं?"

अरी, चलो। तो यहाँ हमारे पास क्या है, हमारे पास दो साल की तारीखें हैं जनवरी 2016 से पूरे 2017 तक। अब बेशक मैं अप्रैल में यह रिकॉर्ड कर रहा हूं, यह अभी 15 अप्रैल है जब मैं द्वंद्वयुद्ध के अपने टुकड़े को रिकॉर्ड कर रहा हूं। और इसलिए यहाँ पर हमारे पास एक पिवट टेबल है जो लेफ्ट-हैंड की तरफ डेज़ दिखाती है, कैटेगिरी ऊपर की तरफ, और रेवेन्यू पिवट टेबल के बीच में है।

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

अब ठीक है, हमें इस अतिरिक्त डेटा को अपनी पिवट टेबल में प्राप्त करना है, इसलिए मैं यहाँ पिवट टेबल पर आता हूं, विश्लेषण करें और यह डेटा स्रोत को बदलने के लिए उतना मुश्किल नहीं है, बस उस बड़े बटन पर क्लिक करें और कहें कि हम कॉलम डी पर जाना चाहते हैं , ओके पर क्लिक करें। ठीक है, इसलिए अब हमारे पास वह अतिरिक्त फ़ील्ड है, मैं उस मंथ टू डेट फ़ील्ड के आधार पर एक स्लाइसर सम्मिलित करने जा रहा हूं और मैं केवल यह देखना चाहता हूं कि हमारा महीना टू डेट कैसे सही है। अब, क्या हमें स्लाइस की आवश्यकता है कि वह बड़ा हो? नहीं, हम संभवत: इसे दो कॉलम बना सकते हैं और सिर्फ दाहिने हाथ की तरफ ही इसे बाहर निकाल सकते हैं। तो अब हमारे पास 2016 में सभी तिथियां और 2017 में सभी तिथियां हैं; हालाँकि, इन साइड की तुलना करना वास्तव में अच्छा होगा। तो मैं वह Date फ़ील्ड लेने जा रहा हूँ और उसका विश्लेषण करूँगा। मैं ग्रुप द फील्ड जा रहा हूं, मैं इसे सिर्फ ईयर्स तक ग्रुप करने जा रहा हूं। मैं डॉन'टी वास्तव में व्यक्तिगत दिनों के बारे में परवाह है। मैं सिर्फ मंथ टू डेट जानना चाहता हूं। अब, हम कहाँ हैं? इसलिए मैं इसे वर्षों तक समूहीकृत करूंगा और हम इन 2 वर्षों के साथ समाप्त हो जाएंगे और मैं इसे फिर से व्यवस्थित करने जा रहा हूं, उन वर्षों को पार करने के लिए, श्रेणियां नीचे जाने के लिए डाल रहा हूं। और अब मैं देख रहा हूं कि हम पिछले साल कहां थे और इस साल हम कहां थे। अब ठीक है, क्योंकि मैंने समूहीकरण कर लिया है मुझे अब धुरी तालिका के अंदर एक परिकलित फ़ील्ड बनाने की अनुमति नहीं है। अगर मुझे वहां पर एक साल से अधिक की राशि चाहिए थी, तो मैं राइट क्लिक करूंगा, ग्रैंड टोटल को हटा दूंगा, ठीक है, और अब हम हैं, इसलिए% परिवर्तन, हम एक पिवट टेबल के बाहर हैं जो पिवट टेबल के अंदर इंगित कर रहे हैं। । हमें GetPivotData को या तो बंद करना सुनिश्चित करना है या बस इस तरह का एक सूत्र बनाना है: = J4 / I4-1 और यह एक सूत्र बनाता है जिसे हम बिना किसी परेशानी के कॉपी कर सकते हैं, जैसे।ठीक है, माइक, देखते हैं कि आपके पास क्या है।

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

अब, मैं ऐसा करने के दो दिन बाद कर रहा हूं। F2 मेरे पास TODAY फ़ंक्शन है जो हमेशा आज की वर्तमान तारीख के लिए तारीख की जानकारी होने वाला है जो कि यहां नीचे दिए गए सूत्रों द्वारा उपयोग किया जाएगा क्योंकि हम इसे अपडेट करना चाहते हैं। मैंने एक्सेल टेबल का भी उपयोग किया है और इसका नाम FSales है। यदि मैं Ctrl + डाउन एरो है, तो मुझे यह 4/14 दिखाई देता है, लेकिन मैं नवीनतम रिकॉर्ड जोड़ना चाहता हूं और जब हम अगले महीने पर जाते हैं, तो हमारे सूत्र अपडेट में शामिल होते हैं। Ctrl + ऊपर तीर। ठीक है, मेरे पास कॉलम हेडर के रूप में ईयर क्राइटेरिया है, पंक्ति हेडर के रूप में श्रेणी है, और फिर महीने और दिन के लिए विवरण उस सेल से आएंगे। इसलिए मैं केवल SUMIFS फ़ंक्शन का उपयोग करने जा रहा हूं क्योंकि हम कई शर्तों के साथ जोड़ रहे हैं, यहाँ की कुल आय है, हम एक Excel तालिका के लिए उस महान ट्रिक का उपयोग करने जा रहे हैं।ठीक ऊपर हम देखते हैं कि काले नीचे की ओर इंगित करने वाला तीर, BAM! यह उचित तालिका नाम और फिर वर्ग कोष्ठक में फ़ील्ड नाम, अल्पविराम में डालता है। मानदंड श्रेणी, हम दो बार दिनांक का उपयोग करने जा रहे हैं, इसलिए मैं दिनांक के साथ प्रारंभ करने जा रहा हूं। क्लिक करें, दिनांक स्तंभ, अल्पविराम है। अब मैं अप्रैल में हूं, इसलिए मुझे 1 से 1 अप्रैल तक की स्थिति बनानी होगी। इसलिए तुलनात्मक संचालक दोहरे उद्धरणों में "> =" हैं और मैं इसमें शामिल होने जा रहा हूं। अब मुझे कुछ तारीख के फॉर्मूले बनाने हैं जो हमेशा यहाँ दिखता है और इस विशेष वर्ष के लिए महीने का पहला बनाता है। इसलिए मैं DATE फ़ंक्शन का उपयोग करने जा रहा हूं। वर्ष, अच्छी तरह से कॉलम हेडर के रूप में मेरे पास वर्ष सही है और मैं F4 कुंजी को एक बार हिट करने जा रहा हूं, दो बार पंक्ति को लॉक करने के लिए लेकिन कॉलम को नहीं, इसलिए जब यह यहां से आगे बढ़ेगा तो हम 2017 तक चले जाएंगे, अल्पविराम महीना - मैं 'महीने की संख्या 1 से 12. प्राप्त करने के लिए MONTH फ़ंक्शन का उपयोग करने जा रहा हूँ। उस सेल में जो भी महीना हो, उसे सभी दिशाओं में बंद करने के लिए F4, लघु कोष्ठक और उसके बाद अल्पविराम, 1 यह हमेशा 1 के होने वाला है। कोई बात नहीं महीने क्या है, करीब कोष्ठक।

ठीक है, इसलिए यह मानदंड है। यह हमेशा रहेगा> = महीने का पहला, अल्पविराम, मानदंड श्रेणी दो मैं अपना दिनांक स्तंभ, अल्पविराम प्राप्त करने जा रहा हूं। मानदंड दो, ठीक है, यह <= ऊपरी सीमा होने जा रही है, इसलिए "<=" और & में। मैं धोखा देने जा रहा हूं, यह देखो। मैं इसे यहाँ से कॉपी करने जा रहा हूँ क्योंकि यह एक ही बात है, Ctrl-C Ctrl-V, दिन को छोड़कर, हमें DAY फ़ंक्शन का उपयोग करना है और हमेशा इस विशेष माह से दिन जो भी हो हमारी ऊपरी सीमा के रूप में प्राप्त करें। । F4 को सभी दिशाओं में बंद करने के लिए, दिनांक पर कोष्ठक बंद करें। ठीक है, इसलिए यह हमारा मापदंड दो है: अल्पविराम। मानदंड रेंज 3, यह श्रेणी है। यह वहाँ है, अल्पविराम और हमारी पंक्ति शीर्ष लेख है। तो यह एक हमें F4 एक दो तीन बार है, कॉलम लॉक करें लेकिन पंक्ति नहीं है इसलिए जब हम सूत्र को कॉपी करते हैं, तो हम Gizmo और विजेट पर जाएंगे,निकट कोष्ठक और वह सूत्र है। ड्रैग ओवर, डबल क्लिक करें और इसे नीचे भेजें। मैं देख सकता हूँ कि वहाँ परेशानी है। मैं बेहतर ढंग से अंतिम सेल तिरछे दूर दूर करने के लिए आते हैं। F2 को मारो। अब तालिका सूत्र नामकरण के लिए डिफ़ॉल्ट व्यवहार तब होता है जब आप सूत्रों को साइड में कॉपी करते हैं, वास्तविक कॉलम चलते हैं जैसे कि वे मिश्रित सेल संदर्भ थे। अब हम उन्हें बंद कर सकते हैं लेकिन मैं इस बार ऐसा नहीं करने जा रहा हूं। अब ध्यान दें कि जब आप इसे कॉपी करते हैं तो यह ठीक काम करता है लेकिन जब आप उस तरफ कॉपी करते हैं जब वास्तविक कॉलम चलता है। तो यह देखें, मैं Ctrl + C और Ctrl + V पर जा रहा हूं और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / आरंभ राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।ड्रैग ओवर, डबल क्लिक करें और इसे नीचे भेजें। मैं देख सकता हूँ कि वहाँ परेशानी है। मैं बेहतर ढंग से अंतिम सेल तिरछे दूर दूर करने के लिए आते हैं। F2 को मारो। अब तालिका सूत्र नामकरण के लिए डिफ़ॉल्ट व्यवहार तब होता है जब आप सूत्रों को साइड में कॉपी करते हैं, वास्तविक कॉलम चलते हैं जैसे कि वे मिश्रित सेल संदर्भ थे। अब हम उन्हें बंद कर सकते हैं लेकिन मैं इस बार ऐसा नहीं करने जा रहा हूं। अब ध्यान दें कि जब आप इसे कॉपी करते हैं तो यह ठीक काम करता है लेकिन जब आप उस तरफ कॉपी करते हैं जब वास्तविक कॉलम चलता है। तो यह देखें, मैं Ctrl + C और Ctrl + V पर जा रहा हूं और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / आरंभ राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।ड्रैग ओवर, डबल क्लिक करें और इसे नीचे भेजें। मैं देख सकता हूँ कि वहाँ परेशानी है। मैं बेहतर ढंग से अंतिम सेल तिरछे दूर दूर करने के लिए आते हैं। F2 को मारो। अब तालिका सूत्र नामकरण के लिए डिफ़ॉल्ट व्यवहार तब होता है जब आप सूत्रों को साइड में कॉपी करते हैं, वास्तविक कॉलम चलते हैं जैसे कि वे मिश्रित सेल संदर्भ थे। अब हम उन्हें बंद कर सकते हैं लेकिन मैं इस बार ऐसा नहीं करने जा रहा हूं। अब ध्यान दें कि जब आप इसे कॉपी करते हैं तो यह ठीक काम करता है लेकिन जब आप उस तरफ कॉपी करते हैं जब वास्तविक कॉलम चलता है। तो यह देखें, मैं Ctrl + C और Ctrl + V पर जा रहा हूं और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / आरंभ राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।मैं बेहतर पिछले दूर तिरछे दूर सेल के लिए आते हैं। F2 को मारो। अब तालिका सूत्र नामकरण के लिए डिफ़ॉल्ट व्यवहार तब होता है जब आप सूत्रों को साइड में कॉपी करते हैं, वास्तविक कॉलम चलते हैं जैसे कि वे मिश्रित सेल संदर्भ थे। अब हम उन्हें बंद कर सकते हैं लेकिन मैं इस बार ऐसा नहीं करने जा रहा हूं। अब ध्यान दें कि जब आप इसे कॉपी करते हैं तो यह ठीक काम करता है लेकिन जब आप उस तरफ कॉपी करते हैं जब वास्तविक कॉलम चलता है। तो यह देखें, मैं Ctrl + C और Ctrl + V पर जा रहा हूं और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / आरंभ राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।मैं बेहतर ढंग से अंतिम सेल तिरछे दूर दूर करने के लिए आते हैं। F2 मारो। अब तालिका सूत्र नामकरण के लिए डिफ़ॉल्ट व्यवहार तब होता है जब आप सूत्रों को साइड में कॉपी करते हैं, वास्तविक कॉलम चलते हैं जैसे कि वे मिश्रित सेल संदर्भ थे। अब हम उन्हें बंद कर सकते हैं लेकिन मैं इस बार ऐसा नहीं करने जा रहा हूं। अब ध्यान दें कि जब आप इसे कॉपी करते हैं तो यह ठीक काम करता है लेकिन जब आप उस तरफ कॉपी करते हैं जब वास्तविक कॉलम चलता है। तो यह देखें, मैं Ctrl + C और Ctrl + V पर जा रहा हूं और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / आरंभ राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।वास्तविक कॉलम चलते हैं जैसे कि वे मिश्रित सेल संदर्भ थे। अब हम उन्हें बंद कर सकते हैं लेकिन मैं इस बार ऐसा नहीं करने जा रहा हूं। अब ध्यान दें कि जब आप इसे कॉपी करते हैं तो यह ठीक काम करता है लेकिन जब आप उस तरफ कॉपी करते हैं जब वास्तविक कॉलम चलता है। तो यह देखें, मैं Ctrl + C और Ctrl + V पर जा रहा हूं और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / आरंभ राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।वास्तविक कॉलम चलते हैं जैसे कि वे मिश्रित सेल संदर्भ थे। अब हम उन्हें बंद कर सकते हैं लेकिन मैं इस बार ऐसा नहीं करने जा रहा हूं। अब ध्यान दें कि जब आप इसे कॉपी करते हैं तो यह ठीक काम करता है लेकिन जब आप उस तरफ कॉपी करते हैं जब वास्तविक कॉलम चलता है। तो यह देखें, मैं Ctrl + C और Ctrl + V पर जा रहा हूं और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / प्रारंभिक राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।m Ctrl + C और Ctrl + V पर जा रहा है और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / आरंभ राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।m Ctrl + C और Ctrl + V पर जा रहा है और फिर जब आप इसे साइड में कॉपी करते हैं तो F को कॉलम से जाने से बचाते हैं। डबल क्लिक करें और इसे नीचे भेजें। अब हमारा% परिवर्तन सूत्र = अंतिम राशि / आरंभ राशि -1, Ctrl + Enter, डबल क्लिक करें और इसे नीचे भेजें।

Now, before we go test it, now add some new records. I actually want to create this label up here so it's dynamic. And the way I'm going to do that is I'm going to say = sign and we're going to do a Text formula so anytime we want text and a formula, you have to put it in: “ and I'm going to type Sales Between, space”& and now I need to extract from that single date there, the first of the month to the end of the month. I'm going to use the TEXT function. The TEXT function can take a number dates or serial numbers, comma and use some custom number formatting in ” . I always want to see three-letter abbreviation for the month, mmm, I always want it as the first. Now if I put a 1 here, comma space yyy, that won't work. Wants to see that that gives us a value or because it doesn't like that 1. But we're allowed to insert a single character if we use forward slash, that's in Custom Number formatting. The mm and the yy will be understood by Custom Number formatting as month and year and now Custom Number format will understand to insert the number 1. F2 and now we're simply going to: &“ - ”&TEXT of that comma and now we’ll just use straight number formatting: “mmm spaceD, yyy”) Ctrl+Enter.

Now let's just, before we add some data, let's just change this. Pretending that today was showing: 3/15/2017 just like that, all the formulas are updating and our text formula is also, Ctrl+Z. Now, let's go down to the bottom of the data set, Ctrl+Down Arrow I want to add one new record. I'm in the last cell of the data set, I hit Tab to add a new record to our data set. I'm simply going to copy this record over here, Ctrl+Up Arrow, and there we can see the difference. If we wanted to check these formula values against the ones did: =relative cell reference= click on the sheet, we're going to click right in I4. We could see our formula up there, Ctrl+Enter. Actually I'm going to drag it down. Ctrl+Enter just populated everything I had highlighted. And of course, FALSE FALSE. Well guess what? = that amount right there -, click Ctrl+Down Arrow, Ctrl+Backspace, so I'm going to subtract that just to check and sure enough that was the exact amount we could look back there.

That is a little fun with some IFS and some date calculations, TODAY and even some text formula fun. Alright, throw back to.

Bill Jelen: Alright, Mike, that's awesome. So to wrap it up, Mike took the data and turned it into a table using Ctrl+T that allows more data to be added to the table, and the formulas will update, create that great little formula with SUMIFS, DATE, MONTH and DAY functions. Remember pressing F4 three times, locks the reference to just the column though watch out if you drag a table formula sideways using the Fill Handle, the columns change but Copy and Paste alleviates that problem. I never knew that one.

And then nice trick there using the heading with the text date format and that 1 to insert a number 1 in the text, in any character. I would allow to insert something, so you might have to do something like COOL to get an entire word in there but it would work.

Alright, my method was using a pivot table, I added a helper column with a MONTH TO DATE formula that one there using =AND checking if the MONTH and the DAY match. Add that field as a Slicer, set the Slicer =True. And then bonus tip: Group Daily Dates up to Years and then added a calculation outside of the pivot table while avoiding GetPivotData. And I'm interested, I still don't know how Mike did it with his formula. He managed to use the mouse to point to this equal to something, it’s on my pivot table and didn't get GetPivotData. Maybe, maybe he's turned it off.

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

फ़ाइल डाउनलोड करें

यहाँ नमूना फ़ाइल डाउनलोड करें: Duel181.xlsm