Excel2007条件付きで重複分はカウントしない

質問

以下のようなCSVデータがあり、A列、B列、C列のみを使用して
集計を行うことになりました。B列のランクは8種類あります。

Ａ列　　Ｂ列　　　Ｃ列
地域　　区別　　担当
東京　　高　　　　山田太郎
東京　　中　　　　山田花子
埼玉　　高　　　　佐藤太郎
東京　　中　　　　山田太郎
東京　　高　　　　山田太郎
東京　　花　　　　佐藤花子
埼玉　　夢　　　　加藤太郎

地域と区別については、counfifやsumproductを使用して、
以下のような感じでカウントすることができました。

地域　　件数　　「高」　「中」……　
東京　　４件　　３件　　２件……
埼玉　　２件　　１件　　０件……

今回、「重複するデータは1として数える」ということになり、
お力を貸していただきたく書き込みさせていただきました。

・「地域」で重複分を1としてカウントした件数
・「地域」と「区別」を条件に重複分を1としてカウントした件数

上記の件数をカウントしたいのですが、
何かいいアイディアがございましたらご教授お願いいたします。

地域　　件数　　「高」　「中」……
東京　　３件　　１件　　２件……
埼玉　　２件　　１件　　０件……

回答No.4

kagakusuki

今仮に、元データーの表中で、「地域」と入力されているセルがSheet1のA1セルで、集計結果のの表中で、「地域」と入力されているセルがSheet2のA1セルであるものとします。
　又、Sheet2の
C1セルには、"高"ではなく"「高」"と入力されていて、
D1セルには、"中"ではなく"「中」"と入力されていて、
E1セルには、"花"ではなく"「花」"と入力されていて、
F1セルには、"夢"ではなく"「夢」"と入力されているものとします。

●作業列を使用する方法
　まず、適当な列（例えばSheet3のA列）の2行目のセル（Sheet3のA2セル）に、次の数式を入力して下さい。

=IF(COUNTIF(A$1:A1,Sheet1!$A2&"「"&Sheet1!$B2&"」"&Sheet1!$C2)=0,Sheet1!$A2&"「"&Sheet1!$B2&"」"&Sheet1!$C2,"")

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet2のB2セルに、次の数式を入力して下さい。

=COUNTIF(Sheet3!$A:$A,$A2&"*")

　次に、Sheet2のC2セルに、次の数式を入力して下さい。

=COUNTIF(Sheet3!$A:$A,$A2&C$1&"*")

　次に、Sheet2のC2セルをコピーして、Sheet2のC2セルよりも右にあるセルに貼り付けて下さい。
　次に、Sheet2の2行目のB列～表の中で最も右側にある列の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　以上です。

●作業列を使用しない方法
　まず、Sheet2のB2セルに、次の数式を入力して下さい。

=SUMPRODUCT((COUNTIFS(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$1)+1),$A2,OFFSET(Sheet1!$B$1,,,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$1)+1),Sheet1!$B$2:$B$8,OFFSET(Sheet1!$C$1,,,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$1)+1),Sheet1!$C$2:$C$8)=1)*1)

　次に、Sheet2のC2セルに、次の数式を入力して下さい。

=SUMPRODUCT((COUNTIFS(OFFSET(Sheet1!$A$1,,,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$1)+1),Sheet1!$A$2:$A$8,OFFSET(Sheet1!$B$1,,,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$1)+1),Sheet1!$B$2:$B$8,OFFSET(Sheet1!$C$1,,,ROW(Sheet1!$A$2:$A$8)-ROW(Sheet1!$A$1)+1),Sheet1!$C$2:$C$8)=1)*(Sheet1!$A$2:$A$8=$A2)*("「"&Sheet1!$B$2:$B$8&"」"=C$1))

　次に、Sheet2のC2セルをコピーして、Sheet2のC2セルよりも右にあるセルに貼り付けて下さい。
　次に、Sheet2の2行目のB列～表の中で最も右側にある列の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　以上です。

回答No.3

noname#204879

添付図参照

D2: =A2&"_"&B2&"_"&C2
E2: =COUNTIF(D$2:D2,D2)=1
範囲 D2:E2 を下方にドラッグ＆ペースト
H2: =SUM(I2:P2)
I2: =SUMPRODUCT(($A$2:$A$100=$G2)*($B$2:$B$100=I$1)*($E$2:$E$100))
セル I2 を右方にドラッグ＆ペースト
範囲 H2:P2 を下方にドラッグ＆ペースト

回答No.2

whiteocean

こういうのはいかがでしょうか？

並び替えで「地域」「区別」「担当」の順に並べる
↓
D列に「=IF(A2=A3,IF(B2=B3,IF(C2=C3,"重複",""),""),"")」を入力
↓
重複している箇所に「重複」と出るので行を削除
↓
残ったデータでcountif やsumproductで集計

D列のIF文をアレンジすれば地域、区別の重複だけ見ることもできます。

回答No.1

imogasi

こういうのは
（１）ピボットテーブル
（２）VBAでプログラムを作成(略）
（３）フィルタオプションの設定
向きではないかな。
ピボットで
データの個数 / 担当
地域 区別 合計
埼玉 高 1
夢 1
埼玉 合計 2
東京 花 1
高 2
中 2
東京 合計 5
総計 7
ーーー
フィルタオプションの設定では
A,B列を範囲指定して
指定した範囲
重複するレコードは無視する
で
地域 区別
東京 高
東京 中
埼玉 高
東京 花
埼玉 夢
具体的には出てくる。
ーー
関数ではこういうのは苦手のタイプです。
２列に亘って考えるのも難しくする。
地域 区別 担当
東京 高 山田太郎 東京高
東京 中 山田花子 東京中
埼玉 高 佐藤太郎 埼玉高
東京 中 山田太郎 東京中
東京 高 山田太郎 東京高
東京 花 佐藤花子 東京花
埼玉 夢 加藤太郎 埼玉夢
神奈川 川 木村次朗 神奈川川

6　　＜－地域＋区別で６種類
６のところの式は=SUMPRODUCT(1/COUNTIF(D2:D9,D2:D9))　（データ行は余分に指定しないこと）
具体的に
東京 高
東京 中
埼玉 高
東京 花
埼玉 夢
神奈川 川
をシートに出す式は、この後の関数熟練の回答者から出るかもしれない。
もう１列作業列を使って出してよければE列に
地域 区別 担当
東京 高 山田太郎 東京高 1
東京 中 山田花子 東京中 2
埼玉 高 佐藤太郎 埼玉高 3
東京 中 山田太郎 東京中
東京 高 山田太郎 東京高
東京 花 佐藤花子 東京花 4
埼玉 夢 加藤太郎 埼玉夢 5
神奈川 川 木村次朗 神奈川川 6
E2の式は　=IF(COUNTIF($D$2:D2,D2)=1,MAX($E$1:E1)+1,"")
下方向に式を複写。
別シートや別セル範囲に出すため
Sheet2のA2に
=INDEX(Sheet1!$A$1:$C$9,MATCH(ROW()-1,Sheet1!$E$1:$E$9,0),COLUMN())
と入れてC列まで式複写。
Sheet2のA2:C2を下方向に式複写
東京 高 山田太郎
東京 中 山田花子
埼玉 高 佐藤太郎
東京 花 佐藤花子
埼玉 夢 加藤太郎
神奈川 川 木村次朗
Cれつまで出しているが、場合によってはB列で止める。
（自称imogasi方式）Googleで照会すれば沢山例がでる、