ここから質問投稿すると、最大10000ポイント当たる!!!! >>

Excel 関数 一度抽出された値を検索対象から外すには?
Excelの関数について教えて頂きたいことがあります。例えばある検索範囲があり、vlookupか何かで対象の値を引っ張ってきたいのですが、検索値に対象となる値が複数ある場合、一度抽出された値を検索範囲から外し、次のvlookupではそれ以外の対象となる検索値から値を引っ張る、そしてそのまた次のvlookupでは既に抽出された2つの値を対象から外し検索値を探す、そしてそのまた・・・といったような関数の設定は可能でしょうか?
もう少し詳しく状況を説明しますと、日付の"年"と"月"を検索値とし、範囲に該当する値が複数ある時に(仮に検索値を"2010年3月"とし、検索範囲の中に2010年3月1日、2010年3月8日、2010年3月15日を=year()&"/"&month()で表示させた値がある場合)、=vlookup("2010年3月",A:B,2,false)でまず一つ目を抽出し、次のvlookupでの検索の時に一つ目を検索対象から外して再度検索させ、さらに次では一つ目と二つ目を検索対象外として検索させたい、と考えています。

どなたかこのような関数の組み方をご存知の方、教えて頂きたく思います。別にvlookupにはこだわりませんので、近い方法でもいいのでどうぞ宜しくお願いします。

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

A 回答 (5件)

3月のデータがたくさんあるときにVLOOKUP関数を使ってそのようなことをするには複雑な式になってしまうでしょうし、データがたくさんになれば対応ができません。

同じ月のデータがあるときにそれを抽出するためには次のようにすればよいでしょう。
例えばA列の2行目から下方に日付が、B列の2行目から下方には=IF(A2="","",YEAR(A2)&"/"&MONTH(A2))の式で下方にオートフィルドラッグコピーしたものが表示されているとします。また、C列には別の項目のデータが例えば氏名が入力されているとします。
作業列を使って対応するのが簡単です。
例えばE2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",B2&COUNTIF(B$2:B2,B2))

そこで例えば2010年3月のC列にあるデータを抽出したい場合にはVLOOKUP関数の代わりに次のような式を入力して、下方にオートフィルドラッグします。

=IF(COUNTIF($E:$E,"2010/3"&ROW(A1))=0,"",INDEX($C:$C,MATCH("2010/3"&ROW(A1),$E:$E,0)))

これで2010年3月のデータを連続して抽出し、表示させることができます。
    • good
    • 0

こんばんは!


外していたらごめんなさい。

↓の画像でとりあえず二段階で検索条件以外のものを表示させるようにしてみました。

E1セルの「3月」は3/1のシリアル値を入れて、
表示形式をユーザー定義から m"月"としています。

まず、E2セルに
=IF(SUMPRODUCT(($A$2:$A$100<>"")*(MONTH($A$2:$A$100)<>MONTH($E$1)))<ROW(A1),"",INDEX(A$2:A$100,SMALL(IF(MONTH($A$2:$A$100)<>MONTH($E$1),ROW($A$1:$A$99)),ROW(A1))))

これは配列数式になってしまいますので、
この画面からE2セルにコピー&ペーストしただけではエラーになると思います。
貼り付け後、F2キーを押す、又はE2セルをダブルクリック、又は数式バー内で一度クリックします。
編集可能になりますので Shift+Ctrl+Enterキーで確定してください。
数式の前後の{ }マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーします。

同様に第二段階の元データがこの表になりますので
H2セルに
=IF(SUMPRODUCT(($F$2:$F$100<>"")*($F$2:$F$100<>$H$1))<ROW(A1),"",INDEX(E$2:E$100,SMALL(IF($F$2:$F$100<>$H$1,ROW($A$1:$A$99)),ROW(A1))))

これも配列数式ですので、Shift+Ctrl+Enterキーで確定です。
この数式を列方向と行方向にオートフィルでコピーすると
画像のような感じになります。
尚、エラー処理はしていません。数式は最初のデータの100行目まで対応できるようにしていますが
データ量によって範囲指定の領域はアレンジしてみてください。

以上、長々と書きました。
参考になれば良いのですが
的外れなら読み流してくださいね。m(__)m
「Excel 関数 一度抽出された値を検索」の回答画像5
    • good
    • 0

データの検索ではなく、条件にあったデータの抽出(一覧表の作成)といったことでしょうか。


もう少し、補足していただければ別案が出ると思います。
1、データは日付順に並んでいるのでしょうか?
2、同じ日付のデータがいくつもあるのか、日付はひとつづつなのか?
といったところです。
仮に、データが日付順でれば
データシートに(シート名 データ)
 A  B
日付
2000/1/1
・・・
2010/3/1
2010/3/8
2010/3/15
・・・・
2010/4/2 と並んでいるとして
別シートの A1に 2010 と年の数値、B1に 3 と月の数値を入れているとして
表示したいセルに
=INDEX(データ!A:A,MATCH(DATE(A$1,B$1,0.5),データ!A:A)+ROW(A1))
いれて下へコピィしてみてください。
参考ですのでエラー処理や空白の処理はしていません。
まずは式の意味を理解して応用してください。
    • good
    • 0

現在エクセルを操作できない環境なので考え方のみの回答になりますが



1)vlookupを利用する場合、検索範囲を絶対値で指定せずにoffset関数等を利用して検索範囲を狭めていく
・元データーにrow関数を利用して行番号を設置、vlookupで取得した時に行番号も一緒に取得、次の検索範囲は直前に取得した行番号+1行から下を検索範囲と指定

2)オートフィルターを利用して必要データーのみを抽出してコピーして貼り付け

3)マクロを作成
・2の操作をマクロ化するのが簡単です
    • good
    • 0

先ほどはC列だけのデータを表示させる方法ですが例えばC列とD列のデータを表示させるのでしたら、最後の式は次のようになりますね。



=IF(COUNTIF($E:$E,"2010/3"&ROW(A1))=0,"",INDEX($C:$D,MATCH("2010/3"&ROW(A1),$E:$E,0),COLUMN(A1)))

この式をあるセルに入力した後で右横方向に1列だけオートフィルドラッグコピーしたのちに下方にもオートフィルドラッグコピーします。
    • good
    • 0
この回答へのお礼

ご回答どうもありがとうございました。
Countifやindexとmatch関数の組み合わせの勉強になりました。これで作成したかったシートも出来そうです。ありがとうございました。

お礼日時:2010/04/04 00:46

このQ&Aに関連する人気のQ&A

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

この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行までとしていますが、必要に応じて変更して下さい

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【エクセル】1列内に複数ある同項目を、各項目一つずつに絞る方法

お世話になります。

アクセスで各商品コードごとに、「各施設の価格一覧」
集計をとるための下準備(各コード、重複なく一つずつに
絞られたテーブルを作りたいです)として、
エクセルシート内で無秩序に複数ある各商品コードから、
それぞれ一つずつだけ列挙されたシートを作りたいです。

1万行を超えているので、「集計」でまとめても
意味がないだろうし、この場合の対応策について
皆目思い浮かばないのですが、
よろしければアドバイスいただけないでしょうか。

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

Aベストアンサー

こんばんは。

Accessを使うのも結構なのですが、Excelでは、フィルタオプションを使います。

メニューから
[データ]-[フィルタ]-[フィルタオプションの設定]

 指定した範囲(O)
 リスト範囲(L) に範囲を入力
 抽出範囲は、適当に1つのセルを選択し、

 □重複するレコードは無視する(R)にチェック

 OK

で、ユニーク・データが取れます。

これは、ユニーク・データを取るためのExcelのデータベース機能です。

Q【Excel】行に複数回出てくる人を一回だけ別の表に表示したい

A行に1000人分くらいの名前があります。
今は、ソートしてB行に関数

=if(A1=A2,"重複","")

という式を行分コピーして、重複が出たところだけをオートフィルタで
処理して名前を一名ずつ抽出する、という面倒なことをしています。

よく行う処理なので、これを簡素化したいです。

具体的にはシート1にA行に複数の名前(順番には並んでいません)
を打ち込むとシート2にシート1のA行に出てくる名前を上から順番に
一回ずつ登場するような表(一度の抽出で1000を超えることは無いため
1000でシートを作っておこうと思います。)を作りたいです。

マクロの組み方はわかりませんので、関数だけでこの問題を解決
する方法があればお教えください。

Aベストアンサー

非効率な方法ですね。
エクセルでは2つの方法があります。

countif を利用して重複をチェックしオートフィルタで抽出する。

http://plaza.rakuten.co.jp/pasodairy/diary/200411250000/

フィルタオプションを利用して重複を除く。

http://www.k1simplify.com/vba/tipsleaf/leaf13.html

Qエクセル Vlookup関数の入ったセルで関数を削除して値だけを残す方

エクセル Vlookup関数の入ったセルで関数を削除して値だけを残す方法を教えてください。現在はコピーして、値のみをペーストしています。もっと簡単にできないでしょうか。

Aベストアンサー

値化した後、検索値のセル内容が変更された場合どうしますか?
1.検索値の変更はない
2.Vlookupの式を設定するのでしょうか?
3.現在の値のまま変更しない?

1の場合はNo.1の方の回答にありますが、csv形式やtext形式で名前をつけて保存すれば
数式はすべて値化します。
2と3の場合はVBAを組む方法です。検索値のセル内容が変わったときのイベントで
検索値のセルから離れた相対位置のセル内容を変更するVBAを組みます。
2と3の切り分けは相対位置のセル内容が未入力かどうかを判定すれば可能です。

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で別のシートのデータを参照して返す方法

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

作業用のシート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エクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセルで重複しているデータの抽出のしかたを教えてください。

エクセルで重複しているデータを抽出したいのですが・・。なにぶん初心者なもので簡単な方法があればよいのですが・・。
A列に日付 B列に企業名このデータが300件ほどあります。

 Å列      B列
1月 1日    AAAA社
1月 4日   BBBB社
1月 8日   CCCC社
2月 1日   BBBB社
2月20日    DDDD社
3月 2日   AAAA社

と、あるとしてB列の企業名が重複しているものを抽出したいのですが・・・。この場合 AAAA社とBBBB社ですが。
これを簡単に抽出する方法はないでしょうか?
教えてください お願いいたします。

Aベストアンサー

企業名がセルB2からセルB300まで入力されていると仮定します。
セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。すると重複したデータの二番目以降に "*" のマークが付きます。
(注) 数式を下のセルまでコピーするため、COUNTIF($B$2 は絶対参照にして下さい。

QexcelのVLOOKUPで検索値を2つにできますか?

excelのVLOOKUPで検索値を2つにしたいです
私の知っているVLOOKだと下記のことは対応できます
例えば、A列に会社名、B列に住所、C列に電話番号 とあった場合
「住所が検索値と同一なら電話番号を表示しなさい」という指示は出せます

そこで質問です
「会社名と住所が検索値と同一なら電話番号を表示しなさい」
というような、複数の検索値を持つ事はできないのでしょうか?

参考になるURLなどでも結構ですので、ご存知の方よろしくお願いいたします

Aベストアンサー

元の表をA列に会社名、B列に住所、C列に「=A2&B2」、D列に電話番号のようにして
=VLOOKUP(会社名&住所,$D$2:$C$100,2,FALSE)
のようにすれば可能です。


人気Q&Aランキング