はじめに
たまたま知ったINDIRECT関数。
作業手順書など作成するうえで有効だと思ったのでいろいろ試してみました。
INDIRECT関数とは
INDIRECT関数とは、Excelで使用できる関数の一つで、文字列として提供された参照を実際のセル参照や範囲参照へ変換するために使用するとのこと。
実際に使ってみる
B2セルに「テストします」の値を入力します。
B4セルに以下の書式で入力します。
=INDIRECT("参照セル")
B4セルに「=INDIRECT("B2")」の値を入力します。
B2セルの値が参照され、B4セルに「テストします」が表示されました。
参照先の値が変更されても反映されている
B2セルの値を「テストしませんでした」に変更します。
B4セルの値も「テストしませんでした」に変更されています。
参照先の値と変更したい値が同一であれば、変更する手間がなくなります。
任意の場所で参照先を指定する
1行目のセルを削除します。
1行目のセルを削除すると、B2セルがB1セルとなります。
B4セルもB3セルに変更となります。
B2セルを参照先としていたので、B3の値は「0」となってしまいました。
セルの値を参照し続けるためには
セルの書式を変更せずに参照するためには以下の書式を使用します。
=INDIRECT(ADDRESS(ROW(), COLUMN()))
それぞれの関数の説明は以下の通りとなります。
| 関数 | 説明 |
|---|---|
| ADDRESS | 行番号と列番号を指定して、シート内のセルのアドレスを取得 |
| ROW | 引数として指定された配列の行番号を返す |
| COLUMN | 指定された セル参照の列番号を返す |
B2セルに「テスト再実施します」の値を入力します。
C4セルに「=INDIRECT(ADDRESS(ROW()-2, COLUMN()-1))」を入力します。
C4セルに「テスト再実施します」が表示されたことを確認できました。
- 「ROW()-2」で2つ上の行
- 「COLUMN()-1」で1つ左の列
をそれぞれ指定しています。
B5セルに「=INDIRECT(ADDRESS(ROW()-3, COLUMN()))」を入力します。
- 「ROW()-3」で3つ上の行
を指定しています。
行や列の指定を片方のみと指定したい場合は、指定する側のみ書式に数値を入力します。
今回の場合は行のみをしていて、列は指定していません。
INDIRECT関数を使用する際の注意点
3行目のセルを削除すると、B5がB4となります。
B4(もともとB5)セルは3つ上の行を参照するように引数を指定していて、B1セルを参照することとなり、「0」という値を返しています。
今回のように行や列を削除して、指定したセルの場所に変更が生じる場合には、修正は必須であることがわかりました。
セルの値が連続で入力されている場合
手順書作成時に作業時間、開始時間、終了時間を記載する必要がある場合を考えます。
前提
- 作業時間は作業完了時にかかった時間を手入力する
- 開始時間は一番目を手入力する
- 上記以外は手入力しないこととする
一番目であるC5セルに「9:00」と手入力し、開始時間を9時とします。
一番目の作業時間に10分要したとします。
終了時間が開始時間 + 作業時間である「9時10分(9:10)」となりました。
D5セルには以下の値を入力しています。
= INDIRECT(ADDRESS(ROW(), COLUMN()-1))+ INDIRECT(ADDRESS(ROW(), COLUMN()-2))
C6セルには以下の値を入力しています。
一番目の終了時間を参照し、二番目の開始時間としています。
= INDIRECT(ADDRESS(ROW()-1, COLUMN()+1))
C6セルが「9:10」となっていることを確認できます。
二番目の作業時間は5分、終了時間は一番目の値と同じ値を入力します。
終了時間が「9時15分(9:15)」となったことを確認します。
三番目の作業時間を20分、開始時間と終了時間は二番目の作業と同じ値を入力します。
二番目の作業である6行目を削除してみます。
セルが連続している場合には、開始時間と終了時間が問題なく反映されていることが確認できました。
作業時間を変更する必要はありますが、二番目の作業の行をコピー&貼り付けしても問題なく表示させることができました。
まとめ
参照先のセルに依存するのではなく、セル場所で参照できることが便利だと感じました。