A6からD10番地までには、1月から3月までの各店舗のデータが表としてまとめられています。
その中で、B7からD7番地は仙台店のデータなので、「仙台店」という名前を、
B8からD8番地には「那須店」という名前をあらかじめ設定しています。
同様にしてB9からD9番地には「京都店」、B10からD10番地には「福岡店」という名前を設定しています。
そして、これら店舗の合計と平均が表示されるようにしたいのですが、表形式で各店舗の合計と平均を表示させるのではなく、B1番地の店舗名を変更すると、合計と平均もその店舗のものに切り替わるように設定してみます！
合計する範囲、平均する範囲が切り替わるようにする
まず、合計を出したい場合はおなじみのSUM関数を使います。
SUM関数は、上の図のような数式になるので、
もし仙台店の3ヶ月分の合計を出したい場合には、SUM関数で指定する範囲をB7からD7番地に設定すればいいことになります。
でも、もともとこのB7からD7番地には、「仙台店」という名前が設定してあるので、
仙台店の3ヶ月分の合計を求める数式は、図のようになります。
もちろん、他の店舗のデータ範囲にも、それぞれ名前が設定されているので、
数式の名前の部分を変えれば、各店舗の合計を出すことができるわけです。
そしてその合計を出す範囲の名前は、B1番地に入力することになるので、「B1番地に入力されている名前の範囲を合計してね」という数式にすれば、B1番地の店舗名を変更するたびに、その店舗の合計が表示されることになります。
ところがそうなると、図のような数式を組み立てたくなるわけですが、これが間違い。
これは「B1番地に入っているものを合計してね」という数式。
「B1番地に入っているもの」は「「仙台店」という文字」なので、この数式だと「「仙台店」という文字を合計してね」ということになってしまいます。
足し算できるような数字が入力されているわけではないので、「数字は無いよ」と、「0」という答えが表示されてしまいます。
そうです、合計してほしいのはB1番地に入力されているデータそのものではなく、B1番地に入力されているものと同じ名前が付いたセル範囲。
ということは、B1番地に入力されているものを、ただの文字ではなく、セルに付けられた名前として認識してほしい、ということをExcelさんにお伝えする必要があるんです！
ここで出てくるのがINDIRECT関数。
INDIRECT関数は、指定した文字を、ただの「文字」としてではなく、「セルの名前」として認識してね、という関数。
今回は、B1番地に入力されている文字を、セルの名前として認識してほしいわけですから、
とすれば、B1番地に入力されているものを、普通の文字としてではなく、セル範囲として認識してね、という設定にできます。
そのB1番地に入力されているのは「仙台店」という文字。
INDIRECT関数を使うことで、B1番地に入っている「仙台店」を、「仙台店」という文字としてではなく、「仙台店」という名前の付いたセルとして認識させてね、とできます。
この文書で「仙台店」という名前に予め設定しておいたのはB7からD7番地なので、
B1番地に入力されている「仙台店」とは、B7からD7番地のことなんだ、とExcelさんにお伝えすることができるわけです。
そして、このセル範囲として認識できるようにしたB1番地の名前の範囲を合計したいわけですから、このINDIRECT関数の数式と、
合計を出すSUM関数を組み合わせ、
とすれば、B1番地に入力されている店舗の合計を出すことができるというわけです。
スポンサーリンク
数式の設定
それでは早速この数式を、
B2番地に設定してみることにしましょう！
B2番地を選択し、［関数の挿入］ボタンをクリックします。
関数を指定するためのダイアログボックスが表示されます。
この時、SUM関数を選ぶべきなのか、INDIRECT関数を選ぶべきなのか悩んだりしますが、ポイントは「結果として表示させたい関数を先に選ぶ」です。
答えとして表示させたいのは、合計なので、合計を計算するSUM関数を選びます。
［関数の分類］で「すべて表示」を選択し、［関数名］欄で「SUM」を選択。
［OK］ボタンをクリックします。
ちなみに、この一覧から「SUM」を選択する際、「SUM」の頭文字である「S」のところまで、一瞬でスクロールする技を使うと便利です。
前のダイアログボックスで「SUM」を選択して［OK］ボタンをクリックすると、このようなダイアログボックスが表示されます。
この［数値1］欄に合計したい範囲を設定します。
今回の場合、合計したい範囲は、B1番地に入力されている名前が付いたセル範囲で、そのセル範囲はINDIRECT関数を使って指定するわけですから、この［数値1］欄にINDIRECT関数を入れ込んでいきましょう。
［数値1］欄に文字カーソルがある状態で、数式バーの左端にある［▼］をクリックすると、
最近使った関数が一覧表示されます。
この一覧の中に「INDIRECT」があれば、クリックで選択します。
（この一覧の中に「INDIRECT」があった方は次の図の、関数を選択するダイアログボックスからINDIRECT関数を選択する操作は不要なので、次の次の図から操作を続けてください。）
もし、一覧の中に「INDIRECT」がない場合には、単純に最近使っていないというだけなので、一覧の一番下にある「その他の関数」をクリックで選択すると、
関数を選択するダイアログボックスが表示されるので、この一覧から「INDIRECT」を選択し、［OK］ボタンをクリックします。
先ほどまでSUM関数のものだったダイアログボックスが、INDIRECT関数のものに変わり、数式バーに表示されている数式にも、SUM関数の中にINDIRECT関数が入っていることが分かります。
それではINDIRECT関数の設定を行っていきましょう。
［参照文字列］欄には、どの文字を、そういう名前のセル範囲として認識してほしいのかを指定します。
今回はB1番地に入力されているものを、そういう名前のセル範囲として認識してほしいわけですから、この欄には「B1」と設定します。
この時、「B1」と手入力しても構いませんし、［参照文字列］欄に文字カーソルがある状態で、
実際のB1番地をクリックすれば、手軽に「B1」と設定することができます。
（次の［参照形式］欄は省略します。詳しくは「INDIRECT関数の基本」でご紹介しています。）
最後にダイアログボックス右下の［OK］ボタンをクリックします。
B1番地に入力されている名前の付いた範囲の合計を表示することができました！
合計の数式を入力したB2番地を選択した状態で数式バーを見ると、先ほどまとめた通りの数式が入力されていることも確認できます！
スポンサーリンク
平均も考え方は同じ
B1番地に入力されている名前の付いた範囲を合計する数式はこうだったわけですが、もし合計ではなく平均を出したいのなら、この数式のSUM関数の部分を、
AVERAGE関数に変えればいいだけ。
もちろん数式の設定の仕方も、選択する関数が違うだけで、あとは同じです。
それでは早速、この数式を、
B3番地に設定してみることにしましょう！
B3番地を選択し、［関数の挿入］ボタンをクリックします。
関数を指定するためのダイアログボックスが表示されます。
この時、AVERAGE関数を選ぶべきなのか、INDIRECT関数を選ぶべきなのか迷っても、「結果として表示させたい関数を先に選ぶ」がポイントになるのは変わりません。
答えとして表示させたいのは、平均なので、平均を計算するAVERAGE関数を選びます。
［関数の分類］で「すべて表示」を選択し、［関数名］欄で「AVERAGE」を選択。
［OK］ボタンをクリックします。
前のダイアログボックスで「AVERAGE」を選択して［OK］ボタンをクリックすると、このようなダイアログボックスが表示されます。
この［数値1］欄に平均したい範囲を設定していくわけですが、既にExcelさんが「B1からB2番地の平均を出すんじゃないの？」と気を利かせて範囲を設定してくれています。
ですが今回の場合、平均したい範囲は、B1番地に入力されている名前が付いたセル範囲で、そのセル範囲はINDIRECT関数を使って割り出すわけですから、この［数値1］欄にINDIRECT関数を入れ込んでいくことになります。
というわけで、Excelさんが気を利かして設定してくれた［数値1］欄を消してしまいます。
そして、［数値1］欄に文字カーソルがある状態で、数式バーの左端にある［▼］をクリックすると、
最近使った関数が一覧表示されます。
先ほど使ったばかりなので、この一覧の中に「INDIRECT」があるはずなので、それをクリックで選択します。
（もし先ほどの合計を出さずにこの操作をしている場合など、この一覧の中に「INDIRECT」がない場合には、単純に最近使っていないというだけなので、一覧の一番下にある「その他の関数」をクリックで選択し、その後に表示されたダイアログボックスで「INDIRECT」を選択し、［OK］ボタンをクリックしてください。）
先ほどまでAVERAGE関数のものだったダイアログボックスが、INDIRECT関数のものに変わり、数式バーに表示されている数式も、AVERAGE関数の中にINDIRECT関数が入っていることが分かります。
それではINDIRECT関数の設定を行っていきましょう。
［参照文字列］欄には、どの文字を、そういう名前のセル範囲として認識してほしいのかを指定します。
今回はB1番地に入力されているものを、そういう名前のセル範囲として認識してほしいわけですから、この欄には「B1」と設定します。
この時、「B1」と手入力しても構いませんし、［参照文字列］欄に文字カーソルがある状態で、
実際のB1番地をクリックすれば、手軽に「B1」と設定することができます。
（次の［参照形式］欄は省略します。詳しくは「INDIRECT関数の基本」でご紹介しています。）
最後にダイアログボックス右下の［OK］ボタンをクリックします。
B1番地に入力されている名前の付いた範囲の、平均を表示することができました！
平均の数式を入力したB3番地を選択した状態で数式バーを見ると、先ほどまとめた通りの数式が入力されていることも確認できます！
合計する範囲、平均する範囲を切り替えてみる
今回は、B1番地に入力されている名前の付いた範囲の合計や、平均を求める数式を組み立てたので、B1番地の文字を他の店舗の名前に変更し、文字カーソルが表示されなくなるまで［Enter］キーを押して入力を確定すると、
その名前の付いた範囲の合計や平均の表示に切り替わります！
そして更に工夫を加えて、B1番地の文字をドロップダウンリストで選択できるようにしておけば、もっと手軽に切り替えることができます！
Officeの使いこなしテクニックをメールでお届け！