異なる予算で複数のプロジェクトを実行していて、それぞれのコストを視覚化したいとします。 つまり、次のソース テーブルから:
.. 次のようなものを取得します。
つまり、予算を各プロジェクトの日数に分散し、プロジェクト ガント チャートの簡易バージョンを取得する必要があります。 これを手作業で行うのは長くて退屈で、マクロは難しいですが、このような状況で Power Query for Excel が威力を発揮します。
パワークエリ は Microsoft のアドオンで、ほぼすべてのソースからデータを Excel にインポートし、さまざまな方法でデータを変換できます。 Excel 2016 では、このアドインは既定で既に組み込まれています。Excel 2010 ～ 2013 では、Microsoft の Web サイトからダウンロードして、PC にインストールできます。
まず、コマンドを選択して、元のテーブルを「スマート」テーブルに変えましょう。 表としてフォーマットする タブ ホーム (ホーム — テーブルとしてフォーマット) または、キーボード ショートカットを押して Ctrlキー+T :
次に、タブに移動します 且つ (Excel 2016 を使用している場合) またはタブで パワークエリ (Excel 2010-2013 を使用していて、Power Query を別のアドインとしてインストールした場合)、[テーブルから/範囲] ボタンをクリックします。 :
スマート テーブルが Power Query クエリ エディターに読み込まれます。最初の手順は、テーブル ヘッダーのドロップダウンを使用して各列の数値形式を設定することです。
XNUMX 日あたりの予算を計算するには、各プロジェクトの期間を計算する必要があります。 これを行うには、選択します（キーを押したままにします Ctrlキー) 列が最初 終了、次いで 開始 そしてチームを選択 列を追加 – 日付 – 日数を減算 (列の追加 — 日付 — 日数の減算):
各プロジェクトは初日の朝に開始し、最終日の夕方に終了することになっているため、結果の数値は必要以上に 1 少なくなります。 したがって、結果の列を選択し、コマンドを使用してユニットを追加します 変換 – 標準 – 追加 (変換 — 標準 — 追加):
それでは、XNUMX 日あたりの予算を計算する列を追加しましょう。 これを行うには、タブで 列の追加 私は遊びません カスタム列 (カスタム列) 表示されるウィンドウで、リストの列の名前を使用して、新しいフィールドの名前と計算式を入力します。
ここで最も微妙な点として、開始から終了までの日付のリストを 1 日刻みで表示する別の計算列を作成します。 これを行うには、もう一度ボタンを押します カスタム列 (カスタム列) と呼ばれる組み込みの Power Query 言語 M を使用します。 リスト.日付:
この関数には XNUMX つの引数があります。
- 開始日 - この例では、列から取得されます 開始
- 生成される日付の数 - この場合、これは各プロジェクトの日数であり、列の前半で数えました 引き算
- 時間ステップ – 設計により設定 #期間(1,0,0,0)、M の言語での意味 – XNUMX 日、XNUMX 時間、XNUMX 分、XNUMX 秒。
をクリックした後 OK 日付のリスト (List) を取得します。これは、テーブル ヘッダーのボタンを使用して新しい行に展開できます。
…そして、次の結果が得られます。
あとは、生成された日付を新しい列の名前として使用して、テーブルを折りたたむだけです。 チームはこれに責任があります。 詳細列 (ピボットコラム) タブ 変換 （変身）:
をクリックした後 OK 目的の結果に非常に近い結果が得られます。
この場合、Null は Excel の空のセルに相当します。
コマンドを使用して、不要な列を削除し、結果のテーブルを元のデータの横にアンロードする必要があります。 閉じてロード – 閉じてロード… (クローズ & ロード — クローズ & ロード…) タブ ホーム （ホーム）:
結果として次のようになります。
見栄えを良くするために、タブ上の結果のスマート テーブルの外観をカスタマイズできます。 コンストラクタ （設計）: 単一の色スタイルを設定し、フィルター ボタンを無効にし、合計を有効にします。さらに、タブの条件付き書式を使用して、日付を含むテーブルを選択し、数値の強調表示を有効にすることができます。 ホーム — 条件付き書式 — カラースケール (ホーム — 条件付き書式 — カラー スケール):
そして最良の部分は、将来、古いプロジェクトを安全に編集したり、元のテーブルに新しいプロジェクトを追加したりできることです。その後、マウスの右ボタンで日付を使用して適切なテーブルを更新します。Power Query は、実行したすべてのアクションを自動的に繰り返します。 .
ほら！
- 条件付き書式を使用した Excel のガント チャート
- プロジェクト マイルストーン カレンダー
- Power Query で重複行を生成する