【Excel関数】自分より上の行までで、条件にあうセルを数える

効率化

Excelで上から順にデータが並んでいる時、そのデータが自分より上の行にも存在したのか、それとも初めて出てきたものなのかを判断したい場合があります。

今回は、このような時に「自分より上の行まで」を関数の範囲として指定する方法を紹介します。

使用するのはADDRESS関数とINDIRECT関数です。

ADDRESS関数を使って、セル位置の文字列を取得する

ADDRESS関数は指定したセルの位置を「A1」などの形式の文字列にしてくれます。
この関数を使うと例えばA1から自分のセルの行までを範囲指定する「A1:A25」のような文字列を作れます。

例えば下記のような感じで数式を記載すると、そのセルの位置が「A1」形式で表示されます。

=ADDRESS(ROW(),COLUMN(),1)

ROW()、CLOUMN()は、セルの行/列のインデックスをADDRESS関数の引数に渡しています。3つ目の引数の「1」は、セルの絶対位置を返すように指定しています。

上記を使ってセルの範囲を示す文字列を作って、関数の範囲指定に使いたいところですが、実際にはできません。

例えば =COUNTA(“A1:A5") という数式は、A1~A5を範囲とした処理ができていません。

INDIRECT関数を使って、文字列をセル参照にする

文字列を関数の範囲指定に使う場合はINDIRECT関数を使います。
INDIRECT関数にセルやセルの範囲を示す文字列を渡し、その結果を別の関数の引数にすると、きちんとセルやセルの範囲として扱ってくれます。

例えば =COUNTA(INDIRECT(“A1:A5"))という数式は、A1~A5を範囲として処理ができます。

ADDRESS関数とINDIRECT関数を組み合わせて、「自分より上の行」を実現する

ADDRESS関数とINDIRECT関数を組み合わせると、自分のセルより上の行まで、という範囲指定が実現できます。
A列にデータが入力されているとして、自分より上の行にある同じ値の個数を数える数式(B2セルで入力する場合)は下のようになります。

=COUNTIF(INDIRECT(“A1:"&ADDRESS(ROW()-1,1,1)), A2)

ADDRESS関数の第一引数にROW()-1を渡すことで、自分より上の行としています。ADDRESS関数の第二引数の「1」はA列を示しています。ADDRESS関数の第三引数の「1」は絶対位置で返すように指定しています。

上記では「自分より上の行」のケースを書きましたが、「自分より下の行」や「自分より前の列」などのケースにも使えます。

Excel作業を楽にしよう

日頃、面倒だなと思っている作業も、Excelのことを少し知るだけで簡単に片付けられるようになったりします。
どんなことができるのかを知っているだけでも違うので、よくExcelを使うという方は参考書などをざっと見てみることをオススメします!

Kindle Unlimited会員なら無料で読めるExcel関連書籍を紹介しておきます。

.

Excel

Posted by moto2g