- 締切済み
組み合わせを上手に選ぶ（内容を変えて再投稿）
手元のExcelデータには、Ａ列に商品名、Ｂ列に値段が入力されています。 ここでＣ列に１以上の整数を１・２・３・・・と順に、以下の条件に合うように自動的に入力したいです。 条件とは、Ｃ列に同じ数値が入った行のＢ列セルの合計が、（全てのＣ列の数値において）１００００以上になるように、そのうえでＣ列の最大値がより大きくなるようにすることです。 商品の内容（値段）によっては、全て１００００以上になる組み合わせ方が複数存在することもありますが、任意の１種類だけＣ列に自動入力できれば良いです。 但し、Ａ・Ｂ列への入力が今後増えていくので、その都度Ｃ列の自動入力をし直せるものがよいです。 以上の内容で、Excelを上手に使うアイデアを紹介していただければと思います。
- ao-b
- お礼率74% (230/307)
- Excel（エクセル）
- 回答数10
- ありがとう数5
- みんなの回答 （10）
- 専門家の回答
みんなの回答
- kkkkkm
- ベストアンサー率66% (1734/2604)
未解決終了したものを追いかけるような感じになりますが。カテゴリをエクセルではなく数学にして 一定数の価格の違う商品を袋に一定額（例えば10000円）以上詰め込み、袋の数をなるべく多くしたい(金額の増加単位が10円単位とか、最低価格と最高価格も記載すれば範囲が絞れます)その時に、各々の袋に入る商品がわかる という問題が数学的に解決できるのか、そのようなアルゴリズムがあるのかどうかを確認してみてはいかがでしょう。袋の数が増えれば１個の袋の金額は10000円に限りなく収束していくと思いますので、この部分に関しては何がしかの数学的解法があるような気がします。そこに「各々の袋に入る商品がわかる」の部分を加えた数学的解法があるのかは疑問なところではありますが。 アルゴリズムのカテゴリがないので数学ではなければプログラム系でもいいかもしれません。エクセルのマクロに応用することを考えたらVisual Basicが移行しやすいでしょう。
- kkkkkm
- ベストアンサー率66% (1734/2604)
> 、「Ｃ列のセルにどのように１および２を入れても >『Ｃ＝１の行のＢ列の値の合計もＣ＝２の行のＢ列の値の合計も１００００以上』にならないこと」を証明したいです ちなみに、B列の値の合計が10000を超える場合、上記の条件に合致することは絶対にありえません。 また、10000以下の組み合わせがあるかないかを2個ずつ総当たりで加算して調べると書きましたが、過去の補足の例を見ると1個でも組み合わせと考えるみたいなので、取り下げです。実は総当たりでなくても、B列を昇順に並び替えて小さい方から調べたら即わかることでした。 とりあえず、その結果をもって結局何がしたいのかいまだ理解できません。
- MackyNo1
- ベストアンサー率53% (1521/2850)
皆さんからの回答は、おそらく、ao-bさんのご希望の処理と違った方向になっているように思いますので、ご希望の回答ではないかもしれませんが、あえてコメントします。 まず、前回の質問と関連する質問の場合は（経過を見ていると全く同じ質問をしているように思います）、以下のように前回の質問のリンクを付けて、これまでの経過をわかるようにして、回答者に無駄な回答をさせないようにしましょう。 http://questionbox.jp.msn.com/qa8712221.html これまでの経過を見ていると、質問者のご希望の処理は、B列にバラバラの数字が入力されていて、これらの数字の中からいくつか適当に組み合わせて10000を超える組み合わせをできる限りたくさん作成したいということではないでしょうか（A列やC列の説明が質問内容をわかりにくくしているように思います）。 この場合は、私がすでに回答したように、Excelで用意されている最良な方法はソルバーの機能を利用することになりますが、この場合でも一度にすべての組み合わせを提示させることはできません。 また、試行錯誤的に計算をするソルバーを利用する場合でも、当然のことですが、Excelが計算するための基準が必要です。 すなわち前回も回答したように、たとえばすべての数字の組み合わせの中で合計値が10000に最も近い数字を選ぶなどの計算の基準が必要となるわけです。 なお、今回のご質問では、このような基準がないので、ソルバーを利用した場合でも解を求めることができません。 ちなみに、２００個ぐらいの数字で、計算の基準がなく単純に1～200個の数字の可能な組み合わせをすべて計算するようなプログラムを組んだとしても、計算回数が天文学的数字になるので、おそらくその計算に何時間もかかるのではないかと思います。
お礼
分かりました。この題材で別に求めたいものができましたので、ここの質問は未解決終了にします。
- kkkkkm
- ベストアンサー率66% (1734/2604)
> ですからそれは、「Ｃ列に１および２の数値を適切に入れれば『Ｃ＝１の行のＢ列の値の合計もＣ＝２の行のＢ列の値の合計も１００００以上』になる（ことが、コンピュータを介さなくても明らかである）にもかかわらず、Ｃ列の全ての有効セルに１を入れただけで探索を止めてしまっている」点が不都合なのです。 だからこそ、「Ｃ列のセルにどのように１および２を入れても >『Ｃ＝１の行のＢ列の値の合計もＣ＝２の行のＢ列の値の合計も１００００以上』にならないこと」を証明したいです なのです。 10000以上で、個数は問わない組み合わせが最大何通りになるのかというのを求めて（求めなくても）、その組み合わせがどれになるのかを知りたいために、同じ組み合わせのものにたいしC列に同じ数値を振りたいということでしょうか。 で、組み合わせで10000以下になるとダメなので、まずそれを確かめたい（10000以上にならないことを証明というか確認になりますが)でしたら、単にB列の2個の全ての組み合わせでその2個の値を足して10000以下にならないことを確かめるということになりますが…。 1で止めたらダメということと、10000以上にならないことの証明との因果関係がそのようなことくらいしか思い浮かびません。
お礼
何度も回答していただき、ありがとうございました。しかしNo.8様から「不可能」とのお答えを（二度も）いただきましたので、この質問は未解決終了とさせていただきます。尚、No.9へのお返事は省略させていただきます。
- kkkkkm
- ベストアンサー率66% (1734/2604)
> 『Ｃ＝１の行のＢ列の値の合計もＣ＝２の行のＢ列の値の合計も１００００以上』にならないこと」を証明したいです というのは「以下」だと思いますが… 条件がいまいち不明なので数式は適当に考えただけですので、間違っていて当然と言えば当然です(笑) 条件が以下となったり以上となったり理解できませんが、とりあえず以上としたら かなり元に戻ってのあなたの例をもとにお聞きしますが Ｂ列／Ｃ列 ２０００／１ ３０００／２ ４０００／３ ５０００／４← ６０００／４← ７０００／３ ８０００／２ ９０００／１ この例だと組み合わせはB列の2個のデータの組み合わせが10000以上でC列の数値を割り振っていますが、2個限定にするとB列が奇数の場合1個余りますよね。で、その1個が10000以下だとどうするのでしょう。 Ｂ列／Ｃ列 ２０００／１ ３０００／２ ４０００／３ ５０００／４ ５０００／？ ６０００／４ ７０００／３ ８０００／２ ９０００／１ で、組み合わせが2個以上でも10000以上になればいいということにすると Ｂ列／Ｃ列 ２０００／１ ３０００／１ ４０００／１ ５０００／１ ６０００／１ ７０００／１ ８０００／１ ９０００／１ で、いいわけですよね。 組み合わせが2個以上で10000以上になればそこでその組み合わせは終わる? Ｂ列／Ｃ列 ２０００／１ ３０００／２ ４０００／３ ５０００／４ ５０００／４ ６０００／４ ７０００／３ ８０００／２ ９０００／１ こんな感じ？ もしかして B列の値を組み合わせてその合計が10000以上になれば次の組み合わせを探し、その合計が10000以上になれば次の組み合わせを探すというように次々と組み合わせを探していき、最後に10000以下の値が出現することを証明したいということですか。となれば最初の？の例で証明できたという結果になるのでしょうか。 とすればNo1さんの補足に書かれている > もしＣ列＝１と２のどちらかでもＢ列のセル値の合計が１００００未満であれば、Ｃ列への１，２の入力をやり直す とかNo4の補足 > 『Ｃ＝１の行のＢ列の値の合計もＣ＝２の行のＢ列の値の合計も１００００以上』にならないこと」を証明したいです という条件が合致しなくなりますよね…。 条件に矛盾がありそうでとりあえず理解できません。
お礼
>2個限定にするとB列が奇数の場合1個余りますよね。で、その1個が10000以下だとどうするのでしょう ２個限定という条件はありませんので、考慮不要です。（私の示した具体例がたまたま２個ずつだっただけですが、具体例を出すと意図しない情報が伝わってしまうのはよくあることですね。） >で、組み合わせが2個以上でも10000以上になればいいということにすると（中略）で、いいわけですよね。 ですからそれは、「Ｃ列に１および２の数値を適切に入れれば『Ｃ＝１の行のＢ列の値の合計もＣ＝２の行のＢ列の値の合計も１００００以上』になる（ことが、コンピュータを介さなくても明らかである）にもかかわらず、Ｃ列の全ての有効セルに１を入れただけで探索を止めてしまっている」点が不都合なのです。 だからこそ、「Ｃ列のセルにどのように１および２を入れても >『Ｃ＝１の行のＢ列の値の合計もＣ＝２の行のＢ列の値の合計も１００００以上』にならないこと」を証明したいです なのです。 私はＣが最大になるようにと書いているのですが、どうも回答者様はＣを最小にしたいと思われているような気がするのですが・・・（そのせいで話がすれ違っているのではないかと思うのですが）いかがでしょうか。
- kkkkkm
- ベストアンサー率66% (1734/2604)
> 以上が、私の求めるところです。いかがでしょうか。 単純な話、B列の値段が10000以下になる組み合わせ(組み合わせじゃなく単体でよければC列の最大値は商品数分になりますから)が最低何通りあるかという話ですか。で、組み合わせは2個でもいいとか、2個以上で10000に近い数値になる組み合わせとかですか。 以下のような感じの事なら数学の問題になりそうですけど。 http://www.albert2005.co.jp/technology/OPT/OPTproblem.html とりあえず、違うかもしれないけど(あくまでも違うかもですよ) 10000に近い数値になる組み合わせなら、int(B列の値の総数/10000)+1でよさそうな気がするけど、違うかもしれない。 2個でもいいならIF(商品数/2<10000,1,int(商品数/2)+1)でよさそうな気もするけど、これも違うかもしれない。
お礼
どうも混乱させてしまったようです。＞B列の値段が10000以下 ではなく１００００「以上」ですので。 リンク先も拝見しましたが、「・・・以下」「・・・以内」とあり、これはちょっと、私の場合の問題につなげられるようなところが見つかりません。あるいは、どのように応用できるのか、もう少し詳しく説明していただけないでしょうか。 ・「Ｂ列の値段の合計を１００００で割った整数部分に１を足す」 最後の＋１は書き間違いだと思うので無視しますが、これは私もすぐに思いつきました。しかし、例えば、「８０００円の商品３個だけ」という場合、示していただいた式だと２となりますが、 ８０００／１ ８０００／１ ８０００／２ あるいは ８０００／１ ８０００／２ ８０００／２ とするしかなく、これでは、Ｃ列＝２（あるいは１）の行のＢ列の値の合計が８０００となってしまうので、題意に合いません。 ・「商品数が１９９９９以下ならば１であり、商品数が２００００以上ならば商品数を２で割った整数部分に１を足す」 これは私がお示ししたＣ列＝＜４の例に反するので明らかに間違いなのですが・・・何と書き間違えられたのかもわかりません。 しかしかなり前進したような気がします。
- kkkkkm
- ベストアンサー率66% (1734/2604)
> ちょっと待ってください、今回の問題において、Ｃ列で扱える最大値が３であることを知りませんでした。 「たとえば３までだとしたら」で3と書いているだけで、誰も最大値は3だとは言ってません。また、「最大値を決めないならエクセルで扱える最大数です」と書いています。 あなたの書いた条件は B列の合計が10000以上でＣ列の最大値がより大きくなるように で、よろしいのでしょうか? > 既にＡ，Ｂ列に入力された行のうちいくつか行を適当に選びＣ列セルに１ を実行してみましょう。以下にあなたの示した例があります。C列はまだ入力されていない状態です。 Ｂ列／Ｃ列 ２０００／ ３０００／ ４０００／ ５０００／ ６０００／ ７０００／ ８０００／ ９０００／ さて、ここで適当に8行選びC列に１を入力します。 Ｂ列／Ｃ列 ２０００／1 ３０００／1 ４０００／1 ５０００／1 ６０００／1 ７０００／1 ８０００／1 ９０００／1 B列の合計は10000以上ですからこれで作業が終了しました。このあと何も起こりません。 この動作で、あなたの条件と不一致なところがありますか?
補足
分かりました、やっと一歩進んだ感じがします。 No.1様へのお礼の２に長々と書き続けてしまったことですが、例示においてＢ列の合計が１００００以上だということになったら、次は「Ｃ列のうちの適当なセルの１を２に変えるとなると、どのように変えても、『Ｃ＝１の行のＢ列の値の合計もＣ＝２の行のＢ列の値の合計も１００００以上』にならないこと」を証明したいです。今回の場合は、８０００／２・９０００／２に変えるとＣ＝１の行のＢ列の値の合計は２７０００、Ｃ＝２では１７０００となり反証されます。 ということは、「この場合、Ｃの最大値は２かもしれないが、１ということはない」と言えるわけです。 そして「Ｃ列のうちの適当なセルの値に１，２，３を入れると・・・」と続いていくことになります。 以上が、私の求めるところです。いかがでしょうか。
- kkkkkm
- ベストアンサー率66% (1734/2604)
回答した例示に難癖つけても(そもそも違うんだろうなぁと言ってるわけですから)、具体的に、どのような状態を想定してるのか例示していただかないと…申し訳ありませんが理解不能です。 バラバラであったとしても、条件が「B列の合計が10000以上でＣ列の最大値がより大きくなるように」ですので、C列に入れる数値の最大値が3までとしたら（最大値を決めないならエクセルで扱える最大数です) B列 C列 1000 3 2000 3 3000 3 4000 3 5000 3 とか B列 C列 6000 3 7000 3 8000 3 9000 3 と考えますが。
補足
ちょっと待ってください、今回の問題において、Ｃ列で扱える最大値が３であることを知りませんでした。 例えば、 Ｂ列／Ｃ列 ２０００／１ ３０００／２ ４０００／３ ５０００／４← ６０００／４← ７０００／３ ８０００／２ ９０００／１ という風にＣ列に自動入力することはできないのでしょうか？できないのであれば今回の質問は終了します。
- kkkkkm
- ベストアンサー率66% (1734/2604)
C列の同じ数値の行のB列の合計が10000以上になるようにということでしょうか B列 C列 5000 1 5000 1 5000 2 5000 2 みたいなことになるようにC列に自動で数値を入れたいということなのかなぁと思ったけど、C列に同じ数値が入った行のB列の合計が10000以上でＣ列の最大値がより大きくなるようにだから、C列に入れる数値の最大値がわからないけど3までとしたら B列 C列 5000 3 5000 3 5000 3 5000 3 以下の行すべてC列は3 の状態でも正解になるので、C列はすべて同じ数値でオッケーなことになるから違うような…。 具体的に、どのような状態を想定してるのでしょうか。
補足
例示いただいた「Ｂ列に５０００が４つ」であれば、Ｃ列に１，２，３まで入力しようとするとどうしても（Ｃ列の値が同じ行の）Ｂ列の合計に１００００未満のものができてしまうことが、自分であればコンピュータを介さなくてもすぐに分かります。 問題はＢ列の各値がまるでバラバラであることで、そのためＣ列に１から順に数値を入力しようとした場合どこまで増やせるかがすぐに掴めないのです。
- usami33
- ベストアンサー率36% (808/2210)
たぶん、貴方の説明だと、永遠に回答は得られないかと キチンと読み直しして、読み手が処理を連想できる質問にしないと・・・ 今の文章だとLookupの組み合わせで出来そうな気もするし、 VBAでトリガーを含む機能にすべきか判断付きません ※ちょっと拝見しただけでも、↓こんな感じですよ > Ｃ列に同じ数値が入った行のＢ列セルの合計が、 セル数なのか、セルの値なのか判りませんよね > Ｃ列に１以上の整数を１・２・３・・・と順に >（全てのＣ列の数値において）１００００以上になるように、 1,2,3なのか10000以上なのか判らん > そのうえでＣ列の最大値がより大きくなるようにすることです。 Ｃ列の最大値が何より大きくなるの？ Ｃ列の値なら、既に最大値とご自分で書いてますよね > 商品の内容（値段）によっては、 > 全て１００００以上になる組み合わせ また10000が出たけど、これもＣ列なの、それとも今度は値段のＢ列 > Ａ・Ｂ列への入力が今後増えていくので 末尾に足されるの、それとも行間に挿入されるの
補足
なかなか難しいですね。 １．「Ｃ列に同じ数値が入った行のＢ列セルの「値の」合計が」です。 ２．試行錯誤のような書き方をした方が良いならば、以下でいかがでしょうか。 「既にＡ，Ｂ列に入力された行のうちいくつか行を適当に選びＣ列セルに１と、残りの行のＣ列セルに２と入力していき、Ｃ列＝１であるＢ列のセル値の合計もＣ列＝２であるＢ列のセル値の合計も１００００以上になればよい。もしＣ列＝１と２のどちらかでもＢ列のセル値の合計が１００００未満であれば、Ｃ列への１，２の入力をやり直す（行を選択し直す）。全てのＣ列＝１，２入力パターンにおいてもＢ列のセル値の合計が１００００未満のものができてしまうならば、全てのＣ列に１を入力して終了。またＣ列＝１と２の両方のＢ列のセル値の合計が１００００以上になる入力パターンが見つかったら、「いくつか行を適当に選びＣ列セルに１と、残りの行のうちいくつかに２と、残りに３と入力した場合、全てのＣ列＝１，２，３入力パターンにおいてＢ列のセル値の合計が１００００未満のものができてしまう」ことを証明しなければいけない。証明できたらそこで終了。反証できてしまったら「（一部略）Ｃ列＝１，２，３，４・・・」で同様に入力パターンを探る。 以上ですがいかがでしょうか。 これで２・３・４番目の回答に変えます。 ３．入力位置はどこでもよいのですが、末尾の方が楽と言えば楽です。が、意図をもって途中に入力する方が都合のよい方法を紹介していただけるのであればそれでも良いです。
お礼
わざわざありがとうございます。質問を終了させていただいたのは、（これ以上前に進みそうにないから、というのもありましたが、）実際にまとめなければいけない期限が来てしまったからです。ですので、もう解決すべきお題はありません。 ちなみにですが、結局以下の手順で、半分人力で解決しました。 １．合計金額から作れる袋の最大数を予想する。 ２．各商品にランダムに袋の番号を振る。（番号は１から上記の最大数まで当てるように。） ３．同じ袋の番号がついた商品の合計金額が、その袋の金額である。 ４．全ての袋の金額を参考にして、任意の２個の商品を入れ替える。 ５．手順４を繰り返せば、いつかは全ての袋の金額が一定額以上になるかもしれない。 「予想」「参考」という言葉を用いたように、結局この部分はヒューリスティックでしたね。 しかも手順１で予想した最大数が誤っているかもしれないのが欠点でした。