新刊ピックアップ

表を調べる関数で,Excel力をアップしよう!

この記事を読むのに必要な時間:およそ 1 分

日頃から業務でExcelを使っていれば,SUM,PRODUCT,IF,VLOOKUPなどの重要関数はとっくにマスターしていることでしょう。しかし,そのような⁠エース級⁠以外にもExcelには多くの関数があり,それらをうまく活用することで,業務の効率を大幅に改善することが可能です。

たとえば,次の表のサイズは何列何行でしょうか?

小さい表ならサイズはすぐわかりますが…

画像

答えは一目瞭然,4列5行ですね。

では,表のサイズが50列100行を超えるほどの,1画面に収まらない大きさの場合は,どのように数えればよいでしょうか? いちいち画面をスクロールして最下端と最右端を表示するのは大変ですし,列名は英字なので引き算ができません。

COUNTA関数で行数・列数を数える!

そんなときは,COUNTA関数の出番です。COUNTA関数は,セル範囲内のデータの個数を数えるのが主な使い道ですが,次のようにすると行・列のデータの個数を調べることができます(調べる行・列に表以外のデータが入っていないことが前提です⁠⁠。

「A」のデータの個数を調べる

=COUNTA(A:A)

※この表では結果は5

「1」のデータの個数を調べる

=COUNTA(1:1)

※この表では結果は4

これを利用すれば,「列のデータの個数=表の行数」「行のデータの個数=列の行数」が求まり,どんな大きな表でもすぐにサイズがわかるのです!

ADDRESS関数で一番右下のセルを調べる!

さらに,行数・列数からセル名を生成するADDRESS関数を使うと,この結果を利用して表の一番右下のセルを調べることができます。次のように組み合わせれば一発です(表がセルA1から始まっている場合の例です⁠⁠。

=ADDRESS(COUNTA(A:A),COUNTA(1:1))

※この表では結果は$D$5

さて,⁠一番右下のセル」でピンと来る人は多いと思います。そう,VLOOKUP関数やDSUM関数などの関数です。これらは検索対象に表のセル範囲を指定するので,表の右下のセル名を調べる必要があるのです。そこで,上の数式を引数に利用して次のように入力してみましょう(ADDRESS関数の戻り値は文字列なので,INDIRECT関数でセル参照に変換します⁠⁠。

=VLOOKUP("商品A",A1:INDIRECT(ADDRESS(COUNTA(A:A),COUNTA(1:1))),3,FALSE)

このように,関数で表の右下のセルを計算すれば,事前に表のセル範囲を調べなくてもVLOOKUP関数を利用できます。さらに,データを追加・削除してセル範囲が変わった場合でも,数値を再入力する必要がありません!

ほかにも,右下のセルの総計値をINDEX関数で抽出したり,TRANCEPOSE関数で行・列を入れ替えたりといった活用が可能です。

地味な関数は組み合わせで輝く!

ここではCOUNTA関数とADDRESS関数を紹介しましたが,他にもROW,COLUMN,FIND,MATCH,OFFSET,MODなど,組み合わせることでもっと便利に使える関数はたくさんあります。新刊今すぐ使えるかんたんPLUS+ Excel関数組み合わせ完全大事典では,そんな珠玉の組み合わせ技を多数収録していますので,ぜひお手にとって,よりよいExcel業務のヒントにしていただければと思います。