エクセルVBAでの条件付きカウントについて

Question

お世話になります。
エクセルＶＢＡでのコード作成について質問です。

以下のデータから条件付きで件数をカウントしたいです。
A　　　　　　B　　C　　　D
20171010　東京　佐藤　10000
20171012　大阪　鈴木　1500
20171017　東京　田中　20000
・
・
・

A列は日付データ、B列は都市名（全部で5種類程度)、C列は個人名（10名ほど）、D列は金額
以上のようなデータです。

これをA列から、その日付がその月の何週目にあたるのかを検出し、その週でC列の人名とB列の都市が一致する場合にその合計を計算し、その一致の件数をカウントしたいと考えています。
実際週検出から個人名、都市名での金額合計は出せるのですが、件数のカウントがうまくいきません。

このように複条件で、キーとなるものも複数あるような場合のカウントのコードはどのように作ればいいでしょうか？ご教授いただけると幸いです。

宜しくお願い致します。

tatsu99 · Accepted Answer

No3です。
同一シートに出力したいとのことですが、とりあえず、
Sheet1を入力シート、Sheet2を出力シートにしたマクロを提示します。
＞複数条件、複数キーでのカウントのコードがわかれば全体の絵は描くことができる状態です。 
ということなので、これを加工して使用してください。
Sheet1,Sheet2が存在しないとエラーになりますのでご注意ください。
以前のSheet2の内容は破棄されますのでご注意ください。
なお、    
Set sh2 = Worksheets("Sheet2")
の箇所を
Set sh2 = Worksheets("Sheet1")
のようにすれば、Sheet1へ出力することも可能です。（以前のSheet1の内容は破棄されますのでご注意ください。）
以下のマクロを標準モジュールへ登録ください。
多分、集計の方法はあなたが想像していたものと、異なるかと思いますが、
このようなケースは、連想配列が有効です。
-----------------------------------------------------
Option Explicit

Public Sub 週単位集計()
    Dim sh1 As Worksheet    '入力シート
    Dim sh2 As Worksheet    '出力シート
    Dim dicT As Object      'キー：週＋都市名＋氏名（10月1週|東京|佐藤）　値：合計金額
    Dim dicC As Object      'キー：週＋都市名＋氏名（10月1週|東京|佐藤）　値：合計件数
    Dim maxrow As Long
    Dim key As Variant
    Dim row As Long
    Dim elm As Variant
    Set sh1 = Worksheets("Sheet1")
    Set sh2 = Worksheets("Sheet2")
    Set dicT = CreateObject("Scripting.Dictionary") ' 連想配列の定義
    Set dicC = CreateObject("Scripting.Dictionary") ' 連想配列の定義
    '最終行取得
    maxrow = sh1.Cells(Rows.Count, 1).End(xlUp).row
    '最終行まで繰り返す
    For row = 1 To maxrow
        key = GetMonthWeek(sh1.Cells(row, "A").Value) & "|" & sh1.Cells(row, "B").Value & "|" & sh1.Cells(row, "C").Value
        If dicT.exists(key) = False Then
            dicT(key) = sh1.Cells(row, "D").Value
            dicC(key) = 1
        Else
            dicT(key) = dicT(key) + sh1.Cells(row, "D").Value
            dicC(key) = dicC(key) + 1
        End If
    Next
    '集計分を出力する
    sh2.Cells.Clear
    row = 1
    For Each key In dicT
        elm = Split(key, "|")
        sh2.Cells(row, "A").Value = elm(0)
        sh2.Cells(row, "B").Value = elm(1)
        sh2.Cells(row, "C").Value = elm(2)
        sh2.Cells(row, "D").Value = dicT(key)
        sh2.Cells(row, "E").Value = dicC(key)
        row = row + 1
    Next
    MsgBox ("完了")
End Sub
'指定日に対する月と週を返す（例 10月1週）
Public Function GetMonthWeek(ByVal date0 As Date) As String
    Dim mm As Long
    mm = Month(date0)
    GetMonthWeek = CStr(mm) & "月" & CStr(GetWeekNumber(date0)) & "週"
End Function
'指定日がその月の第何週かを返す
Private Function GetWeekNumber(ByVal date0 As Date) As Long
    Dim yyyy As Long
    Dim mm As Long
    Dim dd As Long
    Dim date1 As Date
    '1日のシリアル値を取得
    yyyy = Year(date0)
    mm = Month(date0)
    date1 = DateSerial(yyyy, mm, 1)
    GetWeekNumber = WorksheetFunction.WeekNum(date0) - WorksheetFunction.WeekNum(date1) + 1
End Function

WindFaller · Answer

こんばんは。

この質問は、どうしてもVBAなのでしょうか。
私の回答は、VBAが主ですが、見ていると、全体として表にすることだとすると、関数でやってよいのではないかと思っています。
要求しているレイアウトとも違いますので、参考まででも構いません。

添付画像を御覧ください。

①
A列の日付データが、単に数値の場合は、
F列で「日付シリアル値」にして、
=TEXT(A1,"##!/##!/##")*1
G列で、第何週目としています。
=MONTH(F1)&"月"&WEEKNUM(F1)-WEEKNUM(F1-DAY(F1))&"週目"

黄色の部分は、任意で書き換えするなり、横に広げてください。
②
金額　B3から
=SUMIFS(Sheet1!$D$1:$D$10,Sheet1!$G$1:$G$10,$A3,Sheet1!$B$1:$B$10,$B$1,Sheet1!$C$1:$C$10,$C$1)

③
件数 C3から
=COUNTIFS(Sheet1!$G$1:$G$10,$A3,Sheet1!$B$1:$B$10,$B$1,Sheet1!$C$1:$C$10,$C$1)

tatsu99 · Answer

No2です。補足要求の追加です。
日付は、同年のみですか。年が異なり、月が同じケースは考慮しなくてよいのですか。
例えば、
20161010　東京　佐藤　10000
20171010　東京　佐藤　10000
で、仮に両方とも10月の2週だとすると
10月2週　東京　佐藤　20000　２
のようになりますが、それで良いのでしょうか。

tatsu99 · Answer

補足に対して全ての回答をいただいておりません。
１）入力データの1行は、見出しではなく、いきなりデータなのでしょうか。

２）同一シートに結果を書き出すとすると、
入力データの最後の行の次に、結果を書き出すのでしょうか。
通常、このような場合は、入力データの最後の行を事前に取得し、その最後の行まで1行単位の処理を
繰り返すことにより、処理結果を得ることができます。
ところが、入力データの最後の行の次に、結果を書き出してしまった場合、
再度、マクロをリラン（再実行）した時に、結果を書き出した行も、入力データとして扱ってしまします。
その為、同一シートに書き出すなら、列をすこし開けて、H列（10月1週）、I列（東京）、J列（佐藤）、のようにすべきです。

３）入力データが以下の場合
A　　　　　　B　　C　　　D
20171010　東京　佐藤　10000
20171012　大阪　鈴木　1500
20171013　東京　佐藤　5000
20171014　東京　鈴木　1000
20171017　東京　田中　20000
結果は
H　　　　I　　　J　　　K　　　L
10月2週　東京　佐藤　15000　2
10月2週　東京　鈴木　2500　　2
10月3週　東京　田中　20000　１
であってますか。

週の始まりは日曜日とする。
2017/10/1が日曜日なので、
2017/10/1～2017/10/7　　・・1週
2017/10/8～2017/10/14　　・・2週
2017/10/15～2017/10/21　・・3週
となります。

３）入力データの日付が、20171010と提示されていますが、
これは、書式設定でyyyymdと設定してあると解釈して宜しいでしょうか。

４）日付は昇順に並んでいる前提で良いですか。

tatsu99 · Answer

カウントした結果はどのように表示したいのでしょうか。
同じシートへ表示ですか。それとも別シートですか。
入力データに対応した表示例を提示していただけますか。
又、入力データの1行目は見出しですか。それとも、いきなりデータですか。（見出しが無いように見えます）

エクセルVBAでの条件付きカウントについて

No3です。

こんばんは。

No2です。

補足に対して全ての回答をいただいておりません。

カウントした結果はどのように表示したいのでしょうか。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング