エクセルの関数についての質問です。

質問

エクセル関数について質問です。
初心者ですが、宜しくお願い致します。

例えば、A列に名前、B列に数字、C列にコストを並べて、30行の種類が有る場合。
：C列のコスト10000以内で、B列の数字が一番大きくなる、10行の組み合わせの式を作りたい時には、どうすれば良いですか？

30行の中で、コストが10000以内で、Bの値が一番大きくなる組み合わせの１０行が知りたいです。
説明下手で申し訳ございませんが、どなたかご回答宜しくお願い致します。

回答No.5

shibushibu1 · ベストアンサー

こんばんは、No.2による再回答です。
補足、御礼ありがとう御座います。読ませて頂きました。

関数ではなく「ソルバー」という分析ツールを使うのが一番簡単です。

まずは、下記のURL内のマニュアル通りにソルバーをエクセル内に入れて下さい。
http://office.microsoft.com/ja-jp/excel-help/HP010342660.aspx

その後、添付画像のような形式で表を作ります。

※「採用/不採用」欄は、その行を採用するなら1、不採用なら0という表記にしています。
最終的に１が入る10行が、tad1201さんが求めている10行になります。

【E３】＝０と１を直接入力しています。どこにどっちを入力するか、現時点では適当でOKです。
【F３】＝C3*E3
【G3】＝D3*E3

また、表の一番下の行３３にはSUM関数でE、F、G列の合計値を出します。

その後、「ツール」タブから「ソルバー」を起動し、
添付画像通りにそれぞれの項目を入力します。

【目的セル】＝F33
【目標値】＝最大値
【変数セル】＝E3:E32
【制約条件】・・・追加ボタンから作成します。
・E33=10
・E3:E32＜＝1
・E3:E32＞＝0
・E3:E32＝整数
・G33＜＝10,000

※整数の指定時には「＝」ではなく「int」を入れます。

これによって、

・10行限定
・採用/不採用欄は1か0限定
・コストは10,000以下

という条件下で、数字の合計が最大になるような
0と1のパターンを見つける準備が整いました。
満を持して、ソルバーの「解決」ボタンをクリックしましょう。

次に、右上の「解答」をクリックし、反転させてから、OKをクリックします。

すると、隣に「解答レポート」というシートが自動で現れます。
その中の「最終値」という欄が「1」になっている行の組み合わせが、
「コスト10,000以下で数字を最大にする」組み合わせの答えです。

この表の場合、コスト10,000以下で数字を最大化したい場合は、
「No.10、11、15、17、18、19、24、25、26、30」の10社を選定すれば、
数字合計が11,134になり最大になるという結果になりました。

分からなければ、また補足欄にてご連絡下さい。
(その場合、エクセルのバージョンを教えて頂けると助かります。)

お礼

度々の丁寧なご回答、本当に有り難う御座います。

早速ご教示頂いた方法を試してみます。

取り急ぎお礼まで。

補足

有り難う御座います。

お陰様で、ほぼ出来ましたが、１点だけおかしな箇所が御座いました。

採用、不採用の欄に、端数が出る箇所が有り、11社が選定されてしまいました。

F、Gの数字も下記の端数で割られていました。

0.391・・・と
0.608・・・で、２社で足して１になりました。

一箇所不明な点がありましたが、そこが関係しているのでしょうか？

「E3:E32＝整数

※整数の指定時には「＝」ではなく「int」を入れます。」

が解らず、上記以外の項目は全て入力致しました。

エクセルは、2010を使用しております。

大変お手数をお掛け致しますが、引き続き宜しくお願い致します。

回答No.8

shibushibu1

No.2,5の再回答です。補足ありがとうございます。

おっしゃる通り、あとは整数の条件を付けるだけですね！

条件追加の時、真ん中に=や=>を等を選択する項目があると思いますが、
その中にintという選択項目はありませんか？
ここは=ではなく、intを選択して下さい。

それを選択すると、整数を指定することが出来ます。

お礼

出来ました！！！

本当にお手数をお掛けしました。

有り難う御座いました。
上司に説明すると、分からんから、今後も君がやりなさい！と言われましたが。。。

お陰様で前回のミスを挽回出来ました。

本当に、本当にありがとうございました。

回答No.7

MarcoRossiItaly

No.3・4です。そうですね。ソルバーが良いですね。2変数までならゴールシークという機能（アドインでない一般のソルバー機能）もExcelにはあるんですが、2変数だけじゃちょっとムリっぽいですね。掲示板でソルバー使わないと解けなさそうな問題なんて、久々に見ました（笑）皆さんの素晴らしいご回答を参照なさってください。上司の方には「組み合わせとか、簡単に求められる式はないので諦めてください」と言いましょう（笑）Excelの前に数学力が試されるかも。

お礼

ご回答有り難う御座います。

皆様が、親切、丁寧にご回答下さったお蔭で、何とか解決する事が出来ました。

本当に有り難う御座いました。

回答No.6

keithin

んーと？
ご相談は、３０行の中からコスト「の合計が」１０，０００以下で、Ｂ列「の合計が」最大になる１０行の組み合わせを探したかったということですか？
「」の中、ご相談には書かれていませんが。

ソルバーを使う場面ですが、もうちょい簡単に。
Ａ列に名前（Ａ２：Ａ３１）
Ｂ列に得点（Ｂ２：Ｂ３１）
Ｃ列にコスト（Ｃ２：Ｃ３１）

サンプルデータ：
Ｂ列：
9
1
8
7
6
9
9
2
6
4
1
7
1
2
5
8
4
1
6
4
2
1
7
2
3
3
7
3
9
7

Ｃ列：
1750
1750
2250
1250
1000
1750
2250
500
750
2250
2250
1500
250
1250
2250
1000
1000
1500
2000
1500
1500
750
1000
1750
250
250
1000
1000
1750
2250

評価関数として
Ｇ２に=SUMPRODUCT(B:B,D:D)
Ｇ３に=SUMPRODUCT(C:C,D:D)
Ｇ４に=SUM(D:D)
をそれぞれ用意。

ソルバーを開始して
目的セルは　Ｇ２　で最大値にマーク
変化するセルはＤ２：Ｄ３１を指定

Ｄ２：Ｄ３１は、実施前は必ず空っぽにしておく

制約条件として
Ｇ３　＜＝　１００００　を追加
Ｇ４　＝　１０　を追加
Ｄ２：Ｄ３１　データ　を選択（バイナリと自動記入される）
で実行すると

途中「制限時間」とか出てきますが、無視して続行すると

結果:
1
0
0
1
1
1
3.92031E-10　→ゼロに
0
1
0
0
-3.92032E-10　→ゼロに
0
0
0
1
0
0
0
0
0
0
1
0
1
1
1
0
0
0

といった具合に落ち着きます。

お礼

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

皆様のご回答、本当に助かっております。

こちらで教えて頂いた方法も試してみたいと思います。

お礼が遅れまして大変申し訳ございません。

本当に有り難う御座いました。

回答No.4

MarcoRossiItaly

No.3です。すみません、No.2さんへの補足を見逃しました。意図が分かりました。

ご要望の組み合わせを求めるには、マクロコードを書いて実行すればいいと思うのですが、3千万通り以上（= 30C10）の組み合わせを全部調べることになり、ものすごい時間がかかります。

やはり、No.3の要領でだいたいの当たりを付けて、目視で組み合わせを考えるのが早いかと思います。

お礼

ご回答有り難う御座います。
仰る通り、現在まで、目視で確認して、コストの割り振りをしておりました。

ですが、先日私が目視で算出したコスト割よりも良い割り振りが有り、上司に指摘されてしまい、エクセルの関数で何とかならないかと、宿題を出されたので、質問させて頂きました。

No,2様の新たなご回答を基に、もう一度頑張ってみます。

回答No.3

MarcoRossiItaly

関数もいいですが、結果が得られればいいだけなら、「フィルタ」あるいは「オートフィルタ」がいちばんお手軽だと思います。

先に、表に1列を追加し、その列の各行に、通し番号を振ります。

次に、データの並んでいる表中のどこかをクリックした状態で、リボンあるいはメニューバーの「データ」から、「フィルタ」あるいは「オートフィルタ」をクリック。表示されるプルダウンのボタンのうち、まずB列のボタンで、「降順」で並べ替え。続いて、C列のボタンで、「指定の値以下」により10000を指定。

抽出された表をコピーして別シートに保存するなど、お好きに。並べ替えた表を元の順に戻すには、通し番号を振った列のボタンで、「昇順」で並べ替えます。

回答No.2

shibushibu1

こんにちは。

「C列が10,000以下」かつ「B列が大きい行のTOP10」が知りたいわけですね？

画像添付しましたので、よろしければご参照下さい。

B、C、D列は数字を直接入力しています。
E、F、G列の数式の中身は以下の通りです。

【E3】=IF(D3<=10000,C3,"")
【F3】=IF(ISERROR(RANK.EQ(E3,E:E,))=TRUE,"",(RANK.EQ(E3,E:E,)))
【G3】=IF(F3<=10,F3,"")

この式を入れて、あとは下までコピーして貼り付けしてあるだけです。

これでG列にTOP10のみの順位が表示されました。

TOP10以外の行を削除したい場合は、表内全ての行をドラッグした上で、
「データ」タブの「並び替え」を使用してください。
「最優先されるキー」欄にG列を設定すれば、
順位と同じ順番に並べ替わります。
あとは不要な行を一気に削除すれば完成です☆

分かりづらければまた遠慮なく聞いてください。

お礼

教えてご回答ありがとうございます。
説明が下手で申し訳ございません。
例えば、
A1が山田商事、B1が3000、C1が1000
A2が田中商事、B2が5000、C2が1500
A3が佐藤商事、B3が2000、C3が800
の様な表がA30、B30、C30まで有るとします。

Cのコストが合計10000以内に収まり、Bの値が一番大きくなる１０人の組み合わせを表す式が有れば、ご教示頂きたいです。

宜しくお願い致します。

回答No.1

-9L9-

自分で何がやりたいのかわかっていない質問ですね。もちろん質問文自体意味不明なので、回答はできません。
以下の点に気を付けて、具体的かつ論理的に何をどうしたいのかを見直せば、おのずと答えが出てきます。答えが出てこなければ、それはできないことということです。
・A列、B列、C列のデータはそれぞれどのような意味があり、それぞれが相互にどのように関連するのか
・行固有のデータ（ID）はあるか
・１行が１件のデータと考えてよいのか、行に関係なく各セルのデータがばらばらに存在しているのか
・「30行の種類がある」とは具体的にどういうことか、セルのデータ・行のデータとはどう関連するのか
・「組み合わせ」とは何のことか、何をどう組み合わせるというのか
・「コスト1000以内」とあるが、既にC列にある「コスト」のデータとは違うのか（C列を調べれば済むことではないのか）
・「一番」といいながら「10行の組み合わせ」とはどういうことか、同じ結果が10個出現するということなのか、1番目から10番目ということか

EXCELで計算するには数理的に評価できることが前提です。意味が明確でないものは計算できません。まず計算対象をきちんと定義することから始めなければなりません。そのうえで、目的に沿った計算やデータ操作の機能がEXCELに備わっているかどうかを検討します。今の質問内容では判断できることではありません。