【Excel】重複データ抽出

質問

ブック内のシート別（月別）に月利用者のリストがあるのですが重複利用者を新規シートに抽出したいのです。
例えば、Aさんは4月、5月、6月利用でBさんは4月、6月利用の様に利用者別に利用頻度を確認したいのですが。
利用者リストはA列にあり、例えば10名利用があるとするとセル番地はA1～A10利用者のデータがあります。
利用者数は月によって異なります。当方、関数は少しかじった程度なので関数使用の場合は計算式をご提示頂くと助かります。
バージョンは2010です。
宜しくお願いします。

回答No.4

kagakusuki · ベストアンサー

ANo.2です。

>IDリストはB列に入力されているのですが利用者と同様に抽出出来るものなのでしょうか？

　IDがあるのでしたら、同姓同名の別人が存在する可能性のある利用者名を基にするよりも、IDを基にして抽出した方が確実ですね。

　まず、Ｓｈｅｅｔ2の
A2セルに　　1月、
A3セルに　　2月、
A4セルに　　3月、
A5セルに　　4月、
A6セルに　　5月、
A7セルに　　6月、
A8セルに　　7月、
A9セルに　　8月、
A10セルに　　9月、
A11セルに　　10月、
A12セルに　　11月、
A13セルに　　12月、
という具合に、各月ごとのデータが入力されているシートのシート名を、漏らす事なく順番に入力して下さい。
　次に、Ｓｈｅｅｔ2のB2セルに次の関数を入力して下さい。

=IFERROR(MAX(MATCH("*?",INDIRECT("'"&$A2&"'!B:B"),-1),MATCH(-9E+307,INDIRECT("'"&$A2&"'!B:B"),-1))-ROW('1月'!$A$3),"")

　次に、Ｓｈｅｅｔ2のC1セルに次の関数を入力して下さい。

=SUM($B$1:$B1)

　次に、Ｓｈｅｅｔ2のC1セルをコピーして、すぐ下のC2セルに貼り付けて下さい。
　次に、Ｓｈｅｅｔ2のB2～C1の範囲をコピーして、Ｓｈｅｅｔ2のB3～C13の範囲に貼り付けて下さい。
　次に、Ｓｈｅｅｔ2のF2セルに次の関数を入力して下さい。

=IFERROR(INDIRECT("'"&INDEX($A:$A,MATCH(ROWS($2:2)-1,$C:$C)+1)&"'!R"&ROWS($2:2)-VLOOKUP(ROWS($2:2)-1,$C:$C,1)+ROW('1月'!$A$3)&"C"&COLUMNS($F:F),FALSE)&"","")

　次に、Ｓｈｅｅｔ2のE2セルに次の関数を入力して下さい。

=IF(AND($G2<>"",COUNTIF($G$2:$G2,$G2)=1),COUNT(E$1:E1)+1,"")

　次に、Ｓｈｅｅｔ2のF2セルをコピーして、Ｓｈｅｅｔ2のG2セルに貼り付けて下さい。
　次に、Ｓｈｅｅｔ2のE2～G2の範囲をコピーして、同じ列の３行目以下に貼り付けて下さい。

　次に、Ｓｈｅｅｔ１のA3セルに「利用者」、B3セルに「ID」、C3セルに「総計」と入力して下さい。
　次に、Ｓｈｅｅｔ１のD3セルに次の関数を入力して下さい。

=INDEX(Ｓｈｅｅｔ2!$A$2:$A$13,COLUMNS($D:D))&""

　次に、Ｓｈｅｅｔ１のA4セルに次の関数を入力して下さい。

=IF(ROWS($4:4)>COUNT(Sheet2!$E:$E),"",VLOOKUP(ROWS($4:4),Sheet2!$E:$G,COLUMNS($A:A)+1))

　次に、Ｓｈｅｅｔ１のD4セルに次の関数を入力して下さい。

=IF($B4="","",IFERROR(COUNTIF(INDIRECT("'"&D$3&"'!B:B"),$B4),""))

　次に、Ｓｈｅｅｔ１のC4セルに次の関数を入力して下さい。

=IF($A4="","",SUM($D4:$O4))

　次に、Ｓｈｅｅｔ１のD3～D4範囲をコピーして、Ｓｈｅｅｔ１のC3～O4範囲に貼り付けて下さい。

　次に、Ｓｈｅｅｔ１のA4～O4範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　これで準備は完了で、後は各月ごとのデータを、該当する月用のシートに入力して行きますと、自動的に集計結果が表示されます。

お礼

引き続きご回答ありがとうございます。
このやり方があったんですね。
このご回答いただく前に前回答の式をお借りし作業列を増やし作成しました。
今後のために本回答メモしておきます。

回答No.3

KURUMITO

月別のシート名が1月、2月、3月・・・のようにあるとして、またそれぞれのシートのA列には利用者の名前が入力されているとしたら次のようにしてはどうでしょう。
お求めのまとめのシートを別のシートとしてA2セルから下方には利用者の名前を入力します。B1セルから右横方向には1月、2月、3月・・・のように入力します。
B2セルには次の式を入力して右横方向にドラッグコピーしたのちに下方向にもドラッグコピーします。

=IF(OR($A2="",B$1=""),"",IFERROR(COUNTIF(INDIRECT(B$1&"!A:A"),$A2),""))

これでお望みの表は完成しますが、問題はA列の利用者の名前が自動で入力出来ないかということでしょうか？その場合には利用者を表示させるために作業列を作って対応することが必要でしょう。
例えば同じシートのP2セルには次の式を入力して下方にドラッグコピーします。

=IFERROR(INDIRECT(ROUNDUP(ROW(A1)/100,0)&"月!A"&ROW(A1)-INT(ROW(A1)/100)*10),"")

この式では各月のシートのA列には100行以下の行に名前が入力されているとした場合です。
Q2セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(P2="",P2=0),"",IF(COUNTIF(P$2:P2,P2)=1,MAX(Q$1:Q1)+1,""))

これらの作業列のデータを使ってA列の名前を表示させるためにA2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNTIF(Q:Q,ROW(A1))=0,"",INDEX(P:P,MATCH(ROW(A1),Q:Q,0)))

これでいつの時点で新しい利用者が追加されても自動的に追加されますね。

お礼

ご回答ありがとうございます。
上記計算式を元に作成してみます。
取り急ぎ、お礼まで。

回答No.2

kagakusuki

利用者別に利用頻度を確認されたいのでしたら、次の様な方法は如何でしょうか。

　今仮に、月別のデータを入力したシートのシート名が、それぞれ1月、2月、3月、4月、5月、6月、7月、8月、9月、10月、11月、12月であるものとし、各月のシートのA4以下に利用者名が並んでいて、Ｓｈｅｅｔ２を作業用シート（前処理用シート）として使用して、Ｓｈｅｅｔ１に集計結果をまとめて表示させるものとします。

　まず、Ｓｈｅｅｔ2の
A2セルに　　1月、
A3セルに　　2月、
A4セルに　　3月、
A5セルに　　4月、
A6セルに　　5月、
A7セルに　　6月、
A8セルに　　7月、
A9セルに　　8月、
A10セルに　　9月、
A11セルに　　10月、
A12セルに　　11月、
A13セルに　　12月、
という具合に、各月ごとのデータが入力されているシートのシート名を、漏らす事なく順番に入力して下さい。
　次に、Ｓｈｅｅｔ2のB2セルに次の関数を入力して下さい。

=IFERROR(MATCH("゛",INDIRECT($A2&"!A:A"),-1)-ROW('1月'!$A$3),"")

　次に、Ｓｈｅｅｔ2のC1セルに次の関数を入力して下さい。

=SUM($B$1:$B1)

　次に、Ｓｈｅｅｔ2のC1セルをコピーして、すぐ下のC2セルに貼り付けて下さい。
　次に、Ｓｈｅｅｔ2のB2～C1の範囲をコピーして、Ｓｈｅｅｔ2のB3～C13の範囲に貼り付けて下さい。
　次に、Ｓｈｅｅｔ2のF2セルに次の関数を入力して下さい。

=IFERROR(INDIRECT(INDEX($A:$A,MATCH(ROWS($2:2)-1,$C:$C)+1)&"!A"&ROWS($2:2)-VLOOKUP(ROWS($2:2)-1,$C:$C,1)+ROW('1月'!$A$3))&"","")

　次に、Ｓｈｅｅｔ2のE2セルに次の関数を入力して下さい。

=IF(AND($F2<>"",COUNTIF($F$2:$F2,$F2)=1),ROW(),"")

　次に、Ｓｈｅｅｔ2のE2～F2の範囲をコピーして、同じ列の３行目以下に貼り付けて下さい。

　次に、Ｓｈｅｅｔ１のA3セルに「利用者」、B3セルに「総計」と入力して下さい。
　次に、Ｓｈｅｅｔ１のC3セルに次の関数を入力して下さい。

=INDEX(Ｓｈｅｅｔ2!$A$2:$A$13,COLUMNS($C:C))&""

　次に、Ｓｈｅｅｔ１のA4セルに次の関数を入力して下さい。

=IF(ROWS(A$4:A4)>COUNT(Sheet2!$E:$E),"",VLOOKUP(SMALL(Sheet2!$E:$E,ROWS(A$4:A4)),Sheet2!$E:$F,2))

　次に、Ｓｈｅｅｔ１のC4セルに次の関数を入力して下さい。

=IF($A4="","",IFERROR(COUNTIF(INDIRECT(C$3&"!A:A"),$A4),""))

　次に、Ｓｈｅｅｔ１のB4セルに次の関数を入力して下さい。

=IF($A4="","",SUM($C4:$N4))

　次に、Ｓｈｅｅｔ１のC3～C4範囲をコピーして、Ｓｈｅｅｔ１のD3～N4範囲に貼り付けて下さい。

　次に、Ｓｈｅｅｔ１のA4～N4範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　これで準備は完了で、後は各月ごとのデータを、該当する月用のシートに入力して行きますと、自動的に集計結果が表示されます。

お礼

ご回答ありがとうございます。
一度、計算式を参考に作成してみます。
画像までご教授いただきありがとうございました。

補足

ありがとうございます。出来ました。
追加でご教授賜りたいのですが、利用者には個々に専用IDがありましてIDリストはB列に入力されているのですが利用者と同様に抽出出来るものなのでしょうか？
図々しく誠に申し訳ありませんがよろしくお願いいたします。

回答No.1

atamagawarui5

こんなイメージでいいのなら
COUNTIF関数使えば満足しますが。

お礼

返信遅れて申し訳ありません。
ご回答頂きありがとうございました。