複数人で共有フォルダを利用していると、ファイルやフォルダの量が膨大になるのが通常です。
何故ならファイルやフォルダは仕事と共に増えていきますが、余程のことが無い限り減らすことはないからです。
運用を始めて1年も経てば、共有フォルダのフォルダやファイル名の情報だけでも膨大な情報量になります。
この記事は、共有フォルダに含まれるこの膨大な情報を Power Query でデータベース化して、様々な用途に活用してみようというお話しです。
会社など複数人で仕事している環境であれば共有フォルダは沢山あると思います。これらをデータベース化することにより業務の効率化が図れたり、これまでに無い高度な業務をこなせるようになる可能性があります。
データベースの用途は人により様々です。この記事で紹介する活用事例を参考にご自分なりの活用法を編み出していって頂ければと思います。
それでは早速データベース化の方法の解説からいってみましょう！
後半は作成したデータベースの活用事例もご紹介します。
共有フォルダのデータベース化手順
共有フォルダのデータベース化の手順は大きく分けて次の２ステップになります。とてもシンプルです。
- DOSコマンド で共有フォルダの情報を テキストファイルに出力
- Excel の Power Query で上記のテキストファイルを 読み込み、分析処理
それぞれについて順に解説します。
DOSコマンドで共有フォルダの情報をテキストファイルに出力
共有フォルダの情報を一括して取得するために、DOSコマンドを活用します。
コマンドプロンプトでファイル一覧を出力
DOSコマンドはコマンドプロンプトから実行できますので、まず図のようにしてコマンドプロンプトを起動します。
通常のフォルダ内の全てのファイルとフォルダの一覧を出力する場合は、以下のようにコマンドを入力して実行します。
フォルダ内の全てのファイル、フォルダを出力するコマンド
dir /s /b c:\tempこの例では “C:\temp” に含まれる全てのファイルとフォルダの一覧を出力しています。
これは更に下位のフォルダにあるファイルやフォルダも含みますので、フォルダの奥深くにあるファイルも情報が取得できます。
対象の共有フォルダをネットワークドライブ接続
しかし、共有フォルダについてはこのコマンドでは直接処理できません。そこで、対象の共有フォルダをネットワークドライブに 割り当ててからコマンドを実行します。
対象の共有フォルダをネットワークドライブに設定する手順は図のようになります。
これで DOSコマンドからネットワークドライブ経由で対象の共有フォルダにアクセスできるようになりました。
共有フォルダ内のファイル、フォルダ一覧をテキストファイルに出力
続いて以下のコマンドを入力します。すると “C:\temp\shared_files.txt” のファイルに共有フォルダのファイルとフォルダの一覧が出力されます。
dir /s /b z:\ > c:\temp\shared_files.txtこのコマンドを例えば list_files.bat という名前を付けてバッチファイルとして保存しておけばこのファイルをダブルクリックするだけで処理できるようになります。
このバッチファイルのサンプルファイルは以下のリンクからダウンロードできますので、ダウンロードしてメモ帳で開いてご自分の環境に合わせて変更して下さい。
なお、この処理はファイル、フォルダの数が多いと10分以上かかることもありますので、処理を開始したら終わるまで放っておきましょう。
Power Query によるフォルダ情報の分析
続いてコマンドで出力されたテキストファイルを Power Query で分析していきます。
具体的にはファイル、フォルダのパスの情報から必要な情報を抽出していく処理になります。
コマンド出力のファイルを取り込む
まず最初にやるのが、コマンドで出力したテキストファイルの取り込みです。
以下の手順で処理します。
- Excel のメニューから「データ」→ 「テキストまたは CSV から」 を選択
- ファイル選択ダイアログで上記でコマンドで出力したファイル（例：sheared_files.txt ）を選択
- 「インポート」ボタンを押して読み込み
- ダイアログ ウィンドウが出るが、通常はデフォルト設定のままでOK
（テキスト読み込みのダイアログウィンドウ の図参照） - ダイアログ ウィンドウの「データの変換」ボタンをクリック
- 1列目はネットワークドライブ名（ “Z” など）になっているので列を選択して DELキー を押して削除
- メニューから「列の追加」→「カスタム列」をクリック
- 下図のように設定して列を追加する
（ネットワークパス名の復元 の図参照） - “Column2” の列は不要になったので列を選択して DEL キーを押して削除
- メニューの「ホーム」→「閉じて読み込む」をプルダウン → 「閉じて次に読み込む」
- ダイアログ ウィンドウで「接続の作成のみ(O)」を選択して「OK」ボタンを押す
"\\ServerName\ShearFiles" & [Column2]
以上で “path” という列に共有フォルダ内の全てのファイル、フォルダのパス文字列が入ったクエリが作成できました。
しかし、このままではファイルをキーワード検索をしようとしても、ファイルだけでなく途中のフォルダ名も含めて検索されてしまうなど、使いにくい状態です。
そこで、次の項からはこのパスの情報から様々な情報を抽出してきます。
ファイル、フォルダ名取得
次の手順を実行します。
- Excelのメニューから「データ」→「クエリと接続」をクリックしてサイドウィンドウを出す
- 作成したクエリが表示されるのでダブルクリックして Power Query エディターを開く
- Power Query エディターのメニューから「列の追加」→「カスタム列」をクリック
- 出たウィンドウに図のようにM関数式を入力して「OK」ボタンを押す
List.Last(Text.Split([path],"\"))これで名称という列にファイル、フォルダの名称のみが抽出されて入りました。
ファイル拡張子取得
続いてはファイルの拡張子を取得します。次の手順を実行します。
- Power Query エディターのメニューから「列の追加」→「カスタム列」をクリック
- 出たウィンドウに図のようにM関数式を入力して「OK」ボタンを押す
if Text.Contains([名称], ".")
then Text.AfterDelimiter([名称], ".", {0, RelativePosition.FromEnd})
else null親フォルダ名取得
次は親フォルダ名の取得です。同様に次の手順を実行します。
- Power Query エディターのメニューから「列の追加」→「カスタム列」をクリック
- 出たウィンドウに図のようにM関数式を入力して「OK」ボタンを押す
List.Last(List.RemoveLastN(Text.Split([path],"\"),1))フォルダ階層深さの取得
共有フォルダ直下の階層を1として、その下のフォルダの階層を２、その下は３と順々に階層の深さを数値にします。
これをすることで、例えばある深さの階層にあるフォルダのみを抽出するなどの処理が可能になります。
これまでと同様にカスタム列を追加して M関数式を記入します。
- Power Query エディターのメニューから「列の追加」→「カスタム列」をクリック
- 出たウィンドウに図のようにM関数式を入力して「OK」ボタンを押す
Text.Length([path]) - Text.Length(Text.Remove([path], "\")) - 3クエリの活用事例
ここまでの処理で共有フォルダの情報から ファイル、フォルダの名称、拡張子、親フォルダ名、階層深さ の情報の取得をすることができました。
このクエリの活用事例をご紹介します。
ファイル検索
最初はファイル検索です。共有フォルダのフォルダ構成も膨大になると特定のファイルを探すのも一苦労です。しかし、作成したクエリは共有フォルダ内にある全てのファイルの情報が入っているのでこの中を検索すれば良いのです。
クエリを作成した際に「接続の作成のみ」を選択して、クエリの出力はシートに表示されていないですが、これをテーブルに出力します。
- Excelのメニューから「データ」→「クエリと接続」をクリックしてサイドウィンドウを表示
- 作成したクエリを右クリック
- メニューが出るので「読み込み先」を選択
- 小ウィンドウで「テーブル」、「新規ワークシート」を選択して「OK」ボタンを押す
すると、新しいシートが作成されて、ファイル一覧のテーブルが表示されます。
ここでは簡単にテーブルの検索機能を活用してファイル名にキーワードを含むものを検索します。
拡張子の列も例えば “xlsx” でフィルターを掛けて、ファイル名にキーワードを含んだ Excel ファイル を検索することも可能です。
資料台帳の作成
フォルダ名に一定のルールを決めておくと後で有効に活用できます。
例えば ABC社の見積資料をフォルダに保管しているとして、図のようなルールを決めておくとします。
そうすると、前半で作成したクエリから次の手順で新たなクエリを作成することで見積資料の一覧リストを自動で作成できます。
- 前半で作成したクエリを右クリックして「参照」を選択
- 「名称」列をテキスト フィルターで「指定の値で始まる」を選択
- “ABC見積” をキーワードとして入力
- 「拡張子」列をフィルターで「（空白セル）」を選択してフォルダーに絞り込む
- Power Query エディターのメニューから「変換」→「列の分割」→「区切り文字による分割」を選択
- 区切り文字に “_”（アンダーバー）を入力して、分割オプションは「一番左の区切り記号」を選択して「OK」ボタンを押す
- Power Query エディターのメニューから「ホーム」→「閉じて読み込む」をクリック
すると、”ABC見積” が頭につくフォルダから資料番号と資料名称のリストが自動で作成されます。
数値集計
前項の ABC社の見積資料と同様に、今度は XYZ社 の受注資料の管理フォルダを例に数値集計を行います。
フォルダ名のルールを図のように設定します。
これも前半で作成したクエリから次の手順で新たなクエリを作成すると数値集計が可能になります。
日付を抽出（できない場合はnullを返す）
受注資料の管理フォルダのルールでは名称の頭の８文字で年月日を記載することにしました。
しかし、全てのフォルダがこのルールが適用されている訳では無いので、頭の８文字が年月日を表していないフォルダも多くあると思います。
そこで以下のように少し工夫した M関数式 を作成します。
- 前半で作成したクエリを右クリックして「参照」を選択
- Power Query エディターのメニューから「列の追加」→「カスタム列」をクリック
- 出たウィンドウに図のようにM関数式を入力して「OK」ボタンを押す
try Date.FromText(Text.Middle([名称], 0, 8)) otherwise nullここで、try…otherwise という構文を利用することで、もし try の後の M関数式が処理できなかった場合は null を返すという指示を出すことができます。
数値を抽出
ルールでは “【】”（隅つきカッコ）の中に金額を記入することにしました。
ここから数値を抽出する場合は、以下のように処理します。
- Power Query エディター「名称」列を選択
- Power Query エディターのメニューから「列の追加」→「重複する列」（「名称」列のコピーを作成）
- 追加された列の名称を「受注額」に変更
- 「受注額」の列を選択
- Power Query エディターのメニューから「変換」→「抽出」→「区切り記号の間のテキスト」
- 出てきたダイアログウィンドウに図のように【】を入力して「OK」ボタンを押す
- 「受注額」の列のデータ型を数値に変更
この処理は Power Query エディターの GUI を使って処理することができました。
フィルターで受注管理データに絞り込み
ここまでで受注の日付と受注金額を抽出することができました。しかし、現状では受注管理フォルダ以外の情報も全部クエリ出力に含まれています。
受注管理以外のものは日付も受注金額も null になっていますので、これをフィルター機能でチェックを外します。
すると、日付と受注金額が入ったデータのみが出力されます。
これをデータモデルに出力して、Power Pivot を使って集計すれば年、月、日ごとの受注額を集計できます。
このように、わざわざ Excel の表に記入することなくフォルダ管理だけで数値を集計することが可能になります。
まとめ
共有フォルダ内のファイルやフォルダの一覧を取得してデータベース化する方法と、そのデータベースの活用事例をご紹介しました。
共有フォルダに含まれる膨大な情報を上手に活用することで様々な業務効率化が図れる可能性があります。
特に、ファイルやフォルダ名に一定のルールを設定することで、その名称から必要な情報を抽出して活用ができます。
ファイルやフォルダ名に記入した情報を同じように別の Excel ファイルに転記する無駄作業が多くあります。この記事でご紹介した仕組みを活用すれば、この無駄作業を無くすことができます。
その他にも色々とアイディアを出せば様々な活用法が見つかりそうです。
会社や組織では必ずあると言って良い共有フォルダ、皆さんも上手に活用して業務パフォーマンスを向上させてみませんか？
