谷歌日历和 Excel 的订单跟踪系统

今生的许多业务流程（甚至整个业务）都涉及到在给定的截止日期前由有限数量的执行者完成订单。 正如他们所说，在这种情况下进行计划是“从日历中”进行的，并且通常需要将其中计划的事件（订单、会议、交付）传输到 Microsoft Excel——以便通过公式、数据透视表、图表进行进一步分析，等等

当然，我想实现这样的转移不是通过愚蠢的复制（这并不困难），而是通过自动更新数据，以便将来对日历和新订单进行的所有更改都将显示在Excel。 从 2016 版本开始，您可以使用 Microsoft Excel 中内置的 Power Query 加载项在几分钟内实现此类导入（对于 Excel 2010-2013，可以从 Microsoft 网站下载并通过链接单独安装） .

假设我们使用免费的谷歌日历进行计划，为了方便，我在其中创建了一个单独的日历（右下角旁边的加号按钮 其他日历) 与标题 工作. 在这里，我们输入所有需要完成并交付给客户地址的订单：

通过双击任何订单，您可以查看或编辑其详细信息：

注意：

  • 活动名称是 经理谁履行了此订单（Elena）和 订单号
  • 已指示 地址 交货
  • 注释包含（在单独的行中，但以任何顺序）订单参数：付款类型、金额、客户名称等，格式为 参数=值.

为清楚起见，每个经理的命令都以自己的颜色突出显示，但这不是必需的。

第 1 步：获取 Google 日历的链接

首先，我们需要获得一个指向我们的订单日历的网络链接。 为此，请单击带有三个点的按钮 日历选项工作 在日历名称旁边，然后选择命令 设置和共享:

在打开的窗口中，如果需要，您可以将日历设为公开或向个人用户开放对它的访问。 我们还需要一个链接以访问 iCal 格式的日历：

步骤 2. 将日历中的数据加载到 Power Query

现在打开 Excel 并在选项卡上 时间 （如果您有 Excel 2010-2013，则在选项卡上 电源查询) 选择一个命令 来自网络 （数据——来自互联网）. 然后将复制的路径粘贴到日历并单击确定。

iCal Power Query 无法识别格式，但很容易提供帮助。 本质上，iCal 是一个以冒号作为分隔符的纯文本文件，它的内部看起来像这样：

因此，您只需右键单击下载文件的图标并选择含义最接近的格式 CSV – 我们关于所有订单的数据将被加载到 Power Query 查询编辑器中，并用冒号分为两列：

如果你仔细观察，你可以清楚地看到：

  • 关于每个事件（订单）的信息被分组到一个以单词 BEGIN 开头并以 END 结尾的块中。
  • 开始和结束日期时间存储在标记为 DTSTART 和 DTEND 的字符串中。
  • 送货地址是 LOCATION。
  • 订单注释 - 描述字段。
  • 事件名称（经理姓名和订单号）— 摘要字段。

剩下的就是提取这些有用的信息并将其转换为一个方便的表格。 

步骤 3. 转换为普通视图

为此，请执行以下操作链：

  1. 让我们删除第一个 BEGIN 命令之前不需要的前 7 行 主页 — 删除行 — 删除顶部行 （首页 - 删除行 - 删除顶部行）.
  2. 按列过滤 Column1 包含我们需要的字段的行：DTSTART、DTEND、DESCRIPTION、LOCATION 和 Summary。
  3. 在高级选项卡上 添加列 选择 索引栏 （添加列 - 索引列）为我们的数据添加行号列。
  4. 就在标签上。 添加列 选择一个团队 条件列 （添加列 - 条件列） 在每个块（订单）的开头，我们显示索引的值：
  5. 填写结果列中的空单元格 阻止通过右键单击其标题并选择命令 填充 - 向下 （填充 - 向下）.
  6. 删除不必要的列 Index.
  7. 选择一列 Column1 并对列中的数据进行卷积 Column2 使用命令 变换 - 枢轴柱 （变换——透视列）. 一定要在选项中选择 不要聚合 （不要聚合）这样就不会对数据应用数学函数：
  8. 在生成的二维（交叉）表中，清除地址列中的反斜杠（右键单击列标题 - 替换值) 并删除不必要的列 阻止.
  9. 翻转列的内容 开始 и 达腾 在完整的日期时间，突出显示它们，在选项卡上选择 转换 - 日期 - 运行分析 （变换——日期——解析）. 然后我们通过替换函数来更正公式栏中的代码 日期.从 on 日期时间.从以免丢失时间值：
  10. 然后，通过右键单击标题，我们拆分列 商品描述 带有分隔符的订单参数 - 符号 n，但同时，在参数中，我们将选择划分为行，而不是划分为列：
  11. 再一次，我们将结果列分成两个独立的列——参数和值，但使用等号。
  12. 选择列 说明.1 像我们之前所做的那样，使用命令执行卷积 变换 - 枢轴柱 （变换——透视列）. 在这种情况下的值列将是具有参数值的列​​​ - 说明.2  一定要在参数中选择一个函数 不要聚合 （不要聚合）:
  13. 仍然需要为所有列设置格式并根据需要重命名它们。 您可以使用命令将结果上传回 Excel 主页 — 关闭并加载 — 关闭并加载... （首页 — 关闭并加载 — 关闭并加载到…）

这是我们从 Google 日历加载到 Excel 中的订单列表：

将来，当更改或添加新订单到日历时，只需使用命令更新我们的请求即可 数据 – 全部刷新 （数据——全部刷新）.

  • Excel 中的工厂日历通过 Power Query 从 Internet 更新
  • 将列转换为表
  • 在 Excel 中创建数据库

发表评论