[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『特定条件下での行の組み合わせ』（モンゴリ）
エクセル:Excel2003 ＯＳ:WindowsXP
エクセル初心者です。
こんなタイトルで要約になってるかどうかも怪しいですが質問させてください。
条件つきで行の組み合わせを検索できるようにしたいのですが、
例えば、
あ い う え お か
A1 ● ● ● - - -
A2 - ● - ● - -
A3 - - ● - ● -
A4 - - ● - - ●
A5 ● - - - ● ●
A6 - - - ● ● ●
B1 - ● - ● - -
B2 ● - - - - ●
B3 - - ● - ● -
B4 ● - - ● - -
B5 - - ● ● ● ●
B6 - - - ● ● ●
みたいな表があったとします
（A1セルの行には[あ]・[い]・[う]がある、B1セルの行には[い]・[え]がある、みたいな)
ここから行を組み合わせると、[あ]〜[か]をすべて含むようにする、という
組み合わせを調べたいのですが、
条件として、
・A1〜A6からは１つの行だけ、同様にB1〜B6からも１つの行だけ、
(A1,B1)や(A3,B4)のように２つの行の組み合わせで絞り込む。
（(A1,A2)(B3,B6)のように同じセル郡から２つだしたり、(A1,B1,B3)のように３つ使うのは×）
というようにしたいのです。
この例ですと、組み合わせたときに、
[あ]〜[か]全てを含むようになるのは(A1,B5)の組と(A1,B6)の組です。
(ここで、A1の[う]とB5の[う]が重複しているのはOK。
(A1,A6)も全て満たしているが、同じAの行なのでNG。)
絞り込んだ結果を
A A1 A1
B B5 B6
のように表現できるとありがたいです。(結果がわかれば出来なくてもいいです。)
また、「[あ]〜[か]を含む」以外の条件でも絞り込めるように
汎用性を持たせたいです。
(例えば「[あ]〜[う]と[か]を含む([え][お]は含んでいてもいなくても、 どちらでもOK)」 など)
長々と拙い文章で失礼しました。
どうぞ皆さんの知恵を貸してください。宜しくお願いします！
・上の例で、セル A1 には、具体的に「何が」はいってるのかな？ ・A1の行って？ １行目ということ？ すると B1の行も１行目？ ・「同じAの行なのでNG」というところも？ 同じＡの列という日本語ならわかるけど？
(ぶらっと)
もしかして・・・・ A列 B列 C列 D列 E列 F列 G列 1行目 あ い う え お か 2行目 A1 ● ● ● - - - 3行目 A2 - ● - ● - - 4行目 A3 - - ● - ● - 5行目 A4 - - ● - - ● 6行目 A5 ● - - - ● ● 7行目 A6 - - - ● ● ● 8行目 B1 - ● - ● - - 9行目 B2 ● - - - - ● 10行目 B3 - - ● - ● - 11行目 B4 ● - - ● - - 12行目 B5 - - ● ● ● ● 13行目 B6 - - - ● ● ●
こんなレイアウト？
(ぶらっと)
ぶらっと さん わかりにくい例え、かつ文章ですいません；；。
コメントいただいたとおりのレイアウトです。
・A1は適当な場所のセルです。A1という表現は紛らわしすぎですね。。。
もっと具体的にするならば、
A→東京店、B→大阪店、１〜６の数字→店員の名前、あ〜か＝資格の種類
として、
あ い う え お か
東京店１番さん ● ● ● ― ― ―
→東京店の１番さんは[あ]、[い]、[う]の資格をもっている。
みたいな風にしたかったんです。
・A1の行
→東京店１番さんの行 と思ってください。
・絞り込んだ結果をA A1 A1 B B5 B6のように〜
→ A A1 A1
B B5 B6
みたいなレイアウトがうれしいです(これも変なレイアウトになってますが；；)
・同じAの行なのでNG
→すいません・・・。「行」って言う言葉は無視してください。
東京店、大阪店それぞれから１人ずつ選びたかったので、
「同じ東京店のグループなのでNG」ということでお願いします。
(モンゴリ)
じゃぁ、ちょっとコードをかいてみる。（でいいんだよね？まさか関数で？） 追加で、 ・東京店、大阪店というグループ分けは理解するけど、先頭の３桁が同じという条件でいいの？ ・たとえば満足する組合せがA1+B5とかA1+B6というのは理解したけど、A A1 A1 とか B B5 B6 という意味がわからないなぁ。 もう少しわかりやすく説明してくれない？ ・で、結果を、どこかのシートに書き出すというのが要望かな？
追加で）・グループは２つだけと限定していいの？それとも、増えていく可能性はある？ ・グループは昇順でならんでいる？それとも、ばらばら？
(ぶらっと)
ありがとうございます<(_ _)> コードでお願いします。
・先頭の３桁が同じ
→その条件で大丈夫です。●or空白、で分けようと思っています
・A A1 A1 B B5 B5
→レイアウトがうまくできないので言葉で・・。
「東京店」という言葉が入ったセルをＢ２(実際のエクセルでのセルの位置です。
私のわかりにくいたとえは忘れてください<(_ _)>)とすると、
Ｂ３に「大阪店」、Ｃ２、Ｄ２にそれぞれ「東京店の１番さん」、
Ｃ３には「大阪店の５番さん」、Ｄ３には「大阪店の６番さん」がは行っているイメージです。
行ごとに、〜店を分けて、列ごとに、組み合わせの結果が出るようにしたいです。
・結果のシートの場所
→どこでも大丈夫です。ほかのシートでも同じシート内でも。
・グループの数
→４つほしいです。。。これ以上増やす予定はないです。
・グループの順番
→バラバラです。グループの店の種類や、グループ毎の〜さんの部分には、
数値ではなく固有名詞を入れる予定です。
お願いばかりでほんと申し訳ないです；；
よろしくお願いします。 (モンゴリ)
なんとなくわかったような・・・ ４グループ！ ということはデータによっては２グループかもしれないし３グループかもしれないということだね。 たとえば東京、大阪、名古屋、神戸 だった場合、東京の誰かと大阪の誰か、東京の誰かと名古屋の誰か・・・名古屋の誰かと神戸の誰か・・・ こんな組合せだねぇ。 ちょっと時間がかかると思うので、しばらく時間ください。
あぁ、それと、先頭３けたということでコードを作るけど、データ処理としては店列と名前列をわけるレイアウトが 何かと処理しやすい。（たぶん、入力もしやすいと思う） レイアウト設計も重要な業務設計だよ。
(ぶらっと)
大変遅くなりましたが、ありがとうございます！よろしくおねがいします！
・・・・・・で、またしても、言葉足らずだったみたいなのですが、
４つグループつくっていただけるということで、
各店からは１人ずつ、であれば、
今回なら４グループなので４人の組み合わせでもＯＫとできるようにしたかったんです。
(もちろん条件を満たせるなら３人の組み合わせでも２人の組み合わせでもＯＫ、
東京の誰か１人と、大阪の誰か１人と、名古屋の誰か１人と、神戸の誰か１人(計４人)、や、
東京の誰か１人と、名古屋の誰か１人と、神戸の誰か１人(計３人)などなど。
東京から２人とか３人はNG)
あと、私が「先頭３桁」の意味を取り違えてたみたいで、
やっぱり店列と名前列を分けて作っていただけると幸甚です。
今更で本当にもうしわけないです。
もう遅いかもしれないですが、よろしくお願いします。。。<(_ _)> (モンゴリ)
>今回なら４グループなので４人の組み合わせでもＯＫとできるようにしたかったんです。 >(もちろん条件を満たせるなら３人の組み合わせでも２人の組み合わせでもＯＫ、
要件はクリアに了解。しかし・・・ひゃぁ！！！ これだと、「数学」の領域というか、もしかしたら「線形分析」の領域のはなし（というほどでもないかな）になりそう。
当方、「算数」で、人生がストップしているので手に負えなくなるかもしれない。 コード以前に、アルゴリズムを組み立てるのに、すごく時間がかかりそう。 がんばってみるけど、ギブアップする可能性もあるなぁ。 そのときはなるべく「早く白旗をあげる」ようにするね。
それより、この領域のエキスパートさんから、さっと回答があればいいねぇ。
★店列、氏名列の分離は了解。そのほうがいいねぇ。
★追伸 アルゴリズムが確定したら、コードは淡々と書くだけでいい。 なので、もしかしたら、本件、まず、そのアルゴリズムについて、「数学専門の掲示板」なんかで質問したら、早いかもよ。 どんな掲示板がどこにあるのかは、私の人生からは、もっとも遠い領域なのでわからないんだけど。
(ぶらっと)
何から何まで丁寧に教えていただき、本当にありがとうございます！
私も、ちょっとエクセルで関数覚えてきたので、作れるかなーとか思って
３日ほどあれこれ悩んでたんですけど、やっぱりかなり難しいことだったんですね。
数学の掲示板で、このアルゴリズムについて質問してきます！
(モンゴル)
>数学の掲示板で、このアルゴリズムについて質問してきます！
うん。是非。 とりあえず、こちらは、｢力任せの総当たり｣コードを書いてみる。 でも、あらためて、すごいことになるなぁと。 仮に、４グループあって、各グループに１００人いたとして、各組から１人ずつ、４人でチェックするとすればそれだけで、１億の組み合わせ！ 可能性としては、そのすべての組み合わせが条件にマッチするかも。 すると、その結果って、どこに表示したらいいんだろうなんて。 もちろん、まず１人でチェック、次に２人でチェック・・とやっていって、より少ない人数で条件を満たした組み合わせが その後の、より多い人数の組み合わせにあれば、それはカットするといったこともやるので まぁ、実際にはそんなことはないんだろうけどね。 でも、処理としては、とにかく、そのような数になるので、何時間しても終わらなかったりして。
(ぶらっと)
>仮に、４グループあって、各グループに１００人いたとして、各組から１人ずつ、４人でチェックするとすればそれだけで、１億の組み合わせ！ >可能性としては、そのすべての組み合わせが条件にマッチするかも。 すべての組み合わせを検討するような問題の場合、各組の人数が多いと組み合わせ数は膨大になります。また条件に合致する場合の数も多くなって、それを列挙するのも大変です。 資格の数(〜6)、および各組の人数はどれくらいでしょうか。 各組の人数が少なければ(〜10位)、全組み合わせを一覧にして、そこから選択するという方法もあります。
＞１億の組み合わせ
言われてハッとしました。総当りするとなるとそうなっちゃいますよね。
各グループにはまちまちではありますが、４０〜８０人ぐらいいます。
資格の種類は９個です。
結果は上位５０通りくらい(少ない人数で満たせたほうが上位)見つけられれば、
十分ですので、その時点で計算終了する、みたいなことってできるんでしょうか？
(モンゴリ)
>結果は上位５０通りくらい(少ない人数で満たせたほうが上位)見つけられれば、 >十分ですので、その時点で計算終了する、みたいなことってできるんでしょうか？
そうだね、そのほうが無難だね。 そうしてみよう。ただ、なかなか落ち着いて時間が確保できないので、ちょっと待ってね。
その前に、コメントをつけてくださった「名無しさん」からコードが提示されるかも。 それも、大いに期待しよう！
(ぶらっと)
よろしくお願いします！
友人にも似たような質問をしたら、
「モンハンのスキルシミュレーターを参考にしたら」といわれたので、
教えてもらった↓のサイトに行くと、
「検索ロジック」とやらがありました。
私にはあんまり理解できなかったのですが；；参考になれば幸いです。 (モンゴリ)
http://www.geocities.jp/masax_mh/logic/
もっと大きな問題の一部のような気がします。 総勢２５０人ほどで、4つのグループに分かれている。各グループから１人ずつ集めて、２〜４人で作業し、またその人数で必要な資格を満たすようにする。 とすると作業現場は６０箇所以上で、その現場にうまく人を配分するという問題になります。
全員が一斉に現場に派遣される訳ではなく、
必要な資格も、現場によってばらばらです。
一回の絞り込みで、一現場分が出せればokです。
(モンゴリ)
まず、申し訳ないけど「回答者」としては、本スレ、「白旗・撤退」宣言です。 期待を持たせたまま引きずっても、迷惑をかける公算が大なので。
理由は ・やはり、アルゴリズムというか、処理構造そのものが、アニメーション・ゲーム・あるいは本件のような解析系の処理は 自分自身の領域（アプリロジックをゴリゴリかいて処理できる領域）とは、全く異次元のものなんだろうという認識。 ・さらには、それらを実装するコードレベルでも、全くことなる手法があるんだろうなという認識。
テーマとしては、非常におもしろいので、自分自身の楽しみも含めて取り組みを継続していくけど、通常の処理用の構成で 事足りるのかどうかという以前に、コード完成までに１日でできるか、１週間なのか、１ヶ月なのか、１年なのか？ めどが立っていないということもあって。
ということで、他のエキスパートさんからの回答を心待ちにしているけど、とりあえず、今、「ゴリゴリ構成」で 取り組んでみようとしている、そのイメージは以下のような感じ。参考にも何にもならないだろうけど。 ・まず、処理に与える要求資格、あるいは各人が持っている資格については、二進数の1,0の形にしたものを Long型の値で保持し、それらのAndやOrで判定していく。 ・グループ毎に、「処理に要求された資格」の保有数をキーに降順で並び替えておいて、上からチェック。 ・グループ毎のデータは、配列で持たせるかDictionaryで持たせるか、あるいは、グループ毎の情報保持クラスインスタンスを 生成して、そこに持たせるか、これは、まだ決めかねている。 ・要求を満足する組合せのうち、より少ない人数で要求を満足したものを含めば、それは除外。 ・組合せ処理自体は、１グループ、２グループの組合せから、そのグループ所属の人の総当たり（１組２人）、 ３グループの組合せから、そのグループ所属の人の総当たり（１組３人）、４グループでも総当たり（１組４人） この順序で行う。 ・あらかじめ指定された組数に結果が到達すれば終了。
(ぶらっと)
私の無理難題に応えていただきありがとうございました！
自分でも何かやり方が無いかを探してみます。
それまではアナログ方式でやっていきます。
合間をみて、エクセルの勉強を続けていれば何か
思い付く日がくることを期待してます。
今は、マンパワーでどの様にやって居られるのでしょう？
組合せを見つけやすく出来るだけでも良い と言う事なら、次の様に考えると見つかりやすいかもしれません。 （既にやって居られる方法かもしれませんが。。。）
・資格をたくさん持っている人の方が、組み合わせた時の人数が少なく成るし 組合せやすい ・二人目以降は、これまでで決定した人の資格以外の資格をたくさん持っている人の中から 選んで行く事にした方が、途中で行き詰まらない可能性が高くなる。
A:J列は、確定後の確認の為に取ってあります。 決定の過程では必要無いので、レイアウトを載せるだけにしておきます。
作業中に必要に成ってくるのは、L列以降です。 ただしこれも、殆どはデータ表部分で、数式を入れるのは「S1」の見出しにしてある部分です。
[A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [1] 資格1 資格2 資格3 資格4 資格5 資格6 資格7 資格8 資格9 [2] 人数 1 1 1 1 1 1 1 1 2 [3] [4] A4 ● ● [5] B10 ● ● ● [6] C5 ● ● ● [7] D10 ● ● [8] V1=V2 ↓ ↓AH1=V1+AH2 [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z] [AA] [AB] [AC] [AD] [AE] [AF] [AG] [AH] [AI] [1] 2 5 [2] A4 ● ● 2 B10 ● ● ● 3 [3] [4] 資格1 資格2 資格3 資格4 資格5 資格6 資格7 資格8 資格9 S1 資格1 資格2 資格3 資格4 資格5 資格6 資格7 資格8 資格9 S1 [5] A2 ● ● ● 3 B10 ● ● ● 3 [6] A5 ● ● ● 3 B4 ● ● ● 2 [7] A1 ● ● 2 B2 ● ● 2 [8] A4 ● ● 2 B11 ● ● 2 [9] A8 ● ● 2 B15 ● ● 2 [10] A3 ● ● 2 B3 ● ● 2 [11] A6 ● ● 2 B7 ● ● 2 [12] A9 ● ● 2 B6 ● ● ● 2 [13] A10 ● ● 2 B12 ● ● 2 [14] A12 ● ● 2 B9 ● ● 2 [15] A14 ● ● 2 B1 ● ● 1 [16] A7 ● 1 B13 ● ● 1 [17] A11 ● 1 B5 ● 1 [18] A13 ● 1 B8 ● 1 [19] A15 ● 1 B14 ● 0 [20] AT1=AH1+AT2 ↓ ↓BF1=AT1+BF2 [AJ] [AK] [AL] [AM] [AN] [AO] [AP] [AQ] [AR] [AS] [AT] [AU] [AV] [AW] [AX] [AY] [AZ] [BA] [BB] [BC] [BD] [BE] [BF] [BG] [1] 7 9 [2] C5 ● ● ● 2 D10 ● ● 2 [3] [4] 資格1 資格2 資格3 資格4 資格5 資格6 資格7 資格8 資格9 S1 資格1 資格2 資格3 資格4 資格5 資格6 資格7 資格8 資格9 S1 [5] C5 ● ● ● 2 D10 ● ● 2 [6] C9 ● ● 1 D1 ● ● ● 1 [7] C14 ● ● 1 D6 ● ● 1 [8] C10 ● ● 1 D9 ● ● 1 [9] C13 ● ● 1 D12 ● ● 1 [10] C4 ● 1 D3 ● 1 [11] C8 ● ● 1 D7 ● ● 1 [12] C12 ● 1 D15 ● ● ● 0 [13] C3 ● 0 D11 ● ● 0 [14] C1 ● ● ● 0 D5 ● ● ● 0 [15] C15 ● 0 D13 ● ● 0 [16] C6 ● ● 0 D8 ● 0 [17] C2 ● ● 0 D4 ● ● ● 0 [18] C11 ● 0 D2 ● ● 0 [19] C7 ● 0 D14 ● 0 [20]
V5=COUNTA(M5:U5) AH5=SUMPRODUCT(($M$2:$U$2="")*(Y5:AG5="●")) AT5=SUMPRODUCT(($M$2:$U$2="")*($Y$2:$AG$2="")*(AK5:AS5="●")) BF5=SUMPRODUCT(($M$2:$U$2="")*($Y$2:$AG$2="")*($AK$2:$AS$2="")*(AW5:BE5="●")) 人数分フィルドラッグ。
準備が出来たら、ピックアップ作業になります。
AのグループをS1列を降順で並べ替えます。 S1の列の値が大きい人が、たくさん資格を持っている人です。 最初に資格が多い人を選んでおいた方が、後グループで選べる人数が多く成る可能性が 高いと思います。
このサンプルでは、Aグループは２つ資格を持っている人の中から一人選びました。 選んだ人の行（L:V列）をコピーして、L2から貼り付けます。
BのグループのS1列が再計算されるので、降順で並べ替えます。 人を選び、コピーし、X2から貼り付けます。
CのグループのS1列を降順で並べ替え・・・・
と、作業を続けていきます。
Dのグループまで行ったら、AT1に表示される数から9を引いた数以上の数が S1列に出ている人なら、誰でも当てはめることが出来ます。
このサンプルでは、2以上の人が一人しか居ないので 選択の余地は無いですが。。。
逆に、9-AT1 以上の数が無い場合は どこかのグループで人選の変更が必要です。 その際、前のデータ（2行目）は消すようにしておいた方が心配が無いと思います。 勿論、一番最初に始める時も。 どこかのグループで選んだ段階で、1行目の累計を入れたセルが9に成れば それ以降の人選作業は不要です。
「とにかく一行目に表示された人から決定していく」事にすれば 特に何も考えなくても、組合せが出来る事に成ると思いますが 最初のグループ等は特に 資格を持っている数が多い人ばかり 選ばれる事に成ります。
また、最初のグループは基本的に並び順が変わりません。
個人毎の資格を取得している数や、どの資格がより多く取られているか 等のバラツキで、変わってくると思いますが 一回実行したら ・一番最後のグループを先頭グループに持ってくる ・ピックアップする人（行）をルールを作って決定する ・実行前には乱数で一旦並びをバラバラにする ・最初の２グループは、二人ずつを候補にする（最低でも４つの組合せを作る） 等の工夫で、有る程度機械的に また、固定の組合せに成らない様には 出来るのではないかと思います。
そちらでルールをさらに追加して貰って 完全に機械的に出来るように成った場合 その手順やルールと共に 「○○でピックアップして行けば良いだけなんだけど、自動化出来たらな〜」 と呟いておけば、何方かマクロにして下さるかもしれません。
条件がシビアで無いなら、このくらいファジーな感じでも良いかもしれません。 ・・・と思いましたが、条件がシビア（選択の余地が無い）なら それこそ、「一行目に来る人で組み合わせていく」で良いんですね。たぶん。。。
まぁ、まずは確認してみて貰うと良いと思います。 まだ見て居られるか分からないですが。。。
(HANA)
ありがとうございます！ 一つ一つ探しながらあーでもないこーでもないと、 効率の悪いやり方をしていたのですが、 ものすごく楽になりました！
(モンゴリ)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.