Excelの検索について

質問

シート1に、
A列 B列 C列 …G列
氏名 番号 年月日1 …年月日2

が入力されています。

B列［番号］が＂3＂のときのみ、それと同じ行のA列［氏名］、C列［年月日1］、G列［年月日2］をシート2に、上から詰めて返す方法を教えてください。
難しく考えずに、VLOOKでいけるのでしょうか。

回答No.4

kagakusuki · ベストアンサー

今仮に「氏名」、「 番号」、「年月日1」、 …「年月日2」等の項目名が入力されているセルは１行目のセルであるものとします。
　又、どの番号のデータのみを抽出する様にするのかは、Sheet2のB2セルに入力されている値で決まるものとします。
　例えば「［番号］が＂3＂となっているデータ」のみを抽出する場合、Sheet2のB2セルに＂3＂と入力すると、Sheet2のB3以下には自動的に＂3＂が表示され、それと共に他の行には「［番号］が＂3＂となっているデータ」が自動的に表示される様にするものとします。

【方法その1】
　SUMPRODUCT関数を使用して、作業列を使わずに関数のみで結果を出す方法です。

　まず、Sheet2のB3セルに次の関数を入力して下さい。

=IF(OR($B$2="",ROWS($2:3)>COUNTIF(Sheet1!$B:$B,$B$2)),"",$B$2)

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(OR($B$2="",ROWS($2:2)>COUNTIF(Sheet1!$B:$B,$B$2)),"",INDEX(Sheet1!$A:$G,SUMPRODUCT(NOT(ISERROR(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9E+307,Sheet1!$C:$C))))*(COUNTIF(OFFSET(Sheet1!$B$1,,,ROW(Sheet1!$B$1:INDEX(Sheet1!$B:$B,MATCH(9E+307,Sheet1!$C:$C)))-ROW(Sheet1!$B$1)+1),$B$2)<ROWS($2:2)))+ROW(Sheet1!$B$1),COLUMNS($A:A)))

　次に、Sheet2のA2セルをコピーして、同シートのA3セル及びC2セル～G3セルに貼り付けて下さい。
　次に、Sheet1のA2～G3のセル範囲をコピーして、Sheet2のA2～G3のセル範囲に"書式のみ"を貼り付けて下さい。
　次に、Sheet2のA3～G3のセル範囲をコピーして、Sheet2のA列～G列の4行目以下に貼り付けて下さい。

※SUMPRODUCT関数はセルに値を入力するだけで自動的に結果が表示されますが、配列演算と同様に繰り返し処理を行う関数であるため、元データの行数があまりにも多過ぎる場合(数千行にもなる場合)には、配列演算と同様に処理が重くなるという短所があります。

【方法その2】
　使用していない適当な列をを作業列として使用して中間処理を行い、その作業列に表示される結果を利用してSheet2に抽出結果を表示させる方法です。
　尚、ここでは仮の話としてSheet3のA列を作業列として使用するものとします。
　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$B:$B,ROW())="","",INDEX(Sheet1!$B:$B,ROW())&CHAR(7)&COUNTIF(Sheet1!$B$1:INDEX(Sheet1!$B:$B,ROW()),INDEX(Sheet1!$B:$B,ROW())))

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$A:$G,MATCH($B$2&CHAR(7)&ROWS($2:2),Sheet3!$A:$A,0),COLUMNS($A:A))<>"")),"",INDEX(Sheet1!$A:$G,MATCH($B$2&CHAR(7)&ROWS($2:2),Sheet3!$A:$A,0),COLUMNS($A:A)))

　次に、Sheet2のA2セルをコピーして、同シートのC2～G2及びA3～G3のセル範囲に貼り付けて下さい。
　次に、Sheet1のA2～G3のセル範囲をコピーして、Sheet2のA2～G3のセル範囲に"書式のみ"を貼り付けて下さい。
　次に、Sheet2のA3～G3のセル範囲をコピーして、Sheet2のA列～G列の4行目以下に貼り付けて下さい。

【方法その3】
　使用していない適当な列をを作業列として使用して中間処理を行い、その作業列に表示される結果を利用してSheet2に抽出結果を表示させる方法という点では【方法その2】と共通ですが、【方法その2】とは異なる手法を用いる方法です。
　尚、ここでは仮の話としてSheet5のA列を作業列として使用するものとします。
　まず、Sheet5のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$B:$B,ROW())="","",MATCH(INDEX(Sheet1!$B:$B,ROW()),Sheet1!$B:$B,0)*10000000+ROW())

　次に、Sheet5のA2セルをコピーして、Sheet5のA3以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNTIF(Sheet1!$B:$B,$B$2),"",IF(ISERROR(1/(INDEX(Sheet1!$A:$G,MOD(SMALL(Sheet5!$A:$A,COUNTIF(Sheet5!$A:$A,"<"&MATCH($B$2,Sheet1!$B:$B,0)*10000000)+ROWS($2:2)),10000000),COLUMNS($A:A))<>"")),"",INDEX(Sheet1!$A:$G,MOD(SMALL(Sheet5!$A:$A,COUNTIF(Sheet5!$A:$A,"<"&MATCH($B$2,Sheet1!$B:$B,0)*10000000)+ROWS($2:2)),10000000),COLUMNS($A:A))))

　次に、Sheet2のA2セルをコピーして、同シートのC2～G2及びA3～G3のセル範囲に貼り付けて下さい。
　次に、Sheet1のA2～G3のセル範囲をコピーして、Sheet2のA2～G3のセル範囲に"書式のみ"を貼り付けて下さい。
　次に、Sheet2のA3～G3のセル範囲をコピーして、Sheet2のA列～G列の4行目以下に貼り付けて下さい。

お礼

非常に分かりやすい回答、ありがとうございました。
大変参考になりました。
恐れながら、ベストアンサーとさせていただきますm(_ _)m

回答No.3

bunjii

>上から詰めて返す方法を教えてください。
>難しく考えずに、VLOOKでいけるのでしょうか。
VLOOKUP関数は検索値が範囲の中で最初に見付かった行位置、または、最後に見付かった近似値の行位置を返します。
従って、検索範囲を順次変更する必要がありますので、その処理の方が複雑になります。
また、VLOOKUP関数はB列を検索範囲にするとA列の値を返せず、B列より右側にA列の複製を置かなければなりません。
INDEX関数とSMALL関数またはLARGE関数の組み合わせで配列演算をする方が分かり易いでしょう。
Sheet2の配置がSheet1と同じと仮定すれば次のようになります。
Sheet2!A2=INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$B$2:$B$1000=3)*ROW(B$2:B$1000)+(Sheet1!$B$2:$B$1000<>3)*1001,0),ROWS(B$2:B2)))
Sheet2!C2とSheet2!G2はSheet2!A2をコピーします。
其々の列は2行目の数式をオートフィルコピーで下へ必要数コピーします。
但し、元データは1000行目までとしました。
Sheet1のB列に3が10個有ったときSheet2の2行目から11行目までは目的の値を取り出せますが12行目には0または関係の無い値が返りますので、それを防ぐにはIF関数で対処してください。

お礼

具体的な回答、大変参考になりました。
ありがとうございましたm(_ _)m

回答No.2

keithin

＞上から詰めて返す方法を教えてください。

初心者さんからよく寄せられるご相談の一種ですが、エクセルには条件（たとえば３）に該当するヤツを「まとめて取ってくる関数」は一つもありません。（Googleスプレッドシートなど、そういう事ができる便利な関数が用意されている表計算も世の中にはあります）

どーしてもエクセルだけでヤリタイ場合、出来る方法は幾つかあります。

方法１）
多くの初心者さんは「とにかく関数なんでも関数」でやりたがりますが、エクセルでは「３に該当するデータを集める」ため、たとえば「オートフィルタ」などのフィルタの機能を利用して、手を動かして結果を出します。

手順：
Ｂ列にオートフィルタを設定する
３で絞り込む

●ほとんどの場合、３で絞り込んだ状態で「３を一覧する」目的は達成できます
●何らかの理由で別シートに書き出さなきゃならない場合、エクセルでは「３で絞り込んだ後コピーして貼り付ける」のが一番簡単に「結果を得られる」方法です
●他に、あなたがご利用のエクセルのバージョンも不明のご相談ですが、バージョンに応じて「フィルタの詳細設定」あるいは「フィルタオプションの設定」などの機能で、シート２に絞り込んだ結果を書き出させる事もできます

方法２）
それでも関数でやりたければ、前述したようにエクセルの関数は「１つの計算結果」しか出すことができません。
なので「３の一覧」を関数で得るためには、
最初の３には「３－１」
２番目の３には「３－２」
３番目のには「３－３」
などのように、３の子番号を付けた一行ずつ違う検索値（一意の検索値と言います）を関数を利用して配置、それを手掛かりにVLOOKUPするような事もできます。

計算例：
Ｂ２以下に１や２や３が並んでいる
Ａ２に
=IF(B2="","",B2&TEXT(COUNTIF($B$2:B2,B2),"-000")
と記入、以下コピー

これで３－００１から始まる一意の検索値が計算できたので、シート２では３－００１や３－００２をそれぞれ検索値としてVLOOKUP関数を使い、所定の列のデータを参照する事ができるようになります。

お礼

早速の回答、ありがとうございました。大変分かりやすかったです。

回答No.1

msMike

》 難しく考えずに、VLOOKで…
VLOOKって何？