複数条件に合致する名前を表示する関数について

質問

お世話になります。

関数をほぼ1から勉強しており、excelにて練習用問題を解いていたのですが、
以下のような問題でつまづき、まったく先に進めなくなりました。

No 生徒名 クラス 得点 予備校通い進学希望
1 生徒０１ A組 　　　　58 　　× 　　　　　○
2 生徒０２ B組 　　　　96 　　○ 　　　　　○
3 生徒０３ A組 　　　　61 　　× 　　　　　○
4 生徒０４ C組 　　　　29 　　× 　　　　　×
5 生徒０５ C組 　　　　91 　　○ 　　　　　○
6 生徒０６ A組 　　　　60 　　× 　　　　　×
7 生徒０７ B組 　　　　34 　　○ 　　　　　×

という表がまだ下に続いているのですが、

この表を元に
”進学希望で、予備校に通っておらず、70点未満の生徒の名前を求めろ”
という問題があります。

これを関数で求めなければならないんですが、まったく見当がつきません。
どの関数をどう用いればいいかもさっぱりです。

どうか教えてください。

回答No.4

tom04 · ベストアンサー

こんばんは！
どうしても関数で！ということであれば↓の画像のように作業用の列を設ける方法が簡単だと思います。

作業列G2セルに
=IF(AND(F2="○",E2="×",D2<70),ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておき
条件があう行に、行番号を表示させておきます。

結果のI2セルに
=IF(COUNT(G:G)<ROW(A1),"",INDEX(B:B,SMALL(G:G,ROW(A1))))
という数式を入れオートフィルで下へコピーすると画像のような感じになります。m(__)m

お礼

tom04　さん

返答　ありがとうございます。

しかも図付きとは・・・

試してみた結果、解くことができました。
下準備が必要になるんですね。

参考になりました。

ありがとうございました。

回答No.6

imogasi

まず、エクセルの質問をするときはバージョンを書くこと。
２００７を境にして、大幅に変わっている。関数でもＳＵＭＩＦＳなどが追加されている。
ーー
初心者がやる問題としては、複数条件の該当分の合計なら、問題集に載って居てもおかしくないかもしれない。
しかし、複数（列）条件による抜き出しは、関数の本を読んでも載ってないはず。大型書店で参考にエクセルの関数の本を見て見ればわかる（１本だけ載っている例があったが上級社向け）。
エクセルでは抜き出しや、表の組み換えなどは難しいのだ。エクセル関数では、どういうものが適していて、どういうものが不適か見極められるようになるのが、エクセルの関数の勉強で一番大切なこと。もちろん使用経験が要るが、そういう意識を持って勉強しないといけないのも確か。
それに、少し勉強すると、何でもエクセルと思っているような質問者を見かけるが、ソフトには処理内容に適不適があるのでそれを勉強することも大切。（理由は推測すると、他のソフトを買う金が無い。エクセルを知ったと思い込む。ソフトの他の世界を知らない。そのほか視野が狭いなど）
趣味なら別だが、関数はパズル的な面白さを秘めているので、のめりこむ人も居るかもしれないが、職場では、時間をかけて、数日間関数式を考えている時間はないのが普通。このことを忘れないこと。こんな質問コーナーで回答も待っていられない場合も多いだろう。
ーー
本題
私がいつも書いているimogasi方式を書く（作業列を使う方式）。
例データ　Ｓｈｅｅｔ１　
元データＡ２：Ｄ８　　Ｅ列は作業列（書き式の結果出てくるもの）
生徒０１A組 58 × ○ 1
生徒０２B組 96 ○ ○
生徒０３A組 61 × ○ 2
生徒０４C組 29 × ×
生徒０５C組 91 ○ ○
生徒０６A組 60 × ×
生徒０７B組 34 ○ ×
Ｅ２の式は　=IF(AND(D2="○",C2="×",B2<70),MAX($E$1:E1)+1,"")
下方向に式を複写。
結果上記Ｅ列。
やっている意味は、条件に該当する列に、上から連番を振っている。
ーー
Ｓｈｅｅｔ２でＡ２に
=INDEX(Sheet1!$A$1:$E$100,MATCH(ROW()-1,Sheet1!$E$1:$E$100,0),COLUMN())
と入れて、右方向にＤ列まで式を複写。
２行目ではＳｈeｅｔ１のＥ列の１の行、３行目ではＳｈeｅｔ１のＥ列の２の行を持ってこようとしている。
Ａ２：Ｄ２の式を下方向に式を複写。
結果　Ａ２：Ｄ３
生徒０１A組 58 × ○
生徒０３A組 61 × ○
ーー
これでも既に出ている回答の一番長い式よりはやさしいと思うがどうかな。

お礼

imogasi さん

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

解説まで付けていただけるなんて・・・

式の子細はまだのみ込めておりませんが、
のちに勉強させていただきます。

諸々のアドバイスについても、今後肝に銘じさせていただきます。

ありがとうございました。

回答No.5

kagakusuki

今仮に、「No」と入力されているセルがA1セルであるものとします。
　一応、ＥｘｃｅｌのバージョンがＥｘｃｅｌ2007以降であれば、

=IF(ROW(A1)>COUNTIFS($D$2:$D$8,"<70",$E$2:$E$8,"×",$F$2:$F$8,"○"),"",INDEX($B:$B,SUMPRODUCT(ROW(A$2:A$8)*($D$2:$D$8<70)*($E$2:$E$8="×")*($F$2:$F$8="○")*(COUNTIFS(OFFSET($D$1,,,ROW(A$2:A$8)),"<70",OFFSET($E$1,,,ROW(A$2:A$8)),"×",OFFSET($F$1,,,ROW(A$2:A$8)),"○")=ROW(A1)))))

という関数を適当なセルに入力してから、オートフィル等を使用して、そのセルを下に向かってコピーすれば、条件に合致した生徒名を抽出する事は可能ですが、この様な複雑な関数を組もうと考えるのは私くらいのもので、一般的な関数の使い方ではありませんから、keithin様が仰る様に、「エクセルの練習問題」としてこの様な関数を扱う事は、通常は考えられないと思います。

お礼

kagakusuki　さん

返答　ありがとうございます。

これは難しい・・・
今の自分の頭じゃわかりそうにないなぁ。

確かにこれなら練習問題を逸脱してますね・・・

いや、頑張らせていただきます。

参考になりました。

ありがとうございました。

回答No.3

bin-chan

行ごとに生徒名を表示することで良いなら、ifとandでいけそうに思います。

式は[=if(and(式１, 式２,　式３), 生徒名, "")]
式１・２・３はそれぞれ条件を満たすように作成してください。

お礼

bin-chan　さん

返答　ありがとうございます。

解答欄が別にあったもので、行ごとの表示は考えていなかったのですが、
これでもいけるかな？

参考になりました。

ありがとうございました。

回答No.2

keithin

あるいは
＞excelにて練習用問題を解いていたのですが

あなたは今「関数の勉強をしよう」と思って，その設問を何としても関数で解いてみたいと，ご自分で制約をつけてチャレンジなさっているのかも？しれません。

その場合，そもそもその問題は関数で解くことを求めていない
○オートフィルタの練習
○フィルタオプションの設定の使い方
といった事を練習するための課題だったかも？しれません。

こちらのような質問相談掲示板にも，非常にしばしば「何としても関数で関数で」というご相談が数多く寄せられますが，そういう「できない」の殆どは，オートフィルタや並べ替え，ピボットテーブルレポートなどの「エクセルの操作」によって合理的至極簡単に成果が得られるモノばかりだったりします。

お礼

keithin　さん

そうですね、
ほかの問題が関数を使うこと前提だったもので、躍起になっていたというのもあるかもしれません。

ほかの方法も考えてみないといけませんね。
試してみます。

ありがとうございました。

回答No.1

keithin

＞excelにて練習用問題を解いていたのですが、

エクセルの関数の勉強のための，練習問題で間違いありませんか。

だとすると
＞”進学希望で、予備校に通っておらず、70点未満の生徒の名前を求めろ”

関数をちょっと脇に置いておいてご自分の目で見てみると，この条件を満たす生徒はA組の０１とA組の０３の２名が該当します。
通常エクセルの関数は「２つ以上の答えが成立する」時に正しく２つの答えを計算できませんから，その設問はエクセルの練習問題として間違っています。

実際には例えば７０点未満というのが間違いで，
”得点は６０点未満，予備校は×，進学は○の生徒をピックアップしなさい”
という問題だった仮定すると，DGET関数などを利用する問題だと推定できます。

ご相談に書かれた通りの条件でも，複雑な配列数式を用いたり，また作業列を追加して２人（以上）の答えを導くような関数を組み立てることは勿論可能ですが，現在お話の「エクセルの練習問題」としてそういうのを扱うことは通常は考えられません。

お礼

keithinさん

お返事ありがとうございます。

dget関数、こちらも試してみたのですが、表内の重複する条件というものがどうしてもネックでした。

質問としてコピーしたのは10名程度でしたが、これが以下20名分続いています。

どうしてもわからなくて質問させていただきましたが、自分にはレベルが飛びすぎたかな・・・

ありがとうございました。