ls -lの結果からExcel関数を使ってファイル名とディレクトリ名とyyyy/mm/ddを取り出す

UNIX/Linuxサーバ等で取得したファイル情報を、使いやすい形に加工したい！
そんな時に使えるエクセルの関数です。

たとえば、UNIXサーバで「ls -l」を実行した結果、
下記のようなデータを取得し、Excelに貼り付けたとします。

アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log

エクセルの関数を使って、以下のようにファイル名、ディレクトリ名、日付（yyyy/mm/dd）を取得しましょう。
関数なので、マクロ等のスクリプト不要です。

アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス        ファイル名     ディレクトリ名   更新日時
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log   test.log    /val/log/   2016/12/21

　※セルは、A1セルから記載していると仮定します。
　※Linuxの場合、表示形式が少し違うので、修正が必要です。

それでは、さっそく見ていきましょう！

更新日時をyyyy/mm/ddで取得する

=IF(ISERROR(DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)))=TRUE,DATEVALUE(CONCATENATE(E1,"-",F1)),DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)))

これで、「2016/12/21」　という形で取得できます。

アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス        更新日時
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log   2016/12/21

解説

1. CONCATENATE(F1,"-",E1,"-",G1)　／　CONCATENATE(E1,"-",F1)

CONCATENATEは文字列の結合です。& でつなげるのと同義です。
CONCATENATE(F1,"-",E1,"-",G1)は、
「21−Dec-0.44779...」になります。
CONCATENATE(E1,"-",F1)は、
「Dec-21」になります。

　※時間はシリアル値に変更されていますので、10:45が0.4479...になっています。

2. DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1))　／　DATEVALUE(CONCATENATE(E1,"-",F1))

DATEVALUEは、日付を表す文字列を日付のシリアル値に変換します。
DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1))
は、「#VALUE!」
DATEVALUE(CONCATENATE(E1,"-",F1))
は、「42725（書式設定で日付に変換すると2016/12/21）」
になります。
前者が「#VALUE!」になるのは、年が入る場所に時間が入っているためエラーになっています。
後者は年を省いていますので、直近の年になっています。

3.IF(ISERROR(DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)))=TRUE,DATEVALUE(CONCATENATE(E1,"-",F1)),DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1)))

これで仕上げです！
ISERRORは、対象がエラーの時にTRUEを返します。
つまり、2.で出てきた「#VALUE!」を判定できます。
VALUE!等のエラーが出た場合は、DATEVALUE(CONCATENATE(E1,"-",F1))
出なかった場合は、DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1))
といった感じに、結果を分岐して表示します。
今回は、年が時間になっていてエラーなので、DATEVALUE(CONCATENATE(E1,"-",F1))の結果「42725（書式設定で日付に変換すると2016/12/21）」を表示します。
エラーにならないケース（年が入っているケース）では、DATEVALUE(CONCATENATE(F1,"-",E1,"-",G1))を使用して表示します。

ファイル名を取得する

=TRIM(RIGHT(SUBSTITUTE(H1,"/",REPT(" ",100)),100))

これで、「test.log」が取得できます。

アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス        ファイル名     更新日時
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log   test.log    2016/12/21

解説

1. REPT(" ",100)

まずはこれ。空白を100回繰り返します。理由は後述します。

2. SUBSTITUTE(H1,"/",REPT(" ",100))

H1（/val/log/test.log）を置き換えます。
"/"を探して、空白100個に置き換えます。
下記のような状態になります。
「　　　　　var　　　　　log　　　　　test.log」（空白数はイメージです。実際は100個です。）

3. RIGHT(SUBSTITUTE(H1,"/",REPT(" ",100)),100)

右から100文字取得します。
「　　　　　　　　test.log」
こんな状態になります。92個の空白と、test.logで100文字です。

4. TRIM(RIGHT(SUBSTITUTE(H1,"/",REPT(" ",100)),100))

TRIMは、スペースを一括削除します。
これで、「test.log」が取得できます。

※注意：ファイル名が100文字以上の場合、右から100文字以上取得できませんので、
100を200とかに変えると良いと思います。

ディレクトリ名を取得する

=LEFT(H1,LEN(H1)-LEN(I1))

これで、「/var/log/」と取得できます。
　※「I1」には、前項で取得したファイル名を入れておきます。

アクセス権 所有者   グループ    サイズ   月 日 時間or年     フルパス        ファイル名     ディレクトリ名   更新日時
-rw-r--r--  root    root    5600    Dec 21  10:45   /val/log/test.log   test.log    /val/log/   2016/12/21

解説

1. LEN(H1)-LEN(I1)

LENで文字列の長さを取得しています。
G1（/val/log/test.log）の長さから
H1（test.log）の長さを引いています。
つまり、ディレクトリの文字列（/var/log/）の長さを取得しています。

2. LEFT(H1,LEN(H1)-LEN(I1))

LEFTで、左からディレクトリの文字数だけ切り取っています。
これで、「/var/log/」が取得できましたね。

いかがでしたでしょうか。

他にも色々な方法があると思いますし、シェルやPerlなどを使って、サーバ上で表示を整えても良いと思います。

どうしてもExcel上で形式を整える必要がある（多くは環境の制限などで）ケースで、使ってみてください。

今回使用した関数

ISERROR　：　セルの値がエラーかどうか調べる
DATEVALUE　：　指定した文字列をシリアル値に変換する
CONCATENATE　：　文字列を結合する

TRIM　：　スペースを一括削除する
RIGHT　：　右から指定の文字数を取得する
SUBSTITUTE　：　文字列を置き換える
REPT　：　文字列を指定した数だけ繰り返す

LEFT　：　左から指定の文字数を取得する
LEN　：　文字列の長さを取得する