モーリーのメモ

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

モーリーのメモ

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

<今回やること!>
 Excelで、COUNTIFS関数を使って順位を計算します。
 グループ別や除外(対象限定)等の条件付きの順位付けが出来ます。

    f:id:mmorley:20170530100556p:plain
 上の表は、20歳未満を対象に、AとBのグループ別に順位を付けています。
 D2のセルの式は次のようになります。

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

 単に順位を付ける場合はRANK関数を使いますが、条件付きの順位を付ける場合はCOUNTIFS関数を使います。
 ただし、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を足したもの』です。
  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等)を使う場合は、関数によっては処理がめちゃくちゃ重くなるので気をつけた方が良さそうです。