Excelで大量のファイルを管理している時、ファイル名を手入力するのは大変ですよね。さらにファイル名が変わったときに、手入力だと修正漏れが発生してしまうことも。
そんなときには、「CELL関数」がおすすめです。今回は、CELL関数を用いて「ファイル名」を取得する方法だけでなく、CELL関数を応用して「保存先のフォルダ名」や「ワークシート名」を自動で取得し表示させる方法まで、詳しくご紹介します。
■ 説明に使用したExcelのバージョン：Excel2019
|
フルスタックエンジニア |
|
フルリモート |
|
Webアプリケーションの設計／開発／運用経験 他 |
|
|
サーバーサイドエンジニア |
|
|
フルリモート |
|
|
Ruby on Railsを用いた開発経験3年以上 他 |
あなたに合う案件をプロが探し、案件参画から、稼働中のお悩みまで全サポート致します。
まずは気軽にご連絡ください。
1. セルの情報を得られるCELL関数とは？
1.1 CELL関数とは？
CELL関数とは、指定した範囲のセルの情報を調べて表示する関数です。セルの内容や行番号だけでなく、そのセルを含むファイルの情報を表示することも可能です。
1.2 CELL関数の使い方
CELL関数は、次のような書式で表すことができます。
CELL("検査の種類", 検査対象のセルの範囲)
検査対象のセルには「A1」などの単体のセルだけでなく、「A1:B2」のような範囲を指定することも可能です。
1.3 CELL関数で指定できる「検査の種類」は12種類
CELL関数で使用できる「検査の種類」は、全部で12種類あります。
| 検査の種類 | 表示される結果 |
|---|---|
| filename | 対象範囲を含むファイルのフルパス＋ワークシート名を表示する。（未保存の場合は空欄になる） |
| address | 対象範囲の一番左上のセルの絶対アドレスを表示する。（$A$1など） |
| contents | 対象範囲の一番左上のセルの値（数式でなく結果）を表示する。 |
| col | 対象範囲の一番左上のセルの列番号を表示する。 |
| row | 対象範囲の一番左上のセルの行番号を表示する。 |
| width | セル幅を表示する。（小数点以下切り捨て） |
| format | 対象範囲の一番左上セルの表示形式に対応する文字列定数を表示する。 |
| color | 負の数を自動的に色付けする書式設定の有無により、次の値を表示する。
設定されている場合：1 それ以外：0 |
| parentheses | 正の値である、または全ての値を括弧で囲まれる書式設定の有無により、次の値を表示する。
設定されている場合：1 それ以外：0 |
| prefix | 対象範囲のセルに設定されている文字位置により、次の値を表示する。
左詰めの文字列を含む：単一引用符 (') 右詰めの文字列を含む：二重引用符 (") 中央揃えの文字列を含む：キャレット (^) 両揃えの文字列を含む：円記号 (\) セルにそれ以外のデータが入力されている：空白文字列 ("") |
| type | 対象範囲のセルに含まれるデータタイプにより、次の値を表示する。
文字列定数が入力されている："l" （Label の頭文字） その他の値が入力されている："v" （Value の頭文字） セルが空白："b"（Blank の頭文字） |
| protect | セルのロックの有無により、次の値を表示する。
ロックされている場合：1 ロックされていない場合：0 |
ファイル名の取得は、上記のうちの「filename」を用いて実現することが可能です。
2. CELL関数でファイル名（ワークブック名）を取得する
2.1 CELL関数でフルパスを取得する
前項でご紹介した通り、ファイル名の取得には「filename」を使用します。書式は次の通りです。
CELL("filename", A1)
filenameの場合、どのセルでも同じファイルの中のセルであるため、検査対象の部分にはどのセルを指定しても同じ結果になります。そのためA1セルを指定しても、B2セルを指定しても、結果は同じになります。
2.2 ファイルが保存されているフォルダの部分を切り出す
この結果から、表示内容は次のような構成になっていることが分かります。
保存先フォルダのパス[ファイル名]ワークシート名
ファイルが保存されているフォルダのパスについては「 [ 」より左の部分を抜粋すればいいので、LEFT関数を使用します。
その前にまずどこまで切り出せばいいのか、FIND関数を用いて「 [ 」の位置を調べます。
FIND("目印の文字列", 対象のセル)
このように「 [ 」は13番目であることが分かったので、その1文字手前を指定（-1）してLEFT関数に組み入れます。
LEFT(対象のセル, 文字数)
これで、ファイルが保存されているフォルダのパス部分が抜粋できました。
2.3 ファイル名（ブック名）の部分を切り出す
先ほどの情報から、ファイル名は中心の「 [ ] 」で囲まれた部分を抜粋すればいいことが分かります。よってこちらは、MID関数を使用します。
MID(対象のセル, スタートの文字位置, 文字数)
まず、スタートの位置は先ほどの「 [ 」の1文字後を指定（+1）でOKです。
次に文字数ですが、スタートの「 [ 」とエンドの「 ] 」の位置をそれぞれ調べて、その差分をとることで求められます。
文字数 = エンド]の位置 - スタート[の位置 -1
これらを組み合わせると、次のような式が求められます。
MID(CELL("filename", A1)
, FIND("[", CELL("filename", A1)) +1
, FIND("]", CELL("filename", A1)) - FIND("[", CELL("filename", A1)) -1
3. CELL関数でワークシート名を取得する
3.1 CELL関数でフルパスを取得する
こちらも同じく、まずフルパスを取得します。
CELL("filename",A1)
3.2 ワークシート名の部分を切り出す
この結果から、ワークシート名はファイル名の終わりを表す「 ] 」より右側の部分を切り出せばいいので、RIGHT関数を使用します。
ただしRIGHT関数は右から何文字切り出すかを指定しなければならないため、まずその文字数を調査する必要があります。文字数の調査には、LEN関数を使用します。
LEN(対象のセル)
全体の長さから記号「 ] 」までの長さを引くと、「 ] 」以降の長さが求められるので、次のように表すことができます。
RIGHT(対象のセル, LEN(対象のセル) - FIND("目印の文字列"))
このように、作業中のワークシート名を求めることができました。
4. ExcelのCELL関数を使う際に、よくある質問
今回はExcelのCELL関数を使う際の、よくある質問をまとめました。
【A】CELL("filename") は、シートを含むブックが保存されている場合にのみファイル名を返します。空文字（""）を返す場合は、まだブックが保存されていない可能性があります。
【対策】ブックを一度保存し直してから、もう一度CELL 関数を試してください。
【Q】表示された結果が想定と違っていた場合、どんな間違いが考えられますか？
【A】第二引数で指定するセルの参照先が、想定外のシートやブックを指定していた場合、取得した値も想定外のものになります。
また、Windowsにはファイルパスの文字数制限があるため、取得結果のファイルパスが255文字を超える場合、CELL関数が正しく動作しないことがあります。
【対策】第二引数で指定するセルの参照先を、見直してみてください。
または、ファイルパスが上限を超えないよう、ファイル名を短くしてみてください。
【Q】ファイル名を変更したのに、CELL関数の結果に反映されません
【A】数式を自動計算する設定になっていない場合、取得結果は自動では更新されません。
【対策】「数式」タブから「計算方法の設定」を開き、自動計算されるように設定を変更してください。
5. まとめ
今回はCELL関数とMID関数やLEFT関数などの文字列切り出し関数を組み合わせることで、ファイル名などの表示を行いました。文字列の切り出しについての詳細は次の記事にも記載していますので、よろしければご覧下さい。
次回は、Excelの表に自動で更新されるカレンダーを作る方法について、ご紹介したいと思います。
よければ合わせてご覧下さい。
実際にフリーランスエンジニアとして活躍されている方のインタビューはこちら
【Q】ファイル名が取得できず、空白（空文字）になる場合はなぜですか？