書式設定を自動判断するにはCELL関数[CELLとIF関数の組み合わせで塗り分け]

今回の記事では

対象のセルにどんな書式設定が設定されているか調べる方法は無いの?

一つ一つ調べるのは大変だから関数を使って一気に調べられたら簡単なのに

セルに設定された書式設定が分かったら、その結果を活用する方法は無いの?

書式を調べることと同時に結果を自動的に判断させられたら簡単なのに

こういった疑問に答えます

✔CELL関数で書式設定を自動判断させる方法のテーマ

①セルに設定された書式設定を自動で判断させるにはCELL関数が正解です

②CELL関数とIF関数を組み合わせれば書式に合わせた結果を表示出来ます

この記事を書いているのはOfficeを約20年間活用しながら

ITを活用したエステの経営マネジメントしている私の経験を基に

「CELL関数で書式設定を自動判断させる方法のテーマ」に関して実体験に基づいて解説しています

今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい

セルに設定された書式設定を自動で判断させるにはCELL関数が正解です

セルに設定された書式設定を自動で調べたいならCELL関数を使うのが正解です

なぜなら、CELL関数は書式設定を始めセルに関する色々な情報を返してくれる関数だからです

まず、CELL関数を使って調べたいセルに設定された書式設定の結果を見てみましょう

これがCELL関数で文字列の書式を調べた結果です

書式設定は色々な種類があるので多くの結果が表示されます

全般を表す「G」は「標準でも文字列でも同じ結果」になります

また、CELL関数の結果を全て覚えるのは大変なので設定された書式の種類をパッと見で判断することは難しいですね

もし、どんな書式が表示されているのかすぐに判断したい時は次の方法がオススメです

例えば「A3セル」に設定されている書式設定を調べたい時はA3セルをクリック

「ホーム」タブの「スタイル」グループにある「数値」をクリック

書式のリストボックスに「文字列」と表示されている場合は文字列の書式設定が設定されていると一目で判断できます

また、「A7のセル」のように「ユーザー定義」が表示された場合は「書式のリスト」から「その他の表示形式」をクリック

これだけで詳しい情報を確認することが出来ます

「その他の表示形式」をクリック

設定されてい項目がすでに選ばれている状態で表示されます

選択中の項目は「青い背景で目立つ」ようになっているので一目で判断出来ます

どんな設定がされているかは、コレを見た方が早いですね

CELL関数のもう少し具体的な使い方は後半で解説しますが、基本的な使い方についてもう少し解説します

CELL関数の基本的な使い方は、「=CELL(検査の種類,[参照])」です

「検査の種類」は「対象のセルの何を知りたいか」を指定する引数です

今回は書式設定の種類を表示してほしかったので「”format”(フォーマット)」を指定しました

検査の種類を指定する時は必ず「”(ダブルクォーテーション)」で種類名を囲みます

「参照」は調べたい対象を指定します

  • 何も設定しない場合はCELL関数を入力したセル
  • 指定した場合は指定した対象のセル
  • 範囲を指定した場合は範囲内の左上のセル

それぞれのセルの情報を指定された種類で表示してくれるのがCELL関数です

ちなみにそれぞれの設定内容は次の通りです

【検査の種類】

検査の種類戻り値
“address”対象範囲の左上隅にあるセルの参照を表す文字列。 
“col”対象範囲の左上隅にあるセルの列番号。
“color”負の数を色で表す書式がセルに設定されている場合は 1。それ以外の場合は 0 (ゼロ)。注: この値は、Starter、 Web 用 Excel、Excel MobileではExcelされません。
“contents”対象範囲の左上隅にあるセルの値 (数式ではない)。
“filename”対象範囲を含むファイルのフル パス名 (文字列)。 対象範囲を含むワークシートが保存されていなかった場合は、空白文字列 (“”)。注: この値は、Starter、 Web 用 Excel、Excel MobileではExcelされません。
“format”セルの表示形式に対応する文字列定数。 各種の表示形式に対応する文字列定数については、次の表を参照してください。 セルが負数に対応する色で書式設定されている場合、文字列定数の末尾に “-” が付きます。 正数またはすべての値をかっこで囲む書式がセルに設定されている場合、結果の文字列定数の末尾に “()” が付きます。注: この値は、Starter、 Web 用 Excel、Excel MobileではExcelされません。
“parentheses”正の値またはすべての値をかっこで囲む書式がセルに設定されている場合は 1。それ以外の場合は 0。注: この値は、Starter、 Web 用 Excel、Excel MobileではExcelされません。
“prefix”セルの “文字位置” に対応する文字列定数。 セルが左詰めの文字列を含むときは単一引用符 (‘)、右詰めの文字列を含むときは二重引用符 (“)、中央揃えの文字列を含むときはキャレット (^)、両揃えの文字列を含むときは円記号 (¥)、また、セルにそれ以外のデータが入力されているときは空白文字列 (“”) になります。注: この値は、Starter、 Web 用 Excel、Excel MobileではExcelされません。
“protect”セルがロックされていない場合は 0、ロックされている場合は 1。注: この値は、Starter、 Web 用 Excel、Excel MobileではExcelされません。
“row”対象範囲の左上隅にあるセルの行番号。
“type”セルに含まれるデータのタイプに対応する文字列定数。 セルが空白の場合は “b” (Blank の頭文字)、セルに文字列定数が入力されている場合は “l” (Label の頭文字)、その他の値が入力されている場合は “v” (Value の頭文字) になります。
“width”2 つの項目を含む配列を返します。配列の 1 つめはセルの列幅で、整数に丸められます。 セル幅の単位は、既定のフォント サイズの 1 文字の幅と等しくなります。配列の 2 つ目の項目はブール値で、列幅が既定値の場合は TRUE、幅がユーザーによって明示的に設定されている場合は FALSE です。 注: この値は、Starter、 Web 用 Excel、Excel MobileではExcelされません。

【表示形式の種類】

Excel の表示形式戻り値
全般“G”
0“F0”
#,##0“,0”
0.00“F2”
#,##0.00“,2”
$#,##0_);($#,##0)“C0”
$#,##0_);[赤]($#,##0)“C0-“
$#,##0.00_);($#,##0.00)“C2”
$#,##0.00_);[赤]($#,##0.00)“C2-“
0%“P0”
0.00%“P2”
0.00E+00“S2”
# ?/? または # ??/??“G”
m/d/yy または m/d/yy h:mm または mm/dd/yy“D4”
d-mmm-yy または dd-mmm-yy“D1”
d-mmm または dd-mmm“D2”
mmm-yy“D3”
mm/dd“D5”
h:mm AM/PM“D7”
h:mm:ss AM/PM“D6”
h:mm“D9”
h:mm:ss“D8”

それぞれの内容は、Microsoftの公式サイトから引用しました

かなり色々なことが出来るので必要に応じて使い分けましょう

「書式設定に関して調べる時」は今回の方法でOKなので「表示形式の種類」だけは今回の表を参考にしてみて下さい

繰返しになりますが、セルに設定された書式設定を自動で調べたいならCELL関数を使うのが正解です

CELL関数とIF関数を組み合わせれば書式に合わせた結果を表示出来ます

CELL関数とIF関数を組み合わせると書式に合わせた結果を表示することが出来ます

なぜなら、CELL関数で判断した結果を基にIF関数で分岐すれば設定した書式に合わせて結果を変更出来るからです

それではCELL関数とIF関数を使った設定した書式によって結果を分岐する方法について見てみましょう

まず、設定された書式の種類によって自動的に結果変更するとこんな風になります

設定内容はコレだけです

「=IF(B2<>”C0-“,”金額ではありません”,”金額です”)」

この式を日本語にしてみましょう

「もし、(B2のセルが「C0-」でなければ,「金額ではありません」、そうでなければ「金額です」と表示してね)」

内容はすごく簡単ですね

こんな風にCELL関数の結果を条件判断の基にして書式の違いを分かりやすく表示することが出来ます

では、コレを更に応用してみましょう

「B2セル~B7」のセルを選択

「ホーム」タブをクリック

「条件付き書式」の「新しいルール」をクリック

「次の数式を満たす場合に値を書式設定」のテキストボックスに「=IF(B2=”G”,TRUE,FALSE)」と入力

意味は「B2のセルが「G」だったら色を塗ってね」という設定になります

書式を設定したら「OK」ボタンをクリック

その結果「G」の書式設定に対して背景色を自動で塗り分けられました

こんな風に書式によってセルの色を塗り分けたい時にも工夫次第で設定できます

もし、条件付き書式の色分け方法を詳しく知りたい場合は「条件付き書式の基本は条件の設定とルール管理」という記事で解説しているので参考にして下さい

繰り返しになりますが、CELL関数とIF関数を組み合わせると書式に合わせた結果を表示することが出来ます

コメントを残す

メールアドレスが公開されることはありません。