Excelではほとんどの関数がセル番地や範囲を参照して値を取得します。
そのため、セル番地や範囲を自在に操ることができれば出来ることの幅が一気に広がります。
そこで必須となるのが「INDIRECT関数」と「OFFSET関数」です。
INDIRECT関数
INDIRECT(“参照文字列”)またはINDIRECT(“名前”)
→指定した参照文字列をセル番地や範囲に変換します
セル番地や範囲に名前の定義をしている場合は参照文字列の代わりに名前を指定できます
INDIRECT(セル番地)
→指定したセルの値(参照文字列や名前)をセル番地や範囲に変換します
多段階プルダウンリストを作成する場合は指定したセルに名前を入れて範囲を切り替えます。
※R1C1参照形式で使用する場合は第2引数を指定します。(今回は説明を割愛します)
※INDIRECT関数で指定した参照文字列が範囲を表すものでない場合はエラーとなります。
Excel関数でセル番地や範囲を指定する際にINDIRECT関数を使用すると、任意の文字列をセル番地や範囲に変換して指定できるため、値の取得範囲を可変にするなど幅広く応用することができます。
セル番地や範囲指定の際に定義した名前をそのまま使用する場合は、わざわざINDIRECT関数を使用する必要はありません。(「=INDIRECT(“名前”)」は「=名前」と同じため)
ただし、仮にA1セルに名前を入れて間接的に指定する場合は結果が異なるため注意が必要です。
「=INDIRECT(A1)」→A1セルの値を参照文字列や名前と認識してセル番地や範囲に変換
「=A1」→A1セルの値に定義した名前が入っていてもただの文字列とみなされる
使用例
INDIRECT(A1) → 「#REF!」エラー
指定したA1セルの値は範囲に変換できる文字列や名前ではないためエラーとなります。
INDIRECT(“A1”) → 「これは「A1」の値です」
指定した文字列「A1」をセル番地に変換してて値を取得しています。(「=A1」と同じ)
INDIRECT(C1) → 「これは「A1」の値です」
指定したC1セルの値「A1」(文字列)をセル番地に変換して値を取得しています。
INDIRECT(E1&1) → 「これは「B1」の値です」
指定したE1セルの値「B」に数字の「1」を結合した文字列「B1」をセル番地に変換しています。
A1～A5を「テストA」、C1～C5を「テストC」で名前の定義をした場合
COUNTA(INDIRECT(“テストA”)) → 5 ※この場合は「COUNTA(テストA)」と同じ
COUNTA(INDIRECT(“テストC”)) → 1 ※この場合は「COUNTA(テストC)」と同じ
例えばF1セルに「テストA」や「テストC」などの名前を入力して範囲を切り替える場合は
COUNTA(INDIRECT(F1))と指定することで簡単に切り替えが出来ます。
簡単な応用テクニック
月毎の集計など各シートから任意のデータを引っ張ってきて一つの表に集約する、INDIRECT関数の簡単な応用テクニックを紹介します。
(用意した月別シート)
4月～9月(上半期分)のシートを作成しています。
(各月別シートの内容)
各シートには月ごとの集計を入力しています。
(集約シートの内容)
集約シートの項目名(4月～9月)と文字列「集計リスト」を結合してシート名と一致する参照文字列を作り、INDIRECT関数を使って各月ごとの集計を参照しています。
この例においてはExcelの標準機能である「串刺し集計(3D集計)」を使う方が一般的ですね。
関数の引数に指定する「値」は文字列や数値など自由に設定できるため、少し工夫をすれば簡単に可変にすることができます。しかし「範囲」は参照形式(または名前)でしか指定できず、文字列のように自由に結合したり分割することができません。
INDIRECT関数を活用すれば範囲を可変にするだけでなく、他の関数と組み合わせることで様々な複雑な処理が可能となります。
INDIRECT関数の威力を最大限に発揮する実用テクニック
別々のシートに入力された各支店ごとの社員名簿から必要なデータを取り出し、１つのシートに素早く集約する実践テクニックを紹介します。
完成イメージ
メインシート(集約シート)にて、支店名を選択します。
氏名のプルダウンリストは選択した支店名のシートから氏名欄を参照して自動で作成されます。
支店名、氏名を選択すると、該当のメールアドレスが自動表示されます。
手順は以下のとおり
各シートを作成する(準備)
シート一覧
「抽出メンバー」シート
「富山支店」シート
「石川支店」シート
「福井支店」シート
範囲に名前を定義する
「富山支店」シートを開き、テーブルを挿入してから氏名の範囲に名前を定義します。
(テーブル化しておくとメンバー追加の際に範囲が自動変更されます)
範囲名は選択した支店名と紐づけるため「富山支店」としています。
VLOOKUP関数でメールアドレスを取得するため、リスト全体に名前の定義をします。
範囲名は選択した支店名と紐づけるため「富山支店一覧」としています。
他のシートも同様に設定します。
定義した名前(名前の管理)はこんな感じになります。
※テーブルを使用する場合はテーブル名による範囲指定も可能ですが、あえて分かりやすくするため名前の定義をしています。
データの入力規制や数式を入力する
データの入力規制にて「支店名」入力用のプルダウンリストを作成します。
今回の例では3店舗しかないため、元の値は直接入力しています。
データの入力規制にて「氏名」入力用のプルダウンリストを作成します。
INDIRECT関数を使用し、「支店名」と一致する名前の範囲からリストを参照しています。
メールアドレス表示用セルに下図の数式を入力し、選択した支店名と氏名に一致するメールアドレスを参照して自動表示します。
VLOOKUP関数で指定する範囲にあらかじめ「支店名＋一覧」と名前の定義をすることで、各支店名に紐づけされた範囲の参照を可能にしています。
OFFSET関数
INDIRECT関数が参照文字列で範囲を指定するのに対し、OFFSET関数は数値のみで範囲を指定します。つまりその２つの関数を場面ごとに使い分けることで、どんな複雑な数式の範囲指定にも対応できるようになり、様々な処理を行うことが出来ます。
書式
OFFSET(基準, 行数, 列数, [高さ], [幅])
基準に設定したセルから行数・列数分移動した場所を起点とし、高さと幅に指定した範囲を参照します。
使用例
引数が数値となることから、数値を返す関数(MATCH、FIND、LOW、COLUMN、COUNTなど)を引数に指定し、そうやって作成したOFFSET関数を更に別の関数の範囲にネストします。
前回記事「プルダウンリスト絞り込み」では、キーワード検索によって絞り込んだ範囲をOFFSET関数の引数に指定することでプルダウンリストを可変にしています。
コメント