ディレクトリ構成をExcelにしたかったら、treeでなくdirコマンド使うのもアリという話

ディレクトリ構成をExcelの一覧にしたいとき、treeでなくdirで出力するほうが扱いやすいときある

どこにどんな資料があるか把握したい
テストしてて大量のデータを大量のディレクトリに格納していって、漏れがないかチェックしたい

こんなときにいつも、treeコマンドで出力したtxtファイルからExcelで表にするのに苦労していた。

いつも「tree　Excel」とかで検索してて気づかなかったけど今日ふと「dir」コマンドが使えるってことに気づけて、大変捗った^^

同じことでいいやり方探してる人に届け～～ということで書いていく！

ディレクトリ構成をExcelの一覧にしたいとき、treeでなくdirで出力するほうが扱いやすいときある
この記事にやり方書いてること
今回の前提
１．まずdirコマンドでテキストファイルに出力
- ちなみに小ネタ：エクスプローラで開いたとこをカレントディレクトリとしてコマンドプロンプトを立ち上げる方法
- こんな感じでファイルのフルパスが並んだテキストファイルが出力されます。
２．Excelに貼り付ける
３．見たいディレクトリより上の階層はExcelの巻数substituteで消しとく
- ちなみに小ネタ
４．あとの階層分けはお好きなように
- 好みの話
  - プログラミング学んでから、Excelの式の作り方少し変えた
- 今回の場合の階層の分け方（階層ごとに文字列を抜き出していく）
  - 式
  - 見た目
縦に並んだデータを横に
- 準備
  - 式（下までフィルしてるけど省略）
  - 見た目
- 準備しといた縦の表からv-lookupで抜き出してくる
  - 式（下までフィルしてるけど省略）
  - 見た目

この記事にやり方書いてること

この記事ではこのようなことをします。

以下のような構成を。。

こうします。

構成を、記事のために見やすくtreeコマンドで出しましたがこの記事ではtreeは使いません
見やすさのために階層2が3フォルダになってますが実際には20以上など、多いときに使うほうが有用です

今回の前提

今回は以下Ａのパターンだったのでそれとして記載します。

ディレクトリ構成・そこに格納する資料名に決まりがある
一応ルールはあるけど中の資料のファイル名までは決まってない

でもExcelの関数次第で後者にも充分有用だと思う。Bなら以下の記事を参考にできそうです（そもそも、Excelの表にするのが目的ならdirコマンドのほうがいいって気づいたのもこちらの記事が発端）

Windowsフォルダのファイル名一覧をサクッと作る方法／EXCELの小ネタ - SE_BOKUのまとめノート的ブログ

１．まずdirコマンドでテキストファイルに出力

dir /s /b /a-d > xxx.txt

これだとtxtファイルがそのディレクトリ内に出力されてしまうので、例えばDドライブ直下に出力したかったら　dir /s /b /a-d > D:\xxx.txt　です。

ちなみに小ネタ：エクスプローラで開いたとこをカレントディレクトリとしてコマンドプロンプトを立ち上げる方法

別記事に書きました。いろんなときに使える小技なのでぜひ読んでみてください。

同じ形式で同じ構成のフォルダにファイル入れるようなときのtips集 - 文系のIT勉強メモ

こんな感じでファイルのフルパスが並んだテキストファイルが出力されます。

２．Excelに貼り付ける

出力されたテキストファイルの中身をCTRL＋A⇒CTRL+Cでコピーして、Excelに貼り付けます

私物PCはExcel入ってないので互換性のある別ソフトですが、汎用性のある関数しかこの記事では使わないので、表示がExcelと少し違うのは気にしないでください。

３．見たいディレクトリより上の階層はExcelの巻数substituteで消しとく

今回の例で言えば、階層1より下をチェックしたいので、それより上はsubstituteで削除( "" に置換))します。

B列に

=SUBSTITUTE(A1,"C:\Users\USER\Desktop\ここより上はチェック不要の階層\階層1_文字数異なる\","")

（txtファイルの段階で置換で消しちゃっても良いですが、チェックのとき何かしらでフルパス使う可能性があれば、フルパス残しておくと役立ちます）

ちなみに小ネタ

こういうとき、下までドラッグしないでも、セルの右隅をカチカチすると下までオートフィルされます。

後輩に何か教えてるときにその子がやってるの見て知った///
わざわざ聞いて教えてもらうとか知りたくて調べることじゃなく、想定しなかったことが知ってみたら効率アップに超重要で頻出だったりする。

そういう想定しなかったことを知る機会ってオフラインのほうが豊富なのは否めないなって思うできごとです。

４．あとの階層分けはお好きなように

ここから先はこの記事の要旨ではなく、例えばこんな風にして使います。という例にすぎないです。この先はとくに、もっといいやり方がありそう。

例えば階層2の文字数が決まっている場合、階層2の抜き出し方は

＝LEFT(B2,7)

でも文字数が不定だったら

＝LEFT(B2,FIND("\",B2)-1)

がいい。場合によって最適なやり方が異なるし、同じ結果だすのでもいろんなやり方があるし、その点において私は常に発展途上であり、最適解を出せてる気はしないです。

好みの話

上で書いた＝LEFT(B2,FIND("\",B2)-1)みたいに関数が入れ子になってると混乱するので私は分けて書くこともよくある。自分のメモだったらそれで充分だし。

1つ目の「\」は何文字目か＝FIND("\",$B2)　だけで１列使って、

その隣に＝LEFT(B2,C2-1)　みたいな感じで。

プログラミング学んでから、Excelの式の作り方少し変えた

式の中に「7」というリテラルを直接入れるのではなく、見えるところに階層２の文字列を抜き出しておいて＝len(A1)みたいに、階層２の文字数が7であることを明示して、その=len(A1)の式を入れたセルの参照にするとか

ドラッグして次の階層の式を作りやすいよう（できるだけ手打ちでなく式を作れるよう）$C2みたいに列だけ絶対参照になるようにするとか。以前は$C$1みたいな絶対参照のセル以外はそんな使ってなかった（ちなみにセルに絶対参照の$つけるのはF4キーが便利です）

今回の場合の階層の分け方（階層ごとに文字列を抜き出していく）

今回は見たい階層（第2階層）の下のフォルダ（第3階層）、その下にファイル、それでおしまい、のルールでデータを格納している

上に書いた私の好みの話で、いったんC列D列で\の位置を算出させておく

C列：1つ目の「\」は何文字目か: =FIND("\",$B2)
D列：2つ目の「\」は何文字目か: =FIND("\",$B2,C2+1)
階層2: =LEFT(B2,C2-1)
階層3: =MID(B2,C2+1,D2-C2-1)
階層4(ファイル): =RIGHT(B2,LEN(B2)-D2)

わかりにくいか？Excelの記事みててわかりづらいと思ったことないけど、みなさんまとめ方上手だったんだと実感させられる。見やすくわかりやすくまとめるの難しい。

式

今、式はこういう状態です。（下までフィルしてるけど省きます）

A列	B列	C列	D列	E列	F列	G列
元データ（フルパス）	見たい階層を抜き出し	1つ目の「\」は何文字目か	2つ目の「\」は何文字目か	階層2	階層3	ファイル
C:\Users\USER\Desktop\ここより上はチェック不要の階層\階層1_文字数異なる\階層2_001\階層3_1\aaa.xls	=SUBSTITUTE(A3,"C:\Users\USER\Desktop\ここより上はチェック不要の階層\階層1_文字数異なる\","")	=FIND("\",$B3)	=FIND("\",$B3,C3+1)	=LEFT(B3,C3-1)	=MID(B3,C3+1,D3-C3-1)	=RIGHT(B3,LEN(B3)-D3)

見た目

上のが計算されてこのように表示されてる状態（下までフィルしてるけど省きます）

A列	B列	C列	D列	E列	F列	G列
元データ（フルパス）	見たい階層を抜き出し	1つ目の「\」は何文字目か	2つ目の「\」は何文字目か	階層2	階層3	ファイル
C:\Users\USER\Desktop\ここより上はチェック不要の階層\階層1_文字数異なる\階層2_001\階層3_1\aaa.xls	階層2_001\階層3_1\aaa.xls	8	14	階層2_001	階層3_1	aaa.xls

縦に並んだデータを横に

今回は、階層2配下のそれぞれのサブフォルダにファイルが格納されているのを確認したいです。

準備

階層3_2では、同じフォルダのなかに2つのファイルが入るものもあります。それを横に並べるのに、私はvlookup用のキーになる項目を追加することにしました。これはファイル名がルール化されてる今回ならではの私の工夫なので、サラッと流します。（xlookupもつかってみたいけど使えないExcelバージョンなのでvでします）

式（下までフィルしてるけど省略）

G列	H列	I列
検索用	ファイル	検索用部品
=E3&"-"&F3&I3	=RIGHT(B3,LEN(B3)-D3)	=IF(F3="階層3_2",IF(LEN(H3)=23,"B",""),"")

見た目

G列	H列	I列
検索用	ファイル	検索用部品
階層2_001-階層3_1	aaa.xls
階層2_001-階層3_2	yyyymmddhhmmss_bbb.xls
階層2_001-階層3_2B	yyyymmddhhmmss_Bbbb.xls	B
階層2_001-階層3_3	ccc.txt
階層2_001-階層3_4	ddd.log
階層2_002-階層3_1	aaa.xls
階層2_002-階層3_2	yyyymmddhhmmss_bbb.xls
階層2_002-階層3_2B	yyyymmddhhmmss_Bbbb.xls	B
階層2_002-階層3_3	ccc.txt
階層2_002-階層3_4	ddd.log
階層2_003-階層3_1	aaa.xls
階層2_003-階層3_2	yyyymmddhhmmss_bbb.xls
階層2_003-階層3_2B	yyyymmddhhmmss_Bbbb.xls	B
階層2_003-階層3_3	ccc.txt
階層2_003-階層3_4	ddd.log

準備しといた縦の表からv-lookupで抜き出してくる

式（下までフィルしてるけど省略）

	階層3_1	階層3_2		階層3_3	階層3_4
	階層3_1	データA	データB	階層3_3	階層3_4
階層2_001	=VLOOKUP($A3&"-"&B$1,Sheet1!$G:$H,2,0)	=VLOOKUP($A3&"-"&C$1,Sheet1!$G:$H,2,0)	=VLOOKUP($A3&"-"&C$1&"B",Sheet1!$G:$H,2,0)	=VLOOKUP($A3&"-"&E$1,Sheet1!$G:$H,2,0)	=VLOOKUP($A3&"-"&F$1,Sheet1!$G:$H,2,0)

見た目

できあがりです💕

	階層3_1	階層3_2		階層3_3	階層3_4
	階層3_1	データA	データB	階層3_3	階層3_4
階層2_001	aaa.xls	yyyymmddhhmmss_bbb.xls	yyyymmddhhmmss_Bbbb.xls	ccc.txt	ddd.log
階層2_002	aaa.xls	yyyymmddhhmmss_bbb.xls	yyyymmddhhmmss_Bbbb.xls	ccc.txt	ddd.log
階層2_003	aaa.xls	yyyymmddhhmmss_bbb.xls	yyyymmddhhmmss_Bbbb.xls	ccc.txt	ddd.log