こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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を使ってできないかと色々やってみたんですが、力量不足でうまくいきません。
恐れ入りますが、ご教示いただけますとありがたいです。

投稿日時 - 2018-04-21 17:03:40

QNo.9490908

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

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

投稿日時 - 2018-04-22 14:48:40

ANo.4

(1)ソートする。グループ順+値順(2列)でソート(並べ替え)すれば、目視で異質のものはシート上で、見てすぐわかるのでは。
(2)ピボットで表を作れば、炙りだせる。
(3)VBAでやれば簡単。
 (2)と実質同じのことだが、ロジックをプログラムに組み込めば、こまめな表が作れる。
ーー
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")
'--件数+1
Cells(k, "J") = Cells(k, "J") + 1
End If
Next i
End Sub
実行。
結果
H:J列
いちごb3
ばななa1
ばななb1
ばななc2
りんごa3
りんごb1
この中から異質のものを目視でみつける。
VBAのプログラムに細かな条件を、簡単に組み込むことはできる。3件以上はリストしないとか。異質なものの行番号をリストするとか。

投稿日時 - 2018-04-22 11:25:38

ANo.3

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

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

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

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

投稿日時 - 2018-04-22 08:38:40

ANo.2

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

投稿日時 - 2018-04-21 20:56:48

ANo.1

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

投稿日時 - 2018-04-21 20:53:05

お礼

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

投稿日時 - 2018-04-28 23:17:30

あなたにオススメの質問