はじめての親子ハイキングに挑戦!! >>

エクセルの表である条件にあてはまる行を
別シートに順番に抽出されるような関数を教えてください。


シート1のB列にあるデータが"渋谷区"のとき
シート2のE74からE80くらいまでに
シート1のC列のデータを上から順に抽出されるようにしたいです。

【シート1】
A        B         C
1 東京都 渋谷区  PC
2東京都  港区  PC
3東京都 新宿区  マウス
4東京都 渋谷区  ペン
5東京都 渋谷区  モニタ



10

【シート2】

E
74 PC
75 ペン
76 モニタ



80 


【補足】
元の表(シート1)を編集することがあるので
自動的に反映されるよう、関数で作成できたらと思います。
シート2の構成上、抽出後のソートや、フィルタオプションは避けたいです。

わかりづらい文章で申し訳ございませんが
どなたかいいアドバイスがありましたら教えてください。
お礼は明日になってしまいますが、必ずご返答いたします。
よろしくお願いいたします。

 

このQ&Aに関連する最新のQ&A

A 回答 (6件)

>E74に関数を入力し、その後関数をE80までドラッグすればよいのでしょうか?


いいえ。
1.
=INDEX(シート1!C1:C11,SMALL(IF(シート1!B1:B10="渋谷区",ROW(A1:A10),11),ROW(A1:A10)))&""
の数式を掲示板上でコピーします(シート名は合わせてください)
2. E74:E80セル範囲を選択します
3. 数式バーで[Ctrl]+[V]貼り付け
4. [Ctrl]+[Shift] +[Enter] でセルへの入力を確定させます

>その際、絶対参照や相対参照は必要でしょうか?
セルのコピーはしませんので不要です。
参考まで
    • good
    • 13
この回答へのお礼

ご連絡おそくなりまして
申し訳ございません!

なんとかできました!ありがとうございました。
助かりました!

お礼日時:2009/04/04 21:21

[回答番号:No.1この回答へのお礼]に対するコメント、



1.例えば Sheet2 のセル H73 に 渋谷区 を入力して、次式を入力し
  たセル H74 を下方にズズーッとドラッグ&ペースト
  =IF(Sheet1!B1=H$73,ROW(A1),"")
2.同シートのセル E74 に次式を入力して、此れを下方にズズーッと
  ドラッグ&ペースト
  =IF(ISERROR(SMALL(H$74:H$99,ROW(A1))),"",INDEX(Sheet1!C:C,SMALL(H$74:H$99,ROW(A1))))
    • good
    • 6
この回答へのお礼

わざわざすみません。
やっと、理解できました。

本当にありがとうございました。

お礼日時:2009/04/04 21:22

配列数式を提示しても意味がわからないと、また、質問することになるんだろうなあ と思いつつ、軽量と思われる配列数式



シート1のC11セルは未入力とする
シート2のE74:E80セルを選ぶ
=INDEX(シート1!C1:C11,SMALL(IF(シート1!B1:B10="渋谷区",ROW(A1:A10),11),ROW(A1:A10)))&""
と入力後、[Ctrl]+[Shift] +[Enter] で確定({}で囲まれる)
参考まで
「【エクセル】表から条件に合ったデータを別」の回答画像4
    • good
    • 7
この回答へのお礼

ご回答ありがとうございます。
画面まで貼り付けていただき、わかりやすくて助かります。

ご指摘のとおり、配列数式については知識がなく
ちょっとわからないので
再度質問させてください。
>シート2のE74:E80セルを選ぶ
とありますが
E74に関数を入力し、その後関数をE80までドラッグすればよいのでしょうか?
その際、絶対参照や相対参照は必要でしょうか?

よろしくお願いいたします。

お礼日時:2009/04/01 14:23

No.2です。


書き忘れましたけど、配列数式なので、
式の確定時はCtrl +Shift + Enter をお忘れなく。
    • good
    • 11

こんにちは。



もう少し短い数式でもできるかもしれませんが、
一例ってことで・・・。

Sheet2のE74に

{=IF(ISERROR(INDEX(Sheet1!$A$1:$C$5,SMALL(IF((Sheet1!$B$1:$B$5)="渋谷区",ROW(Sheet1!$C$1:$C$5),""),ROW()-73),3)),"",INDEX(Sheet1!$A$1:$C$5,SMALL(IF((Sheet1!$B$1:$B$5)="渋谷区",ROW(Sheet1!$C$1:$C$5),""),ROW()-73),3))}

として、下にズリズリ~っとコピーして下さい。

Sheet1のリスト範囲はA1:C5としています。実情に合わせて変更して下さい。
数式中の ROW()-73 は74行目から始まる場合です。
    • good
    • 3
この回答へのお礼

ご回答ありがとうございます!
こんなに難しそうな関数をわざわざ作成していただき
感謝してます。
ちょっと内容を理解するのに時間がかかりそうなので
取り急ぎ、お礼をさせていただきます。

お礼日時:2009/04/01 14:25

「シート2」の何処かの空き列を1列だけ(作業列として)使ってもOKですか?

    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
作業列を作成してもOKです!
アドバイス、よろしくお願いいたします。

お礼日時:2009/04/01 14:18

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Qエクセルで複数の条件を抽出し自動的に別シートに反映する方法を教えてください。

こんばんは。教えてください。

元データ(sheet1)を更新するたび
2つの条件にあったデータを
別シートに自動的に抽出したいので
適した関数がありましたら教えてください。



◇シート1◇ ※元データ
 A      B     C     D     F
1 氏名 役職 部署 年齢 移動時期     
2 山田 社員 開発 45  未調整
3 田中 社員 人事 42  4/1~
4 鈴木 派遣 企画 30  
5 高橋 役員 人事 50  未調整
6 坂野 社員 企画 33  未調整  
7 井上 派遣 企画 29  未調整

◇シート2◇ ※部署が人事で移動時期が未調整の人のみ抽出
 A      B     C     D    F
1 氏名 役職 部署 年齢 移動時期 
2 高橋 役員 人事 50  未調整


補足
・元データ(シート1)はこれからもデータを追加するので
 その度、シート2、シート3もそれぞれ自動で反映されるようにした いです。

・マクロ、VBAは知識がないので
 関数で作成したいです。

・できるだけエラー(#N/Aなど)表示されないようにしたいです。

説明が不十分で伝わりにくいかと思いますが
よろしくお願いします。

こんばんは。教えてください。

元データ(sheet1)を更新するたび
2つの条件にあったデータを
別シートに自動的に抽出したいので
適した関数がありましたら教えてください。



◇シート1◇ ※元データ
 A      B     C     D     F
1 氏名 役職 部署 年齢 移動時期     
2 山田 社員 開発 45  未調整
3 田中 社員 人事 42  4/1~
4 鈴木 派遣 企画 30  
5 高橋 役員 人事 50  未調整
6 坂野 社員 企画 33  未調整  
7 ...続きを読む

Aベストアンサー

> 部署を"人事"と"企画"の両方で抽出する条件を追加する

おはようございます、merlionXXです。
Operator:=xlOr, Criteria2:="=企画" と、OR条件で"企画"を加えれば可能です。
一応、各コードの動きに解説もつけておきました。(解説は’でコメントアウトしてますのでこのまま貼り付けてもOKです。)

Private Sub Worksheet_Activate() 'このシートがアクティブになったら自動実行
With Sheets("Sheet1") 'Sheet1対して
.AutoFilterMode = False 'オートフィルタ終了(あればエラーになるため)
.Range("A1:E1").AutoFilter 'A1:E1にオートフィルタ設定
.Range("A1:E1").AutoFilter Field:=3, Criteria1:="=人事", Operator:=xlOr, Criteria2:="=企画" '左から3つ目のフィルタを"人事"と"企画"のOR条件で抽出
.Range("A1:E1").AutoFilter Field:=5, Criteria1:="未調整" '左から5つ目のフィルタを"未調整"で抽出
.Range(.Range("A1"), .Range("A1").SpecialCells(xlLastCell)).SpecialCells(xlCellTypeVisible).Copy Range("A1") 'A1から最終セル範囲の可視セルをこのシートのA1以降に貼り付け
.AutoFilterMode = False 'オートフィルタ終了
End With 'Sheet1に対する記述おわり
End Sub

> 部署を"人事"と"企画"の両方で抽出する条件を追加する

おはようございます、merlionXXです。
Operator:=xlOr, Criteria2:="=企画" と、OR条件で"企画"を加えれば可能です。
一応、各コードの動きに解説もつけておきました。(解説は’でコメントアウトしてますのでこのまま貼り付けてもOKです。)

Private Sub Worksheet_Activate() 'このシートがアクティブになったら自動実行
With Sheets("Sheet1") 'Sheet1対して
.AutoFilterMode = False 'オートフィルタ終了(あればエラーになるた...続きを読む

Qエクセルで条件に合うものを別シートに抽出(関数)

希望日    りんご   ばなな   みかん   担当   配達チェック
6月1日      2             1     山田
6月3日             4      2     佐藤      
6月1日      3                   山田      済
6月2日             1            佐藤 
6月2日                    2     山田

というデータがsheet1に入力されています。
<条件>
 ・担当 山田のもの
 ・6月2日以前(このデータでは1日と2日)
 ・配達チェックが済でないもの(空白です)

この条件を満たすものをsheet2に抽出したいのです。
希望日    りんご   ばなな   みかん   担当   配達チェック
6月1日      2              1     山田
6月2日                    2     山田
とsheet2に表示させるには関数でできますか?
できれば、セル参照でA1セルに6月2日、B1に山田と入力すると抽出されるととても便利なのですが・・・。
皆様のお知恵を拝借させてください!!

希望日    りんご   ばなな   みかん   担当   配達チェック
6月1日      2             1     山田
6月3日             4      2     佐藤      
6月1日      3                   山田      済
6月2日             1            佐藤 
6月2日                    2     山田

というデータがsheet1に入力されています。
<条件>
 ・担当 山田の...続きを読む

Aベストアンサー

こんばんは!
一例です。

↓の画像でSheet1に作業用の列を設けています。
作業列G2セルに
=IF(COUNTBLANK(Sheet2!$A$1:$B$1),"",IF(AND(A2<=Sheet2!$A$1,E2=Sheet2!$B$1,F2=""),ROW(),""))
という数式を入れオートフィルでずぃ~~~!っと下へコピーしておきます。

そしてSheet2のA4セルに
=IF(COUNT(Sheet1!$G:$G)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1))))
という数式を入れ列方向と行方向にオートフィルでコピー!

A列の表示形式は「日付」にしておきます。
ただ、このままではSheet1に空白セルがあれば「0」が表示されてしまいますので、
当方使用のExcel2003の場合ですが、
メニュー → ツール → オプション → 「表示」タブ → 「ゼロ値」のチェックを外すと
画像のような感じになります。

以上、参考になれば良いのですが・・・m(__)m

Q【エクセル】表から条件にあったデータを別のシートに抽出する関数

エクセルの表である条件にあてはまる行(複数の列を指定したい)を、
別シートに順番に抽出されるような関数を教えてください。
元の表を編集するので、自動的に反映されるように関数で作成したいと思っています。できれば、抽出後のソートやフィルタオプションは避けたいです。

シート1のコード(A列)が100番台のもの、200番台のもの、300番台のものの、それぞれ食品名(B列)と総量(D列)を、シート2~シート4に別表として抽出したいです。
食材の注文をするのに、業者別にしなければいけませんが、量が多くて毎回大変なんです。お助けください。

【シート1】 元データ
   A     B        C       D
1 コード  食品名    一人分分量   総量
2 101    牛肉      30      150
3 201    じゃが芋    80      400
4 202    玉葱      40      200
5 203    人参      20      100
6 301    しょうゆ    6       30
7 302    砂糖      4       20

【シート2】 コード100番台
  A     B  
1 牛肉   150

【シート3】 コード200番台
  A     B      
1 じゃが芋 400
2 玉葱   200
3 人参   100

【シート4】 コード300番台
  A     B 
1 しょうゆ 30
2 砂糖   20

無謀な事をやろうとしているのかもしれませんが、どなたかアドバイスをください。
よろしくお願い致します。

エクセルの表である条件にあてはまる行(複数の列を指定したい)を、
別シートに順番に抽出されるような関数を教えてください。
元の表を編集するので、自動的に反映されるように関数で作成したいと思っています。できれば、抽出後のソートやフィルタオプションは避けたいです。

シート1のコード(A列)が100番台のもの、200番台のもの、300番台のものの、それぞれ食品名(B列)と総量(D列)を、シート2~シート4に別表として抽出したいです。
食材の注文をするのに、業者別にしなければいけませんが、量が多くて毎...続きを読む

Aベストアンサー

>恥ずかしながら、ピボットテーブルを初めて知りました。
とても面白くて便利ですね。すぐにできました。
関数とどちらが自分の仕事に合うのかやってみます。

ピボットテーブルはうまく使いこなすと非常に便利な機能です。

今回の回答では、簡潔に回答するため、操作上の細かい注意点やノウハウなどを書きませんでしたが、今回のケースでピボットテーブルを利用されるなら、たとえばグループ化したコードのフィールドをページフィールド(A1セルの部分)にドラッグすれば、その部分のドロップダウンリストから該当データだけを表示させることができます。
また、予備のデータ範囲を広くとるため、ピボットテーブルのA列に「0」のダミーデータを入れる方法を提示しましたが、名前定義を利用して範囲に名前をつけ、データ数に応じて自動的にピボットテーブルの範囲を広げる設定にするほうがお勧めです。

http://www.officetanaka.net/excel/function/function/offset.htm
http://www.becoolusers.com/excel/pivottable-table.html

>恥ずかしながら、ピボットテーブルを初めて知りました。
とても面白くて便利ですね。すぐにできました。
関数とどちらが自分の仕事に合うのかやってみます。

ピボットテーブルはうまく使いこなすと非常に便利な機能です。

今回の回答では、簡潔に回答するため、操作上の細かい注意点やノウハウなどを書きませんでしたが、今回のケースでピボットテーブルを利用されるなら、たとえばグループ化したコードのフィールドをページフィールド(A1セルの部分)にドラッグすれば、その部分のドロップダウンリスト...続きを読む

QEXCELで別のシートのデータを参照して返す方法

ちょっとしたことなのですが、うまく式が書けなくて悩んでいます。

作業用のシート1と参照用のシート2があり、
シート1のA列には
 あおき
 あべ
 いぐち
 いはら
などと文字列(名前)が入力されています。
こちらは今度の野球のスタメンだと思ってください。

シート2にはA列とB列があって、
わたなべ 55
いぐち  43
あおやま 67
いはら  41
などと、名前:背番号が羅列されています。こちらが参照用の全選手の背番号リストだと思ってください。

ここで、 シート1のB列に、A列の名前に対応した背番号を返したいのです。
なので、シート1のB列に
IF(シート1のA列の値=シート2のA列のいずれかの値)だったとき、
適合したシート2の行のB列の値を表示する
という式を入れたいのですが、どのように式を書けばよいでしょうか。

すみませんがどなたか教えてください。

Aベストアンサー

私もその場合はVLOOKUP関数を使用しています。

「B1」=VLOOKUP(A1,Sheet2!$A:$B,2,0)

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qエクセルで特定の行を抽出させる方法について。

エクセルでの関数についての質問ですが、

     A     B    C     D  
 1  名前   日付  場所   時間
 2  Aさん  10/7  工場1   8.0
 3  Bさん  10/7  工場1   8.0
 4  Cさん  10/7  工場2   4.0
 5  Cさん  10/7  工場1   8.0
 6  Aさん  10/8  事務所   8.0
 7  Cさん  10/8  工場2   8.0
 8  Aさん  10/9  事務所   4.0

以上の表から別のシート2にAさんだけの行を抽出するには、どのようにすれば良いのでしょうか?
INDEXやMATCH・LOOKUPを使っていろいろ試したのですがイマイチうまく行かないもので・・・。

因みに、シート2のイメージは

      A     B     C  
 1   名前  :  Aさん 
 2
 3   日付   場所   時間
 4   10/7   工場1    8.0
 5   10/8   事務所    8.0
 6   10/9   事務所   4.0

このような感じです。

よろしくお願いします。

エクセルでの関数についての質問ですが、

     A     B    C     D  
 1  名前   日付  場所   時間
 2  Aさん  10/7  工場1   8.0
 3  Bさん  10/7  工場1   8.0
 4  Cさん  10/7  工場2   4.0
 5  Cさん  10/7  工場1   8.0
 6  Aさん  10/8  事務所   8.0
 7  Cさん  10/8  工場2   8.0
 8  Aさん  10/9  事務所   4.0

以上の表から別のシート2にAさんだけの行を抽出するには、どのように...続きを読む

Aベストアンサー

こんにちは!

極端にデータ量が多い場合はおススメできませんが・・・
一気に表示する方法です。

↓の画像で右側がSheet2とします。
Excel2007以降のバージョンをお使いだとして・・・

Sheet2のA4セルに
=IF($B$1="","",IFERROR(INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(A$1:A$1000)),ROW(A1))),""))
これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定!
この画面から数式をコピー&ペーストする場合は
上記数式をドラッグ&コピー → Sheet2のA4セルを選択 → 数式バー内に貼り付け
→ そのまま(編集可能なまま)Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
これをC4セルまでコピー!

A4セルの表示形式は「日付」にし、A4~C4セルを範囲指定 → C4セルのフィルハンドルで下へコピー!

これで画像のような感じになります。m(_ _)m꜊

こんにちは!

極端にデータ量が多い場合はおススメできませんが・・・
一気に表示する方法です。

↓の画像で右側がSheet2とします。
Excel2007以降のバージョンをお使いだとして・・・

Sheet2のA4セルに
=IF($B$1="","",IFERROR(INDEX(Sheet1!B$1:B$1000,SMALL(IF(Sheet1!$A$1:$A$1000=$B$1,ROW(A$1:A$1000)),ROW(A1))),""))
これは配列数式になってしまいますので、Shift+Ctrl+Enterで確定!
この画面から数式をコピー&ペーストする場合は
上記数式をドラッグ&コピー → Sheet2のA4セルを選択 → 数式バ...続きを読む

QExcelですが、同一データが複数あるとき、検索して、その全部を抽出する方法

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお願いします。

<データシート>
 列A  列B    列C 列C
行1 No 会社名   所属 担当者
行2 1 A株式会社  ○事業部  坂下順人
行3 2 A株式会社  △事業部  滿山友人
行4 3 A株式会社  △事業部  目標達子
行5 4 B株式会社  設計部山下清人
行6 5 B株式会社  営業部横浜美人
行7 6 C株式会社  営業部川崎次郎
行8・・・・・・・・・・・・

<検索結果:別のシートにおいて>
行1 会社名 所属  担当者
行2 A株式会社 ○事業部 坂下順人
行3 A株式会社 △事業部 滿山友人
行4 A株式会社 △事業部 目標達子

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお...続きを読む

Aベストアンサー

#03です
>最後のROW(T1)で、T1を参照しておりますが

ROW(T1)は「1番目」を意味しています。T1セルに何もなくても関係ありません。コピーしたの行はROW(T2)になりますが、これは2番目のという意味です

#REF!エラーになりますか?
私が示した条件と何か変えていませんか?
別シートのA1に「抽出する会社名」を入力してあれば、回答したような結果になるはずですが‥ (テストしてありますので)

ただし2000行もあるならVBAの方が良いかもしれません
きっと「シートが重くなる」と思います

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング