ダウン ロード

重複リスト

表から重複しているデータの件数を求める。
また、重複した項目を除いたリストを作成し、ＪＩＳコード昇順に並べ替える。

問１．重複を強調表示
問２．重複件数
問３．重複リスト
問４．非重複リスト
問５．昇順に並べ替え
問６．重複を除いたリスト件数－配列数式
サンプルファイルのダウン ロード
リンクリスト
解答を 全て展開　折り畳む

問１．重複を強調表示

問題

問１．重複を強調表示

担当者がいくつか重複しています。
重複している項目をピンクで強調表示（条件付書式）しなさい。

完成例


解答例


現在位置を含めて上側に、同じ担当者が２個以上ある（１より大きい）場合にセルの色をピンクで塗りつ潰す。



問２．重複件数

問題

担当者が重複している項目の件数を求めなさい。



完成例

重複件数＝4件
色が付いている行が、担当者の重複

解答例


COUNTIF関数を使う例
=COUNTIF($D$3:$D$24,D3)
　①現在の行を含めて上側にある同じ担当者の個数を求める
=COUNTIF(H3:H24,">1")
　②　①の中から、１より大きい項目の数を求める。これが重複件数となる。

SUMPRODUCT関数を使う例
=SUMPRODUCT(1*(COUNTIF(D3:D24,D3:D24)>1))
　集計列を使わなくても、SUMPRODUCT関数を使えば一気に求めることも可能
　1* ：COUNTIF(D3:D24,D3:D24)>1で返る論理値を数値にするための操作。論理値に数値を掛けると数値になる

配列数式を使う例
{ =SUM(1*(COUNTIF(D3:D24,D3:D24)>1)) }
　配列数式でも求めることが出来る

問３．重複リスト


問題

担当者が重複している項目のリストを全て表示しなさい。

完成例

解答例


=ROW()-ROW($H$2)
相対行番号を作っておく
=IF(COUNTIF($D$3:$D$24,D3)>1,H3,"")
現在行を含めて上側に同じ項目が１より大きい数在ったら空白

=IF(H3>COUNT($I$3:$I$24),"",SMALL($I$3:$I$24,H3))
上側に詰めて表示
重複位置の番号が小さい順に表示

=IF(J3="","",INDEX($B$3:$F$24,$J3,COLUMN()-COLUMN($K$2)))
INDEX関数でそれぞれの項目を取り出す。
COLUMN()-COLUMN($K$2)は列番号
数式をコピーできるようにしたもの。
直接　１，２，３，４，５でも構わない。


問４．非重複リスト

問題

担当者のリストを全て表示しなさい。
これが重複を排除したリストとなり、いろいろと役に立つことが多い。



完成例

緑枠が非重複リスト

解答例


=IF(COUNTIF($D$3:D3,D3)>1,"",H3)
現在行を含めて上側に同じ担当者が有ったら（個数が１より大きい）空白にしておく。
無かったら相対行番号を表示

=IF(H3>COUNT($I$3:$I$24),"",SMALL($I$3:$I$24,H3))
上側に詰める

=IF(J3="","",INDEX($D$3:$D$24,J3,1))
INDEX関数により担当者名を取り出して表示

問５．昇順に並べ替え


問題

非重複リストを、ＪＩＳコード昇順（読み順）に並べ替える

完成例

解答例


=IF(COUNTIF($D$3:$D$24,D3)>1,"",D3)
　　非重複者だけのリスト
　 　同じ名前が上にあったら空白

=IF(I3="","",COUNTIF($I$3:$I$24,">"&I3))
　読みの大きい人の人数を算出
COUNTIF($I$3:$I$24,">" & I3)
　先頭から１文字ずつ、JISコード表の昇順で比較される
　＆は文字連結
　数値が大きいほど昇順で上位になる。
=IF(H3>COUNT($J$3:$J$24),"",MATCH(LARGE($J$3:$J$24,H3),$J$3:$J$24,0))
　LARGE　：、一番大きい数を見つけて、
　MATCH　：　見付けた数の位置を調べる
　以降、２，３，４・・・番目
=IF(K3="","",INDEX($D$3:$D$24,K3))

位置がわかれば、INDEX関数で担当者は簡単に取得できる。

問６．重複を除いたリスト件数


問題と完成例

担当者は何名いるでしょうか？
本リストでは、担当者が重複して記載されています。
重複を除いた実人数を求めなさい。

完成例


解答例


=COUNTIF($D$3:$D$24,D3)
それぞれの名前の重複している人数を求める。この２という数値は、同じ名前が２名いることを示しています。

よって、重複を除くためには、同じ名前の行を全て足した行数が１になれば良いのだから、
それぞれの行には重複人数の逆数としておけば良いことになる。
たとえば・・同じ名前が３つあれば、それぞれ 1/3 , 1/3 , 1/3 で合計して１となる。
=1/H3

=SUM(I3:I24)
全ての和が、重複を除いた実人数となる。

配列数式では下記の１行で記述可
{=SUM(1/COUNTIF(D3:D24,D3:D24))}

①総件数
=COUNTA(C8:C14,C23:C30)
②重複件数
=SUMPRODUCT((COUNTIF(C8:C14,C23:C30)>0)*1)

③取引件数
=H7-H8

④配列数式を使用すると下記 　
{ =SUM((COUNTIF(C8:C14,C23:C30)>0)*1) }
配列数式については、下記を参照
配列数式を極めたい

-->

×
→PageTop