生产日历，即日期列表，所有官方工作日和节假日都相应标记——对于任何 Microsoft Excel 用户来说都是绝对必要的。 在实践中，你不能没有它：
- 在会计计算中（工资、服务年限、假期……）
- 在物流方面——为了正确确定交货时间，考虑到周末和节假日（还记得经典的“节后来吗？”）
- 在项目管理中——为了正确估计条款，再次考虑到工作日和非工作日
- 任何使用功能，如 WORKDAY （工作日） 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 函数查找下一个工作日
- 查找日期间隔的交集