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の「階層構造」は、マクロでも重要な考え方ですので、覚えておいて損はありません。

タイトルとURLをコピーしました