INDIRECT関数を使って別シートのセルを参照する:Excel関数の技

スポンサードリンク

作成:2016/4/6,更新:2021/11/10

INDIRECT関数を使って別シートのセルを参照する　　topへ

別のシートのセルを参照したいときのINDIRECT関数の使い方です。
ここでは下図のように月別のシートに日付ごとのデータが入力されている例を使って説明します。
シート名は　4月、5月、6月としています。
各月のシートには1行目に日付データが入力されています。
ここではSheet5で各日付の合計値を取り出そうとしています。
B2セルに日付を入力したらその日付の合計値をB3セルに表示したいということです。

「2016/5/3」の合計値を表示するには　シート名が「5月」のシートの「5月3日」の列の12行目を参照すればよいので表引きの関数HLOOKUPが利用できることに気付きます。
=HLOOKUP(B2,'5月'!B1:H12,12,FALSE)　
とすることができます。なお、シート名が「’」（シングルクォーテーションマーク）で囲まれています。
日付が「2016/4/5」となったら数式は　=HLOOKUP(B2,'4月'!B1:H12,12,FALSE)　と修正する必要があります。
シートが変わるたびに数式を修正するのは面倒ですので、どうにかしたい・・・・
入力される日付の月とシート名の数字が同じですので、MONTH(B)&"月"　とすると参照するシートを指定できることに気付きます。
数式内では　INDIRECT関数で　INDIRECT(MONTH(B2)&"月!B1:H12")　と指定することができます。
=HLOOKUP(B2,INDIRECT(MONTH(B2)&"月!B1:H12"),12,FALSE)　
となりました。

下図のようにシート名が「2016-7」とハイフンが含まれているときは？
日付に「2016/7/5」と入力されているときに「2016-7」とするには　TEXT関数を使うことが考えられます。
TEXT(B2,"yyyy-m")　とすればよいと思われます。
ところが、そのまま利用して　=HLOOKUP(B2,INDIRECT(TEXT(B2,"yyyy-m")&"!B1:H12"),12,FALSE)　とすると・・・　#REF! エラーとなってしまいます。
シート名をシングルクォーテーションマーク　で囲む必要があります。
TEXT関数で前後に ’ (シングルクォーテーションマーク)を付けるようにします。
数式を　=HLOOKUP(B2,INDIRECT(TEXT(B2,"'yyyy-m'")&"!B1:H12"),12,FALSE)　とします。

スポンサードリンク

よねさんのWordとExcelの小部屋 » エクセル関数の技 » INDIRECT関数を使って別シートのセルを参照する

　PageViewCounter
　 Counter
　Since2006/2/27