キムには、ロケーションコードを含めるために使用されるデータの列があります。このコードは、A03やB12のように、1文字とそれに続く2桁の数字で構成されます。

キムは、このパターンを使用しない列に入力されたものが何らかの方法で強調表示されるように、条件付きで列をフォーマットしたいと考えています。

この問題に取り組む方法はたくさんあります。各アプローチは、条件付き書式ルール内で使用してTrueまたはFalseを返し、条件付き書式をトリガーできる数式の開発に依存しています。 (このヒントでは、条件付き書式ルールの作成方法については説明しませんが、代わりに、ルールで使用できるさまざまな数式に焦点を当てます。条件付き書式ルールの作成方法については、他の_ExcelTips_で説明しています。)

どの式を組み合わせても、次の3つのことをテストする必要があります。

  • 文字列には正確に3文字が含まれています。

  • 最初の文字は文字です。

※2文字目、3文字目は数字です。

セル内のテキストが3文字しかないかどうかを確認するのは、かなり簡単です。 LEN関数を使用してそれを行うことができます:

=LEN(A1)=3

最初の文字が文字であるかどうかを確認することもかなり簡単です。実際、それを行うにはいくつかの方法があります。最初の文字が文字の場合、次のいずれかがTrueを返します。

=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)

=AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z")

これらは、大文字のみが最初の位置にあることを確認するためにチェックします。小文字も受け入れたい場合は、2番目のテストのバリエーションを使用できます:

=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))<="Z")

大文字と小文字の両方が(事実上他のすべての記号とともに)受け入れられる場合は、次のテストの使用を検討できます。

=NOT(ISNUMBER(LEFT(A1,1)+0))

3番目のテストを適用する方法はいくつかあります-2番目と3番目の文字が数字であるかどうか:

=ISNUMBER(VALUE(RIGHT(A1,2)))

=ISNUMBER(--RIGHT(A1,2))

これらのアプローチでは、最後の2文字を一緒に扱うことに注意してください。つまり、「1」、「11」、「111」はすべてテストに合格し、数字として正常にチェックアウトされます。数式が最後の2桁のみをチェックしている場合、これは問題になる可能性がありますが、最初のチェック(セル内の文字列の全長と3でなければならない)も含めるという事実は、そうではありません。まったく問題はありません。

ここでの秘訣は、3つのテストのそれぞれに対する選択のアプローチを1つの式に結合することです。これは、AND関数を使用して実行できます。各テストから最短のものを選び、次のように組み合わせます。

=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2)))

記述されているように、この数式は、すべてのテストに合格した場合にTrueを返します。つまり、セルには有効なパターンのロケーションコードが含まれています。これは、Kimが列を色(たとえば緑)としてフォーマットしてから、条件付きフォーマットを使用して緑の色を削除する場合、条件付きフォーマットとして最適に機能します。これは逆に思えるかもしれませんが、実際には、パターンが満たされていない場合にのみフォーマットを適用する必要があります。その場合は、数式をNOT関数で囲んで、返されるTrue / Falseを逆にします。

=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2))))

お分かりのように、このような式を使用するのは少し難しいかもしれません。必要に応じて、条件付き書式設定ルールを少し短くするUDF(ユーザー定義関数)を作成できます。次のマクロは良い方法です:

Function IsBadPattern(sCell As String) As Boolean     IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")

End Function

条件付き書式ルールでUDFを使用するには、次の式を使用するだけです。

=IsBadPattern(A1)

参照されるセルの文字列が目的のパターンと一致しない場合、UDFの結果はTRUEになります。書かれているように、最初の文字位置で小文字を使用することはできません。小文字を許可する必要がある場合は、UDFを変更する必要はありません。代わりに、式を次のように変更します。

=IsBadPattern(UPPER(A1))

_ExcelTips_は、費用効果の高いMicrosoftExcelトレーニングのソースです。

このヒント(9976)は、Microsoft Excel 2007、2010、2013、2016、2019、およびOffice365のExcelに適用されます。