ブックとシートの使い分けで広がるExcel“深”世界 第3回

仕事に役立つ! 複数シートの検索＆合算技――応用編 Part.2

前月の売上シートを参照して売上の累計を求めるスゴ技

　ここでは、売上集計表を月別のシートに作成して、売上合計の累計を表示します。

　基本的には、前月シートのセルを参照すればいいのですが、シートを参照するには必ずシート名を指定しなくてはならず、「前シート」のような相対的な指定はできません。そのため、各月ごとに数式を入力しなければならないのです。

　ところがこのスゴ技を使えば、「前シート」というように相対的なシート参照ができるのです。

完成形――こんなExcelシートを作ります

当月までの販売累計を自動計算して表示するシートの完成形

　ここでは、「GET.WORKBOOK」と「GET.DOCUMENT」というマクロ関数を利用します。

　これらは本来、マクロシートでしか使えない関数ですが、「名前」に定義すれば、ワークシートでも利用できます。

「名前の定義」で式を設定する手順

「名前の定義」を一覧表示する式を設定する

　メニューバーから［挿入］→［名前］→［定義］を選びます。

　(1)「名前」欄に「シート名一覧」と入力します。

　(2)「参照範囲」欄に「=GET.WORKBOOK(1)＆T(NOW())」と入力（すべて半角で）。これはブック内のシート名一覧を取得するマクロ関数です。

　(3)［追加］ボタンをクリック。名前の一覧に「シート名一覧」が追加されました。

同様にして「シート番号」を求める式を設定する

　(4)「シート番号」と入力します。

　(5)「=GET.DOCUMENT(87)＆T(NOW())」と入力（すべて半角で）。これは表示中のシートがそのブックのなかで何枚目にあるかを求めるマクロ関数です。

　(6)、(7) をクリックして完了です。

累計の式を入力する手順

累計を求める式を入力する。1月のシートには、前年度の累計数値と今月（1月）の数字を合算する式が入力してある

　(1)「1月」シートを表示します。

　1月は先頭シートで前のシートがないので、(2) 7行目には前年度の販売累計の数値と今月の「合計」（6行目）を足す式を入力してあります。

2月から6月まで同じ形の表なので、式をまとめて入力してしまおう

　(3)「2月」のシート見出しをクリックしたあと、(4)［Shift］キーを押しながら「6月」のシート見出しをクリックします。

　(5) 累計を表示したい範囲（C7:E7）を選択します。

2月から6月まで同じ形の表なので、式をまとめて入力してしまおう

　キーボードから(6)「=INDIRECT(INDEX(シート名一覧,シート番号-1)＆”!”&CELL(”ADDRESS”,C7))+C6」と入力（すべて半角で）して、［Ctrl］＋［Enter］キーで確定します。選択範囲に式が入ります。

　「1月」のシート見出しをクリックして複数シートの選択を解除すれば完了です。

C7セルに入った式は？

　「シート名一覧」にはブック内のシート名の一覧が、「シート番号」には数式を入力したシート番号が取得できます。これらを使いINDEX関数で前シートのシート名を取得します。

　あとはCELL関数でセル番地を生成して、前シートの累計セルを参照すればOKです。

関数名

検索/行列関数

書式

=INDIRECT(参照文字列[,参照形式])

関数名

検索/行列関数

書式

=INDEX(範囲,行番号,列番号[,領域番号])

知ッ得 5――
シート名一覧を取り出す関数の便利な使い方とは？

　「GET.WORKBOOK(1)」とすると、シート名の一覧を配列として取得できます。

　INDEX関数で「シート名一覧」から、番号を指定することで各シート名を取り出せます。

　なお、「&T(NOW())」としているのは、自動的に再計算するための工夫です。

　（次ページ、「マウスクリックで今日の日付に飛ぶ「週間スケジュール帳」を作る」に続く）