この人生における多くのビジネス プロセス (およびビジネス全体でさえ) は、指定された期限までに限られた数の実行者による注文の履行を伴います。 そのような場合の計画は、彼らが言うように、「カレンダーから」行われ、多くの場合、計画されたイベント (注文、会議、配達) を Microsoft Excel に転送する必要があります。数式、ピボット テーブル、チャート、等
もちろん、私はそのような転送を愚かなコピーではなく (難しいことではありません)、データの自動更新で実装したいと考えています。エクセル。 2016 バージョン以降、Microsoft Excel に組み込まれている Power Query アドインを使用して、このようなインポートを数分で実装できます (Excel 2010 ～ 2013 の場合、Microsoft の Web サイトからダウンロードして、リンクから個別にインストールできます)。 .
計画に無料の Google カレンダーを使用するとします。このカレンダーでは、便宜上、別のカレンダーを作成しました (右下隅の横にあるプラス記号のボタン)。 他のカレンダー) タイトル付き 仕事. ここでは、完了して顧客の住所に配送する必要があるすべての注文を入力します。
注文をダブルクリックすると、その詳細を表示または編集できます。
ご了承ください：
- イベント名は マネージャーこの命令を遂行する者（エレナ）と 注文番号
- 示されている 住所 配達
- 注記には、(別の行に任意の順序で) 注文パラメーターが含まれています: 支払いの種類、金額、顧客名などの形式で パラメータ=値.
わかりやすくするために、各マネージャーの注文は独自の色で強調表示されていますが、これは必須ではありません。
ステップ 1. Google カレンダーへのリンクを取得する
まず、注文カレンダーへの Web リンクを取得する必要があります。 これを行うには、XNUMX つのドットのボタンをクリックします。 カレンダーのオプション カレンダーの名前の横にある をクリックし、コマンドを選択します 設定と共有:
開いたウィンドウで、必要に応じて、カレンダーを公開したり、個々のユーザーがカレンダーにアクセスできるようにしたりできます。 iCal 形式のカレンダーへのプライベート アクセス用のリンクも必要です。
ステップ 2. カレンダーから Power Query にデータを読み込む
Excelを開いてタブで 且つ (Excel 2010-2013 を使用している場合は、タブで パワークエリ) コマンドを選択 インターネットから (データ — インターネットから). 次に、コピーしたパスをカレンダーに貼り付けて、[OK] をクリックします。
iCal Power Query はフォーマットを認識しませんが、簡単に解決できます。 基本的に、iCal は区切り文字としてコロンを使用したプレーン テキスト ファイルであり、その内部は次のようになります。
したがって、ダウンロードしたファイルのアイコンを右クリックして、意味に最も近い形式を選択するだけです CSV – すべての注文に関するデータが Power Query クエリ エディターに読み込まれ、コロンで XNUMX つの列に分割されます。
よく見ると、次のことがはっきりとわかります。
- 各イベント (注文) に関する情報は、BEGIN という単語で始まり END で終わるブロックにグループ化されます。
- 開始日時と終了日時は、DTSTART および DTEND というラベルの付いた文字列に格納されます。
- 配送先住所はLOCATIONです。
- 注文メモ - 説明フィールド。
- イベント名 (マネージャー名と注文番号) — 概要フィールド。
この有用な情報を抽出し、便利なテーブルに変換する必要があります。
ステップ 3. 通常表示に変換する
これを行うには、次の一連のアクションを実行します。
- 最初の BEGIN コマンドの前に、不要な上位 7 行を削除しましょう。 ホーム — 行の削除 — 上位行の削除 (ホーム — 行を削除 — 一番上の行を削除).
- 列でフィルタ Column1 必要なフィールドを含む行: DTSTART、DTEND、DESCRIPTION、LOCATION、SUMMARY。
- [詳細設定]タブ 列の追加 選ぶ 索引欄 (列の追加 — インデックス列)データに行番号列を追加します。
- タブのすぐそこにあります。 列の追加 チームを選ぶ 条件付き列 (列を追加 — 条件付き列) 各ブロック (順序) の先頭に、インデックスの値を表示します。
- 結果の列の空のセルに入力します ブロックタイトルを右クリックしてコマンドを選択する フィル - ダウン (フィル - ダウン).
- 不要な列を削除 インデックス.
- 列を選択 Column1 列からのデータの畳み込みを実行します Column2 コマンドを使用する 変換 – ピボット列 (変換 — ピボット列). 必ずオプションで選択してください 集約しない (集計しないでください)データに数学関数が適用されないようにします。
- 結果の XNUMX 次元 (クロス) テーブルで、住所列のバックスラッシュをクリアします (列ヘッダーを右クリックします – 値の置き換え）不要な列を削除します ブロック.
- 列の内容を回転するには DTSTART и 確定 完全な日時で、それらを強調表示し、タブで選択します 変換 – 日付 – 分析の実行 (変換 — 日付 — 解析). 次に、関数を置き換えて数式バーのコードを修正します デートから on 日時.From時間の値を失わないように:
- 次に、ヘッダーを右クリックして、列を分割します DESCRIPTION 区切り記号による順序パラメータ付き – 記号 n、しかし同時に、パラメーターでは、列ではなく行への分割を選択します。
- もう一度、結果の列を XNUMX つの別々の列 (パラメーターと値) に分割しますが、等号によって分割します。
- 列の選択 説明.1 前に行ったように、コマンドを使用して畳み込みを実行します 変換 – ピボット列 (変換 — ピボット列). この場合の値列は、パラメーター値を持つ列になります - 説明.2 パラメータで関数を選択してください 集約しない (集計しないでください):
- すべての列の形式を設定し、必要に応じて名前を変更する必要があります。 コマンドを使用して、結果を Excel にアップロードすることができます。 ホーム — 閉じてロード — 閉じてロード… (ホーム — クローズ & ロード — クローズ & ロード…)
Google カレンダーから Excel に読み込まれた注文のリストは次のとおりです。
将来、カレンダーに新しい注文を変更または追加するときは、コマンドでリクエストを更新するだけで十分です データ – すべて更新 (データ — すべて更新).
- Power Query 経由でインターネットから更新された Excel の工場カレンダー
- 列をテーブルに変換する
- Excel でデータベースを作成する