ここから本文です

以下のような顧客データがあり、最新のカテゴリに顧客が何日分類されているか集計...

mat********さん

2019/9/601:53:04

以下のような顧客データがあり、最新のカテゴリに顧客が何日分類されているか集計したいのですが、エクセルやアクセスでうまく集計することができません。
以下のような出力結果を得たいのですが、どうやって集計した

らよいか教えてもらえないでしょうか。

データ
顧客ID 日付 カテゴリ
1001 20190603 A
1001 20180513 A
1001 20170825 A
1001 20160425 B
1002 20190825 C
1002 20180725 C
1002 20170825 D


出力結果
顧客ID 継続日数
1001 647
1002 396

補足補足します。 顧客ID 1001の場合は、カテゴリAに何日分類されているか。 1002の場合はカテゴリCに何人分類されているか集計したいと考えております。 実際のデータは、大量の顧客IDがあるため、とても手作業で集計できず途方にくれて、知恵袋に助けを求めました。。

閲覧数:
71
回答数:
3

違反報告

ベストアンサーに選ばれた回答

プロフィール画像

カテゴリマスター

kky********さん

2019/9/619:35:34

元のデータをSheet1、結果をSheet2とします。

Sheet1に作業列を使います。

E2
=IF(COUNTIF($A$2:A2,A2)=1,MAX($E$1:E1)+1,"")
下へコピー

F2
=IF(E2="","",MAXIFS(B:B,A:A,A2))
下へコピー

G2
=IF(E2="","",C2)
下へコピー

H2
=IF(E2="","",MINIFS(B:B,A:A,A2,C:C,G2))
下へコピー


Sheet2

A2
=IFERROR(INDEX(Sheet1!A:A,MATCH(ROW(A1),Sheet1!E:E,0)),"")
下へコピー

B2
=IF(A2="","",INDEX(Sheet1!F:F,MATCH(ROW(A1),Sheet1!E:E,0))-INDEX(Sheet1!H:H,MATCH(ROW(A1),Sheet1!E:E,0)))
下へコピー

元のデータをSheet1、結果をSheet2とします。

Sheet1に作業列を使います。

E2...

  • kky********さん

    2019/9/619:45:21

    書き忘れましたが、B列は日付で表示形式がyyyymmddになっているという前提でした。ここが8桁の数値ならもう一つ加工が必要です。

    それからMAXIFSはエクセル2019か365で使用可能な関数ですので、もしお使いのバージョンが該当しなければ、ここも変更しますので返信して下さい。

  • その他の返信(1件)を表示

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

この回答は投票によってベストアンサーに選ばれました!

ベストアンサー以外の回答

1〜2件/2件中

並び替え:回答日時の
新しい順
|古い順

mit********さん

2019/9/610:41:32

お示しの表がA列からC列に有り1行目は項目名で2行目から下方にお示しのようなデータが有るとします。
結構複雑な内容になっていますね。おそらくはカテゴリーが同じ顧客IDで最も多くあるデータの最新と最も古い日付の継続日数を求めるとします。
作業列を多く作って対応します。
J2セルには次の式を入力します。

=A2&C2

K2セルには次の式を入力します。

=IF(J2="","",IF(COUNTIF(J$2:J2,J2)=1,COUNTIF(J:J,J2),""))

L2セルには次の式を入力します。

=IF(K2="","",IF(K2=MAX(INDEX(K:K,MATCH(A2,A:A,0)):INDEX(K:K,MATCH(A2,A:A,0)+COUNTIF(A:A,A2))),MAX(L$1:L1)+1,""))

M2セルには次の式を入力します。

=IF(ROW(A1)<=COUNT(B:B),IFERROR(INDEX(L:L,MATCH(J2,J:J,0))*10^9+B2,""),IF(ROW(A1)<=COUNT(B:B)+MAX(L:L)+1,(ROW(A1)-COUNT(B:B))*10^9,""))

J2セルからM2セルまでを選択してから含まれるそれらの式を下方にドラッグコピーします。
M列のデータが表示されなくなるまで十分下方にまでドラッグコピーします。
その後にお求めの表ですが例えばE2セルには次の式を入力してF2セルまで横にドラッグコピーしたのちに下方にもドラッグコピーすればよいでしょう。

=IF(ROW(A1)>MAX($L:$L),"",IF(COLUMN(A1)=1,INDEX($A:$A,MATCH(ROW(A1),$L:$L,0)),DATE(MID(SMALL($M:$M,RANK((ROW(A1)+1)*10^9,$M:$M,1)-1),3,4),MID(SMALL($M:$M,RANK((ROW(A1)+1)*10^9,$M:$M,1)-1),7,2),MID(SMALL($M:$M,RANK((ROW(A1)+1)*10^9,$M:$M,1)-1),9,2))-DATE(MID(SMALL($M:$M,RANK(ROW(A1)*10^9,$M:$M,1)+1),3,4),MID(SMALL($M:$M,RANK(ROW(A1)*10^9,$M:$M,1)+1),7,2),MID(SMALL($M:$M,RANK(ROW(A1)*10^9,$M:$M,1)+1),9,2))))

なお、作業列が目障りでしたらそれらの列を選択して「非表示」にすればよいでしょう。

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

プロフィール画像

カテゴリマスター

aka********さん

2019/9/608:34:31

この日本語が わかりません
顧客が何日分類されて?

上のデーターから 1001 647 とでるのでしょうか
最初の日から最後の日数であれば 1134日ですが?

ちなみに 最古の日から最新の日であれば

添付になります。

この日本語が わかりません
顧客が何日分類されて?

上のデーターから 1001 647 とでるのでしょうか...

返信を取り消しますが
よろしいですか?

  • 取り消す
  • キャンセル

みんなで作る知恵袋 悩みや疑問、なんでも気軽にきいちゃおう!

Q&Aをキーワードで検索:

Yahoo! JAPANは、回答に記載された内容の信ぴょう性、正確性を保証しておりません。
お客様自身の責任と判断で、ご利用ください。
本文はここまでです このページの先頭へ

「追加する」ボタンを押してください。

閉じる

※知恵コレクションに追加された質問は選択されたID/ニックネームのMy知恵袋で確認できます。

不適切な投稿でないことを報告しました。

閉じる