• ベストアンサー
  • すぐに回答を!

excelのデータ抽出?検索?についておしえてください。

excelのデータ抽出?検索?についておしえてください。 エクセル2003で sheet1に次のようなデータがあります。   A列  B列  C列     D列  E列 ・・・ 1 組   番号  氏名     古典  現文 ・・・  2 1   1   青木 優    3 1   2   池田 洋子   4 1   3   植村 美紀   ・ ・ sheet2には1組の成績が sheet3には2組の成績が入っています。 クラスによって科目が違います。 この「氏名」と1行目の「科目名」が一致するデータを 他のシートから読み出してくる関数がありませんでしょうか 青木さんの古典を1組のシートから読み出すといった感じです。 説明がへたで 申し訳ないのですが うまく検索ができなくて困っています。 ぜひ くわしいかた教えてください。 どうぞ よろしくおねがいします。

共感・応援の気持ちを伝えよう!

  • 回答数5
  • 閲覧数196
  • ありがとう数5

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

  • ベストアンサー
  • 回答No.4
  • tom04
  • ベストアンサー率49% (2537/5117)

こんばんは! すでに回答は出ていますので、参考程度で・・・ 当方使用のExcel2003での方法になります。 ↓の画像で説明させていただきます。 Sheet1に数式を入力する前に一手間・・・いや、二手間かけています。 各組のデータは科目が10科目・生徒数が50名まで対応できるようにしてみました。 まず、1組のSheet2のD2~M52セルを範囲指定 → メニュー → 挿入 → 名前 → 定義 から 名前欄に _1 と名前定義します。 (数値が頭に入るとたぶん名前定義できないような気がしたので、とりあえずアンダーバーを入れておきます) 同じく、Sheet2の氏名欄のC2~C52セルを範囲指定し、 名前定義で _1氏名 と名前定義 Sheet2の科目のD1~M1セルを範囲指定し 同様に、_1科目 と名前定義 以上の操作を各組全て名前定義づけしておきます。 最後にSheet1のD2セルに =IF(OR($C2="",ISERROR(INDEX(INDIRECT("_"&$A2),MATCH($C2,INDIRECT("_"&$A2&"氏名"),0),MATCH(D$1,INDIRECT("_"&$A2&"科目"),0)))),"",INDEX(INDIRECT("_"&$A2),MATCH($C2,INDIRECT("_"&$A2&"氏名"),0),MATCH(D$1,INDIRECT("_"&$A2&"科目"),0))) という数式を入れ、オートフィルで列方向と行方向にコピーすると 画像のような感じになります。 以上、長々と書きましたが、 参考になれば幸いです。 的外れなら読み流してくださいね。m(__)m

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます! 画像まで入れていただいて 本当にわかりやすくて助かります。 とっても参考になりました。 まだ出来ていないのでチャレンジしてみます! ありがとうございました。

関連するQ&A

  • データ抽出法

    一度似たような質問をしましたが応用がきかないので、教えてください。 Sheet1A列に入力されたデータ群を含むデータを別sheetから抽出したいのですがやり方がわかりません。 要は、Sheet1A列に10000件のデータが入力されているのですが、それらを含むデータを別シートから検索したいのです。(別シートで10000回検索しない方法が知りたいです。) よろしくお願いします。

  • EXCELでの検索マクロを作りたいのですが

    マクロの初心者です。氏名の検索マクロを作成したいのですが分かりません。 A列:名前 B列:郵便番号 C列:住所 と 名前等のデータを(ランダムに)入力したシート1を作成し、 シート2にシート1の「A列:名前」から性(たとえば青木)で検索して検索ボタン(マクロボタン)を押して該当データを表示させるようにしたいのですが。

  • 重複するデータの抽出

    どなたか教えてください。 Sheet1A列に入力されたデータ群と重複するデータを 別sheetから抽出したいのですがやり方がわかりません。 要は、Sheet1A列に10000件のデータが入力されているのですが、それらと重複するデータを別シートから検索したいです。(別シートで10000回検索しない方法が知りたいです。) よろしくお願いします。

その他の回答 (4)

  • 回答No.5
  • tom04
  • ベストアンサー率49% (2537/5117)

No.4です! たびたびごめんなさい。 前回書き忘れましたが、 名前定義する場合に _(アンダーバー)の後の数値は各組の数値で名前定義してください。 仮に2組なら _2 、 3組なら _3 ・・・のような感じです。 どうも何度も失礼しました。m(__)m

共感・感謝の気持ちを伝えよう!

質問者からのお礼

重ねて御礼申し上げます。 本当に丁寧に教えていただいてありがとうございます。 がんばってみます。

  • 回答No.3

すでにKURUMITOさんからご回答があり解決済みかと思いますが、補足をお願いした立場からの一例です。 sheet1のd2につぎの式を入力し、縦横に必要数ドラッグ =IF(ISNA(INDEX(INDIRECT("Sheet"&($A2+1)&"!$D$2:$M$51"),MATCH($B2,INDIRECT("Sheet"&($A2+1)&"!$B$2:$B$51"),0),MATCH(D$1,INDIRECT("Sheet"&($A2+1)&"!$D$1:$M$1"),0))),"",INDEX(INDIRECT("Sheet"&($A2+1)&"!$D$2:$M$51"),MATCH($B2,INDIRECT("Sheet"&($A2+1)&"!$B$2:$B$51"),0),MATCH(D$1,INDIRECT("Sheet"&($A2+1)&"!$D$1:$M$1"),0))) 科目数10個、各クラス人員50名まで対応していますので、必要により式の数値等を変更してください。 なお。2007であれば下記のようにIFERROR関数で少しスッキリできるのですが・・ =IFERROR(INDEX(INDIRECT("Sheet"&($A2+1)&"!$D$2:$M$51"),MATCH($B2,INDIRECT("Sheet"&($A2+1)&"!$B$2:$B$51"),0),MATCH(J$1,INDIRECT("Sheet"&($A2+1)&"!$D$1:$M$1"),0)),"") なお、関数式は、この画面からEXCELへコピー貼付ができます。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答ありがとうございます。 理解するのに少し時間がかかりそうなので 先にお礼だけかかせていただきました。 2007になるとまた違うのですね。 とりあえず2003で試してみます。 本当に助かりました。 ありがとうございました!

  • 回答No.2
  • KURUMITO
  • ベストアンサー率42% (1835/4283)

例えばシート2やシート3では1行目に項目名があり、A1セルには番号、B1セルには氏名、C1セルから横の列には科目名が並んでいるとします。 そこでシート1ですがお示しの表があるとして、氏名で検索しますと同じ組でも同じ氏名の人がいる場合もありますので、検索にはここでは番号を使うことにします。また、科目についてはX1セルまで使用できることにします。 シート1のD2セルには次の式を入力し、右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。 =IF(OR($B2="",D$1="",COUNTIF(INDIRECT("Sheet"&($A2+1)&"!A:A"),$B2)=0,COUNTIF(INDIRECT("Sheet"&($A2+1)&"!1:1"),D$1)=0),"",INDEX(INDIRECT("Sheet"&($A2+1)&"!A:X"),MATCH($B2,INDIRECT("Sheet"&($A2+1)&"!A:A"),0),MATCH(D$1,INDIRECT("Sheet"&($A2+1)&"!1:1"),0)))

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答ありがとうございます。 理解するのに少し時間がかかりそうなので 先にお礼だけかかせていただきました。 試してみます。 本当に助かります。 ありがとうございました!

  • 回答No.1

補足をお願いします。 sheet2とsheet3のデータは、どの列にどの項目が配列されていますか? 1行目は見出し行で、明細行は2行目以降でしょうか? 読み出した成績は、sheet1のどの列に表示されるのですか?

共感・感謝の気持ちを伝えよう!

質問者からの補足

sheet2には   A列  B列  C列     D列  E列 ・・・ 1 組   番号  氏名     古典  現社 ・・・  2 1   1   青木 優   56   77 3 1   2   池田 洋子  78   66 4 1   3   植村 美紀  80   56 ・ ・ sheet3には   A列  B列  C列     D列  E列  ・・・ 1 組   番号  氏名     現文  世界史 ・・・  2 2   1   佐伯 力   86   77 3 2   2   志垣 洋   58   66 4 2   3   末武 剛   70   86 ・ ・ といった漢字に並んでいます。 クラスによって 教科がまちまちです。 それをsheet1のその名前と科目が交差しているセルに 表示したいのです。 説明がたりなくてすみません。

関連するQ&A

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

    エクセルの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の別シートでの検索と抽出について ExcelファイルでSheet1~5まで部署別の発注データで項目は下記の通りA列~G列まですべて同じになっているものがあります。 A列 B列 C列 D列 E列 F列 G列 注文番号 商品名 納品日 納品先 発注額 消費税 合計額 別シート(Sheet6)に「商品名」や「納品先」で検索する項目を作成し、その結果を表示できるようにしたいのですが何か良い方法はないでしょうか? マクロ・VBAでの作成方法もあればお願いします。 Excelのバージョンは2010です。 並べ替えとフィルター → 詳細設定 → フィルタオプションの設定 で試してみましたが、検索する商品名(納品場所)を変更してしまうと抽出されませんでした。 宜しくお願いします。

  • エクセルで複数シートから、検索して抽出

    はじめまして、右も左も分からない初心者です。 説明出来ないんですが、よろしくお願いします。 【ご質問内容】 エクセルは、XPを使用しています。 エクセルのVBAで、指定したキーワードを 含むデータを抽出したいと思っています。 【例】 Sheet1 A B C D E 1 番号 氏名  郵便番号  住所  メールアドレス 2 1   ○   △    大阪府   ○○ 3 2   △   ○    茨城県   ○○ Sheet2  A  B    C     D      E 1 氏名 番号 郵便番号  住所  メールアドレス 2 ○  1   △    福岡県   ○○ 3 △  3   ○    茨城県   ○○ と各シートに、列がそれぞれバラバラになっています。 茨城県を検索すると、新しいシートに、 Sheet3  A  B    C     D      E 1 氏名 番号 郵便番号  住所  メールアドレス 2 2   △   ○    茨城県   ○○ 3  A  B    C     D      E 4 氏名 番号 郵便番号  住所  メールアドレス 5 △  3   ○    茨城県   ○○ という風に、抽出したいと思っています。 どうしたらいいでしょうか?? よろしくお願いします。

  • Excelで2箇所のデータを1箇所にまとめる方法は

    2箇所にあるデータを1つにまとめるにはExcelの機能で簡単にする方法はありますか? 例えば シート1 A B C D 1 氏名 12月 2 申告 未申 ク 3 A 0 1 1 4 B 0 1 0 5 D 1 0 0 シート2 A B C D E F G 1 氏名 1月 2月 2 申告 未申 ク 申告 未申 ク 3 A 0 1 0 2 0 0 4 C 0 1 0 0 1 0 5 D 0 0 0 0 2 0 これの合計をシート3にまとめるには、Excelのどのような機能でプログラムで簡単にまとめられますか? シート3 A B C D 1 氏名 2 申告 未申告 ク 3 A 4 B 5 C 6 D B列の申告、C列の未申告、D列のクにExcelの機能を使って簡単に合計をまとめるのを作成したいです。シート1と2にはシート1と2に同じ人もいればシート1にしかいない人、シート2にしかいない人がいます。ただ、データは同じです。シート1は12月のデータ、シート2には1月、2月のデータがあります。シート1にはA1は氏名、A3はA,A4はC,A5はD,、B2は申告、B3は0,B4は0,C1は12月、C2は未申告、C3は1,C4は1です。他のシートも同じように見てください。ズレテ表示されてしまっているかもしれません。実際にはシート2には6月までのデータもまとめたいです。宜しくお願いします。Excelの検索機能等を使ってシート3にシート1と2のデータをまとめる事が出来ますか?回答宜しくお願いします。 シート3に計算式を入れます。例えばB3にSUM=(sheet1!,B3)(sheet2!,B3,E3) シート3のB4にSUM=(sheet1!,B4)(sheet2!,B4,E4) B5にSUM=(sheet1!,B5)(sheet2!,B4,E5)を入れる方法だとシート1とシート2に違った人がいる為に下に向かって同じ式をコピーしていくのが出来なくて困っています。上記の式だとコピーしただけだから職場の人にも駄目だと言われています。他のやり方が分からないので誰か教えて下さい。宜しくお願いします。

  • エクセルでのデータのあいまい検索のVBAについて

    VBAかマクロで作成したいと思いますが、あいまい検索ができずに困っております。 どなたかご教授お願いできますでしょうか? 一応画像を添付しておりますが大変見にくく申し訳ないです。  一つのファイルで2枚のワークシートを使用してデータのあいまい検索をしたいと思っております。 画像1の方のワークシートがSheet2になっていてこちらの”I”の列に入力しているデータが画像2になりますが登録商品リストの”G”の列に含まれているかを検索したいと思います。 その結果は”Sheet2”の緑色の部分”I”のセルにデータがあれば”*”をJのセルに表示したいと思います。 登録商品リストには長いもので桁数が30桁近いものもありますがこの桁数を8ケタくらいまでの一致でSheet2のIの列のデータと照合して結果をJに表示できればと思っております。何卒ご教授くださいますようお願いいたします。 照合するデータにつきましては、その日によって件数が違いますので、Sheet2のI列にあるデータが200件くらいの時もあれば2000件くらいの時もございます。 登録商品リストは登録するたびに量が増えていくので照合件数も増えていくことになりますので、どちらも1行指定でできればべすとだとおもっております。

  • エクセルでデータを検索する際に・・・・

    検索したい文字がSheet1にB列にあります。 検索元のデータは Sheet2に5行目より1データにつき横200列ほど項目があるデータが 5000千ほどあります。 検索したい文字は 検索元のデータの3列目(C列)に存在しています。 これを、Sheet1の検索したい文字の横C列から 該当データの1列目より返していきたいのですが VLOOKUPの数式ではテキストで返り、セルに数式が残ってしまうため、 ここを検索元のデータと全く同じ(フォントや数式を含む)データを 抽出し貼付けて返したいのです。 VBAなどでこの検索処理を出来ないでしょうか? 教えてください、よろしくお願いいたしますm(__)m

  • 検索、データ貼り付け

    シート1の G列に 氏名が有り、H列は 金額入力列です。 シート2の B列に 氏名、 E列に 金額が有ります。 シート2の B列 氏名、 E列 金額セルは共にセル結合されているところが有ります。例えば B1、B2 がセル結合なら E1、E2 もセル結合です。 ここで シート1の氏名でシート2の氏名を検索し、合致したら シート2の E列の該当の金額をシート1の該当のH列に貼り付ける記述を教えてください。よろしくお願いします。

  • Excelをデータベースのように使用した場合 データの抽出

    Excelをデータベースのようにして使用している場合について教えて欲しいことがあります。  ・Sheetを2つ作成したとします。  ・Sheet1はたくさんのデータが入っているシートです。  ・Sheet1の名前を仮に『Data』とします。  ・Sheet2はSheet1から必要なデータを抽出してきて並べるシートです。  ・Sheet2の名前を仮に『Report』とします。  ・『Data』のA列には1~200までの数字が入力されています。  ・その数字は ランダムである上に 重複していることもあります。  ・例えばA1には「1」が入力されていて A2には「2」が入力されています。  ・しかし その後A3にもA4にもA5にも「2」が入力されています。  ・同様にA6~A15までは「3」が入力されているのです。  ・このようにA列の数字は 同じ数字が何度も重複しており その重複には規則性はありません。  ・『Report』シート上で『Data』シートからデータを抽出したいのですが A列に「1」と入力されているデータだけを取り出すのなら「VLOOKUP」でもできますよね・・・・  ・しかし A列に「2」と入力されているデータを「VLOOKUP」関数で選んでも その先頭行のデータしか読み込んできません。  ・『Data』シートのA列に入力している数字から 必要な数字が入力されている行のデータだけを 全て『Report』シートに並べようとした場合 手作業で行うとすると・・・ 『Data』でA列が「1」のデータだけを選び,それを『Report』シートに貼り付ける。次は「2」で実行する。・・・・・という作業を200回繰り返すことになりますが 簡単に行うにはどのようにすればいいのでしょうか? マクロについては あまり詳しくないので できれば関数を使いたいのですが 無理でしょうか? マクロを使うのであれば 分かりやすく教えていただきたいのですが・・・ 勝手なお願いで申し訳ありませんが よろしくお願いします。

  • エクセルのデータ検索について(1)

    ご覧いただきありがとうございます。 写真の添付が1枚しか出来ない為、質問を2つに分けて、1枚ずつ添付します。 ◯[Sheet1]に図のようなデータがあった場合なのですが、そのままだと見辛いので、それぞれ別のシートで検索出来るようにしたいなぁと思っております。 【A列】→No. 【B列】→高校名 【C列】→学科 【D列】→郵便番号 【E列】→住所 【F列】から、添付写真のように内容が入力されていて、県ごとに表が分かれています。(表全体を載せたいのですが、画像が粗い為、抜粋しました。分かりづらくてすみません。) ◯[sheet2]では、県名(プルダウンで選択)→高校名(県名に連動して、プルダウンで選択)→内容(H26年度 入塾生・H27年度 入塾生・H28年度入塾生・H27年度 体験授業参加人数…などをプルダウンで選択)→人数(クラス毎)で検索したいです。 (『エクセルのデータ検索について(2)』に添付の上の図) ◯[sheet3]では、内容(プルダウンで選択)→高校名一覧+人数(sheet1の表で、人数が0の場合は表示しない)で検索したいです。 (『エクセルのデータ検索について(2)』に添付の下の図) 色々調べてみて、VLOOKUP・INDEX・MATCH関数を組み合わせると出来そうなのですが、どのように組み合わせて良いか分かりませんでした。 こんな感じの複雑な検索は可能でしょうか? ご回答宜しくお願い致します。

  • VBAにて、複数シートからデータを抽出

    エクセルVBAです。複数のシートからの集計、抽出の書き方について教えてください。 1つのファイルに50ほどのシートがあります。 各シートの列数やフォーマットは、同じですが行数は、異なります。 例 sheet1(シート名:集計):集計用   A    B    C       D     1 ※検索キーワードを入れるセルや 2   マクロを登録するボタン用として2行開けてある。 3 番号 氏名  郵便番号  住所  sheet2(シート名:STU)   A    B    C       D      1 番号 氏名  郵便番号  住所  2  1  AB   345    YZ 3  1  CD   678    QS sheet3(シート名:XYZ)   A    B    C       D      1 番号 氏名  郵便番号  住所  2  2  AB   345    YZ 3  2  CD   678    QS 4  3  CD   678    QZ 抽出前は、上記の様なファイルとなっております。 上記では、4列としてますが実際は、23列あります。 また、sheet3までですが、実際は、40~100シート位あります。 sheet1(シート名:集計):集計用   A    B    C       D     1     ※検索キーワード:氏名_CD としマクロを実行する  2       (↑例として氏名でフィルタリングしてますが他の指定項目でも実行したい、複数条件は、無)   3 番号 氏名  郵便番号  住所 4  1  CD   678    QS    (←sheet2(シート名:STU)の3列目) 5  2  CD   678    QS  (←sheet3(シート名:XYZ)の3列目) 6  3  CD   678    QZ  (←sheet3(シート名:XYZ)の4列目) ・  ・  CD    ・      ・ (←sheet4(シート名:・・・)の・列目) ・  ・  CD    ・      ・ (←sheet10(シート名:・・・)の・列目) ・ ・  CD    ・      ・ (←sheet27(シート名:・・・)の・列目) ・  ・  CD    ・      ・ (←sheet27(シート名:・・・)の・列目) ・ ・  CD    ・      ・ (←sheet30(シート名:・・・)の・列目) 上記の様にすべてのシートから氏名:CDでフィルタリングし集計シートに抽出したい。 よろしくお願いいたします。