【EXCEL】シート名取得/追加された行を新しい参照先に

今回は、私が業務で使用している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関数
書式
RIGHT(文字列, 文字数)
説明
取り出す文字列を含む元々の「文字列」の右端から「文字数」で指定した文字数分の文字列を取り出す。
※「文字数」は省略可。省略時の「文字数」は「1」として認識される

実施例

RIGHT(“テスト用の文字列です。”,6)⇒ 結果:「文字列です。」

CELL関数
 書式

CELL(検査の種類,対象範囲)

説明

「対象範囲」を含むファイルに対し、「検査の種類」で指定した情報を文字列で返す。

実施例

CELL(“filename”,$A$1) ⇒ 結果:ファイル名を絶対パスで取得する。

LEN関数
書式
LEN(文字列)
概要
指定した「文字列」の文字数を返す。

実施例

LEN(“テスト用の文字列です。”)⇒ 結果:「11」

FIND関数
書式
FIND(検索文字列, 対象, 開始位置)
説明
検索する対象の「検索文字列」から、検索する文字列「対象」を探し、「何文字目にあるか?」を返す。また、検索する左から何文字目から開始するかを「開始位置」で指定できる。
※開始位置は省略可。省略時の「開始位置」は「1」として認識される。

実施例

FIND(“文”,”テスト用の文字列です。”)⇒ 結果:「6」

FIND(“文”,”テスト用の文字列です。”,3)⇒ 結果:「6」

FIND(“です。”,”テスト用です。文字列です。”,6)⇒ 結果:「11」

スポンサーリンク

参照していた列で追加された行を新しい参照先とする

使用している式

=OFFSET($B$2,COUNTIF(C:C,”<>”),,,)

解説

OFFSET関数により、動的な範囲の指定を可能にしています。

範囲については、C列で文字が入力されている数を求め、B2セルから求めた数分、行を下にずらすように設定しています。

使用している関数の説明

OFFSET関数
書式
OFFSET(参照、行数、列数、高さ、幅)
説明
基準となるセルである「参照」のセルから指定した「行数」と「列数」だけずれたセルの値を返します。
また、「高さ」および「幅」を指定すると、返すセルの値を範囲で指定でき、戻り値も範囲で返ことが可能です。
※INDIRECT関数(こちらを参照)、ADDRESS関数(こちらを参照)などを使用して「基準のセル範囲」を指定することも可能
※「参照」は範囲での指定も可能。ただし、基準となるセルは、範囲指定したセル範囲のうち始点である一番左上のセルになります。
※「行数」は省略可能。省略した場合は「0」と認識され、「参照」で設定したセルと同じ行になります。
※「列数」は省略可能。省略した場合は「0」と認識され、「参照」で設定したセルと同じ列になります。
※「高さ」は省略可能。省略した場合は「1」と認識され、「参照(0の場合のみ)」もしくは「行数(0の場合を除く)」で設定したセルと同じ行になります。
※「幅」は省略可能。省略した場合は「1」と認識され、「参照(0の場合のみ)」もしくは「列数(0の場合を除く)」で設定したセルと同じ列になります。

実施例

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関数
書式
COUNTIF(範囲, 検索条件)
説明
指定された「範囲」内で、設定された「検索条件」に適合するセルの個数を求める。

実施例

COUNTIF(A:A, “〇”)⇒ 結果:A列にある「〇」の数が求められる

まとめ

今回は、EXCELを使う上で私が良く使う2つの構文の式を説明しました。

特にOFFSET関数については、EXCELの関数の中でも難しい部類に入ると思います。しかし、使いこなせるようになれば、いろんな場面で活躍してくれると思います。

今回は一部になりますが、今後も別の記事で同様の内容を作っていこうと思っていますので、参考にしていただければ幸いです。

スポンサーリンク

その他 業務・ITの最新記事4件