Excel。複数列データから重複を除いたデータの一覧を作成する
＜INDEX＋MOD&ROUNDUP＋ROW関数＞
現場で重複データを取り除いて作業するなんてことは、結構あるのですが、次のような複数列にわたってデータがある表があります。
この3列のデータから重複したものを除いたデータの一覧表を作る場合には、
どうしたらいいのでしょうか？
【単数列での重複削除】
一つの列内において、重複データを除きたい場合は、とても簡単に取り除くことができるのようにExcelはなっていますので、確認しておきましょう。
データタブのデータツールにある『重複の削除』を選択します。
重複の削除ダイアログボックスが表示されますので、先頭行をデータの見出しとして使用するにチェックマークがついていることを確認して、OKボタンをクリックしましょう。
これで、削除することができましたね。
なので、重複データを削除するとしたら、一列にデータをしてあげれば簡単に削除することができます。
当然といえば当然なのですが、残念ながら、複数列では対応していないわけです。
【複数列の場合は単数列にする】
複数列ならば、どうやったら、単数列。すなわち、一列にすることができるのかというのを考えればいいわけですね。
今回使うような小さなデータでしたら、コピー＆ペーストで、一列にしてしまえばいいのですが、データが膨大の場合のことを考えると、コピー＆ペーストも大変です。
そこで、今回はマクロではなくて、関数を使っていくことにします。
該当する範囲からデータを検索するには、INDEX関数を使うと効率がいいかと思われます。
INDEX関数の引数は、INDEX（配列,行番号,列番号）という順番になっています。
配列は、データの範囲なので、B2:D6までを絶対参照で設定すればいいのですが、行番号と列番号が悩むポイントですね。
【行番号から考えていきましょう】
どうやったら、数式をオートフィルでコピーしたら、1～5までを繰り返すように算出することができるのを考えてみましょう。そして、どうやったら、1を導くことができるのかが大切になります。
繰り返す場合は、MOD関数で除算した余りを算出すれば、繰り返し算出することができます。
そこで、次のように数式を作ってみます。
=MOD(ROW(A5),5)+1
説明しましょう。
ROW(A5)は、5が算出されますね。
これを5で除算した余りをMOD関数で算出します。
当然、5÷5なので余り0（ゼロ）。
1にする必要があるので、+1（プラス1）します。
では、オートフィルで数式をコピーしてみましょう。
1～5を繰り返し算出することができました。
データ100件の場合には、=MOD(ROW(A100),100)+1 としてあげればOKですね。
つまりデータ件数の行数をデータ件数で除算して+1してあげればいいわけです。
【列番号はROUNDUP関数】
列番号は、1列分のデータ個数分で繰り返す必要があります。つまり、このようにしたいわけですね。
そこで、次に様な数式を作ってみましょう。
=ROUNDUP(ROW(A1)/5,0)
ROW(A1)ですから、1。1÷5なので、0.2それを1にするには、ROUNDUP関数で切り上げてしまえばいいわけですね。
そうすると、1になります。
6行目ならば、6÷5で、1.2なので、2にすることができます。
仮に、データが100件ある場合には、
=ROUNDUP(ROW(A1)/100,0)
と、データ件数で除算すればいいわけですね。
この2つの数式をINDEX関数で使ってF2に数式を作ってみましょう。
=INDEX($B$2:$D$6,MOD(ROW(A5),5)+1,ROUNDUP(ROW(A1)/5,0))
オートフィルで数式をコピーしてみます。
すると、複数列を単数列にすることができますので、あとは、データを別の列に値の貼付けを行って、最初に紹介した、重複の削除を実行すればいいわけですね。
このように、INDEX関数を使って抽出する場合には、行番号と列番号をどうやって持ってくるのかを考えることで様々な表からデータを抽出することができます。
