לוח שנה של המפעל באקסל

לוח הפקה, כלומר רשימת תאריכים, שבו כל ימי העבודה והחגים הרשמיים מסומנים בהתאם – דבר הכרחי לחלוטין לכל משתמש ב-Microsoft Excel. בפועל, אתה לא יכול בלעדיו:

• בחישובים חשבונאיים (שכר, משך שירות, חופשות...)
• בלוגיסטיקה - לקביעה נכונה של זמני אספקה, תוך התחשבות בסופי שבוע וחגים (זוכרים את הקלאסי "יאללה אחרי החגים?")
  
• בניהול פרויקטים – להערכת תנאים נכונה, תוך התחשבות, שוב, בימי עבודה ללא עבודה
• כל שימוש בפונקציות כמו יום עבודה (יום עבודה) or עובדים טהורים (NETWORKDAYS), כי הם דורשים רשימה של חגים כטיעון
• בעת שימוש בפונקציות Time Intelligence (כמו TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR וכו') ב-Power Pivot וב-Power BI
  
• ... וכו' וכו' - המון דוגמאות.
  

קל יותר למי שעובד במערכות ERP ארגוניות כמו 1C או SAP, שכן לוח השנה הייצור מובנה בהן. אבל מה לגבי משתמשי אקסל?

אפשר כמובן לשמור יומן כזה באופן ידני. אבל אז תצטרך לעדכן אותו לפחות פעם בשנה (או אפילו לעתים קרובות יותר, כמו ב-2020 ה"עליז"), להזין בזהירות את כל סופי השבוע, העברות וימי אי-עבודה שהומצאו על ידי הממשלה שלנו. ואז לחזור על הליך זה כל שנה הבאה. שעמום.

מה דעתך להשתגע קצת וליצור יומן מפעל "נצחי" באקסל? כזה שמעדכן את עצמו, לוקח נתונים מהאינטרנט ומייצר תמיד רשימה עדכנית של ימי עבודה לשימוש אחר כך בכל חישוב? מפתה?

לעשות זאת, למעשה, זה בכלל לא קשה.

מקור נתונים

השאלה העיקרית היא היכן משיגים את הנתונים? בחיפוש אחר מקור מתאים, עברתי על מספר אפשרויות:

• הגזירות המקוריות מתפרסמות באתר הממשלה בפורמט PDF (כאן, אחת מהן, למשל) ונעלמות מיד – לא ניתן לשלוף מהן מידע שימושי.
• 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. מְעוּלֶה!

אם, פתאום, אתה לא יודע, אז XML הוא פורמט טקסט עם תוכן מסומן עם מיוחד . קל משקל, נוח וקריא על ידי רוב התוכניות המודרניות, כולל Excel.

ליתר ביטחון פניתי לכותבי האתר והם אישרו שהאתר קיים 7 שנים, הנתונים עליו מתעדכנים כל הזמן (יש להם אפילו סניף ב-github בשביל זה) והם לא מתכוונים לסגור אותו. ולא אכפת לי בכלל שאתה ואני טוענים ממנו נתונים עבור כל אחד מהפרויקטים והחישובים שלנו באקסל. בחינם. כיף לדעת שעדיין יש אנשים כאלה! הערכה!

נותר לטעון נתונים אלה לאקסל באמצעות תוסף Power Query (עבור גרסאות של Excel 2010-2013 ניתן להוריד אותו בחינם מאתר האינטרנט של מיקרוסופט, ובגירסאות של Excel 2016 ואילך הוא כבר מובנה כברירת מחדל ).

ההיגיון של הפעולות יהיה כדלקמן:

1. אנו מבקשים להוריד נתונים מהאתר לכל שנה
2. הפיכת הבקשה שלנו לפונקציה
3. אנו מיישמים את הפונקציה הזו על רשימת כל השנים הזמינות, החל משנת 2013 ועד השנה הנוכחית - ומקבלים לוח ייצור "תמידי" עם עדכון אוטומטי. וואלה!
   

שלב 1. ייבא לוח שנה לשנה

ראשית, טען את לוח הייצור עבור כל שנה, למשל, עבור 2020. לשם כך, ב-Excel, עבור ללשונית נתונים (אוֹ שאילתת כוחאם התקנת אותו כתוסף נפרד) ובחר מהאינטרנט (מהאינטרנט). בחלון שנפתח הדביקו את הקישור לשנה המקבילה, שהועתק מהאתר:

לאחר לחיצה על OK מופיע חלון תצוגה מקדימה, שבו עליך ללחוץ על הכפתור המרת נתונים (שינוי נתונים) or כדי לשנות את הנתונים (ערוך נתונים) ונגיע לחלון עורך השאילתות של Power Query, שם נמשיך לעבוד עם הנתונים:

מיד אתה יכול למחוק בבטחה בחלונית הימנית פרמטרים בקשה (הגדרות שאילתה) צעד סוג שונה (סוג שונה) אנחנו לא צריכים אותו.

הטבלה בעמודת החגים מכילה קודים ותיאורים של ימי עבודה לא - ניתן לראות את תוכנו על ידי "נפילה" פעמיים על ידי לחיצה על המילה הירוקה טבלתי:

כדי לחזור אחורה, תצטרך למחוק בחלונית הימנית את כל השלבים שהופיעו בחזרה מָקוֹר (מָקוֹר).

הטבלה השנייה, שאליה ניתן לגשת בצורה דומה, מכילה בדיוק את מה שאנחנו צריכים - התאריכים של כל ימי העבודה שאינם:

נותר לעבד את הצלחת הזו, כלומר:

1. סנן רק תאריכי חגים (כלומר אלה) לפי העמודה השנייה תכונה: t

2. מחק את כל העמודות מלבד הראשונה - על ידי לחיצה ימנית על הכותרת של העמודה הראשונה ובחירה בפקודה מחק עמודות אחרות (הסר עמודות אחרות):

3. פצל עמודה ראשונה אחר נקודה בנפרד עבור חודש ויום עם פקודה פיצול עמודה - לפי מפריד כרטיסייה טרנספורמציה (טרנספורמציה - פיצול עמודה - לפי מפריד):

4. ולבסוף ליצור עמודה מחושבת עם תאריכים רגילים. כדי לעשות זאת, בכרטיסייה הוספת עמודה לחץ על הכפתור עמודה מותאמת אישית (הוסף עמודה - עמודה מותאמת אישית) והזן את הנוסחה הבאה בחלון שמופיע:

=#מְיוּשָׁן(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])

כאן, לאופרטור #date יש שלושה ארגומנטים: שנה, חודש ויום, בהתאמה. לאחר לחיצה על OK אנו מקבלים את העמודה הנדרשת עם תאריכי סוף שבוע רגילים, ונמחק את העמודות הנותרות כמו בשלב 2

שלב 2. הפיכת הבקשה לפונקציה

המשימה הבאה שלנו היא להמיר את השאילתה שנוצרה עבור 2020 לפונקציה אוניברסלית עבור כל שנה (מספר השנה יהיה הארגומנט שלה). לשם כך, אנו עושים את הפעולות הבאות:

1. הרחבת (אם עדיין לא הורחב) את הפאנל פניות (שאילתות) בצד שמאל בחלון Power Query:

2. לאחר המרת הבקשה לפונקציה, היכולת לראות את השלבים המרכיבים את הבקשה ולערוך אותם בקלות, לצערנו, נעלמת. לכן, הגיוני להכין עותק של הבקשה שלנו ולהשתולל כבר איתה, ולהשאיר את המקור במילואים. כדי לעשות זאת, לחץ לחיצה ימנית בחלונית השמאלית על בקשת לוח השנה שלנו ובחר בפקודה שכפל.

לחיצה ימנית שוב על העותק שנוצר של לוח שנה(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.

ובכן, כדי לא להיות קשור בצורה נוקשה לשנת 2020, אנו משתמשים בפונקציה DateTime.LocalNow() - אנלוגי לפונקציית Excel היום (היום) ב-Power Query - ולחלץ ממנה, בתורו, את השנה הנוכחית לפי הפונקציה תאריך.שנה.

3. קבוצת השנים שהתקבלה, למרות שהיא נראית די מספקת, היא לא טבלה עבור Power Query, אלא אובייקט מיוחד - רשימה (רשימה). אבל להמיר אותו לטבלה זו לא בעיה: פשוט לחץ על הכפתור לשולחן (לשולחן) בפינה השמאלית העליונה:

4. קו סיום! יישום הפונקציה שיצרנו קודם לכן fxyear לרשימת השנים שהתקבלה. כדי לעשות זאת, בכרטיסייה הוספת עמודה לחץ על הכפתור התקשר לפונקציה מותאמת אישית (הוסף עמודה - הפעל פונקציה מותאמת אישית) וקבעו את הטיעון היחיד שלו - העמודה Column1 במהלך השנים:

לאחר לחיצה על OK הפונקציה שלנו fxyear הייבוא ​​יעבוד בתורו עבור כל שנה ונקבל עמודה שבה כל תא יכיל טבלה עם תאריכי ימי עבודה שאינם פועלים (תוכן הטבלה נראה בבירור אם תלחץ ברקע התא ליד המילה טבלתי):

נותר להרחיב את התוכן של טבלאות מקוננות על ידי לחיצה על הסמל עם חיצים כפולים בכותרת העמודה תאריכים (תִקתוּק השתמש בשם העמודה המקורי בתור קידומת ניתן להסירו):

... ולאחר לחיצה על OK אנחנו מקבלים את מה שרצינו - רשימה של כל החגים משנת 2013 ועד השנה הנוכחית:

ניתן למחוק את העמודה הראשונה, שכבר מיותרת, ובשנייה להגדיר את סוג הנתונים נתונים (תַאֲרִיך) ברשימה הנפתחת בכותרת העמודה:

ניתן לשנות את שם השאילתה עצמה למשהו משמעותי יותר מאשר בקשה 1 ולאחר מכן העלה את התוצאות לגיליון בצורה של טבלה "חכמה" דינמית באמצעות הפקודה לסגור ולהוריד כרטיסייה עמוד הבית (בית - סגור וטען):

אתה יכול לעדכן את לוח השנה שנוצר בעתיד על ידי לחיצה ימנית על הטבלה או שאילתה בחלונית הימנית דרך הפקודה עדכן ושמור. או השתמש בכפתור רענן הכל כרטיסייה נתונים (תאריך - רענן הכל) או קיצור מקלדת Ctrl+אחר+F5.

זה הכל.

עכשיו אתה לא צריך יותר לבזבז זמן ודלק בחיפוש אחר ועדכון רשימת החגים - עכשיו יש לך לוח ייצור "נצחי". בכל מקרה, כל עוד מחברי האתר http://xmlcalendar.ru/ תומכים בצאצאיהם, מה שאני מקווה שיהיה להרבה מאוד זמן (שוב תודה להם!).

• יבא שער ביטקוין ל-Exel מהאינטרנט באמצעות Power Query
• מציאת יום העסקים הבא באמצעות הפונקציה WORKDAY
  
• מציאת הצומת של מרווחי תאריכים