ExcelでA列・B列・C列の3項目がすべて同じ行をCOUNTIFS関数で重複チェックしていると、数千行程度のデータでも数式の書き方によっては再計算が重くなることがあります。
特に注意したいのが、COUNTIFSの「条件」に1つのセルではなく、3500行分の範囲そのものを指定しているケースです。複数行の結果を配列として一度に計算する必要があるため、それと同じ式を多数のセルへ入れると不要な計算量が非常に大きくなる場合があります。
A・B・Cの3列が同一かを各行ごとに判定したいだけなら、COUNTIFSを捨てて別の高度な関数へ置き換える前に、条件部分を「現在行の1セル」に変更するのが第一の改善策です。さらに大量データでは補助列を使う方法なども有効です。
COUNTIFSが重くなる原因は条件に列範囲そのものを指定していること
例えば次のように、条件範囲だけでなく条件にも3500行分の範囲を指定した数式を考えます。
=COUNTIFS($A$1:$A$3500,$A$1:$A$3500,$B$1:$B$3500,$B$1:$B$3500,$C$1:$C$3500,$C$1:$C$3500)
この書き方では「A1を検索する」「A2を検索する」といった単一条件ではなく、複数行の条件をまとめて評価する形になります。Microsoft 365など動的配列に対応したExcelでは、複数の結果を配列として返す動作になることがあります。
さらに同じような式を3500行へコピーしている場合、各セルで3500件分に相当する計算を繰り返すことになり、必要以上に負荷が増えます。重複判定を1行ずつ表示したい用途では、この計算方法を避けるだけで大幅に軽くなる可能性があります。
各行の重複数を調べるならCOUNTIFSの条件をA1・B1・C1にする
A列・B列・C列がすべて一致するデータが何件あるかを各行に表示したい場合は、次のようにします。
=COUNTIFS($A$1:$A$3500,A1,$B$1:$B$3500,B1,$C$1:$C$3500,C1)
この数式を例えばD1に入力してD3500までコピーします。検索する範囲は固定しますが、検索条件は現在の行にあるA1・B1・C1だけです。2行目にコピーされれば条件部分は自動的にA2・B2・C2へ変わります。
例えばA1が「東京」、B1が「営業」、C1が「1001」であり、この3項目が完全に一致する行が表の中に3件あれば結果は3になります。同じ組み合わせがその行だけなら1です。
つまり、元の目的が「A・B・Cの組み合わせが何回登場しているか」を各行で調べることなら、別の関数へ変更しなくてもCOUNTIFSの使い方を変えるだけで対応できます。
重複しているかだけ知りたいなら1より大きいかを判定する
重複件数そのものではなく「重複している／していない」だけ分かればよい場合は、COUNTIFSの結果を論理判定にできます。
=COUNTIFS($A$1:$A$3500,A1,$B$1:$B$3500,B1,$C$1:$C$3500,C1)>1
同じ組み合わせが2件以上あればTRUE、1件だけならFALSEになります。文字で表示したい場合はIF関数を組み合わせます。
=IF(COUNTIFS($A$1:$A$3500,A1,$B$1:$B$3500,B1,$C$1:$C$3500,C1)>1,"重複","重複なし")
例えばA列が氏名、B列が生年月日、C列が社員番号という表なら、3項目すべてが一致するレコードだけを「重複」と表示できます。
2件目以降だけを重複として表示する方法
COUNTIFSを表全体に対して実行すると、同じデータが2件あった場合は1件目にも2件目にも「2」が表示されます。しかし実務では、最初に出てきたデータは正常として残し、2件目以降だけ「重複」と表示したいことがあります。
その場合は検索範囲の終点を現在行まで広げていく方法が使えます。
=IF(COUNTIFS($A$1:A1,A1,$B$1:B1,B1,$C$1:C1,C1)>1,"重複","")
1行目ではA1:C1まで、2行目ではA1:C2まで、100行目ならA1:C100までを対象にします。同じ組み合わせが初めて出たときは空欄になり、2回目以降にだけ「重複」と表示されます。
例えば「AAA・東京・100」という組み合わせが10行目と120行目にあった場合、10行目は空欄、120行目には「重複」と表示されます。データ削除候補を探す用途ではこちらのほうが使いやすい場合があります。
さらに軽くしたいなら補助列で3項目を1つのキーにまとめる方法もある
数万行以上へ増える場合や、同じ3列の組み合わせを何度も別の計算で利用する場合は、補助列を作ってA・B・Cを一つのキーとして扱う方法もあります。
例えばD1に次のような式を入れます。
=A1&CHAR(1)&B1&CHAR(1)&C1
そしてE1で次のように重複数を求めます。
=COUNTIF($D$1:$D$3500,D1)
これならCOUNTIFSで3組の条件を毎回比較する代わりに、あらかじめ作った1つのキーだけをCOUNTIFで比較できます。データ量やブック全体の数式構成によっては、こちらのほうが管理しやすくなる場合があります。
なお、単純に=A1&B1&C1と連結すると、「AB・C」と「A・BC」がどちらも「ABC」になってしまう可能性があります。そのため、通常のデータには現れにくい区切り文字を間に入れるほうが安全です。
SUMPRODUCTへの置き換えは高速化目的ではおすすめしにくい
複数条件の重複チェックではSUMPRODUCTを使った数式も作れます。しかし、COUNTIFSが遅いからSUMPRODUCTへ変えれば必ず高速になるわけではありません。
例えばSUMPRODUCT(($A$1:$A$3500=A1)*($B$1:$B$3500=B1)*($C$1:$C$3500=C1))のような式でも同じような件数を求められますが、配列演算を行うため、大量の行へコピーした場合はCOUNTIFSより負荷が高くなるケースがあります。
COUNTIFSは条件付き集計のために用意されている関数なので、単純な複数条件カウントでは基本的にCOUNTIFSを優先します。高速化の目的なら、まず範囲指定を見直し、それでも不足する場合に補助列などを検討する順序が適しています。
3500行なら列全体参照より実データ範囲を指定する
Excelでは$A:$Aのように列全体を指定することもできますが、パフォーマンスを重視するブックでは必要な範囲だけに限定したほうが無難です。
今回のデータが3500行までなら、$A$1:$A$3500、$B$1:$B$3500、$C$1:$C$3500のように実際のデータ範囲を指定する考え方は適切です。将来データが増えるなら5000行や10000行程度まで余裕を持たせる方法もあります。
一方、データが継続的に増える表ではExcelのテーブル機能を使う方法もあります。テーブルなら行を追加したときに数式や参照範囲が自動的に拡張されるため、毎回3500を4000へ変更するといった作業を減らせます。
数式以外にもExcelが重くなる原因を確認する
3500行程度はExcelにとって極端に多いデータ量ではありません。COUNTIFSを正しい形で3500行程度使用しているだけなら、通常は比較的扱いやすい規模です。そのため数式を修正しても非常に重い場合は、ブック内の別の原因も確認します。
例えば数万個の数式、VLOOKUPやXLOOKUPの大量使用、INDIRECTやOFFSETなど再計算されやすい関数、広範囲の条件付き書式、外部リンク、大量の画像やオブジェクトなどが組み合わさると動作が重くなることがあります。
また、数式が必要ない過去データなら計算結果をコピーし、「値として貼り付け」で固定する方法もあります。ただし数式を削除すると元データ変更時に結果が更新されなくなるため、更新不要な範囲だけに限定します。
目的別におすすめの重複チェック式を整理
A・B・C列の複数条件による重複チェックは、目的に応じて次のように使い分けると分かりやすくなります。
| 目的 | 数式 |
|---|---|
| 同一データの件数を表示 | =COUNTIFS($A$1:$A$3500,A1,$B$1:$B$3500,B1,$C$1:$C$3500,C1) |
| 重複かどうかTRUE/FALSEで判定 | =COUNTIFS($A$1:$A$3500,A1,$B$1:$B$3500,B1,$C$1:$C$3500,C1)>1 |
| 「重複」と文字表示 | =IF(COUNTIFS($A$1:$A$3500,A1,$B$1:$B$3500,B1,$C$1:$C$3500,C1)>1,"重複","") |
| 2件目以降だけ重複表示 | =IF(COUNTIFS($A$1:A1,A1,$B$1:B1,B1,$C$1:C1,C1)>1,"重複","") |
| 補助列で比較 | =A1&CHAR(1)&B1&CHAR(1)&C1を作成後に=COUNTIF($D$1:$D$3500,D1) |
一般的な3500行程度の表なら、最初のCOUNTIFSで十分なケースが多いでしょう。補助列方式は、さらに行数が増える場合や、作成した複合キーを別の処理にも利用したい場合に検討すると便利です。
まとめ：COUNTIFSを別関数に変える前に条件を1セルへ変更する
Excelで3000行以上の複数列重複チェックが重い場合、最初に確認したいのはCOUNTIFSの条件指定です。A・B・C列の組み合わせを各行ごとに判定するなら、=COUNTIFS($A$1:$A$3500,A1,$B$1:$B$3500,B1,$C$1:$C$3500,C1)という形にするのが基本です。
条件部分へ$A$1:$A$3500のような範囲そのものを指定すると、複数の結果をまとめて計算する配列処理になり、同様の数式を各行へ配置した場合には不要な計算が大量に発生する可能性があります。
重複しているかだけなら>1で判定し、2件目以降だけ検出したいならCOUNTIFSの検索範囲を現在行まで広げていく方法が使えます。さらに大規模な表では、A・B・Cを補助列で複合キーにしてCOUNTIFする方法も選択肢になります。
3500行という行数そのものはExcelではそれほど大きくありません。COUNTIFSを別の複雑な関数へ置き換えるより、まず「検索範囲は固定、検索条件は現在行の1セル」という形へ修正することが、結果を変えずに計算負荷を抑える最も分かりやすい改善方法です。
コメント