2015年04月18日

INDIRECT関数でセルの位置や範囲をテキストで参照させてみた/Excel/検索関数

Excelの関数でセルの位置や範囲をテキストで参照させます。
INDIRECT関数を使用します。

通常、セルの参照は式や関数の中に参照するセルやセル範囲を"A1"、"A1:A3"と入力されますが、それをINDIRECT関数によって、テキスト(文字列)で入力された"A1"、"A1:A3"等をセル参照にすることが出来ます。
20150418_000.jpg

さっそく、具体例で説明しましょう。
こんな、テストの点数の一覧表があります。
20150418_001.JPG

ここで、加藤泰江さんの点数"73"を他のセルで表示(参照)します。
ちなみに、"73"の入力されているセルは"C7"です。
20150418_002.JPG

セルC15に"=C7"と入力(または"="を入力してセルC7を選択)します。
20150418_003.JPG

すると、"C7"の値が参照されて、セルC15に"73"と表示されました。
20150418_004.JPG

同様なことを"INDIRECT関数"をつかってやってみます。
セル"B16"にテキストで"C7"と入力しておきます。
で、"C16"で、「数式」、「関数ライブラリ」、「検索/行列」から「INDIRECT」を選択します。
20150418_005.JPG

参照文字列:"C7"が入力されている"B16"を指定します。
参照形式:省略
20150418_006.JPG

すると、"C7"の"73"が参照されました。
20150418_007.JPG

次は、セルの範囲を参照してみます。
合計を"SUM関数"で求めます。参照範囲は"C4:C12"です。
20150418_008.JPG

合計"564"が求められました。
20150418_009.JPG

セル"B16"にテキストで"C4:C12"と入力しておきます。
その右にSUM関数のセル範囲の代わりに"INDIRECT"で"B16"を参照します。
20150418_010.JPG

と、合計が求められました。
20150418_011.JPG

次は、VLOOKUPでの応用編です。
VLOOKUP関数の参照範囲に範囲名を付けてその"名前"をINDIRECTで参照します。
国語と数学の点数が2つの表に集計されています。
加藤泰江さんの国語の点数をこの表から"VLOOKUP関数"で参照するとこうなります。
20150418_012.JPG

加藤泰江さんの国語の点数"73"が求められました。
20150418_013.JPG

数学の点数を参照したい時は、数学の点数の範囲をVLOOKUP関数で参照しなければなりません。
20150418_014.JPG

いちいち、範囲を設定するのは面倒なので、予め範囲に名前を付けておくことができます。
国語の表の範囲を選択して、数式バーの左隣の「名前ボックス」に"国語"と入力して「Enter」キーを押して確定します。(Enterするのを忘れないでくださいね)
同様に数学の表も"数学"と名前を付けておきます。
20150418_016.JPG

すると、"VLOOKUP関数"で、参照範囲には"国語"と入力するか、範囲を選択すると範囲名の"国語"が入力されます。
20150418_017.JPG

数学も同様に範囲名を指定します。
20150418_018.JPG

これで、加藤泰江さんの国語と数学の点数が、一覧表から参照されました。
20150418_019.JPG

ここで、INDIRECT関数で、VLOOKUP関数の参照範囲の範囲名をセル"B19"の"国語"、セル"B20"の"数学"を参照して範囲名にすることが出来ます。
20150418_020.JPG
20150418_021.JPG

これで、国語、数学の加藤泰江さんの点数が、VLOOKUP関数で面倒な参照範囲を設定しなくても自由に求めることが出来ました。
20150418_022.JPG


応用
2つのドロップダウンリストを連動させて絞り込んだデータを選択させてみた

関連ページ
VLOOKUPで一覧表から列方向の検索結果を求めてみた
INDEX関数で指定した行番号のセルのデータを求めてみた
OFFSET関数で関数の引数の範囲を設定してみた
関数一覧(カテゴリ別)
関数一覧(名前別)
エラー値について(#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM!、#NULL!とか)


posted by haku1569 at 21:30| Excel関数 | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。