【Excel】COUNTIFで長文を正しく数えられない時の対処|255文字制限と全文比較

COUNTIFで長い説明文やURLを数えた結果が合わないときは、検索条件の長さを確認します。Microsoftは255文字を超える文字列の照合で誤った結果が出る場合を案内しています。ただし、件数違いの原因がすべて文字数制限とは限りません。

1. 文字数と一致の条件を確認する

検索値をD2に置いた場合、=LEN(D2)で文字数を確認できます。先頭255文字が同じでも、後ろが違えば別の文です。短く切り詰めた文字列だけで重複を判定しないでください。

確認する点	間違いやすい扱い
完全一致か部分一致か	文に含まれるだけの行も数えてしまう
大文字・小文字を区別するか	COUNTIFは通常区別しない
*や?を含むか	COUNTIFではワイルドカードになる

2. 補助列で全文を比較する

A2:A1000の文字列をD2と比べるなら、B2に=A2=$D$2を入れ、B1000までコピーします。TRUEになった行を確認し、=COUNTIF(B2:B1000,TRUE)で数えます。長文自体をCOUNTIFの条件に渡さない方法です。

この比較では大文字と小文字を区別しません。区別したい場合は補助列を=EXACT(A2,$D$2)にします。元セルにエラーがあると比較結果もエラーになるため、別に確認してください。

3. 一つの式にするなら範囲を限定する

同じ比較をまとめる例は=SUMPRODUCT(--(A2:A1000=D2))です。比較のTRUE/FALSEを1/0にして合計します。大文字と小文字を区別しない完全一致の件数を求める用途です。

A:Aのような列全体では、不要な多数のセルまで計算することになります。実データの範囲に限定し、最初は補助列の結果と件数を照合します。空の検索値や範囲内の空欄を数える必要があるかも決めてください。

4. 長文を短縮しただけでは完全一致を確認できない

LEFTで先頭200文字だけ残す方法は、候補を絞る用途には使えても、全文一致の証明にはなりません。同じ長さの文章でも内容が違うため、文字数を付け加えるだけでも十分ではありません。

ハッシュを使う運用も、仕様や衝突の扱いが必要です。単純な件数確認のために不明なマクロを導入するより、まず元の全文を比較できる補助列を使います。

5. 部分一致や別ブック参照は別の条件として調べる

「含まれる文を探す」と「同じ文を数える」は異なります。SEARCHなどに置き換えると照合条件が変わるので、長文対応という理由だけで置換しないでください。MATCHへ変えれば無条件に長文へ対応するという判断も避けます。

また、COUNTIFが閉じた別ブックを参照すると#VALUE!になる場合があります。文字数だけでなく、参照先を開いた場合との差も確認します。

6. 末尾だけ違う文章で検算する

同一の長文を2行、末尾だけ違う文を1行用意し、同一文の件数が2になるか試します。大文字の違い、空白、改行も実際のデータに応じて確認してください。短いサンプルで意図した照合ができてから、本番範囲へ広げます。

参考: Microsoft公式: COUNTIF、COUNTIFの#VALUE!、SUMPRODUCT

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】