【Excel】日別のIDの件数をカウントする方法
- Excelで日別の利用IDの件数をカウントする方法を教えてください。
- Sheet1のA列には日付、B列には利用のあった回数、C列には利用されたお客様のIDの件数を入力するセルがあります。Sheet2のA列には日付、B列にはIDを入力するセルがあり、こちらに利用のあった日付とお客様のIDを入力しています。
- Sheet2を参照の元、Sheet1のC列に日別の利用ID件数をカウントするための式を教えてください。IDは重複しているものは1としてカウントし、作業列を作らずにSheet1のC列に式を入力したいです。
- ベストアンサー
【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列に日別で反映されるようにしたいと思っています。 わかりづらい説明かもしれませんが、調べて色々試しても上手くいかず、とても困っているのでよろしくお願い致します!
- mazdax
- お礼率57% (4/7)
- オフィス系ソフト
- 回答数7
- ありがとう数9
- みんなの回答 (7)
- 専門家の回答
質問者が選んだベストアンサー
>ちなみにですが、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列が空欄("")のセルに対しては強制的に空欄になるようにします。 尚、データの無いセルに対しても計算対象にすると処理に余分な負荷が掛かりますのでお勧めできません。
その他の回答 (6)
- imogasi
- ベストアンサー率27% (4737/17068)
VBAでの方法なので、質問者には役立たないかもしれないが、WEBには、この本質問が残ると思うので書いてみます。 背景 コントロールブレーク問題というアルゴリズムを使っている。昔(表計算ソフトが出る前、約40年前)はこれがよく使われた。 http://www.nurs.or.jp/~sug/soft/super/cbreak.htm ザ・レトロ・アルゴリズム「コントロールブレーク」by OOP この古典的な手法を使ってます。 ソートする必要はあるが、データを、1度読みするだけで結果が出る。 ーー データ例 A、B例 Sheet (質問のものを多少増やしてます。) データは日付+ID列で、事前にソートしておくこと 日付 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() '--データシートと結果書き出しシート '--データは日付+IDで事前にソートしておく Set sh1 = Worksheets("Sheet1") Set sh2 = Worksheets("Sheet3") sh2.Cells.Clear '--データ最下行 lr = sh1.Range("A100000").End(xlUp).Row MsgBox lr '--初期化 k = 2 'sh2で最初に書き出す行 '--最初のデータ行はSh1の第2行目から 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 を作る(コピペ) ーー 結果は Sheet3のA,B,C列 日付、ID,件数 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
- bunjii
- ベストアンサー率43% (3589/8248)
>この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$))とすれば良いでしょうか?
- tsubu-yuki
- ベストアンサー率46% (179/386)
「同じ日付で、重複しないIDの出現数を取りたい」ってことですね。 小難しい関数式は私は不得意なので、素直に「作業列」を使います。 便宜上(主にSSを作るのが面倒だったので)同じシートに纏めていますが、 ご自身の環境に置き換えて考えてみてください。 添付図のC列(Sheet2のC列)に、作業列を作ります。 ま、そんなに難しく考えずに、 C2セル:=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,1,"") 以下、フィル。 ※2行目起点で自行までに日付もIDも同じものがなければ1 あれば空白を返しなさい。と意味。 COUNTIF関数の複数条件版です。 で、利用回数(当方の図ではF列)にはお解りの通り、 F2セル:=COUNTIF(A:A,E2) 以下、フィル。 COUNTIF関数を使ってやれば簡単ですね。 問題のID件数(添付図だとG列)。 ココにはSUMIF関数を使います。 G2セル:=SUMIF(A:A,E2,C:C) 以下、フィル。 えぇ、このために作業列に「1」を立てたわけです。 単純に足してやれば、合計=ID件数ですね。 そんなわけで、比較的単純な関数で完結です。 作業列は邪魔にならない位置に作るか、非表示にするか。 それともそのまま見せておくかは、お好みです。
お礼
ありがとうございます! 作業列を作ってカウントする方法も知ることができて勉強になりました! 簡単な数式の組み合わせでわかりやすいです!
- msMike
- ベストアンサー率20% (363/1775)
[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),"")
- msMike
- ベストアンサー率20% (363/1775)
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,)))
- skp026
- ベストアンサー率45% (1011/2238)
複数の条件が設定可能なCOUNTIFS関数でできると思います。 以下はサンプルです。 https://www.becoolusers.com/excel/countifs.html
お礼
COUNTIFS関数は最初に試したのですが、自分だけでは難しかったです! しかしサンプルをご用意していただき便利になりました!ありがとうございました!
関連するQ&A
- 【画像あり】複数条件の件数カウントについて
タイトルの件、質問いたします。 下記の図の、シート2の条件が下記の件数をカウントできる関数を、シート1のB2セルに 作りたいです。 【条件】 A列【受付日】が、4月中で かつ B列【地域 】が、東京で かつ C列【分類1】が、A の件数 ご存知の方、いらっしゃいましたら、宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- EXCELでの期間のカウント方法
EXCELのA列に日付が入っているのですが、決められた期間のセルをカウントしたいのです。 例 日付 5月件数 6月件数 7月件数 6/1 2 3 1 6/3 5/24 7/16 5/1 6/30 このようにカウントしたいのですが、具体的にはどのような式を作ればよいでしょうか? ご存知のかたがいらっしゃいましたらよろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- EXCEL:ID番号の自動入力
EXCEL:ID番号の自動入力 複数のシートに、 B列 C列 日付 案件 が入力されるファイルがあり、A列に自動的にID番号をふりたい と考えております。 可能であれば、B列の日付ごとに複数シートにわたって(シート毎のID ではなく、ファイル内で連番)ID番号が入力されればベストなのですが、 同日付の案件もあるのでそれはやはり関数では難しいでしょうか? また、B列に日付を入力された時点で、連番式にIDが入力されるVBA を組んだとして、その行を削除した場合、他のIDも自動的に一個前に詰める ように組むことはできますでしょうか? もしよい方法をご存知の方いらっしゃいましたら ご教示願えれば幸いです。
- ベストアンサー
- その他MS Office製品
- エクセルで月間の件数をカウントしたい
エクセルで月間の件数をカウントしたい エクセル2003で以下のよな表をつくりました。 A B 1 日付 管理番号 2 2010/2/3 12345 3 2010/2/12 12346 4 2010/2/14 空白 5 2010/2/21 文字列 6 2010/3/4 12357 7 2010/3/7 12348 8 2010/3/30 文字列 ここから一ヶ月間の管理番号(数値)の件数を自動でカウントするようにしたいのですが、どのようにすれば良いでしょうか。 (例:2月の管理番号の件数) 月(A列)の件数を絞るのにはCOUNTIFの組み合わせなどで出来たのですが、さらに管理番号(B列)を絞り込むやり方が分かりません。 宜しくお願い致します。
- ベストアンサー
- その他MS Office製品
- エクセルで日付データの比較/判定でカウント
エクセルで日付データの比較/判定でカウント A1に 2006/2/22 A2に 2006/2/23 ---------- という日付データが入っている。 またc1~c4に c1:2006/2/24+時間 (例2006/2/24 07:18) c2:2006/2/23+時間 c3:2006/2/23+時間 c4:2006/2/22+時間 ---------- という時刻まで含む日付データが入っている。 (処理)A1とC列のデータの日付けが同じものは1つc4だけなのでカウントし1件とその横の B列(B1)にカウント件数を表示したい。 B1に書く式はどのようにすればよいか。よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルのセルのカウント方法で質問です。
エクセルのセルのカウント方法で質問です。 A列B列C列があります。 A列とB列のセル内の文字が指定した文字の時 C列のセル内に文字が入力されてる場合にカウントするにはどうすればよいでしょうか またカウントではなくC列に数字が入力されているとして合計値を出したい場合も教えてください。 例として画像を貼り付けます。
- 締切済み
- オフィス系ソフト
- !!!!エクセルのカウント!!!!
他のシートから「=」で顧客名と担当者を拾い以下のように2段でセルに入力し入力されている上段の顧客数のみカウントを行ないたいのですが、COUNTAで行なうと全部の数(イコール設定されているセルも含めて)カウントしてしまいます。困っています。良い式を教えてください宜しくお願いします。 A様 担当者 B様 担当者 C様 担当者 上記別のシートよりデータを各々のセルに飛ばし、A、B、Cの各顧客の数のみをカウントしたい。
- ベストアンサー
- オフィス系ソフト
- excel2000 ある列の項目名をカウントしその件数表示をする
エクセルで表10行×5列の表があります。1列目にあるデータが入っております。その項目名をカウントし多い順に別の表にその項目名と件数を表示させたい。できるだけ自動的におこなうためにはどうすればよいでしょうか。 例 :ある表 1列 A B c B c A D E F B 結果 別の表 項目 件数 B 3 A 2 C 2 D 1 E 1 F 1
- ベストアンサー
- オフィス系ソフト
- EXCEL関数について
EXCEL関数について Sheet1、A1~A1000セルに「1~10」までの値がランダムに入力されております。 数字は「グループ1」、「グループ2」・・・という意味です。 B列、B1~B1000セルには「1~4」までの値がランダム入力されております。 B列については、空白のセルもあります。 Sheet2、A1~A10セルに、「1~10」までの値、「グループ1」~「グループ10」までが順に入力されております。 ここで、Sheet2、B列~E列、それぞれの1~10セルを使用し、、 それぞれの「グループ」がSheet1にて「1~4」の値をいくつずつ選んでいるか算出したいのです。 「グループ1」について、「1」のカウントはB1セル、「2」のカウントはC1のセルに返します。 同じように「グループ2」はB2~E2のセルに、「グループ3」はB3~E3のセルに値を返したいのです。 「COUNTIF」等の関数でいろいろと試してみたのですがうまく反映されません。 拙文乱文、大変恐縮ですが、ご教示頂きたく何卒宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- 月別 出勤日数の件数の出し方
いつもお世話になります。 WINDOWS XP EXCELL2003 月度別に出勤日数を求めたいのですが ;現在 シート「日報」に B列 日付 C列 個人別ID番号 件数を求めるのに N列 個人別ID番号 O列 氏名 P列 件数 P1に=COUNTIF($C$2:$C$5000,N2) で件数のみを求めていました。 これを別シート「月別日数」で月度別に件数が出るようにしたいのですがどういうようにすればいいかご教示いただけませんか。 シートを追加して シート「月別日数」 A列 個人別ID番号 B列 個人氏名 C列(4月)~N列(3月) 件数 宜しく御願いします。
- ベストアンサー
- オフィス系ソフト
お礼
タイプミス失礼致しました。 B列は文字列なので、ご教示いただいた数式を入れたらエラーもなくできました! 本当にありがとうございます!