"BOKU"のITな日常

還暦越えの文系システムエンジニアの”BOKU”は新しいことが大好きです。

Windowsのフォルダのファイル名だけの一覧をサクッと作る方法/EXCELの小ネタ

WindowsコマンドプロンプトでDirコマンドを使って指定フォルダ以下のファイルの一覧を取得する方法と、取得したフルパスの文字列から「ファイル名」だけを抜き出す方法をやります。

EXCELですが、VBAは使わず、関数+手作業でやります。

EXCEL

 

この技の利用シーン

 

自分の場合は、主に仕事で使います。

よくあるのが。

提出する資料(成果物)を作るために、共有フォルダのプロジェクトフォルダ以下にある設計書とかマニュアルとかのファイル一覧を作ったりするパターンです。

最初のうちは管理表を更新してたけど、いつのまにかきちんとされなくなって正しいかどうかわからなくなったから、フォルダに実際にあるファイル名を拾って作り直そう・・みたいなこともあります(笑)。

 

ファイル名だけの一覧表を作る手順

 

流れはごくシンプルです。

  • 指定フォルダ以下のフルパスのファイル名一覧をテキストデータで取得する
  • 上記データをEXCELに貼り付け、関数でファイル名だけを取り出す。
  • そのファイル名だけを使って一覧表を作る

 

指定フォルダ以下のフルパスのファイル名一覧をテキストデータで取得する

 

例として「Neural Network Console 1.4.0」のサンプルプロジェクトの一覧表を作ってみます。

パスは「C:\neural_network_console_140\samples\sample_project」としときます。

f:id:arakan_no_boku:20190224011821j:plain

 

コマンドプロンプトWindowsシステムツールの下とかにあります)を立ち上げて以下のコマンドを打ちます。

cd C:\neural_network_console_140\samples\sample_project

もちろん、実際は「C:\neural_network_console_140\samples\sample_project」の部分は、その環境のパスですけどね。

そうすると、そのフォルダが「カレント」になります。

f:id:arakan_no_boku:20190224012252j:plain

そこで、以下のコマンドを実行します。

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

こうすると、「C:\neural_network_console_140\samples\sample_project」以下のすべてのファイルとフォルダが、tmp.txtにフルパスで出力されます。

f:id:arakan_no_boku:20190224012720j:plain

 

上記データをEXCELに貼り付け、関数でファイル名だけを取り出す

 

あとは上記テキストファイルをEXCELに貼り付けて、関数使ってファイル名だけとりだせばよい・・。

口で言うと簡単ですが、フォルダの階層の深さもフォルダ名の長さもばらばらのデータなので一筋縄ではいきません。

ちょっと、小ネタ的な技が必要になります。

まず、EXCELを開いて、上記テキストファイルをA列に貼り付けてやります。

f:id:arakan_no_boku:20190224200425j:plain

ここでB1セルに以下の計算式を貼り付けてやります。

=MID(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),1)+1,999)

 これが「A1セルのフルパスの文字列からファイル名だけを抜き出す」計算式・・今回の小ネタ・・です。

f:id:arakan_no_boku:20190224201004j:plain

後は、B1セルをパーッとコピーしてやればファイル名だけの一覧がB列にできるので、フィルターで「sdcproj」だけを抜き取って、どっかにコピーしたら、サンプルプロジェクトの一覧ができる・・というわけです。

f:id:arakan_no_boku:20190224201628j:plain

 

さっきの計算式の解説です

 

=MID(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),1)+1,999)

パスの区切り文字は「\」です。

だから、A1セルからファイル名だけを抜き出すには、「最後の\が出現する位置の次の文字から最後まで」を表示してやればよいことになります。

そういう動きをするのはMID関数ですから、MID(A1、「最後の\が出現する位置の次」,999)と書いているわけです。

位置の次は「+1」でいいわけですから、難しいのは「最後の\が出現する位置」です。

上記の関数では、これを以下の2段階でやってます。

① SUBSTITUTE関数で、A1セルのパスの最後の「\」だけを「@」に置き換える。

② FIND関数でA1セルのパスの中の「@」の位置を求める

つまり。

トリッキーなのは①の最後の「\」だけを「@」に置き換える部分です。

SUBSTITUTE関数は以下のような引数を持ってます。

SUBSTITUTE(文字列, 検索文字列,置換文字列, 置換対象)

この最後の「置換対象」が面白くて、ここに「3」とか指定すると、置き換え対象が3つあった時に「3つ目だけを置き換える」ということができます。

これを使うと。

「¥」がその文字列に何個出現するかを数えて、その数字を置換対象にすれば「最後に出現する¥だけを@に置き換える」という芸当ができてしまいます。

それがわかれば、「LEN(A1)-LEN(SUBSTITUTE(A1,"\","")」の意味がわかります。

LEN(A1)のA1セル全体の文字数から、\をすべて”””・・つまり空文字に置き換えた文字数「LEN(SUBSTITUTE(A1,"\","")」を引けば、「\」が何個出現するのかがわかる。

まあ、そういう理屈です。

 

こんな応用もできます。

 

計算式をこんな感じに変更します。

=LEFT(A1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))),1))

すると。

同じような理屈で「ファイル名以外」を抜き出すことができます。

これをC列にコピーしていくと、A列のパスをB列の「ファイル名」とC列の「パスのみ」に分解できたりします。

f:id:arakan_no_boku:20190224205430j:plain

ちょっと便利ですよ。

ではでは。