[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『金額の組み合わせと振り分け』(しゅうか)
たとえば10万円が、
\10,000×5
\5,000×3
\1,000×25
\500×13
\100×35
このようにあったとした場合
自動で5万円づつセルに表示するような関数はありますか?
その際、お札や小銭ができるだけ偏りなく均等に配分したいです。
< 使用 Excel:Excel2010、使用 OS:unknown >
90000円を30000円づつとかあるの?
例題みたいに2つに均等? (GobGob) 2014/10/30(木) 17:48
Sub test() Const MAX0 = 5& Const MAX1 = 3& Const MAX2 = 25& Const MAX3 = 13& Const MAX4 = 35&
Dim i As Long Dim i0 As Long Dim i1 As Long Dim i2 As Long Dim i3 As Long Dim i4 As Long Dim iTarget As Long Dim iA1 As Long Dim iA2 As Long Dim iC1 As Long Dim iC2 As Long Dim iDim(4) As Long Dim iMin As Long
iMin = 999 iTarget = (MAX0 * 10000 + MAX1 * 5000 + MAX2 * 1000 + MAX3 * 500 + MAX4 * 100) / 2
For i0 = 0 To MAX0 For i1 = 0 To MAX1 For i2 = 0 To MAX2 For i3 = 0 To MAX3 iA1 = i0 * 10000 + i1 * 5000 + i2 * 1000 + i3 * 500 iA2 = (MAX0 - i0) * 10000 + (MAX1 - i1) * 5000 + (MAX2 - i2) * 1000 + (MAX3 - i3) * 500 If iA1 <= iTarget And iA2 <= iTarget And iTarget <= iA1 + MAX4 * 100 And iTarget <= iA2 + MAX4 * 100 Then i4 = (iTarget - iA1) / 100 If i4 <= MAX4 Then iC1 = i0 + i1 + i2 + i3 + i4 iC2 = MAX0 - i0 + MAX1 - i1 + MAX2 - i2 + MAX3 - i3 + MAX4 - i4 If iC1 - iC2 < iMin And 0 < iC1 - iC2 Then iMin = iC1 - iC2 iDim(0) = i0 iDim(1) = i1 iDim(2) = i2 iDim(3) = i3 iDim(4) = i4 End If End If End If Next i3 Next i2 Next i1 Next i0
For i = 0 To 4 Cells(i + 2, "B").Value = iDim(i) Next i End Sub (???) 2014/10/31(金) 10:34
ロジック追加し、札枚数、硬貨枚数もなるべく均一になるようにしたところ、以下になりました。
10000*2 + 5000*3 + 1000*11 + 500*4 + 100*20 40枚(札16枚、硬貨24枚)
10000*3 + 5000*0 + 1000*14 + 500*9 + 100*15 41枚(札17枚、硬貨24枚)
差:札1枚、硬貨0枚
Sub test2() Const MAX0 = 5& Const MAX1 = 3& Const MAX2 = 25& Const MAX3 = 13& Const MAX4 = 35&
Dim i As Long Dim i0 As Long Dim i1 As Long Dim i2 As Long Dim i3 As Long Dim i4 As Long Dim iTarget As Long Dim iA1 As Long Dim iA2 As Long Dim iC1 As Long Dim iC2 As Long Dim iC1C As Long Dim iC2C As Long Dim iC1S As Long Dim iC2S As Long Dim iDim(4) As Long Dim iMin As Long Dim iMinCS As Long Dim iw As Long Dim iSa As Long
iMin = 999 iMinCS = 999 iw = (MAX0 * 10000 + MAX1 * 5000 + MAX2 * 1000 + MAX3 * 500 + MAX4 * 100) / 2
For iTarget = iw To iw + 1400 Step 100 For i0 = 0 To MAX0 For i1 = 0 To MAX1 For i2 = 0 To MAX2 For i3 = 0 To MAX3 iA1 = i0 * 10000 + i1 * 5000 + i2 * 1000 + i3 * 500 iA2 = (MAX0 - i0) * 10000 + (MAX1 - i1) * 5000 + (MAX2 - i2) * 1000 + (MAX3 - i3) * 500 If iA1 <= iTarget And iA2 <= iTarget And iTarget <= iA1 + MAX4 * 100 And iTarget <= iA2 + MAX4 * 100 Then i4 = (iTarget - iA1) / 100 If i4 <= MAX4 Then iC1 = i0 + i1 + i2 + i3 + i4 iC2 = MAX0 - i0 + MAX1 - i1 + MAX2 - i2 + MAX3 - i3 + MAX4 - i4 iC1S = i0 + i1 + i2 iC2S = MAX0 - i0 + MAX1 - i1 + MAX2 - i2 iC1C = i3 + i4 iC2C = MAX3 - i3 + MAX4 - i4 iSa = Abs(iC1C - iC2C) + Abs(iC1S - iC2S) If Abs(iC1 - iC2) < iMin Then iMin = Abs(iC1 - iC2) iMinCS = iSa iDim(0) = i0 iDim(1) = i1 iDim(2) = i2 iDim(3) = i3 iDim(4) = i4 ElseIf Abs(iC1 - iC2) = iMin Then If iSa < iMinCS Then iMinCS = iSa iDim(0) = i0 iDim(1) = i1 iDim(2) = i2 iDim(3) = i3 iDim(4) = i4 End If End If End If End If Next i3 Next i2 Next i1 Next i0 Next iTarget
For i = 0 To 4 Cells(i + 2, "B").Value = iDim(i) Next i End Sub (枚数数え間違い修正) (???) 2014/10/31(金) 14:26
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.