تقویم کارخانه در اکسل

تقویم تولید، به عنوان مثال لیستی از تاریخ ها، که در آن تمام روزهای کاری رسمی و تعطیلات بر اساس آن مشخص شده است - یک چیز کاملاً ضروری برای هر کاربر Microsoft Excel. در عمل، شما نمی توانید بدون آن انجام دهید:

• در محاسبات حسابداری (حقوق، مدت خدمت، مرخصی و …)
• در تدارکات - برای تعیین صحیح زمان تحویل، با در نظر گرفتن تعطیلات آخر هفته و تعطیلات (به یاد داشته باشید کلاسیک "بعد از تعطیلات می آیید؟")
  
• در مدیریت پروژه - برای برآورد صحیح شرایط، با در نظر گرفتن مجدد روزهای کاری-غیر کاری
• هر گونه استفاده از توابع مانند روز کاری (روز کاری) or کارگران خالص (NETWORKDAYS)، زیرا آنها به لیستی از تعطیلات به عنوان استدلال نیاز دارند
• هنگام استفاده از توابع Time Intelligence (مانند TOTALYTD، TOTALMTD، SAMEPERIODLASTYEAR، و غیره) در Power Pivot و Power BI
  
• ... و غیره و غیره - نمونه های زیادی.
  

برای کسانی که در سیستم های ERP شرکتی مانند 1C یا SAP کار می کنند آسان تر است، زیرا تقویم تولید در آنها تعبیه شده است. اما کاربران اکسل چطور؟

البته می توانید چنین تقویمی را به صورت دستی نگه دارید. اما پس از آن باید حداقل یک بار در سال آن را به روز کنید (یا حتی بیشتر اوقات، مانند "شاد" 2020)، با دقت وارد تمام تعطیلات آخر هفته، نقل و انتقالات و روزهای غیر کاری که توسط دولت ما اختراع شده است. و سپس این روش را هر سال بعد تکرار کنید. کسالت.

در مورد کمی دیوانه شدن و ساختن یک تقویم کارخانه ای "همیشگی" در اکسل چطور؟ یکی که خود را به روز می کند، داده ها را از اینترنت می گیرد و همیشه یک لیست به روز از روزهای غیر کاری برای استفاده بعدی در هر محاسباتی تولید می کند؟ وسوسه انگیز؟

انجام این کار، در واقع، به هیچ وجه دشوار نیست.

منبع اطلاعات

سوال اصلی این است که داده ها را از کجا باید دریافت کرد؟ در جستجوی منبع مناسب، چندین گزینه را طی کردم:

• احکام اولیه در وب سایت دولت در قالب PDF (در اینجا، یکی از آنها، برای مثال) منتشر می شود و بلافاصله ناپدید می شوند - اطلاعات مفیدی را نمی توان از آنها بیرون کشید.
• در نگاه اول یک گزینه وسوسه انگیز به نظر می رسید "پورتال باز اطلاعات فدراسیون" که در آن مجموعه داده های مربوطه وجود دارد، اما با بررسی دقیق تر، همه چیز غم انگیز بود. این سایت برای وارد کردن به اکسل بسیار ناخوشایند است، پشتیبانی فنی پاسخ نمی دهد (خود ایزوله شده؟)، و خود داده ها برای مدت طولانی در آنجا قدیمی هستند - تقویم تولید برای سال 2020 آخرین بار در نوامبر 2019 به روز شد (افتضاح!) و البته، برای مثال، «کرونا ویروس» و «رای گیری» آخر هفته 2020 ما را شامل نمی شود.

من که از منابع رسمی سرخورده شده بودم، شروع به کندن منابع غیر رسمی کردم. تعداد زیادی از آنها در اینترنت وجود دارد، اما بیشتر آنها باز هم برای وارد کردن به اکسل کاملاً نامناسب هستند و یک تقویم تولیدی را در قالب تصاویر زیبا ارائه می دهند. اما این برای ما نیست که آن را به دیوار آویزان کنیم، درست است؟

و در روند جستجو، یک چیز شگفت انگیز به طور تصادفی کشف شد - سایت http://xmlcalendar.ru/

بدون "زواید" غیر ضروری، یک سایت ساده، سبک و سریع، که برای یک کار تیز شده است - برای ارائه یک تقویم تولید برای سال مورد نظر در قالب XML. عالی!

اگر به طور ناگهانی نمی دانید، XML یک قالب متنی است که محتوای آن با علامت های خاص مشخص شده است. . سبک، راحت و قابل خواندن توسط اکثر برنامه های مدرن، از جمله Excel.

فقط در صورت امکان با نویسندگان سایت تماس گرفتم و آنها تأیید کردند که سایت 7 سال است که وجود دارد، اطلاعات آن دائماً به روز می شود (حتی برای این کار در github شعبه دارند) و قرار نیست آن را ببندند. و من اصلاً بدم نمی آید که من و شما داده ها را برای هر یک از پروژه ها و محاسبات خود در اکسل بارگذاری کنیم. رایگان است. خوب است بدانید که هنوز هم چنین افرادی وجود دارند! توجه!

باقی مانده است که این داده ها را با استفاده از افزونه Power Query در اکسل بارگیری کنید (برای نسخه های Excel 2010-2013 می توان آن را به صورت رایگان از وب سایت مایکروسافت بارگیری کرد و در نسخه های اکسل 2016 و جدیدتر از قبل به طور پیش فرض داخلی است. ).

منطق اعمال به صورت زیر خواهد بود:

1. ما درخواست بارگیری داده ها از سایت را برای هر سال می دهیم
2. تبدیل درخواست ما به یک تابع
3. ما این تابع را در لیست همه سال‌های موجود، از سال 2013 تا سال جاری، اعمال می‌کنیم - و یک تقویم تولید «همیشگی» با به‌روزرسانی خودکار دریافت می‌کنیم. وویلا!
   

مرحله 1. یک تقویم برای یک سال وارد کنید

ابتدا تقویم تولید را برای هر یک سال بارگذاری کنید، به عنوان مثال، برای سال 2020. برای انجام این کار، در اکسل، به تب بروید. داده ها (و یا پرس و جو برقاگر آن را به عنوان یک افزونه جداگانه نصب کرده اید) و انتخاب کنید از اینترنت (از وب). در پنجره ای که باز می شود، پیوند سال مربوطه را که از سایت کپی شده است، قرار دهید:

پس از کلیک روی OK یک پنجره پیش نمایش ظاهر می شود که در آن باید روی دکمه کلیک کنید تبدیل داده ها (تغییر داده ها) or برای تغییر داده ها (ویرایش داده ها) و به پنجره ویرایشگر پرس و جو Power Query می رسیم، جایی که کار با داده ها را ادامه می دهیم:

بلافاصله می توانید با خیال راحت در پنل سمت راست حذف کنید درخواست پارامترها (تنظیمات پرس و جو) گام نوع اصلاح شده (تغییر نوع) ما به او نیاز نداریم

جدول در ستون تعطیلات حاوی کدها و توضیحات روزهای غیر کاری است - با کلیک کردن روی کلمه سبز رنگ، می توانید با دو بار "افتادن" آن را مشاهده کنید. جدول:

برای بازگشت به عقب، باید تمام مراحلی را که در پنل سمت راست ظاهر شده اند حذف کنید منبع (منبع).

جدول دوم که به روشی مشابه قابل دسترسی است، دقیقاً حاوی آنچه ما نیاز داریم - تاریخ همه روزهای غیر کاری:

برای پردازش این صفحه باقی مانده است، یعنی:

1. فقط تاریخ های تعطیلات (یعنی آنهایی که) را با ستون دوم فیلتر کنید ویژگی: t

2. با کلیک راست بر روی عنوان ستون اول و انتخاب دستور، تمام ستون ها به جز ستون اول را حذف کنید. ستون های دیگر را حذف کنید (حذف ستون های دیگر):

3. ستون اول را به صورت جداگانه برای ماه و روز با دستور تقسیم کنید ستون تقسیم - توسط جداکننده برگ دگرگونی (تبدیل - تقسیم ستون - با جداکننده):

4. و در نهایت یک ستون محاسبه شده با تاریخ های معمولی ایجاد کنید. برای انجام این کار، در برگه اضافه کردن یک ستون روی دکمه کلیک کنید ستون سفارشی (افزودن ستون - ستون سفارشی) و در پنجره ظاهر شده فرمول زیر را وارد کنید:

=#تاریخ(2020، [#»ویژگی:d.1″]، [#»ویژگی:d.2″])

در اینجا عملگر #date دارای سه آرگومان سال، ماه و روز است. پس از کلیک بر روی OK ستون مورد نیاز را با تاریخ های عادی آخر هفته دریافت می کنیم و ستون های باقی مانده را مانند مرحله 2 حذف می کنیم

مرحله 2. تبدیل درخواست به یک تابع

وظیفه بعدی ما این است که کوئری ایجاد شده برای سال 2020 را به یک تابع جهانی برای هر سال تبدیل کنیم (عدد سال آرگومان آن خواهد بود). برای این کار موارد زیر را انجام می دهیم:

1. گسترش پانل (اگر قبلاً گسترش نیافته است). سوالات (پرس و جو) در سمت چپ پنجره Power Query:

2. پس از تبدیل درخواست به تابع، متأسفانه امکان مشاهده مراحل تشکیل دهنده درخواست و ویرایش آسان آنها از بین می رود. بنابراین منطقی است که یک کپی از درخواست خود تهیه کنیم و از قبل با آن لذت ببریم و اصل را در رزرو بگذاریم. برای انجام این کار، در قسمت سمت چپ درخواست تقویم خود کلیک راست کرده و دستور Duplicate را انتخاب کنید.

با کلیک راست مجدد بر روی کپی حاصل از تقویم (2) دستور انتخاب می شود تغییر نام (تغییر نام) و یک نام جدید وارد کنید - اجازه دهید، برای مثال، fxYear:

3. ما کد منبع پرس و جو را در زبان داخلی Power Query (که به طور خلاصه "M" نامیده می شود) با استفاده از دستور باز می کنیم. ویرایشگر پیشرفته برگ مرور(مشاهده - ویرایشگر پیشرفته) و تغییرات کوچکی در آنجا ایجاد کنیم تا درخواست ما به یک تابع برای هر سال تبدیل شود.

بود:

بعد از:

اگر به جزئیات علاقه دارید، در اینجا:

• (سال به عنوان عدد)=>  - ما اعلام می کنیم که تابع ما یک آرگومان عددی خواهد داشت - یک متغیر سال
• چسباندن متغیر سال به لینک وب در مرحله منبع. از آنجایی که Power Query به شما اجازه نمی دهد اعداد و متن را بچسبانید، ما با استفاده از تابع، شماره سال را به متن تبدیل می کنیم. Number.ToText
• متغیر سال را برای سال 2020 در مرحله ماقبل آخر جایگزین می کنیم #"اشیاء سفارشی اضافه شد«، جایی که تاریخ را از قطعات تشکیل دادیم.
  

پس از کلیک روی پایان درخواست ما به یک تابع تبدیل می شود:

مرحله 3. تقویم ها را برای تمام سال ها وارد کنید

آخرین چیزی که باقی می‌ماند این است که آخرین درخواست اصلی را انجام دهید، که داده‌های تمام سال‌های موجود را آپلود می‌کند و تمام تاریخ‌های تعطیلات دریافتی را در یک جدول اضافه می‌کند. برای این:

1. در پنل پرس و جو سمت چپ در یک فضای خالی خاکستری با دکمه سمت راست ماوس کلیک می کنیم و به ترتیب انتخاب می کنیم درخواست جدید – منابع دیگر – درخواست خالی (پرسش جدید - از منابع دیگر - پرس و جو خالی):

2. ما باید فهرستی از تمام سال‌هایی که برای آن‌ها تقویم درخواست می‌کنیم ایجاد کنیم، یعنی 2013، 2014 ... 2020. برای انجام این کار، در نوار فرمول کوئری خالی که ظاهر می‌شود، دستور را وارد کنید:

ساختار:

={NumberA..NumberB}

... در Power Query لیستی از اعداد صحیح از A تا B ایجاد می کند. برای مثال، عبارت

={1..5}

... لیستی از 1,2,3,4,5،XNUMX،XNUMX،XNUMX،XNUMX را تولید می کند.

خوب، برای اینکه به شدت به سال 2020 گره نخوریم، از تابع استفاده می کنیم DateTime.LocalNow() - آنالوگ تابع اکسل امروز (امروز) در Power Query - و به نوبه خود سال جاری را توسط تابع از آن استخراج کنید تاریخ. سال.

3. مجموعه سال‌های حاصل، اگرچه کاملاً کافی به نظر می‌رسد، جدولی برای Power Query نیست، بلکه یک شی خاص است. فهرست (فهرست). اما تبدیل آن به جدول مشکلی نیست: فقط روی دکمه کلیک کنید به میز (به میز) در گوشه سمت چپ بالا:

4. خط پایان! اعمال تابعی که قبلا ایجاد کردیم fxYear به لیست سال های حاصل برای انجام این کار، در برگه اضافه کردن یک ستون دکمه را فشار دهید فراخوانی تابع سفارشی (افزودن ستون - فراخوانی تابع سفارشی) و تنها آرگومان آن - ستون را تنظیم کنید Column1 در طول سال ها:

پس از کلیک روی OK عملکرد ما fxYear واردات به نوبه خود برای هر سال کار می کند و ما ستونی دریافت می کنیم که در آن هر سلول حاوی جدولی با تاریخ روزهای غیر کاری خواهد بود (اگر در پس زمینه سلول کناری کلیک کنید، محتویات جدول به وضوح قابل مشاهده است. کلمه جدول):

باقی مانده است که محتویات جداول تودرتو را با کلیک بر روی نماد با فلش های دوتایی در هدر ستون گسترش دهید. تاریخ (تیک از نام ستون اصلی به عنوان پیشوند استفاده کنید قابل حذف است):

… و پس از کلیک بر روی OK ما به آنچه می خواستیم می رسیم - لیستی از تمام تعطیلات از سال 2013 تا سال جاری:

ستون اول، که قبلا غیر ضروری است، می تواند حذف شود، و برای دوم، نوع داده را تنظیم کنید تاریخ (تاریخ) در لیست کشویی در عنوان ستون:

خود پرس و جو را می توان به چیزی معنی دارتر تغییر نام داد درخواست 1 و سپس نتایج را در قالب یک جدول "هوشمند" پویا با استفاده از دستور در برگه بارگذاری کنید ببندید و دانلود کنید برگ صفحه اصلی (صفحه اصلی - بستن و بارگیری):

می توانید با کلیک راست روی جدول یا پرس و جو در پنجره سمت راست از طریق دستور، تقویم ایجاد شده را در آینده به روز کنید. به روز رسانی و ذخیره. یا از دکمه استفاده کنید همه را بازخوانی کنید برگ داده ها (تاریخ - بازخوانی همه) یا میانبر صفحه کلید کلیدهای Ctrl+دگرساز+F5.

همه چیز هست

اکنون دیگر نیازی به تلف کردن زمان و انرژی فکری برای جستجو و به روز رسانی لیست تعطیلات ندارید - اکنون یک تقویم تولید "همیشگی" دارید. در هر صورت، تا زمانی که نویسندگان سایت http://xmlcalendar.ru/ از فرزندان خود حمایت کنند، که امیدوارم برای مدت بسیار بسیار طولانی (باز هم از آنها تشکر کنم!).

• نرخ بیت کوین را برای اکسل از اینترنت از طریق Power Query وارد کنید
• یافتن روز کاری بعدی با استفاده از تابع WORKDAY
  
• یافتن محل تلاقی فواصل تاریخ