- ホーム >
- 応用操作 >
- エクセル（Excel）編 >
SUMIF関数とCOLUMN関数 ～ 勝敗予想ゲームを集計する ～
タイトルからは、ゲームソフトが出てきそうですが、本項でもあくまでExcelの関数の使い方と、関数を応用した集計方法の一例をご紹介したいと思います。
本項で学習する関数の題材として適切なサンプルは他にいくつもあると思いますが、なかなかしっくりくる例えがなく、あえて「勝敗予想ゲーム」を題材として扱うこととしました。
回りくどい言い方をしていますが、どのような勝敗予想かというと、「相撲」を題材として、下表のように勝率の良さそうな力士から順に選んでいきます。上から10点、9点…1点、と点数を振り、勝てばその点数が獲得できるというものです。
強い力士を上位にすると、勝つ確率が高くなり、累計で高得点を得やすくなっています。こうした予想を複数人で行い、場所が終わったときに、合計点が最も多いのは誰かというゲームです。下図は「参加者②」の予想です。
つまり、もうお分かりのとおり、使い方を間違えればとてもグレーな世界にいざなわれる可能性がありますので、
あくまでシミュレーションゲームの一例
として考えていきましょう。
別シートに全力士の一覧を作成し、すべての勝敗を入力しています。このシートはドロップダウンリストを使って、勝敗のみを入力するシート（勝敗一覧表）にしています。（ドロップダウンリストについては、入力規則 を参照してください）
この勝敗一覧表でドロップダウンリストを使う理由は、入力が容易になること、ともう一つ「入力方法を統一する」という重要な意味があります。
例えば、上図では勝ちを「〇」、負けを「●」としていますが、リストからの選択でなければ「◎」や「勝」、「×」、「負」などのように統一性のない入力をしてしまうかもしれません。
Excelに限らず、蓄積されたデータを利用する場合には、その集団が同じ形式で入力されたデータである必要があります。意味は同じでも異なる値や表現で入力されてしまうと、集計するのに非常に手間がかかってしまいます。特に自分だけでなく複数の人が共同で利用するシートを作成する場合は、このようにドロップダウンリスト使ってデータの入力形式を限定するのが有効な方法になります。
では、1日目「10/1(金)」からみていきましょう。通常は、一気に勝敗表が埋まることはありません。一日ずつ結果は入力されていきます。日を追うごとに得点は増え、順位が変動していくゲームです。
それぞれの参加者は全力士から10名をセレクトして順位付けしています。勝敗一覧表への勝敗の入力をするだけで、他の表にすべて自動的に値を反映させ、順位を確定できるように関数で設定してみましょう。
まず、参加者1「野茂英雄21」の表に関数式を入れてみましょう。「C4」セルはどのような関数式を入力したらよいでしょうか？
これはもうお分かりのとおり「VLOOKUP関数」を使います。下図のとおり、勝敗一覧表の範囲を指定して「10/1」の列である「2」を指定します。（VLOOKUP関数について詳しくは、基本操作編 LOOKUP関数 を参照してください）
すると、「舞の雲丹」の「10/1」の「●」が反映されます。そして、このセルをオートフィルでコピーしますが、それを見越して「検索値」の「B4」セルは列固定のため絶対列参照（複合参照）、勝敗一覧表の「範囲」は完全固定のため絶対参照としています。（絶対参照について詳しくは、基本操作編 絶対参照と相対参照 を参照してください）
まずは、縦方向にコピーします。
検索値の「B4」セルは絶対列参照のため、縦方向には相対参照であり、それぞれの行のB列を指定するように変化しながらコピー（「B5、B6、B7・・・」）され、勝敗一覧表の「10/1」の結果がすべて正しく反映されます。
このとき、オートフィルでは書式までコピーされるので、罫線の書式が乱れるなどの場合は、上図のようにコピー後に「書式なしコピー」を選択すると数式のみがコピーされます。（書式のコピーについては、書式のユーザー定義 を参照してください）
次に、このまま横方向へ列全体をコピーします。
すると、「10/1」の結果がそのままコピーされます。これは、すべてのセルの式が同じままでコピーされているためです。引数のうち、検索値は列を固定、範囲は絶対参照で全固定しているので、横方向へのコピーは絶対参照となり不変です。それは問題ありませんが、列番号が「2」のまま変動していないためにすべて勝敗一覧表の「10/1」の列の結果が反映されてしまっているのです。
列番号だけ変化して増えていけばよいのですが、
VLOOKUP関数はオートフィルで列番号の数値（数字）は変化しない仕様となっている
ためです。
したがって、横方向へのコピーに対しては列番号を「手動で」増やしていく必要があります。これはかなり面倒な作業になりますが、このままでは手作業で1列ずつ増やしていくしかありません。
これを解消する方法として、代表的な2つの方法があります。まず1つ目は、下図のように列番号を表す行を追加し、VLOOKUP関数の列番号に、数値ではなく「セル」を指定する方法です。
この場合、日付と対応している列番号を別に入力し、そのセルを指定します。こうすることで、オートフィルコピーとともにセル番号がスライドし、列番号を自動的に増やしていくことができます。
ただ、この方法では表のどこかにこうした数値を入力する行を作成する必要があり、不自然な数値が気になる場合は、行の非表示などで隠す必要が出てきます。
2つ目の方法としては、
COLUMN（カラム）関数
を使う方法があります。
COLUMN関数はどのような関数かというと、非常に単純で、引数となる参照値（セル）の「列番号」を返す関数になります。下図のように、COLMUN関数の引数に「D1」セルを指定した場合は、その列番号である「4」が返ってきます。
したがって、VLOOKUP関数の引数にCOLUMN関数を入れ子にして列番号を返す「セル」で指定することができます。
指定した「B1」セルは、下図の勝敗一覧表の「B列」の列番号「2」となります。列番号さえ指定できればよいので行は何番でもよく「B2」でも「B100」でもかまいません。（この場合は、縦方向へのオートフィルを想定して行を固定する絶対行参照で「B$1」としていますが、行番号は変動してもよいので気にならなければ$で固定する必要はありません）
すると、関数で列を指定しても下図のように正しい結果が反映されます。
このままオートフィルで横方向にコピーします。こちらも全列で正しい結果が反映されます。
それぞれのセルの式を確認すると、引数のセルがともなって変化していることが確認できます。
そして、このまま行全体をオートフィルで縦方向にコピーします。縦方向へのコピーにも正しい結果が反映されてきます。
このように、VLOOKUPはよく使われる関数なので、列番号をオートフィルで可変にするCOLMUN関数との組み合せは非常に便利です。とても簡単なのでぜひ習得しておきましょう。
では次に、それぞれの参加者において、日々の得点を計算していきましょう。
下図の参加者1の「10/1（金）」は、どのような計算になるでしょうか？
この場合は、勝ち「〇」の点数が合計されるで、「9+8+5+2+1」で合計「25」となります。
これを関数で表現すると、
条件が「〇」の行の「点数」を合計する
となります。言い換えれば、「もし値が〇なら、△の値を合計する」です。何かこんな関数がなかったでしょうか？
そうです。基本操作編の IF関数 で学習した「IF関数」によく似ています。
IF関数と同じようなこの関数は、
SUMIF（サムイフ）関数
といいます。SUMIF関数は、指定された検索条件に一致するセルの値を合計する関数になります。名前のとおりIF関数にSUM関数を足したイメージです。
SUMIF関数を使うと、こうした値の合計を簡単に算出することができます。SUMIF関数の引数の画面は下図のように3つの引数を指定するだけです。
まず「範囲」は、検索する対象の範囲のことで、この場合では「C4:C13」になります。次に「検索条件」は「〇」、「合計範囲」は合計する値の範囲を指定し「A4:A13」となります。
オートフィルコピーを念頭に、関数式は「=SUMIF(C4:C13,"〇",$A$4:$A$13)」となります。
これだけで、「〇」の行の点数を合計することができます。この場合は「25」と正しい計算結果となります。オートフィルコピーですべての列を合計すると、「参加者1」の合計点は「189」となります。
こうして他の参加者の点数も計算することができます。「参加者2」の点数は「139」となりました。
最後にRANK関数で順位を決めます。（RANK関数について詳しくは、基本操作編 いろいろな関数 を参照してください）
以上で、シミュレーションゲームは完了です。適切な例題ではなかったと思いますが、SUMIF関数の使い方をよく理解できたのではないでしょうか。SUMIF関数を利用すると、ある条件を満たす数の合計を簡単に計算することができます。
また、VLOOKUP関数のオートフィルコピーに対するCOLMUN関数を使った小技的なテクニックも知っておくと便利です。どちらも積極的に活用してみてください。
更新履歴
- 2021年10月7日
- ページを公開。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- サイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします！