【Excel】日別のIDの件数をカウントする方法

質問

初めまして。
日別のID利用件数の管理について、Excelに詳しい方、何卒ご教授くださいませ。

Sheet1のA列に日付、B列には利用のあった回数、C列には利用されたお客様のIDの件数(個数)を入力するセルが用意されています。
Sheet2のA列に日付、B列にはIDを入力するセルがあり、こちらに利用のあった日付とお客様のIDを入力しています。(抽出した別データから1週間分をまとめてコピペする形で入力しています)

このSheet2を参照の元、Sheet1のC列に日別の利用ID件数をカウントするにはどのような式を入力すれば良いでしょうか？
なお、IDは重複しているものは1としてカウントしたく、できれば作業列も作らずにそのままSheet1のC列に式を入力したいです。

添付画像を例にしますと、12月1日は4回利用がありましたが”00001”のIDで2回利用があったため、この日の利用IDの件数は”3”。
というのをSheet1のC列に日別で反映されるようにしたいと思っています。

わかりづらい説明かもしれませんが、調べて色々試しても上手くいかず、とても困っているのでよろしくお願い致します！

回答No.7

bunjii · ベストアンサー

>ちなみにですが、Sheet2のA列とB列の範囲をそれぞれA$2:A$10、B$2:B$10と指定しておりますが、B列全てにする場合は=SUMPRODUCT((Sheet2!$A:$A=A2)*1/COUNTIF(Sheet2!$B:$B,Sheet2!B$:B$))とすれば良いでしょうか？
タイプミスかと思いますが「Sheet2!B$:B$」は誤りで「Sheet2!$B:$B」です。
また、ブランクのセルを検索値にすると0が返されるため0で除算したことによるエラー（#DIV/0!）になります。
Sheet2のB列の値は文字列でしょうか？
下記の数式で試してください。
=IF(A2="","",SUMPRODUCT((Sheet2!A:A=A2)*1/COUNTIF(Sheet2!B:B,Sheet2!B:B&"")))
Sheet1のA列がブランクのセルに対して1が返ることを防止するためにIF関数でA列が空欄（""）のセルに対しては強制的に空欄になるようにします。
尚、データの無いセルに対しても計算対象にすると処理に余分な負荷が掛かりますのでお勧めできません。

お礼

タイプミス失礼致しました。
B列は文字列なので、ご教示いただいた数式を入れたらエラーもなくできました！
本当にありがとうございます！

回答No.6

imogasi

ＶＢＡでの方法なので、質問者には役立たないかもしれないが、ＷＥＢには、この本質問が残ると思うので書いてみます。
背景
コントロールブレーク問題というアルゴリズムを使っている。昔（表計算ソフトが出る前、約４０年前）はこれがよく使われた。
http://www.nurs.or.jp/~sug/soft/super/cbreak.htm
ザ・レトロ・アルゴリズム「コントロールブレーク」by OOP
この古典的な手法を使ってます。
ソートする必要はあるが、データを、１度読みするだけで結果が出る。
ーー
データ例　Ａ、Ｂ例　Ｓｈｅｅｔ
（質問のものを多少増やしてます。）
データは日付＋ＩＤ列で、事前にソートしておくこと
日付 　ID
2017/12/1 1
2017/12/1 1
2017/12/1 2
2017/12/1 3
2017/12/2 4
2017/12/2 4
2017/12/2 5
2017/12/3 5
2017/12/3 6
2017/12/3 6
2017/12/3 7
標準モジュールに
Sub test01()
'--データシートと結果書き出しシート
'--データは日付＋ＩＤで事前にソートしておく
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet3")
sh2.Cells.Clear
'--データ最下行
lr = sh1.Range("A100000").End(xlUp).Row
MsgBox lr
'--初期化
k = 2 'sh2で最初に書き出す行
'--最初のデータ行はSh1の第２行目から
maehiduke = sh1.Cells(2, "A")
maeid = sh1.Cells(2, "B")
kensu = 1
'--sh1の各行について最終行まで繰り返し
For i = 3 To lr
If sh1.Cells(i, "A") = maehiduke And sh1.Cells(i, "B") = maeid Then
'--日付もidも変わらず
kensu = kensu + 1
Else
'--日付か Idが変わった
'--直前までの結果書き出し
sh2.Cells(k, "A") = maehiduke
sh2.Cells(k, "B") = maeid
sh2.Cells(k, "C") = kensu
k = k + 1
maehiduke = sh1.Cells(i, "A")
maeid = sh1.Cells(i, "B")
kensu = 1
'－－
End If
Next i
'---データ終了後のあと仕舞い
sh2.Cells(k, "A") = maehiduke
sh2.Cells(k, "B") = maeid
sh2.Cells(k, "C") = kensu
End Sub
を作る（コピペ）
ーー
結果は
Ｓｈｅｅｔ３のＡ，Ｂ，Ｃ列　日付、ＩＤ，件数
2017/12/1 1 2
2017/12/1 2 1
2017/12/1 3 1
2017/12/2 4 2
2017/12/2 5 1
2017/12/3 5 1
2017/12/3 6 2
2017/12/3 7 1

回答No.5

bunjii

>このSheet2を参照の元、Sheet1のC列に日別の利用ID件数をカウントするにはどのような式を入力すれば良いでしょうか？
SUMPODUCT関数を使うことが最も簡単化と思います。
回答No.3でも良いと思いますがOFFSET関数で集計範囲を決める必要がありませんので以下の数式を提言します。
Sheet1のC2セルに下記の数式を設定し、下へ必要数コピーしてください。
=SUMPRODUCT((Sheet2!A$2:A$10=A2)*1/COUNTIF(Sheet2!B$2:B$10,Sheet2!B$2:B$10))

数式の中で1/COUNTIF(Sheet2!B$2:B$10,Sheet2!B$2:B$10)を乗じているのはCOUNTIF関数で重複となるセルの数を1に集約するためです。

お礼

ありがとうございます！そちらの数式でカウントすることができました！大変助かりました！

ちなみにですが、Sheet2のA列とB列の範囲をそれぞれA$2:A$10、B$2:B$10と指定しておりますが、B列全てにする場合は=SUMPRODUCT((Sheet2!$A:$A=A2)*1/COUNTIF(Sheet2!$B:$B,Sheet2!B$:B$))とすれば良いでしょうか？

回答No.4

tsubu-yuki

「同じ日付で、重複しないＩＤの出現数を取りたい」ってことですね。

小難しい関数式は私は不得意なので、素直に「作業列」を使います。
便宜上（主にＳＳを作るのが面倒だったので）同じシートに纏めていますが、
ご自身の環境に置き換えて考えてみてください。
添付図のＣ列（Sheet2のＣ列）に、作業列を作ります。
ま、そんなに難しく考えずに、
　　Ｃ２セル：=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,1,"")
　　　　　　　以下、フィル。
　　　　　　　※２行目起点で自行までに日付もＩＤも同じものがなければ１
　　　　　　　　あれば空白を返しなさい。と意味。
ＣＯＵＮＴＩＦ関数の複数条件版です。

で、利用回数（当方の図ではＦ列）にはお解りの通り、
　　Ｆ２セル：=COUNTIF(A:A,E2)
　　　　　　　以下、フィル。
ＣＯＵＮＴＩＦ関数を使ってやれば簡単ですね。

問題のＩＤ件数（添付図だとＧ列）。
ココにはＳＵＭＩＦ関数を使います。
　　Ｇ２セル：=SUMIF(A:A,E2,C:C)
　　　　　　　以下、フィル。
えぇ、このために作業列に「１」を立てたわけです。
単純に足してやれば、合計＝ＩＤ件数ですね。

そんなわけで、比較的単純な関数で完結です。
作業列は邪魔にならない位置に作るか、非表示にするか。
それともそのまま見せておくかは、お好みです。

お礼

ありがとうございます！
作業列を作ってカウントする方法も知ることができて勉強になりました！
簡単な数式の組み合わせでわかりやすいです！

回答No.3

msMike

[No.2]の訂正、
何を血迷っていたか、作業用の式をアップして仕舞いました。
下記の式に訂正します。
でも、冗長に過ぎるかも、と思っています。

Sheet1!B2: =IF(A2="","",COUNTIF(Sheet2!A$2:A$10,A2))
Sheet1!C2: =IFERROR(IF(B2,SUMPRODUCT(1/COUNTIF(OFFSET(Sheet2!A$2,MATCH(A2,Sheet2!A$2:A$10,0)-1,1,B2,),OFFSET(Sheet2!A$2,MATCH(A2,Sheet2!A$2:A$10,0)-1,1,B2,))),0),"")

回答No.2

msMike

C2: =SUMPRODUCT(1/COUNTIF(OFFSET(A$11,MATCH(A2,A$11:A$19,0)-1,1,B2,),OFFSET(A$11,MATCH(A2,A$11:A$19,0)-1,1,B2,)))

回答No.1

skp026

複数の条件が設定可能なCOUNTIFS関数でできると思います。
以下はサンプルです。
https://www.becoolusers.com/excel/countifs.html

お礼

COUNTIFS関数は最初に試したのですが、自分だけでは難しかったです！
しかしサンプルをご用意していただき便利になりました！ありがとうございました！