Excel関数で別シートのセルを指定する INDIRECT関数を使うと簡単に別シートのセルを指定することができます

シェアする

  • このエントリーをはてなブックマークに追加

Excelで関数を使う時には、別シートのセルを指定することもよくあります。シート内のセルの時と少し勝手が違うので、注意すべき点をおさえておきましょう。

スポンサーリンク
自動サイズレスボンシブ

Excel上のセル指定のルール

Excelで関数を使う時に入力するデータには、「数値」「文字データ」の他に、 
「A1」「B5」など、セルの場所を指定する「セル番地」があります。


「セル番地」を指定すると言うことは、


「指定されたセルのデータを使いなさい」という指示を出していることになります。


たとえば、このような表があるとします。

スクリーンショット 2015 02 16 23 32 34


赤で囲ったセルに、SUM関数で得票数の合計を表示することを考えます。


SUM関数は、指定したセルの数値を合計する関数ですから、

スクリーンショット 2015 02 16 23 37 12


このようにセル番号を指定すれば、


「D3セルからD14セルまでのデータを使いなさい」


という指示になって、正しく合計してくれます。


確かに、これでセル番地は正しく指定できているのですが、 
実は、普段、当たり前のように使っている、「D3」や「D14」のような、セル番地だけを入力する方法には、 
ある情報が省略されていることを覚えておく必要があります。


もともと、Excel上でセル番地を指定するためには、セル以外に2つの情報が必要です。


1つは、ブック
もう1つが、シート


です。

スクリーンショット 2015 02 17 1 12 58
(ブックとは、この「ファイル」のことを指します。)


なぜ、セル以外の情報として、上の2つが必要になるかというと、 
Excelは、その重要な仕組みとして「階層構造」を持っているからです。


「階層構造」というのは、Excelの中で場所を示す時には、 
大きな単位から順番に指定しなければいけない、というルールのこと。


具体的には、


ブック → シート → セル


の順番で指定します。


つまり、「セルを指定する」場合には、 
その前に「ブック((例)book1、book2など)」「シート((例)sheet1、sheet2など)」を指定するのが、本来のルールなのです。


では、なぜ、「D3」や「D14」のように、セル番地だけを入力しても、正しく指定できるかというと、特に指定が無ければ、


今、開いているブックの、 
今、開いているシートが、 


自動的に指定されているからです。


Excelでセル指定する時は、ブック、シート、の指定が必要
ブック、シートの指定がなければ、「今開いているブック&シート」が自動的に指定される


この2点を理解しておけば、セルの指定がスムーズになります。


これを踏まえて、関数の中で、別シートのセルを指定する方法を考えます。

別シートのセルを指定する方法

先ほどの表を、別シートで集計することを考えます。


4つのシートがあって、

スクリーンショット 2015 02 17 1 31 11


「201503」以下のシートには、

スクリーンショット 2015 02 17 1 34 51


このような表が作成され(形は全て同じ。数字だけ異なります)、


Summaryシートで、

スクリーンショット 2015 02 17 1 34 06


SUM関数を使い、「201503」、「201502」、「201501」シートの得票数の合計をもとめます。


ポイントはシートの指定の仕方です。 
「201503」シートを指定します。


SUM関数が入力されているセルを見て下さい。

スクリーンショット 2015 02 17 2 00 52


ご覧の通り、


’ ’」(クォーテーションマーク)で、「シート名」を囲み


「!」(エクスクラメーション)で、セル番地とつなぐ、


ことで、シートを指定できることが分かりました。


実際に、指定したいセルをドラッグすれば、シート名も含めて指定できるのですが、


「シート名の指定方法」として、何を、どのように入力するかは、 知っておくべきです。


次に説明する、より簡単な方法で、別シートのセルを指定する時に役立ちます。

INDIRECT関数を使って、簡単に別シートを指定する

別シートのセルを指定する際の、シートの指定方法を見てきました。


入力方法はわかりましたが、いちいちシート名を入力する方法は、面倒ですし、


指定するシートのセルに移動してドラッグするのも、シート数が増えると、やはり、面倒です。

スクリーンショット 2015 02 17 2 24 55


そこで、シート数が増えた場合にでも、簡単にシートを指定できる方法を考えます。


INDIRECT関数を使う方法です。


最初に、INDIRECT関数を使って入力する際のポイントを押さえておきます。


・INDIRECT関数でシートとセルのデータをまとめて指定する。 
 =SUM(INDIRECT(D2!$D$3:$D$14))


・セルを「” ”」で囲んで文字データにして、 
 シートのデータと「&」でつなぐ 
 =SUM(INDIRECT(D2&”!$D$3:$D$14))


この2点です。 
順番に説明します。


INDIRECT関数はセルに入力された「シート名」や「セル番号」を 
Excel上の場所を示す、シート名やセル番号に変換してくれる関数です。


たとえば、C2セルに「B2」と入力したとします。

スクリーンショット 2015 02 17 2 36 11


すぐ下のC3セルに、「=C2」と入力して、

スクリーンショット 2015 02 17 2 39 06


「C2セルのデータを使う」ように指定すると、

スクリーンショット 2015 02 17 2 42 00


C2セルに入力された「B2」を表示します。


ところが、INDIRECT関数でC2セルを指定すると、


C2に入力されたデータ”B2”は、「B2」というデータから、


シート名やセル番号などのExcel上の”場所”を表すデータに変換されますから、

スクリーンショット 2015 02 17 2 48 29


「B2セルのデータを使いなさい」という指示に変わります。

スクリーンショット 2015 02 17 2 53 27


その結果、B2に入力されている「ヴィヴィくん」が表示されます。


この、INDIRECT関数を使って、セルに入力されたシート名を、 
関数で指定するシート名として使ってしまいましょう。

スクリーンショット 2015 02 17 3 02 45


まず、シート名が入力されているセルをINDIRECT関数で指定するところからスタートします。


元の式が、

=SUM(‘201502’!$D$3:$D$14)


ですから、赤字の部分ですね。 
ここを、INDIRECT関数で指定します。

スクリーンショット 2015 02 17 3 11 54


これで良さそうですが、このままでは、INDIRECT関数で指定した「シート」と


「$D$3:$D$14」で入力された「セル」とが、バラバラになって、 
「$D$3:$D$14」は単独で入力されていることになってしまいます。


つまり、シートは「201502」が指定されているのに、 
セルは、「今開いているシート(=Summaryシート)」のセル 
を指定することになってしまうので、正しくセルが指定できていないことになるのです。


そこで、修正を加えます。 
「シート」だけでなく、「セル」まで含めた、ひとまとまりを、INDIRECT関数で指定します。

スクリーンショット 2015 02 17 3 29 03


=SUM(INDIRECT(D2!$D$3:$D$14))


こうなりました。


これで上手くいきそうですが、もうひとつ。


ひとまとまりにしたのは良いのですが、今度はINDIRECT関数の中が整理できていません。


「シート」!「セル」


のつなぎ方では、INDIRECT関数の方が正しく認識できないのです。


そこで、「セル」の方を、「” ”」(ダブルクォーテーション)で、文字データにして、


「シート」との間を「&」でつなぐことで、


「シート」と「セル」を指定したデータであることを、INDIRECT関数に認識させます。

スクリーンショット 2015 02 17 3 36 43


=SUM(INDIRECT(D2&”!$D$3:$D$14″))

こうなりました。


最後に、もう一度まとめると、


・INDIRECT関数でシートとセルのデータをまとめて指定する。 
 =SUM(INDIRECT(D2!$D$3:$D$14))


・セルを「” ”」で囲んで文字データにして、 
 シートのデータと「&」でつなぐ 
 =SUM(INDIRECT(D2&”!$D$3:$D$14))


これでOKです。


このように指定できれば、

スクリーンショット 2015 02 17 3 55 03


数式をコピーするだけでシート名が、上にあるセルのデータ(201501)に変わるので、いちいちシート名を入力する必要がなくなります。


シートが、何10枚増えても、コピーするだけで、シート名の入力ができるのです。

まとめ

Excel上の場所を指示する時の基本的なルール、「階層構造」をおさえておきましょう。 
その上で、INDIRECT関数を使えば、別シートのセルの指定がスムーズになります。

<おまけ>
今回触れた、Excelの「階層構造」は、マクロでも重要な考え方ですので、覚えておいて損はありません。

スポンサーリンク
自動サイズレスボンシブ
自動サイズレスボンシブ

シェアする

  • このエントリーをはてなブックマークに追加

フォローする