検索値に一致するデータ行の抽出方法(エクセル)

このQ&Aのポイント
  • エクセルを使用して、検索値に一致するデータ行を抽出する方法について説明します。
  • データシートで、任意のセルに検索値を入力し、別の列に検索結果を表示させることができます。
  • この方法を使えば、大量のデータから特定の条件に一致する行を抽出することができます。
回答を見る
  • ベストアンサー

検索値に一致するデータ行の抽出方法(エクセル)

例えば・・・ ↓データ _ABCDE 1あカa1122 2あキb1223 3いクc1324 4うケd1425 5うコe1526 のようなデータがあったとして、別のシートの任意のセル(例えばA1)に「あ」と入力し、C列に「カ」を入力することでC列以降のデータをD列以降に並べたいのですが・・・ 結果↓ _ABCDE 1う←任意のセル:データシートのA列の検索値 2 3__*項目* 4__ケd1425 5__コe1526 ___↑ ___データシートのB列を検索する値を入力するC列    *データの行数は5000は軽くあります。 *データシートは、受け取る側のシートとは別のファイルにしたい。 *受け取る側は、1~100行になります。(ファイルによります) *受け取る側のファイルは、100ファイルぐらいになります。 *いずれのファイルも列数は2,30列程度です。 *格納するフォルダは同じです。 *できればフォルダ毎別のPCに持っていっても作業できるようにしたい。 環境:構築していくマシンは、Windows2000Pro,Excel2000です。 (実際の抽出作業もこれがメインのPCですが、Win95、98,Excel97、2000でも抽出作業ができれば嬉しいです。)

質問者が選んだベストアンサー

  • ベストアンサー
  • nishi6
  • ベストアンサー率67% (869/1280)
回答No.3

1) B1に表示 2) ALL(半角) またはALL(全角) で全項目を抽出。 3) クリアした 4) B1が未入力の場合、1回目と解釈。項目を表示した。 5) 6)とからめて、受け取るシートのH列からSUMをセット。10000行を集計する算式。 6) データシートはG列から12列、月次データ。受け取るシートには1列ずれて、   H列から5列、月次データを逆順に表示。 データシートの1行目は項目名としています。 補足もこれくらいにしましょう。意図したことを行っているかどうかも分かりません。 ご参考に。 ThisWorkbookに貼り付け ↓ Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)  '単一セルを操作、C列に入力  If Not (Target.Count = 1 And Target.Column = 3) Then Exit Sub  Set wsData = Workbooks("srcBook1.xls").Worksheets("Sheet1")  Dim wYMD As Date     '入力した日付  Dim c As Integer, c2 As Integer '列カウンタ  Dim strA1 As String    '全指定用、A列の値  Dim strB_old As String   '全指定用、B列の値  Dim r As Long       '全指定用、行カウンタ  '入力した日付から項目列を決める  wYMD = Range("D1")  For c = 1 To 5   '項目名と同じ日付を作る(データシートの1行目のG列以降は日付形式とした)   hdYMD(c) = DateSerial(Year(wYMD), Month(wYMD) + c - 1, 1)  Next  '書き出す列を決める  On Error Resume Next  For c2 = 1 To 5   wCol(c2) = 0   For c = wsData.Range("IV1").End(xlToLeft).Column To 1 Step -1    If hdYMD(c2) = wsData.Cells(1, c) Then     If c >= 7 Then 'G列より右なら      wCol(c2) = c     End If     Exit For    End If   Next  Next  On Error GoTo 0  'データを抽出する  Application.EnableEvents = False  If StrConv(Target.Text, vbNarrow) <> "ALL" Then   '単独指定   Data_Pick_Sub Target  Else   '全指定(A、B列はソートされていることが条件)   strA1 = Range("A1")   Target.Select   For r = 2 To wsData.Range("A65536").End(xlUp).Row    If wsData.Cells(r, 1) = strA1 Then     If wsData.Cells(r, 2) <> strB_old Then      '今までに処理していない文字なら入力の変わりに書き込む      ActiveCell = wsData.Cells(r, 2)      '抽出処理を行う      Data_Pick_Sub ActiveCell      strB_old = wsData.Cells(r, 2) '今処理した文字列     End If    End If   Next  End If  Application.EnableEvents = True End Sub 標準モジュールに貼り付け ↓ Public wsData As Worksheet 'データシート Public wCol(5) As Integer '日付に対応して書き出す列番号 Public hdYMD(5) As Date '項目名の日付 'データを抽出する Sub Data_Pick_Sub(schRg As Range)  Dim rw As Long '行数  Dim cLstCol As Integer 'コピー元のセル範囲の最後の列  Dim cRow As Integer 'コピー先の行番号  Dim strA As String, strC As String 'A列の値、C列の値  Dim HD As Integer, wHD As Integer '表題カウンタ  Dim TopAdr As String, BotAdr As String 'Sum用の開始、最終アドレス  strC = schRg.Text  strA = schRg.Offset(0, -2).End(xlUp).Text  With wsData   For rw = 1 To .Range("A65536").End(xlUp).Row    If .Cells(rw, 1) = strA Then     If .Cells(rw, 2) = strC Then      cLstCol = 11 '元のデータの範囲を決める      wHD = 0      For HD = 2 To cLstCol 'コピー実行       If schRg.Offset(0, HD - 2).Column > 7 Then 'G列(7がGのこと)以降        wHD = wHD + 1        If wCol(wHD) > 0 Then         schRg.Offset(cRow, HD - 2) = wsData.Cells(rw, wCol(wHD))        End If       Else        schRg.Offset(cRow, HD - 2) = wsData.Cells(rw, HD)       End If      Next      '*** B1が未登録の時、最初の処理とする ***      If Range("B1") = "" Then       Range("B1") = .Cells(rw, 2)       With schRg        wHD = 0        For HD = 2 To cLstCol '項目名を書く         If .Offset(0, HD - 2).Column > 7 Then 'G列(7がGのこと)以降          wHD = wHD + 1          .Offset(-1, HD - 2) = Format(hdYMD(wHD), "yyyy/mm/dd")         Else          .Offset(-1, HD - 2) = wsData.Cells(1, HD)         End If        Next        For HD = 2 To cLstCol '合計の算式を書く         TopAdr = .Offset(0, HD - 2).Address(0, 0)         BotAdr = .Offset(10000, HD - 2).Address(0, 0)         If .Offset(0, HD - 2).Column > 7 Then 'G列(7がGのこと)以降          .Offset(-2, HD - 2).Formula = "=SUM(" & TopAdr & ":" & BotAdr & ")"         End If        Next       End With      End If      cRow = cRow + 1 '複数見つかったら次の行へ     End If    End If   Next  End With  Application.CutCopyMode = False 'コピー内容を空にする  schRg.Offset(cRow, 0).Select 'C列に復帰 End Sub

rurucom
質問者

お礼

nishi6さん!ありがとうございます。お返事が遅れてすみません。 実は、なかなか時間に余裕がなく、現在作業中断の状態です。 コードもまだ実際のもにに落とし込めていません。いづれにしろ今月か来月には仕上げていきたいので、教えていただいたコードを参考に、勉強しながらやっていきます。 すぐすぐに結果が書けそうにないので、取り急ぎお礼申し上げます。ありがとうございました。 今後、部分的に質問することがあると思いますが、その際にはよろしくお願いいたします。

その他の回答 (2)

  • nishi6
  • ベストアンサー率67% (869/1280)
回答No.2

>Data_Pick_Subでとまります。何故でしょうか? 失礼しました。回答に1行抜けていました。多分この影響です。 Dim wsData As Worksheet '←この1行を追加して下さい。 Sub Data_Pick_Sub(schRg As Range) Excel2000 で確認しました。 >それと、データシートの検索列を指定させている部分を教えてください。 標準モジュールに貼り付けた部分で  If wsData.Cells(rw, 1) = strA Then   この Cells(rw, 1) の1がA列を表します。   If wsData.Cells(rw, 2) = strC Then    この Cells(rw, 2) の2がB列を表します。    wsData.Range(wsData.Cells(rw, 3), wsData.Cells(rw, copyLastCol)).Copy     この Cells(rw, 3) の3がC列を表します。     一番右の列は計算しています。列IVから左に動いて登録された列を探しています。 質問にあるままにしているつもりです。 Cells(行,列)の引数『列』は 列A,B,C,D,E,F・・・に対し、1,2,3,4,5,6・・・ となります。 //////////////////////////////////////  ダメ押しで説明を追記しておきます。 ////////////////////////////////////// '***ThisWorkbook*** Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)   '受け取る側のシートに入力された時、それが単一セル(Target.Count=1)で   'C列(Target.Column=3)に入力されたら、Targetを引数にしてData_Pick_Subに分岐   If Target.Count = 1 Then '単一セルを操作     If Target.Column = 3 Then 'C列に入力       Data_Pick_Sub Target     End If   End If End Sub '***標準モジュール*** '変数を宣言 Dim wsData As Worksheet Sub Data_Pick_Sub(schRg As Range)   '変数wsDataにデータシートを定義   Set wsData = Workbooks("srcBook1.xls").Worksheets("Sheet1")   Dim rw As Long '行数   Dim copyLastCol As Integer 'コピー元のセル範囲の最後の列   Dim copyRow As Integer 'コピー先の行番号   Dim strA As String 'A列の値   Dim strC As String 'C列の値   'strCが受け取る側のシートのC列に入力した値(schRgが入力したセル)   strC = schRg.Text   'strAが受け取る側のシートのA列に入力した値   'C列に入力されたら探すようにしてあるため、ここでA列の入力値を探している   'schRgから.Offset(0, -2)で左に2つ動き、.End(xlUp)で上方向に登録されたセルを探している   strA = schRg.Offset(0, -2).End(xlUp).Text   'wsData.Range("A65536").End(xlUp).Rowでデータシートのデータ数を求めている   For rw = 1 To wsData.Range("A65536").End(xlUp).Row     'For Next でA、C列を調べる     'データシートのA、B列の値と、受け取る側のシートのA、C列を照合する。     If wsData.Cells(rw, 1) = strA Then       If wsData.Cells(rw, 2) = strC Then         '一致したデータが見つかったら、データシートの複写範囲を決める         '列方向の最後が不明なので右方向から探している         copyLastCol = wsData.Range("IV" & rw).End(xlToLeft).Column         'コピー実行         '見つかった行の、3列目から一番右の列までをコピー         wsData.Range(wsData.Cells(rw, 3), wsData.Cells(rw, copyLastCol)).Copy         '受け取る側のシートのC列の右のセルに貼り付ける         schRg.Offset(copyRow, 1).Select: ActiveSheet.Paste         '複数見つかったら次の行へ貼り付けるためカウンタを進める         copyRow = copyRow + 1       End If     End If   Next   '次の入力を待つ   schRg.Offset(copyRow, 0).Select 'C列に復帰 End Sub うまく動けばいいですが。

rurucom
質問者

補足

ご丁寧にありがとうございます。うまく動きました!!大変勉強になります!感謝です! 付加機能でいくつかお願いします。 1)受け取る側とシートのA1の検索値のデータシートのB列の値(文字列)を受け取るシートのB1に表示する。   *データシートには検索結果は複数存在しますが、受け取るシートのB1に1つだけ表示させたい。 2)A1とC列に検索値を入れますが、A1に該当するものすべてを抽出したい場合の方法はありませんでしょうか?     例えば、C列に"ALL"を入力するなど・・・    *9999999の数値または、文字は100%ありえないので使用できます。 3)C列へ入力後エンターを押した後、コピーしてきますが、次の行で誤ってエンターすると最後にコピーしてきた列を貼り付けてしまいます。できれば回避したいのですが・・・ 4)データの項目をコピーしたい。項目は、日付で毎月変わっていきますので、その都度範囲指定して項目を受け取ったほうがいいのですが、一応自分でコピー&ペーストのコードを記述してみましたが、エンターでプログラムが実行される度にコピペをしてしまいます。C列の検索値を入力後のエンターをした場合のはじめの1回のときだけコピペを実行してくれるといいのですが・・・どこの部分にどのように記述すればよいのでしょうか? 5)受け取るシートに抽出(コピー)してきたデータの、G列以降の各列の5行目 (G列と5行目は任意で、とりあえず今回の例として・・・)から最終行の縦合計を求め、4行目は項目を置いたとして、3行目の各列にその合計値を表示させたいのですが・・・ 6)これは、ちょっと贅沢でやっかいなことかもしれませんが、できれば・・・ 今のところ、別のシートからMATCH関数で抽出しようと思って途中までできていますが、重たくなってきたので、一気にできればともおもって(^^; ) ・・・質問します。できればで良いです。 例えば・・・  データシートのG列以降の項目(1行目)にはG列を基点とし当月から過去1年間の月毎の日付(月毎でG列が2002/5/1で、以降H列が2002/4/1 ・・・・・これは当月を基点とし毎月変わっていきます)になっています。 受け取るシートの任意のセル、例えば"D1"に「2002/03/16」などの任意の日付を入力することで入力された月から未来へ5ヶ月を計算(計算後は各月の1日)しデータシートの列(2002/3/1~2002/7/1)をコピーしてくるようにしたい。この例の場合は、データシートには、6月と7月のデータはないので、2002/3/1~2002/5/1までの該当データを抽出し、2002/6/1~2002/7/1までは項目のみでデータは空白になる。(この例の場合D1に入力した日付が2002/3/??だったら2002/3/1~2002/7/1までの該当データになる) 説明が下手くそですみません。何卒よろしくお願いいたします。

  • nishi6
  • ベストアンサー率67% (869/1280)
回答No.1

今、データシートは、ブック名『srcBook1.xls』、シート名『Sheet1』としてあります。 『Set wsData = 』の行ですので、ご自分の環境に合うように修正して下さい。 このマクロをセットしたBook(受け取る側)と、データシートのブックを起動して実行します。 受け取る側ではどのシートでも実行できます。 受け取る側のシートでは、  (1)A列にデータシートのA列検索用入力  (2)C列にデータシートのB列検索用入力  (3)(1)の行番号<(2)の行番号 が条件です。(1)、(2)に一致すれば見つかった行(複数行でも)を出力します。 (1)をそのままにして、(2)のみ変更していく事ができます。こういうことがしたかった? ThisWorkbookに貼り付け ↓ Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)   If Target.Count = 1 Then      '単一セルを操作     If Target.Column = 3 Then   'C列に入力       Data_Pick_Sub Target     End If   End If End Sub 標準モジュールに貼り付け ↓ 'データを抽出する Sub Data_Pick_Sub(schRg As Range)   Set wsData = Workbooks("srcBook1.xls").Worksheets("Sheet1")   Dim rw As Long        '行数   Dim copyLastCol As Integer  'コピー元のセル範囲の最後の列   Dim copyRow As Integer    'コピー先の行番号   Dim strA As String      'A列の値   Dim strC As String      'C列の値   strC = schRg.Text   strA = schRg.Offset(0, -2).End(xlUp).Text   For rw = 1 To wsData.Range("A65536").End(xlUp).Row     If wsData.Cells(rw, 1) = strA Then       If wsData.Cells(rw, 2) = strC Then         '元のデータの範囲を決める         copyLastCol = wsData.Range("IV" & rw).End(xlToLeft).Column         'コピー実行         wsData.Range(wsData.Cells(rw, 3), wsData.Cells(rw, copyLastCol)).Copy         schRg.Offset(copyRow, 1).Select: ActiveSheet.Paste         '複数見つかったら次の行へ         copyRow = copyRow + 1       End If     End If   Next   schRg.Offset(copyRow, 0).Select 'C列に復帰 End Sub

rurucom
質問者

補足

nishi6さん!ありがとうございます。お久しぶりです。以前は、かなりお世話になりました。その節はどうもありがとうございました。 またお世話になりますが、よろしくお願いします。 >(1)をそのままにして、(2)のみ変更していく事ができます。こういうことがしたかった? そのとおりです。(2)が複数行です。 ThisWorkbookに貼り付けた、 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range) に黄色マーク。 Data_Pick_Subでとまります。何故でしょうか? それと、データシートの検索列を指定させている部分を教えてください。 よろしくお願いします。

関連するQ&A

  • 条件に一致する行データを別シートに抽出する

    日付別の各データが入力された総括のシートがあり「0」とします。 別に13個の項目別シートを固定の様式で作ってあります。「1~13」とします。 シート「0」のB2~B30までセル上に入力するデータが1~13の整数として入力する訳ですが、その入力されたデータセルの中で該当する数字を条件として抽出し目的のシートの決められた座標から順に行を開けずに表示したいのです。 例えばシート「0」のB列に6という数字が入力されていたら同じ行A、C、Dのデータを全てシート「6」のA、B、Cに表示させたいのです。 同じように他の数字も該当するシートに該当する数字の全てを表示したいのです できるだけ判り易く教えてくださいお願いします。

  • データ抽出方法

    おはようございます。 sheet1 B列、C列に重複してるデータと、そうでないデータが混在して沢山あります。 sheet2 B列、C列に一点一様の型でデータを抽出したいご伝授下さい sheet1 B列、C列 A-1-1 A A-1-1 A B-2-1 D B-2-2 E C-2-1 B C-2-1 B C-2-3 C sheet2 B列、C列(抽出結果) A-1-1 A B-2-1 D B-2-2 E C-2-1 B C-2-3 C

  • エクセルで、データのある行だけを抽出したい。

    エクセルのSheet1にデータが入っており、セルが空白でない行のデータのみを、Sheet2に抽出したいと思っています。 Sheet2にあらかじめ関数を入れておく方法にしたいため、関数について教えてください。 タイムテーブルで予定を管理するような表で、A~Hまでにデータが入っています。 予定を入れる際にはE列には必ず入力をするため、E列が空白でない行のみを抽出したいです。 A|B|C|D|E|F|G|H 月|日|時間|… 11|22|10:00|… 11|22|10:30|… 時間枠は10:00~17:00の30分刻みで、1日15行使用するので、1年で5475行まで使用します。 1行目はタイトル行なので、データは2行目から入力しています。 フィルタオプションで抽出する方法や、それをマクロで登録しておく方法は分かったのですが、あとから予定を追加することがあるため、マクロを実行する度にデータが置き換わると困ってしまいます。 また、抽出したSheet2のI列以降には備考などを入力したいため、やはり後から列がずれると困るため、A~H列に関数を入れておく方法で実行したいと思います。 よろしくお願いいたします。

  • エクセル 大量のデータから抽出し自動表示

    エクセルのSheet1のC列に大量(3500件くらい)のデータがあります。このデータは毎日さらに増えます。D列にもおなじだけのデータがあります。 (E列・F列にはあとから別のデータをさらに入力していきます。) Sheet1 A B C D E F     1 2 3 4     2 3 4 5      1 3 5 6     3 5      2 4      ・ ・     ・ ・ こんな感じです。 今回、このC列に入っているデータを検索し、完全に一致したデータのみを別シートに抽出したいのです。 C列にはいろいろな数字が入っていて、同じものもありますが、D列にも同じ数字とは限りません。 なので、VLOOKUPは使用できないのかと思っています。 一致したデータはその行をすべて別シートに抽出したいです。 検索をかけて一致しないデータがあった場合もその検索した数字は残しておいてさらに今後一致になるまでデータを残しておくという方法はできますでしょうか。 エクセルは2003です。 よろしくお願いします。

  • 【Excel】一致するデータの検索

    Excel2003を使用しています。 Sheet1のB列の値がSheet2のA列に入力されている値と一致したら、Sheet1のC列とD列の値をSheet2のC列とD列に表示させたく、VLOOKUP関数を使用したところ、Sheet1のB列とSheet2のA列のデータの並び方(順序)が同じではないためか、VLOOKUP関数ではできませんでした。 こういう場合、マクロで処理することは可能でしょうか?可能であれば、どのようにコードを記述すればいいでしょうか? Sheet1のデータは4行目から、Sheet2のデータは6行目から入力されています。 よろしくお願いします。

  • 重複するデータの抽出について(エクセル)

    エクセル2003にて重複するデータの取り出しをしたいのですが、 わからないので教えてください。 例えば   A列   B列   C列    D列    E列・・・ 1  色  1回目  2回目  3回目   4回目 2  赤   あ    a      A       0 3  白   い    b      B       1 4  黄   う    c      C       2 5  黄   え    d      D       3 6  黒   お    e      E       4 7  赤   か    f      F       5 : このデータの中から、別のシートのA1に 赤と入力したら、B1にD列のA・Fを抽出 黄と入力したら、B1にD列のC・Dを抽出をしたいです。 VLOOKUP関数を使用してみたのですが、 赤と入力すると、D列のA(1番上のデータ)のみしか抽出出来ず、Fが抽出されません。 重複するデータがあるのはA列のみで、D列には重複するデータはありません。 わかりにくい文章ですみませんが、よろしくお願いします。

  • 検索したい日付からデータを抽出したい。

    こんにちわ。 キャンペーンが何日~何日までどういった内容がある、という一覧を作成しています。 Aの列が開始日、Bの列が終了日、Cの列がキャンペーン内容、です。 検索したい日付をD1のセルに入力したら、その日付に該当するキャンペーンすべてを別のシートに抽出できないでしょうか・・・ オートフィルタで抽出は出来るのですが、日付を入力しただけで抽出 させたいのですが、無理でしょうか・・・ よろしくお願いします。

  • Excelでの複数データ抽出方法

    Excelで関数を使用して以下のようなデータから 複数のデータ抽出を行いたいのですが可能でしょうか? <sheet1> A列 B列 C列 D列     E列 123456 11 12 13    13 123457 12 13 14    13、14 123158 13 14 15    13,14 123159 14 15 16    14  123160 15 16 17 ※抽出するデータは「13・14」になり、抽出場所は別シートでもかまいません。 過去の質問など参照してみましたがうまく出来ませんでした。 複数の関数を組み合わせでもかまいませんので、お手数おかけしますが よい方法が有ればご教示お願いします。

  • 必要なデータを抽出したい

    必要なデータを抽出したい    A   B    C   D ・・・ 1 7/1   101   102  103 2 7/2   101   103 3 7/3   105   203 4 7/4   103   205 5 7/5   101   202 ・ ・    ・ ・ ・    ・ ・ ・    ・ Excelで上の様にA列に日付、B行列以降に数字が書かれているシートから、抽出したい数字が含まれている行を別シートに抽出するマクロはどのように書けばよろしいのでしょうか。 技術者の方、よろしくお願いします。 例えば、101が含まれているデータを抽出したら、sheet2に    A   B    C   D 1 7/1   101   102  103 2 7/2   101   103 3 7/5   101   202 となるようにしたいのです。

  • データの抽出について

    エクセルにてデータの抽出をしたいのですが、わからないので教えてください。 例えば、下の様なデータがあります。 回数   1回目   2回目   3回目   4回目 1000    あ      a      A      0 1001    い       b       B      1  1002    う       c       C      2 1003    え      d       D      3   1004    お      e       E      4  1005    か      f       F      5 別のシートに、 回数の1001と入力すると、3回目のBが抽出    1003と入力すると、3回目のDが抽出    1005と入力すると、3回目のFが抽出出来る様にしたいです わかりにくい文章ですいませんが、ご回答よろしくお願いします。 

専門家に質問してみよう