モーリーのメモ

アプリ開発等（プログラミング、CG作成）、興味を持ったことを実践してまとめるブログです。

モーリーのメモ

COUNTIFS関数でグループ別・除外等の条件付きの順位を付ける！：Excel

Excel

この記事の内容
  • Excelで『COUNTIFS関数』を使って表データに条件付きの順位を計算します。
　　
　単純に順位を付ける場合は『RANK関数』を使いますが、『COUNTIFS関数』を使うと『グループ別』や『対象を限定』等の条件が付いた順位を計算することが出来ます。
　
  • 例：20歳未満を対象に、AとBのグループ別に順位を付ける
    f:id:mmorley:20170530100556p:plain
　
　D2のセルの式は次の通りです。式の解説は後で詳しく行います。

    =IF(B2<20,COUNTIFS(A$2:A$7,A2,$B$2:$B$7,"<20",C$2:C$7,">"&C2)+1,"対象外")

　
　注意：『COUNTIFS関数』が使えるのは『Excel2007』以降です。
　それ以前のバージョンでは『SUMPRODUCT関数』を使いますが、2007以降であれば『COUNTIFS関数』のほうが計算速度が速いのでおすすめです。
　
　以降で、詳しいやり方を説明します。

目次

使用環境

　今回使用した環境です。

COUNTIFS関数について

　『COUNTIFS関数』は順位付け専用の関数ではありません。『複数（1つでも可）の条件に一致した行数（または列数）を数える』関数です。
　例えば『A列の値が1かつB列の値が2の行が何行あるか数える』ことが出来ます。
　使い方は次のとおりです。

    =COUNTIFS(範囲1,検索条件1,[範囲2,検索条件2,…])　※[]内は省略可能

　範囲と検索条件を1組として、最大127組まで指定できます。

COUNTIFS関数の検索条件の書き方

　『検索条件』の書き方が少し変わってるので説明します。

  • 比較演算子（>,<,>=,<=,=,<>）と比較する値を『"（ダブルコーテーション）』で囲む
    用例意味
    =COUNTIFS($A$2:$A$7,">=20")A2～A7で値が20以上のセルの数
    =COUNTIFS($A$2:$A$7,"<>はてな")A2～A7で値が"はてな"ではないセルの数
  • 『=（イコール）』は省略可
    • 数値は『"（ダブルコーテーション）』も省略可

    =COUNTIFS($A$2:$A$7,"はてな") A2～A7で値が"はてな"のセルの数
    =COUNTIFS($A$2:$A$7,20) A2～A7で値が20に等しいセルの数

  • セルは『"（ダブルコーテーション）』で囲まない
    • 比較演算子とセルは『&（アンド）』でつなげる

    =COUNTIFS($A$2:$A$7,A2) A2～A7で値が20に等しいセルの数
    =COUNTIFS($A$2:$A$7,">"&A2) A2～A7で値がA2の値より大きいセルの数

順位付けの仕組み

　順位（降順）は、言い換えると『"自分よりポイントの高い人"の数に１を足したもの』です。
　　1位は、『自分よりポイントが高い人が0人』+1で、1位
　　2位は、『自分よりポイントが高い人が1人』+1で、2位
　といった感じです。
　つまり『自分よりポイントが高い人』を『COUNTIFS関数』で数えて1を足すことで順位が計算出来ます。

順位付けの手順

　まずは条件のない単純な順位付けを行います。
　次の表に順位を付けます。

  1. 下記の表全体を選択して右クリック→『コピー』をクリックします。
    ポイント順位
    80
    65
    90
    85
    70
    75
  2. ExcelのシートでA1のセルを右クリック→『形式を選択して貼り付け』をクリックします。
    f:id:mmorley:20170603132514p:plain:w250
  3. 『貼り付ける形式』で『テキスト』を選択して『OK』をクリックします。
    f:id:mmorley:20170519103402p:plain:w350
  4. B2のセルに『=countifs』と入力して、キーボードの『TAB』押します。
    f:id:mmorley:20170524152708g:plain
  5. A2～A7セルをドラッグして選択して、キーボードの『F4』を1回押します。
    ＊『F4』を押す度に『$A$2（列行固定）』→『A$2（行固定）』→『$A2（列固定）』→『A2（固定なし）』と循環します。固定すると式をコピーした時に勝手にセルの位置が変更されません。
    f:id:mmorley:20170524175630g:plain
  6. 続けて『,">"&A2)+1』を入力して、キーボードの『Enter』を押します。
    f:id:mmorley:20170529141725g:plain
    　最終的に下記の式になります。

    =COUNTIFS($A$2:$A$7,">"&A2)+1

  7. B2のセルの枠の右下をドラッグして、B3～B7のセルにコピーします。
    f:id:mmorley:20170529141754g:plain
　以上で、次のように順位が入力されます。
    f:id:mmorley:20170517233302p:plain

グループ別の順位を付ける

　グループ別に順位を付ける場合は、『グループが自分と同じ人』という条件を追加します。
　『自分よりポイントが高い人』かつ『グループが自分と同じ人』を『COUNTIFS関数』で数えて1を足すことで順位を計算します。
　
　次の表に順位を付けます。

  1. 下記の表全体を選択して右クリック→『コピー』をクリックします。
    グループポイントグループ別順位
    A80
    A65
    A90
    B85
    B70
    B75
  2. ExcelのシートでA1のセルを右クリック→『形式を選択して貼り付け』をクリックします。
  3. 『貼り付ける形式』で『テキスト』を選択して『OK』をクリックします。
  4. C2のセルに下記の式を入力して、キーボードの『Enter』を押します。

    =COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,">"&B2)+1

  5. C2のセルの枠の右下をドラッグして、C3～C7のセルにコピーします。
　
　以上で、次のように順位が入力されます。
    f:id:mmorley:20170530104057p:plain

『20歳未満を対象』という条件を追加する

　『グループが自分と同じ人』かつ『年齢が20未満の人』かつ『自分よりポイントが高い人』を『COUNTIFS関数』で数えて>1を足すことで順位を計算します。
　次の表に順位を付けます。

  1. 下記の表全体を選択して右クリック→『コピー』をクリックします。
    グループ年齢ポイントグループ別順位（20歳未満）
    A1880
    A1765
    A1990
    B2085
    B1870
    B1775
  2. ExcelのシートでA1のセルを右クリック→『形式を選択して貼り付け』をクリックします。
  3. 『貼り付ける形式』で『テキスト』を選択して『OK』をクリックします。
  4. C2のセルに下記の式を入力して、キーボードの『Enter』を押します。

    =COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,"<20",$C$2:$C$7,">"&C2)+1

  5. C2のセルの枠の右下をドラッグして、C3～C7のセルにコピーします。
    f:id:mmorley:20170530102623p:plain
    　5行目は20歳以上なので対象外です。
    　6～7行は、5行目を除外した順位が付けられています。
    　ただ、5行目にも順位が入っていてまぎらわしいので、IF文を使って"対象外"にします。
  6. C2のセルに下記の式を入力して、キーボードの『Enter』を押します。

    =IF(B2<20,COUNTIFS($A$2:$A$7,A2,$B$2:$B$7,"<20",$C$2:$C$7,">"&C2)+1,"対象外")

  7. C2のセルの枠の右下をドラッグして、C3～C7のセルにコピーします。
　
　以上で、次のように順位が入力されます。
    f:id:mmorley:20170530100556p:plain

補足:COUNTIFS関数のほうがSUMPRODUCT関数より速い

　『COUNTIFS関数』は『Excel2007』で追加されました。それ以前のバージョンでは、『SUMPRODUCT関数』を使う方法がよく紹介されています。
　次の2つの式は、どちらも上記で説明したグループ別に20歳未満を対象に順位を付ける式です。

    =COUNTIFS($A$2:$A$7,A3,$B$2:$B$7,"<20",$C$2:$C$7,">"&C3)+1
=SUMPRODUCT(($A$2:$A$7=A6)*($B$2:$B$7<20)*($C$2:$C$7>C6))+1　←上と同等の式

　ただ、冒頭にも書きましたが、『COUNTIFS関数』のほうが計算速度が速いです。
　さらに、次の式のように範囲を列全体（$A:$Aなど）にした場合、『COUNTIFS関数』は『データが入った最後の行』を検出して無駄な計算を省きますが、『SUMPRODUCT関数』はシートの全行を計算します。

    =COUNTIFS($A:$A,A2,$B:$B,"<20",$C:$C,">"&C2)+1
=SUMPRODUCT(($A:$A=A2)*($B:$B<20)*($C:$C>C2))+1　←上と同等の式

    　

　試しに計測してみました。時間はパソコンの性能によりますし、ストップウォッチでの手計測なので参考程度に。
　5000行のデータに下記の1～4の式で順位付け。各3回計測。

    1:COUNTIFS 行指定
 =IF(B2<20,COUNTIFS($A$2:$A$5000,A2,$B$2:$B$5000,"<20",$C$2:$C$5000,">"&C2)+1,"対象外"),"対象外")
2:COUNTIFS 全行
 =IF(B2<20,COUNTIFS($A:$A,A2,$B:$B,"<20",$C:$C,">"&C2)+1,"対象外")
3:SUMPRODUCT 行指定
 =IF(B2<20,SUMPRODUCT(($A$2:$A$5000=A2)*($B$2:$B$5000<20)*($C$2:$C$5000>C2))+1,"対象外")
4:SUMPRODUCT 全行
 =IF(B2<20,SUMPRODUCT(($A:$A=A2)*($B:$B<20)*($C:$C>C2))+1,"対象外")

    測定回数1回目2回目3回目
    1:COUNTIFS 行指定4.46秒4.42秒4.43秒
    2:COUNTIFS 全行5.45秒5.47秒5.49秒
    3:SUMPRODUCT 行指定7.53秒7.56秒7.57秒
    4:SUMPRODUCT 全行44.44秒44.45秒44.47秒
　私のパソコンの性能では『4:SUMPRODUCT 全行』の使い方はしちゃだめですね。
　
参考ページ

あとがき

　Excelのシートの最大行数はExcel2003までは6万5536行（256列）で、Excel2007からは104万8576行（1万6384列）あるそうです。
　ずっと6万5536行思ってましたが、今はかなり増えてました。
　行が増えたこともあって、データの末尾を検出する関数が実装されたんでしょうか。
　範囲の指定に列全体（A:A等）を使う場合は、関数によっては処理がめちゃくちゃ重くなるので気をつけた方が良さそうです。

おすすめの記事