エクセルのVLOOKUP関数について

質問

エクセルのVLOOKUP関数を使用しての集計に関する質問です。
以下の例のような場合の関数が分からず本当に困っております。
どなたかご享受いただけたら幸いです。
よろしくお願いいたします。
毎月サポート費としてお客様ごとに定額が振り替えられ、その集計表を作成しております。
その際振替に使用するプランが2パターンあり、その月どちらのプランになるかは月によってまちまちです。
（同じお客様でも3月はパターン1、4月はパターン2だったりします。）
また、振替日は使用するパターンによって異なります。

SHEET1に、
A列 B列 C列 D列
顧客名 金額 振替日 パターン名

という表があり顧客ごとの過去2年分の振替履歴が、1000レコード程並んでいます。

SHEET2でSHEET1の月ごとの集計を出そうと考えております。
表自体は月ごとに分けて作成したいので、表の上部に該当期間をつけます。下記のような感じです。
B2セル C2セル D2セル
○月 開始日 末日
(ex2月) (ex.11/2/1) (ex.11/2/28)

A列：顧客名
B列：パターン名
C列：振替日
D列：金額

と並べ、顧客名ごとに、その月のパターン名・振替日・金額を集計したいです。
金額に関しては、SUMIFS関数、振替日に関しては使用パターンさえ出せれば、IF関数で出せると考えています。
パターン名のところでつまづいてしまっています。

行いたいこととしては、SHEET1のA列からD列（顧客名からパターン名まで）の範囲で、顧客名が一致したときに、振替日を抜き取り、振替日がC2（月初日）より大きく、かつD2（月末日）より小さいときのパターン名を表示させたいです。

(1)----------------------------------------------------

=IF(VLOOKUP(A3,sheet1!$A$2:$D$1000,3,FALSE)>$C$2,IF(VLOOKUP(A3,sheet1!$A$2:$D$1000,3,FALSE)<$D$2,(VLOOKUP(A3,sheet1!$A$2:$D$1000,4,FALSE)),""))

といれると、表示されるきちんとパターン名が表示されるセルと空白が返ってきてしまうセルがあります。

空白が返ってきてしまうセルは、双方の顧客名の最後の1文字を消す、スペースを入れる、など何かしら手を加えるときちんと表示されます。

------------------------------------------------------

その他、VLOOKUP関数、IF関数、ISNA関数などを組み合わせて色々と試してみましたが期間がうまく指定出来ていないようで、当月に振替履歴のない顧客の欄にもパターンが返ってきてしまいます。

下記を教えていただきたいです。
（１）(1)のような状態になる理由と解決法
（２）この場合に使用するのに最適な式

説明が長くなり、またうまく状況を説明できず申し訳ございません。
本当に困っています。ご回答よろしくお願いいたします。

回答No.1

kmetu · ベストアンサー

> =IF(VLOOKUP(A3,sheet1!$A$2:$D$1000,3,FALSE)>$C$2,IF(VLOOKUP(A3,sheet1!$A$2:$D$1000,3,FALSE)<$D$2,(VLOOKUP(A3,sheet1!$A$2:$D$1000,4,FALSE)),""))

SHEET1には顧客ごとのデータがあると言うことで
SHEET2A列のデータを検索してますから
これだと,sheet1!$A$2:$D$1000の一番最初にヒットした行の3列目を見てるということになります

データが以下のようにあるのだとしたら

a社　　　　200　　　　2011/2/2　　　　1
a社　　　　300　　　　2011/3/2　　　　2

この場合、結果として　1　か　2月以外では　FALSE　が返ります。

SHEET2のA3&MONTH(C2)
で
SHEET1のA&MONTH(C)
のデータを検索しなければいけないと思われますので

たとえば
SHEET1のC列の前に作業列を追加して(作業列がC列もとのC列がD列になる)
=A2&MONTH(C2)
として
必要なだけフィル

それで

=IF(AND(VLOOKUP(A3&MONTH($C$2),Sheet1!$C$2:$E$1000,2,FALSE)>$C$2,VLOOKUP(A3&MONTH($C$2),Sheet1!$C$2:$E$1000,2,FALSE)<$D$2),VLOOKUP(A3&MONTH($C$2),Sheet1!$C$2:$E$1000,3,FALSE),"")

という式になると思われます。

お礼

kmetuさん

ご回答ありがとうございました！
教えていただいた通りにやってみたところ、無事集計表が完成しました。
本当に助かりました。

回答No.2

KURUMITO

顧客名や検索する月などが何回も同じ列にある場合には単にVLOOKUP関数で処理することはできません。
問題を処理しやすいようにシート1では作業列を作って対応することにします。
シート1ではA1セルからD1セルまでの項目名が有り、2行目から下の行にそれぞれのデータが入力されているとします。
作業列としてE2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",DATE(YEAR(C2),MONTH(C2),1)+IF(COUNTIF(A$2:A2,A2)=1,ROW(A2)*100000,MATCH(A2,A:A,0)*100000))

ここではそれぞれの顧客については振替日が突き当り１回でそれ以上には無いものとしています。
そこでシート２にお求めの表を作るのですがA２セルには求めようとする月がB２セルにはその月の初日を2011/3/1のように入力するとします。必ずその月の１日を入力します。C２セルにはその月の最終日を入力します。ここではB２セルのデータが大切でA２セルやC２セルは計算に使っていません。
３行目にはA３セルに顧客名、B３セルにはパターン名、C３セルには振替日、D３セルには金額とそれぞれ項目名を入力します。
A４セルから下方には表示したい顧客名を入力します。その上でB４セルには次の式を入力してD４セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(OR(A4="",COLUMN(A1)>3),"",IF(COUNTIF(Sheet1!$A:$A,$A4)=0,"該当者なし",IF(COUNTIF(Sheet1!$E:$E,MATCH($A4,Sheet1!$A:$A,0)*100000+$B$2)=0,"データなし",INDEX(Sheet1!$A:$D,MATCH(MATCH($A4,Sheet1!$A:$A,0)*100000+$B$2,Sheet1!$E:$E,0),IF(COLUMN(A1)=1,4,IF(COLUMN(A1)=2,3,IF(COLUMN(A1)=3,2,"")))))))

最後にC列を選択してセルの書式設定から表示形式を日付から望みの日付にします。

お礼

KURUMITOさん

ご回答ありがとうございました。
先にご回答いただいた方のやり方でやってみたところ先程無事完成しました！
今回は別のやり方になってしまいましたが、わかりやすく解説してくださっているので、これから試しにやってみようと思います。
本当にご親切にありがとうございました。