Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

知らぬ間に増殖しエラーになる名前定義

ぎゃー!

f:id:ExcelLover:20210919180438j:plain

何だかよく分からないうちに増殖をしている「名前」。

そして知らないうちにエラーとなっている「名前」。

「名前」がどこで増殖しエラーとなっているのか検証しようと思います。

「名前」とは

そもそも「名前」とは何でしょうか。

「名前」とはセル範囲・関数・定数・テーブルに「名前」を付けて、数式の中で使用することが出来る機能です。

「名前」を付けることを「名前定義」といいます。

名前定義

名前定義はどのようにすればいいのか。

1つ目は数式 → 名前の定義からメニューを呼び出して設定する方法 f:id:ExcelLover:20210919180505j:plain

2つ目は名前定義したいセルを選択してから「名前ボックス」に名前を入力する方法
f:id:ExcelLover:20210919180521j:plain

3つ目は「選択範囲から作成」。

数式 → 選択範囲から作成から作成します。

下記の例の場合、E列のそれぞれのセルにD列の値が名前定義されます。 f:id:ExcelLover:20210919180545g:plain

このようになります。
f:id:ExcelLover:20210919180623j:plain

名前の使用方法

f:id:ExcelLover:20210919180643j:plain

例えば上記のような表があります。

E3・E5セル以外は値が直接入力されています。

先程3番目の方法でE列には名前が定義されていますので、これを利用して式を変更してみます。
f:id:ExcelLover:20210919180702j:plain

利益と税金の数式に名前を使用しています。

名前を使用することで参照先のセルを確認しなくても、数式だけで「利益」は「売上」から「費用」を引いているのだなということがわかります。

名前の増殖

ではどのような時に名前が増殖するのでしょうか。

例えば以下のような時に名前が増殖します

  1. シート自体を同一ブックもしくは別のブック(新規ブック含む)にコピーした時
  2. 名前が定義されたセルをコピーして別のブックにペーストした時

シート自体のコピーによる増殖

1は下記のような状態でシート自体を同一ブック内にコピーしてみます。 f:id:ExcelLover:20210919180720j:plain

そうするとこのように同じ名前で別のシートを参照する名前が作成されます。

但し新しく作成された名前は範囲がシート単位になっています。 f:id:ExcelLover:20210919180735j:plain

セルのコピーによる増殖

2は名前定義されたセルを含むセル範囲をコピーをします。 f:id:ExcelLover:20210919180750j:plain

その後、別のブックに貼り付けます。

そうすると元のブックと同じセル範囲を参照する同名の名前が作成されます。
f:id:ExcelLover:20210919180807j:plain

但し元のブックで他のセルから参照されていない名前はコピーされません。

今回の例で言えば「税金」は他のセルから参照されていないためコピーされていません。

名前の範囲

名前の範囲は以下の2つがあります。

  • ブック
  • シート

「シート」の場合名前が所属するシートを削除すると、その名前同時削除されます。

またブックとシートで同名の名前が存在する場合シートの名前が優先されます。
f:id:ExcelLover:20210919180824j:plain

名前定義エラー

ではどのような時に名前の定義がエラーになるのでしょうか。

  1. 名前が定義されているセルを削除する
  2. 名前の範囲が「ブック」の時に、名前が参照しているシートを削除した時

名前定義済みセルの削除によるエラー

下記の状態で税率のセル(D4:E4)を削除します。
f:id:ExcelLover:20210919180643j:plain

そうすると下記のように「税率」の参照先であったE4セルが削除されたため、名前「税率」が参照先エラーになっています。

また数式の中で「税率」を参照していた「税金」も「税率」がエラーになっているため、ひっぱられてエラーになっています。

セル削除によるエラーは名前の範囲がブックでもシートでも発生します。 f:id:ExcelLover:20210919180916j:plain

シートの削除によるエラー

下記の状態で名前が所属しているシートを削除するとどうなるでしょうか。

(「TEST」という名前だけ範囲をシートにしています) f:id:ExcelLover:20210919180932j:plain

範囲がシートであった「TEST」という名前はシートの削除に伴って名前も削除されています。

ところがそれ以外の範囲がブックの名前については、名前自体は削除されず参照先エラーが発生しています。 f:id:ExcelLover:20210919180947j:plain


まとめ

シートやセルのコピー削除で「名前」が自分の意識外のところで増えたり減ったとしてもExcelはなんのアラートも発してくれません。(名前が重複する時はアラートを出してくれますが)

これへの対策があるのか。

また進展があれば続編を作成したいと思います。