エクセルデータベースで検索

質問

エクセルでデータベースを作っています。

大量のデータ（全校生徒テスト成績データベース）の中から、（2009/07/01）の（５年○組）の（最低点）と（最高点）を導きたいです。
これを別シート（シート２）のＣ列に（最低点）を、Ｄ列に（最高点）を入れます。
以下のようにしたいです。

　　Ａ列　Ｂ列　　　Ｃ列　　　Ｄ列
１　09/07/01実施テスト結果
２　５年　１組　　　２５　　　９０
３　　　　　２組　　　３０　　　８５
４　　　　　３組　　　１５　　　９５

ベータベースから条件に合ったものの中から、最小値を導く方法がわかりません。
セルＣ２からＤ４は関数で出来るのでしょうか？
それともＶＢＡ？
どなたか教えて下さい。

宜しくお願い致します。

回答No.7

KURUMITO · ベストアンサー

回答Ｎｏ6です。
最後の式は間違って入ってしまいました。ごめんなさい。

お礼

回答ありがとうございます。
見事私の思っている通りにできました。
本当にありがとうございます。

｛＝・・・・・・｝
大カッコ・・・関数ではじめて見ました。

こういうのもあるんですね。
勉強になります。

ちなみに大カッコはずしたら、２行目以降できなくなりました。
不思議です。

一生懸命勉強します。
また、わからないことがあったら教えてください。

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

回答No.9

merlionXX

> エクセル２００７を使っていますが、最大値はうまくいったんです、本当に。

2007は仕様が違うようですね。
で、No8の回答で最小も出来たのですね？

あと、余談ですが
いちいち学年や組を式に入れるのは面倒ですよね？
ならば仮に
G2以下に調べたい日付、H2以下に調べたい学年、I2以下に調べたい組を入れ
J2に
=SUMPRODUCT(MAX(($A$2:$A$65536=G2)*($B$2:$B$65536=H2)*($C$2:$C$65536=I2)*$E$2:E$65536))
K2に
=SUMPRODUCT(SMALL(($A$2:$A$65536=G2)*($B$2:$B$65536=H2)*($C$2:$C$65536=I2)*($E$2:$E$65536),ROWS($B$2:$B$65536)+1-SUMPRODUCT(($A$2:$A$65536=G2)*($B$2:$B$65536=H2)*($C$2:$C$65536=I2))))
として、J列K列を下に式をフィルドラッグしてコピーすれば簡単に各学年や組の最高点（J列）、最低点（K列）の一覧が出来ます。
お試しください。

お礼

本当に丁寧に教えてくださり、ありがとうございます。
感謝感激です。

ＳＵＭＰＲＯＤＵＣＴ関数ただいま勉強中です。
配列関数ですか、難しい関数をよくご存知ですね。

私もこういう関数を使いこなせるよう、また、勉強します。
本当にありがとうございました。

回答No.8

merlionXX

merlionXXです。

> データベースが日々大きくなることを想定しているので、Ａ２：A１０００の所はＡ：Ａにしてみました。
> すると、最小値が上手く計算できなくなりました。

それでは最小値のみならず最大値も計算できないと思います。（エクセル2007なら存じませんが）
せめてA2:A65536 と具体的に範囲を設定してください。

で、最大値
=SUMPRODUCT(MAX((A2:A65536=DATEVALUE("2009/7/1"))*(B2:B65536="5年")*(C2:C65536="4組")*E2:E65536))

最小値
=SUMPRODUCT(SMALL((A2:A65536=DATEVALUE("2009/7/1"))*(B2:B65536="5年")*(C2:C65536="4組")*(E2:E65536),ROWS(B2:B65536)+1-SUMPRODUCT((A2:A65536=DATEVALUE("2009/7/1"))*(B2:B65536="5年")*(C2:C65536="4組"))))

お礼

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

エクセル２００７を使っていますが、最大値はうまくいったんです、本当に。
最小値だけが上手くいかなかったんです。
そこで、ＭＡＸの式を、ただＭＩＮに置き換えたら、見事に全部”０”になりました。
やはり、私の素人考えは歯が立ちませんでした。

回答No.6

KURUMITO

シート2では次のようにします。
Ａ1セルには2009/7/1のように入力します。セルの表示形式のユーザー定義でyy/mm/ddのようにして09/07/01のように表示させます。
Ｂ1セルには「実施テスト結果」と入力します。
Ａ2セルには「学年」、Ｂ2セルには「組」、Ｃ2セルには「最低点」、Ｄ2セルには「最高点」と入力します。
3行目以降に学年や組を数値で5年1組ならＡ3セルに５、Ｂ3セルに１と入力します。2組の場合でもＡ列の学年も入力します。同じ学年の数値を並ばせたくない場合でも数値は入力して、数値の色を白にするなどで印刷においては隠れてしまうようにしてもよいでしょう。
Ｃ3セルには次の式を入力し、式を確定する段階でCtrl+Shiftキーを押しながらEnterキーを押して確定します。
=IF(OR($A$1="",$A3="",$B3=""),"",MIN(IF((Sheet1!$A:$A=$A$1)*(Sheet1!$B:$B=$A3)*(Sheet1!$C:$C=$B3),Sheet1!$E:$E)))
同じくＤ3セルには次の式を入力し、Ctrl+Shift+Enterで確定します。
=IF(OR($A$1="",$A3="",$B3=""),"",MAX(IF((Sheet1!$A:$A=$A$1)*(Sheet1!$B:$B=$A3)*(Sheet1!$C:$C=$B3),Sheet1!$E:$E)))
最後にＣ3とＤ3を選択して下方にオートフィルドラッグします。
=IF(OR($A$1="",$A3="",$B3=""),"",MIN(IF((Sheet1!$A:$A=$A$1)*(Sheet1!$B:$B=$A3)*(Sheet1!$C:$C=$B3),Sheet1!$E:$E)))
これで、お望みの表が出来上がります。

回答No.5

merlionXX

No2 merlionXXです。
A列の日付も対象なのですね。では

2009/7/1、5年、4組　の場合

最高点
=SUMPRODUCT(MAX((A2:A1000=DATEVALUE("2009/7/1"))*(B2:B1000="5年")*(C2:C1000="4組")*E2:E1000))

最低点
=SUMPRODUCT(SMALL((A2:A1000=DATEVALUE("2009/7/1"))*(B2:B1000="5年")*(C2:C1000="4組")*(E2:E1000),ROWS(B2:B1000)+1-SUMPRODUCT((A2:A1000=DATEVALUE("2009/7/1"))*(B2:B1000="5年")*(C2:C1000="4組"))))

もし、学年や組が上記のような文字列ではなく数値なのであれば
"5年"を、5
"4組"を、4 に置き換えてください。（数値の場合、""も不要になります）

補足

回答ありがとうございます。
最大値できました！！

データベースが日々大きくなることを想定しているので、Ａ２：A１０００の所はＡ：Ａにしてみました。
すると、最小値が上手く計算できなくなりました。

ここをうまく回避するにはどうしたらいいのでしょうか？

すみません、素人なもんで、応用が上手くできません。
教えて下さい。
宜しくお願い致します。

回答No.4

tom04

No.3です！
何度もごめんなさい。

投稿した後で質問文を読み直して見ると
日付も検索対象なのですね？

↓の画像のように検索対象を増やして

K2セルの数式を
=IF(COUNTBLANK(H2:J2)>0,"",DMAX(A1:F10,5,H1:J2))

K3セルの数式を
=IF(COUNTBLANK(H2:J2)>0,"",DMIN(A1:F10,5,H1:J2))

に変更してみてください。

結局、D○○関数と頭にDがつく関数は検索対象が増えてもその表をつくって
検索対象に加えれば希望の値が求められるはずです。

どうも何度も失礼しました。m(__)m

補足

回答ありがとうございます。
こういう方法もあるんですね。
ちょっと質問です。

最大値と最小値を横に並べて、５年１組の下に２組、３組と続けたい場合はどうしたらいいですか？

見出し行がちょっとネックになるのでしょうか？
それとも２行目３行目でも平気なんですか？

さらに質問になってしまって、すみません、教えて下さい。
宜しくお願い致します。

回答No.3

tom04

こんにちは！
参考になるかどうか分かりませんが・・・

生徒氏名等は関係なく、学年と組で最高点・最低点などを表示させたいということですよね？

一応そういう事だと解釈させてもらって
↓の画像のような表をつくってみました。

DMAX関数や・DMIN関数がありますので
それを使っています。

表のJ2セル（最大値）に
=IF(COUNTBLANK(H2:I2)>0,"",DMAX(A1:F10,5,H1:I2))

J3セル（最小値）に
=IF(COUNTBLANK(H2:I2)>0,"",DMIN(A1:F8,5,H1:I2))

という数式が入っています。

尚、H2・I2セルに学年・組を入力するたびにその最大値・最小値が表示されます。

表のデータは10行目までの数式ですので
データの量によって範囲指定はアレンジしてみてくださいね。

このDMAX関数等はタイトル行まで範囲指定が必要になり、
条件選択も同じタイトルが必要です。

以上、参考になれば幸いですが、
的外れなら読み流してくださいね。m(__)m

回答No.2

merlionXX

データベース？
エクセルで作成したただの成績一覧表ですよね？
その大量のデータがいくつあってどのような配置をしているのかわかりませんので
仮に
A列2行目～1000行目に学年が文字列で入っている。（例：5年）
B列2行目～1000行目にクラスが文字列で入っている。（例：4組）
C列が氏名だとして
D列2行目～1000行目に点数が数値で入っている場合

5年4組の最高点
=SUMPRODUCT(MAX((A2:A1000="5年")*(B2:B1000="4組")*D2:D1000))

5年4組の最低点
=SUMPRODUCT(SMALL((A2:A1000="5年")*(B2:B1000="4組")*(D2:D1000),ROWS(A2:A1000)+1-SUMPRODUCT(((A2:A1000="5年")*(B2:B1000="4組")))))

回答No.1

gyouda1114

データベースがどのように作られているか判らないと
回答のしようがありません。

補足

すみません。
データバースは以下のようになっています。

Ａ列＝テスト日、Ｂ列＝学年、Ｃ列＝組、Ｄ列＝生徒Ｎｏ、Ｅ列＝テスト点数、Ｆ列＝評価（Ａ、Ｂ、Ｃ）

となっています。