Excel2007 複数条件での検索

質問

Excel2007で複数条件のデータの抽出について教えて下さい。

現在、シートAにデータが入力され、シートBにデータの抽出を行いたいと思います。

シートAの内容は以下の通りです（アルファベットと数字はセルの場所です）：

1行目：　項目欄
2行目以下：　データ詳細

1行目

A1　「日付」　B1「名前」　C1「出社状況」

2行目以下

A2　12/01　　B2　山田　　C2　出社
A3　12/01　　B3　佐藤　　C3　早退
A4　12/01　　B4　木村　　C4　出社
A5　12/02　　B5　木村　　C5　早退
A6　12/02　　B6　山田　　C6　遅刻
A7　12/02　　B7　佐藤　　C7　遅刻
・・・・・

A列の日付は昇順ですが、B列の人名はランダムに入力されています。
また、日付によっては途中入退社する人もいるので、12/01に名前がなくても、
12/10から名前が入力されている場合（あるいはその逆）もありえます。

シートBは以下の通りです：

1行目　名前
A列：日付

B1　木村　C1　山田　D1　佐藤
・・・

A2　12/01
A3　12/02

このシートBの　B2に　シートAから　「木村の12/01の出社状況」（つまりC4)に値するデータを
自動的に抽出するような関数を入力したいのですが、どのようにすればよいのでしょうか？
（このB2セルの式をB2:D3に入力していきたいので、絶対値を指定することになると思いますが・・・）

IndexやらMatchやらLookupやらを色々試してみたのですが、どうも上手くいきません。
どなたかアドバイスをお願いいたします。

回答No.2

nattocurry · ベストアンサー

一例を。

シートAの名前列と出社状況列の間に作業列を設ける方法です。
作業列には、
セルC2：「=A2&B2」
という式を入力し、下のセルにコピーします。

データが数行あると、その下の行のA列とB列に何かを入力した時点で、C列に自動で計算式が入力されます。

作業列が邪魔なら、非表示にしても大丈夫です。
その場合は、必ず空行が無いように入力する必要がありますが。

シートBには、
セルB2：「=VLOOKUP($A2&B$1,'シートA'!$C:$D,2,FALSE)」
と入力し、他のセルにコピーします。

お礼

nattocurryさん、回答ありがとうございます。
非常にシンプルな式で助かります。
作業列を使用する、というのは目から鱗です。また検査値に&条件を付けることもできるのですね。まだまだ自分の知識は浅いなぁ、と思いました。
幅広く応用できそうな式をご教授下さりありがとうございます。

回答No.6

某HN クロメート(Chromate)（@CoalTar）

#4です
＞最後のfalseの前の「2」はどの列番号に値するのでしょうか？（B列になりますか？）

>>F2セル
=IF($E2="","",VLOOKUP(F$1,
INDEX($B:$B,MATCH($E2,$A:$A,0)):INDEX($C:$C,MATCH($E2,$A:$A))
,2,FALSE))

=vlookup(A1,範囲,列番号,検索の型)
VLOOKUP関数の「範囲」の部分を見ると

INDEX($B:$B,MATCH($E2,$A:$A,0))
でB列のセル参照を返します。
次の
INDEX($C:$C,MATCH($E2,$A:$A))
ではC列のセル参照を返しています
それを「:」でつなげていますので
結果的にセル範囲、B○○:C○○となります。

だから、
＞B列になりますか？
は、セル範囲内で2列目なのでC列になります。
結果を見れば一目瞭然ですね。

名前の数が多いと、無駄な計算が多くなるので作業列(MATCH関数を使った2列)を
作ったほうが良いですね

＞抽出結果が「出社」の場合は空欄、「遅刻」の場合は別数値を入力・・・という様に）
これも作業列というか作業表(中継)にすれば、INDEX,MATCH またはVLOOKUP系の関数で
作成できるかと思います。
=INDEX({"",1,2},MATCH(A1,{"出社","遅刻","早退"},0))

お礼

CoalTarさん、再びレスありがとうございます。
また、詳しく解説して下さり、大変参考になりました。
CoalTarさんのおっしゃる通り、作業列を使用する方が負担が少なそうです。実際に運用する表は、参照データが入力されているシート名だけでも長いので、式を一つで済ませようとすると、式の記述が数式バーからはみ出てしまう程なのです・・・。

回答No.5

MackyNo1

>実際の表はかなり複雑でして、実はこの条件にさらにIf関数を加えた物を使用したいので（抽出結果が「出社」の場合は空欄、「遅刻」の場合は別数値を入力・・・という様に）、そうとう長い式になり、

複雑な条件を返したい場合は、IF関数ではなく配列定数を引数とするVLOOKUP関数を使用した以下のような数式にします。
例えば出社なら空白、遅刻なら１、早退なら２を返すなら以下の式になります。

=VLOOKUP(検索値,{"出社","";"遅刻",1;"早退",2},2,0)

検索値の部分に前回回答した数式を当てはめると以下の式になります。

=IF(SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1)),VLOOKUP(INDEX(Sheet1!$C:$C,MAX(INDEX((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1)*ROW($A$2:$A$100),))),{"出社","";"遅刻",1;"早退",2},2,0),"")

ちなみにSUMPRODUCT関数でデータの有無を判定している部分は、Excel2007以降のバージョンのみ使用するのであれば、以下のようなCOUNTIF関数を使用するほうが計算負荷が少なくなります。

COUNTIFS(Sheet1!$A$2:$A$100,$A2,Sheet1!$B$2:$B$100,B$1)

お礼

MackyNo1さん、再びレスありがとうございます。
またCOUNTIFS関数の記述までして下さり、感謝です。
さて、実際に運用している表にこの式を参照して入力してみたのですが、なぜかVLOOKUP以下でエラー表示になってしまいます。今回サンプルで出した表の場合は、MackyNo1さんに教えて頂いた式で問題なく解決出来たので、当方に問題があるのは明白です。何度もトライしてみたのですがどうも上手くいかず・・・。結果をお伝えしたかったのですが申し訳ありません。
しかし式の記述方法は非常に勉強になりました。ありがとうございます。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

日付が昇順、同じ日に名前が重複する場合は、上の行の出社状況。

F2セル
=IF($E2="","",VLOOKUP(F$1,
INDEX($B:$B,MATCH($E2,$A:$A,0)):INDEX($C:$C,MATCH($E2,$A:$A))
,2,FALSE))
右へ下へオートフィル

お礼

CoalTarさん、回答ありがとうございます。
画像まで添付して下さり、大変見やすいです。
初歩的な質問で大変恐縮なのですが、最後のfalseの前の「2」はどの列番号に値するのでしょうか？（B列になりますか？）

回答No.3

layy

ＶＢＡでもよければサンプルです。
シート２の
B1に"12/01"、C1に"山田"と入れた場合、
シート１から該当行を見つけ出し、
シート２の
A2に"12/01"、B2に"山田"、C2に"出社"、とシート１の内容を表示します。
１行目："検索条件","12/01","山田"
２行目："12/01","山田","出社"

セル位置、範囲行、判定文を考慮すれば対応できますから、
悩むところは少なく、長い文の関数よりは保守できると思います。
必要に応じて手直ししてください。
文の「’」値の右側はコメントになります。

Sub Macro1()
Dim WKGYO As Long
Dim WKOUTGYO As Long
Dim WKRETUA As String
Dim WKRETUB As String
Dim WKRETUC As String
Dim WKJYOKENDATE As String
Dim WKJYOKENNAME As String
Sheets(2).Select
WKJYOKENDATE = Cells(1, 2) 'SHEET2!B1
WKJYOKENNAME = Cells(1, 3) 'SHEET2!B2
WKOUTGYO = 2
Sheets(1).Select
Range("A2").Select
For WKGYO = 2 To 500 ' ２行目から順次下５００行まで
Cells(WKGYO, 1).Select
If Len(Cells(WKGYO, 1)) = 0 Then Exit For 'A列何もないとき終わり
WKRETUA = Cells(WKGYO, 1) 'SHEET1!A列
WKRETUB = Cells(WKGYO, 2) 'SHEET1!B列
WKRETUC = Cells(WKGYO, 3) 'SHEET1!C列
'判定
If WKJYOKENDATE = WKRETUA Then 'SHEET2!B1 = SHEET1!A列 判定？？？
If WKJYOKENNAME = WKRETUB Then 'SHEET2!C1 = SHEET1!B列 判定？？？
'条件にあうものを表示
Sheets(2).Select
Cells(WKOUTGYO, 1) = WKRETUA 'SHEET1!A列 -> SHEET2!A列
Cells(WKOUTGYO, 2) = WKRETUB 'SHEET1!B列 -> SHEET2!B列
Cells(WKOUTGYO, 3) = WKRETUC 'SHEET1!C列 -> SHEET2!C列
WKOUTGYO = WKOUTGYO + 1 'SHEET2 表示行
Sheets(1).Select 'SHEET1 判定行へ制御戻す
Else
End If
Else
End If
Next WKGYO
Owari:
Sheets(2).Select
Range("A1").Select
MsgBox ("終了")
End Sub

お礼

layyさん、回答ありがとうございます。
今回作成しようとしている表を運用する人は私以外にも複数いるので、VBAですと使用者が誤ってセルの式を消してしまう心配が少なくて助かります。
実際に運用する表はサンプルで提示しました表よりも複雑なせいなのか、正直まだ求める値が上手く表示されていないのですが、今一度記述を見直してみます。
長い式の記述なのに工夫して下さりありがとうございました。

回答No.1

MackyNo1

元データがSheet1にあるなら、以下の式をB2セルに入力して右方向および下方向にオートフィルすれば該当データを表示できます。

=IF(SUMPRODUCT((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1)),INDEX(Sheet1!$C:$C,MAX(INDEX((Sheet1!$A$2:$A$100=$A2)*(Sheet1!$B$2:$B$100=B$1)*ROW($A$2:$A$100),))),"")

ただし、多数のセルに上記の数式を入力すると、再計算に時間がかかるのでシートの動きが重くなる可能性がありますので、運用上は再計算を自動にするなどの処理が必要かもしれません。

お礼

MackyNo1さん、回答ありがとうございます。
恥ずかしながら、Sumproductという関数は初めて知りました。こんなに便利な関数があるんですね。
教えて頂いた関数ですが、確かに入力するとかなり長い関数になってしまいました。今回サンプルで載せている例はかなりシンプルなのですが、実際の表はかなり複雑でして、実はこの条件にさらにIf関数を加えた物を使用したいので（抽出結果が「出社」の場合は空欄、「遅刻」の場合は別数値を入力・・・という様に）、そうとう長い式になり、なお且つこの式を実際に運用するのが私以外にもいるので、あまり長くなりすぎるとエラーがあった場合、修正するのが大変になってしまうかも知れないので、関数を別の場所から読み込めるような工夫をしてみます。
しかし非常に勉強になりました。ありがとうございます。