エクセルにて、別シートから柔軟に未納者を取り出したいです

質問

はじめまして。

エクセルに、このようなシートがあります。
（１月～１２月まで、１２シート）

Ａ ナンバー
Ｂ 氏名
Ｃ ふりがな
Ｄ～Ｈまで使用料金など
Ｉ 受領日

この中から、月ごとの料金未納者一覧を別シートに割り出したいので、
「未納者リスト」というシートを、新たに作成しました。

Ａ ナンバー
Ｂ 氏名
Ｃ ふりがな
Ｄ ４月
Ｅ ５月
Ｆ ６月…

受領日セルには、受領した場合、日付が入っています。
未納の場合は空白です。

仮に、４月が空白（未納）だった場合には、
未納者リストシートの４月に、「未納」であると表示したいです。

ただ、月ごとに、若干、利用者さまが増減しますので、
普通のＶＬＯＯＫＵＰ関数を入れただけでは、
単純にその月に利用していない方まで「未納」になってしまい、うまく引き出せません…。

柔軟に、月ごとの未納者さまをうまく取り出すには、
どうしたらよろしいのでしょうか？

良いお知恵をお持ちの方、
ぜひ、ご教授いただけますと幸いです。

どうかよろしくお願いいたします。

回答No.2

imogasi · ベストアンサー

こういうシートの組み換え、のようなことは、関数では不得意だと思います。
年の途中の新規顧客についても、扱いが難しい。
本来アクセスででも扱って、それも易しく無い課題だと思います。
其れでエクセルVBAでやってみたのですが１点疑問があります。
（１）受領日は本来４月に受け取るべき４月分を５月に受け取った場合受領日はどちらに入力されますか。
下記は、もらうべき月に入力すると仮定します。
＝＝＝
４－６の３月分に簡略化。項目も最小限にした。
例データ　
「４月」シート
氏名 受領日
aaa
bbb 2009/4/1
ccc 2009/4/27
ddd 2009/4/3
eee 2009/4/11
fff
ggg 3009/4/23
ーーーーーーーーーー
「５月」シート
氏名 受領日
aaa 2006/5/14
bbb 2009/5/1
ccc
ddd 2009/5/3
eee 2009/5/11
fff 2009/5/12
kkk
iii 2009/5/12
ーーーー
「６月」シート
氏名 受領日
aaa
bbb 2009/6/1
ccc
ddd 2009/6/3
eee 2009/6/11
fff
hhh 2009/6/11
iii
ーーーーーーーーーー
「集積」シート（途中の表）
aaa ４月
aaa 2006/5/14 ５月
aaa ６月
bbb 2009/4/1 ４月
bbb 2009/5/1 ５月
bbb 2009/6/1 ６月
ccc 2009/4/27 ４月
ccc ５月
ccc ６月
ddd 2009/4/3 ４月
ddd 2009/5/3 ５月
ddd 2009/6/3 ６月
eee 2009/4/11 ４月
eee 2009/5/11 ５月
eee 2009/6/11 ６月
fff ４月
fff 2009/5/12 ５月
fff ６月
ggg 3009/4/23 ４月
hhh 2009/6/11 ６月
iii 2009/5/12 ５月
iii ６月
kkk ５月
６月
ーーー
結果　日付のセルは日付の表示形式を設定のこと。
「月別」シート
- ４月 ５月 ６月

aaa - 2006/5/14
bbb 2009/4/1 2009/5/1 2009/6/1
ccc 2009/4/27
ddd 2009/4/3 2009/5/3 2009/6/3
eee 2009/4/11 2009/5/11 2009/6/11
fff - 2009/5/12
ggg 3009/4/23
hhh - - 2009/6/11
iii - 2009/5/12
kkk
ーは空白セル（左寄せされないように入れたもの）
ーーーー
コード　標準モジュール
Sub test01()
x = Array("４月", "５月", "６月")
k = 2
For Each sh In x
MsgBox sh
d = Sheets(sh).Range("A65536").End(xlUp).Row
MsgBox d
MsgBox k
Sheets(sh).Range("A2:B" & d).Copy Sheets("集積").Range("A" & k)
For i = k To k + d - 1
Sheets("集積").Range("C" & i) = sh
Next i
k = k + d - 1
Next
'----

Sheets("集積").Range("A2:C" & k).Sort Key1:=Sheets("集積").Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin, DataOption1:=xlSortNormal
End Sub
Sub test02()
Dim sh1, sh2
Set sh1 = Sheets("集積")
Set sh2 = Sheets("月別")
d = sh1.Range("A65536").End(xlUp).Row
MsgBox d
k = 2 'ｋは月別の行
j = 1 'ｊは月別の列
m = sh1.Cells(1, "A")
sh2.Cells(k, j) = m
j = sh2.Range("B1:D1").Find(sh1.Cells(2, "C")).Column
sh2.Cells(k, j) = sh1.Cells(1, "B")

For i = 2 To d
If sh1.Cells(i, "A") = m Then '上行と同じ得意先
j = sh2.Range("B1:D1").Find(sh1.Cells(i, "C")).Column

sh2.Cells(k, j) = sh1.Cells(i, "B")
Else '得意先変わった
k = k + 1
j = 1
m = sh1.Cells(i, "A")
sh2.Cells(k, j) = m
j = sh2.Range("B1:D1").Find(sh1.Cells(i, "C")).Column
sh2.Cells(k, j) = sh1.Cells(i, "B")
End If
Next
End Sub
私も時間がかかった。実際の質問者の場合に上記コードを修正できるかな。

お礼

ありがとうございます、
利用料に関して、仰られる通り、４月分に関しては、
５月に料金を受け取ったとしても、４月に入力されます。

ＶＢＡはまったく使ったことがないのですが、
懇切丁寧に考えてくださり、心より感謝の限りです。
本当にありがとうございます。

専門家の方でもお時間がかかったとのこと、
あたたかなお気持ちに感謝いたしますとともに、
そのご労力を、何とか自分の表に反映させたいのですが、
どうしていいのかわかりません…(>_<)。

補足

ありがとうございます、
利用料に関して、仰られる通り、４月分に関しては、
５月に料金を受け取ったとしても、４月に入力されます。

ＶＢＡはまったく使ったことがないのですが、
懇切丁寧に考えてくださり、心より感謝の限りです。
本当にありがとうございます。

専門家の方でもお時間がかかったとのこと、
あたたかなお気持ちに感謝いたしますとともに、
そのご労力を、何とか自分の表に反映させたいのですが、
どうしていいのかわかりません…(>_<)。

回答No.1

KURUMITO

未納者リストシートの1行目は項目名や4月、5月、6月・・・などが入力されているとして、4月のシートはシート名が4月のようになっているとします。
ナンバー、氏名、ふりがなは予め入力されているとします。
Ｄ2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=IF(OR($A2="",D$1="",ISERROR(INDIRECT(D$1&"!A:A"))),"",IF(COUNTIF(INDIRECT(D$1&"!A:A"),$A2)=0,"",IF(INDEX(INDIRECT(D$1&"!I:I"),MATCH($A2,INDIRECT(D$1&"!A:A"),0))<>"","","未納")))

お礼

ありがとうございます、
関数を入れてみましたら、
未納の方は、きれいに未納表示されるようになりました！

ですが、月別で、その月に利用しなかった方は、
利用料を頂いてはいけないのですが、
そういった方に関しても、「未納」になってしまいます…

ここを修正できますと完璧なのですが、
なんとかなりませんでしょうか…？(>_<)

補足

先ほど、お礼に書かせていただきました点ですが、
無事、利用していない方は排除されるのを確認しました。

わたしのミスだったようです、本当にすみません。
ご協力とお力添えに、本当に感謝しています。
無事に表が完成しました。
ありがとうございます。