エクセルを用いた処理について質問です。

質問

39×39の表があります。
（具体的には、B1セルに１、C1セルに２・・・というようにAN１セルに３９の値があり、A２セルに１、A３セルに２・・・というようにA40セルに３９の値があります）

次に、表中の一部の箇所に１の数字があります。
（例えば、5列目16行（この場合のセル番地は、F17セル）に１の数字、17列目34行（この場合のセル番地は、R３５セル）に１の数字・・・といった感じです）

そこで、質問をしたいことが、適当に列、行にある数字１～３９の中から３つを選んだときに(例えば５，２０，３０とします。）3つの数字を含む列、行のセル番地が全て１が付いているかどうか調べたいのです。（この場合はF21セル、U31セル、F31セルのところに全て１がついているかどうかを調べたいです）

はじめはindex関数を用いて作業をしようと思いましたが、１～３９の組み合わせを全て入力すると約9000にも及んでしまって途方にくれているところです。

いい方法があれば教えていただけるとうれしいです。よろしくお願いします。

回答No.4

MarcoRossiItaly · ベストアンサー

セル参照にすればいいだけ。A44:C44の範囲に、「5、20、30」の3数を入力。どこかの適当なセルに次の式を入力。

=(index(b2:an40,b44,a44)=1)*(index(b2:an40,c44,b44)=1)*(index(b2:an40,c44,a44)=1)=1

結果がTRUEかFALSEで表示されます。いちばん最後の「=1」のみ削除すれば1か0で表示されます。

U21、AE21、AE31を含む6セルについて1かどうかを確認したければ、上の式中の「*(index()=1)」という部分を適当に追記してください。5つ以下のセルについて見る場合、INDEX関数で、行と列の番号を逆にしないよう注意。

回答No.5

MarcoRossiItaly

連続投稿すみません。書き忘れたので、補足を。

No.4の式中では「*」を使っているのでAND条件ですが、これらを「+」に書き換えればOR条件になります。その場合、1であるセルの個数を求めていることになるので、いちばん最後の「=1」のみ「>0」に書き換えるか、削除します。

回答No.3

keithin

座標のように言うなら、どうして（５、２０）が採用なのに、（２０、５）は不採用なのですか？ということです。
（５，３０）は間違いで（３０，５）でしたというのなら、まだ少しはわかりますが。

いずれにしても回答１の方法で、（５，２０）も（２０，５）も含めた９通りの組み合わせが「すべて列記できます」。その中からアナタが選んだ「組み合わせの座標」のセルを随意に選び、１が記入されているかIFを連ねて確認してみると簡単に答えはわかります。

例えば
＞（５，２０）、（２０，３０）、（５，３０）の3箇所
＞F21セル、U31セル、F31セルになる

というのなら
=IF(AND(B45=1,B46=1,C46=1),"３つとも１","ＮＧ")
のように調べられます。

もちろん、上記作成例の「Ｂ４５」「Ｂ４６」「Ｃ４６」の部分に、それぞれのセルに記入されている数式を直接記入して作業用の表を用意せずに詰め込んでも計算はできます。

お礼

ありがとうございました。参考になりました。

回答No.2

KURUMITO

Ａ50セルに5、Ｂ50セルに20、Ｃ50セルに30と入力したときに1の入ったセルの数をＤ50セルに表示させるとしたらＤ50セルには次の式を入力します。

=IF(INDEX(A$1:AN$40,B50+1,A50+1)=1,1,0)+IF(INDEX(A$1:AN$40,C50+1,A50+1)=1,1,0)+IF(INDEX(A$1:AN$40,C50+1,B50+1)=1,1,0)

Ａ，Ｂ，Ｃ列の51行以降のセルに上と同様の数値を入力する場合にはＤ50セルの式を下方にドラッグコピーすればよいでしょう。

お礼

ありがとうございます。この方法を１～３９の全ての組み合わせでやらねばならず、組み合わせが約１００００程度あります。効率よくできればと思うのですが・・・

アドバイスお願いできればと思います

回答No.1

keithin

＞3つの数字を含む列、行のセル番地が全て１が付いているかどうか調べたいのです。
＞この場合はF21セル、U31セル、F31セルのところに全て１がついているかどうかを調べたい

５，２０、３０の３つの数字を指定すると、（Ｆ列、Ｕ列、ＡＥ列の３列）×（６行目、２１行目、３１行目）の合計９セルが対象になります。

ご質問ではその中からＦ２１とＵ３１とＦ３１の３セルだけ選んでますが、どうしてその３セルだけ、１がついていれば良しになるのでしょうか。
「すべて１がついているか調べたい」とはどういうことですか？

簡単な作成例：
Ａ４４に５
Ａ４５に２０
Ａ４６に３０
を記入する

Ｂ４３に５
Ｃ４３に２０
Ｄ４３に３０
を記入する

Ｂ４４に
=INDEX($B$2:$AN$40,$A44,B$43)
と記入し、Ｄ４６までコピー貼り付ける

と、「指定の数字」の行・列の交差するセルの数字が一覧されるので、「すべて１」（なら１が９個並びますね）かどうかCOUNTIF関数などで数えてみたらどうでしょう。

お礼

ご回答ありがとうございます。説明不足で申し訳ありません。５，２０，３０の数字を決めたら、座標のようにいうなら（５，２０）、（２０，３０）、（５，３０）の3箇所のセルすなわちF21セル、U31セル、F31セルになるということです。

補足

５，２０，３０の数字を決めたら、座標のようにいうなら（５，２０）、（２０，３０）、（５，３０）の3箇所のセルすなわちF21セル、U31セル、F31セルになるということです。

これら3つのセルにすべて１が入っているかどうかを39×39の表において効率よく分かるといいなあと思っています。

アドバイスお願いします。