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 ： 文字列の長さを取得する