【Excel】渡された工数入力シートが使いづらかったので最適化してみた（工数集計）

こんにちは、朱夏です。

それでは、最後にすべての工数を集計するファイルを作っていきたいと思います。
集計にはPowerクエリを使用します。

Powerクエリはoffice2010以降かMicrosoft365のExcel出ないと使用できません。

昔は複数の表を一つにまとめるときとかはマクロを使ったりとかして試行錯誤してたのですが、かなり便利になりましたね。

それではいってみましょう。

事前準備

事前準備として、フォルダ構成とダミーの個人用工数ファイルを作成しておきます。
工数管理フォルダ内に月ごとのフォルダ、その中にテンプレートファイルと全体集計用ファイル、個人用の工数入力ファイルを格納するフォルダーを用意します。
個人用ファイル格納フォルダーの中にはダミーで２つほどファイルを作成しておきました。

フォルダ構成はこんな感じ。

treeコマンド：コマンドプロンプトで対象フォルダのツリー構成を表示させることができるコマンドです。
/fオプションでファイル名まで表示させることができます。

全体集計用ファイル

工数管理フォルダ内の毎月のフォルダ（例では202306月フォルダ）内に[全体集計用.xlsx]ファイルを作成します。

ここに個人で入力してもらった工数を集めて表示させるため、Powerクエリを使います。
手順はこんな感じ。

Powerクエリのデータ取得手順

[データ]タブ　＞　[データの取得]　＞　[ファイルから]　＞　[フォルダーから]　を選択
集計したいファイルが保存されたフォルダー（個人ファイル格納フォルダー）を選択して開く
[結合]　＞　[データの結合と変換]　を選択
一番上の[パラメーター～]　＞　[OK]ボタン　をクリック
エディター画面が表示されます。
Name列から以下の不要なシートをフィルター
・【テンプレート】工数シートYYYYMMDD
・プルダウン
・勤怠表
右側の　[クエリの設定]　＞　[適用したステップ]　＞　[フィルターされた行]　の歯車を選択
フィルター条件の詳細を以下の様に設定して　[OK]ボタン　をクリック
[詳細設定]にチェック
Name　指定の値と等しくない　【テンプレート】工数シートYYYYMMDD
および　Name　指定の値と等しくない　プルダウン
および　Name　指定の値と等しくない　勤怠表
※[句の追加]ボタンで条件の行を追加できます。
[Data]列　の右端にある　　をクリック　＞　[OK]ボタン　をクリック
各シートの情報が読み込まれます。
[Source.Name]、[Name]、[Data.Column1]列　を削除
[Item]、[Kind]、[Hidden]列　を削除
[Data.Column5]列　の　(null)　をフィルター
※フィルター条件が以下の様になっていることを確認
　をクリック　＞　[1行目をヘッダーとして使用]　を選択
[日付]列　から余分な”日付”をフィルター
※フィルター条件が以下になっていることを確認
[ホーム]　＞　[閉じて読み込む]　＞　[閉じて次に読み込む…]　を選択
以下を設定して　[OK]ボタン　をクリック
[テーブル]　にチェック
[既存のワークシート]　にチェック
“=$A$1″　を指定
集計されたデータが表示されます。

相対パスの指定

Powerクエリの惜しいところの1つとして、読み込みファイルを相対パスで指定できないというのがあります。
これだとフォルダの置き場所を変更したり、新しい月にフォルダを追加したりするときに不便です。

というわけで、相対パスで読み込みができる様にしていきます。

初めに、相対パスを取得するための別シートを用意します。
ついでに、Powerクエリでデータを読み込んでいるシートは「集計用」という名前を付けてました。

やることは3つです。

①CELL関数でフォルダパスを取得
②セルに名前を付ける
③Powerクエリの内容の書き換え

順番にやっていきます。

①CELL関数でフォルダパスを取得

[パス]シートのA1セルに以下のパスを入力して[全体集計用.xlsx]ファイルが保存されているフォルダまでのパスを取得します。

=LEFT(CELL(“filename”,$A$1),FIND(“[“,CELL(“filename”,$A$1))-1)

②セルに名前を付ける

A1セルを選択した状態で、[数式]　＞　[名前の管理]　を選択します。

[新規作成]ボタン　をクリック

[名前]欄に任意の名前（今回は”FilePath”）を入力

A1を選択した時に、左上に[FilePath]と表示されるようになっていればOKです。

③Powerクエリの内容の書き換え

最後に、Powerクエリの内容を書き換えます。
[集計用]シートの読み込んだデータのどこかを選択した状態だと、リボンに[クエリ]タブが表示されるので選択します。

そして[編集]をクリック

Powerクエリのエディターが表示されたら、更に詳細エディターを開きます。

詳細エディターが開きます。
修正内容は1行追加、1行修正です。

letのすぐ下に1行追加
ファイルパス = Excel.CurrentWorkbook(){[Name=”FilePath”]}[Content]{0}[Column1],ソースの行の内容を修正
ソース = Folder.Files(ファイルパス & “個人ファイル格納フォルダー”),

【修正前】