生產日曆，即日期列表，所有官方工作日和節假日都相應標記——對於任何 Microsoft Excel 用戶來說都是絕對必要的。 在實踐中，你不能沒有它：
- 在會計計算中（工資、服務年限、假期……）
- 在物流方面——為了正確確定交貨時間，考慮到週末和節假日（還記得經典的“節後來嗎？”）
- 在項目管理中——為了正確估計條款，再次考慮到工作日和非工作日
- 任何使用功能，如 工作日 （工作日） or 純工人 （網絡日），因為它們需要一個假期列表作為參數
- 在 Power Pivot 和 Power BI 中使用時間智能函數（如 TOTALYTD、TOTALMTD、SAMEPERIODLASTYEAR 等）時
- ……等等等等——很多例子。
對於那些在 1C 或 SAP 等企業 ERP 系統中工作的人來說，這更容易，因為它們內置了生產日曆。 但是 Excel 用戶呢？
當然，您可以手動保留這樣的日曆。 但是，您必須每年至少更新一次（甚至更頻繁，如“歡樂” 2020 年），仔細輸入我們政府發明的所有周末、轉賬和非工作日。 然後每年重複這個過程。 無聊。
來點瘋狂並在 Excel 中製作“永久”工廠日曆怎麼樣？ 一個自我更新、從 Internet 獲取數據並始終生成最新的非工作日列表以供後續用於任何計算的系統？ 誘人？
要做到這一點，其實一點也不難。
資料來源
主要問題是從哪裡獲取數據？ 為了尋找合適的來源，我經歷了幾個選擇：
- 最初的法令以 PDF 格式發佈在政府的網站上（這裡是其中之一）並立即消失 - 無法從中提取有用的信息。
- 乍一看，一個誘人的選擇似乎是“聯邦開放資料入口網站”，那裡有相應的資料集，但仔細一看，一切都變得令人悲傷。該網站導入 Excel 非常不方便，技術支援沒有回應（自我隔離？），而且資料本身已經過時很長一段時間了——2020 年的生產日曆上次更新是在 2019 年 2020 月（丟臉！）當然，不包含我們的「冠狀病毒」和XNUMX 年「投票」週末等內容。
對官方消息感到失望，我開始挖掘非官方消息。 網上有很多，但大部分還是完全不適合導入 Excel 並以精美圖片的形式給出生產日曆。 但我們不能把它掛在牆上，對吧？
而在搜索的過程中，意外發現了一個奇妙的東西——網站http://xmlcalendar.ru/
沒有不必要的“裝飾”，一個簡單、輕便、快速的網站，專為一項任務而設計——以 XML 格式為每個人提供所需年份的生產日曆。 出色的！
如果突然間，您不知道，那麼 XML 是一種文本格式，其內容用特殊標記
以防萬一，我聯繫了該網站的作者，他們確認該網站已經存在 7 年，其上的數據不斷更新（他們甚至在 github 上為此設立了一個分支）並且他們不會關閉它。 而且我完全不介意您和我為我們在 Excel 中的任何項目和計算從中加載數據。 免費。 很高興知道還有這樣的人！ 尊重！
仍然可以使用 Power Query 加載項將此數據加載到 Excel 中（對於 Excel 2010-2013 版本，可以從 Microsoft 網站免費下載，在 Excel 2016 及更高版本中，默認情況下已內置）。
動作的邏輯如下：
- 我們要求從網站下載任何一年的數據
- 把我們的請求變成一個函數
- 我們將此功能應用於所有可用年份的列表，從 2013 年開始一直到當前年份 - 我們會得到一個自動更新的“永久”生產日曆。 瞧！
步驟 1. 導入一年的日曆
首先，加載任何一年的生產日曆，例如 2020 年。為此，在 Excel 中，轉到選項卡 數據 電源查詢如果您將其作為單獨的附加組件安裝）並選擇 來自網絡 （來自網絡）. 在打開的窗口中，粘貼從網站複製的相應年份的鏈接：
點擊後 OK 出現一個預覽窗口，您需要在其中單擊按鈕 轉換數據 （轉換數據） or 更改數據 （編輯數據） 我們將進入 Power Query 查詢編輯器窗口，我們將在其中繼續處理數據：
您可以立即在右側面板中安全地刪除 請求參數 （查詢設置） 步驟 修改型 （更改類型） 我們不需要他。
假期列中的表格包含非工作日的代碼和描述 - 您可以通過單擊綠色單詞“落入”兩次來查看其內容 枱燈:
要返回，您必須在右側面板中刪除所有已出現的步驟 資源 （資源）.
第二個表，可以通過類似的方式訪問，包含我們需要的內容——所有非工作日的日期：
剩下的就是處理這個板塊，即：
1. 僅按第二列過濾假期日期（即假期） 屬性：t
2. 刪除除第一列之外的所有列 - 右鍵單擊第一列的標題並選擇命令 刪除其他列 （刪除其他列）:
3. 使用命令分別按月和日拆分第一列 拆分列 - 按分隔符 選項卡 轉型 （變換——拆分列——按分隔符）:
4. 最後創建一個具有正常日期的計算列。 為此，在選項卡上 添加列 點擊按鈕 自定義列 （添加列 - 自定義列） 並在出現的窗口中輸入以下公式：
=#日期(2020, [#»屬性:d.1″], [#»屬性:d.2″])
這裡，#date 運算符有三個參數：分別是年、月和日。 點擊後 OK 我們得到具有正常週末日期的所需列，並在步驟 2 中刪除剩餘的列
Step 2. 將請求轉化為函數
我們的下一個任務是將針對 2020 年創建的查詢轉換為任何年份的通用函數（年份編號將作為其參數）。 為此，我們執行以下操作：
1. 展開（如果尚未展開）面板 查詢 （查詢） 在 Power Query 窗口的左側：
2. 不幸的是，在將請求轉換為函數後，查看構成請求的步驟並輕鬆編輯它們的能力消失了。 因此，將我們的請求復制一份並與它一起玩耍是有意義的，並將原件保留。 為此，請在我們的日曆請求的左窗格中右鍵單擊並選擇複製命令。
再次右鍵單擊生成的 calendar(2) 副本將選擇命令 重命名 （改名） 並輸入一個新名稱——例如，讓它成為， 年:
3. 我們使用以下命令以內部 Power Query 語言（簡稱為“M”）打開查詢源代碼 高級編輯器 選項卡 評論（查看 - 高級編輯器） 並在那裡進行一些小改動，以將我們的請求變成任何一年的函數。
它是：
後：
如果你對細節感興趣，那麼在這裡：
- （年份作為數字）=> – 我們聲明我們的函數將有一個數字參數 – 一個變量 年
- 粘貼變量 年 到網頁鏈接一步 資源. 由於 Power Query 不允許您粘合數字和文本，我們使用該函數將年份數字即時轉換為文本 數字到文本
- 我們在倒數第二步中將年份變量替換為 2020 #”添加自定義對象«，我們從片段中形成日期。
點擊後 完 我們的請求變成了一個函數：
步驟 3. 導入所有年份的日曆
剩下的最後一件事是進行最後一個主查詢，它將上傳所有可用年份的數據並將所有收到的假期日期添加到一個表中。 為了這：
1. 我們用鼠標右鍵點擊左側查詢面板灰色空白處，依次選擇 新請求 – 其他來源 – 空請求 （新查詢——來自其他來源——空白查詢）:
2. 我們需要生成一個我們將請求日曆的所有年份的列表，即 2013、2014 ... 2020。為此，在出現的空查詢的公式欄中，輸入命令：
結構體：
={編號A..編號B}
... 在 Power Query 中生成從 A 到 B 的整數列表。例如，表達式
={1..5}
... 將生成 1,2,3,4,5、XNUMX、XNUMX、XNUMX、XNUMX 的列表。
好吧，為了不拘泥於2020年，我們使用函數 日期時間.LocalNow() – Excel 函數的模擬 今天 （今天） 在 Power Query 中，然後通過函數從中提取當前年份 日期.年份.
3. 生成的年份集，雖然看起來相當充足，但並不是 Power Query 的表格，而是一個特殊的對象—— 表 （列表）. 但是將其轉換為表格不是問題：只需單擊按鈕 到餐桌 （到表） 在左上角：
4. 終點線！ 應用我們之前創建的函數 年 到生成的年份列表。 為此，在選項卡上 添加列 按下按鈕 調用自定義函數 （添加列——調用自定義函數） 並設置它的唯一參數——列 Column1 這些年來：
點擊後 OK 我們的功能 年 導入將在每年依次進行，我們將得到一個列，其中每個單元格將包含一個包含非工作日日期的表格（如果您單擊旁邊的單元格的背景，表格的內容將清晰可見這個單詞 枱燈):
通過單擊列標題中帶有雙箭頭的圖標，仍然可以擴展嵌套表的內容 蜜棗 （打鉤 使用原始列名稱作為前綴 它可以被刪除）：
…點擊後 OK 我們得到了我們想要的——從 2013 年到當年的所有假期列表：
第一個，已經不需要的列，可以刪除，第二個，設置數據類型 日期 （日期） 在列標題的下拉列表中：
查詢本身可以重命名為比 請求1 然後使用命令將結果以動態“智能”表的形式上傳到工作表 關閉並下載 選項卡 首頁 （首頁 - 關閉並加載）:
您可以通過右鍵單擊表格或通過命令在右側窗格中查詢來更新創建的日曆 更新並保存. 或使用按鈕 全部刷新 選項卡 數據 （日期 - 全部刷新） 或鍵盤快捷鍵 按Ctrl+其他+F5.
就這樣。
現在，您再也不需要浪費時間和精力來搜索和更新假期列表了——現在您有了一個“永久”的生產日曆。 無論如何，只要網站 http://xmlcalendar.ru/ 的作者支持他們的後代，我希望這將持續很長時間（再次感謝他們！）。
- 通過 Power Query 從 Internet 將比特幣匯率導入 Excel
- 使用 WORKDAY 函數查找下一個工作日
- 查找日期間隔的交集