選択した範囲のデータと上限値を比較して上限を超えない最大値を算出する方法

質問

色々ネットで調べてみたんですが、当てはまるサイトがなかったので、この場をお借りしたいと思います。

金額がいくつかあってそれらを重複しないように足し算をします。
1 2 3
A 100 200 300

=(A1),(A2),(A3),(A1+A2),(A2+A3)
=100 ,200 ,300 , 300 ,500

それぞれ算出された値を次は「上限値」と比較して超えたものは「False」枠内に入ったものを「True」として、Trueのなかでさらに「上限額」に一番近い(True内での最大値)を出す。

上限額=400

今回の場合は(A2+A3)=500は超えてしまってるのでFalse、(A1),(A2),(A3),(A1+A2)の中で比較し、一番上限額に近い(A3),(A1+A2)が算出される。

このような関数もしくはプログラムが知りたいのですが、できそうでしょうか？

回答No.6

hallo-2007

>セル(A1～A6、D1～D6)を参照することは可能でしょうか？
離れたセルの範囲を指定すると言うことでしょうか？
隣接したセルならば
=MAX(IF(A1:B1000<=C$1,A1:B1000,0))
とでも指定すれば大丈夫ですが、離れていれば
=MAX(IF(A1:A6<=C$1,A1:A6,0))
=MAX(IF(D1:D6<=C$1,D1:D6,0)) と2つのセルに答えを出して大きい方
では、回答になりませんでしょうか。
ひとつセルで済ますなら
=MAX(MAX(IF(A1:A6<=C$1,A1:A6,0)),MAX(IF(D1:D6<=C$1,D1:D6,0)))
でCtrl+Shift+Enterで大丈夫では。

お礼

助かりました！！！
ありがとうございますｍ(＿＿)ｍ

回答No.5

hallo-2007

>A1～A3がなんの数値かを教えてくださいｍ(＿＿)ｍ
A1～A3は式では無視しています。
質問に
>1 2 3
>A 100 200 300
とあったので、A列に100,200,300と入っているのかなと思ったしだいです。
式は単にB1～B6の値の中で、C1以下の値で最大値を求めるだけです。

補足

ありがとうございます。

ちなみにB1～B6までを対象にしていますが、それ以外のセル(A1～A6、D1～D6)を参照することは可能でしょうか？

回答No.4

hallo-2007

>式の中にC1が書かれてないのはどうしてでしょうか？
失礼しました。
別セルに　=MAX(IF(B1:B6<=C$1,B1:B6,0))
でした。
B1～B6で400以下のものは、そのまま、以上は0にした結果を集計して
最大値の意味になります。

補足

またまた質問で申し訳ないのですが、数値が入っているセルがいまいちわからないです＾＾；
A B C
1 100 100 400
2 200 200
3 300 300
4 400
5 500
6 600

これはA1～A3、B1～B6、C1に値が入ってると考えています。
C1には上限値B1～B6には対象の数値(計算結果？)
A1～A3がなんの数値かを教えてくださいｍ(＿＿)ｍ

お手数おかけします。

回答No.3

fujillin

同じ数字は複数回使用することはないのですよね？
全体がｎ個として、そのうちの一つの組合せ（=1個の値という意味です）を調べるのは、単純にループを用いればよいですよね。

さて、1個（上記）～ｎ個（全部足したもの=一通りのみ）までの個数の組合せを網羅するためには、上のループを再帰的にしておいて、値をチェックした（m個の組合せをチェック）後に、ループのスタート値を「その時の添え字+1」とし、比較のための上限値を「その時の上限値-自分の値」にセットし直して呼び出すようにしておけば（m+1個以上の組合せをチェック）可能かと思われます。
（個々の値が正の値と決っている場合は、「上限値-自分の値」が正でない時には呼出しを省略することが可能です）

数値がランダムのままだと、とにかく全通り計算しなければなりませんが、最初に並べ替えておけば（例えば昇順）、加算して上限値を超えたらそのループは中断できますので（それ以降の値はさらに大きいので）、計算効率が上がると思われます。

随時、記録しておく必要があるのは、一つの値（それまでの最適値とその時の組合せも必要なのかも？）だけですので、チェックと記録の部分はさほど難しくはないでしょう。

回答No.2

hallo-2007

>重複しないように足し算をします。
これは既に解決済みでエクセルでの話として？
　 　A　B　　C
1　100　100　400
2　200　200
3　300　300
4　　　400　
5　　　500
6　　　600
とB列に足し算の値が入っている、C1に上限値が入れてあるとして
別セルに　=MAX(IF(B1:B6<=B$10,B1:B6,0))
と入れて　Ctrl＋Shift＋Enter　で決定してください。
配列関数として認識します（{関数}と{}で囲まれて表示されます）
B列の　400が表示されます。

補足

＞これは既に解決済みでエクセルでの話として？
これは既に解決済みです！
式の中にC1が書かれてないのはどうしてでしょうか？

回答No.1

n-jun

全ての組み合わせで和を求めながら、その和と上限値を比較していくとか。

何のソフトを用いるのかは不明ですが。

補足

和を求めて上限値を比較してそこからさらに上限値内の最大値を別のセルに表示させるもしくは色づけをさせたいのです。