ワークシート関数でファイル名とかなんとか

ちょっと窓XP終了問題はお休みしまして…（もうふた月経ってるけどまだ終わらんのかい！）



Excelでファイル名やフルパス・シート名なんかを取得しようと思ったらまずはVBAでコードを書いて…というのがセオリーではあります。

しかし私の職場みたいにマクロ禁止令(原則は、ね)が出ていたりするとそうはいきません。
まぁ実際問題自分以外のメンバーが使用するファイルだと、やたらにVBAでというのも考えものです。ブラックボックス化してしまいますから。


で色々調べていた所『CELL』というワークシート関数を見つけました。

詳しくはExcelヘルプを参照いただきたいんですが、見たところはっきり申し上げて


　「ファイル名とパスを取得するのくらいしか使いみちがない」


ような関数です（暴言）。

しかも取得の仕方はほぼ『おまじない』レベルで、暗記してしまえばどこでも使えます。

ということでここ暗記しましょうテストに出ますよ（棒）！

【ファイル名の取得】
=MID(CELL("filename",$A$1),FIND("[",CELL("filename",$A$1),1)+1,FIND("]",CELL("filename",$A$1),1)-FIND("[",CELL("filename",$A$1),1)-5)

【シート名の取得】
=RIGHT(CELL("filename",$A$1),LEN("filename",$A$1))-FIND("]",CELL("filename",$A$1),1))

以上。

（ただし、拡張子が[.xlsx]のファイルの場合、MIDの「-5」を「-6」にしないといけないんで注意ね）


まぁ、ただこれだけならネットに記事がそこそこ転がってますので、ここでは応用編といきましょうか。

(長くなるので"続き"に入れておきます)

■面倒な日報・月報業務への応用■

今回仕事上で必要になったのは、毎月同じことをしなければならないのが非効率的であるということで、日付だのは決まってるんだから自動化できねえのかよぅ、という話。その中で、必要な仕様をリストアップしてみます。

1. 月別のファイルがある。
2. 日別のシートがあり、表集計で金額を合わせたい。
3. 集計シートがあり、日別の差し引き合計がここで集約されるようにしたい。

実際のモノを見ながらでないと分かりづらいかと思いますが、問題点は以下のとおりでした。

• 1で新しい月のファイルを作る時に、2のシートの内容（主には日付）を全シート分書き換えなければならない。
• 3の参照先を月ごとにいちいち書き換えないとエラーが出て計算結果が出なくなる。

まぁ自分が使うと考えれば月別ファイルより年度ファイルに月シート、テーブル化してフォーム機能で入力、みたいな感じですがちょっと利用するメンバーには難しそうでしたので却下。
次にテンプレファイルを作っておいて、集計シートの冒頭にある日付を該当月の月初に、それに合わせて各シートが参照して…っていう方法なんですが、本っ当にデータの扱い等についても知る機会のなかったメンバーなので、例えば書式設定を[yyyy年M月]にした場合月初"日付"を入力するのも理解できんかもしれない、という危惧が。

ならばもういっそ、テンプレからファイル名を変えるだけで全部書き換わる方がスマートだろうという結論に達しました。数式についてイチから教えている暇はないので、この際若干のブラックボックス化は仕方ない、と。

具体的には、

1. ファイル名を「yyyy年MM月.xls」という仕様にしておく。
2. シート名を「d日」という仕様にしておき、３１日(最大分)まで作成しておく。
   
   こんな感じ
   
   
3. CELL関数を使って取得したファイル名とシート名をCONCATENATE関数等で繋げ、DATEVALUE関数で日付シリアル値にする。※あとは、必要な書式を設定すれば管理部門提出用出力帳票にもなりますよね。
4. 集計用シートで行見出しに日付(シート名)を取り、列に必要項目と参照セルを入力しておく。
5. やはりCONCATENATE関数とINDIRECT関数で文字列から[シート!セル]を参照し、自動的に値を取得する。※この際、存在しない日付はISERROR関数等を利用して除外判定し、０を返すように数式を組んでおけば正しく集計することができます。
   
   こんなイメージになりました（ちなみにこれOfficeRTで作ったよ）

こうすれば、テンプレファイルをコピーして日付を書き変えれば全て自動で集計してくれるというわけや(ｸｯﾁｬｸｯﾁｬ

■注意すべきこと■

注意事項としては、MID等で切り出す場合の文字数ですね。ファイル名を「yyyy年MM月.xls」という仕様にしたなら、それを守ってもらう必要があります。文字数が変わると切り出す文字数も変わるからね。例えば「yyyy年MM月●●●.xls」などにしたら、その分差し引きする数字が変わります。おまじないでは

…FIND("[",CELL("filename",$A$1),1)-5)

になっていますが、この下線部分を「-8」とかに変えなきゃいけない。もしくは拡張子付きのファイル名を取得したいなら「-1」にするとか。そういうことです。

幸いシート名についてはファイル名の文字数関係ない(シート名終了の文字列をFINDしてるから)ですが、ファイル名の取得と利用については注意が必要です。

■おわりに■

と、そんなこんなで引渡しをするわけですが、そうは言ってもシステム関連のリーダー的存在には話を通しておく方がいいですね。うちの部署には元システム部門にいたメンバーがいるのですが、Excel数式やVBAついてはさっぱりなので、一応こういうことだよ、とこのブログ及び印刷したCELL関数のヘルプを渡しておくことにしました。まぁそれどこまで読んでくれるか疑問ではありますが…(苦笑)


それと、今回はDATEVALUEとINDIRECTを使って値の取得をしましたが、やりようによってはMATCH＋INDEX、VLOOKUP/HLOOKUP、下手したらSUMPRODUCTなんかを利用することも可能かもしれません。物は考えようなので、この記事を参考に色々試してみてください。ただし、おそらく”ネスト地獄”になるんでその辺だけお気をつけあそばせ…

…でもそこまでするならGETPIVOTDATAの方が使い勝手良さそうな気もするなw