今回は、私が業務で使用しているEXCELの式の中で、役立ちそうな式を記載しようと思います。
初めに使用している実際の式（一部変更している箇所あり）を示し、次に解説、関数の説明の流れで記載しています。
シート名の取得
使用している式
=RIGHT(CELL(“filename”,$A$1),LEN(CELL(“filename”,$A$1))-FIND(“]”,CELL(“filename”,$A$1)))
解説
上記式は、RIGHT関数（CELL関数,LEN関数-FIND関数)となっています。
CELL関数で$A$1セルが存在するシート名をフルパスで取得し、「LEN関数-FIND関数」で文字数を求めています。
LEN関数でCELL関数で得たフルパスの文字数を求め、その値からFIND関数でフルパスのシート名直前までの文字数を引いています。
なお、CELL関数で得られるフルパスは「”ファイルが存在するまでのパス”\[Book1.xlsx]Sheet1」となるので、FIND関数の検索文字を”]”としています。
各関数の説明を以下に示します。
使用している関数の説明
実施例
RIGHT(“テスト用の文字列です。”,6）⇒ 結果：「文字列です。」
CELL(検査の種類,対象範囲)
「対象範囲」を含むファイルに対し、「検査の種類」で指定した情報を文字列で返す。
実施例
CELL(“filename”,$A$1) ⇒ 結果：ファイル名を絶対パスで取得する。
実施例
LEN(“テスト用の文字列です。”）⇒ 結果：「11」
実施例
FIND(“文”,”テスト用の文字列です。”）⇒ 結果：「6」
FIND(“文”,”テスト用の文字列です。”,3）⇒ 結果：「6」
FIND(“です。”,”テスト用です。文字列です。”,6）⇒ 結果：「11」
参照していた列で追加された行を新しい参照先とする
使用している式
=OFFSET($B$2,COUNTIF(C:C,”<>”),,,)
解説
OFFSET関数により、動的な範囲の指定を可能にしています。
範囲については、C列で文字が入力されている数を求め、B2セルから求めた数分、行を下にずらすように設定しています。
使用している関数の説明
実施例
OFFSET(B2,,,3,4) ⇒ 結果：B2:E4
OFFSET(B2,0,0,3,4) ⇒ 結果：B2:E4
OFFSET(B2,2,3,,) ⇒ 結果：E4
OFFSET(B2,2,3,1,1) ⇒ 結果：E4
OFFSET(B2,2,3,3,4) ⇒ 結果：E4:H6
OFFSET(B2:C2,2,3,3,4) ⇒ 結果：E4:H6
また、通常、OFFSET関数を単体で使用することは、ほとんどないと思います。
大抵は、SUM関数で対象のセル範囲の合計値を求めたり、count系の関数などの他の関数と組み合わせたり、入力規則のリストの項目設定で使うことが多いと思います。
実施例
COUNTIF(A:A, “〇”）⇒ 結果：A列にある「〇」の数が求められる
まとめ
今回は、EXCELを使う上で私が良く使う２つの構文の式を説明しました。
特にOFFSET関数については、EXCELの関数の中でも難しい部類に入ると思います。しかし、使いこなせるようになれば、いろんな場面で活躍してくれると思います。
今回は一部になりますが、今後も別の記事で同様の内容を作っていこうと思っていますので、参考にしていただければ幸いです。