Excel。一覧表に重複除いて何人いるの？目視確認では大変なんです。
＜COUNTIF関数・IFERROR関数・SUMPRODUCT関数＞
複数のスタッフで店舗を回している一覧表があります。
今回知りたいことは、重複しているスタッフを除いて、いったい何名のスタッフで店舗を回しているのかを確認したいわけです。
一列だったらば、重複を除く方法は色々あるのですが、今回のように、縦横の表になってしまっていると、うまくいきません。
当然、人間による、『目視』なんて、大変以外の何物でもありません。
では、どうやったらいいのでしょうか？
数式一発で算出する方法もありますが、知っている関数を使って算出してきます。
最初に算出するのは、そのスタッフが何回登場しているのかを算出していきます。
F2にCOUNTIF関数をつかって数式を作っていきますので、COUNTIF関数ダイアログボックスを表示しましょう。
範囲には、$B$2:$D$4 と設定します。
範囲は固定しておきたいので、絶対参照を忘れずに設定します。
検索条件には、B2 と設定します。
あとは、オートフィルで数式をコピーします。
F2の数式は、
=COUNTIF($B$2:$D$4,B2)
するとこのような結果になりました。
1でないところが、重複しているわけですね。
2だったら、1というような条件を付けた場合、場合によっては3ということも想定されるので、2だったらというような固定的な考え方では対応できません。
ここからがアイディア。
仮に、全部のデータが1だとしたら、データ全部を合算すれば、人数が算出されますよね。
だったら、2のところを、0.5にすれば、データ全部を合算すればいいわけです。
仮に4だったら、0.25にすればいいわけです。
そのようにするには、算出された値を「1」で除算すればいいわけです。
つまり、
=1/F2という式を作ればいいので、その隣に、改めて表をつくります。
このデータ全部を合算したら、6となるわけです。
つまり6名で3店舗を回していることがわかります。
算出することはできましたが、途中計算を出すのを繰り返すのは、ちょっとスマートではありません。
そこで、COUNTIF関数を使う方法もあります。
F2の数式を次のように変更してみました。
=IFERROR(1/COUNTIF($B$2:$D$4,B2),0)
オートフィルで数式をコピーした結果が次の通りです。
ついでなので、式をまとめただけでなく、空白だった場合エラーになってしまう欠点もIFERROR関数を使って防いでいます。
どうしても、現場ではExcelにとって都合のいい表ばかりではありませんので、様々アイディアを投入して解決ことになりますね。
ちなみに、一発で算出する場合には、SUMPRODUCT関数を使う方法もあります。
=SUMPRODUCT(1/COUNTIF(B2:D4,B2:D4))
ただ、SUMPRODUCT関数はあまり、なじみがないのと、配列関数なので、ちょっとわかりにくいところがありますね。
