ქარხნული კალენდარი Excel-ში

წარმოების კალენდარი, ანუ თარიღების სია, სადაც ყველა ოფიციალური სამუშაო დღეები და უქმე დღეები შესაბამისად აღინიშნება – აბსოლუტურად აუცილებელი რამ Microsoft Excel-ის ნებისმიერი მომხმარებლისთვის. პრაქტიკაში, ამის გარეშე არ შეგიძლიათ:

• საბუღალტრო გამოთვლებში (ხელფასი, სტაჟი, შვებულება…)
• ლოჯისტიკაში - მიწოდების დროების სწორად განსაზღვრისთვის, შაბათ-კვირის და არდადეგების გათვალისწინებით (გაიხსენეთ კლასიკური "მოდი არდადეგების შემდეგ?")
  
• პროექტის მენეჯმენტში – ვადების სწორი შეფასებისთვის, ისევ სამუშაო-არასამუშაო დღეების გათვალისწინებით.
• ფუნქციების ნებისმიერი გამოყენება, როგორიცაა ᲡᲐᲛᲣᲨᲐᲝ ᲓᲦᲔ (ᲡᲐᲛᲣᲨᲐᲝ ᲓᲦᲔ) or სუფთა მუშები (NETWORKDAYS), რადგან არგუმენტად მოითხოვენ არდადეგების ჩამონათვალს
• Time Intelligence ფუნქციების გამოყენებისას (როგორიცაა 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 წელია არსებობს, მასზე მონაცემები მუდმივად ახლდება (ამისთვის github-ზეც კი აქვთ ფილიალი) და დახურვას არ აპირებენ. და საერთოდ არ მაწუხებს ის, რომ მე და თქვენ ვტვირთავთ მისგან მონაცემებს ჩვენი რომელიმე პროექტისთვის და გამოთვლებისთვის Excel-ში. Თავისუფალია. სასიამოვნოა იმის ცოდნა, რომ ჯერ კიდევ არსებობენ ასეთი ადამიანები! პატივისცემა!

რჩება ამ მონაცემების Excel-ში ჩატვირთვა Power Query დანამატის გამოყენებით (Excel-ის 2010-2013 ვერსიებისთვის ის შეიძლება უფასოდ ჩამოტვირთოთ Microsoft-ის ვებსაიტიდან, ხოლო Excel 2016 და უფრო ახალ ვერსიებში ის უკვე ჩაშენებულია ნაგულისხმევად. ).

მოქმედებების ლოგიკა იქნება შემდეგი:

1. ჩვენ ვთხოვთ ჩამოტვირთოთ მონაცემები საიტიდან ნებისმიერი ერთი წლის განმავლობაში
2. ჩვენი მოთხოვნის ფუნქციად გადაქცევა
3. ჩვენ ვიყენებთ ამ ფუნქციას ყველა ხელმისაწვდომი წლის ჩამონათვალში, 2013 წლიდან დაწყებული და მიმდინარე წლამდე - და ვიღებთ "მუდმივ" წარმოების კალენდარს ავტომატური განახლებით. ვოილა!
   

ნაბიჯი 1. შემოიტანეთ კალენდარი ერთი წლის განმავლობაში

პირველ რიგში, ჩატვირთეთ წარმოების კალენდარი ნებისმიერი ერთი წლისთვის, მაგალითად, 2020 წლისთვის. ამისათვის Excel-ში გადადით ჩანართზე თარიღი (ან დენის მოთხოვნათუ დააინსტალირეთ ცალკე დანამატად) და აირჩიეთ ინტერნეტიდან (ვებებიდან). ფანჯარაში, რომელიც იხსნება, ჩასვით საიტიდან დაკოპირებული შესაბამისი წლის ბმული:

დაწკაპვით OK ჩნდება გადახედვის ფანჯარა, რომელშიც თქვენ უნდა დააჭიროთ ღილაკს მონაცემთა კონვერტაცია (მონაცემების ტრანსფორმაცია) or მონაცემების შესაცვლელად (მონაცემების რედაქტირება) და ჩვენ მივიღებთ Power Query შეკითხვის რედაქტორის ფანჯარას, სადაც გავაგრძელებთ მუშაობას მონაცემებთან:

დაუყოვნებლივ შეგიძლიათ უსაფრთხოდ წაშალოთ მარჯვენა პანელში მოითხოვეთ პარამეტრები (შეკითხვის პარამეტრები) გადადგება შეცვლილი ტიპი (შეცვლილი ტიპი) ჩვენ ის არ გვჭირდება.

არდადეგების სვეტის ცხრილი შეიცავს კოდებს და არასამუშაო დღეების აღწერილობას - შეგიძლიათ იხილოთ მისი შინაარსი ორჯერ „ჩავარდნით“ მწვანე სიტყვაზე დაწკაპუნებით. მაგიდის:

უკან დასაბრუნებლად, თქვენ მოგიწევთ მარჯვენა პანელში წაშალოთ ყველა ის ნაბიჯი, რომელიც უკან გამოჩნდა წყარო (წყარო).

მეორე ცხრილი, რომლის წვდომაც შესაძლებელია ანალოგიურად, შეიცავს ზუსტად იმას, რაც ჩვენ გვჭირდება - ყველა არასამუშაო დღის თარიღები:

რჩება ამ ფირფიტის დამუშავება, კერძოდ:

1. გაფილტრეთ მხოლოდ დასვენების თარიღები (ანუ თარიღები) მეორე სვეტით ატრიბუტი: ტ

2. წაშალეთ ყველა სვეტი პირველის გარდა – პირველი სვეტის სათაურზე მაუსის მარჯვენა ღილაკით და ბრძანების არჩევით წაშალეთ სხვა სვეტები (ამოშალე სხვა სვეტები):

3. გაყავით პირველი სვეტი ცალ-ცალკე წერტილით თვისა და დღისთვის ბრძანებით Split Column – საზღვრის მიხედვით tab ტრანსფორმაციის (ტრანსფორმა — სვეტის გაყოფა — დელიმიტერის მიხედვით):

4. და ბოლოს შექმენით გამოთვლილი სვეტი ნორმალური თარიღებით. ამისათვის, ჩანართზე სვეტის დამატება დააჭირეთ ღილაკს მორგებული სვეტი (სვეტის დამატება — მორგებული სვეტი) და შეიტანეთ შემდეგი ფორმულა ფანჯარაში, რომელიც გამოჩნდება:

=#დათარიღებული(2020, [#»ატრიბუტი:d.1″], [#»ატრიბუტი:d.2″])

აქ #date ოპერატორს აქვს სამი არგუმენტი: წელი, თვე და დღე, შესაბამისად. დაწკაპუნების შემდეგ OK ჩვენ ვიღებთ საჭირო სვეტს ჩვეულებრივი შაბათ-კვირის თარიღებით და ვშლით დარჩენილ სვეტებს, როგორც ნაბიჯი 2-ში

ნაბიჯი 2. მოთხოვნის ფუნქციად გადაქცევა

ჩვენი შემდეგი ამოცანაა 2020 წლისთვის შექმნილი შეკითხვის გადაყვანა უნივერსალურ ფუნქციად ნებისმიერი წლისთვის (წლის რიცხვი იქნება მისი არგუმენტი). ამისათვის ჩვენ ვაკეთებთ შემდეგს:

1. პანელის გაფართოება (თუ უკვე არ არის გაფართოებული). ინტერაქციები (შეკითხვები) მარცხნივ Power Query ფანჯარაში:

2. მოთხოვნის ფუნქციად გადაყვანის შემდეგ ქრება მოთხოვნის შემადგენელი ნაბიჯების ნახვის და მათი მარტივად რედაქტირების შესაძლებლობა, სამწუხაროდ, ქრება. აქედან გამომდინარე, აზრი აქვს ჩვენი თხოვნის ასლის გაკეთებას და უკვე მასთან ერთად მხიარულებას, ხოლო ორიგინალის რეზერვში დატოვება. ამისათვის დააწკაპუნეთ მაუსის მარჯვენა ღილაკით მარცხენა პანელზე ჩვენი კალენდრის მოთხოვნაზე და აირჩიეთ Duplicate ბრძანება.

ხელახლა დააწკაპუნეთ კალენდრის (2) ასლზე მარჯვენა ღილაკით, შეარჩევს ბრძანებას გადარქმევა (გადარქმევა) და შეიყვანეთ ახალი სახელი - ასე იყოს, მაგალითად, fxYear:

3. ჩვენ ვხსნით მოთხოვნის წყაროს კოდს შიდა Power Query ენაზე (მას მოკლედ უწოდებენ "M") ბრძანების გამოყენებით გაფართოებული რედაქტორი tab მიმოხილვა(ნახვა - გაფართოებული რედაქტორი) და იქ შეიტანეთ მცირე ცვლილებები, რომ ჩვენი მოთხოვნა ნებისმიერი წლის ფუნქციად აქციოს.

Ის იყო:

შემდეგ:

თუ გაინტერესებთ დეტალები, მაშინ აქ:

• (წელი როგორც ნომერი)=>  – ვაცხადებთ, რომ ჩვენს ფუნქციას ექნება ერთი რიცხვითი არგუმენტი – ცვლადი წელი
• ცვლადის ჩასმა წელი ვებ ბმულზე ეტაპობრივად წყარო. ვინაიდან Power Query არ გაძლევთ საშუალებას დააწებოთ რიცხვები და ტექსტი, ჩვენ ვაქცევთ წლის ნომერს ტექსტად ფრენის დროს ფუნქციის გამოყენებით ნომერი.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 და შემდეგ ატვირთეთ შედეგები ფურცელზე დინამიური „ჭკვიანი“ ცხრილის სახით ბრძანების გამოყენებით დახურეთ და გადმოწერეთ tab მთავარი (მთავარი — დახურვა და ჩატვირთვა):

თქვენ შეგიძლიათ განაახლოთ შექმნილი კალენდარი მომავალში ცხრილზე მაუსის მარჯვენა ღილაკით ან მარჯვენა პანელში შეკითხვით ბრძანების მეშვეობით განახლება და შენახვა. ან გამოიყენეთ ღილაკი განაახლეთ ყველაფერი tab თარიღი (თარიღი - ყველას განახლება) ან კლავიატურის მალსახმობი Ctrl+Alt+F5.

სულ ეს არის.

ახლა აღარასოდეს დაგჭირდებათ დროისა და საწვავის დახარჯვა არდადეგების სიის ძიებასა და განახლებაში - ახლა თქვენ გაქვთ "მუდმივი" წარმოების კალენდარი. ნებისმიერ შემთხვევაში, სანამ საიტის ავტორები http://xmlcalendar.ru/ მხარს უჭერენ თავიანთ შთამომავლობას, რომელიც, იმედი მაქვს, იქნება ძალიან, ძალიან დიდი ხნის განმავლობაში (კიდევ ერთხელ მათი მადლობა!).

• შემოიტანეთ ბიტკოინის კურსი ინტერნეტიდან ექსელისთვის Power Query-ის საშუალებით
• შემდეგი სამუშაო დღის პოვნა WORKDAY ფუნქციის გამოყენებით
  
• თარიღის ინტერვალების კვეთის პოვნა