[[20160914124703]] 『30程度の乱数から4つの合計を160に近いものにす』(もくねん) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『30程度の乱数から4つの合計を160に近いものにする。』(もくねん)

ある数字 例として 22 28 35 36 45 46 48 55 57 63 75 80 があってその4つの数字の合計が160に近い数字になる組み合わせが出せる方法がありますか?

28+35+45+55=163
いろいろと頭の中で計算すればできるのですが、エクセルで一覧がでる良い方法を教えてください。

< 使用 Excel:Excel2003、使用 OS:WindowsXP >


27405通りの組合せしか無いようですので、マクロを使って、総当たりで、全組合せを計算した後、ソートする、とか?

(???) 2016/09/14(水) 13:12


全組み合わせの計算方法はどんな感じですか?
(もくねん) 2016/09/14(水) 13:17

どんな感じ、って、それを私が書いたら、コーディング完了ですよ?

まずはご自分で考えてみてください。4つの合計ですが、2つの合計だったらどうするか、から考えてみてはいかがでしょうか。2つならば、A列とB列に2つ選び出し、C列には合計する計算式を埋めれば良いですよね。
(Forループを重ねることになります)
(???) 2016/09/14(水) 13:39


順番を考えると、思ったより面倒なロジックになったので、そんな例なぞ。
とりあえず、ご提示の例で、ぴったり160になるのは、以下4通り。

22+28+35+75
22+35+46+57
22+35+48+55
22+36+45+57

 Sub test()
    Dim A As Variant
    Dim i As Long
    Dim j As Long
    Dim iR As Long
    Dim iAll As Long
    Dim iOn As Long
    Dim iCou As Long
    Dim iDim(3) As Long

    A = Array(22, 28, 35, 36, 45, 46, 48, 55, 57, 63, 75, 80)

    For i = 2 ^ 4 - 1 To 2 ^ (UBound(A) + 1) - 1
        iAll = i
        iCou = 0
        iOn = 0
        While 0 < iAll
            If iAll Mod 2 = 1 Then
                If iCou < 4 Then
                    iDim(iCou) = iOn
                End If
                iAll = iAll - 1
                iCou = iCou + 1
            End If
            iAll = iAll / 2
            iOn = iOn + 1
        Wend
        If iCou = 4 Then
            iR = iR + 1
            For j = 0 To 3
                Cells(iR, j + 1).Value = A(iDim(j))
            Next j
        End If
    Next i

    Range("E1:E" & iR).Formula = "=SUM(A1:D1)"
 End Sub
(???) 2016/09/14(水) 14:11

 To ???さん

 >For i = 2 ^ 4 - 1 To 2 ^ (UBound(A) + 1) - 1
 これ乱数が30個あれば、ループ回数が1,073,741,808回とかになりませんか。。。
 しかもWhile 0 < iAllのループで、iAll = iAll / 2になってるので、
 全てのループ回数は、17,592,722,653,176回になると思います。
 以下のように4つネストさせた方が結果的にループ回数は最小になると思います。

 以下コードはA列に乱数があるとして、C〜F列に結果を表示させています。
    Const s As Integer = 160 '合計値
    Const w As Integer = 3   '振れ幅
 合計値と振れ幅の分だけ結果を表示します。
 のsで合計値、wで振れ幅を変えて試して下さい。

 Sub test()
    Const s As Integer = 160 '合計値
    Const w As Integer = 3   '振れ幅
    Dim v() As Variant
    Dim r As Variant
    Dim a As Integer
    Dim b As Integer
    Dim c As Integer
    Dim d As Integer
    Dim i As Long

    Range("C:H").ClearContents

    r = WorksheetFunction.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value)
    ReDim v(1 To WorksheetFunction.Combin(UBound(r), 4), 1 To 4)

    For a = 1 To UBound(r) - 3
        For b = a + 1 To UBound(r) - 2
            For c = b + 1 To UBound(r) - 1
                For d = c + 1 To UBound(r)
                    If r(a) + r(b) + r(c) + r(d) >= s - w And r(a) + r(b) + r(c) + r(d) <= s + w Then
                        i = i + 1
                        v(i, 1) = r(a)
                        v(i, 2) = r(b)
                        v(i, 3) = r(c)
                        v(i, 4) = r(d)
                    End If
                Next d
            Next c
        Next b
    Next a

    Range("C1:F" & i).Value = v
    Range("G1:G" & i).Formula = "=SUM(C1:F1)"
    Range("H1:H" & i).Formula = "=ABS(" & s & "-G1)"
    Range("G1:H" & i).Value = Range("G1:H" & i).Value

    With ActiveSheet.Sort.SortFields
        .Clear
        .Add Key:=Range("H1")
        .Add Key:=Range("G1")
    End With
    With ActiveSheet.Sort
        .Header = xlNo
        .SetRange Range("C:H")
        .Apply
    End With

 End Sub

(sy) 2016/09/16(金) 22:43


質問者さんの反応がないのが残念なスレですが、syさんからも良い例が挙がったので、解説なぞ。

私のロジックは、2進数でどの番号を使用するかどうかを決めているところがミソです。ループ数とロジックの単純さ、判りやすさでは、syさんの例の方が良いですね。

2進数案のメリットは、組合せの個数が3個とか5個とかに変わっても、更には、1〜4個どれでも可、という条件に変わっても、修正量が少ない点です。
デメリットは、個数を数えるために二重ループになっている点。更には、最大のデメリットとして、符号付き長整数型の限界である31ビットまでしか対応しない点。

今回は、30個くらいという微妙な線だったので、実は31個でした〜、とか、ロジックが使えなくなって再質問してくるのでは?、という予想をしました。このとき、やる気のある反応であればsyさんのロジックのような別例を。まだ丸投げ気配であれば案だけを返そうと思っていたのです。(学校の宿題丸投げ、と読みました)
(???) 2016/09/20(火) 13:22


 >学校の宿題丸投げ、と読みました
 なるほど、もしそう言う要件であれば、私の回答は質問者さんの為にならないですね。
 ちょっと軽率でした。

(sy) 2016/09/20(火) 20:51


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.