エクセルにて、合計値→複数の項目の各個数を逆算

質問

エクセルにて、合計値→複数の項目の各個数を逆算

例えば、合計が「〇〇〇〇円」というのが分かっていて、そこから

商品Ａ　３４２円
商品Ｂ　１２３円
商品Ｃ　５３２円
　・
　・
　・

という項目から商品Ａが〇個、商品Ｂが△個・・・などと逆算できる計算式の作成は可能でしょうか？
よろしくお願いします。

回答No.2

_Kyle · ベストアンサー

ご質問の内容は、いわゆる「部分和問題」の一種で
残念ながらExcelの数式で解決するのは困難です。

数式以外のアプローチで解決できる可能性はありますが
以下のような条件によって（可能かどうかも含め）話が違ってきます。

以下「合計が一致する各商品の個数の組合せ」を「解」と呼びます。

●解が複数ある場合

解は一つとは限りません。
解が複数ある場合に
・どれか一つの解が判ればよい
・できるだけ多くの解を知りたい
・すべての解を求める必要がある
いずれかによって話が違います。

●解がない場合

合計がキッカリになる個数の組合せがないこともありえます。
・キッカリになる解が必ずあるハズ
・解がない場合に、ないことが判ればよい
・できるだけ近い額になる組合せを知りたい
・合計以下で近い額になる組合せを知りたい
・合計以上で近い額になる組合せを知りたい
いずれかによって話が違いきます。

●商品の種類

商品の種類が多いほど調べるべき組合せが増えます。
商品の種類が
・数種類しかないのか
・数千種類あるのか
で話が違います。

●単価と合計の比

各商品の単価が小さいほど／合計の額が大きいほど、
調べるべき組合せが増えます。
単価と合計の比が、
・1:10程度なのか
・1:1000といったオーダーになるのか
で話が違います。

商品の種類が少なく、解を一つ見つければ良いような場合には
Excelの機能である[ソルバー]で解が見つかることもあります。

一方、商品の種類が多い場合や、すべての解を知りたい場合は
VBA(マクロ)を使うのが現実的です。

VBAを使うにしても、さまざまなアルゴリズムが考えられますが
とりあえず再帰処理によるコードを上げておきます。

B1セルに合計、B4セル以下に各商品の単価を入力して実行すると
合計が一致する商品の個数をC列以降に書き出します。

以上ご参考まで。長乱文陳謝。

　Dim oRng As Range
　Dim oAry() As Long, tAry() As Long, rAry() As Variant
　Dim oCnt As Long, rCnt As Long, rLmt As Long
　Dim t As Variant
Sub Sample()
　Dim i As Long
　t = Timer
　Set oRng = Range(Cells(4, 2), Cells(Rows.Count, 2).End(xlUp))
　oCnt = oRng.Cells.Count
　rLmt = Columns.Count - 2
　rCnt = 0
　ReDim oAry(1 To oCnt)
　ReDim tAry(1 To oCnt)
　ReDim rAry(1 To oCnt, 1 To rLmt)
　oRng.Offset(0, 1).Resize(, rLmt).ClearContents
　For i = 1 To oCnt
　　oAry(i) = oRng(i, 1)
　Next i
　Call SampleS(1, Cells(1, 2).Value)
　oRng.Offset(0, 1).Resize(, rLmt).Value = rAry
　MsgBox rCnt & " 通り" & vbCrLf & Format(Timer - t, "0.00 秒")
End Sub
Private Sub SampleS(ByVal iIdx As Long, ByVal tSum As Long)
　Dim i As Long
　If tSum = 0 Then
　　rCnt = rCnt + 1
　　For i = 1 To oCnt
　　　If tAry(i) <> 0 Then
　　　　rAry(i, rCnt) = tAry(i)
　　　End If
　　Next i
　　If rCnt = rLmt Then
　　　oRng.Offset(0, 1).Resize(, rLmt).Value = rAry
　　　MsgBox rCnt & " 通り以上" & vbCrLf & Format(Timer - t, "0.00 秒")
　　　End
　　End If
　　Exit Sub
　End If
　If iIdx > oCnt Then Exit Sub
　For i = Int(tSum / oAry(iIdx)) To 0 Step -1
　　tAry(iIdx) = i
　　Call SampleS(iIdx + 1, tSum - oAry(iIdx) * i)
　Next i
End Sub

参考URL：

http://ja.wikipedia.org/wiki/%E9%83%A8%E5%88%86%E5%92%8C%E5%95%8F%E9%A1%8C

お礼

求めている物に一番近かったです。ありがとうございました。

回答No.1

kagakusuki

例えば、「ＡはＢより２個少ない」とか、「ＣはＢの４倍」と言うような、各商品毎の個数の関係が判明しているのであれば可能になりますが、その様な関係が無く、全く無関係に商品の個数が決まるのであれば、逆算する方法は、この世に存在しません。
　エクセルは勿論、どんなスーパーコンピューターを使って、天才数学者が計算したとしても答えは出ません。
　何故なら答えが存在しないからです。
　例えば、Ａが300円、Ｂが200円、Ｃが100円の時、合計が1000円だとします。
　Ａが２個、Ｂが１個、Ｃが２個の時は合計が1000円ですが、
Ａが１個、Ｂが２個、Ｃが３個の時や、
Ａが１個、Ｂが１個、Ｃが５個の時や、
Ａが１個、Ｂが３個、Ｃが１個の時も
全て合計が1000円になります。　各商品の値段と料金の合計が同じである組み合わせが幾つも存在しているのですから、他の情報が無ければ、どの組み合わせが正解であるのかを、判断する事は不可能です。

お礼

やはり組み合わせが膨大でした。ありがとうございました。