エクセルの条件付き書式では256文字以上の重複セルをチェックできない件について

エクセル
この記事は約5分で読めます。
スポンサーリンク
スポンサーリンク

エクセルの関数や条件付き書式を使えば、マクロを組まなくてもいろんな機能が使えて便利ですよね。

特に条件付き書式の「重複する値」を使えば、同じ文字列が書いてあるセルを色分け表示できてかなり便利です。

エクセルの条件付き書式では256文字以上の重複セルをチェックできない

スポンサーリンク
スポンサーリンク

256文字以上のセルでの不具合の内容

ところが、「重複する値」のセルの文字列が256文字以上だと、重複していても認識されません。

どうやら、文字列を比較する関数や条件式書式は、文字列の上限が255文字のようです。他にもCountif関数で、重複する文字列の数をカウントしようとしても、256文字以上のセルを指定しているとエラーになってしまいます。

直接の解決方法はないようです。マクロのVBA使って実現とか、大変すぎますよね。

(参照:英語の質問サイトでの「How can we improve Excel for Windows (Desktop Application)?」

下図は、「エクセル:重複した内容のセルに色をつけてダブリチェックするには」の手順で重複する文字列をチェックした結果です。

上図のように、256文字以上のセルは重複確認してくれてません。

確認のために、「重複した数」列には、 =COUNTIF(B:B, B3) という関数を使って、B列にB3セルと同じ文字列がいくつあるかの数をC3セルに表示するようにしています。

同様にC4セル以降もやっていくと、256文字と比較しているC10セルやC13セルでは、COUNTIF関数がエラーになっています。

おそらく、条件付き書式の条件チェックも、関数での文字列チェック同様に、256文字以上はエラーで判定できないと思われます。

ちなみに、下図のように文字列の長さをカウントする関数は、256文字以上でも正常にカウントできます。文字列の比較にだけ制限があるようです。

google Spreadsheetでは、256文字以上の文字列でも重複する値による条件付き書式ができます

残念ながら、現段階のExcelではすぐに対応できる方法を知りません。VBAを組めば何でもできるのですが、お手軽ではありません。

代わりに、Googleの表計算アプリであるGoogle Spreadsheet での解決方法を紹介しますね。

Google Spreadsheetは、Excel互換のアプリの中でもすごく良くできたアプリで、オンラインで使うなら、Excelよりも便利だったりします。僕も、ExcelとGoogle Spreadsheetを両方使い分けています。パソコンの中だけで完結することはExcelで便利ですが、共有することがあったり、インターネット上で他のツールと連携させたいときとか、断然Google Spreadsheetのほうが便利です。

必要なときだけでも、Google Spreadsheetを併用してみると良いと思います。

ということで、下図は、Google Spreadsheetで、上図で行った重複する値での条件付き書式の例ですが、256文字のセルもちゃんと比較できています。そして、countif関数もちゃんと、エラーにならず、重複する数を判断できています。

Google Spreadsheetでの重複する値に書式を付ける手順

ただし、エクセルのように、条件付き書式で「セルの強調表示ルール→重複する値」と選ぶだけとはいかないため、重複する値に書式を付ける手順を紹介します。

  • 手順1. 設定ウィンドウを表示
    メニューの「表示形式→条件付き書式」をクリックして、条件付き書式設定ルールのウィンドウを表示させます。
  • 手順2. チェックしたい範囲のセルを選択して「条件を追加」をクリック
    重複した値をチェックしたいセルを選択してから、「条件付き書式設定ルール」ウィンドウにある「条件の追加」をクリックします。
    下図の例では、B列のタブをクリックしてB列全体を選択しています。

  • 手順3. 条件を設定
    セルの書式設定の条件に「カスタム数式」を選択します。すぐ下に数式をいれるテキストボックスがありますので、「=countif(B:B, B1)>1」と入力します。
    意味は、『B:B(B列全体)の範囲に、B1セルの内容がいくつかるかをカウントして(countif関数の意味) それが、1より大きい数だったら(つまり2つ以上あったら)、条件成立とします。』です。countif関数の「B1」ですが、適用範囲の一番上のセルに対する位置を書きましょう。
    この例では、「範囲に適用」としてB1:B995と、B1セルが一番上になっているので、countif関数の第2引数もB1セルとしています。

  • 手順4. 書式を設定して「完了」
    条件成立したときの書式を設定します。

    「デフォルト」と書いてある部分をクリックすると、いくつかの書式から手軽に選ぶこともできます。

    最後に「完了」ボタンを押して確定します。

まとめ

この記事を書いている今現在では、エクセルにおいて、重複した値のチェックでは 256文字以上の文字列が色付けされない問題があります。

お手軽に解決する方法として、エクセル互換ツールのGoogle Spreadsheetでの条件付き書式での解決例を紹介しました。

ExcelもGoogle Spreadsheetもそれぞれ良いところがあるので、ぜひ使い分けると良いと思います。

まだGoogle Spreadsheetを使ったことがない方は、ぜひ、試してみましょう。

 

 

この記事が気に入ったら
いいね ! しよう

コメント