こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

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

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

投稿日時 - 2018-07-08 09:01:04

QNo.9516301

困ってます

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

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に会社の個数がでます。
たぶんこれでいけると思います。

投稿日時 - 2018-07-08 11:40:32

お礼

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

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

投稿日時 - 2018-07-08 15:57:11

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(8)

ANo.8

#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

投稿日時 - 2018-07-08 15:17:59

ANo.7

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

投稿日時 - 2018-07-08 15:05:37

お礼

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

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

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

投稿日時 - 2018-07-08 15:51:54

ANo.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))
    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

投稿日時 - 2018-07-08 14:17:34

ANo.5

#2です。
可視セルの判別関数は無いが、可視セルだけカウントする関数SUBTOTALはあったので、#2の記述を一部訂正します。
VBAで、私の言うロジックで、モデル的にやってみたので参考に。
データ例 私の勝手例
A列とE列
会社名日付
A2018/6/12
D2018/6/3
A2018/6/4
B2018/7/1
C2018/7/2
D2018/7/3
A2018/7/4
A2018/7/5
B2018/7/6
C2018/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
ーー
結果
7月分のみカウント
K,L列
会社名件数
B2
C2
D1
A2
結果はA列での出現順になる。
結果を望みの順序にするには、ソートキー列がすでにあるか、作るか。
そしてソートのVBA行を最後の行の次に加えればよい。

投稿日時 - 2018-07-08 11:42:27

お礼

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

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

投稿日時 - 2018-07-08 16:05:10

ANo.3

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

投稿日時 - 2018-07-08 11:11:20

ANo.2

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

投稿日時 - 2018-07-08 10:44:32

ANo.1

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

投稿日時 - 2018-07-08 09:12:07

お礼

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

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

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

投稿日時 - 2018-07-08 10:31:38