excelグループごとに他と値が違うセルを知りたい

質問

お世話になります。
Excelで、A列にグループ名があり、B列に値が入力されているリストで、同じグループの中で他の値が入力されている箇所を調べようとしています。（わかりにくいですよね・・・）

例えば・・・・

1 グループ　値
2 いちご b
3 いちご b
4 いちご b
5 りんご a
6 りんご a
7 りんご b （←ここを知りたいです）
8 りんご a
9 ばなな c
10 ばなな c
11 ばなな a （←ここを知りたいです）
12 ばなな b （←ここを知りたいです）
・
・
・

というリストがあり、7のb、11のa、12のbを求めたいと考えています。
グループは何百とあり、値はせいぜい10個程度です。

COUNTIFSや、SUMPRODUCTを使ってできないかと色々やってみたんですが、力量不足でうまくいきません。
恐れ入りますが、ご教示いただけますとありがたいです。

回答No.5

imogasi

#4です。エクセルの関数での処理が好き、というレベルかもしれないので、
関数でやると、
元データ　
A、B、C列
C列はデータ全行に１を入れておく。SUMIFを使うために。
COUNTIFS使用なら、この操作は不要だろうが。
A21:C31ー＞前回答で出たデータや結果をコピーして使ったので、範囲が飛んでいるがすみません。
いちご b 1
いちご b 1
いちご b 1
ばなな a 1
ばなな b 1
ばなな c 1
ばなな c 1
りんご a 1
りんご a 1
りんご a 1
りんご b 1
ーー
A21:B31のデータ範囲について
データーフィルター詳細設定で「重複するデータは無視する」、で
いちご b
ばなな a
ばなな b
ばなな c
りんご a
りんご b
が出る（A列データ＋B列データの組み合わせで、重複がなく、漏れのない組み合わせリスト）。
A47:B5２にこのリスト部分をコピー貼り付けで置くとする。
C47に式　=SUMIFS($C$21:$C$31,$A$21:$A$31,$A47,$B$21:$B$31,$B47)
を入れてC５２まで式複写。ＳＵＭＩＦＳ関数を利用した。
ーー
結果
A４７：C５２には
いちご b 3
ばなな a 1
ばなな b 1
ばなな c 2
りんご a 3
りんご b 1
の表ができる。これを見て後、考えては、どうでしょう。
現状の数は判るが、度の行にあるかは、この方法ではわかりにくい。行番号を入れて、元データをソートすれば、行的に固まって見えるが。

回答No.4

imogasi

（１）ソートする。グループ順＋値順（２列）でソート（並べ替え）すれば、目視で異質のものはシート上で、見てすぐわかるのでは。
（２）ピボットで表を作れば、炙りだせる。
（３）VBAでやれば簡単。
　（２）と実質同じのことだが、ロジックをプログラムに組み込めば、こまめな表が作れる。
ーー
VBAでの例。
A列グループ列でソートする。
例データ
グループ 値
いちご b
いちご b
いちご b
りんご a
りんご a
りんご b
りんご a
ばなな c
ばなな c
ばなな a
ばなな b
ーー
ソートする。
A,B列
グループ 値
いちご b
いちご b
いちご b
ばなな a
ばなな b
ばなな c
ばなな c
りんご a
りんご a
りんご a
りんご b
これを簡単なVBAで件数表にする。
標準モジュールに下記を作る。
Sub test01()
Range("I2:j1000").Clear
lr = Range("A100000").End(xlUp).Row
MsgBox lr
k = 2
Cells(k, "H") = Cells(2, "A")
Cells(k, "I") = Cells(2, "B")
maekey = Cells(2, "A") & Cells(2, "B")
Cells(k, "J") = 0
For i = 2 To lr
Key = Cells(i, "A") & Cells(i, "B")
If Key = maekey Then
Cells(k, "J") = Cells(k, "J") + 1
Else
maekey = Cells(i, "A") & Cells(i, "B")
k = k + 1
Cells(k, "H") = Cells(i, "A")
Cells(k, "I") = Cells(i, "B")
'--件数＋１
Cells(k, "J") = Cells(k, "J") + 1
End If
Next i
End Sub
実行。
結果
H:J列
いちご b 3
ばなな a 1
ばなな b 1
ばなな c 2
りんご a 3
りんご b 1
この中から異質のものを目視でみつける。
VBAのプログラムに細かな条件を、簡単に組み込むことはできる。３件以上はリストしないとか。異質なものの行番号をリストするとか。

回答No.3

HohoPapa

いちごやばなななど、種類ごと、値ごとの数を数え、
極端に数の少ないグループ名と値のペアがあぶり出せればいいですか？
ならば、
添付のようなPivotテーブルで集計するというのはどうでしょうか？

あぶり出せれば、
あとはフィルダーを使って絞り込んだり
行位置を特定できるだろうと思います。

そうではなく、
あぶり出した行の背景色を染めるとか
C列にそれなりの表示するするといったことを求めているのであれば
あぶり出す条件（例えばグループ内で登場する割合が5%未満とか）を
提示してみてください。

その場合、
おそらくVBAを使うことになるものとおもいますので、
その可否も教えてください。

回答No.2

msMike

D2: =A2&B2
C2: =IF(A2="","",IF(COUNTIF(D$2:D$1000,D2)>1,"","←ここ"))

回答No.1

bunjii

>A列にグループ名があり、B列に値が入力されているリストで、同じグループの中で他の値が入力されている箇所を調べようとしています。（わかりにくいですよね・・・）
説明が足りないためでしょうか分かり難いです。
仲間と仲間外れの違いについて説明されていません。
例えばA列のグループは同じものが連続していますよね？
連続していないこともありますか？
B列の値がA列のグループでB列の値が複数のとき仲間でB列の値が1つだけの行が仲間外れで良いでしょうか？
仲間外れのマークをC列に表示すれば良いでしょうか？
それで良ければC2セルへ次の数式を設定して下へ必要数をコピーすれば良いでしょう。
=IF(COUNTIFS(A:A,A2,B:B,B2)=1,"×","")

お礼

皆さま、回答いただきありがとうございました。
教えて頂いたことを試してみましたが、私の説明が悪く、うまく活用することができず、結局１つ１つ確認することで作業を終えました。