【Excel関数】自分より上の行までで、条件にあうセルを数える
Excelで上から順にデータが並んでいる時、そのデータが自分より上の行にも存在したのか、それとも初めて出てきたものなのかを判断したい場合があります。
今回は、このような時に「自分より上の行まで」を関数の範囲として指定する方法を紹介します。
（一部更新しました）
- 関数の説明や、使用状況に合わせて変更する箇所の説明を追記しました。
- Excelにそのままコピペできるようにwordpressの設定を変更しました。(ダブルクォーテーションの開始が自動変換されるのを防止)
使用するのはADDRESS関数とINDIRECT関数です。
ADDRESS関数を使って、セル位置の文字列を取得する
書式
ADDRESS(行番号, 列番号, 参照の種類)
- 行番号 … 1行目を示すなら「1」、2行目を示すなら「2」のようにする
- 列番号 … A列を示すなら「1」、B列を示すなら「2」のようにする
- 参照の種類 … 絶対位置表示なら「1」、相対位置表示なら「4」のようにする
※引数は他にもありますが、今回は使わないので省略
説明
ADDRESS関数は指定したセルの位置を「A1」などの形式の文字列にしてくれます。
この関数を使うと例えば「A1から自分のセルの行まで」を範囲指定する「A1:A25」のような文字列を作れます。
例えば下記のような感じで数式を記載すると、そのセルの位置が「A1」形式で表示されます。
=ADDRESS(ROW(),COLUMN(),1)
ROW()、CLOUMN()は、セルの行/列のインデックスをADDRESS関数の引数に渡しています。
3つ目の引数の「1」は、セルの絶対位置を返すように指定しています。
上記を使ってセルの範囲を示す文字列を作って、関数の範囲指定に使いたいところですが、実際にはできません。
例えば =COUNTA("A1:A5") という数式は、A1～A5を範囲とした処理ができていません。
INDIRECT関数を使って、文字列をセル参照にする
書式
INDIRECT(参照文字列)
- 参照文字列 … A1セルを参照したいのなら「"A1"」、B2セルを参照したいのなら「"B2"」のようにする
※引数は他にもありますが、今回は使わないので省略
説明
文字列を関数の範囲指定に使う場合はINDIRECT関数を使います。
INDIRECT関数にセルやセルの範囲を示す文字列を渡し、その結果を別の関数の引数にすると、きちんとセルやセルの範囲として扱ってくれます。
例えば =COUNTA(INDIRECT("A1:A5"))という数式は、A1～A5を範囲として処理ができます。
ADDRESS関数とINDIRECT関数を組み合わせて、「自分より上の行」を実現する
書式
=COUNTIF(INDIRECT("カウントしたい最初のセル:" & ADDRESS(ROW()-1, カウントしたい列の列番号, 1)), カウントしたい値)
今回のケースで使う場面によって変更が必要なのは黄色アンダーラインの箇所です。
- カウントしたい最初のセル: … A列の1行目からカウントするなら「A1」とする
- カウントしたい列の列番号 … A列をカウントするのなら「1」とする
- カウントしたい値 … COUNTIFの条件なので、「あいう」を数えるなら「"あいう"」とする。どこかのセルの値を使うのならそのセルを指定する
また、「自分より上の行」というのを「自分の行も含める」のなら、緑色アンダーラインの箇所を「ROW()」にします。
説明
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関連書籍を紹介しておきます。
.
