セルを調べるCELL関数

2019.02.12関数

今回はExcelの中でも特殊な関数であるCELL関数について説明します。
Excelのセルには、値だけではなく、さまざまな情報が記録されています。CELL関数では、それを読み出すことができます。
例えば、次の計算式では、セルが左揃えなのか、中央揃えなのか、右揃えなのかを検査できます。

=CELL("prefix",A5)

そのセルに設定されている揃えの位置によって、中央揃えなら「^」のように、対応した文字列を表示します。

CELL関数

CELL関数の基本

CELL関数の書式は次の通りです。
=CELL(検査の種類,検査するセル)
検査の種類は次の通りの種類があります。

  • address
    セルの場所を調べる
  • col
    セルの横位置を調べる
  • row
    セルの縦位置を調べる
  • prefix
    文字の揃えを調べる
  • protect
    セルのロックを調べる
  • width
    セルの幅を調べる
  • color
    負の値の色設定を調べる
  • format
    表示形式を調べる
  • parentheses
    正の値の()の表示指定を調べる
  • type
    セルの入力値の種類を調べる
  • contents
    セルの値を調べる
  • filename
    セルのファイル名とシート名を調べる

検査するセルは設定しなくてもよいです。
その場合、計算式が入力されているセルが検査対象となります。

CELL関数を使用する場合の注意点

CELL関数は、検査の種類によって、元の設定を変更した後に、F9キーなどで再計算をする必要がある場合があります。
詳しくは後で説明します。

CELL関数の種類

CELL関数は、大まかに、セルの位置に関するもの、セルの書式に関するもの、表示形式に関するもの。セルの値に関するもの、ファイルやシート名に関するものに分かれます。
その分類ごとに詳しく説明します。

CELL関数の検査の種類ごとの設定

検査の種類ごとに、それぞれの検査の種類の使い方を解説します。

セルの位置に関するもの

  • 検査の種類:address
    addressと指定した場合の内容は「セル位置を表す文字列を絶対参照形式で求める」です。
    再計算は不要で、削除や挿入などで位置が変わった場合でも自動的に計算されます。
    =CELL("address",A1)
    この計算式の答えは「$A$1」です。
    使用例)
    INDIRECT関数と組み合わせると、指定したセルまでの範囲の合計を求めることができます。
    =SUM(INDIRECT("A1:"&CELL("address",A2))
    この計算式では、A1から指定したセルまでの合計を求めることができます。
    上下左右にコピーしても、先頭のセルA1は文字列で指定されているので、絶対にA1になり、絶対参照のような使い方ができます。
  • 検査の種類:col
    colと指定した場合の内容は「セルの横位置を求める(COLUMN関数と同じ)」です。
    再計算は不要で、削除や挿入などで位置が変わった場合でも自動的に計算されます。
    =CELL("col",C1)
    この計算式の答えは「3」です。
    この計算式は
    =COLUMN(C1)
    と同じ動作をします。
    使用例)
    INDEX関数などと組み合わせて使います。
    =INDEX(A1:A5,CELL("col",C1-2))
    この計算式は、セルA1からA5までの範囲に入力されている値のうち、C1の列番号つまり3から2を引いた1番目のセルの値、つまりセルA1の値を求めています。
    この計算式を右にコピーすることで、A2、A3の値を求めることができます。
    つまりこの計算式では縦横変換ができます。
  • 検査の種類:row
    rowと指定した場合の内容は「セルの縦位置を求める(ROW関数と同じ)」です。
    再計算は不要で、削除や挿入などで位置が変わった場合でも自動的に計算されます。
    =CELL("row",A5)
    この計算式の答えは「5」です。
    この計算式は
    =ROW(A5)
    と同じ動作をします。
    使用例)
    address同様、INDIRECT関数と組み合わせると、指定したセルまでの範囲の合計を求めることができます。
    =SUM(INDIRECT("A"&CELL("row",A1)&":C"&CELL("row",A1))
    この計算式はどこに入力してもA列からC列までの1行目の合計を求めます。
    下にコピーすることで2行目、3行目のA列からC列の合計を求める計算式になります。

セルの書式に関するもの

  • 検査の種類:prefix
    prefixと指定した場合の内容は「文字の横位置を表す」です。
    注意しなければならないのは、セルに入力している値が文字列のみ反応し、数値では結果が空白になります。
    再計算は、検査するセルの揃えを変更した場合は必要です。検査するセルの値を変更して、数値から文字列、または文字列から数値に変更した場合は自動的に再計算が行われます。
    求められる答えは、右揃えの場合は、「"」、左揃えの場合は「‘」、中央揃えの場合は「^」、両端揃えや設定なしの場合で文字列ならば、「’」となります。
    使用例)
    もしもセルA1に入力されているものが文字列でかつ、右揃えになっていると大変見づらくなります。
    そのような場合は、次の計算式を条件付き書式に入力し、もし合致したら赤く塗りつぶすなどの設定をしてておくとよいかもしれません。
    =AND(NOT(ISNONTEXT(A1)),CELL("prefix",A1)="""")
    ISNONTEXT関数でセルA1の内容が文字列ではないかチェックしています。NOT関数でそれを逆転させ、セルA1の内容が文字列ならばという計算をしています。
    CELL関数で揃えを調べるためにprefixを検査しており、その内容が右揃えである「"」ならば、という式にしています。「"」を1文字の文字列として認識させるためには「""""」とする必要があります。
    AND関数で文字列、右揃えのチェックをなおかつ条件で繋いでいます。
  • 検査の種類:protect
    protectと指定した場合の内容は「セルのロックがされているか」です。
    再計算は、セルのロックを入れたり外したりするたびに必要です。
    ロックされていれば「1」、ロックされていなければ「0」です。
    使用例)
    範囲に対して、条件付き書式で次のような計算式を入力し、セルの塗りつぶしをしておけば、入力可能なセルだけを塗りつぶしでき、見やすくなります。
    =CELL("protect")=1
    この式を設定しているセルがロックされている場合に1となりますが、その状態かチェックしています。
  • 検査の種類:width
    widthと指定した場合の内容は「小数点を切り捨てたセル幅」です。
    つまり等幅のフォントで、フォントサイズを変更していない状態で、そのセルに入れる半角の文字数を表しています。
    列幅を変更した後の再計算は必要です。
    使用例)
    そのセルに入りきる文字数かどうかをチェックできます。
    次の計算式を入力し、成立していなければ塗りつぶす設定をすることで、セルに入りきらない文字数の場合、塗りつぶしを設定できます。
    =CELL("width",A1)<LENB(A1)
    セルA1の列幅よりも、LENBで求めた半角での文字数が多かったらを判定する式です。

表示形式に関するもの

  • 検査の種類:format
    formatと指定した場合の内容は「表示形式」です。
    セルの表示形式を変更した後の再計算は必要です。
    表示形式によって、求まる値が変わります。
    例えば、カンマなしの小数点以下3位の「12345.00」という表示がされているセルでは、「F3」と表示されます。
    はじめの「F」が表示の種類で、「3」が小数点以下の桁数です。
    「F」がカンマなし数値
    「,」がカンマあり数値
    「C」が通貨記号あり数値
    「P」がパーセント表示
    「S」が指数表示
    です。
    「C0-」のように数値の後ろにマイナスが付いているものは、プラスのほかにマイナス側の数値の設定もされているという意味です。
    日時に感じては上記の法則ではありません。必ず「D」から始まりますが、その後ろの数値によってどの表示形式を表しているかが変わります。
    法則がバラバラなので、実際にセルに表示形式を設定し、CELL関数で調べてみるといいでしょう。
    使用例)
    日時の表示系形式が設定されているかどうかチェックするには次の計算式です。
    =LEFT(CELL(“format”),1) ”D” 
  • 検査の種類:color
    colorと指定した場合の内容は「負の値が赤文字で表示される表示形式の場合、1、そうでなければ0」です。
    セルの表示形式を変更した後の再計算は必要です。
  • 検査の種類:parentheses
    parenthesesと指定した場合の内容は「正の値が()で囲まれる表示形式の場合、1、そうでなければ0」です。
    セルの表示形式を変更した後の再計算は必要です。

セルの値に関するもの

  • 検査の種類:type
    typeと指定した場合の内容は「セルの入力値の種類」です。
    セルに値を入力すると自動で計算されるため、再計算は不要です。
    セルの入力値が空白ならば「b」、文字列ならば「l」、数値、日時、論理値の場合は「v」です。
    入力値だけではなく、セルの計算結果に対しても求めてくれます。
    使用例)
    Excelの関数では、文字列であるかを確認するための関数はありません。
    文字列ではないことを確認するには、ISNONTEXT関数がありますが、その場合、NOT関数と組み合わせないといけません。
    そこで、CELL関数でセルの値が文字列かどうかを判定することができます。
    この方が使い勝手が良いかもしれません。
    そのためには、次の計算式です。 = CELL(“type”) = ”l”
  • 検査の種類:contents
    contentsと指定した場合の内容は「セルの値」です。
    セルに値を入力すると自動で計算されるため、再計算は不要です。
    =CELL(“contents“,A1) 

    =A1 と同じ意味になりますので、あまり使うことはないでしょう。
    contentsは昔のマクロではよく使っていましたが、VBAでもセルの値をそのまま取得できるようになっています。

ファイルやシート名に関するもの

  • 検査の種類:filename
    filenameと指定した場合の内容は「ファイルパスを含んだシート名」です。
    Dドライブの「実験」フォルダ内に「CELL関数」という名前で保存したシート名に「CELL」とついているシートのセルA1を指定している場合、次のような計算式になります。
    =CELL(“filename”,A1)
    この計算式の答えは、「D:\実験\[CELL関数.xlsx]CELL」となります。
    再計算は、ブックを別名で保存した時には必要です。シート名を変更した時は自動で計算してくれるので不要です。
    ただし、再計算時にブック名までしか求まらず、シート名が求まらなくなることがあります。その場合、シート名を再度入力することによってシート名を求めることができます。
    使用例)
    現在のブックのシート名を求める関数はありませんが、次の方法で求めることができます。
    CELL関数のほかにINFO関数という現在のExcelの環境を求める関数も使います。
    =INFO("DIRECTORY")
    この計算式で、直前にアクセスしたフォルダを調べることができます。
    直前にDドライブの「実験」フォルダ内に「CELL関数」ブックを開いた場合、「D:\実験\」が求まります。
    そして、次の計算式で求めたものは、
    =CELL(“filename”,A1)
    「D:\実験\[CELL関数.xlsx]CELL」となります。
    この計算式を組み合わせます。
    組み合わせに使う関数の詳細は関連記事をご覧ください。
    INFO関数で求めた分の文字数だけ、CELL関数で求めた文字列の左側から文字を、文字列を切り出すMID関数と文字数を求めるLEN関数を使って削除します。
    =MID(CELL(“filename”,A1),INFO("DIRECTORY")+1,1000)
    1000というのは、とても多い文字数を指定し、それ以降の最後の文字までという意味です。
    この数式の答えは、「[CELL関数.xlsx]CELL」となります。
    「]」の文字の後ろを切り出せばシート名が求まります。
    さらにMID関数、FIND関数を組み合わせて次の計算式にします。
    =MID(MID(CELL("filename",A1),LEN(INFO("DIRECTORY"))+1,1000),FIND("]",MID(CELL("filename",A1),LEN(INFO("DIRECTORY"))+1,1000))+1,1000)
    長くなるのですが、これでシート名を求めることができます。
    ただし、他のフォルダのブックを開いたり保存したりすると、INFO関数の答えが変わるので注意が必要です。
    また、filenameの指定は全く保存してないブックだと空白になるので、一旦名前を付けて保存してから関数を入力しましょう。

まとめ

今回は、セルの情報を調べるCELL関数を紹介しました。
実用的な計算式をいくつか紹介しましたが、中にはあまり使い道がないように感じるものもあります。
しかし、ブック間の連携した計算式を作成したり、条件付き書式や入力規則と組み合わせたりすることによって、いままでできないと思っていたことができるようになるかもしれません。
CELL関数はVBAと組み合わせれば、さらにもっともっと使える関数になってきます。
widthで調べた値よりも多い文字数が指定されているセルのみ折り返して全体を表示する書式設定をすることもできますし、すべてのセルに対してformatを調べて、Dで始まっていたら、その値を日付としてピックアップするということもできます。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top