見積書や連絡書などの書類をExcelで作成しているとExcelのファイル名にある送り先様名や書類番号をSheetのセル内に入力することがあるかと思います。
この時、本記事で紹介する関数を入れたフォーマットを作ることにより、ファイル名を変更すると自動的にSheetのセル内に送り先様名や書類番号を反映することができます。
時間短縮はもちろん、変更忘れによる間違えの防止にも役立つと思います。ぜひ参考にしてみてください。
- 使用する関数
- CELL関数
- MID関数
- FIND関数
- Excelファイル内への自動入力
- (参考)CELL関数の検査の内容
使用する関数
使用する関数は3種類です。
- CELL関数：ファイルのURLを呼び出すのに使用
- MID関数：URLの情報からファイル名を切り出すのに使用
- FIND関数：MID関数でファイル名を切り出す際の位置指定に使用
下記でそれぞれの関数の使い方を簡単に紹介します。
CELL関数
CELL関数はセルの情報や書式、位置等を調べることが出来る関数です。
= CELL ( 検査の内容 , 参照するセル )
「検査の内容」を決める引数に”filename”を入れることで、そのExcelファイルのURLを表示することができます。(他の検索内容は本記事の文末に記載します。)
もう1つの引数「参照するセル」はセル自体の情報を知るときに使う引数ですが、今回はExcelファイルの状態を確認するため使用しません。特に入力しなくても問題なくExcelファイルのURLを表示することができます。
MID関数
MID関数は指定した文字列から文字を数を切り出して表示できる関数です。
= MID ( 文字列 , 開始位置 , 文字数)
FIND関数
FIND関数は文字列の中で指定した文字が何文字目に位置しているかを求める関数です。
= FIND ( 検索対象 , 文字列 )
Excelファイル内への自動入力
上記で紹介した関数を組み合わせることによりファイル名の一部をSheet内へ自動入力することができます。下記の見積書の画像を例に説明していきたいと思います。
関数組み合わせ方としては、MID関数の文字列の部分にCELL関数でファイルURLを読み込んでFIND関数で表示したい箇所の文字位置を指示していく形になります。
ExcelファイルのURLを確認するとExcelのファイル名部分は “[ ]” で囲われているのが分かるかと思います。FIND関数の検索対象の部分に”[” や “]”を使うことでMID関数で指定する文字数を出していきます。
ファイルURL内のフォルダ名等に “[ ]” が使われているとうまくいきません。そんな時にはファイル名の拡張子 “xlsx” など、別の検索対象にしてみるなどの工夫が必要になります。
1.入力する文字数に変化がない場合
まずは見積書Noを出力してみましょう。
CELL関数を使ってファイルURLの情報を確認してみます。
MID関数の開始位置についてはファイル名の緑枠の部分にある “[” を基準に不要な文字数分を足し合わせればできます。足し合わせる文字数は”[“を含めてカウントします。今回の場合は「 [見積書_ 」の5文字になります。
MID関数の文字数の部分は青枠のように見積書Noが4桁で確定しているので「4」になります。
まとめると、下記のような関数の組み合わせになります。
= MID ( CELL(“filename”) , FIND(“[“,CELL(“filename”))+5 , 4 )
2.入力する文字数に変化する場合
続いて送り先様名を抽出したいと思います。
MID関数の開始位置については「1.入力する文字数に変化がない場合」と考え方は同じです。
緑枠の部分にある “[” を基準に不要な文字「 [見積書0004_ 」の10文字分を指定します。
MID関数の文字数については、FIND関数を用いて下記のように赤枠部分の文字数から青枠、緑枠の文字数分を引くことで抽出できます。
まとめると、下記のような関数の組み合わせになります。
= MID ( CELL(“filename”) , FIND(“[“,CELL(“filename”))+10 , FIND(“]”,CELL(“filename”)) – FIND(“[“,CELL(“filename”)) -15 )
(参考)CELL関数の検査の内容
| 検査の種類 | 戻り値 |
| address | 対象範囲の左上隅にあるセル番地を文字列として出力 |
| col | 対象範囲の左上隅にあるセルの列番号を出力 |
| color | セルに負の数を色で表すように書式設定されている場合は「1」 それ以外の場合は「0」を出力 |
| contents | 対象範囲の左上隅にあるセルの値を出力 |
| filename | 対象範囲を含むファイルのフルパス名を文字列として出力 保存されていない場合は「””（空白文字列）」 |
| format | セルの表示形式を出力 |
| parentheses | 正の値またはすべての値をかっこで囲む書式がセルに設定されている場合は「1」 それ以外の場合は「0」を出力 |
| prefix | セル内のデータの配置 左詰めの文字列を含むときは単一引用符「’」 右詰めの文字列を含むときは二重引用符「”」 中央揃えの文字列を含むときはキャレット「^」 両揃えの文字列を含むときは円記号「¥」 セルにそれ以外のデータが入力されているときは空白文字列「””」を出力 |
| protect | セルがロックされている場合は「1」 ロックされていない場合は「0」を出力 |
| row | 対象範囲の左上隅にあるセルの行番号を出力 |
| type | セルに含まれるデータのタイプ 空白の場合は「b（Blankの頭文字）」 文字列定数が入力されている場合は「l（Labelの頭文字）」 その他の値が入力されている場合は「v（Valueの頭文字）」を出力 |
| width | セルの幅を出力 |
