INDIRECT_応用編
INDIRECT関数は、文字列で間接的にセルを参照して、そのセルの値を返してくれる関数になります。
INDIRECT関数は単体での使用はほぼなく、他の関数と組み合わせて使用するのがメインとなる関数なので、応用編として、ここでは一覧の最終行にある値を抽出する方法をご紹介していきます。
INDITRECT関数の基本の使い方は【こちら】
練習用ファイルを準備していますので、必要な方は下記よりダウンロードしてくださいね。
最終行の値を抽出する方法
データ内の最終行を求めたい場合、VBAを使わないと難しいと思われている方が多いですが、INDITRECT関数とCOUNTA関数を一緒に組むことで、VBAを使わなくても最終行の値を抽出することが可能です。
もちろん、データに増減があった場合でも、増減に応じて最終行もきちんと移動して、正しく最終行の値を抽出してくれますので、とても便利です。
練習用ファイルを使って、実際に操作しながら覚えていきましょう！
A～C列に日付、商品名、金額がリストになっています。
この一覧から、E～G列に最後に売れた日、最後に売れた商品、金額をそれぞれ表示させたい場合、リストがこれ以上増えないのであれば、セル参照方式で「A5～C5」セルを = で反映させれば良いですよね。
でも、まだリストは増えるし、何行増えるか分からないという場合、セル参照方式では先ほど指定した「A5」セルを指定したままなので、増減があるたびに手動でセル参照を変更しなければいけません。
こういう時に、INDITRECT関数で列記号は文字列で指定して、行番号のところに、空白ではないセルの数を数えてくれるCOUNTA関数を入れてあげれば、リストにデータが増えてもCOUNTA関数で行番号を返してくれるので増えた行番号を返してくれるようになります。
COUNTA関数の紹介記事は準備中です
実際にやってみましょう。
E2セルにINDITRECT関数を入力して、使用関数を確定させます。
Tab キーで使用関数を確定させたら、第1引数を入力します。
ここでは日付の列を反映させたいので、第1引数はA列になりますが、列記号は文字列で指定するので、必ずダブルクォーテーション “ で囲んであげましょう。
続けて行番号を繋ぐための ＆ を入力したらCOUNTA関数を入れて、Tab キーで使用関数を確定させます。
Tab キーで使用関数を確定させたらCOUNTA関数の第1引数を入力します。
COUNTA関数の第1引数は、空白ではないセルの数を数える場所を聞かれているので、ここではA列全体を選択します。
第1引数を指定したら、COUNTA関数のカッコを閉じます。
ここまでがINDIRECT関数の第1引数となるので、続けてINDIRECT関数のカッコも閉じてあげましょう。
最後に Enter を押せば完成です。
一番下にある12月8日が表示されました。
E2セルをコピーしてF2・G2セルに数式だけ貼付してあげましょう。
すると全てのセルに「12月8日」と表示されてしまいました。
なぜこうなるのか、F2セルを選択した状態でキーボードの F2 キーを押して数式を確認してみましょう。
列記号は文字列で入力したので、列記号はＡのままになっていますね。
セル参照と違い、文字列で指定した場合はコピーしても連動して動かないので、F2セル・G2セルのどちらもそれぞれ手動でB、Cと変更してあげる必要があります。
それぞれ列記号を修正してあげた結果、正しく反映されました。
では、先ほどと同じように6行目にデータを追加してみましょう。
今度はリストにデータが増えても自動で最後のデータを反映してくれました。
INDIRECT関数とCOUNTA関数を一緒に組むことで、最終行のセルを参照するように指定ができました。
このように、INDIRECT関数は他の関数と一緒に組むことで、かなり自由度の高いセル参照が可能になり、エクセル作業の幅が大きく広がります。
=INDIRECT("列記号"&COUNTA(行番号を数えたい列))
=INDIRECT("A"&COUNTA(A:A))
まとめ
INDIRECT関数とCOUNTA関数を一緒に組む方法をご紹介しました。
最終行に変動があっても、常に最終行の値を返してほしいケースは実務でもかなり多いですが、VBAは難しくてできないとあきらめている方も多いので、ぜひINDIRECT関数とCOUNTA関数を一緒に組む方法をマスターして、周囲に差をつけましょう！
他にもINDIRECT関数と一緒に組むことで便利な動きをしてくれる関数があるので、また別記事でご紹介したいと思います。
励みになります！高評価とチャンネル登録よろしくおねがいいたします。