皆様こんにちは。 Excelについての質問です。 少し説明がつたないですが、ご了承ください。。。 例

皆様こんにちは。

Excelについての質問です。
少し説明がつたないですが、ご了承ください。。。

例えば、
1～20までの行の中にランダムで5桁の数字が羅列しています。
そして21行目に合計金額（A）が計算されています。

そして22行目には4桁の数字（B）が入力されています。

23行目には（A）から（B）を引いた数字（C）が入力されています。

この（C）については、1～20行目までの数字のなかで、
ランダムに選ばた数字の合計になっています。

複数該当する場合もあると思いますが、
該当するものを引き出すExcelの計算式などあるのでしょうか。

恥ずかしながら無知で申し訳ありませんが、
ご回答のほど宜しくお願いいたします。

質問者からの補足コメント

• 早々にご回答いただきありがとうございます。
  
  計算式ではなくても
  何か方法はございますか？

    補足日時：2017/10/04 18:22

• 【補足/訂正】
  
  皆様困惑させてしまい申し訳ありません。
  
  整理してみました。
  
  ❶ A1～A20まで各商品の売上が入力されています。
  
  ❷A21には❶の合計金額が入力されています。
  
  ❸A22にはお客様の入金金額の総合計が入力されています。
  
  問題発生！
  入金金額が足りません！！
  
  ❹A23にはその不足している差額が入力されています。
  
  そこで先程の質問です。
  お客様がいくつの商品を入金して、
  どの商品がいくつ入金されているかが分かりません。
  
  A23に入力されている金額が
  A1～A20までのどの組み合わせで
  成り立つが知りたいです。
  
  どうでしょうか？
  つたない文章ばかりで申し訳ありません。。。

    補足日時：2017/10/04 18:46

No.8 回答者： WindFaller 回答日時： 2017/10/10 11:38

なお、コードの中で、以下の部分を、

　　If Application.Sum(ar) = TargetNum Then

If Abs(Application.Sum(ar)-TargetNum)<11 then
とすれば、10の誤差は範囲の中に入れてくれます。
ただし、見つけた順からですから、同等の誤差が後に出てきたものに関しては、検出できません。これは、ソルバーよりも圧倒的に速いです。

もう少し、これについては私自身が専門的な勉強が必要のようです。

No.7 回答者： WindFaller 回答日時： 2017/10/09 12:21

改めて、マクロで考えてみました。

必ず、その組み合わせがあるという前提の元に組み立てられています。
不足金額は、5個までで、それ以上では見つからないようです。（スペックの関係？）
添付画面のように、黄色の部分の数値を探す、つまり少ない分の金額を探します。
ファジーな検索は、場合によっては可能かもしれませんが、そこまでは、考えていません。また、同じ請求金額でも、検索は可能です。ただし、早い方の順からチェックしていきます。

'//
Option Explicit
Private TargetNum As Long
Private flg As Boolean
Private DataOut As Range
Private MissingCnt As Long
Sub Main()
Dim i As Long, j
Dim rx
Dim x
Dim c As Range
Dim cnt As Long
Dim cnt1 As Long
Dim dic As Object
Dim v As Variant
　Set DataOut = Range("E1") '解の出力する場所
　TargetNum = Range("A22").Value　'不足している金額
　
　MissingCnt = 5 '規定では探す個数は、5個までです。それ以上は無理です。
　
　flg = False 'フラグは必ず、False
　DataOut.CurrentRegion.Columns(1).ClearContents
　On Error Resume Next
　'元のデータ範囲
　rx = Range("A1:A20").Value
　If Err() <> 0 Then MsgBox "範囲にエラーがあります。", vbCritical: Exit Sub
　On Error GoTo 0
　
　x = Application.Transpose(rx)
　For i = 1 To UBound(x)
　　If x(i) = TargetNum Then
　　　 DataOut.Value = x(i)
　　　 MsgBox "見つかりました!", vbInformation
　　　 flg = True
　　　 cnt = 1
　　　 GoTo EndLine1
　　End If
　Next i
　For i = 2 To MissingCnt
　　ReDim Preserve x(0 To UBound(x) - 1)
　　sCombinations x, i
　　cnt = i
　　If flg Then GoTo EndLine1
　Next i
EndLine1:
　If flg = False Then
　　 MsgBox "不足する数値が見つかりませんでした。", vbExclamation
　　 Exit Sub
　Else
　　　 Set dic = CreateObject("Scripting.Dictionary")
　　　 cnt1 = 1
　　　 For Each v In DataOut.Resize(cnt)
　　　　 If dic.Exists(v.Value) = False Then
　　　　　 dic.Add v.Value, cnt1
　　　　 Else
　　　　　 dic.Item(v.Value) = dic.Item(v.Value) + 1
　　　　 End If
　　　 Next
　　 For Each c In Range("A1:A20")
　　　 If dic.Exists(c.Value) Then
　　　　　c.Offset(, 1).Value = 1
　　　　　If dic.Item(c.Value) > 1 Then
　　　　　　dic.Item(c.Value) = dic.Item(c.Value) - 1
　　　　　Else
　　　　　　dic.Remove c.Value
　　　　　End If
　　　 Else
　　　　　c.Offset(, 1).Value = 0
　　　 End If
　　 Next c
　End If
End Sub
Sub sCombinations(ByVal Stock, ByVal r As Long)
　On Error GoTo errHandler
　Dim num As Long
　Dim ar As Variant
　Dim i As Long, j As Long
　Dim k As Long
　num = UBound(Stock) - LBound(Stock)
　r = r - 1
　ReDim ar(0, r)
　Dim idx() As Long
　ReDim idx(0 To r)
　For i = 0 To r
　　idx(i) = i
　Next i

　Do
　　For j = 0 To r
　　　ar(0, j) = Stock(idx(j))
　　　DoEvents
　　Next j
　　k = k + 1
　　If Application.Sum(ar) = TargetNum Then
　　　DataOut.Resize(r + 1).Value = Application.Transpose(ar)
　　　flg = True
　　　MsgBox "見つかりました", vbInformation
　　　GoTo EndLine:
　　End If
　　i = r
　　While (idx(i) = num - r + i)
　　　i = i - 1
　　　If i = -1 Then
　　　　Exit Do　''
　　　End If
　　Wend
　　idx(i) = idx(i) + 1
　　For j = i + 1 To r
　　　idx(j) = idx(i) + j - i
　　Next j
　Loop
errHandler:
　If Err.Number <> 0 Then
　　MsgBox Err.Number
　End If
EndLine:
End Sub

No.6 回答者： WindFaller 回答日時： 2017/10/04 23:18

私は、この手の質問は始めてではありません。

以前も、ここの「教えて！goo」で経験したことがあります。

例えば、20個以上の数値があって、その合計より少ないの数値がある。
その数値は、その合計のどれかを足したものとしたら、
その中で、どれとどれを組み合わせたら、その数字になるかをExcelで求めたい。

ということでした。

Excelのアドインのソルバーを使って解くという問題でしょうね。

やってみまして出来ましたが、超鈍足ですね。
10個の数値の組み合わせの時に、184,756通りの組み合わせになってしまいます。
マクロで組めればよいのですが、私は、この手のものは手を出したことがありません。かなり自信がありません。

p.s.
補足読みました。

現実の問題ですが、どれがどれというふうに払ったつもりでしたが、たぶん、面倒だったのだろうと思います。相手は、こちらの思わくに反して、全部合算して、古い順から買掛金を消してしまいました。

以下の画像は、ソルバーの画面です。少ない金額を導き出すか、合計金額で導き出すか、確率の高い方を選択します。画面では、C列のC1は、=A1 * B1 ～ 20行目まで。
C21は、=SUM(C1:C20)という数式が入れてあることにします。

No.5 回答者： 銀鱗 回答日時： 2017/10/04 19:24

続いて、A23セルが「0」以外…プラスになったりマイナスになって…で、A1セルからA20セルまでに一致する値が無い場合。

（複数の支払いが滞っている場合がこれに当たります）

　=MATCH(A23,A1:A20,0)
これは同じ。
しかし、これが一致する値を見つけられない場合はエラーになります。
ですので、エラーであるかを調べる関数を使います。

　＝ISERROR(数式）

これで、数式の結果がエラーなら”True”、エラーでなければ”False”が返ってきます。
条件付き書式の場合は、条件が成立した時にセルを修飾（色を付ける）ので、
この場合はエラーなら…ということで、そのまま数式に上の式を入れます。

　=ISERROR(MATCH(A23,A1:A20,0))

これをA1セルからA20セルまで指定します。
そしてこれとは別にA22セルにも指定します。

そしてセルの色を付ける設定をしましょう。


・・・
A1セルからA20セルの範囲を選択してから「条件付き書式」で「新しいルール」から「数式を～」を選んで設定すると、
セル一つ一つで設定しなくて済みます。
ただしその場合は、数式中で使っているセル番地の表記を少し変える必要があります。
　A1→A$1
のようにすべての数字の前に "$" を付けてください。

No.4 回答者： 銀鱗 回答日時： 2017/10/04 19:10

18:46の補足を確認しました。

それならOK。
意図が通じます。

基本的に
MATCH関数でA1セルからA20セルの範囲に、A23セルと同じ値があるかを検索すればよいのです。

この場合は「条件付き書式」の「数式で～」に数式を入力し、該当するセルに色を付けるという作業になります。
また、A23セルが0以外、該当するセルが無い場合、A1セルからA22セルまでのセルの見直しが必要という事で、このセルすべてに色を付けるという操作も必要でしょう。

・・・数式の作り方・・・
まずはMATCH関数の使い方を確認してください。

　=MATCH( 検索値 , 範囲 , 照合の型 )

のように指定します。
MATCH関数は検索値が範囲の何番目にあるかを返す関数です。
ですのでこの質問のケースでは、
　「検索値」には A23セル を指定、
　「範囲」には A1セルからA20セル を指定、
　「照合の型」には「0」（ゼロ）を指定しましょう。
※使い方の詳細は必ず自身で確認しておくこと。

これをA1セルからA20セルに指定するのですが、それぞれのセルの位置を比較する必要があります。
行番号を拾えばよいので、ここはROW関数を使います。

　＝ROW()
と、括弧の中に何も入れないと、この関数が使われているセルの行番号が値として返ってきます。
（A1セルなら「1」、A2セルなら「2」…のように）

あとはMATCH関数の結果とROW関数の結果を比較して、等しければそのセルに色を付けるという事になります。

するとA1セルからA20セルに指定する条件付き書式の数式は
　＝MATCH(A23,A1:A20,0)=ROW()
こうなります。
あとは条件が成立した時にセルに色を付ける設定を行いましょう。


・・・
そして、A23セルが0以外の時は別回答します。
一度には理解できませんよね。

No.3 回答者： nitto3 回答日時： 2017/10/04 18:27

23行目には（A）から（B）を引いた数字（C）が入力

（C）については、1～20行目までの数字のなかで、ランダムに選ばた数字の合計
この二つは必ず同じになることはないはずで、設定自体間違っているよ。

No.2 回答者： 銀鱗 回答日時： 2017/10/04 18:24

(´・ω・`)？

こーゆー事でしょうか。

１．A1セルからA20セルまで、任意の値段が入力されている。
２．A21セルにはA1セルからA20セルの合計金額。
３．A22セルには何かよくわからない4桁の数字。
４．A23セルには「＝A21-A22」
５．しかしながらA23セルの値はA1セルからA20セルのうちの一つの値になる。

かな？
２がA1からA20の合計ではないような気がするんですけど、A21セルが「合計」と言っているので、
どこかのセルを積算しているはずなので、この質問ではA1セルからA20セルと判断させてもらいました。

…すると矛盾が多くてどうにもならんのですわ。
具体的な数字を入れて説明してください。

No.1 回答者： mike_g 回答日時： 2017/10/04 17:40

「計算式など」はあるかも、でも「計算式」はない。