ปฏิทินโรงงานใน Excel

ปฏิทินการผลิต เช่น รายการวันที่ โดยระบุวันทำงานและวันหยุดราชการทั้งหมดตามนั้น - เป็นสิ่งที่จำเป็นอย่างยิ่งสำหรับผู้ใช้ Microsoft Excel ในทางปฏิบัติคุณไม่สามารถทำได้โดย:

• ในการคำนวณทางบัญชี (เงินเดือน ระยะเวลาการทำงาน วันหยุด …)
• ในด้านลอจิสติกส์ – สำหรับการกำหนดเวลาจัดส่งที่ถูกต้อง โดยคำนึงถึงวันหยุดสุดสัปดาห์และวันหยุดนักขัตฤกษ์ (จำคำว่า "มาหลังวันหยุด" แบบคลาสสิกได้ไหม)
  
• ในการจัดการโครงการ – สำหรับการประเมินเงื่อนไขที่ถูกต้องโดยคำนึงถึงวันทำงานที่ไม่ใช่วันทำงาน
• การใช้ฟังก์ชั่นเช่น วันทำงาน (วันทำงาน) or คนงานบริสุทธิ์ (เครือข่ายวัน)เพราะพวกเขาต้องการรายการวันหยุดเป็นข้อโต้แย้ง
• เมื่อใช้ฟังก์ชัน 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 โดยใช้ Add-in ของ Power Query (สำหรับเวอร์ชันของ Excel 2010-2013 สามารถดาวน์โหลดได้ฟรีจากเว็บไซต์ Microsoft และในเวอร์ชันของ Excel 2016 และใหม่กว่าจะมีอยู่แล้วภายในตามค่าเริ่มต้น ).

ตรรกะของการกระทำจะเป็นดังนี้:

1. เราขอดาวน์โหลดข้อมูลจากเว็บไซต์เป็นเวลาหนึ่งปี
2. เปลี่ยนคำขอของเราให้เป็นฟังก์ชั่น
3. เราใช้ฟังก์ชันนี้กับรายการปีที่มีทั้งหมด เริ่มตั้งแต่ปี 2013 จนถึงปีปัจจุบัน และเราได้รับปฏิทินการผลิต "ถาวร" พร้อมการอัปเดตอัตโนมัติ โว้ว!
   

ขั้นตอนที่ 1. นำเข้าปฏิทินเป็นเวลาหนึ่งปี

ขั้นแรก ให้โหลดปฏิทินการผลิตสำหรับปีใดก็ได้ เช่น ปี 2020 เมื่อต้องการทำเช่นนี้ ใน Excel ให้ไปที่แท็บ ข้อมูล (หรือ Power Queryหากคุณติดตั้งเป็นส่วนเสริมแยกต่างหาก) และเลือก จากอินเทอร์เน็ต (จากเว็บ). ในหน้าต่างที่เปิดขึ้น ให้วางลิงก์ไปยังปีที่เกี่ยวข้อง ซึ่งคัดลอกมาจากไซต์:

หลังจากคลิกที่ OK หน้าต่างแสดงตัวอย่างปรากฏขึ้น ซึ่งคุณต้องคลิกปุ่ม แปลงข้อมูล (แปลงข้อมูล) or เพื่อเปลี่ยนข้อมูล (แก้ไขข้อมูล) และเราจะไปที่หน้าต่างตัวแก้ไขคิวรี Power Query ซึ่งเราจะทำงานกับข้อมูลต่อไป:

ทันทีคุณสามารถลบได้อย่างปลอดภัยในแผงด้านขวา ขอพารามิเตอร์ (การตั้งค่าแบบสอบถาม) ขั้นตอน แก้ไขประเภท (เปลี่ยนประเภท) เราไม่ต้องการเขา

ตารางในคอลัมน์วันหยุดประกอบด้วยรหัสและคำอธิบายของวันที่ไม่ทำงาน คุณสามารถดูเนื้อหาได้โดย "ผ่าน" สองครั้งโดยคลิกที่คำสีเขียว ตาราง:

ในการย้อนกลับคุณจะต้องลบในแผงด้านขวาขั้นตอนทั้งหมดที่ปรากฏขึ้นกลับมาที่ แหล่ง (ที่มา).

ตารางที่สอง ซึ่งสามารถเข้าถึงได้ในลักษณะเดียวกัน มีสิ่งที่เราต้องการ นั่นคือวันที่ของวันที่ไม่ทำงานทั้งหมด:

มันยังคงดำเนินการกับจานนี้กล่าวคือ:

1. กรองเฉพาะวันหยุด (เช่นวัน) ตามคอลัมน์ที่สอง แอตทริบิวต์:t

2. ลบคอลัมน์ทั้งหมดยกเว้นคอลัมน์แรก – โดยคลิกขวาที่ส่วนหัวของคอลัมน์แรกและเลือกคำสั่ง ลบคอลัมน์อื่นๆ (ลบคอลัมน์อื่น):

3. แยกคอลัมน์แรกด้วยจุดแยกกันสำหรับเดือนและวันด้วยคำสั่ง แยกคอลัมน์ – ตามตัวคั่น แถบ การแปลง (แปลง — แยกคอลัมน์ — ตามตัวคั่น):

4. และสุดท้ายสร้างคอลัมน์จากการคำนวณด้วยวันที่ปกติ ในการทำเช่นนี้บนแท็บ การเพิ่มคอลัมน์ คลิกที่ปุ่ม คอลัมน์ที่กำหนดเอง (เพิ่มคอลัมน์ — คอลัมน์กำหนดเอง) และป้อนสูตรต่อไปนี้ในหน้าต่างที่ปรากฏขึ้น:

=#ลงวันที่(2020, [#»แอตทริบิวต์:d.1″], [#»แอตทริบิวต์:d.2″])

ในที่นี้ ตัวดำเนินการ #date มีอาร์กิวเมนต์สามตัว: ปี เดือน และวัน ตามลำดับ หลังจากคลิกที่ OK เราได้รับคอลัมน์ที่จำเป็นพร้อมวันหยุดสุดสัปดาห์ปกติและลบคอลัมน์ที่เหลือตามขั้นตอนที่2

ขั้นตอนที่ 2 เปลี่ยนคำขอเป็นฟังก์ชัน

งานต่อไปของเราคือการแปลงการสืบค้นที่สร้างสำหรับปี 2020 เป็นฟังก์ชันสากลสำหรับปีใดๆ (หมายเลขปีจะเป็นอาร์กิวเมนต์) ในการทำเช่นนี้ เราทำสิ่งต่อไปนี้:

1. การขยาย (ถ้ายังไม่ได้ขยาย) แผงควบคุม สอบถามข้อมูล (คำถาม) ทางด้านซ้ายในหน้าต่าง Power Query:

2. หลังจากแปลงคำขอเป็นฟังก์ชันแล้ว ความสามารถในการดูขั้นตอนที่ประกอบเป็นคำขอและแก้ไขได้อย่างง่ายดาย แต่น่าเสียดายที่หายไป ดังนั้นจึงเหมาะสมที่จะทำสำเนาคำขอของเราและสนุกสนานไปกับมันแล้วและปล่อยให้ต้นฉบับสำรองไว้ ในการดำเนินการนี้ ให้คลิกขวาในบานหน้าต่างด้านซ้ายบนคำขอปฏิทินของเรา แล้วเลือกคำสั่งทำซ้ำ

คลิกขวาอีกครั้งบนสำเนาผลลัพธ์ของปฏิทิน(2) จะเลือกคำสั่ง ตั้งชื่อใหม่ (เปลี่ยนชื่อ) แล้วป้อนชื่อใหม่ เช่น fxปี:

3. เราเปิดซอร์สโค้ดของคิวรีในภาษา Power Query ภายใน (เรียกสั้นๆ ว่า "M") โดยใช้คำสั่ง ตัวแก้ไขขั้นสูง แถบ รีวิว(ดู — ตัวแก้ไขขั้นสูง) และทำการเปลี่ยนแปลงเล็กน้อยที่นั่นเพื่อเปลี่ยนคำขอของเราให้เป็นฟังก์ชันสำหรับปีใดก็ได้

มันคือ:

หลังจากที่:

หากคุณสนใจในรายละเอียดแล้วที่นี่:

• (ปีเป็นตัวเลข)=>  – เราประกาศว่าฟังก์ชันของเราจะมีอาร์กิวเมนต์ตัวเลขหนึ่งตัว – ตัวแปร ปี
• วางตัวแปร ปี ไปยังเว็บลิงค์ในขั้นตอน แหล่ง. เนื่องจาก Power Query ไม่อนุญาตให้คุณติดตัวเลขและข้อความ เราจึงแปลงหมายเลขปีเป็นข้อความได้ทันทีโดยใช้ฟังก์ชัน เบอร์.ToText
• เราแทนที่ตัวแปรปีสำหรับปี 2020 ในขั้นตอนสุดท้าย #”เพิ่มวัตถุที่กำหนดเอง«ที่เราสร้างวันที่จากชิ้นส่วน
  

หลังจากคลิกที่ เสร็จสิ้น คำขอของเรากลายเป็นฟังก์ชัน:

ขั้นตอนที่ 3 นำเข้าปฏิทินสำหรับทุกปี

สิ่งสุดท้ายที่เหลืออยู่คือการสร้างแบบสอบถามหลักสุดท้าย ซึ่งจะอัปโหลดข้อมูลสำหรับปีที่มีอยู่ทั้งหมด และเพิ่มวันที่ในวันหยุดที่ได้รับทั้งหมดลงในตารางเดียว สำหรับสิ่งนี้:

1. เราคลิกในแผงแบบสอบถามด้านซ้ายในพื้นที่ว่างสีเทาด้วยปุ่มเมาส์ขวาและเลือกตามลำดับ คำขอใหม่ – แหล่งอื่น – คำขอว่างเปล่า (แบบสอบถามใหม่ — จากแหล่งอื่น — แบบสอบถามเปล่า):

2. เราจำเป็นต้องสร้างรายชื่อปีทั้งหมดที่เราจะขอปฏิทิน เช่น 2013, 2014 … 2020 ในการทำเช่นนี้ ในแถบสูตรของคิวรีว่างที่ปรากฏขึ้น ให้ป้อนคำสั่ง:

โครงสร้าง:

={หมายเลขเอ..หมายเลขบี}

… ใน Power Query จะสร้างรายการจำนวนเต็มจาก A ถึง B ตัวอย่างเช่น นิพจน์

={1..5}

… จะผลิตรายการ 1,2,3,4,5

เพื่อไม่ให้ผูกมัดอย่างแน่นหนากับปี 2020 เราใช้ฟังก์ชั่น DateTime.LocalNow() – อะนาล็อกของฟังก์ชัน Excel วันนี้ (วันนี้) ใน Power Query – และแยกจากปีปัจจุบันโดยฟังก์ชัน วันที่.ปี.

3. ชุดปีที่เป็นผลลัพธ์ แม้ว่าจะดูเพียงพอ แต่ก็ไม่ใช่ตารางสำหรับ Power Query แต่เป็นออบเจ็กต์พิเศษ – รายการ (รายการ). แต่การแปลงเป็นตารางไม่ใช่ปัญหา แค่คลิกปุ่ม ไปที่โต๊ะ (ไปที่โต๊ะ) ที่มุมซ้ายบน:

4. เส้นชัย! การใช้ฟังก์ชันที่เราสร้างไว้ก่อนหน้านี้ fxปี ไปยังรายการผลลัพธ์ของปี ในการทำเช่นนี้บนแท็บ การเพิ่มคอลัมน์ กดปุ่ม เรียกใช้ฟังก์ชันกำหนดเอง (เพิ่มคอลัมน์ — เรียกใช้ฟังก์ชันแบบกำหนดเอง) และตั้งอาร์กิวเมนต์เพียงอย่างเดียว – คอลัมน์ Column1 นานนับปี:

หลังจากคลิกที่ OK หน้าที่ของเรา fxปี การนำเข้าจะทำงานในแต่ละปีและเราจะได้รับคอลัมน์ที่แต่ละเซลล์จะมีตารางที่มีวันที่ของวันที่ไม่ทำงาน (เนื้อหาของตารางจะมองเห็นได้ชัดเจนหากคุณคลิกในพื้นหลังของเซลล์ถัดจาก คำ ตาราง):

มันยังคงขยายเนื้อหาของตารางที่ซ้อนกันโดยคลิกที่ไอคอนที่มีลูกศรคู่ในส่วนหัวของคอลัมน์ วันที่ (ติ๊ก ใช้ชื่อคอลัมน์เดิมเป็นคำนำหน้า สามารถลบออกได้):

… และหลังจากคลิกที่ OK เราได้สิ่งที่ต้องการแล้ว – รายการวันหยุดทั้งหมดตั้งแต่ปี 2013 ถึงปีปัจจุบัน:

คอลัมน์แรกซึ่งไม่จำเป็นอยู่แล้วสามารถลบได้ และสำหรับคอลัมน์ที่สอง ให้ตั้งค่าประเภทข้อมูล ข้อมูล (วันที่) ในรายการดรอปดาวน์ในส่วนหัวของคอลัมน์:

แบบสอบถามเองสามารถเปลี่ยนชื่อสิ่งที่มีความหมายมากกว่า คำขอ1 แล้วอัปโหลดผลลัพธ์ไปยังชีตในรูปแบบของตาราง "อัจฉริยะ" แบบไดนามิกโดยใช้คำสั่ง ปิดและดาวน์โหลด แถบ หน้าแรก (หน้าแรก — ปิด & โหลด):

คุณสามารถอัปเดตปฏิทินที่สร้างขึ้นได้ในอนาคตโดยคลิกขวาที่ตารางหรือแบบสอบถามในบานหน้าต่างด้านขวาโดยใช้คำสั่ง อัปเดตและบันทึก. หรือใช้ปุ่ม รีเฟรชทั้งหมด แถบ ข้อมูล (วันที่ — รีเฟรชทั้งหมด) หรือแป้นพิมพ์ลัด Ctrl+อื่น ๆ+F5.

นั่นคือทั้งหมด

ตอนนี้คุณไม่ต้องเสียเวลาและคิดเรื่องเชื้อเพลิงในการค้นหาและปรับปรุงรายการวันหยุดอีกต่อไป ตอนนี้คุณมีปฏิทินการผลิตที่ "ถาวร" แล้ว ไม่ว่าในกรณีใดตราบใดที่ผู้เขียนเว็บไซต์ http://xmlcalendar.ru/ สนับสนุนลูกหลานของพวกเขาซึ่งฉันหวังว่าจะเป็นเวลานานมาก (ขอบคุณพวกเขาอีกครั้ง!)

• นำเข้าอัตรา bitcoin เพื่อ excel จากอินเทอร์เน็ตผ่าน Power Query
• ค้นหาวันทำการถัดไปโดยใช้ฟังก์ชัน WORKDAY
  
• การหาจุดตัดของช่วงวันที่