支店別や 学年別など、Excelのシートを支店や学年ごとに分け、それぞれの指標や実績が細かく入力されるケースはよくあることですね。
例えば、シート1は「A支店」の、シート2には「B支店」のあらゆる実績が、別シートには「E支店」の、、、といった具合です。
そのうちの「特定の指標」について、1枚のシート上に各支店の数値を横一線に並べて比較したり集計したりすること、ありませんか？
そんなときに、シートをひとつずつ開いて該当するセルを、、、なんてナンセンス。
別シートの同一セルに入力されている値を参照、リスト化し集計する時に便利な関数があるのです。
今回の h1r0-style.net は、複数シートの同一セルの値を一覧にする方法について解説します。
きっと皆さんのお役に立てると思います。
別シートの値を集計する前提条件
各シートの実績表は同じレイアウトで
複数シートにまたがる「特定の指標」を効率よく取得するには、各シートの実績表のレイアウトは同じにしておく 必要があります。
その事を前提に話を進めていきます。
INDIRECT関数
別シートの同一セルに入力されている値をリストにし、集計する時に便利な関数があります。
それが INDIRECT関数 です。
INDIRECT関数とは
INDIRECT関数とは、指定される文字列への参照を返す関数で；
のように表されます。（今回は参照形式についての解説は省略します）
これだけ聞くと、普通は「なんのこっちゃ？」だと思いますが、これから分かりやすく説明します。
INDIRECTで別シートの値を参照しリスト化･集計
INDIRECT関数の使い方
では、サンプルの Excelファイルを使いながら INDIRECT関数の使い方 を見ていきましょう。
下の図のような Excelファイルがあったとします。
一番左のシートは集計用で、それ以外のシートには各支店の四半期実績が入力されいるものとします。
四半期の合計は、セルC5の値です。
▲東京シート
ちなみに「名古屋」シートも「大阪」シートも数値以外は同じフォーマットです。
▲大阪シート
さて、今日の本題は「集計」シートです。
「集計」シートは、各シートのセルC5の値を横一線に並べて比較・集計するためのシートです。
では、INDIRECT関数をつかって、別々のシートの同じ位置のセルに入力された値を抽出してリストにしてみましょう。
集計シートの仙台の値（セルB3）に数式を入力します。
INDIRECT関数を もっとカンタンに表せば「文字列で表された『参照したいシート・セルの位置』の値を返す」ということです。
INDIRECTの後につづく カッコの中には 参照したいセルの位置を書きこめばよい だけなのです。
つまり、仙台の第１四半期の数値は『仙台』シートのセルC5 の値ですから、数式はこう書き表されます；
▲こんな感じ
数式を右のセルにもコピーすることを考慮すると、C列の参照は固定したいので C5 の部分は $C5 とした方がいいでしょう。
ピンク字の部分について、もう少し解説します。
一般的に、別シートのセルを参照させる場合、セルの中には「シート名！セル範囲」となります。
シート名の後に「！」エクスクラメーションマークが入るのが特徴です。
上の表に入力した INDIRECT関数に話を戻します。
シート名と同じ値をセルに入力しておくのがコツ
ポイントの1つめは、セルB2に仙台シートのシート名と同じ「仙台」の文字があることです。
INDIRECT関数の中の B2はつまり、シート名「仙台」を表しています。
それを＆（アンド）でつないで、「！C5」と続いたということは、「仙台！C5」と書いたのと同じことで、仙台シートのセルC5を参照させる数式になっています。
INDIRECT関数の場合、カッコの中は文字列で表すルールなので、＆を使い「！C5」の部分は"（ダブルクォーテーション）ではさみます。
仙台以外の 別シートの値も抽出するため、数式を右にコピーします。
※C5の部分を＄C5に変更しておいてください。
２行目に入力されている「地域名＝シート名」をうまく利用して、別シートに入力されているセルC5の値を抽出できました。
みなさん、うまくできましたか？
以上で INDIRECT関数で別シートの同一セルにある値をリスト化･集計する方法 の解説は終了です。
コツさえつかめばもっと応用が効きますよ。
関連記事・広告
併せて読んでおくとさらに便利
エクセルで曜日別に集計する(売上·平均)｜Excelのキホン
曜日・集計
エクセルで曜日別の集計をする方法 について解説。曜日別の集計データ はマーケティングにとって欠かせない材料のひとつです。基本的な関数の組合せで、曜日別の売上や平均を算出することが可能です。
EXCELで指定月の日数を表示する方法
EOMONTH関数・DAY関数
エクセルで指定した月の日数を表示させたい時ってありませんか？ つまり、エクセルで「2016年2月」と打てば、28ではなく正確に29を返してくれる仕組みです。この手順について解説します。
COUNTIF で「を含む」データをカウントする｜Excel 関数
COUNTIF関数・ワイルドカード
COUNTIF関数を使い「～を含む」データをカウントする方法を解説。ExcelのCOUNTIF関数で任意の文字列を含むセルをカウントする にはちょっとした工夫が必要なのです。ヒントは「ワイルドカード」。
EXCELでベスト5の順位表を作る方法 Part 1
LARGE関数・RANK関数
学校の成績ベスト5、〇〇の都道府県別ベスト10。。。 日々データ分析に携わっていると、仕事でも何かと「順位」「ベスト〇位」を求められることがしばしばです。
※その他、関連記事はこの記事の下の方にもあります。
