どのような関数を使えばいいか分かりません（訂正版）。

質問

以前、スレ立てたのですが質問の内容に間違いがあったので訂正して再度質問させていただきます。
Excelでちょっとした計算をしたいのですが、どのような関数を使えばいいか分かりません。
例）
2,490
31,520
40,890
20,930
75,660
11,320
44,160
例えば上記のような数値が各セルにあるとします。
で、この数値のうちどれかの「和」が
63,770
になるはずですが、どの行の数値がその対象になっているか調べるにはどうしたら良いのでしょう？
「なるはずと」と書きましたが、ならない場合もあるかもしれません。
場合によっては何通りも抽出できるので関数を使うだけではダメなのでしょうか？

回答No.6

noname#95859

A No2で書き込みをしたものです。
もう少し考えて、改良しました。
答えは１つの組合せに対し複数行でてくることはないです。
従って、No5のマクロは不要です。
（つまり、処理時間も大幅に改善されています）
100行のデータに対し、No5は、約70秒、今回のロジックでは約9秒でした。
----------------------------
Sub Macro1()
Target = 63770
Count = 0
Worksheets("Sheet2").Cells.ClearContents
E_rowpos = Worksheets("Sheet1").Cells(65536, 1).End(xlUp).Row
hani = "A1:A" & E_rowpos
myArray = Worksheets("Sheet1").Range(hani).Value
For i = 1 To E_rowpos
If myArray(i, 1) = Target Then
Count = Count + 1
Worksheets("Sheet2").Cells(Count, 1).Value = myArray(i, 1)
End If
If myArray(i, 1) < Target Then '-------------------------------------------------------------２つの場合
For j = i + 1 To E_rowpos
If myArray(i, 1) + myArray(j, 1) = Target Then
Count = Count + 1
Worksheets("Sheet2").Cells(Count, 1).Value = myArray(i, 1)
Worksheets("Sheet2").Cells(Count, 2).Value = myArray(j, 1)
End If
If myArray(i, 1) + myArray(j, 1) < Target Then '----------------------------------３つの場合
For k = j + 1 To E_rowpos
If myArray(i, 1) + myArray(j, 1) + myArray(k, 1) = Target Then
Count = Count + 1
Worksheets("Sheet2").Cells(Count, 1).Value = myArray(i, 1)
Worksheets("Sheet2").Cells(Count, 2).Value = myArray(j, 1)
Worksheets("Sheet2").Cells(Count, 3).Value = myArray(k, 1)
End If

If myArray(i, 1) + myArray(j, 1) + myArray(k, 1) < Target Then '-------４つの場合
For l = k + 1 To E_rowpos
If myArray(i, 1) + myArray(j, 1) + myArray(k, 1) + myArray(l, 1) = Target Then
Count = Count + 1
Worksheets("Sheet2").Cells(Count, 1).Value = myArray(i, 1)
Worksheets("Sheet2").Cells(Count, 2).Value = myArray(j, 1)
Worksheets("Sheet2").Cells(Count, 3).Value = myArray(k, 1)
Worksheets("Sheet2").Cells(Count, 4).Value = myArray(l, 1)
End If
Next l
End If
Next k
End If
Next j
End If
Next i
End Sub

-------------------
本問題は、再帰プログラムを考えるのに適していると思います。
ハノイの塔は、典型的な「再帰プログラム」の例ですが、
本件にも適用できるのではないかと、推測します。

回答No.5

noname#95859

A No.2 で書き込みをした者です。
3つの和のケースが差し当たり、今回の回答であるとして、
VBAのチェック結果が、多数出てきてしまった対策です。
前回のマクロ　Macro1を走らせた後で、以下に記述する
Macro2を走らせてください。
結果は、ユニークなものだけとなります。
但し、大前提は、４つの数字の「和」までです。
--------------------------------------------
Sub Macro2()
With Worksheets("Sheet2")
E_rowpos = .Cells(65536, 1).End(xlUp).Row
For i = 1 To E_rowpos '--------------横方向で並び替え
hani = Range(.Cells(i, 1), .Cells(i, 1).End(xlToRight)).Address
.Range(hani).Sort Key1:=.Rows(i), Order1:=xlAscending, Orientation:=xlLeftToRight
Next

'-----------------------------------------------横方向で並び替え
.Cells.Sort _
Key1:=Columns("B"), Order1:=xlAscending, _
Key2:=Columns("C"), Order2:=xlAscending, _
Key3:=Columns("D"), Order3:=xlAscending, _
Orientation:=xlTopToBottom

.Cells.Sort _
Key1:=Columns("A"), Order1:=xlAscending, _
Key2:=Columns("B"), Order2:=xlAscending, _
Key3:=Columns("C"), Order3:=xlAscending, _
Orientation:=xlTopToBottom

For i = E_rowpos To 2 Step -1
myArrayL = Range(.Cells(i, 1), .Cells(i, 1).End(xlToRight)).Value
myCountL = Range(.Cells(i, 1), .Cells(i, 1).End(xlToRight)).Count
myArrayU = Range(.Cells(i - 1, 1), .Cells(i - 1, 1).End(xlToRight)).Value
myCountU = Range(.Cells(i - 1, 1), .Cells(i - 1, 1).End(xlToRight)).Count
If myCountL = myCountU Then
flag = 0
For j = 1 To myCountL
If myArrayL(1, j) <> myArrayU(1, j) Then flag = 1
Next
If flag = 0 Then .Cells(i, 1).EntireRow.Delete
End If
Next
End With
End Sub

回答No.4

p-applepie

こんにちは。
前のスレッドで回答を書き込んだものです。
前の回答を少し変更すれば和にも対応出来ますよ。

=SUMIF($A:$A,63770-A1)

先の回答と同じ様に、相手がいない場合は0、相手方の数値があれば
B列に表示されます。

0を表示させない時は
=IF(=SUMIF($A:$A,63770-A1)=0,"",=SUMIF($A:$A,63770-A1))
としてください。

お礼

たびたびご回答ありがとうございます。
質問が説明不足で申し訳ありません。
「例)」の場合ですと31520、 20930、 11320の３つが答えになるように、何通りの答えが出るかは分からない場合でした。
僕もいろいろ考えましたが関数を使っても記述が果てしなく長くなりそうではないでしょうか？

回答No.3

merlionXX

仮にA1からA7に数値がならんでいるとしたら
B1に
=MATCH(63770-A1,$A$1:$A$7,0)
と入れて、B7まで式をオートフィルでコピーしてください。
該当があればその行番号を、なければ#N/Aを返します。

補足

回答ありがとうございます。
質問が曖昧だったので申し訳ありません。
例）の場合ですと、７つの数値の内２つ以上の数値の「和」が63,770になるときの、その２つ以上の数値に該当するものと言う意味でした。

回答No.2

noname#95859

「和」ということで、力ずくでやってみました（総当たり法のVBA)
結果は、31520、 20930、 11320
-------------------------------------
前提
４つの「和」までチェックする
Sheet1のA1から下方に向かって数値が入っているものとします
Sheet2が存在すること（結果が入ります）
-------------------------------------
Sub Macro1()
Target = 63770
Count = 0
E_rowpos = Worksheets("Sheet1").Cells(65536, 1).End(xlUp).Row
hani = "A1:A" & E_rowpos
myArray = Worksheets("Sheet1").Range(hani).Value
For i = 1 To E_rowpos
If myArray(i, 1) = Target Then
Count = Count + 1
Worksheets("Sheet2").Cells(Count, 1).Value = myArray(i, 1)
End If
If myArray(i, 1) < Target Then '---------------------------------------２つの場合
For j = 1 To E_rowpos
If myArray(i, 1) + myArray(j, 1) = Target Then
Count = Count + 1
Worksheets("Sheet2").Cells(Count, 1).Value = myArray(i, 1)
Worksheets("Sheet2").Cells(Count, 2).Value = myArray(j, 1)
End If
If myArray(i, 1) + myArray(j, 1) < Target Then '------------------３つの場合
For k = 1 To E_rowpos
If myArray(i, 1) + myArray(j, 1) + myArray(k, 1) = Target Then
Count = Count + 1
Worksheets("Sheet2").Cells(Count, 1).Value = myArray(i, 1)
Worksheets("Sheet2").Cells(Count, 2).Value = myArray(j, 1)
Worksheets("Sheet2").Cells(Count, 3).Value = myArray(k, 1)
End If

If myArray(i, 1) + myArray(j, 1) + myArray(k, 1) < Target Then '-------４つの場合
For l = 1 To E_rowpos
If myArray(i, 1) + myArray(j, 1) + myArray(k, 1) + myArray(l, 1) = Target Then
Count = Count + 1
Worksheets("Sheet2").Cells(Count, 1).Value = myArray(i, 1)
Worksheets("Sheet2").Cells(Count, 2).Value = myArray(j, 1)
Worksheets("Sheet2").Cells(Count, 3).Value = myArray(k, 1)
Worksheets("Sheet2").Cells(Count, 4).Value = myArray(l, 1)
End If
Next l
End If
Next k
End If
Next j
End If
Next i
End Sub

やってみれば、わかりますが、Sheet2には、6行にわたって、出てきてしまいました。3つの順列＝６通りです。
判っていたのですが、やっていません。
もし、結果が[３つの数字の和]で10種類もあるとするともうそれだけで、120行出てきてしまいます。対策用のVBAを考えなければいけませんが・・・

お礼

「力ずく」で、ありがとうございました(^_^;
あまりVBの知識がないのですが、考え方としては理想通りです。
しかし、100以上のデータで実行しようとしてましたので結局は果てしない計算の繰り返しが必要なんで、結果的にシート１枚に収まらないかもしれません(^_^;
No5のご回答も参考にさせていただきました。

回答No.1

neKo_deux

どれか２つの数の和の問題でしたら、
　　　　　　2,490　31,520　40,890　20,930　75,660　11,320　44,160
2,490
31,520
40,890
20,930
75,660
11,320
44,160

の表でそれぞれの組み合わせの和が網羅できます。
=IF($A2+B$1=63770,"○","")
とかの式で検出できるでしょう。

--
複数の数値を選択する場合ですと、解析的に解くのは困難で、線形計画法などの手法が必要になります。
ちなみに、その問題は、そういう分野でよく取り扱われる「ナップザック問題」です。

ナップザック問題をExcelで解く
http://www.geocities.co.jp/SiliconValley-Oakland/8139/

のサイトに方法やVBAのプログラムが紹介されています。
簡潔ですが、実用的な内容になっていますね。

質問のデータ件数であれば、

| １．手当たり次第法

で十分でしょう。
データ数が100とか以上になる場合、

| ２．総当たり法
| ３．動的計画法

で解くのが現実的です。

お礼

回答ありがとうございます。

「ナップザック問題」と言う言葉は始めて聞きましたが、まさにこのような問題です。
実はデータが100以上になるので、普通に考えれば答えが複数発生してしまうのであまり意味ないですね。
一応、上記のVBAを試してみたいと思います。