-PR-
  • すぐに回答を!
  • 2009-11-18 17:27:04
  • 質問No.5458043
解決
済み

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

  • 閲覧数160
  • ありがとう数2
  • 気になる数0
  • 回答数2
  • コメント数0
kome73

はじめまして。

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

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

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

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

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

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

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

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

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

どうかよろしくお願いいたします。
  • 回答数2
  • 気になる数0
  • Aみんなの回答(全2件)

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

    • 2009-11-18 22:13:07
    • 回答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
    私も時間がかかった。実際の質問者の場合に上記コードを修正できるかな。
    補足コメント
    ありがとうございます、
    利用料に関して、仰られる通り、4月分に関しては、
    5月に料金を受け取ったとしても、4月に入力されます。

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

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

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

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

    その他の回答 (全1件)

    • 2009-11-18 18:18:02
    • 回答No.1
    未納者リストシートの1行目は項目名や4月、5月、6月・・・などが入力されているとして、4月のシートはシート名が4月のようになっているとします。 ナンバー、氏名、ふりがなは予め入力されているとします。 D2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。 =IF(OR($A2="",D$1="",ISERRO ...続きを読む
    未納者リストシートの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-20 14:30:54
    お礼コメント
    ありがとうございます、
    関数を入れてみましたら、
    未納の方は、きれいに未納表示されるようになりました!

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

    ここを修正できますと完璧なのですが、
    なんとかなりませんでしょうか…?(>_<)
    投稿日時 - 2009-11-20 14:01:33
    • ありがとう数0
    • 回答数2
    • 気になる数0
    • ありがとう数0
    • ありがとう
    • なるほど、役に立ったなど
      感じた思いを「ありがとう」で
      伝えてください
    • 質問する
    • 知りたいこと、悩んでいることを
      投稿してみましょう
    このやり方知ってる!同じこと困ったことある。経験を教えて!
    このQ&Aにはまだコメントがありません。
    あなたの思ったこと、知っていることをここにコメントしてみましょう。

    関連するQ&A

    -PR-
    -PR-

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

    別のキーワードで再検索する
    -PR-
    -PR-
    -PR-

    特集


    成功のポイントとは?M&Aで経営の不安を解決!

    関連するQ&A

    -PR-

    ピックアップ

    -PR-
    ページ先頭へ