Excel「名前の管理」での関数設定で注意しなければならない事

「知っていると得するかもしれないExcelの癖」シリーズ第四回、テーマは「名前の管理について」になります。
Excel 2016バージョン1902を使用しています。

---

Excelで複雑な計算や判断を行う場合、どうしても数式が長くなってしまいます。
メンテナンスのし易さを考慮しながら分かり易い数式を組み上げるて行くのは大変ではあります。

今回説明に使う数式は数独書式で使っている物になりますが、複雑な判断をしている数式として捉えていただければ幸いです。

数式を使って判断したい事

「C3セルに1が入っていたらTRUE(1)」＋「C5セルに1が入っていたらTRUE(1)」＋(C7、C11、C13、C15、C17は省略)＋「C19セルに1が入っていたらTRUE(1)」
この合計が1であれば、C列の9つの行には1がひとつだけしかない「正しく入力されている」ことが解ります。

実際の数式としては下記になります。

=IF(C3=1,1,0)+IF(C5=1,1,0)+IF(C7=1,1,0)+IF(C9=1,1,0)+IF(C11=1,1,0)+IF(C13=1,1,0)+IF(C15=1,1,0)+IF(C17=1,1,0)+IF(C19=1,1,0)

この数式で、「=1」を「=2」から「=9」に変えた全部で9つの関数として、最後に9つの関数すべてが1だったらC列には正しく数値が入力されていることが解ります。

名前の定義で関数を登録する

上記の数式をExcel数式タブ→名前の定義で名前として「COLCHK1」から「COLCHK9」として登録した状態が下図になります。

「名前の定義」の登録時に範囲という項目がありますが、ここはブックを選択します。
ただし、上図の参照範囲を見ていただく分かるのですが、「sheet1!C3」になっており、範囲はブックといいながら、参照先はsheet1を見ることになっています。

問題はここにあります。

関数を定義したシートをコピーして見ると

sheet1をシートコピーしてsheet2を作ります。
その後で名前の管理を開いてみると下図のようになります。

ブックで定義したはずの関数がsheet2の範囲で追加されています。
試しに追加されたsheet2の9つの名前を削除してシートの動きを確認したところ、はやりsheet1の状態を見た結果がsheet2に反映されてしまいます。

という事はシートをコピーするたびに関数が9つ追加されて行くことになります。

数式を関数として登録する時の注意点

前置きが長くなり恐縮ですが、ここからが本題となります。

結論から先に書かせていただくと、数式にセル参照が含まれる場合は、いくら名前の定義で範囲をブックにしたとしてもシートに縛られることとなります。

この縛りから逃れるためには数式からセル参照を無くす必要があります。

ここで登場するのが、一般的には馴染みが薄いOFFSET関数になります。
ただし、OFFSET関数には基準となるセル参照が必要になります。
このOFFSET関数で必要なるセル参照を一般的なセル参照の指定の仕方では無いようにするために下記の関数を定義します。

=INDIRECT(ADDRESS(ROW(),COLUMN()))

上記関数の名前は、ここでは「cCELL」としていますが、この関数は、現在フォーカルを持つセルのセル参照を返してくれます。

従ってセルに「=cCELL()」と入力すると「1つ以上の循環参照が発生しています。…」という警告メッセージが表示されます。
これは例えばA1セルに「=A1」と入力するのと同じ結果になります。

このcCELL関数とOFFSET関数を使用して、COLCHK1を書き換えると下記のようになります。

=IF(OFFSET(cCELL,2,0)=1,1,0)+IF(OFFSET(cCELL,4,0)=1,1,0)+IF(OFFSET(cCELL,6,0)=1,1,0)+IF(OFFSET(cCELL,8,0)=1,1,0)+IF(OFFSET(cCELL,10,0)=1,1,0)+IF(OFFSET(cCELL,12,0)=1,1,0)+IF(OFFSET(cCELL,14,0)=1,1,0)+IF(OFFSET(cCELL,16,0)=1,1,0)+IF(OFFSET(cCELL,18,0)=1,1,0)

この形で残りのCOLCHK2～9をブックに定義すると、いくつシートコピーをしても名前の管理で見ても関数が増えることはありません。

以上、最後までご一読いただきありがとうございました。