「一番下にあるデータ」を関数INDIRECTで自動参照する

今回は、表の中で「一番下にあるデータ」を自動参照する方法を紹介していこう。大まかな流れは、関数COUNTAで行数を調べて「セル番号」の文字列を生成し、それを関数INDIRECTで「セル参照」に変換する、という手順になる。現金や預金の残高をまとめた帳簿をはじめ、さまざまな場面に応用できるので、その基本的な考え方を学んでおくとよい。

「一番下にあるデータ」とは？

今回は、関数COUNTAと関数INDIRECTを使って「一番下にあるデータ」を自動取得するテクニックを紹介していこう。

文字列をセル参照に変換できる関数INDIRECT

まずは、「一番下にあるデータとは何を指しているのか？」について解説していこう。以下に示した図は、ある部署における「書籍・研修費」の入出金を管理する表だ。この会社では、自由に使える書籍・研修費として、各部署に毎月3万円を支給する制度が用意されている。

「書籍・研修費の残高」をまとめた表

この表において最も注目すべきデータは、「残高」の列にある「一番下のデータ」となる。つまり、G12セルが最も注目すべきデータという訳だ。というのも、この部分を見ることで「現在の残高」を把握することができるからだ。

この会社では、「書籍・研修費」のほかにも、稟議書なしで各部署が自由に使える経費として「消耗品費」、「出張費」、「会議費」、「接待交際費」といった経費の予算が定められている。これらの残高も、それぞれ別のワークシートに同じ形式の表で管理されている。

Excelファイル（ブック）に保存されているワークシート

一番左にある「残高一覧」のワークシートには、各経費の残高を一目で確認できる表を作成する。この表は、それぞれのワークシートから「現在の残高」に該当するセルを参照すると作成できる。

たとえば、「書籍・研修費」の「現在の残高」を表示したい場合は、「=書籍研修費!G12」とセル参照を記述すればよい。これで「書籍研修費」のワークシートにあるG12セル（現在の残高）の値を表示できるようになる。

別のワークシートにあるセルの参照

このように、「!」の前に「シート名」を記述してセル参照を指定すると、別のワークシートにあるデータを参照することが可能となる。まだ知らなかった人は、この機会にぜひ覚えておくとよいだろう。

同様の手順で、それぞれの経費について「現在の残高」を参照していくと、以下の図のような表が完成する。

各経費の予算残高をまとめた表

これで「各経費の残高を一目で確認できるようになった」と言いたいところであるが、実はこの方法には重大な欠陥が潜んでいる。それは、新しいデータの追加に対応できないことだ。

たとえば、3月1日に「書籍・研修費」として新たに3万円が支給されたとしよう。これを「書籍・研修費」の表に追加すると以下の図のようになり、「現在の残高」は37,113円に増える。

「書籍・研修費」のデータを追加した例

一方、「残高一覧」の表はどうかというと、「書籍・研修費」の残高は7,113円のまま更新されていない。

各経費の予算残高をまとめた表

それもそのはず。このセルが参照しているのは「書籍・研修費」のG12セルであり、「現在の残高」を示すG13セルではないからだ。これを修正するには、セル参照を「=書籍研修費!G13」に書き直す必要がある。

つまり、データを追加する度に「残高一覧」のセル参照を修正しなければならない・・・、という非常に使い勝手の悪い表になってしまう。そこで、関数を使って「一番下にあるデータ」を自動取得するように改良してみよう、というのが今回の連載の趣旨となる。

関数COUNTAと関数INDIRECTによるセル参照の生成

それでは、具体的な手順を解説していこう。まずは、「現在の残高」を表示するセルを以下の図のように用意する。

「現在の残高」を表示するセルを作成

続いて、「一番下にあるデータ」の行番号を取得する。この処理には「関数COUNTA」を使用する。関数COUNTAは、指定したセル範囲内に「空白でないデータが何個あるか？」を調べてくれる関数だ。第4回の連載で紹介した「関数COUNT」とよく似ているが、関数COUNTAの場合は「数値データ」だけでなく「文字データ」もカウントの対象になる。

今回の例では「残高」の列に「数値データ」しかないため関数COUNTを使用しても構わないが、「文字データ」が含まれるケースにも対応できるように、ここでは関数COUNTAを使ってみよう。

関数COUNTAの引数には、「残高のデータのセル範囲」すなわち「G3:G22」を指定する。これでG3～G22のセル範囲に「データが何個あるか？」を調べられる。続いて、この数値に2を足し算するすと、「一番下にあるデータ」の行番号に求められる（※）。

（※）今回の例では、「指定したセル範囲」より上に2つの行がある。よって、「データの個数」に2を足すと行番号に変換できる。