एक्सेल में फ़ैक्टरी कैलेंडर

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

• लेखांकन गणना में (वेतन, सेवा की लंबाई, छुट्टियां ...)
• रसद में - प्रसव के समय के सही निर्धारण के लिए, सप्ताहांत और छुट्टियों को ध्यान में रखते हुए (क्लासिक "छुट्टियों के बाद आओ?" याद रखें)
  
• परियोजना प्रबंधन में - शर्तों के सही आकलन के लिए, फिर से, कार्य-गैर-कार्य दिवसों को ध्यान में रखते हुए
• जैसे कार्यों का कोई भी उपयोग WORKDAY (कार्यदिवस) or शुद्ध कार्यकर्ता (नेटवर्क दिवस), क्योंकि उन्हें तर्क के रूप में छुट्टियों की सूची की आवश्यकता होती है
• Power Pivot और Power BI में Time इंटेलिजेंस फ़ंक्शंस (जैसे TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, आदि) का उपयोग करते समय
  
• ... आदि आदि - बहुत सारे उदाहरण।
  

यह उन लोगों के लिए आसान है जो कॉर्पोरेट ईआरपी सिस्टम जैसे 1 सी या एसएपी में काम करते हैं, क्योंकि उनमें उत्पादन कैलेंडर बनाया गया है। लेकिन एक्सेल उपयोगकर्ताओं के बारे में क्या?

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

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

ऐसा करने के लिए, वास्तव में, बिल्कुल भी मुश्किल नहीं है।

डेटा स्रोत

मुख्य सवाल यह है कि डेटा कहां से प्राप्त करें? एक उपयुक्त स्रोत की तलाश में, मैं कई विकल्पों से गुज़रा:

• मूल फरमान सरकार की वेबसाइट पर पीडीएफ प्रारूप में प्रकाशित होते हैं (यहां, उनमें से एक, उदाहरण के लिए) और तुरंत गायब हो जाते हैं - उनमें से उपयोगी जानकारी नहीं निकाली जा सकती।
• A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.

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

और खोज की प्रक्रिया में, गलती से एक अद्भुत चीज़ का पता चला - साइट http://xmlcalendar.ru/

अनावश्यक "तामझाम" के बिना, एक सरल, हल्की और तेज़ साइट, एक कार्य के लिए तेज - सभी को एक्सएमएल प्रारूप में वांछित वर्ष के लिए उत्पादन कैलेंडर देने के लिए। उत्कृष्ट!

यदि, अचानक, आप नहीं जानते हैं, तो XML एक टेक्स्ट प्रारूप है जिसमें विशेष सामग्री के साथ चिह्नित सामग्री है . एक्सेल सहित अधिकांश आधुनिक कार्यक्रमों द्वारा हल्का, सुविधाजनक और पठनीय।

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

पावर क्वेरी ऐड-इन का उपयोग करके इस डेटा को एक्सेल में लोड करना बाकी है (एक्सेल 2010-2013 के संस्करणों के लिए इसे माइक्रोसॉफ्ट वेबसाइट से मुफ्त में डाउनलोड किया जा सकता है, और एक्सेल 2016 और नए संस्करणों में यह पहले से ही डिफ़ॉल्ट रूप से अंतर्निहित है )

क्रियाओं का तर्क इस प्रकार होगा:

1. हम किसी एक वर्ष के लिए साइट से डेटा डाउनलोड करने का अनुरोध करते हैं
2. हमारे अनुरोध को एक समारोह में बदलना
3. हम इस फ़ंक्शन को सभी उपलब्ध वर्षों की सूची में लागू करते हैं, 2013 से शुरू होकर चालू वर्ष तक - और हमें स्वचालित अपडेट के साथ "सदा" उत्पादन कैलेंडर मिलता है। वोइला!
   

चरण 1. एक वर्ष के लिए कैलेंडर आयात करें

सबसे पहले, किसी एक वर्ष के लिए उत्पादन कैलेंडर लोड करें, उदाहरण के लिए, 2020 के लिए। ऐसा करने के लिए, एक्सेल में, टैब पर जाएं जानकारी (या पावर क्वेरीयदि आपने इसे एक अलग ऐड-ऑन के रूप में स्थापित किया है) और चुनें इंटरनेट से (वेब से). खुलने वाली विंडो में, साइट से कॉपी किए गए संबंधित वर्ष का लिंक पेस्ट करें:

क्लिक करने के बाद OK एक पूर्वावलोकन विंडो दिखाई देती है, जिसमें आपको बटन पर क्लिक करने की आवश्यकता होती है डेटा कनवर्ट करें (डेटा रूपांतरण) or डेटा बदलने के लिए (डेटा संपादित करें) और हम Power Query क्वेरी संपादक विंडो पर पहुंचेंगे, जहां हम डेटा के साथ काम करना जारी रखेंगे:

तुरंत आप सही पैनल में सुरक्षित रूप से हटा सकते हैं अनुरोध पैरामीटर (क्वेरी सेटिंग्स) कदम संशोधित प्रकार (परिवर्तित प्रकार) हमें उसकी जरूरत नहीं है।

छुट्टियों के कॉलम में तालिका में गैर-कार्य दिवसों के कोड और विवरण होते हैं - आप हरे शब्द पर क्लिक करके इसकी सामग्री को दो बार "गिरते हुए" देख सकते हैं तालिका:

वापस जाने के लिए, आपको दाहिने पैनल में उन सभी चरणों को हटाना होगा जो वापस दिखाई दिए हैं स्रोत (स्रोत).

दूसरी तालिका, जिसे इसी तरह से एक्सेस किया जा सकता है, में ठीक वही है जो हमें चाहिए - सभी गैर-कार्य दिवसों की तिथियां:

इस प्लेट को संसाधित करना बाकी है, अर्थात्:

1. दूसरे कॉलम द्वारा केवल छुट्टी की तारीखें (यानी वाले) फ़िल्टर करें विशेषता: टी

2. पहले कॉलम के शीर्षक पर राइट-क्लिक करके और कमांड का चयन करके - पहले को छोड़कर सभी कॉलम हटाएं अन्य कॉलम हटाएं (अन्य कॉलम हटाएं):

3. कमांड के साथ महीने और दिन के लिए अलग-अलग डॉट द्वारा पहले कॉलम को विभाजित करें स्प्लिट कॉलम - डिलीमीटर द्वारा टैब परिवर्तन (रूपांतरण - स्तंभ विभाजित करें - सीमांकक द्वारा):

4. और अंत में सामान्य तिथियों के साथ एक परिकलित कॉलम बनाएं। ऐसा करने के लिए, टैब पर एक कॉलम जोड़ना बटन पर क्लिक करें कस्टम कॉलम (कॉलम जोड़ें - कस्टम कॉलम) और दिखाई देने वाली विंडो में निम्न सूत्र दर्ज करें:

=#दिनांक चढ़ा हुआ(2020, [#»विशेषता:d.1″], [#»विशेषता:d.2″])

यहां, #date ऑपरेटर के तीन तर्क हैं: क्रमशः वर्ष, माह और दिन। पर क्लिक करने के बाद OK हमें सामान्य सप्ताहांत तिथियों के साथ आवश्यक कॉलम मिलता है, और शेष कॉलम को चरण 2 के अनुसार हटा दें

चरण 2. अनुरोध को फ़ंक्शन में बदलना

हमारा अगला कार्य 2020 के लिए बनाई गई क्वेरी को किसी भी वर्ष के लिए एक सार्वभौमिक फ़ंक्शन में बदलना है (वर्ष संख्या इसका तर्क होगा)। ऐसा करने के लिए, हम निम्नलिखित कार्य करते हैं:

1. पैनल का विस्तार करना (यदि पहले से विस्तारित नहीं है) पूछताछ (प्रश्न) Power Query विंडो में बाईं ओर:

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

कैलेंडर (2) की परिणामी प्रति पर फिर से राइट-क्लिक करने से कमांड का चयन होगा नाम बदलें (नाम बदलें) और एक नया नाम दर्ज करें - इसे रहने दें, उदाहरण के लिए, एफएक्सवर्ष:

3. हम कमांड का उपयोग करके आंतरिक पावर क्वेरी भाषा (इसे संक्षेप में "एम" कहा जाता है) में क्वेरी स्रोत कोड खोलते हैं उन्नत संपादक टैब समीक्षा(देखें - उन्नत संपादक) और हमारे अनुरोध को किसी भी वर्ष के लिए एक समारोह में बदलने के लिए वहां छोटे बदलाव करें।

ये था:

बाद:

यदि आप विवरण में रुचि रखते हैं, तो यहां:

• (वर्ष संख्या के रूप में) =>  - हम घोषणा करते हैं कि हमारे फ़ंक्शन में एक संख्यात्मक तर्क होगा - एक चर वर्ष
• चर चिपकाना वर्ष चरण में वेब लिंक के लिए स्रोत. चूंकि Power Query आपको संख्याओं और टेक्स्ट को ग्लू करने की अनुमति नहीं देता है, हम फ़ंक्शन का उपयोग करके वर्ष संख्या को फ़्लाई पर टेक्स्ट में परिवर्तित करते हैं नंबर.टूटेक्स्ट
• हम वर्ष चर को 2020 के लिए अंतिम चरण में प्रतिस्थापित करते हैं #"कस्टम ऑब्जेक्ट जोड़ा गया«, जहां हमने टुकड़ों से तारीख बनाई।
  

क्लिक करने के बाद अंत हमारा अनुरोध एक कार्य बन जाता है:

चरण 3. सभी वर्षों के लिए कैलेंडर आयात करें

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

1. हम दाएं माउस बटन के साथ ग्रे खाली जगह में बाएं क्वेरी पैनल में क्लिक करते हैं और क्रमिक रूप से चयन करते हैं नया अनुरोध - अन्य स्रोत - खाली अनुरोध (नई क्वेरी - अन्य स्रोतों से - खाली क्वेरी):

2. हमें उन सभी वर्षों की एक सूची तैयार करने की आवश्यकता है जिसके लिए हम कैलेंडर का अनुरोध करेंगे, अर्थात 2013, 2014 … 2020। ऐसा करने के लिए, दिखाई देने वाली खाली क्वेरी के सूत्र बार में, कमांड दर्ज करें:

संरचना:

={नंबरए..नंबरबी}

... Power Query में A से B तक के पूर्णांकों की सूची बनाता है। उदाहरण के लिए, व्यंजक

={1}

… 1,2,3,4,5 की सूची तैयार करेगा।

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

3. वर्षों का परिणामी सेट, हालांकि यह काफी पर्याप्त दिखता है, पावर क्वेरी के लिए एक तालिका नहीं है, बल्कि एक विशेष वस्तु है - सूची (सूची). लेकिन इसे तालिका में बदलना कोई समस्या नहीं है: बस बटन पर क्लिक करें जोड़ मिलाने के लिए (जोड़ मिलाने के लिए) ऊपरी बाएँ कोने में:

4. फिनिश लाइन! हमारे द्वारा पहले बनाए गए फ़ंक्शन को लागू करना एफएक्सवर्ष वर्षों की परिणामी सूची के लिए। ऐसा करने के लिए, टैब पर एक कॉलम जोड़ना बटन दबाएँ कस्टम फ़ंक्शन को कॉल करें (कॉलम जोड़ें - कस्टम फ़ंक्शन को आमंत्रित करें) और इसका एकमात्र तर्क सेट करें - कॉलम Column1 पिछले कुछ वर्षों में:

क्लिक करने के बाद OK हमारा कार्य एफएक्सवर्ष आयात प्रत्येक वर्ष बदले में काम करेगा और हमें एक कॉलम मिलेगा जहां प्रत्येक सेल में गैर-कार्य दिवसों की तारीखों के साथ एक तालिका होगी (तालिका की सामग्री स्पष्ट रूप से दिखाई दे रही है यदि आप सेल की पृष्ठभूमि में क्लिक करते हैं) शब्द तालिका):

यह कॉलम हेडर में डबल एरो वाले आइकन पर क्लिक करके नेस्टेड टेबल की सामग्री का विस्तार करने के लिए बनी हुई है खजूर (सही का निशान लगाना मूल स्तंभ नाम को उपसर्ग के रूप में प्रयोग करें इसे हटाया जा सकता है):

… और पर क्लिक करने के बाद OK हमें वह मिलता है जो हम चाहते थे - 2013 से चालू वर्ष तक सभी छुट्टियों की सूची:

पहला, पहले से ही अनावश्यक कॉलम, हटाया जा सकता है, और दूसरे के लिए, डेटा प्रकार सेट करें डेटा (तारीख) कॉलम शीर्षक में ड्रॉपडाउन सूची में:

क्वेरी का नाम बदलकर कुछ अधिक सार्थक किया जा सकता है अनुरोध1 और फिर परिणाम को कमांड का उपयोग करके गतिशील "स्मार्ट" तालिका के रूप में शीट पर अपलोड करें बंद करें और डाउनलोड करें टैब होम (होम - बंद करें और लोड करें):

आप भविष्य में बनाए गए कैलेंडर को कमांड के माध्यम से दाएँ फलक में तालिका या क्वेरी पर राइट-क्लिक करके अपडेट कर सकते हैं अपडेट करें और सहेजें. या बटन का प्रयोग करें सभी को रीफ्रेश करें टैब जानकारी (तारीख - सभी को रिफ्रेश करें) या कीबोर्ड शॉर्टकट कंट्रोल+ऑल्ट+F5.

बस इतना ही।

अब आपको छुट्टियों की सूची को खोजने और अद्यतन करने के लिए समय और विचार-ईंधन बर्बाद करने की आवश्यकता नहीं है - अब आपके पास "सदा" उत्पादन कैलेंडर है। किसी भी मामले में, जब तक साइट के लेखक http://xmlcalendar.ru/ अपनी संतानों का समर्थन करते हैं, जो मुझे आशा है, बहुत लंबे समय के लिए होगा (फिर से उनके लिए धन्यवाद!)।

• Power Query के माध्यम से इंटरनेट से उत्कृष्टता प्राप्त करने के लिए बिटकॉइन दर आयात करें
• WORKDAY फ़ंक्शन का उपयोग करके अगला व्यावसायिक दिन ढूँढना
  
• दिनांक अंतराल का प्रतिच्छेदन ज्ञात करना