エクセルデータベースで抽出

質問

エクセルでデータベースを作っています。
例えば、
全国の小学校の生徒を対象にアンケートをします。
アンケートを受ける受けないは自由です。
アンケートは毎月あります。

アンケートは一人一枚のマークシートで６問にＡＢＣで答えます。
返ってきたアンケートのみデータベースのシート１に入力します。（マークシート読み取り機から一括入力してます）

年月　　　　学校名　　学年　　組　　名前　（１）（２）（３）（４）（５）（６）
０９１　　　Ａ学校　　　５　　１　　ああ　　Ａ　　Ｃ　　Ａ　　Ｂ　　Ｃ　　Ｂ
０９１　　　Ａ学校　　　５　　３　　いい　　Ｂ　　Ｂ　　Ｃ　　Ｂ　　Ａ　　Ａ
０９１　　　Ａ学校　　　５　　４　　うう　　Ｃ　　Ａ　　Ａ　　Ａ　　Ａ　　Ａ
０９１　　　Ｃ学校　　　２　　１　　ええ　　Ａ　　Ｃ　　Ａ　　Ｂ　　Ｃ　　Ｃ
０９１　　　Ｃ学校　　　３　　１　　おお　　Ａ　　Ａ　　Ａ　　Ｂ　　Ｃ　　Ｂ
０９１　　　Ｅ学校　　　４　　４　　あい　　Ｃ　　Ａ　　Ａ　　Ｂ　　Ｂ　　Ａ
０９２　　　Ａ学校　　　５　　４　　いう　　Ｃ　　Ａ　　Ａ　　Ａ　　Ａ　　Ａ
：　　　　　　　　：　　　　　　　　　　　　　：

たとえば、こんな感じで・・・

ここでシート２に、年月と学校名を抽出したいです。
年月　　　学校名
０９１　　Ａ学校
０９１　　Ｃ学校
０９１　　Ｅ学校
０９２　　Ａ学校
　：　　　　：

アンケートは毎月なので、このデータベースは毎月大きくなります。（想定では年に１万行位）
シート２には毎月データ追加分が加算されるようにしたいです。
（シート２を毎回ゼロから作るとデータベースがでかくなったとき、結構時間がかかってしまう為）
なんとかＶＢＡで組もうと思ったのですが、私の腕が足りず出来ません。

どなたかＶＢＡに詳しい方、教えて下さい。
宜しくお願い致します。

回答No.3

KURUMITO · ベストアンサー

ＶＢＡを使わなくても次のようにすることで追加分がシート2に反映できますので何らの問題もないと思います。
シート1では2行目からデータがあり、お示しの表がＫ列まで使用されているとします。
Ｌ2セルには次の式を入力します。
=A2&B2
Ｍ2セルには次の式を入力します。
=IF(L2="","",IF(COUNTIF(L$2:L2,L2)=1,MAX(M$1:M1)+1,""))
Ｌ2セルとＭ2セルを範囲として選択してコピーします。
名前ボックスにはＬ2と表示されていることでしょうがL2:L20000と入力しEnterします。その後に貼り付けを行います。これで20000行まで式をオートフィルドラッグしたと同じ事になります。
次にシート2に移ります。
Ａ2セルには次の式を入力します。
=IF(COUNTIF(Sheet1!$M:$M,ROW(A1))=0,"",INDEX(Sheet1!$A:$B,MATCH(ROW(A1),Sheet1!$M:$M,0),COLUMN(A1)))
この式をＢ2セルまでオートフィルドラッグします。
コピーします。
名前ボックスにＡ2：Ａ20000と入力し貼り付けをします。
これで、シート1に新たなデータが追加されても、自動的に年月と学校名が追加されて表示されることになります。

補足

回答ありがとうございます。
御礼遅れて申し訳ありません。

バッチリできました！
なるほど横に番号を振って行くんですね。

ただ・・・ちょっと問題が・・・。
すみません、実際にはデータベースがかなり大きくて、この処理を加えたところ、すごく遅くなってしまいました。（前段の処理で５分以上）
すみません、結果はばっちりだったんですが、時間がかかり過ぎちゃって。
はじめのベータベースの作り方が悪いと思うのですが、何かいい方法はないでしょうか?

回答No.6

cistronezk

PCの環境によりますが、一般的には数万件のデータを関数のみで処理するのは無謀でしょう。

すでに回答が示されているピボットテーブルによる集計結果をシート２へ出力し、そのデータと後から説明が追加された「別データ」とやらをシート３に統合しはどうでしょう。シート２からシート３への変換は、単なる参照か簡単な関数で対応可能かと思います。

お礼

回答ありがとうございます。
返信遅れて申し訳ありません。

やはりはじめのデータベースの作り方がまずかったようです。
勉強になりました。
今までは小さなものしか作ったことが無かったものですから・・・。

一から勉強し直します。
またいろいろと教えて下さい。
宜しくお願い致します。

返信遅れて本当に申し訳ありませんでした。

回答No.5

tag1701

すみません。No4です。
適当に８，０００件のデータで試したら、やっぱり使用に耐えられませんでした。
すみません。無視してください。

お礼

返信遅れて申し訳ありません。
回答ありがとうございます。

いろいろ試してみましたが、やはりはじめのデータベースの作り方が悪かったようで、うまくいきませんでした。
今は教わったクエリを勉強中です。

いろいろありがとうございます。
また一生懸命勉強します。
返信遅れて本当に申し訳ありませんでした。

回答No.4

tag1701

遅くなる原因（前段階って？）がよくわかりませんが、
抽出方法で別の考え方を紹介します。
エクセルシートを文字通りデータベースとして認識させ、
抽出させます。
１）入力してあるシートは既に保存されていると思いますので、
　　どこに保存したか認識しておいてください。
２）シート２の抽出したい最初のセルにカーソルをおいて
　　「データ」-「外部データの取り込み」-「新しいデータベースクエリ」
　　を選択。（ここでアドインのインストールが始まるケースもある
　　ようですのでその際はOKでインストールしてください）
３）データソースの選択ウィンドウがでますので、Excel File*を選んでOK
４）ここで該当のエクセルファイルが左側のデータベース名の下の方に
　　現れるよう探す。
５）でたらそれを選んでOK
　この時クエリに出来るテーブルがありません等のメッセージが出た
　場合、参照サイトを参照し、出現させてください。
６）左側のシート名の＋を押して列名を表示させ、年月・学校名を
　　右側に移動させ、「次」をクリックします。
７）条件は無いので、「次」をクリックします。
８）並べ替え順序を上から年月・学校名を選択し、「次」をクリックします。
９）Microsoft Queryでデータの表示またはクエリの編集を行うを選択し「完了」をクリックします。
１０）クエリの画面が出て今まで与えた条件の結果が出ています。
　　　この画面の上側ボタン「SQL」を押します。
１１）SQLステートメント画面に　select~ORDER BY `'シート名$'`.年月, `'シート名$'`.学校名と
あります。
１２）この ORDER BYから下をコピーして下さい。
１３）ORDER BYの前に貼り付けてください。
１４）貼り付けたORDER BY を　GROUP BYに書き換えてOKを押してください。
１５）結果が一月・一校にまとまったと思います。そしたらこのクエリ
　　　画面を×を押して消してください。
１６）データを返す場所の確認ウィンドウが出ますので、よければOKを
　　　押してください。
これで抽出完了です。今後は抽出シートの抽出場所最初セルにおいて
「データ」-「データの更新」を押せば、データが何件増えても全て
自動で範囲に入り再計算されます。
この機能はアクセスの一部の機能なので、多分集計スピードは速いと
思います。
興味があれば試してみてください。

参考URL：

http://www11.plala.or.jp/koma_Excel/faq.html#faq13

回答No.2

akina_line

こんにちは。

　お考えになっていることと多少違いますが、ピボットテーブルで集計表を作成したらどうでしょう。
　一例として、下記のような集計表を作ります。
　　　　　　　学年　1　2　3　4　5
年月　　　学校名
０９１　　Ａ学校　　10　20　30　20　20　←年月、学校、学年毎の人数
０９１　　Ｃ学校
０９１　　Ｅ学校
０９２　　Ａ学校
　：　　　　：

　ピボットテーブルの作り方は下記サイトをご参照ください。
　　http://www.officepro.jp/exceltips/pivot/index1.html
　　サンプルとほぼ同じ手順でいけると思います。
　　次に意図した表を作るために項目を設定します。
　　http://www.officepro.jp/exceltips/pivot/index2.html
　　これはサンプルとは項目が全然違うので、以下のようになります。
　　★フィールドリストには年月　　　　学校名　　学年　　組　　名前　（１）（２）（３）（４）（５）（６）が表示されているはずです。
　　★「年月」のフィールドをドラッグして、Ａ列の下の「ここに行のフィールドを。。。」と書いてある枠内にドロップします。
　　★同様に「学校名」も「年月」の下にドロップします。
　　★次に「学年」を3行目の「ここに列のフィールドを。。。」と書いてある枠内にドロップします。
　　★最後に「名前」を「ここにデータアイテム。。。」と書いてある枠内にドロップします。
　　　http://www.officepro.jp/exceltips/pivot/index3.html

　　　以上で集計表が出来上がります。

では。

補足

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

実はこのデータベースには先があって、このシート２には別のデータを付加しなくてはなりません。
どうしても抽出の方法を取りたかったのです。
質問の説明が不十分でした。
どうもすみません。

ピボットテーブルのサイト見ました。
すごくわかりやすく載っていて、大変勉強になります。
また、いろいろと教えて下さい。
宜しくお願い致します。

回答No.1

tag1701

ピボットテーブルを使っては出来ませんでしょうか？
毎回データ範囲の変更は必要ですが、難しいＶＢＡ等を使わなくても
良いのではないかと思いますが。。。

補足

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

実はこのデータベースには先があって、このシート２には別のデータを付加しなくてはなりません。
どうしても抽出の方法を取りたかったのです。

質問の説明が不十分でした。
どうもすみません。