تقويم المصنع في Excel

تقويم الإنتاج ، أي قائمة التواريخ ، حيث يتم وضع علامة على جميع أيام العمل الرسمية والعطلات وفقًا لذلك - وهو أمر ضروري للغاية لأي مستخدم لبرنامج Microsoft Excel. في الممارسة العملية ، لا يمكنك الاستغناء عنها:

• في حسابات المحاسبة (الراتب ، مدة الخدمة ، الإجازات ...)
• في اللوجستيات - لتحديد أوقات التسليم بشكل صحيح ، مع مراعاة عطلات نهاية الأسبوع والعطلات (تذكر العبارة الكلاسيكية "تعال بعد الإجازات؟")
  
• في إدارة المشروع - من أجل التقدير الصحيح للمصطلحات ، مع الأخذ في الاعتبار ، مرة أخرى ، أيام العمل والعطل
• أي استخدام لوظائف مثل يوم عمل (يوم عمل) or عمال نقي (أيام الشبكات)، لأنها تتطلب قائمة الأعياد كوسيلة
• عند استخدام وظائف "معلومات الوقت" (مثل TOTALYTD و TOTALMTD و SAMEPERIODLASTYEAR وما إلى ذلك) في Power Pivot و Power BI
  
• ... إلخ إلخ - الكثير من الأمثلة.
  

إنه أسهل بالنسبة لأولئك الذين يعملون في أنظمة ERP للشركات مثل 1C أو SAP ، حيث تم تضمين تقويم الإنتاج فيها. ولكن ماذا عن مستخدمي Excel؟

يمكنك بالطبع الاحتفاظ بهذا التقويم يدويًا. ولكن بعد ذلك سيتعين عليك تحديثه مرة واحدة على الأقل في العام (أو حتى في كثير من الأحيان ، كما هو الحال في "جولي" 2020) ، وإدخال جميع عطلات نهاية الأسبوع والتحويلات وأيام العطلات التي اخترعتها حكومتنا بعناية. ثم كرر هذا الإجراء كل عام قادم. ملل.

ماذا عن الجنون قليلاً وعمل تقويم المصنع "الدائم" في Excel؟ واحد يقوم بتحديث نفسه ، يأخذ البيانات من الإنترنت وينشئ دائمًا قائمة محدثة بأيام العطلات لاستخدامها لاحقًا في أي حسابات؟ مغري؟

إن القيام بذلك ، في الواقع ، ليس بالأمر الصعب على الإطلاق.

مصدر البيانات

السؤال الرئيسي هو من أين تحصل على البيانات؟ بحثًا عن مصدر مناسب ، مررت بعدة خيارات:

• تُنشر المراسيم الأصلية على الموقع الإلكتروني للحكومة بصيغة PDF (هنا ، أحدها ، على سبيل المثال) وتختفي على الفور - لا يمكن سحب المعلومات المفيدة منها.
• يبدو أن الخيار المغري للوهلة الأولى هو "بوابة البيانات المفتوحة للاتحاد"، حيث توجد مجموعة البيانات المقابلة، ولكن عند الفحص الدقيق، تبين أن كل شيء حزين. الموقع غير مناسب للغاية للاستيراد إلى Excel، والدعم الفني لا يستجيب (معزول ذاتيًا؟)، والبيانات نفسها قديمة هناك لفترة طويلة - تم تحديث تقويم الإنتاج لعام 2020 آخر مرة في نوفمبر 2019 (عار!) بالطبع، لا يحتوي على "فيروس كورونا" وعطلة نهاية الأسبوع "التصويت" لعام 2020، على سبيل المثال.

بخيبة أمل من المصادر الرسمية ، بدأت في البحث عن مصادر غير رسمية. يوجد الكثير منها على الإنترنت ، ولكن معظمها ، مرة أخرى ، غير مناسب تمامًا للاستيراد إلى Excel وإعطاء تقويم الإنتاج في شكل صور جميلة. لكن ليس علينا أن نعلقها على الحائط ، أليس كذلك؟

وأثناء عملية البحث ، تم اكتشاف شيء رائع عن طريق الخطأ - الموقع http://xmlcalendar.ru/

بدون "زخرفة" غير ضرورية ، موقع بسيط وخفيف وسريع ، تم شحذه لمهمة واحدة - لمنح الجميع تقويم إنتاج للسنة المطلوبة بتنسيق XML. ممتاز!

إذا لم تكن على دراية فجأة ، فإن XML هو تنسيق نصي يحتوي على محتوى مرمز بخاص . خفيف الوزن ومريح ويمكن قراءته بواسطة معظم البرامج الحديثة ، بما في ذلك Excel.

فقط في حالة ما إذا اتصلت بمؤلفي الموقع وأكدوا أن الموقع موجود منذ 7 سنوات ، ويتم تحديث البيانات الموجودة عليه باستمرار (حتى أن لديهم فرعًا على جيثب لهذا الغرض) ولن يقوموا بإغلاقه. ولا أمانع على الإطلاق في أن نقوم أنت وأنا بتحميل البيانات منه لأي من مشاريعنا وحساباتنا في Excel. بدون مقابل. من الجيد أن تعرف أنه لا يزال هناك أشخاص مثل هؤلاء! احترام!

يبقى تحميل هذه البيانات إلى Excel باستخدام الوظيفة الإضافية Power Query (بالنسبة لإصدارات Excel 2010-2013 ، يمكن تنزيلها مجانًا من موقع Microsoft على الويب ، وفي إصدارات Excel 2016 والإصدارات الأحدث ، تكون مدمجة بالفعل افتراضيًا ).

سيكون منطق الإجراءات على النحو التالي:

1. نتقدم بطلب لتنزيل البيانات من الموقع لمدة سنة واحدة
2. تحويل طلبنا إلى وظيفة
3. نطبق هذه الوظيفة على قائمة جميع السنوات المتاحة ، بدءًا من 2013 وحتى العام الحالي - ونحصل على تقويم إنتاج "دائم" مع تحديث تلقائي. هاهو!
   

الخطوة 1. استيراد تقويم لمدة عام واحد

أولاً ، قم بتحميل تقويم الإنتاج لأي عام ، على سبيل المثال ، لعام 2020. للقيام بذلك ، في Excel ، انتقل إلى علامة التبويب البيانات (أو استفسار الطاقةإذا قمت بتثبيته كإضافة منفصلة) وحدد من الإنترنت (من الويب). في النافذة التي تفتح ، الصق رابط السنة المقابلة ، المنسوخة من الموقع:

بعد النقر على OK تظهر نافذة معاينة تحتاج فيها إلى النقر فوق الزر تحويل البيانات (تحويل البيانات) or لتغيير البيانات (تحرير البيانات) وسنصل إلى نافذة محرر استعلام Power Query ، حيث سنواصل العمل مع البيانات:

على الفور يمكنك الحذف بأمان في اللوحة اليمنى معلمات الطلب (إعدادات الاستعلام) . نوع معدل (نوع تم تغييره) لسنا بحاجة إليه.

يحتوي الجدول الموجود في عمود الإجازات على أكواد وأوصاف أيام العطلات - يمكنك الاطلاع على محتوياته من خلال "السقوط" مرتين بالنقر فوق الكلمة الخضراء طاولات ومكاتب :

للرجوع ، سيتعين عليك حذف جميع الخطوات التي ظهرت مرة أخرى في اللوحة اليمنى مصدر (مصدر).

الجدول الثاني ، الذي يمكن الوصول إليه بطريقة مماثلة ، يحتوي بالضبط على ما نحتاجه - تواريخ جميع أيام العطلات:

يبقى معالجة هذه اللوحة ، وهي:

1. قم بتصفية تواريخ العطلات فقط (أي التواريخ) حسب العمود الثاني السمة: t

2. احذف جميع الأعمدة باستثناء الأول - عن طريق النقر بزر الماوس الأيمن على عنوان العمود الأول واختيار الأمر احذف الأعمدة الأخرى (إزالة أعمدة أخرى):

3. قسّم العمود الأول بنقطة بشكل منفصل للشهر واليوم باستخدام الأمر عمود الانقسام - بواسطة محدد علامة التبويب تحول (تحويل - عمود انقسام - بواسطة محدد):

4. وأخيرًا ، قم بإنشاء عمود محسوب بالتواريخ العادية. للقيام بذلك ، في علامة التبويب إضافة عمود انقر على الزر عمود مخصص (إضافة عمود - عمود مخصص) وأدخل الصيغة التالية في النافذة التي تظهر:

=#بتاريخ(2020 ، [# »السمة: d.1 ″] ، [#» السمة: d.2 ″])

هنا ، يحتوي عامل التشغيل #date على ثلاث وسيطات: السنة والشهر واليوم على التوالي. بعد الضغط على OK نحصل على العمود المطلوب بتواريخ نهاية الأسبوع العادية ، ونحذف الأعمدة المتبقية كما في الخطوة 2

الخطوة 2. تحويل الطلب إلى وظيفة

مهمتنا التالية هي تحويل الاستعلام الذي تم إنشاؤه لعام 2020 إلى دالة عالمية لأي عام (سيكون رقم السنة هو الوسيطة الخاصة بها). للقيام بذلك ، نقوم بما يلي:

1. توسيع اللوحة (إذا لم تكن موسعة بالفعل) استفسارات (استفسارات) على اليسار في نافذة Power Query:

2. بعد تحويل الطلب إلى دالة تختفي للأسف القدرة على رؤية الخطوات التي يتكون منها الطلب وتحريرها بسهولة. لذلك ، من المنطقي عمل نسخة من طلبنا ومرح بالفعل معها ، وترك النسخة الأصلية في الاحتياطي. للقيام بذلك ، انقر بزر الماوس الأيمن في الجزء الأيمن على طلب التقويم الخاص بنا وحدد أمر تكرار.

سيؤدي النقر بزر الماوس الأيمن مرة أخرى على نسخة التقويم الناتجة (2) إلى تحديد الأمر إعادة تسمية (إعادة تسمية) وأدخل اسمًا جديدًا - فليكن ، على سبيل المثال ، com.fxYear:

3. نفتح كود مصدر الاستعلام بلغة Power Query الداخلية (تسمى بإيجاز "M") باستخدام الأمر محرر متقدم علامة التبويب التقيم(عرض - محرر متقدم) وإجراء تغييرات صغيرة هناك لتحويل طلبنا إلى وظيفة لأي عام.

كانت:

بعد:

إذا كنت مهتمًا بالتفاصيل ، فإذن هنا:

• (السنة كرقم) =>  - نعلن أن دالة لدينا لها متغير رقمي واحد - متغير عام
• لصق المتغير عام لرابط ويب في الخطوة مصدر. نظرًا لأن Power Query لا يسمح لك بلصق الأرقام والنصوص ، فإننا نحول رقم السنة إلى نص سريعًا باستخدام الوظيفة رقم. إلى نص
• نستبدل متغير السنة لعام 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 () - التناظرية من وظيفة Excel TODAY (اليوم) في Power Query - واستخرج منه ، بدوره ، السنة الحالية بواسطة الوظيفة التاريخ والعام.

3. مجموعة السنوات الناتجة ، على الرغم من أنها تبدو مناسبة تمامًا ، ليست جدولًا لـ Power Query ، ولكنها كائن خاص - قائمة (قائمة). لكن تحويله إلى جدول لا يمثل مشكلة: فقط انقر فوق الزر إلى الجدول (إلى الجدول) في الزاوية اليسرى العليا:

4. خط النهاية! تطبيق الوظيفة التي أنشأناها سابقًا com.fxYear إلى قائمة السنوات الناتجة. للقيام بذلك ، في علامة التبويب إضافة عمود اضغط الزر استدعاء وظيفة مخصصة (إضافة عمود - استدعاء وظيفة مخصصة) وتعيين الحجة الوحيدة - العمود Column1 على مر السنين:

بعد النقر على OK وظيفتنا com.fxYear سيعمل الاستيراد بالتناوب لكل عام وسنحصل على عمود حيث ستحتوي كل خلية على جدول يحتوي على تواريخ أيام العطلات (تظهر محتويات الجدول بوضوح إذا نقرت في خلفية الخلية المجاورة لـ الكلمة طاولات ومكاتب ):

يبقى توسيع محتويات الجداول المتداخلة عن طريق النقر فوق الرمز ذي الأسهم المزدوجة في رأس العمود تمور (علامة استخدم اسم العمود الأصلي كبادئة يمكن إزالته):

... وبعد الضغط على OK نحصل على ما أردناه - قائمة بجميع العطلات من 2013 إلى العام الحالي:

يمكن حذف العمود الأول غير الضروري بالفعل ، وللحالة الثانية ، قم بتعيين نوع البيانات تاريخ (تاريخ) في القائمة المنسدلة في عنوان العمود:

يمكن إعادة تسمية الاستعلام نفسه بشيء أكثر دلالة من طلب 1 ثم قم بتحميل النتائج على الورقة في شكل جدول ديناميكي "ذكي" باستخدام الأمر إغلاق وتحميل علامة التبويب الصفحة الرئيسية (الصفحة الرئيسية - إغلاق وتحميل):

يمكنك تحديث التقويم الذي تم إنشاؤه في المستقبل عن طريق النقر بزر الماوس الأيمن على الجدول أو الاستعلام في الجزء الأيمن من خلال الأمر تحديث وحفظ. أو استخدم الزر تحديث كافة علامة التبويب البيانات (التاريخ - تحديث الكل) أو اختصار لوحة المفاتيح CTRL+قديم+F5.

هذا كل شئ.

الآن لن تحتاج أبدًا مرة أخرى إلى إضاعة الوقت والتفكير في البحث عن قائمة العطلات وتحديثها - الآن لديك تقويم إنتاج "دائم". على أي حال ، طالما أن مؤلفي الموقع http://xmlcalendar.ru/ يدعمون ذريتهم ، والتي آمل أن تكون لفترة طويلة جدًا جدًا (شكرًا لهم مرة أخرى!).

• معدل استيراد البيتكوين للتميز من الإنترنت عبر Power Query
• العثور على يوم العمل التالي باستخدام وظيفة WORKDAY
  
• البحث عن تقاطع فترات التاريخ