可視セルで重複セルをカウントしない方法

質問

売上シートのA列のA2から下方向に会社コードが入っています。B列には日付が入っています。同じ月でもA列には同じ会社から注文が入ると同じ会社コードが入ります。
B列の日付ら、月別の集計をフィルター機能を使って特定月を表示させ、A列の会社コードから注文があった会社の個数を求めたいのですが、どうすればよいでしょうか。VBAでも構いません。

A列のA2からA３、A4と売り上げがある度に会社コードが追加されます。会社コードは1001,1002,1003,1004,1005と続き150社ほどあります。B列のB2から下は例として2018/9/27と言う形式の日付が入ります。

よろしくお願いいたします。

回答No.4

kkkkkm · ベストアンサー

C2に
=SUMPRODUCT((A2:$A$1000=A2)*(SUBTOTAL(2,INDIRECT("A"&ROW(A2:$A$1000)))))
として下方向にフィルしてください。仮に1000行としてます。
C1に
=SUMPRODUCT((C2:$C$1000=1)*(SUBTOTAL(2,INDIRECT("A"&ROW(A2:$A$1000)))))
とすればC1に会社の個数がでます。
たぶんこれでいけると思います。

お礼

ご回答いただき、ありがとうございます。関数だけを駆使して、C1にオートフィルの結果、重複をカウントしない会社の件数が見事に出ました。

ありがとございました。こちらの関数を使用させていただきます。

回答No.8

watabe007

#6です
>可視セルで重複セルをカウント
非表示のセルも有るのですね
Sub Test()
　　Dim myDic As Object
　　Dim myMon As Long
　　Dim c As Range

　　myMon = Val(InputBox("特定月を入力して下さい。"))
　　If myMon = 0 Then Exit Sub
　　Set myDic = CreateObject("Scripting.Dictionary")
　　For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible)
　　　　If Month(c.Offset(, 1).Value) = myMon Then
　　　　　　myDic(c.Value) = myDic(c.Value) + 1
　　　　End If
　　Next
　　Range("C1").Value = myDic.Count
End Sub

回答No.7

watabe007

#6です。
>会社コードから重複しないコードの数が同じシートの
>C1かD1のセルに表示されればと考えています。
それぞれの会社コードの数ではなく特定月の会社コードの数なら
Range("C1").Value = myDic.Count

お礼

ご回答いただき、ありがとうございます。マクロを実行した結果、思う通りC1に件数が出ました。

このマクロだと、フィルターをかける必要もなく該当月の件数を数えてくれるので、とても便利です。このシートに2年目の同月が入ると、1年目と2年目の同月を見てカウントするので、1年毎にこのシート情報は更新する必要がありますね。

ありがとうございました。

回答No.6

watabe007

特定月の会社の件数を集計しました。
Sub Test()
　　Dim myDic As Object
　　Dim myMon As Long
　　Dim c As Range

　　myMon = Val(InputBox("特定月を入力して下さい。"))
　　If myMon = 0 Then Exit Sub
　　Set myDic = CreateObject("Scripting.Dictionary")
　　For Each c In Range("A2", Cells(Rows.Count, "A").End(xlUp))
　　　　If Month(c.Offset(, 1).Value) = myMon Then
　　　　　　myDic(c.Value) = myDic(c.Value) + 1
　　　　End If
　　Next
　　Range("C1").Value = myMon & "月"
　　Range("C2").Resize(myDic.Count).Value = Application.Transpose(myDic.Keys)
　　Range("D2").Resize(myDic.Count).Value = Application.Transpose(myDic.Items)
End Sub

回答No.5

imogasi

#2です。
可視セルの判別関数は無いが、可視セルだけカウントする関数SUBTOTALはあったので、＃２の記述を一部訂正します。
VBAで、私の言うロジックで、モデル的にやってみたので参考に。
データ例　私の勝手例
A列とE列
会社名 日付
A 2018/6/12
D 2018/6/3
A 2018/6/4
B 2018/7/1
C 2018/7/2
D 2018/7/3
A 2018/7/4
A 2018/7/5
B 2018/7/6
C 2018/7/12
コード
Sub test01()
Range("K2:L100000").Clear
lr = Range("A100000").End(xlUp).Row
'MsgBox lr
k = 2
For i = 2 To lr
If Month(Cells(i, "E")) <> 7 Then GoTo p1
x = Cells(i, "A")
"MsgBox x
Set y = Range("K2:K" & k).Find(x)
If y Is Nothing Then
Cells(k, "K") = x
Cells(k, "L") = Cells(k, "L") + 1
Else
Cells(y.Row, "L") = Cells(y.Row, "L") + 1
End If
k = k + 1
p1:
Next i
End Sub
ーー
結果
７月分のみカウント
K,L列
会社名 件数
B 2
C 2
D 1
A 2
結果はA列での出現順になる。
結果を望みの順序にするには、ソートキー列がすでにあるか、作るか。
そしてソートのVBA行を最後の行の次に加えればよい。

お礼

ご回答いただき、ありがとうございます。　マクロを検証しようと試したのですが、私の理解不足でうまく動作しませんでした。

もう少し勉強させていただきます。ありがとうございました。

回答No.3

aokii

ピボットテーブルは手動で更新する必要がありますので、更新をクリックしなくてもできるようにするには、フィルター機能で、特定月と会社コードでフィルター設定して、以下の式を任意のセルに入れてみてください。
=SUBTOTAL(102,B2:B7)

回答No.2

imogasi

エクセルの関数での回答を期待しているだろうが、関数では、シートの可視セルは、捉える関数は無いと思う。
従って、VBAでの回答にならざるを得ないと思う。
ロジックは、
簡単なのは、全行繰り返し判別するが、指定月分だけを対象とし、
今までの会社名リスト（既定列に作る）にあれば件数を＋１、なければ、会社名を最終行に新規に記録し（追加）、件数を１にする、繰り返しが、いちばんやさしいロジック。
ーー
こういう問題は、仕事上の問題は、データベース的な扱いをした方がすっきりするので。最低でも、アクセスを使う仕組みにすべきだと思う。エクセルのフィルターのように、シートに見える見えないでなく、SELECTで抜き出して、条件で抜出し、重複は出さない抽出をする。
ーー
他の回答でも示唆されていますが、
エクセルの「ピボットテーブル」で、注文月限定の会社別件数を出す方法を勉強したほうが、ピボットテーブルの応用が広いので、今後のため（会社、質問者の今後のため）とおもう。
こんなことで、解決に何日もかけるのは、会社に損害を与えることでないか。

回答No.1

aokii

月別の集計を行うために、日付を以下の式で月に変換して、
=date(year(B2),month(B2),1）
ピボットテーブルで、会社コード別、月別の個数を集計してみてはいかがでしょう。

お礼

ご回答いただき、ありがとうございます。ピボットテーブルで集計する方法は毎回、日付別集計する都度にピボットテーブルを作成しなおす必要があると思っています。　間違ってたらすみません。

普段使用する人が、単純に日付集計をしたら、会社コードから重複しないコードの数が同じシートのC1かD1のセルに表示されればと考えています。

ありがとうございました。