エクセルで複数条件に一致する値を抽出する

質問

お世話になります。
エクセル2007で次のように単語のテストを作成したいのですが，どういう設定をすれば可能でしょうか。

データというシートにはA列に出題テキスト名が上から入力されています。B列には対象学年が入っています。C列にはテキストのページ数が入っています。D列には英単語が入っています。E列には日本語訳が入っています。

問題作成，というシートのA1に「テキスト名」B1に「学年」C1に「開始ページ」D1に「終了ページ」が入力されており，A2からD2にそれぞれ，テキスト名などを入力するようになっています。その横に「問題作成」というボタン（開発タブのなかにあるものを使用）を配置し，このボタンをクリックすると（どんなマクロを組めばいいのでしょうか），A3からB3以下に「データ」というシートの中から，条件に合致する英単語と日本語訳が抽出される，ということをしたいのです。

エクセルの関数はよく使いますし，入力規則も条件付き書式も理解しています。しかし，ここまで複雑な作業は初めてなので，いろいろと試しているのですが，まったく光が見えてきません。

何か説明不足な点がございましたら，補足いたしますので，ご教授，何卒よろしくお願いいたします。

回答No.4

mimeu · ベストアンサー

> マクロを組むにはまず，どこから手をつければいいでしょうか。
基本的には、Excel VBA の参考書を１～２冊お買いになることです。
（たいてい内容のワリにバカ高いですが、しかたありません、授業料です。）

ともあれ、簡単に説明いたします。

(1) まず普通にこのブックを開きます。
　　　（仮に ABCD.XLS という名だとします）
(2) [Alt] + [F11] キーを押します。
　　　→ ＶＢＡの編集画面が表示されます。
　　　　　タイトルバーには 『Microsoft Visual Basic -』などと書かれています。
(3) 画面左に プロジェクトエクスプローラー といタテ長の窓がありますから
　　その中の VBAProject(ABCD.XLS) を探します。
　　更にその中に、『Sheet? (問題作成）』 がありますから、それを選択します。
　　? は数字の 1 とか 2 とかで、『問題作成』と名前を変える前の名前です。
　　これでタイトルバーは、
　　『Microsoft Visual Basic -ABCD.XLS \ [Sheet1 (コード）]』などとなります。
(4) とともに画面右が真っ白になっていると思います。ここにプログラムを書きます。
　　まずその真っ白な上に、多分 『(General)』 と表示したテキストボックスがありますから、
　　その右の▼を押すと『CommandButton1』, 『Worksheet』とあると思います。
　　あなたは『問題作成』という名前でボタンを作ったとお思いでしょうが、
　　実際にはこんな名前になっているかもしれません。
　　それを選択します。
(5) そうすると、その右のテキストボックスの内容が変わるはずですから
　　その右の▼を押し、リストの中から Click を選択します。
　　すると、真っ白な画面に

　　Private Sub CommandButton1_Click ()

　　End Sub

　　と出ます。中味のないプログラムです。
　　そこへ私のプログラムの　中　味　部　分　を貼り付ければ、ＯＫです。

　　Private Sub ... と　End Sub の行が二重にならないように、という意味です。

これで、Excel画面に戻って、ボタンを押せば、実行されるハズです。

お礼

何度もありがとうございます。
実は今日の昼間VBAの本を購入してきたばかりなんです。
「達人が教えるExcelVBAテクニック207」というものです。
達人だからすごいことができるだろう，と思い購入したのですが，内容が達人過ぎてシロウトには理解不能でした(笑)
でもmimeuさんに教えていただいたやり方はわかりやすいので，挑戦してみたいと思います。うまくいくかどうか，不安ではありますが。
本当に何度もありがとうございました。

回答No.3

tom04

こんばんは！
参考になるかどうか判りませんが・・・
関数でやってみました。
「問題作成」というボタンではなく、Sheet1に全ての条件を入力した段階で英語と日本語が表示されるようになります。

↓の画像で右側がSheet2で質問の「データ」Sheetになり、左側がSheet1で「問題作成」というSheetになります。
Sheet2に作業用の列を設けています。

Sheet2のF2セルに
=IF(AND(A2=Sheet1!$A$2,B2=Sheet1!$B$2,AND(C2>=Sheet1!$C$2,C2<=Sheet1!$D$2)),ROW(A1),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーします。

そして、Sheet1のA5セルに
=IF(COUNT(Sheet2!$F$2:$F$1000)<ROW(A1),"",INDEX(Sheet2!D$2:D$1000,SMALL(Sheet2!$F$2:$F$1000,ROW(A1))))
という数式を入れ、列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

尚、数式はSheet2の1000行目まで対応できるようにしていますが
データ量によって範囲指定の領域はアレンジしてみてください。

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

お礼

ありがとうございます。
マクロを使わずに，関数のみで引っ張ってくるんですね。結構関数は使っているつもりでしたが，まだまだ全然勉強不足でした。試しにやってみたところ，いい感じでいきました。
もしマクロを使うやり方がうまくいかない場合は，tom04さんのやり方で今回は乗り切ろうかと思っています。
ありがとうございました。

回答No.2

mimeu

VBAだけでやっても簡単ですよ。
ボタンの名前が 『btn問題作成』 だという前提です。
行数は長いけど、心を静めて読んでいただくと
とっても単純な内容だとおわかりになると思います。

Private Sub btn問題作成_Click()
　　Const 行頭 = 2 ' ★★★★★ データの先頭行、違ってたら直してね ★★★★★
　　Dim データ As Worksheet, 問題作成 As Worksheet, 行1 As Long, 行2 As Long, 行末 As Long
　　Dim 出題テキスト As String, 出題学年 As Integer, 開始頁 As Integer, 終了頁 As Integer
　　Set データ = Worksheets("データ")
　　Set 問題作成 = Worksheets("問題作成")
　　出題テキスト = 問題作成.Range("A2")
　　出題学年 = 問題作成.Range("B2")
　　開始頁 = 問題作成.Range("C2")
　　終了頁 = 問題作成.Range("D2")
　　行末 = データ.Range("A65536").End(xlUp).Row
　　行2 = 5 ' ★★★★★ 抽出先の先頭行、違ってたら直してね ★★★★★

　　' 実際の処理はここから
　　For 行1 = 行頭 To 行末
　　　　If データ.Range("A" & 行1) = 出題テキスト Then
　　　　　　If データ.Range("B" & 行1) = 出題学年 Then
　　　　　　　　If データ.Range("C" & 行1) >= 開始頁 Then
　　　　　　　　　　If データ.Range("C" & 行1) <= 終了頁 Then
　　　　　　　　　　　　データ.Range("D" & 行1 & ":E" & 行1).Copy 問題作成.Range("A" & 行2)
　　　　　　　　　　　　行2 = 行2 + 1
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　End If
　　　　End If
　　Next 行1
End Sub

補足

ご回答ありがとうございます。
教えていただいた通りにVBAでやろうと思ったのですが，申し訳ありません。質問では「マクロ」なんてかっこいいことを書いてしまいましたが，当方実はマクロの超初心者なんです。触ったことはほとんどゼロです。
まず，教えていただいたものをどこに入力するのか，というところからつまずいてしまいました。でも添付してくださった画像を見ると，見事に私が望んでいる状態になっているので，なんとか達成したいのですが。
こんな状態ですがマクロを組むにはまず，どこから手をつければいいでしょうか。
お忙しいところ本当にもうしわけありません。こんな能力しかありませんが，なんとかしたいと思っておりますので，ご協力していただけると大変ありがたいです。よろしくお願いします。

回答No.1

mshr1962

「フィルタオプションの設定」を利用すれば結構簡単にできます。

事前準備として
E1に「テキスト名」F1に「学年」G1に「ページ」H1に「ページ」を入力
※データシートの1行目の項目名に合わせてください。
E2="=*"&A2&"*"
F2="=B2
G2=IF(C2="",">0",">="&C2)
H2=IF(D2<C2,C2,"<="&D2)
とします。
「ツール」「マクロ」「マクロの記録」
※これで標準モジュールにマクロが自動で作られます。
－－－－－－－－－－－－－－－－－－－－－－－－－
「データ」「フィルタ」「フィルタオプションの設定」

抽出先＿＿＿：指定した範囲
リスト範囲＿：=データ!$A:$E
抽出条件範囲：=問題作成!$E$1:$H$2
抽出範囲＿＿：=問題作成!$A$4

でOKを押す。
－－－－－－－－－－－－－－－－－－－－－－－－－
マクロの記録の終了ボタンを押します。

これで4行目に項目名、5行目以降にデータシートの条件に一致した内容が表示されます。
「挿入」「図形」でボタンにする図形を選択。
テキストの挿入で"問題作成"と入力
図形を右クリックして"マクロの登録"で↑で作ったマクロを選択してください。

補足

ご回答ありがとうございます。
教えていただいた通りに入力してみたのですが，F2に="=B2を入力するとエラーが出てしまいます。自動修正してその後も進めてみたのですが，最終的に「問題作成」ボタンを押すと，「データ」のシートの1行目にある「テキスト名」「学年」などが「問題作成」シートのA4の行に返されてしまいます。
何か設定がまずいのでしょうか。ちなみにセキュリティの設定では，マクロが使えるようにしてあります。
お手数ですが，もう一度ご回答をお願いできますでしょうか。
お忙しいところ，本当にすみません。よろしくお願いします。