• 締切済み

Excel 期間を条件にしたデータベースの検索、抽出について（VBAマクロまたは関数使用）

Excelで塾生の模試の成績と学校での実際の試験結果との比較を行いたいと思っています。 bookの構成は下記のとおりです sheet1は模試の成績のリストで、A列に塾生の名前,B列に模試の実施日,C列に模試の成績が記入されたリストがあります。（A列の名前順でソート） （sheet1例） A列, B列, C列 稲垣, 3/3, 50 稲垣, 3/30, 60 香取, 4/12, 40 木村, 1/20, 75 木村, 4/15, 60 木村, 5/20, 80 中居, 5/25, 100 中居, 5/30， 95 中居, 8/30, 50 sheet2は実際の試験結果のリストで、A列に学生の名前,B列に試験の実施日,C列に試験の成績が記入されたリストがあります。。（A列の名前順でソート） （sheet2例） A列, B列,C 列 石田, 4/1, 60 稲垣, 4/1, 80 稲垣, 4/2, 95 稲垣, 5/1, 60 香取, 5/1, 40 木村, 2/1, 75 木村, 4/1, 80 木村, 5/1, 60 田中, 4/1, 80 中居, 6/1, 100 中居, 7/2, 50 森, 7/2, 80 模試実施日と試験日との関係も分析したいため、模試から2週間以内のテスト結果を抽出し、比較したいと思っています。 その他の条件としては、 (1)抽出結果（試験の得点）はsheet1のD列に出力したい。 (2)試験の実施日が模試の実施日より2週間以上後の場合は“期間外”と表示したい。 (2)最後に模試を受けた後にまだ試験を受けていない場合は“未受”と表示したい。 （抽出結果例） 稲垣の3/3の模試場合、2週間以内に試験を受けていないためD列には“期間外”と表示 稲垣の3/30の模試の場合、D列には4/1の試験結果の“80”を表示（模試後2週間以内に2回以上試験を受けている場合は、より模試実施日に近いデータを選択し抽出） 木村の5/20の模試の場合、模試後に試験を受けていないためD列には“未受験”と表示 中居は6/1の試験の2週前以内に5/25，5/30と2回模試を受けているが、この場合両方の模試結果のD列に6/1の試験結果である100を表示 簡単なようでなかなかうまく抽出が出来ず悩んでいます。 良い方法がありましたらどうかご教授ください。

みんなの回答

noname#204879
noname#204879
回答No.4

別解 =IF(MAX((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>=B1)*(Sheet2!B$1:B$100<=B1+14)),SUMPRODUCT((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100=LARGE((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>=B1)*(Sheet2!B$1:B$100<=B1+14)*(Sheet2!B$1:B$100),SUM((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>=B1)*(Sheet2!B$1:B$100<=B1+14)))),Sheet2!C$1:C$100),IF(MAX((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1+14)),"期間外","未受"))

yaya1977
質問者

お礼

ご回答ありがとうございました。 当方の応用が利かず、今回は別の方法を採用しました。 お手数をお掛けしました。

  • n_na_tto
  • ベストアンサー率70% (75/107)
回答No.3

●E,F列作業列を使う方法 ＿＿＿A＿＿_B＿＿C＿＿＿_D＿_E＿＿F_ 1＿稲垣＿_3/3＿_50＿期間外＿_2＿4/1_ 2＿稲垣＿3/30＿_60＿＿＿80＿_2＿4/1_ 3＿香取＿4/12＿_40＿期間外＿_5＿5/1_ 4＿木村＿1/20＿_75＿＿＿75＿_6＿2/1_ 5＿木村＿4/15＿_60＿期間外＿_8＿5/1_ 6＿木村＿5/20＿_80＿＿未受＿_0＿＿＿ 7＿中居＿5/25＿100＿＿_100＿10＿6/1_ 8＿中居＿5/30＿_95＿＿_100＿10＿6/1_ 9＿中居＿8/30＿_50＿＿未受＿_0＿＿＿ E1[直後の試験日がある行番号] =MIN(IF((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1),ROW(A$1:A$100),"")) Ctrl+Shift+Enter同時押し 下方向・↓ F1[直後の試験日] =IF(E1=0,"",INDEX(Sheet2!B$1:B$100,E1)) 下方向・↓ D1[判定] =IF(F1="","未受",IF(F1>B1+14,"期間外",INDEX(Sheet2!C$1:C$100,E1))) 下方向・↓ ●作業列なし (激重。どちらのシートも100行が限界？) D1[いきなり判定] =IF(SUMPRODUCT((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1))=0,"未受",IF(SUMPRODUCT((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1)*(Sheet2!B$1:B$100<=B1+14))=0,"期間外",INDEX(Sheet2!C$1:C$100,MIN(IF((Sheet2!A$1:A$100=A1)*(Sheet2!B$1:B$100>B1)*(Sheet2!B$1:B$100<=B1+14),ROW(A$1:A$100),""))))) Ctrl+Shift+Enter同時押し 下方向・↓

yaya1977
質問者

お礼

ご回答ありがとうございました。 当方の応用が利かず、今回は別の方法を採用しました。 お手数をお掛けしました。

  • ASIMOV
  • ベストアンサー率41% (982/2351)
回答No.2

作業用のsheetを用意します（sheet3とします） sheet1の内容をsheet3にコピーします sheet3の D列に 　=IF(A2=A1,IF(E2<>"m",IF((DATE(YEAR(B2),MONTH(B2),DAY(B2))-DATE(YEAR(B1),MONTH(B1),DAY(B1)))<14,C2,"期間外"),"未受"),"") E列に 　"m"を入れます （sheet1のデーターのある行の分のみ） 次に sheet2のデーターを、コピーしたsheet3のデーターの末尾から貼り付けます データー全体を選択して、並べ替えをします キー１はA列 キー２はB列にします これで一応出来るんですが、模試と本試の結果が混ざっていますので も一度、全体をコピーして、「値」で貼り付けし、オートフィルターなどで、"m"を抽出して出来上がりです

yaya1977
質問者

お礼

ありがとうございます。今回はこの方法を応用させていただきました。

  • n-jun
  • ベストアンサー率33% (959/2873)
回答No.1

ＶＢＡなら、試しに双方を総当たりでやってみるとか。。。 結構複雑そう。。。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • EXCELで複数条件に合致する全データの抽出方法

    任意のデータ（表）から条件に合致するデータをすべて抽出し、別表の任意の場所に表示する方法を教えていただけますでしょうか。 EXCELにあまり詳しくないので、マクロではなく関数での方法をお願いいたします。 ＜原簿データ（Sheet1）＞ ・講師の授業実績を日別に管理しているデータです。 ・年間の日別データ（約500行）が一つの表に入っています。 ・列（項目）は、これ以外にも10項目くらいあります。 　A　　　　　　　　B　　　　　C　　　　　D　　　　　　E １　実施日　　　　曜日　　講師名　　授業時間　講師料 2　2012/8/30　　木　　　　中居　　　1　　　　　　2,000 3　2012/9/1　　　土　　　　中居　　　2　　　　　　4,000 4　2012/9/10　　月　　　　木村　　　1　　　　　　2,000 5　2012/9/14　　金　　　　中居　　　2　　　　　　4,000 6　2012/9/20　　木　　　　稲垣　　　2　　　　　　4,000 7　2012/9/30　　日　　　　中居　　　1　　　　　　2,000 8　2012/10/2　　火　　　　香取　　　2　　　　　　4,000 やりたいことは、上記データから、講師への講師料支払いの明細表を毎月、講師別に作成することです。 具体的には「実施月」と「講師名」を条件にして、一致するデータ（任意の項目のみ）をすべて別シートの支払明細表の任意の位置に挿入することです。 たとえば、支払明細表の任意の位置で「9月」、「中居」と入力した場合、原簿データの9月分の中居さんの全データを抽出し、「実施日」、「曜日」、「授業時間」を任意の位置の下方に表示するようにしたいです。 ＜支払明細表＞ 　A　　　　　　　　B　　　　　C　　　　　D　　　　　　E 1　【9月】　　　【中居】 ←実施月と講師名を入力 2　実施日　　　　曜日　　　授業時間 *******以下表示データ 3　2012/9/1　　　土　　　　　2 4　2012/9/14　　金　　　　　2 5　2012/9/30　　日　　　　　1 分かりにくいかもしれませんが、よろしくお願いいたします。

  • エクセル　抽出マクロについて

    はじめまして。マクロ初心者です。 エクセルで抽出結果を別シートにコピーしたいのですが、 抽出条件が２列にわたっています。 Ａ　　　　　Ｂ　　　　　　Ｃ　　　　　　Ｄ 日付　　　タイトル　　　分類(あ)　　分類(い） 11/22　　　テレビ　　　　3　　　　　　2 12/15　　　ラジオ　　　　1　　　　　　4 11/18　　　雑誌　　　　　2　　　　　　5 …というような感じです。 例えば分類が「2」のものを抽出したい時に、 ●シート１のあるセルに「2」と入力してマクロを実行。 　↓ ●シート２に以下のように結果をコピーして表示。 Ａ　　　　　　　Ｂ 日付　　　　　タイトル 11/22　　　　　テレビ 11/18　　　　　雑誌 という具合にしたいのです。（Ｃ列、Ｄ列は表示しない） データはシート３に入力されていて、今後どんどん増えていく予定です。 エクセルは２００７です。 本やホームページを参考に試行錯誤したのですが、うまくいきません…。 どなたかご回答よろしくお願いいたします。 　

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

    エクセルでデータベースを作っています。 大量のデータ（全校生徒テスト成績データベース）の中から、（2009/07/01）の（５年○組）の（最低点）と（最高点）を導きたいです。 これを別シート（シート２）のＣ列に（最低点）を、Ｄ列に（最高点）を入れます。 以下のようにしたいです。 　　Ａ列　Ｂ列　　　Ｃ列　　　Ｄ列 １　09/07/01実施テスト結果 ２　５年　１組　　　２５　　　９０ ３　　　　　２組　　　３０　　　８５ ４　　　　　３組　　　１５　　　９５ ベータベースから条件に合ったものの中から、最小値を導く方法がわかりません。 セルＣ２からＤ４は関数で出来るのでしょうか？ それともＶＢＡ？ どなたか教えて下さい。 宜しくお願い致します。

  • 検索・抽出・貼り付けのマクロ

    シート１のデータをオートフィルターかけて検索し抽出したものを、A列、B列、D列、F列をシート２の、A列、B列、C列、D列に貼り付けるマクロを教えて頂けますでしょうか？ 一週間位悩んでいます。

  • EXCELで、ある行のみ抽出したい（マクロ・関数）

    こんにちは！ マクロまたは関数を使って、列DがA1のとき、次に続く行で、かつD1の行のみを残したい（もしくは新しいワークシートにコピー）と考えています。 なお、A1の入る行はいくつもあり、それらすべてを抽出できたらと思います。 何か良い方法はありますでしょうか？ よろしくお願いいたします。

  • エクセルのＶＢＡマクロで検索と結果表示（抽出）

    エクセルのＶＢＡマクロで検索と結果表示（抽出）を行いたいです。 業務で使用している膨大なリストデータから、特定のキーワードで情報の絞り込みを行いたいのですが、上手くマクロが組めません。 機能としては、シート１で特定のキーワード（テキストボックスに）を入力し検索ボタンを押下すると、 シート２のリストデータから検索に引っかかったセルの"行"を、シート１にリストアップ（貼り付け）していくようなマクロを作りたいのです。 シート２にはB列～AH列ｘｎ行のリストデータがあり、シート２のＫ列のセル内から「シート１のテキストボックスで入力したキーワードを含む」検索を行い、 HITした行をシート１のA9の行から結果として表示を行いたいんです。 簡単に言えばオートフィルタ機能の部分一致版を作りたいのですが・・・。 （オートフィルタでは完全一致でしか抽出が出来ないので） そして、検索ボタンを押下すると前回結果はクリアしたいです。 ネット上のサンプル等も参考にしながらやってみたのですが上手く行きません。。。 どなたか上記のマクロ文をご教授願えないでしょうか。 必要な情報（シート２の特定の列）のみ表示させたいとも思いましたが、むずかしくて断念・・・。 もし可能でしたらこちらもお願い致します。 よろしくお願いいたします。

  • データベースを検索するマクロ（エクセルで）

    エクセル初級者です。 データベース化してある顧客名簿がｓｈｅｅｔ２にあり、Ａ列は個人別コードが、Ｂ列は氏名、Ｃ列は郵便番号、Ｄ列は住所、・・・といった感じになってます。 教えていただきたいのは、ｓｈｅｅｔ１上で氏名を入力して検索するフォームを作り、検索の結果、ヒットしたすべてがｓｈｅｅｔ３上に抜き出す方法って可能ですか？ うまくいえないのですが、例えば「佐藤」と入力して検索ボタンを押せば、ｓｈｅｅｔ２のすべての佐藤さんのデータがｓｈｅｅｔ２の形式と同じ形でｓｈｅｅｔ３に表示されるようにしたいのです。 情報に不足があれば補足しますので、どなたかＶＢＡの書き方を教えてください。よろしくお願い致します。

  • VBAを使用したデータの抽出について

    sheet1に下記のような（例）データベースがありＡ～Ｎ列までデータが入力されています。 Ａ　Ｂ　　Ｃ　　Ｄ　 Ｅ　　 Ｆ　　Ｇ　　Ｈ　Ｉ　Ｊ　 Ｎ 1　○○様　○○　 2名 　車　 　可 東京 *** *** *** 2015/7/1 2　○○様　××　 3名　 車　 不可 埼玉 2015/8/1 3　○○様　×□　 2名　 電車　不可　愛知 2015/8/12 4　○○様　□□　 4名　 バス　 可　 新潟 2015/7/13 5　○○様　○×　 3名　 バス　 可　 宮城 2015/6/1 6　○○様　○□　 4名　 車　 不可　大阪 2015/8/21 7　○○様　□○　 2名　 バス　 可　 山梨 2015/8/7 「sheet1」B列のデータを元にして、別シート（sheet2）のA2列に抽出したいもの（例：バス）を入力し、 フォームボタン（例：抽出）で検索し、抽出された結果のsheet1のA列～G列、N列のみ（H列～J列は不要）をSheet2のA11以下へ表示したいと考えています。 Ａ　 Ｂ　 Ｃ　　Ｄ　 Ｅ 　 Ｆ　 Ｇ　　　Ｎ 4　○○様　□□　 4名　 バス　 可　 新潟 2015/7/13 5　○○様　○×　 3名　 バス　 可　 宮城 2015/6/1 7　○○様　□○　 2名　 バス　 可　 山梨 2015/8/7 どのようなVBAのコードを使用すれば良いでしょうか。 宜しくお願いします。

  • エクセルの抽出に関するマクロ

    (1)OSはVistaです。 (2)エクセル２００７を使用しています。 (3)シートは「会員情報」「抽出条件」「抽出結果」の３枚です。 (4)「会員情報」シートのA5からR588までデータ（氏名、住所、電話番号など）が入っています。A5からR5は、タイトル行（フィールド）です。R列は「フォームコントロール」で挿入したチェックボックスです。。 (5)「抽出条件」シートには、会員の種類による抽出ができるような条件（正会員、準会員、協賛会員など）が設定してあります。 (6)「抽出結果」シートには、抽出された結果がコピーされます。 「会員情報」シートのデータを、「抽出条件」シートで指定した条件で抽出し、「抽出結果」シートにコピーするマクロは成功しました。 チェックボックスにチェックを入れた会員を抽出し、その方たちの郵便用ラベルを作成するのが最終目標ですが、今は「会員情報」シートのチェックボックスを使った抽出とその結果のコピーができないところでストップしています。チェックボックスを使った抽出とその結果のコピーのマクロを教えてください。

  • マクロの検索と抽出について

    いつもこのサイトの皆様にはお世話になっておりますm(_ _)m この度、マクロの検索と抽出について教えていただきたいのですが、 シート１（シート名：住所録）のB9からE30に取引会社の住所録を入力しております。 B列は1～30までの数字、C列には会社名、D列には住所、E列には郵便番号をそれぞれ入力しております。 会社名をキーワードに検索をかけて、検索結果をシート２（シート名：抽出結果）に出力されるようなマクロを作りました。下記を参考にしていただきたいのですが、質問としましては、検索を何回か繰り返し行いたく、そしてその結果を抽出結果のシートに反映させる際に、前回の出力されたセルの下に反映されるようにしたいのです。 質問にまとまりがなくて伝わりづらかったとは思いますが どなたか教えていただけないでしょうか。 よろしくお願いいたします。 Private Sub CommandButton1_Click() '#### 最初に、テキストボックスの条件を住所録シートに転記しておく。 If OptionButton1 = True Then '部分一致にチェックが入っていたら '部分一致検索は、検索条件を「*」で囲む Worksheets("住所録").Cells(4, "C").Value = "*" & TextBox1.Text & "*" ElseIf OptionButton2 = True Then '完全一致にチェックが入っている場合 '完全一致検索は、検索条件の先頭に「'=」をつける Worksheets("住所録").Cells(4, "C").Value = "'=" & TextBox1.Text End If '#### 　検索を実行 Sheets("抽出結果").Select '抽出結果を表示するシートを選択しておく。 Cells.Select '前回の抽出結果を消しておく Selection.Clear Range("A2").Select Sheets("住所録").Range("B9:E109").AdvancedFilter Action:=xlFilterCopy, _ CriteriaRange:=Sheets("住所録").Range("C3:C4"), _ CopyToRange:=Sheets("抽出結果").Range("A1"), _ Unique:=False End Sub Private Sub CommandButton2_Click() On Error Resume Next Worksheets("住所録").ShowAllData End Sub Private Sub CommandButton3_Click() TextBox1.Text = "" End Sub Private Sub UserForm_Initialize() OptionButton1 = True 'あいまいにチェックを入れておく End Sub

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する