Excel मध्ये फॅक्टरी कॅलेंडर

उत्पादन दिनदर्शिका, म्हणजे तारखांची यादी, जिथे सर्व अधिकृत कामकाजाचे दिवस आणि सुट्ट्या त्यानुसार चिन्हांकित केल्या जातात - मायक्रोसॉफ्ट एक्सेलच्या कोणत्याही वापरकर्त्यासाठी एक अत्यंत आवश्यक गोष्ट. सराव मध्ये, आपण त्याशिवाय करू शकत नाही:

• लेखा गणनेमध्ये (पगार, सेवेची लांबी, सुट्ट्या ...)
• लॉजिस्टिक्समध्ये - आठवड्याचे शेवटचे दिवस आणि सुट्ट्या लक्षात घेऊन डिलिव्हरीच्या वेळेच्या योग्य निर्धारणासाठी (क्लासिक "सुट्टीनंतर या?") लक्षात ठेवा.
  
• प्रोजेक्ट मॅनेजमेंटमध्ये - अटींच्या अचूक अंदाजासाठी, खात्यात, पुन्हा, कामाचे-नॉन-वर्किंग दिवस
• सारख्या फंक्शन्सचा कोणताही वापर कामाचा दिवस (कामाचा दिवस) or शुद्ध कामगार (नेटवर्कडे), कारण त्यांना वाद म्हणून सुट्टीची यादी आवश्यक आहे
• Power Pivot आणि Power BI मध्ये टाइम इंटेलिजन्स फंक्शन्स (जसे की TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, इ.) वापरताना
  
• … इ. इ. - बरीच उदाहरणे.
  

जे 1C किंवा SAP सारख्या कॉर्पोरेट ERP सिस्टीममध्ये काम करतात त्यांच्यासाठी हे सोपे आहे, कारण त्यांच्यामध्ये उत्पादन कॅलेंडर तयार केले आहे. पण एक्सेल वापरकर्त्यांचे काय?

आपण, अर्थातच, असे कॅलेंडर व्यक्तिचलितपणे ठेवू शकता. परंतु नंतर तुम्हाला वर्षातून किमान एकदा (किंवा त्याहूनही अधिक वेळा, "जॉली" 2020 प्रमाणे) अद्यतनित करावे लागेल, आमच्या सरकारने शोधलेले सर्व शनिवार व रविवार, बदल्या आणि गैर-कामाचे दिवस काळजीपूर्वक प्रविष्ट करा. आणि नंतर दर पुढच्या वर्षी ही प्रक्रिया पुन्हा करा. कंटाळवाणेपणा.

थोडे वेडे होऊन एक्सेलमध्ये "शाश्वत" फॅक्टरी कॅलेंडर बनवण्याबद्दल काय? जो स्वतः अपडेट करतो, इंटरनेटवरून डेटा घेतो आणि कोणत्याही गणनेमध्ये त्यानंतरच्या वापरासाठी नेहमी नॉन-वर्किंग दिवसांची अद्ययावत यादी तयार करतो? भुरळ पाडणारी?

हे करणे, खरं तर, अजिबात कठीण नाही.

माहितीचा स्रोत

डेटा कुठून मिळवायचा हा मुख्य प्रश्न आहे. योग्य स्त्रोताच्या शोधात, मी अनेक पर्यायांमधून गेलो:

• मूळ आदेश सरकारच्या वेबसाइटवर PDF स्वरूपात प्रकाशित केले जातात (येथे, त्यापैकी एक, उदाहरणार्थ) आणि ताबडतोब गायब होतात – उपयुक्त माहिती त्यांच्यामधून काढता येत नाही.
• एक मोहक पर्याय, पहिल्या दृष्टीक्षेपात, "फेडरेशनचे ओपन डेटा पोर्टल" असल्याचे दिसते, जेथे संबंधित डेटा सेट आहे, परंतु, जवळून तपासणी केल्यावर, सर्वकाही दुःखी असल्याचे दिसून आले. एक्सेलमध्ये आयात करण्यासाठी साइट अत्यंत गैरसोयीची आहे, तांत्रिक समर्थन प्रतिसाद देत नाही (स्वत: पृथक?), आणि डेटा स्वतःच तेथे बर्याच काळापासून जुना आहे - 2020 चे उत्पादन कॅलेंडर नोव्हेंबर 2019 मध्ये शेवटचे अद्यतनित केले गेले होते (अपमानास्पद!) आणि , अर्थातच, उदाहरणार्थ, आमचा "कोरोनाव्हायरस' आणि 2020 चा 'मतदान' शनिवार व रविवार समाविष्ट नाही.

अधिकृत स्त्रोतांबद्दल भ्रमनिरास होऊन, मी अनधिकृत खोदण्यास सुरुवात केली. इंटरनेटवर त्यापैकी बरेच आहेत, परंतु त्यापैकी बहुतेक, पुन्हा, एक्सेलमध्ये आयात करण्यासाठी पूर्णपणे अनुपयुक्त आहेत आणि सुंदर चित्रांच्या रूपात उत्पादन दिनदर्शिका देतात. पण ते भिंतीवर टांगणे आपल्यासाठी नाही, बरोबर?

आणि शोधण्याच्या प्रक्रियेत, चुकून एक आश्चर्यकारक गोष्ट सापडली - साइट http://xmlcalendar.ru/

अनावश्यक “फ्रिल” शिवाय, एक साधी, हलकी आणि वेगवान साइट, एका कार्यासाठी तीक्ष्ण केली – प्रत्येकाला इच्छित वर्षासाठी XML स्वरूपात उत्पादन दिनदर्शिका देण्यासाठी. उत्कृष्ट!

जर, अचानक, तुम्हाला माहिती नसेल, तर XML हा मजकूर स्वरूप आहे ज्यामध्ये विशेष चिन्हांकित केलेली सामग्री आहे . हलके, सोयीस्कर आणि एक्सेलसह बर्‍याच आधुनिक प्रोग्रामद्वारे वाचनीय.

फक्त बाबतीत, मी साइटच्या लेखकांशी संपर्क साधला आणि त्यांनी पुष्टी केली की साइट 7 वर्षांपासून अस्तित्त्वात आहे, त्यावरील डेटा सतत अद्यतनित केला जातो (त्यांच्याकडे यासाठी गिथबवर शाखा देखील आहे) आणि ते ते बंद करणार नाहीत. आणि मला अजिबात हरकत नाही की तुम्ही आणि मी आमच्या कोणत्याही प्रोजेक्टसाठी आणि एक्सेलमधील गणनेसाठी त्यातून डेटा लोड करू. मोफत आहे. असे लोक अजूनही आहेत हे जाणून आनंद झाला! आदर!

पॉवर क्वेरी अॅड-इन वापरून हा डेटा एक्सेलमध्ये लोड करणे बाकी आहे (एक्सेल 2010-2013 च्या आवृत्त्यांसाठी ते मायक्रोसॉफ्ट वेबसाइटवरून विनामूल्य डाउनलोड केले जाऊ शकते आणि एक्सेल 2016 आणि नवीन आवृत्तींमध्ये ते आधीच डीफॉल्टनुसार अंगभूत आहे. ).

कृतींचे तर्क खालीलप्रमाणे असतील:

1. आम्ही कोणत्याही एका वर्षासाठी साइटवरून डेटा डाउनलोड करण्याची विनंती करतो
2. आमची विनंती फंक्शनमध्ये बदलत आहे
3. आम्ही हे कार्य 2013 पासून सुरू होणार्‍या आणि चालू वर्षापर्यंत सर्व उपलब्ध वर्षांच्या सूचीवर लागू करतो - आणि आम्हाला स्वयंचलित अद्यतनासह "शाश्वत" उत्पादन दिनदर्शिका मिळते. व्होइला!
   

पायरी 1. एका वर्षासाठी कॅलेंडर आयात करा

प्रथम, उत्पादन कॅलेंडर कोणत्याही एका वर्षासाठी लोड करा, उदाहरणार्थ, 2020 साठी. हे करण्यासाठी, Excel मध्ये, टॅबवर जा. डेटा (किंवा उर्जा प्रश्नजर तुम्ही ते स्वतंत्र अॅड-ऑन म्हणून स्थापित केले असेल) आणि निवडा इंटरनेटवरून (वेब वरून). उघडणाऱ्या विंडोमध्ये, साइटवरून कॉपी केलेल्या संबंधित वर्षाची लिंक पेस्ट करा:

वर क्लिक केल्यानंतर OK एक पूर्वावलोकन विंडो दिसेल, ज्यामध्ये तुम्हाला बटण क्लिक करावे लागेल डेटा रूपांतरित करा (डेटा ट्रान्सफॉर्म) or डेटा बदलण्यासाठी (डेटा संपादित करा) आणि आम्ही पॉवर क्वेरी क्वेरी एडिटर विंडोवर पोहोचू, जिथे आम्ही डेटासह कार्य करणे सुरू ठेवू:

ताबडतोब आपण उजव्या पॅनेलमध्ये सुरक्षितपणे हटवू शकता मापदंडांची विनंती करा (क्वेरी सेटिंग्ज) पाऊल सुधारित प्रकार (बदललेला प्रकार) आम्हाला त्याची गरज नाही.

सुट्टीच्या कॉलममधील टेबलमध्ये काम नसलेल्या दिवसांचे कोड आणि वर्णन आहेत – तुम्ही हिरव्या शब्दावर दोनदा क्लिक करून "फॉलिंग थ्रू" करून त्यातील मजकूर पाहू शकता. टेबल:

परत जाण्यासाठी, तुम्हाला उजव्या पॅनेलमध्ये परत दिसलेल्या सर्व पायऱ्या हटवाव्या लागतील स्रोत (स्त्रोत).

दुस-या सारणीत, ज्यामध्ये अशाच प्रकारे प्रवेश केला जाऊ शकतो, त्यात आपल्याला जे आवश्यक आहे ते समाविष्ट आहे - सर्व नॉन-वर्किंग दिवसांच्या तारखा:

या प्लेटवर प्रक्रिया करणे बाकी आहे, म्हणजे:

1. दुसऱ्या स्तंभाद्वारे फक्त सुट्टीच्या तारखा (म्हणजेच) फिल्टर करा विशेषता:t

2. पहिला वगळता सर्व स्तंभ हटवा - पहिल्या स्तंभाच्या शीर्षावर उजवे-क्लिक करून आणि कमांड निवडून इतर स्तंभ हटवा (इतर स्तंभ काढा):

3. आदेशासह महिना आणि दिवसासाठी पहिला स्तंभ बिंदूद्वारे स्वतंत्रपणे विभाजित करा स्प्लिट कॉलम - डिलिमिटरद्वारे टॅब परिवर्तन (परिवर्तन — स्प्लिट स्तंभ — परिसीमकानुसार):

4. आणि शेवटी सामान्य तारखांसह एक गणना केलेला स्तंभ तयार करा. हे करण्यासाठी, टॅबवर एक स्तंभ जोडत आहे बटणावर क्लिक करा सानुकूल स्तंभ (स्तंभ जोडा — सानुकूल स्तंभ) आणि दिसत असलेल्या विंडोमध्ये खालील सूत्र प्रविष्ट करा:

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

येथे, #date ऑपरेटरकडे तीन वितर्क आहेत: अनुक्रमे वर्ष, महिना आणि दिवस. वर क्लिक केल्यानंतर OK आम्हाला सामान्य शनिवार व रविवारच्या तारखांसह आवश्यक स्तंभ मिळतो आणि चरण 2 प्रमाणे उर्वरित स्तंभ हटवतो

पायरी 2. विनंतीला फंक्शनमध्ये बदलणे

आमचे पुढील कार्य 2020 साठी तयार केलेली क्वेरी कोणत्याही वर्षासाठी सार्वत्रिक कार्यामध्ये रूपांतरित करणे आहे (वर्ष क्रमांक हा त्याचा युक्तिवाद असेल). हे करण्यासाठी, आम्ही पुढील गोष्टी करतो:

1. पॅनेलचा विस्तार करत आहे (आधीच विस्तारित केलेले नसल्यास). चौकश्या (प्रश्न) पॉवर क्वेरी विंडोमध्ये डावीकडे:

2. विनंतीला फंक्शनमध्ये रूपांतरित केल्यानंतर, विनंती बनवणारे चरण पाहण्याची आणि त्यांना सहजपणे संपादित करण्याची क्षमता, दुर्दैवाने, नाहीशी होते. म्हणून, आमच्या विनंतीची एक प्रत तयार करणे आणि आधीच त्यासोबत आनंद व्यक्त करणे आणि मूळ राखीव ठेवण्यास अर्थ आहे. हे करण्यासाठी, आमच्या कॅलेंडर विनंतीवर डाव्या उपखंडात उजवे-क्लिक करा आणि डुप्लिकेट कमांड निवडा.

कॅलेंडर (2) च्या परिणामी प्रतीवर पुन्हा उजवे-क्लिक केल्याने कमांड निवडली जाईल पुनर्नामित करा (नाव बदला) आणि एक नवीन नाव प्रविष्ट करा - ते असू द्या, उदाहरणार्थ, fxवर्ष:

3. कमांड वापरून आम्ही क्वेरी सोर्स कोड अंतर्गत पॉवर क्वेरी भाषेत उघडतो (याला थोडक्यात "M" म्हणतात) प्रगत संपादक टॅब पुनरावलोकन(पहा — प्रगत संपादक) आणि आमच्या विनंतीला कोणत्याही वर्षासाठी फंक्शनमध्ये बदलण्यासाठी तेथे छोटे बदल करा.

ते होते:

नंतर:

आपल्याला तपशीलांमध्ये स्वारस्य असल्यास, येथे:

• (संख्या म्हणून वर्ष) =>  - आम्ही घोषित करतो की आमच्या फंक्शनमध्ये एक संख्यात्मक युक्तिवाद असेल - एक व्हेरिएबल वर्ष
• व्हेरिएबल पेस्ट करत आहे वर्ष स्टेप मध्ये वेब लिंक वर स्रोत. पॉवर क्वेरी तुम्हाला संख्या आणि मजकूर चिकटवण्याची परवानगी देत ​​​​नाही, आम्ही फंक्शन वापरून फ्लाय ऑन टेक्स्टमध्ये वर्ष क्रमांक रूपांतरित करतो क्रमांक.ToText
• आम्ही अंतिम टप्प्यात २०२० साठी वर्ष व्हेरिएबल बदलतो #"सानुकूल ऑब्जेक्ट जोडले«, जिथे आम्ही तुकड्यांमधून तारीख तयार केली.
  

वर क्लिक केल्यानंतर समाप्त आमची विनंती फंक्शन बनते:

पायरी 3. सर्व वर्षांसाठी कॅलेंडर आयात करा

शेवटची मुख्य क्वेरी करणे बाकी आहे, जी सर्व उपलब्ध वर्षांसाठी डेटा अपलोड करेल आणि प्राप्त झालेल्या सुट्टीच्या तारखा एका टेबलमध्ये जोडेल. यासाठी:

1. आम्ही उजव्या माऊस बटणाने राखाडी रिकाम्या जागेत डाव्या क्वेरी पॅनेलवर क्लिक करतो आणि क्रमाने निवडा. नवीन विनंती - इतर स्त्रोत - रिक्त विनंती (नवीन क्वेरी — इतर स्त्रोतांकडून — रिक्त क्वेरी):

2. आम्हाला सर्व वर्षांची यादी तयार करायची आहे ज्यासाठी आम्ही कॅलेंडरची विनंती करू, म्हणजे 2013, 2014 … 2020. हे करण्यासाठी, रिक्त क्वेरीच्या फॉर्म्युला बारमध्ये, कमांड एंटर करा:

रचना:

={NumberA..NumberB}

… Power Query मध्ये A ते B पूर्णांकांची सूची तयार करते. उदाहरणार्थ, अभिव्यक्ती

={1..5}

… 1,2,3,4,5 ची यादी तयार करेल.

बरं, 2020 ला कठोरपणे बांधले जाऊ नये म्हणून, आम्ही फंक्शन वापरतो DateTime.LocalNow() - एक्सेल फंक्शनचे अॅनालॉग आज (आज) पॉवर क्वेरीमध्ये - आणि त्यातून फंक्शनद्वारे चालू वर्ष काढा तारीख.वर्ष.

3. परिणामी वर्षांचा संच, जरी तो पुरेसा दिसत असला तरी, पॉवर क्वेरीसाठी सारणी नसून एक विशेष वस्तू आहे – यादी (यादी). परंतु ते टेबलमध्ये रूपांतरित करणे ही समस्या नाही: फक्त बटणावर क्लिक करा टेबल करण्यासाठी (टेबलकडे) वरच्या डाव्या कोपर्यात:

4. अंतिम रेषा! आम्ही पूर्वी तयार केलेले फंक्शन लागू करणे fxवर्ष परिणामी वर्षांच्या यादीत. हे करण्यासाठी, टॅबवर एक स्तंभ जोडत आहे बटण दाबा सानुकूल कार्य कॉल करा (स्तंभ जोडा — सानुकूल कार्य सुरू करा) आणि त्याचा एकमेव युक्तिवाद सेट करा - स्तंभ Column1 वर्षांमध्ये:

वर क्लिक केल्यानंतर OK आमचे कार्य fxवर्ष आयात प्रत्येक वर्षाच्या बदल्यात कार्य करेल आणि आम्हाला एक स्तंभ मिळेल जिथे प्रत्येक सेलमध्ये नॉन-वर्किंग दिवसांच्या तारखांसह एक टेबल असेल (आपण पुढील सेलच्या पार्श्वभूमीत क्लिक केल्यास टेबलची सामग्री स्पष्टपणे दृश्यमान होईल. शब्द टेबल):

स्तंभ शीर्षलेखातील दुहेरी बाणांसह चिन्हावर क्लिक करून नेस्टेड टेबलची सामग्री विस्तृत करणे बाकी आहे तारखा (टिक मूळ स्तंभाचे नाव उपसर्ग म्हणून वापरा ते काढले जाऊ शकते):

… आणि क्लिक केल्यानंतर OK आम्हाला पाहिजे ते मिळते - 2013 पासून चालू वर्षाच्या सर्व सुट्ट्यांची यादी:

पहिला, आधीच अनावश्यक स्तंभ, हटविला जाऊ शकतो आणि दुसऱ्यासाठी, डेटा प्रकार सेट करा तारीख (तारीख) स्तंभ शीर्षकातील ड्रॉपडाउन सूचीमध्ये:

क्वेरीला स्वतःहून अधिक अर्थपूर्ण असे नाव दिले जाऊ शकते विनंती1 आणि नंतर कमांड वापरून डायनॅमिक "स्मार्ट" टेबलच्या स्वरूपात परिणाम शीटवर अपलोड करा बंद करा आणि डाउनलोड करा टॅब होम पेज (घर - बंद करा आणि लोड करा):

तुम्ही टेबलवर उजवे क्लिक करून किंवा कमांडद्वारे उजव्या उपखंडात क्वेरी करून भविष्यात तयार केलेले कॅलेंडर अपडेट करू शकता. अपडेट आणि सेव्ह करा. किंवा बटण वापरा सर्व रिफ्रेश करा टॅब डेटा (तारीख — सर्व रिफ्रेश करा) किंवा कीबोर्ड शॉर्टकट Ctrl+alt+F5.

त्या सर्व आहे.

आता तुम्हाला सुट्ट्यांची यादी शोधण्यात आणि अपडेट करण्यात वेळ आणि विचार-इंधन वाया घालवण्याची गरज नाही – आता तुमच्याकडे “शाश्वत” उत्पादन दिनदर्शिका आहे. कोणत्याही परिस्थितीत, जोपर्यंत http://xmlcalendar.ru/ साइटचे लेखक त्यांच्या संततीला समर्थन देतात, जे मला आशा आहे की, खूप, खूप दीर्घ काळासाठी (त्यांना पुन्हा धन्यवाद!).

• Power Query द्वारे इंटरनेटवरून एक्सेल करण्यासाठी बिटकॉइन दर आयात करा
• WORKDAY कार्य वापरून पुढील व्यवसाय दिवस शोधणे
  
• तारखेच्या अंतरालांचा छेदनबिंदू शोधत आहे