選曲にオリンピック開催地選出法をEXCELでする

質問

5曲のうち、どの曲をやりたいですか、というアンケートをとりました。
５曲それぞれに、１位から５位までつけてもらっています。
その集計をするのに、オリンピックの開催地を選出する方法を使いたいと思っています。

こんな方法です。
１　開催地に相応しいと思うところを投票。
２　一番得票数が少なかったところを落選とする。
３　残ったものを選択肢として、再び投票。
４　一番得票数が少なかったところが落選。
５　…という流れを繰り返し、1位を選出。

これを利用して、選曲アンケートの集計をＥＸＣＥＬで作りたいと思っています。

アンケート用紙では、5曲の候補曲について、
それぞれ、１番やりたい曲、2番目にやりたい曲、と5番まで記入してもらっています。

それをＥＸＣＥＬにデータ入力します。

曲名　あ　い　う　え　お　

Ａ君　１　４　３　２　５
Ｂ君　３　２　１　４　５
Ｃ君　２　３　４　５　１
…

という入力をします。
これをデータとして、オリンピックの開催地選出の方法で第1位を選び出したいのです。
流れとしては、
まずは、１の付いている数を比較し、一番１の付いていない曲を落選させます。
つぎに、1位が落選した人もいますので、一番小さい数字のつけられた曲を選びだし、比較し、得票の少なかった曲を落選させます。
ということを繰り返し、最終的に１曲を選び出す形になるかと思います。

どうしたらいいか、アイデアを頂けませんでしょうか。
よろしくお願い致します。

回答No.3

kagakusuki · ベストアンサー

今仮に、アンケート回答者の氏名がA2以下に並んでいて、B1～F1に各曲名が並んでいて、B列～F列の2行目以下にアンケートの回答が並んでいるものとします。
　そして、H列～M列を使用して、選出を行うものとします。

　まず、H1セルに「曲名」、H2セルに「選出」、H3セルに「第何選考まで残る」と入力して下さい。
　次に、H4セルに次の数式を入力して下さい。

=ROWS($4:4)&"位の数"

　次に、I1セルに次の数式を入力して下さい。

=B$1&""

　次に、I4セルに次の数式を入力して下さい。

=COUNTIF(OFFSET($A:$A,0,COLUMNS($I:I)),1)

　次に、I5セルに次の数式を入力して下さい。

=IF((0&I4)+0>MIN($I4:$M4),COUNTIF(OFFSET($A:$A,0,COLUMNS($I:I)),ROWS($3:4)),"")

　次に、I3セルに次の数式を入力して下さい。

=COUNT(I$4:I$8)

　次に、I2セルに次の数式を入力して下さい。

=IF(I$3=MAX($I$3:$M$3),"○","")

　次に、I1～I5の範囲をコピーして、J1～M5の範囲に貼り付けて下さい。
　次に、H4セルをコピーして、H5セルに貼り付けて下さい。
　次に、H5～M5の範囲をコピーして、H6～M8の範囲に貼り付けて下さい。

　これで、I1～M1の範囲に並んでいる曲名において、選出された曲名に関しては、その下に丸印が表示されます。

回答No.4

kagakusuki

ANo.3です。
　それで、例えば１位の数で比較した際と、2位の数で比較した際に落選しなかった曲が複数あり、その残った複数の曲に関して、それぞれの３位の数が全く同数であった場合には、どの様に選考しようと考えておられるのでしょうか？
　しかも、その残った複数の曲の中の２つの曲（仮に曲名を「α」と「β」とします）に関して、1位の数では曲「α」の方が曲「β」よりも僅かに多いものの、2位の数では逆に曲「β」の方が曲「α」よりも圧倒的に多い、という様な場合には、どの様に選考しようと考えておられるのでしょうか？

お礼

早速のご回答ありがとうございます。

すごい！とびっくりしました。しかもこんなに丁寧にお書き下さって、ありがとうございます。

ただ、せっかくお作りいただいたのですが、少しニュアンスがお伝えできていない感じがありました。補足致します。説明が悪くすみません。
オリンピックの場合は、毎回投票し直しますが、選曲の場合は、手間を減らすため、１位から５位を書いてもらっています。
その１位から５位のデータをもとにして、オリンピックと同じ選考方法をシミュレートしよう、という感じです。
ですから、１位で比較するのは１回目のみで、
２回目は、消去された最下位を除く４曲で、最上位の選曲を比較することになります。
たとえば、１位に選んだ曲がいきなり落選した人は、２回目のときに２位に選んだ曲に投票することになります。１位に選んでいる曲が１回目に落選しなかった人は、２回目も１位に投票することになります。

そんな具合なんです。

落選すべき曲が２曲以上同票になる場合は、それら全て落選で良いと思います。
ただ、すべて同票となった場合は、そこまで、という琴になると思います。

なんかめちゃくちゃややこしいですね。申し訳ないです。

今、自分でなんとかやってみました。
ＭＩＮとＳＵＭを使って落選すべき曲がどれかを集計して割り出し、その都度、落選する曲のデータを１列、手作業で消去して空白にし、ふたたび割り出して、というのを繰り返すことで、手作業になりますが、オリンピックと同じ結果を得ることが出来ました。

kagakusuki様のように、美しくスパッと答えが出るように作ることができれば最高と思っています。
ご回答頂く前に解決してしまい、申し訳ありません。
ただ、私自身はアルゴリズムなどを考えるのが大好きな人間でして、もし、これを解決するご回答を頂ければ、質問の主旨とは異なりますが、大変うれしく存じます。

回答No.2

23tomo-u

1から5番までつけてもらうとしたら、足した数が
一番大きな数字のところが、一番人気がないと思うので、
一番大きい数字のものを弾いていくのはどうでしょう。
（その繰り返し）

お礼

早速のご回答ありがとうございます。

ただ、この方法ですと、１位や５位といった数字の足し算ですから、
オリンピックの方式とは異なる結果になるのではないかと思います。
ややこしいのですが、なんとか、オリンピック方式の結論が導けるようになればと思っております。

回答No.1

keithin

再投票をしない(毎回アンケートを取り直すのではない)のでしたら，単純に
５が一番多かった曲を外す
次に４が一番多かった曲を外す
次に３が一番多かった曲を外す
次に２が一番多かった曲を外す
残りが当選
という段取りという事になります。

曲「あ」の５の得票数は
Ｂ３２：
=COUNTIF(B2:B31,5)
のようにして算出します。
Ｂ３３：「あ」の４の数
=COUNTIF(B2:B31,4)
以下同文

お礼

早速の解答ありがとうございます。

ただ、この方法ですと、
「選択肢の中で一番やりたくない曲を書いてもらい、一番になった曲をはずす」これを繰り返す。
ということになります。
狙いは、
「選択肢の中で一番やりたい曲を書いてもらい、一番得票しなかった曲をはずす」これを繰り返す。
ですので、結果が異なったものになる可能性があります。

難しいのは、ある人にとって１位の曲が落選したとき、その人が次の投票で選曲するのは２位の曲になる、
ということです。つまり、１を何人が選んだか、というのは２回目以降は直接的には
意味をなさなくなるということなんです。
ここが何とかなれば、と思っています。