Excelの活用について

質問

ザックリとしたタイトルですみません。
Excelで入力した数字の平均値を出し、その平均値に近い構成のグループを自動的に作成するようなマクロ？が作れないかと思って質問しました。

具体的には、
仮に1000、1100、1200、1300、1400、1500、1600、1700、1800、1900がいたとして、
平均すると1450の為1000、1100、1500、1700、1900の平均1440と1200、1300、1400、1600、1800の平均1460のような事を自動的に割り振るような事をしたいのです。
一度どこかで見たことがあったのですが、こういうものを作成するには何を使うと良いのかがサッパリだったので質問しました。

自身では作成は出来ないとは思った為依頼をしようかとも思ってますが、どのくらいかかりそうな物でしょうか？

回答No.2

nishi6

質問にある数値を使って実際に計算してみました。（Excel2010です。重要です）

まず、添付図の説明ですが、

セルA5:=0、下方向のセルに連番で1023まで入力
セルB4:=1000、右のセルに100飛びで1900まで入力（質問にある数値）
セルB1:=9、右のセルに-1飛びで0まで入力（2進数の桁の重み）

セルL2は指定する平均値1、セルM2は指定する平均値2

セルB5:=MOD(INT($A5/2^B$1),2)、K5までコピー
0～1023を2進数のビットに分けています。0、1が組み合わせです。全件網羅になります。

セルL5:=IFERROR(AVERAGEIF(B5:K5,1,$B$4:$K$4),0)
　　　2進数のビットが1の数値の平均
セルM5:=IFERROR(AVERAGEIF(B5:K5,0,$B$4:$K$4),0)
　　　2進数のビットが0の数値の平均
セルN5:=IF(AND(ROUND(L5,0)=$L$2,ROUND(M5,0)=$M$2),"○","")
　　　四捨五入した数値が指定した平均値と一致したら「○」

セルO2:=COUNTIF(N5:N1028,"○")
　　　「○」の個数

入力したB列からN列の算式を下にコピーします。これで出来上がりです。

使い方は、セルL2、M2に平均を2つ入力します。そのあとフィルターを使って、N列の「〇」を絞り込んで表示します。セルO2に満足する組み合わせ数が表示されています。

＞その平均値に近い構成のグループを自動的に作成するようなマクロ？が作れないかと思って質問しました。

まず質問の内容を整理すると、
　・10個の数値を使い任意の組み合わせを2つ作る
　・そして各々の平均値が指定の数値に近いこと
になりますが、

実際に作るとなると、
　・実際は数値は何個程度なのか。組み合わせの場合の数は対象の個数が増えるにつれて爆発的に増えていきます。重要な要素です。

　・「近い」をどのように定義するか。添付図では、組み合わせの平均を整数に四捨五入して比べています。

　・解が求まったらそこで終了か、または継続？
　　計算機がもてあますような数値の個数なら全件網羅してどこかで打ち切りか、モンテカルロ法（例えば。有効かどうかは不明）を使って１つで済ませるか（モンテカルロ法で複数回可能ですが）。

　・添付図では1024種類の組み合わせ（2の10乗）が計算されていますが、その平均「値」の種類は106個でした。原因は質問の数値の増分が等しいためですが、平均値1を指定すると、もう一つの平均値2は平均値1に従属になります。
平均値の個数は、数値の最小値　≦　平均値　≦　数値の最大値　です。添付図で２つの平均値を入力するとほとんど該当の組み合わせが出てきません。このあたりは、2つの平均値の指定方法に工夫が必要でしょう。

そのような理由で、マクロではなくシートで計算してフィルターを使うようにしてみました。平均値1をフィルターで絞り込んで平均値2をどれにするか選ぶのが合理的に思えました。このシートの計算をマクロにするのは簡単ですが、合理的に2つの平均値を選ぶことをマクロですることはしたくないですね。（フィルターで選べばいいので）

＞自身では作成は出来ないとは思った為依頼をしようかとも思ってますが、どのくらいかかりそうな物でしょうか？

添付図の程度ならご自分で作れるのでは？

シートしか作っていないので、シートについては、
　横の数値、縦の数値・・・・1分
　算式は実質4、5個・・・・・3分
　縦（行方向）にコピー・・・1秒弱
こんなものでした。（後から分析のために時間が10分くらいかかっています）この解答が一番時間がかかっています。

マクロは、全件網羅の組み合わせで最初の平均値を計算して、条件に合致したらもう一つの平均値を計算（逆算）して合致するか調べることになるでしょう。1つの組み合わせを書けば後は繰り返しです。シートで何を計算しているか説明すれば、検証の時間は除くとして、作るのは30分程度でしょうか。組み合わせがなかなか見つからないでしょう。

書き洩らしましたが、10個の数値を2つのグループに分けています。ここで、1つの数値はどちらかのグループに含まれています。これが、含まれない場合も想定する場合は、2進数のビットで組み合わせで表現しているところを3進数に変え、1023（2の10乗-1）を59048（3の10乗-1）にします。こうやって計算量の爆発が起きます。

回答No.1

keithin

とりあえずエクセルの機能で「ソルバー」とかを使うと出来そうです。

ただしご相談に書かれてるお話（考え）だけでは、成立する組み合わせが沢山ありすぎて困ると思います。
たとえばグループを「1400と1500」と「それ以外」とすれば、どちらの平均もぴったし1450に揃います。
４個と６個のグループでも同じに出来ます。

じゃぁと例えば「どちらも５個ずつ」という追加の制約を課したとしても、平均1440になる組み合わせは
「1000と1200と1400と1700と1900」
「1100と1200と1400と1700と1800」
　：
　：
みたいに多数存在するので、もっと「こういう組み合わせになるように選びたい」という何か追加の条件を考えた方が良いでしょう。

どういうロジックで何を探索させたいのか考えれば、あとはそれを実現できるようにエクセルを作成しソルバーを動かすだけです。
ご利用のエクセルのバージョンとか不明なので具体的に手取り足取り操作をお話しは割愛しますが、下記など例えばご参考に。
http://office.microsoft.com/ja-jp/excel-help/HP010342416.aspx

グループ分けは、例えば「バイナリ」を使い０と１を記入させて「グループ０」「グループ１」の２つに分けるような段取りで実現します。

＃ご相談投稿では、普段あなたがご利用のソフトのバージョンまでキチンと明記する事を憶えて下さい。