「一番下にあるデータ」を関数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を足すと行番号に変換できる。

• 

  関数COUNTAの入力

「Enter」キーを押すと「\12」という結果が表示される。現時点において「一番下にあるデータ」は12行目にある。この行番号を正しく取得できていることを確認できるだろう。

• 

  関数COUNTAを使った「行番号」の取得

なお、今回の例ではG3～G22のセル範囲に「通貨」の表示形式が指定されているため、この書式がそのまま引き継がれて「\」の記号を付けた形で数値が表示されている。

続いては、この数値を「セル番号」に変換する。「残高」はG列にあるので、先頭に"G"の文字を追加してあげると「セル番号」に変換できる。この処理には、データを結合する「&」（アンド）を使用する。

先ほど記述した「COUNTA(G3:G22)+2」をカッコで囲み、その前に「"G"&」と記述する。

• 

  「&」を使った「列番号」の追加

このように記述を変更して"G"の文字を先頭に追加すると、セルの値を"G12"に変更できる。

• 

  「一番下にあるデータ」のセル番号

これで「一番下にあるデータ」のセル番号を取得できた。ただし、現時点では"G12"という文字列を生成したに過ぎないため、このままでは「セル参照」として機能してくれない。

文字列を「セル参照」として機能させたいときは「関数INDIRECT」を使用する。この関数を使うと、カッコ内（引数）に指定した文字列を「セル参照」として機能させることが可能となる。

具体的には、先ほどの記述全体をカッコで囲み、関数INDIRECTの引数に指定してあげればよい。これで"G12"の文字列を「セル参照」（=G12）と機能させられるようになる。

• 

  関数INDIRECTで「セル参照」に変換

「Enter」キーを押して結果を見ると、「一番下にあるデータ」の値（\7,113）がそのまま表示されていることを確認できるだろう。

• 

  「一番下にあるデータ」の自動参照

念のため、動作を検証しておこう。以下の図のようにデータを1行追加すると、関数COUNTAの値が1つ増えることになり、その結果、自動的にG13セルが参照されるようになる。

• 

  データを追加した例（1）

同様に、2行分のデータを追加した場合は、関数COUNTAの値が2つ増えることになるため、セル参照もG14に自動変更される。

• 

  データを追加した例（2）

このように「関数COUNTA」と「関数INDIRECT」を組み合わせると、「一番下にあるデータ」を自動取得することが可能となる。

各ワークシートの状況をまとめた表の作成

「一番下にあるデータ」の自動取得は、「最新データを記したセルを固定化できる」という観点においても意味のあるテクニックとなる。本稿の冒頭で「残高一覧」の作成について触れたが、このときに生じる問題も即座に解決できる。

前述した手順で「一番下にあるデータ」を自動取得しておくと、「書籍・研修費」の「現在の残高」を常にG23セルに固定化することが可能となる。つまり、参照先にG23セルを指定するだけで「常に最新の情報」を表示できる訳だ。

• 

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

先ほど「書籍・研修費」に2行分のデータを追加しているので、「現在の残高」は\30,953になる。この値が正しく参照されていることを確認できるだろう。

• 

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

銀行の預金残高、経理で使う帳簿など、「一番下にあるデータ」が重要な意味を持つケースは意外と多い。このような場合に今回のテクニックを活用すると、データ処理の応用範囲を広げられるはずだ。ぜひ、参考にして頂ければ幸いだ。