नैशविले में इयान को एक सिस्टम डाउनलोड से हर दिन डेटा मिलता है। दिनांक कॉलम में दिनांक + समय होता है। यह धुरी तालिका में प्रति दिन एक सेल के सारांश के बजाय प्रति दिन कई पंक्तियाँ होती हैं।
यह आलेख समस्या को हल करने के तीन तरीके दिखाता है।
नीचे दिया गया चित्र दिनांक समय के साथ दिनांक स्तंभ दिखाता है।
एक समाधान यह है कि इसे धुरी तालिका में ठीक किया जाए।
- दिनांक फ़ील्ड को पंक्तियों क्षेत्र में खींचें। यह दिनांक + समय के साथ दिखाई देता है।
- किसी भी सेल का चयन करें जिसमें एक तिथि और समय हो।
- धुरी तालिका उपकरण विश्लेषण टैब पर, समूह फ़ील्ड चुनें।
- समूहीकरण संवाद में, दिनों का चयन करें। अचयन महीने। जब आप ठीक पर क्लिक करते हैं, तो धुरी तालिका प्रति दिन एक पंक्ति दिखाएगी।
एक और थकाऊ समाधान डाउनलोड के बाद एक नया कॉलम जोड़ना है। जैसा कि इस आंकड़े में दिखाया गया है, कॉलम J में सूत्र है =INT(D2)।
एक तारीख + समय एक पूर्णांक दिन होता है और उसके बाद दशमलव समय होता है। INT फ़ंक्शन का उपयोग करके दिनांक + समय का पूर्णांक भाग प्राप्त करने के लिए, आप दिनांक को अलग करते हैं।
यह ठीक काम करेगा, लेकिन अब आपको हर बार डेटा डाउनलोड करते समय नया कॉलम जोड़ना होगा।
मेरा पसंदीदा समाधान पावर क्वेरी का उपयोग करना है। पावर क्वेरी केवल एक्सेल के विंडोज संस्करणों में काम करती है। आप Android, IOS या Mac पर पावर क्वेरी का उपयोग नहीं कर सकते।
Power Query Excel 2016 में बनाया गया है। Excel 2010 और 2013 के लिए, आप Microsoft से पावर क्वेरी डाउनलोड कर सकते हैं।
सबसे पहले, डाउनलोड किए गए डेटा को Ctrl + T का उपयोग करके तालिका में कनवर्ट करें। फिर डेटा टैब पर, तालिका / श्रेणी से चुनें।
पावर क्वेरी ग्रिड में, दिनांक कॉलम चुनें। ट्रांसफॉर्म टैब पर जाएं। दिनांक प्रकार ड्रॉपडाउन खोलें और दिनांक चुनें। यदि आपको रिप्लेसमेंट स्टेप या न्यू स्टेप के बारे में कोई संदेश मिलता है, तो रिप्लेस स्टेप चुनें।
पावर क्वेरी के होम टैब पर, बंद और लोड चुनें।
आपका परिणाम एक नई शीट पर दिखाई देगा। उस शीट से पिवट टेबल बनाएं।
अगली बार जब आपको नया डेटा मिले, तो इन चरणों का पालन करें:
- मूल डेटा सेट पर नया डेटा पेस्ट करें।
- पावर क्वेरी के परिणाम के साथ शीट पर जाएं। क्वेरी पैनल को चौड़ा करें ताकि आप रिफ्रेश देख सकें। ताज़ा करें पर क्लिक करें।
- धुरी तालिका को ताज़ा करें।
वीडियो देखेंा
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट, एपिसोड 2203 से एक्सेल सीखें: ट्रंकैट डेट और टाइम टू जस्ट डेट।
अरे, नेटकास्ट में वापस स्वागत है, मैं बिल जेलन हूं। मेरा नैशविले पावर एक्सेल सेमिनार में इयान से आज का सवाल। इसे देखें: इयान हर दिन अपने सिस्टम से एक डाउनलोड प्राप्त कर रहा है, और उसके पास-- डेट कॉलम में-- यह दिनांक और समय है, और वह चीजों को खराब कर रहा है क्योंकि जब इयान एक पिवट टेबल बनाता है, और डेट्स को साथ रखता है। बाएं हाथ की ओर, प्रति दिनांक केवल एक पंक्ति प्राप्त करने के बजाय, उसे प्रत्येक दिनांक और समय के लिए एक पंक्ति मिल रही है।
अब, यहाँ आकर कहना होगा, Group Field, और Group to Day, OK पर क्लिक करें। और, आप जानते हैं, कि कुछ अतिरिक्त क्लिक हैं जो मुझे नहीं लगता कि हमें करना है। और, इसलिए, इस पॉडकास्ट के लिए, अगले कुछ पॉडकास्ट, हम पावर क्वेरी पर एक नज़र डालने जा रहे हैं। अब, Get & Transform डेटा के तहत, इसे यहां Excel 2016 में बनाया गया है। यदि आपके पास 2010 या 2013 में विंडोज है, तो मैक पर नहीं, आप एक्सेल से मुफ्त में पावर क्वेरी डाउनलोड कर सकते हैं। और पावर क्वेरी एक टेबल या रेंज पर काम करना चाहती है।
इसलिए मैं डेटा का एक सेल चुनने जा रहा हूं, टेबल के लिए Ctrl + T दबाएं, एकदम सही। और फिर, मेरे पावर क्वेरी टैब पर, डेटा, टेबल / रेंज से, मैं दिनांक कॉलम और ट्रांसफ़ॉर्म चुनने जा रहा हूं, और कहता हूं कि मैं चाहता हूं कि यह दिनांक और समय न हो, लेकिन सिर्फ एक दिनांक। मैं रिप्लेस करंट को चुनूंगा, और फिर होम, क्लोज एंड लोड, और हमें एक नया शीट फॉर्म मिलेगा जिसमें डेटा परिवर्तित किया गया है। अब, यहाँ से, निश्चित रूप से, हम एक धुरी के साथ संक्षेप कर सकते हैं और यह सही होगा।
मुझे लगता है कि इस पद्धति का लाभ, पावर क्वेरी, जब इयान को अधिक डेटा मिलता है … तो यहां मेरे पास नया डेटा है, मैं इस नए डेटा को लेने जा रहा हूं, डेटा की प्रतिलिपि बनाता हूं, और बस अपने पुराने डेटा पर यहां आता हूं और नीचे बस पेस्ट करें। और, नोटिस करें कि अभी टेबल मार्कर का अंत रो 474 में है। जब मैं पेस्ट करता हूं, टेबल मार्कर का अंत नए डेटा के निचले भाग में चला जाता है। ठीक है? और यह क्वेरी तालिका के आधार पर लिखी गई है। ठीक है? तो, जैसा कि तालिका बढ़ती है।
अब, यहाँ एक परेशानी यह है कि यदि आप पहली बार पावर क्वेरी का उपयोग कर रहे हैं, तो पावर क्वेरी उस विस्तृत - केवल आधी चौड़ी है - और आप रीफ़्रेश आइकन नहीं देख सकते हैं। तो, आपको इसे इस तरह से यहां खींचना होगा और उस शानदार रिफ्रेश आइकन को प्राप्त करना होगा, और रिफ्रेश पर क्लिक करें। अभी, हमारे पास 473 पंक्तियाँ भरी हुई हैं, और अब 563 पंक्तियाँ भरी हुई हैं। अपनी धुरी तालिका बनाएं, और जीवन महान है।
अब, हे, देखो, मुझे पता है कि यहाँ एक नया कॉलम जोड़ना संभव होगा, = INT (D2) - - दिनांक - कॉपी कि नीचे, कॉपी और पेस्ट स्पेशल वैल्यूज़। लेकिन फिर आप हर एक दिन ऐसा करने के लिए हुक पर हैं। ठीक है? इसलिए जबकि कम से कम तीन अलग-अलग तरीकों से हमने इसे हल करने के इस वीडियो के बारे में बात की, मेरे लिए, पावर क्वेरी और बस, रिफ्रेश पर क्लिक करने की क्षमता, जाने का रास्ता है।
पावर क्वेरी, मैं अपनी नई पुस्तक, MrExcel LIVe, द 54 ग्रेटेस्ट एक्सेल टिप्स ऑफ ऑल टाइम में इसके बारे में बात करता हूं।
आज के एपिसोड का रैप-अप: नैशविले में इयान को हर दिन एक सिस्टम से डेटा मिलता है, डेट कॉलम में डेट के अलावा टाइम टू दोनों भी शामिल होते हैं- यह उसकी पिवट टेबल को खराब कर देता है, इसलिए उसे … तीन, संभावित चीजों: एक, उपयोग INT फ़ंक्शन, कॉपी, पेस्ट मान; दूसरा, बस इसे दिनांक और समय के रूप में छोड़ दें और फिर तिथि द्वारा धुरी तालिका को समूहीकृत करें; या, एक बेहतर समाधान, पावर क्वेरी, डाउनलोड किए गए डेटा को Ctrl + T के साथ तालिका में सेट किया गया है, और फिर डेटा टैब पर, तालिका से चुनें, दिनांक और समय कॉलम चुनें, पावर क्वेरी में रूपांतरण टैब पर जाएं, कन्वर्ट करें उसे दिनांक, बंद करें और लोड करें, उससे पिवट तालिका बनाएँ। अगली बार जब आप डेटा प्राप्त करें, तो मूल तालिका में पेस्ट करें, क्वेरी पैनल पर जाएं, ताज़ा करें पर क्लिक करें, और आप जाने के लिए अच्छे हैं।
अरे, आज के वीडियो से कार्यपुस्तिका डाउनलोड करने के लिए, YouTube विवरण में URL पर जाएं।
मैं नैशविले में अपने सेमिनार में दिखाने के लिए इयान को धन्यवाद देना चाहता हूं, मैं आपको रोककर धन्यवाद देना चाहता हूं। मैं आपको अगली बार एक और नेटकास्ट से देखूंगा।
एक्सेल फ़ाइल डाउनलोड करें
एक्सेल फ़ाइल डाउनलोड करने के लिए: date-time-to-date.xlsx
पावर क्वेरी प्रक्रिया को और बेहतर बनाने के लिए:
- सुनिश्चित करें कि डाउनलोड वर्कबुक हमेशा एक ही नाम के साथ एक ही फ़ोल्डर में सहेजी जाती है।
- रिक्त कार्यपुस्तिका से प्रारंभ करें। DownloadedFileTransformed.xlsx जैसे नाम के साथ सहेजें
- रिक्त कार्यपुस्तिका से, डेटा, फ़ाइल से, कार्यपुस्तिका से। चरण 1 से फ़ाइल को इंगित करें।
- स्वच्छ डेटा चुनें। दिनांक / समय को दिनांक में बदलना। बंद करें और लोड करें।
- क्वेरी पैनल पर राइट-क्लिक करें और गुण चुनें। फ़ाइल खोलते समय ताज़ा डेटा चुनें।
- प्रत्येक दिन, काम पर जाते हैं। अपनी कॉफी ले आओ। सुनिश्चित करें कि # 1 के लिए एक नई फ़ाइल फ़ोल्डर में है।
- अपनी DownloadFileTransformed.xlsx वर्कबुक खोलें। इसमें नया डेटा होगा और तारीखें सही होंगी।
एक्सेल थॉट्स ऑफ द डे
मैंने अपने एक्सेल के बारे में सलाह के लिए अपने एक्सेल मास्टर दोस्तों से पूछा है। विचार करने के लिए आज का विचार:
"मान्यता आपकी दोस्त है।"
दुआने औबिन
