谷歌日曆和 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 中創建數據庫

發表評論