エクセルの関数について教えてください

質問

このたび　仕事関係で「ビジネスゲーム」なるものをやることになりまして
その際　「メーカーへの入札」を擬似的に行います
私は　進行役として参加します

例えば　メーカーが希望する入札数は１００個と決まっていて
１０社ほどのチームが　その１００個をとるために　５０個１００円とか　５０個９０円とか　４０個９０円とかで入札をかけてくるわけです
上記の場合は　９０円で入札した会社から全部　１００円で入札した会社から１０個とすれば問題ありませんが
問題は　例えば　残りの２０個に３社で同じ金額　１０個　２０個　３０個と入札してきた場合です
この場合　按分をして　３個　７個　１０個　としなければなりませんよね

これを　簡単に　計算できる式があれば　教えてください

まとめると
・相手が希望する入札数が決まっている
・安く入札してきた会社から　希望数をとっていく
・同じ金額で　希望入札数を越える場合は　按分する

以上です　よろしくお願いします

回答No.1

osamuy · ベストアンサー

ざっと書いてみましたが、以下のような感じ：

Option Explicit

Function sum_array(a As Variant) As Long
Dim k As Variant
sum_array = 0
For Each k In a
sum_array = sum_array + k
Next
End Function
Public Sub distribute(ByVal num_item As Long, orders_list As Variant)
Dim it As Variant, unitprice As Long, orders As Variant, sum_order As Long
Dim allot_ratio As Double
For Each it In orders_list
unitprice = it(0)
orders = it(1)
sum_order = sum_array(orders)
If sum_order > num_item Then
allot_ratio = CDbl(num_item) / sum_order
sum_order = num_item
Dim k As Long
For k = UBound(orders) To LBound(orders) Step -1
orders(k) = Int(orders(k) * allot_ratio + 0.5)
If orders(k) < 1 Then orders(k) = sum_order
sum_order = sum_order - orders(k)
Next
sum_order = sum_array(orders)
End If
Debug.Print unitprice; "円 数量＝"; Join(orders, ", ")
num_item = num_item - sum_order
If num_item <= 0 Then Exit For
Next
End Sub

Public Sub test()
Dim OrderList As Variant
OrderList = Array(Array(50, Array(20, 80)), Array(100, Array(10, 20, 30)))
distribute 100, OrderList
End Sub

実行すると、次のような出力が得られます：
test
50 円 数量＝20, 60
100 円 数量＝3, 7, 10

ここでは、入札に関する情報を、
[単価、入札数のリスト]
の配列で表現してます（サブルーチンdistributeの第2引数）。
実用上は入札者の名前とかを保持するために、別途クラスを作って、データを表現するようにしたほうが良いと思いますが、サンプルなので手を抜いてます。

あと、案分するところの境界値テストが甘いかも。

お礼

ありがとうございました
早速　やってみようと思います
まだまだエクセルは勉強することがたくさんありそうです