[[20251226131716]]　『上限値内に収まるよう、いくつかの数値から選択し』（おーぶ）　ページの最後に飛ぶ

『上限値内に収まるよう、いくつかの数値から選択して合計したい』（おーぶ）

A1～A10のバラバラな数値の合計が、設定した上限値を超える場合に上限に一番近い合計になるようにA1～A10の中から数値(セル)を自動で選択して合計を出したいのですが、計算式があれば教えてください。
(A1～A10の合計が上限以下のときはそのまま合計を表示)
また、その計算から除外された数値(セル)で同じことを繰り返す方法あれば教えてください。よろしくお願いします。

＜使用 Excel：Microsoft365、使用 OS：Windows11 ＞

https://www.excel.studio-kazu.jp/cgi-bin/estindex/estseek2.cgi?phrase=%E7%B5%84%E3%81%BF%E5%90%88%E3%82%8F%E3%81%9B+%E3%82%BD%E3%83%AB%E3%83%90%E3%83%BC+%E3%83%90%E3%82%A4%E3%83%8A%E3%83%AA&perpage=10&attr=&order=%40uri+STRD&clip=-1&navi=0
（ggr） 2025/12/26(金) 14:26:56

 既に貼られている通り、ソルバーでの処理が最も簡易かと思います

 より厳密にやるなら、A1:A10に対してビット全探索を行い
 Min(Abs(diff)))の時に答えを更新するようなアルゴリズムをUDFで実装すればいいでしょう

 ただし、「除外された数値で繰り返す」ことは一つの関数では不可能のように思います
 その場合はUDFだけではなく、再帰的にUDFを回すプロシージャを記述する必要があるかと

 私の知識ではこの程度です
（Asa） 2025/12/26(金) 14:43:20

 UDF、ビット全探索の例です。検証不十分なので、あしからず。

 [E1]  =Near($A$1:$A$10,D1)

      __A  __B  __C___  __D  __E  __F____
  1    40       目標値  100   99  44,5,50
  2    42                                
  3    34                                
  4    80                                
  5    44                                
  6    41                                
  7     5                                
  8    96                                
  9    40                                
 10    50

 Function Near(rng As Range, ByVal maxValue#)
    Dim v, i&, j&, tempValue#, tempBool() As Boolean, ret, retList
    v = WorksheetFunction.Transpose(rng.Value)
    ReDim tempBool(1 To UBound(v))
    For i = 1 To 2 ^ UBound(v) - 1
        For j = 0 To UBound(v) - 1
            tempBool(j + 1) = CBool(i And 2 ^ j)
        Next
        tempValue = WorksheetFunction.Sum(WorksheetFunction.Filter(v, tempBool))
        If tempValue <= maxValue And tempValue > ret Then
            ret = tempValue
            retList = WorksheetFunction.Filter(v, tempBool)
        End If
    Next
    If IsEmpty(ret) Then
        Near = "解なし"
    Else
        Near = Array(ret, WorksheetFunction.TextJoin(",", True, retList))
    End If
End Function
（まる2021） 2025/12/26(金) 15:31:21

ご返信ありがとうございます。
ソルバーは試していて目的の合計は出せたのですが、参照データが変わる度に都度計算させる必要があり、また繰り返し性も無さそうでしたので、何かいい計算式がないか問い合わせてみた次第です。
（Asa）様や（まる2021）様のご回答のようにハイレベルな処理が必要なのですね。
私には難しすぎます。
わざわざご回答頂いたのに申し訳ありません。
（おーぶ） 2025/12/26(金) 17:01:55

 Microsoft365なら関数だけでもできます。

 ソルバーにしろ、ビット全探索にしろ、やっている事はそんなに難しい話ではなく、
 全組合せの合計を計算して、その中で目標値に最も近い値を抽出しているだけです。
 組合せは要素数が1つ増えるだけでも計算量が爆上がりします。
 でもA1:A10の10個程度なら10C1+10C2+....10C10の1023通りなので関数でも十分だと思います。

 以下に関数での例を示します。ワンライナーで書くことも出来ますが作業列を使った方が理解しやすいと思うのでそちらで提示します。

 [A1:A10]と[C2]に目標値を手入力

 以下に数式をコピペ

 [D2]  =LET(sm,SUM(A1:A10),IF(sm<=C2,sm,XLOOKUP(C2,G1#,G1#,"解なし",-1)))
 [E2]  =IFERROR(XMATCH(D2,G1#),"")
 [G1]  =byrow(H1#,LAMBDA(a,SUM(FILTER(A1:A10,TRANSPOSE(a)))))
 [H1]  =IF(BITAND(SEQUENCE(2^ROWS(A1:A10)-1), 2^SEQUENCE(1,ROWS(A1:A10),0)) > 0,1,0)

 G列～Q列は1023行目までスピルするので空白にしておく事。

      __A  __B  __C___  __D___  __E___  __F  __G  __H  __I  __J  __K  __L  __M  __N  __O  __P  __Q
  1    40       目標値    結果  適用行        40    1    0    0    0    0    0    0    0    0    0
  2    42          100      99     592        42    0    1    0    0    0    0    0    0    0    0
  3    34                                     82    1    1    0    0    0    0    0    0    0    0
  4    80                                     34    0    0    1    0    0    0    0    0    0    0
  5    44                                     74    1    0    1    0    0    0    0    0    0    0
  6    41                                     76    0    1    1    0    0    0    0    0    0    0
  7     5                                    116    1    1    1    0    0    0    0    0    0    0
  8    96                                     80    0    0    0    1    0    0    0    0    0    0
  9    40                                    120    1    0    0    1    0    0    0    0    0    0
 10    50                                    122    0    1    0    1    0    0    0    0    0    0
（まる2021） 2025/12/26(金) 21:16:15

 > ソルバーは試していて目的の合計は出せたのですが、
 > 参照データが変わる度に都度計算させる必要があり、
 > また繰り返し性も無さそうでしたので、
 > 何かいい計算式がないか問い合わせてみた次第です。
 なるほどですね。
 念のために申し上げると、
 ソルバーは内部で多くの繰り返し計算をしていますよ。
 インプットとアウトプットだけを見ると1対1の関係ですけどね。
 でも、だからと言って「簡単に計算式で求めることが可能」とはなりません。
 それならソルバーの立場がありません。

 このケース（10個の数値）では、2＾10＝1024とおりの組み合わせしかないので、
 既に回答がありますように全ケースを列挙することができます。

 なお、私も365で計算しておりました。殆ど重複しましたので掲載はしません。
 ・結果の数値をソートして別のセルに書き出すこともできます。
 ・また、その結果の数値を 要素別に " +"でTextJoinして表示することもできるでしょう。
 質問者さんのほうで追加して検討してみてください。

（xyz） 2025/12/26(金) 21:45:36

 既にいくつか有効な回答が出ているようなので、感想と私見だけ述べます
 ハイレベルというより、「ソルバーの内部処理をより明確にしている」という評価の方が適切かと思います
 実際の内部処理を把握しているわけではありませんが。

 ソルバーの存在意義に関してはxyz様のご指摘の通りで、ハイレベルと感じるところをブラックボックス化させ
 最適化問題の入門的なツールとしての役割があるように思います
（Asa） 2025/12/27(土) 01:44:47

コメント返信：

[ 一覧(最新更新順) ]

[[20251226131716]] 『上限値内に収まるよう、いくつかの数値から選択し』（おーぶ） ページの最後に飛ぶ

[[20251226131716]]　『上限値内に収まるよう、いくつかの数値から選択し』（おーぶ）　ページの最後に飛ぶ