-PR-

解決済みの質問

質問No.5458043
すぐに回答を!
すぐに回答を!
お気に入り投稿に追加する (0人が追加しました)
回答数2
閲覧数123
エクセルにて、別シートから柔軟に未納者を取り出したいです
はじめまして。

エクセルに、このようなシートがあります。
(1月~12月まで、12シート)

A ナンバー
B 氏名
C ふりがな
D~Hまで使用料金など
I 受領日

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

A ナンバー
B 氏名
C ふりがな
D 4月
E 5月
F 6月…

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

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

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

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

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

どうかよろしくお願いいたします。
投稿日時 - 2009-11-18 17:27:04

質問者が選んだベストアンサー

回答No.2
こういうシートの組み換え、のようなことは、関数では不得意だと思います。
年の途中の新規顧客についても、扱いが難しい。
本来アクセスででも扱って、それも易しく無い課題だと思います。
其れでエクセルVBAでやってみたのですが1点疑問があります。
(1)受領日は本来4月に受け取るべき4月分を5月に受け取った場合受領日はどちらに入力されますか。
下記は、もらうべき月に入力すると仮定します。
===
4-6の3月分に簡略化。項目も最小限にした。
例データ 
「4月」シート
氏名受領日
aaa
bbb2009/4/1
ccc2009/4/27
ddd2009/4/3
eee2009/4/11
fff
ggg3009/4/23
ーーーーーーーーーー
「5月」シート
氏名受領日
aaa2006/5/14
bbb2009/5/1
ccc
ddd2009/5/3
eee2009/5/11
fff2009/5/12
kkk
iii2009/5/12
ーーーー
「6月」シート
氏名受領日
aaa
bbb2009/6/1
ccc
ddd2009/6/3
eee2009/6/11
fff
hhh2009/6/11
iii
ーーーーーーーーーー
「集積」シート(途中の表)
aaa4月
aaa2006/5/145月
aaa6月
bbb2009/4/14月
bbb2009/5/15月
bbb2009/6/16月
ccc2009/4/274月
ccc5月
ccc6月
ddd2009/4/34月
ddd2009/5/35月
ddd2009/6/36月
eee2009/4/114月
eee2009/5/115月
eee2009/6/116月
fff4月
fff2009/5/125月
fff6月
ggg3009/4/234月
hhh2009/6/116月
iii2009/5/125月
iii6月
kkk5月
6月
ーーー
結果 日付のセルは日付の表示形式を設定のこと。
「月別」シート
-4月5月6月

aaa-2006/5/14
bbb2009/4/12009/5/12009/6/1
ccc2009/4/27
ddd2009/4/32009/5/32009/6/3
eee2009/4/112009/5/112009/6/11
fff-2009/5/12
ggg3009/4/23
hhh--2009/6/11
iii-2009/5/12
kkk
ーは空白セル(左寄せされないように入れたもの)
ーーーー
コード 標準モジュール
Sub test01()
x = Array("4月", "5月", "6月")
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 'kは月別の行
j = 1 'jは月別の列
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
私も時間がかかった。実際の質問者の場合に上記コードを修正できるかな。
投稿日時 - 2009-11-18 22:13:07
この回答を支持する
(現在1人が支持しています)
補足
ありがとうございます、
利用料に関して、仰られる通り、4月分に関しては、
5月に料金を受け取ったとしても、4月に入力されます。

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

専門家の方でもお時間がかかったとのこと、
あたたかなお気持ちに感謝いたしますとともに、
そのご労力を、何とか自分の表に反映させたいのですが、
どうしていいのかわかりません…(>_<)。
投稿日時 - 2009-11-20 14:18:31
お礼
ありがとうございます、
利用料に関して、仰られる通り、4月分に関しては、
5月に料金を受け取ったとしても、4月に入力されます。

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

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

ベストアンサー以外の回答 (1)

回答No.1
未納者リストシートの1行目は項目名や4月、5月、6月・・・などが入力されているとして、4月のシートはシート名が4月のようになっているとします。
ナンバー、氏名、ふりがなは予め入力されているとします。
D2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。
=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))<>"","","未納")))
投稿日時 - 2009-11-18 18:18:02
この回答を支持する
(現在1人が支持しています)
補足
先ほど、お礼に書かせていただきました点ですが、
無事、利用していない方は排除されるのを確認しました。

わたしのミスだったようです、本当にすみません。
ご協力とお力添えに、本当に感謝しています。
無事に表が完成しました。
ありがとうございます。
投稿日時 - 2009-11-20 14:30:54
お礼
ありがとうございます、
関数を入れてみましたら、
未納の方は、きれいに未納表示されるようになりました!

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

ここを修正できますと完璧なのですが、
なんとかなりませんでしょうか…?(>_<)
投稿日時 - 2009-11-20 14:01:33
この質問は役に立ちましたか?
0人が「このQ&Aが役に立った」と投票しています
もっと聞いてみる

関連するQ&A

この他の関連するQ&Aをキーワードで探す

回答募集中

同じカテゴリの人気Q&Aランキング

-PR-

特集

背筋がゾクゾクする心霊体験を要チェック!

お城、ボート、ツリーハウス、ユニークな物件満載!

【期間限定】1ヶ月無料キャンペーン中!

このQ&Aの関連キーワード

同じカテゴリの人気Q&Aランキング

-PR-

ピックアップ

-PR-