[Excel] INDIRECT関数で別シートのセルを参照

今回は、INDIRECT関数を使って、Excel(エクセル)のファイル(ブック)内にある別のシートのセルを参照する方法を確認していきます。複数のシートに表を分けている場合に効果を発揮する関数なので、基本的な使い方を覚えておくと便利です。

セルの文字列を参照するINDIRECT関数

Excelで複数の表を作成する際、一つのシートに同居させるのではなく、表ごとに別々のシートを用意するのが効率的です。それぞれのシートに作成した表の内容を参照してまとめたい場合は、INDIRECT関数を利用するのがオススメです。

式の基本形は、=INDIRECT("参照シート!参照セル")

今回は、「商品A」「商品B」「商品C」の3つのシートと、その内容をまとめる「全体」シートで構成したExcelファイルを作成(図1)。「商品A」～「商品C」内には同じ構成の表を作成しています(図2)。

「全体」シートでINDIRECT関数の数式を設定したいセル(今回の例ではB2)を選択して、数式バーに「=INDIRECT("商品A!A3")」と入力します(図3)。数式の「"商品A!A3"」の部分は、「商品Aシート」の「A3セル」を示しています。「"」で囲み、シートとセルの間に「!」を入れるのが基本となります。[Enter]キーを押して確定させると、B2セルに「商品A」シートの「A3セル」にある文字列が表示されます(図4)。

• (図1)「全体」「商品A」「商品B」「商品C」の4つのシートを作成しています

• (図2)「商品A」～「商品C」シートはこのような構成になります

• (図3)「全体」シートのB2セルを選択して、数式バーに「=INDIRECT("商品A!A3")」と入力します

• (図4)「商品A」シートのA3セルの文字列(図2参照)が表示されました

INDIRECT関数を連続したセルに反映させる方法

今回の例では、「全体」シートに「商品A」～「商品C」シートのセルに入力した文字列を参照しようとしており、A2～A4セルにシート名と同じ「商品A」～「商品C」の文字列を入れています。ところが、先ほど作成した「=INDIRECT("商品A!A3")」の数式では、フィルハンドルをドラッグして下のセルにコピーしても、「商品B」「商品C」シートのA3セルを参照する数式にはなりません(図5)。個別に数式を入力するのは面倒なので、コピーして「商品B」「商品C」シートを参照できる数式にしてみましょう。

参照するシート名と同名のセルを用意

前提条件として必要なのは、A2～A4セルに参照したいシート名と同じ文字列を入れておくことです。先ほどと同じくB2セルを選択して、数式バーに「=INDIRECT(」と入力したら(図6)、参照したいシートと同じ文字列が入力されたA2セルをクリックして数式に入力します(図7)。続いて「&"!A3")」と入力して数式を完成させます(図8)。

式は、=INDIRECT(参照シート&"!参照セル")

数式の「A2&"!A3"」の部分は、先ほどの数式と同じく「商品A」シートのA3セルを示していますが、「商品A」シート部分をA2セルの文字列で参照しているのが違いとなります。[Enter]キーで確定させると、先ほどと同じように「商品A」シートのA3セルの文字列が表示されます(図9)。

フィルハンドルを使ってコピー

セル右下のフィルハンドルを下にドラッグして数式をコピーすると、「商品B」「商品C」シートのA3セルが参照されます(図10)(図11)。多数のシートの内容をINDIRECT関数で参照したい場合は、こちらの方法を利用するのが効率的といえます。

• (図5)「=INDIRECT("商品A!A3")」では数式をコピーしてもうまくいきません

• (図6)数式バーに「=INDIRECT(」と入力します

• (図7)A2セル(参照したいシートと同じ文字列)をクリックして数式に入力します

• (図8)「=INDIRECT(A2&"!A3")」という数式を完成させます

• (図9)[Enter]キーを押すと「商品A」シートのA3セルの文字列が表示されます

• (図10)B2セル右下のフィルハンドルを下にドラッグして数式をコピーします

• (図11)「商品B」「商品C」シートのA3セルが参照されました

SUM関数とINDIRECT関数を組み合わせる

INDIRECT関数は応用範囲の広い便利な関数です。今回は活用例として、SUM関数と組み合わせて、別シートの複数セルの数値を参照して合計値を表示させる方法を紹介します。

今回の例では、「商品A」シートのA3～C3セルに1月～3月の売上額を入力しています(図12)。「商品B」「商品C」シートも同じ構成で表を作成してあります。

式は、=SUM(INDIRECT(参照シート&"!参照開始セル:参照終了セル")

「全体」シートのセル(ここではC2)を選択したら、数式バーに「=SUM(INDIRECT」と入力し、A2セルをクリックして数式に反映させます(図13)。続けて「&"!A3:C3"))」と入力して数式を完成させます(図14)。

数式の「A2&"!A3:C3"」の部分で「商品A」シートの「A3～C3セル」を参照しています。[Enter]キーで確定させると、SUM関数で「商品A」シートのA3～C3セルの値が合計されて表示されます(図15)。フィルハンドルをドラッグして下のセルに数式をコピーすると、「商品B」「商品C」シートの合計を表示することができます(図16)。

• (図12)「商品A」～「商品C」シートのA3～C3セルに数値が入力されています

• (図13)数式バーに「=SUM(INDIRECT」と入力してA2セルをクリックします

• (図14)「=SUM(INDIRECT(A2&"!A3:C3"))」という数式を完成させます

• (図15)「商品A」シートのA3～C3セルに入力された数値の合計が表示されます

• (図16)数式をコピーすれば「商品B」「商品C」シートの合計も表示できます