閑話休題 20人を5人4組に分け10回の研修での組み合わせ

先日のYahoo知恵袋に質問があって、「20人を5人4組に分け10回の研修を計画していて、できるだけ重ならない組み合わせを」と言う事で、ベストアンサーはVBAでのプログラムでした。でも、面白い問題なので、ランダム関数を使ってエクセルプログラムを作って、遊んでみました。

プログラムの概要は、①A~T迄の文字にランダム関数(RAND)をあて、それを1~20位(RANK)に分け、1~5番をⅠ組、6~10番をⅡ組、と言うように分けます。 ②10回分のデータを、5文字x4チームから、各名前毎、すなわちAを含むチーム、Bを含むチームと言うように調べ(MATCH、INDEX関数)、そのチームになった残りの4人を抽出します。(SUBSTITUTE、文字列LEFT、MID、RIGHT) ③この数を計算します。(COUNTIF) ④各自が同じチームになれば平均は2.1回なので、標準偏差(STEDV)を求め、標準偏差が小さいほど、同じ人と重なりが少ない事になります。

同じチームになる回数の理想は、2名が3回、17名が2回です。この標準偏差は0.315になりますから、できるだけ標準偏差の20名の平均が小さくなる組み合わせをベストとし、又個別の組み合わせが1-3回で、3回の組み合わせトータルが小さくなる組み合わせを選んでいきました。

今回の中では標準偏差が最も小さく(標準偏差=0.637)、かつ3回組み合わせ数48(最小でも2回20名の40)が得られました。例えば4回の回数を減らす様に組み合わせを変えると、他の組み合わせで4回になったりするため、今回の結果はベストに近いと思います。

f:id:bellnohito101:20200409093435p:plain

 

エクセル関数をかなり使いこなせるようになったと感じています。

皆さん、コロナ(COVID-19:又の名を武漢ウィルス)には気を付けましょう。