Nội dung
Lịch sản xuất, tức là một danh sách các ngày, trong đó tất cả các ngày làm việc chính thức và ngày nghỉ đều được đánh dấu tương ứng - một thứ hoàn toàn cần thiết cho bất kỳ người dùng Microsoft Excel nào. Trong thực tế, bạn không thể làm mà không có nó:
- trong tính toán kế toán (tiền lương, thời gian phục vụ, kỳ nghỉ…)
- trong lĩnh vực hậu cần - để xác định chính xác thời gian giao hàng, có tính đến các ngày cuối tuần và ngày lễ (hãy nhớ câu cổ điển “đến sau kỳ nghỉ?”)
- trong quản lý dự án - để ước tính chính xác các điều khoản, một lần nữa, có tính đến những ngày không làm việc
- bất kỳ việc sử dụng các chức năng như NGÀY LÀM VIỆC (NGÀY LÀM VIỆC) or CÔNG NHÂN TINH KHIẾT (NGÀY MẠNG), bởi vì chúng yêu cầu một danh sách các ngày nghỉ làm đối số
- khi sử dụng các chức năng Time Intelligence (như TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR, v.v.) trong Power Pivot và Power BI
- … Vv vv - rất nhiều ví dụ.
Sẽ dễ dàng hơn cho những người làm việc trong các hệ thống ERP của công ty như 1C hoặc SAP, vì lịch sản xuất được tích hợp sẵn trong chúng. Nhưng người dùng Excel thì sao?
Tất nhiên, bạn có thể giữ lịch như vậy theo cách thủ công. Nhưng sau đó, bạn sẽ phải cập nhật nó ít nhất một lần một năm (hoặc thậm chí thường xuyên hơn, như trong “vui vẻ” 2020), cẩn thận nhập tất cả các ngày cuối tuần, chuyển khoản và ngày không làm việc do chính phủ của chúng tôi phát minh. Và sau đó lặp lại quy trình này mỗi năm sau. Chán.
Làm thế nào về việc điên rồ một chút và tạo lịch xuất xưởng "vĩnh viễn" trong Excel? Một cái tự cập nhật, lấy dữ liệu từ Internet và luôn tạo danh sách cập nhật những ngày không làm việc để sử dụng tiếp theo trong bất kỳ tính toán nào? Hấp dẫn?
Để làm được điều này, trên thực tế, không khó chút nào.
Nguồn dữ liệu
Câu hỏi chính là lấy dữ liệu ở đâu? Để tìm kiếm một nguồn phù hợp, tôi đã xem qua một số lựa chọn:
- Các nghị định ban đầu được công bố trên trang web của chính phủ ở định dạng PDF (ví dụ ở đây là một trong số chúng) và biến mất ngay lập tức - không thể lấy thông tin hữu ích ra khỏi chúng.
- Thoạt nhìn, một lựa chọn hấp dẫn có vẻ là “Cổng dữ liệu mở của Liên bang”, nơi có tập dữ liệu tương ứng, nhưng khi xem xét kỹ hơn, mọi thứ trở nên đáng buồn. Trang web này cực kỳ bất tiện khi nhập vào Excel, bộ phận hỗ trợ kỹ thuật không phản hồi (tự cô lập?) và bản thân dữ liệu đã lỗi thời ở đó từ lâu – lịch sản xuất cho năm 2020 được cập nhật lần cuối vào tháng 2019 năm 2020 (thật đáng hổ thẹn!) và , tất nhiên, không chứa “coronavirus ' của chúng tôi và cuối tuần 'bỏ phiếu' năm XNUMX chẳng hạn.
Vỡ mộng với những nguồn chính thức, tôi bắt đầu đào những nguồn không chính thức. Có rất nhiều trong số chúng trên Internet, nhưng hầu hết chúng hoàn toàn không phù hợp để nhập vào Excel và đưa ra lịch sản xuất dưới dạng hình ảnh đẹp. Nhưng nó không phải để chúng ta treo nó trên tường, phải không?
Và trong quá trình tìm kiếm, người ta đã tình cờ phát hiện ra một điều tuyệt vời - đó là trang http://xmlcalendar.ru/
Không có sự “rườm rà” không cần thiết, một trang web đơn giản, nhẹ nhàng và nhanh chóng, được làm rõ cho một nhiệm vụ - cung cấp cho mọi người một lịch sản xuất cho năm mong muốn ở định dạng XML. Xuất sắc!
Nếu đột nhiên, bạn không biết, thì XML là một định dạng văn bản có nội dung được đánh dấu đặc biệt
Để đề phòng, tôi đã liên hệ với các tác giả của trang web và họ xác nhận rằng trang web đã tồn tại được 7 năm, dữ liệu trên đó được cập nhật liên tục (thậm chí họ còn có một chi nhánh trên github cho việc này) và họ sẽ không đóng cửa nó. Và tôi không bận tâm chút nào về việc bạn và tôi tải dữ liệu từ nó cho bất kỳ dự án và tính toán nào của chúng tôi trong Excel. Là miễn phí. Thật vui khi biết rằng vẫn còn những người như thế này! Kính trọng!
Vẫn phải tải dữ liệu này vào Excel bằng cách sử dụng bổ trợ Power Query (đối với các phiên bản Excel 2010-2013, nó có thể được tải xuống miễn phí từ trang web của Microsoft và trong các phiên bản Excel 2016 trở lên, nó đã được tích hợp sẵn theo mặc định ).
Logic của các hành động sẽ như sau:
- Chúng tôi yêu cầu tải xuống dữ liệu từ trang web trong một năm bất kỳ
- Biến yêu cầu của chúng tôi thành một chức năng
- Chúng tôi áp dụng chức năng này cho danh sách tất cả các năm có sẵn, bắt đầu từ năm 2013 và đến năm hiện tại - và chúng tôi nhận được lịch sản xuất "vĩnh viễn" với tính năng cập nhật tự động. Thì đấy!
Bước 1. Nhập lịch cho một năm
Trước tiên, hãy tải lịch sản xuất cho một năm bất kỳ, chẳng hạn như cho năm 2020. Để thực hiện việc này, trong Excel, hãy chuyển đến tab Ngày (Hoặc Truy vấn nguồnnếu bạn đã cài đặt nó như một tiện ích bổ sung riêng biệt) và chọn Từ trên mạng (Từ Web). Trong cửa sổ mở ra, hãy dán liên kết đến năm tương ứng, được sao chép từ trang web:
Sau khi nhấp vào OK một cửa sổ xem trước xuất hiện, trong đó bạn cần nhấp vào nút Chuyển đổi dữ liệu (Chuyển đổi dữ liệu) or Để thay đổi dữ liệu (Chỉnh sửa dữ liệu) và chúng ta sẽ đến cửa sổ trình soạn thảo truy vấn Power Query, nơi chúng ta sẽ tiếp tục làm việc với dữ liệu:
Ngay lập tức, bạn có thể xóa một cách an toàn trong bảng điều khiển bên phải Yêu cầu tham số (Cài đặt truy vấn) bước loại sửa đổi (Loại đã thay đổi) Chúng tôi không cần anh ấy.
Bảng trong cột ngày lễ chứa mã và mô tả về những ngày không làm việc - bạn có thể xem nội dung của bảng bằng cách "xem qua" bảng đó hai lần bằng cách nhấp vào từ màu xanh lục Bàn:
Để quay lại, bạn sẽ phải xóa trong bảng điều khiển bên phải tất cả các bước đã xuất hiện trở lại nguồn (Nguồn).
Bảng thứ hai, có thể được truy cập theo cách tương tự, chứa chính xác những gì chúng ta cần - ngày của tất cả những ngày không làm việc:
Nó vẫn để xử lý tấm này, cụ thể là:
1. Chỉ lọc những ngày lễ (tức là những ngày lễ) theo cột thứ hai Thuộc tính: t
2. Xóa tất cả các cột ngoại trừ cột đầu tiên - bằng cách nhấp chuột phải vào tiêu đề của cột đầu tiên và chọn lệnh Xóa các cột khác (Xóa các cột khác):
3. Chia cột đầu tiên theo dấu chấm riêng biệt cho tháng và ngày bằng lệnh Cột Tách - Theo Dấu phân cách chuyển hướng Chuyển đổi (Chuyển đổi - Chia cột - Theo dấu phân cách):
4. Và cuối cùng tạo một cột được tính toán với ngày bình thường. Để làm điều này, trên tab Thêm một cột nhấn nút Cột tùy chỉnh (Thêm cột - Cột tùy chỉnh) và nhập công thức sau vào cửa sổ xuất hiện:
=#ngày(2020, [# »Thuộc tính: d.1 ″], [#» Thuộc tính: d.2 ″])
Ở đây, toán tử #date có ba đối số: năm, tháng và ngày, tương ứng. Sau khi nhấp vào OK chúng tôi nhận được cột bắt buộc với các ngày cuối tuần bình thường và xóa các cột còn lại như trong bước 2
Bước 2. Chuyển yêu cầu thành một hàm
Nhiệm vụ tiếp theo của chúng tôi là chuyển đổi truy vấn được tạo cho năm 2020 thành một hàm phổ quát cho bất kỳ năm nào (số năm sẽ là đối số của nó). Để làm điều này, chúng tôi làm như sau:
1. Mở rộng (nếu chưa được mở rộng) bảng điều khiển Yêu cầu (Truy vấn) ở bên trái trong cửa sổ Power Query:
2. Sau khi chuyển đổi yêu cầu thành một chức năng, rất tiếc, khả năng xem các bước tạo nên yêu cầu và dễ dàng chỉnh sửa chúng sẽ biến mất. Do đó, thật hợp lý khi tạo một bản sao yêu cầu của chúng tôi và vui đùa với nó, đồng thời để bản gốc dự phòng. Để thực hiện việc này, hãy nhấp chuột phải vào ngăn bên trái trên yêu cầu lịch của chúng tôi và chọn lệnh Nhân bản.
Nhấp chuột phải một lần nữa vào bản sao kết quả của lịch (2) sẽ chọn lệnh Đổi tên (Đổi tên) và nhập một tên mới - ví dụ: fxnăm:
3. Chúng tôi mở mã nguồn truy vấn bằng ngôn ngữ Power Query nội bộ (nó được gọi ngắn gọn là “M”) bằng cách sử dụng lệnh Trình chỉnh sửa nâng cao chuyển hướng Đánh giá(Xem - Trình chỉnh sửa nâng cao) và thực hiện những thay đổi nhỏ ở đó để biến yêu cầu của chúng tôi thành một chức năng trong bất kỳ năm nào.
Đó là:
Sau:
Nếu bạn quan tâm đến các chi tiết, sau đây:
- (năm dưới dạng số) => - chúng tôi tuyên bố rằng hàm của chúng tôi sẽ có một đối số số - một biến năm
- Dán biến năm đến liên kết web trong bước nguồn. Vì Power Query không cho phép bạn gắn số và văn bản, chúng tôi chuyển đổi số năm thành văn bản một cách nhanh chóng bằng cách sử dụng chức năng Số.ToText
- Chúng tôi thay thế biến số năm cho năm 2020 ở bước áp chót # ”Đã thêm đối tượng tùy chỉnh«, nơi chúng tôi hình thành ngày tháng từ các mảnh vỡ.
Sau khi nhấp vào Kết thúc yêu cầu của chúng tôi trở thành một hàm:
Bước 3. Nhập lịch cho tất cả các năm
Việc cuối cùng còn lại là thực hiện truy vấn chính cuối cùng, truy vấn này sẽ tải lên dữ liệu cho tất cả các năm có sẵn và thêm tất cả các ngày lễ đã nhận vào một bảng. Đối với điều này:
1. Chúng tôi nhấp vào bảng truy vấn bên trái trong không gian trống màu xám bằng nút chuột phải và chọn tuần tự Yêu cầu mới - Các nguồn khác - Yêu cầu trống (Truy vấn mới - Từ các nguồn khác - Truy vấn trống):
2. Chúng tôi cần tạo danh sách tất cả các năm mà chúng tôi sẽ yêu cầu lịch, tức là 2013, 2014… 2020. Để thực hiện việc này, trong thanh công thức của truy vấn trống xuất hiện, hãy nhập lệnh:
Kết cấu:
= {NumberA..NumberB}
… Trong Power Query tạo ra một danh sách các số nguyên từ A đến B. Ví dụ: biểu thức
= {1..5}
… Sẽ tạo ra một danh sách 1,2,3,4,5.
Vâng, để không bị ràng buộc một cách cứng nhắc đến năm 2020, chúng tôi sử dụng hàm DateTime.LocalNow () - tương tự của hàm Excel TODAY (HÔM NAY) trong Power Query - và lần lượt trích xuất từ đó năm hiện tại bằng hàm Ngày.Năm.
3. Tập hợp năm kết quả, mặc dù có vẻ khá đầy đủ, không phải là một bảng cho Power Query, mà là một đối tượng đặc biệt - (Danh sách). Nhưng chuyển đổi nó thành một bảng không phải là một vấn đề: chỉ cần nhấp vào nút Đến bàn (Để bàn) ở góc trên bên trái:
4. Dòng kết thúc! Áp dụng chức năng chúng tôi đã tạo trước đó fxnăm vào danh sách kết quả của năm. Để làm điều này, trên tab Thêm một cột Nhấn nút Gọi chức năng tùy chỉnh (Thêm cột - Gọi chức năng tùy chỉnh) và đặt đối số duy nhất của nó - cột Column1 qua nhiều năm:
Sau khi nhấp vào OK chức năng của chúng tôi fxnăm quá trình nhập sẽ hoạt động lần lượt cho mỗi năm và chúng ta sẽ nhận được một cột trong đó mỗi ô sẽ chứa một bảng với ngày của những ngày không làm việc (nội dung của bảng sẽ hiển thị rõ ràng nếu bạn nhấp vào nền của ô bên cạnh từ Bàn):
Nó vẫn để mở rộng nội dung của các bảng lồng nhau bằng cách nhấp vào biểu tượng có mũi tên đôi trong tiêu đề cột Ngày (đánh dấu Sử dụng tên cột ban đầu làm tiền tố nó có thể được loại bỏ):
… Và sau khi nhấp vào OK chúng tôi có được những gì chúng tôi muốn - danh sách tất cả các ngày lễ từ năm 2013 đến năm hiện tại:
Cột đầu tiên, đã không cần thiết, có thể bị xóa và cột thứ hai, đặt kiểu dữ liệu ngày (Ngày) trong danh sách thả xuống trong tiêu đề cột:
Bản thân truy vấn có thể được đổi tên thành một cái gì đó có ý nghĩa hơn Yêu cầu1 và sau đó tải kết quả lên trang tính ở dạng bảng “thông minh” động bằng cách sử dụng lệnh đóng và tải xuống chuyển hướng Trang Chủ (Trang chủ - Đóng & Tải):
Bạn có thể cập nhật lịch đã tạo trong tương lai bằng cách nhấp chuột phải vào bảng hoặc truy vấn trong ngăn bên phải thông qua lệnh Cập nhật & Lưu. Hoặc sử dụng nút Làm mới tất cả chuyển hướng Ngày (Ngày - Làm mới tất cả) hoặc phím tắt Ctrl+Khác+F5.
Đó là tất cả.
Giờ đây, bạn không bao giờ cần phải lãng phí thời gian và suy nghĩ để tìm kiếm và cập nhật danh sách các ngày lễ nữa - giờ đây bạn đã có một lịch sản xuất “vĩnh viễn”. Trong mọi trường hợp, miễn là các tác giả của trang web http://xmlcalendar.ru/ ủng hộ thế hệ con cháu của họ, tôi hy vọng sẽ rất lâu nữa (cảm ơn họ một lần nữa!).
- Nhập tỷ giá bitcoin để vượt trội từ internet thông qua Power Query
- Tìm ngày làm việc tiếp theo bằng hàm WORKDAY
- Tìm giao điểm của các khoảng ngày