• 締切済み

条件にあったデータの抽出（別のシートに）

エクセル２００７を使って、検索によるデータの抽出をおこなっています。 データは様々な商品の価格情報で、その店舗、地域等の情報がすべて番号で入力されています。 year item price region 2004 45 23 1 2004 1 5 1 2007 5 98 3 といった具合に多くのデータがあります。 それを異なるシートにその条件にあったものだけを（隙間なく）そのシートの上のほうに詰めて表示 できるようにしたいと奮闘しています。 例えば、別のシートにitem45、region 1のデータを year item price region 2004 45 23 1 2003 45 22 1 2002 45 30 1 と表示するためにはどのようにしたらよろしいでしょうか？ お分かりの方、いらっしゃいましたら教えていただけませんでしょうか？

みんなの回答

  • MackyNo1
  • ベストアンサー率53% (1521/2850)
回答No.5

数式で処理する場合は、データ範囲が大きかったり、表示するデータ数が多いと再計算に時間がかかりシートの動きが重くなりますのであまりお勧めできませんが、例示のデータなら以下のような関数で該当データを表示できます(元データがSheet1のA～D列の100行目までにある場合）。 A列 =INDEX(Sheet1!A:A,SMALL(INDEX(((Sheet1!$B$2:$B$100<>45)+(Sheet1!$D$2:$D$100<>1))*10000+ROW($2:$100),),ROW(1:1)))&"" B～D列(右方向に3つコピー） =IF($A2="","",INDEX(Sheet1!B:B,SMALL(INDEX(((Sheet1!$B$2:$B$100<>45)+(Sheet1!$D$2:$D$100<>1))*10000+ROW($2:$100),),ROW(1:1)))) このようなケースでは、フィルタオプションの操作をマクロで記録して、このマクロを実行するようにすることをお勧めします。

全文を見る
すると、全ての回答が全文表示されます。
  • KURUMITO
  • ベストアンサー率42% (1835/4283)
回答No.4

例えばシート1がまとめのシートでシート2、シート3、…にデータが入力されているとします。 すべてのシートは同じ項目が並んでおり、シート2以降のシートではA1セルにyear,B1セルにitem,C1セルにprice,D1セルにregionの項目名が有り、各データが下方に入力されているとします。 シート2以降についてはそれらのシートのシート名をCtrlキーを押しながらクリックして同じ作業グループにします。 その上で作業列としてシート2のE2セルには次の式を入力して下方にドラッグコピーします。 =IF(INDEX(A:D,ROW(),MATCH(Sheet1!$A$1,A$1:D$1,0))&"/"&INDEX(A:D,ROW(),MATCH(Sheet1!$B$1,A$1:D$1,0))=Sheet1!$A$2&"/"&Sheet1!$B$2,MAX(E$1:E1)+1,"") 上記の式はシート1での抽出の条件を設定することで有効に作動します。 そこでシート1を選択して次の作業を進めます。 A1セルには抽出したい項目名を例えばitemと入力し、B1セルには抽出したい項目名を例えばregionのように入力します。 A2セルにはA1の項目で抽出したい数値、例えば45と入力します。B2セルも同様で例えば1と入力します。 3行目には使うシート名を入力するのですがA3セルは空白のままで、B3セルにはSheet2、C3セルにはSheet3、D3セルにはSheet4・・・・・のように横に入力していきます。 4行目は各シートでの該当する行の数を表示させうためにA4セルには0と入力します。B4セルには次の式を入力して横方向にドラッグコピーします。 =IF(B3="","",MAX(INDIRECT(B3&"!E:E"))) 5行目には該当する行の総数を表示させるためにA5セルには0と入力します。B5セルには次の式を入力して横方向にドラッグコピーします。 =IF(B4="","",A5+B4) A7セルからD7セルにはシート2と同じ項目名を並べます。 A8セルには次の式を入力して横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。 =IF(OR(ROW(A1)>MAX($5:$5),A$7=""),"",INDEX(INDIRECT(INDEX($3:$3,MATCH(ROW(A1)-0.1,$5:$5,1)+1)&"!A:D"),MATCH(ROW(A1)-INDEX($5:$5,MATCH(ROW(A1)-0.1,$5:$5,1)),INDIRECT(INDEX($3:$3,MATCH(ROW(A1)-0.1,$5:$5,1)+1)&"!E:E"),0),COLUMN(A1))) これですべてのシートについて、1行目位で指定した項目と2行目で指定した数値についての該当する行が表示されますね。

全文を見る
すると、全ての回答が全文表示されます。
回答No.3

'キーのリストに一致した行だけを別シートに集める '抽出キーは、列のニモニックとキー値をセットで指定（例えば、[A1：B、B1：goodsA]、[A2：D、B2：domainA]）、複数可 '抽出キーの矛盾はチェックしない 'ターゲットのシートをアクティブにして実行するのが分かり易い Option Explicit Sub ExtractRowsWithMatchKeys() Const xSource = "Sheet1" 'ソースのシート Const xTarget = "Sheet2" 'ターゲットのシート Const xKey = "Keys" '抽出キーのシート Const xKey_Col = "A" '抽出キーを設定する列位置 Const xHeads = 1 'ヘッダの行数 Dim xSheet As Worksheet Dim xSheet2 As Worksheet Dim xKeyWord(1 To 100) As Variant Dim NoData As Boolean Dim MayBeMatch As Boolean Dim kk As Long Dim mm As Long Dim nn As Long Dim xCell As Range Dim xLast As Long Application.ScreenUpdating = False NoData = True 'For nn = 1 To Worksheets.Count For Each xSheet In Worksheets If (xSheet.Name = xKey) Then xLast = xSheet.Cells(Rows.Count, xKey_Col).End(xlUp).Row For kk = 1 To xLast If xSheet.Cells(kk, xKey_Col) <> Empty Then xKeyWord((kk - 1) * 2 + 1) = xSheet.Cells(kk, xKey_Col) xKeyWord((kk - 1) * 2 + 2) = xSheet.Cells(kk, xSheet.Cells(kk, xKey_Col).Column + 1) End If Next kk NoData = False End If Next If (NoData) Then MsgBox ("No Keys found!!") Exit Sub End If Set xSheet = Sheets(xSource) 'ソース Set xSheet2 = Sheets(xTarget) 'ターゲット xSheet2.UsedRange.Clear xSheet.Range("1:" & xHeads).Copy xSheet2.Range("1:" & xHeads).PasteSpecial ' Paste:=xlPasteValues xLast = xSheet.Cells(Rows.Count, xKey_Col).End(xlUp).Row mm = xHeads + 1 For nn = xHeads + 1 To xLast MayBeMatch = True kk = 1 Do Until xKeyWord(kk) = Empty ' exit do If (xSheet.Cells(nn, xKeyWord(kk)).Value <> xKeyWord(kk + 1)) Then MayBeMatch = False Exit Do End If kk = kk + 2 Loop If (MayBeMatch) Then 'セルの値だけをコピー xSheet.Rows(nn).Copy xSheet2.Rows(mm).PasteSpecial Paste:=xlPasteValues mm = mm + 1 End If Next nn xSheet2.Select Application.ScreenUpdating = True End Sub

全文を見る
すると、全ての回答が全文表示されます。
noname#204879
noname#204879
回答No.2

添付図参照 Sheet1 のＡ～Ｄ列範囲、 Sheet2 の範囲 A1:D1、 Sheet2 の範囲 F1:G2、 にそれぞれ dbase、ref、output という名前を付ける。 Sheet2 をアクティブにして、次の[フィルタオプションの設定]を実行すればＯＫ！ [抽出先]→“指定した範囲” [リスト範囲]→ dbase [検索条件範囲]→ ref [抽出範囲]→ output 「データの量がとても多」い場合は時間が掛るけど、トイレに行って戻ってくるまでには希望のデータが出ているかと。

画像を拡大する
全文を見る
すると、全ての回答が全文表示されます。
  • PXU10652
  • ベストアンサー率38% (777/1993)
回答No.1

「例えば、別のシートにitem45、region 1のデータを と表示するためにはどのようにしたらよろしいでしょうか？」 　オートフィルタで、該当データをセレクトし、それを別シートにコピペする。

golioshikun
質問者

補足

ご回答有難う御座いました。 データの量がとても多く、シートもかなり多くにまたがっています。 それらをひとつのシートに自動的に集めるようなことを考えています。 なので、コピーアンドペーストをしない、という条件で、ご回答いただけると助かります。

全文を見る
すると、全ての回答が全文表示されます。
  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. その他MS Office製品

関連するQ&A

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

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

  • 【エクセル】条件に合うものを別シートに抽出

    １、シート１（BC入力）で、ロット番号をバーコード読取りする。 ２、そのロット番号に「検査」、「規制」のデータがあるかどうか（●は有）を、シート２～４から参照し、 　シート１に表示させる。 画像はシート２（生鮮食品）です。シート３、４も同様のつくりとなっています。 シート１はバーコードで読取ったロット番号、およびそれに対応した「検査」、「規制」を表示させるシートです。 【シート１略図】 　検査　　　ロット番号　　　規制 　●　　　　　　4A119　　　　　● 　　　　　　　　4B1018　　　　　● 　●　　　　　　4C267　　　　　● 　　　　　　　　　　　・ 　　　　　　　　　　　・ 　　　　　　　　　　　・ 複数のシートにあるデータベースから、別の１つのシートに条件付きでデータを抽出する方法が わからないでいます。 よろしければ皆様のお知恵を拝借させてください！！

  • ２つの条件からシート２に抽出

    シート１ A1は手入力D4手入力、H2は=D2&C2 担当者と週で検索して抽出する式を教えて下さい。何度もすみません。 上手く出来なくて１行しか抽出しないんです。 上記の表から１週目だったら、１週目の担当者のデータをシート２の表に抽出 週２だったら２週目の担当者データを抽出 A1・D1は手入力 A3は=IF(COUNTIF(Sheet1!D$2:D$1000,B$1)=0,"",MATCH($B$1&$D$1,Sheet1!$H$2:$H$1000,0)) 上記の様にシート２に表示したいんです。 細かく教えて下さい。何度やっても１行目のデータしか抽出しないんです。 A3=IF(COUNTIF(Sheet1!D$2:D$1000,B$1)=0,"",MATCH($B$1&$D$1,Sheet1!$H$2:$H$1000,0)) B3=IF($A3="","",IF(VLOOKUP($A3,Sheet1!$A$2:$H$1000,COLUMN(B1),0)="","",VLOOKUP($A3,Sheet1!$A$2:$H$1000,COLUMN(B1),0))) A4の２行目以降1になってしまって同じ物しか出ないんです。 Aの番号の抽出方法の式を教えて下さい。

  • Excelで条件に合うものを別Sheetに抽出

    Excel2003を使用しています。 下記画像で上の表がsheet1で、下の表がsheet2です。 Sheet1の情報をSheet2の表に抽出したいです。 たとえば、Sheet1で、3日の日（E列）にりんご、ばななに「○」がついているので、 Sheet2の3日の列（E列）に、「りんご」「ばなな」と表示されるようにしたいのです。 この条件を満たせる関数はありますか？ 方法をご教授頂けると幸いです。 よろしくお願いします。 ※「○」は入力規則のリストで、ドロップダウンリストで表示させてるのですが、問題ないでしょうか？

  • データ表から条件一致データを抽出し別の1シート表示

    エクセルでマスター表から、データを抽出して表示させるのに困っています。 具体的には、下図1のＢ1～Ｄ10のデータ表があり、これをSheet2に下図2のように並べて、11行目から、4列おきに表示させたいのですがどうしたらよいのでしょうか。 以前に類似の質問があり、回答（http://qanda.rakuten.ne.jp/qa6497557.html）を参考にさせていただいたのですが、関数のみで表示され、簡明だったのですが、各々別々のシートに各人を抽出するケースのため、 今回の場合の一つのシートに逐次4列おきに表示しようと、コピーペースト等しても、うまくいきません、また数行下がった位置からスタートするだけで正しい表示がされません。これに使われた関数を何か一工夫する形で、できないでしょうか。 もし難しければ、ＶＢＡで教えていただけないでしょうか。 なお各図の下に示した数式は、上記回答であった数式です。 宜しくお願い致します。

  • Excelの質問です。2つの条件のどちらかを満たすデータを抽出し、別シートにデータを移したいです。

    こんばんは。以前の質問でも、データの抽出に関して教えていただいた者です。皆様の優しさに頼りっきりです。 今回は2つ以上の条件でデータを抽出したいと思ったのですが・・・なかなかスマートなやり方が浮かびませんでして(>_<) 今、手元には『名言.xls』というファイルがあります。 シートには名前をつけていません。(Sheet1,Sheet2・・・)といった感じになっています。 Sheet1のA列とB列には、以下のようなデータが入っています。 A列--------B列 sa-1--------若木に腰掛けな ar-1--------毎日少しずつ。それがなかなかできねんだなあ。（相田みつを） sp-1--------「がんばれ、がんばれ」という人がいるより、僕は、「できないでくれ」という人がいる方が熱くなる。(イチロー) sa-2--------骨肉相食む ar-2--------お前自身を知ろうとするならば、いかに他の人々が行動するかを観察せよ。お前が他の人々を理解しようとするならば、お前自身の心を見よ。(シラー『書き板』) ac1--------仕事は来た順。だってそれが誠意でしょ。(哀川翔) というように、A列には番号、B列には名言のデータが、ズラリと入っています。 sa-(＝ことわざ)、ar-(＝芸術家・思想家)、sp-(＝スポーツ選手)、ac(＝俳優)といった感じです。 この一覧のデータの中から、A列が「sa-*(ワイルドカード)」あるいは「ar-*」という、2つの条件のどちらかを満たした場合、そのセルの隣にあるB列のデータを抽出し、Sheet2のA列に、抽出したデータを並べたいのです。 皆様の英知をお借しください(>_<) よろしくお願いします<m(__)m>

  • Excelで条件に合うデータを別シートに表示したい

    Excel初心者です。 条件を満たすデータを別シートに抽出したく、 色々と調べているのですがよくわからず、困っています。 Excelに詳しい方、ご教示いただけますでしょうか？ どうぞ宜しくお願い致します。 今回困っているのは、顧客管理において、 登録者の情報を入力している「登録者一覧」シートから、 現在も解約することなく利用中の顧客だけを、 同じブックの中の「利用中一覧」シートに表示したいと思っています。 また、「登録者一覧」シートは、 顧客が増える毎に情報を更新しますので、 「登録者一覧」シートの情報が更新されると、 「利用中一覧」シートの情報も同時に 更新されるように出来るととても助かります。 なお、各シートの形式は添付画像のような形です。 説明が下手でわかりづらいかもしれず申し訳ありません。 どうぞ宜しくお願い致します。

  • あるシートから条件に合うデータだけを別シートに抽出させる

    会社で上司に質問されたのですが、不明だったので教えてください。 元リストが入力してあるシート 番号　氏名　フリガナ　会社名　　　部署名　役職　参加費 １　　太郎　タロウ　　○×株式会社　経理　 課長　\1000 ２　　花子　ハナコ　　株式会社△　　 総務　　　　　 \1500 ３　　次郎　ジロウ　　□□株式会社　人事　 課長　\1000 このようなデータが100件以上あります。 この中から、参加日が1000円の人だけを下記のレイアウトで抽出をしたいのです。 抽出先リスト 番号　氏名　フリガナ １　　太郎　　タロウ ３　　次郎　　ジロウ ※元リストは今後増える予定。 ※リストが増えた際の、再抽出はできるだけ自動でやりたい。 ※VBA（マクロ）は極力使いたくない（余計なメッセージが出ると余計混乱するので） フィルタオプションの設定を提案したのですが、「操作ごとに範囲を指定するのが面倒」とのことで却下されてしまいました。 オートフィルタで抽出して、コピペの方法も毎回コピペするのが面倒とのこと。 このわがままな上司を納得させる方法、ご存じないですか？ もし分かる方がいましたら、ご教授下さい。 よろしくお願いします。 使用環境：Excel2000/WinXP

  • 条件に合致するものだけを別シートに表示

    エクセル２００３を使用しています。 Sheet1に日付とともに商品出荷データが入力されています。 Sheet2～Sheet13までの12ヶ月分のシートにsheet1で入力された日付を元に、抽出表示したいと思います。 sheet1 商品コード　商品名　出荷日 A001 ABC 2012/6/10 A089 AZZ 2012/6/19 B006 BAA （出荷されていない物は日付の入力無し） C006 C4587 2012/7/6 Sheet2以降のA1セルには、201206 のように年月を入力しておき、抽出条件とします。 また抽出表示項目は、Sheet１に入力されている全ての情報です。 （商品コードから出荷日まで） このような表示にさせたい場合の、式を教えて頂けないでしょうか？　 どうぞ、よろしくお願いします。

  • 条件一致の別シートから、セルデータ抽出

    添付画像に於いて、予測シートの手配月(F2～F6)のデータを、実績シートから抽出する為のF2～F6に入力する関数をご教授下さい。 条件としては (1)予測シートの手配済？項目が”済”ならば、オーダ番号と一致するデータを実績シートから検索し、そのデータ行から手配月を抽出する。手配済？項目は"済"が入らない場合は必ず”　”となる。 (2)添付画像に於いてはオーダ番号が重複しているが、その場合、実績シートの若番の行のデータを抽出。 (3)予測シートのオーダ番号の並びはランダムであり、昇順降順には成らない。 (4)添付画像ではデータは５行だが、実際は3000～5000行はある。 私の方で、予測シートのF2セルに次の関数を入力しましたが、戻り値は#N/Aとなってしまいました。 =IF(D2="","",LOOKUP(A2,実績シート!A:A,実績シート!D:D)) 以上宜しくお願いします。

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

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

質問する