Office TANAKA - [セルの名前について詳しく]

セルには、A1とかB3などのようにアドレスがあります。数式の中で、このアドレスを指定することで、指定したセルを参照できます。

こうしたアドレスとは別に、セルに独自の名前を定義して、その名前を数式の中で使うことができます。

名前の定義と管理

セルに名前をつける最も簡単な方法のひとつは「名前ボックス」を使うやり方です。名前を定義したいセルまたはセル範囲を選択して、名前ボックスに名前を入力します。

名前ボックスを使う方法は、簡便なやり方です。もっと詳細に名前を定義したり、すでに定義されている名前を管理するときは[名前の管理]ダイアログボックスを使います。[名前の管理]ダイアログボックスは、[数式タブ]-[定義された名前]グループにある[名前の管理]ボタンをクリックするか、Ctrl + [F3]キーを押します。

[名前の管理]ダイアログボックスの[新規作成]ボタンをクリックすると、[新しい名前]ダイアログボックスが表示されます。この[新しい名前]ダイアログボックスで、新しい名前を定義できます。

また、新しい名前を定義する[新しい名前]ダイアログボックスは、[名前の定義]ボタンをクリックするか、Ctrl + Alt + [F3]キーを押して直接開くことも可能です。

連続した複数のセルまたはセル範囲に、一気に名前を定義するときは[選択範囲から名前を作成]ダイアログボックスを使うと便利です。[選択範囲から名前を作成]ダイアログボックスを表示するには[選択範囲から作成]ボタンをクリックするか、Ctrl + Shift + [F3]キーを押します。

ちょっと、やってみましょう。下図のように選択して[選択範囲から名前を作成]ダイアログボックスを開きます。

今回は、次のように名前を定義します。

名前	セル範囲
品川区	B1:D1
世田谷区	B2:D2
太田区	B3:D3
千代田区	B4:D4

おそらくExcelが、そうした私の気持ちを察したのでしょう、最初から[左端列]チェックボックスがオンになっています。これは「選択したセル範囲の中で、左端列の名前を右側につけますよ」という意味です。これで[OK]ボタンをクリックします。実行すると次のように名前定義されます。

下図のようなマトリックス表でしたら、[左端列]と[上端列]の名前を一気に定義できます。

ちなみに、上図のように"横方向"と"縦方向"に、それぞれ名前を定義しておくと、指定した名前(のセル範囲)が交差しているセルを、次のように参照できます。

数式の中で、名前と名前の間に半角のスペースを入力しています。この半角スペースを「共通範囲の参照演算子」と呼びます。

数式で名前を使う

数式の中で名前を使うときは、セルのアドレスを指定するのと同じように、ただ名前を記述すればいいです。

上図では、セル範囲A1:A5に「Sample」という名前を定義しました。この名前を数式の中で指定してみます。「=SUM(」に続けて、名前の先頭1文字である"S"を入力すると、"S"で始まる名前がリストに表示されます。

また、参照するセルまたはセル範囲をマウスやキーボードで指定すると、名前が定義されているところは、自動的に数式の中に名前が入力されます。

あるいは、数式内の、名前を入力したい場所にカーソルを置き、[数式]タブの[数式で使用]ボタンをクリックすると、現在定義されている名前の一覧がリスト表示されますので、そのリストから選択することもできます。

さて、ここまでは、先に名前を定義しておき、後からその名前を数式の中で使うという話でした。逆のケースでは事情が異なります。

上図では、セル範囲A1:A5に名前を定義していません。このセル範囲を数式内で指定するときは、当然ですがアドレスを使います。

このように、すでに数式内で使用しているセル範囲A1:A5に、後から名前を定義してみます。

しかし、すでに数式内で使用しているアドレスは、自動的に名前には置き換わりません。これは昔からの仕様ですが、意外とやっかいです。数式で名前を活用するには、あらかじめ使用するであろうすべてのセルやセル範囲に対して、事前に名前を定義しておかなければならないのかと。それは現実的ではありません。こんなときは、便利な機能があります。[数式]タブの[名前の定義]-[名前の引用]をクリックします。

実行すると[名前の引用]ダイアログボックスが表示されます。この「名前の引用」という機能は、今回のように、すでにアドレスで指定しているセルまたはセル範囲を、後から定義された名前に置換する機能です。[オプション]ボタンをクリックすると、複雑に定義されている名前を、どのように置換するかなども指定できます。まぁ、実際にはそれほど複雑な定義をするのは希でしょうから、このダイアログボックスにあるオプションなどは、考えなくてもいいでしょう。ここで[OK]ボタンをクリックすると、数式内のアドレスが名前に置換されます。

名前の一覧

ブック内に、どんな名前が定義されているかは、名前ボックスの▼をクリックして確認できます。

このリストでは、名前を選択することで、名前が定義されているセルまたはセル範囲に、アクティブセルを移動できます。異なるワークシート間を行き来するときなどに便利です。また、定義されているすべての名前を、ワークシート上にリストとして代入することも可能です。[数式]タブの[数式で使用]をクリックし、一番下の[名前の貼り付け]をクリックするか、[F3]キーを押します。実行すると[名前の貼り付け]ダイアログボックスが表示されます。

ダイアログボックス左下にある[リスト貼り付け]ボタンをクリックすると、アクティブセルから下に、すべての名前定義が代入されます。

名前の命名ルール

定義できる名前には、次のようなルールがあります。

名前の先頭は文字または下線(_)でなければならない

先に解説した[選択範囲から作成]を実行するとき、定義して欲しい名前の先頭が数字だった場合、名前の先頭に下線(_)が自動的に付与されます。
半角全角を問わず、255文字まで
あまり長い名前は、可読性が悪くなるので使わない方がいいです。
使用できない記号もある
スペースやスラッシュ(/)は使えません。また、プラス(+)やマイナス(-)などの演算子も使えません。数式内で使用される角括弧([])や感嘆符(!)もNGです。ちょっと不思議なのは「？」は使えるのに「＊」が使えないです。どちらもワイルドカードとしては認識されないのですが、「＊」が使えないのは乗算の演算子だからですね。ちなみにピリオド(.)は使えたりします。
アドレスのような名前は使えない
「A1」や「$B$3」みたいに、セルのアドレス的なやつも定義できません。A1参照形式だけでなく、R1C1参照形式もアウトです。ちなみに、現在のExcelはXFD列まであります。XFDまで3文字のアルファベット＋1,048,576の数値はすべてアドレスですから、「乃木坂46」や「櫻坂46」はOKですけど「AKB48」や「NMB48」はNGです。
大文字と小文字は区別されない
全角と半角は区別されない

ブックレベルとシートレベル

名前には「ブックレベル」と「シートレベル」があります。ブックレベルの名前は、ブック内のすべてのワークシートで使用できますが、シートレベルの名前は、別のワークシートから参照することはできません。

名前のレベルは[名前の管理]ダイアログボックスで確認できます。

上図では、わざとすべて同じ名称の名前を定義しました。ブックレベルの名前は、ブック全体(すべてのワークシート)で使用されるため、同じ名称の名前を複数定義することはできません。しかし、シートレベルの名前は、指定したワークシート内でしか使用できないため、複数のワークシートに同じ名称の名前を定義できます。

「名前ボックス」を使って定義した名前は、必ずブックレベルになります。シートレベルの名前を定義するときは[新しい名前]ダイアログボックスの[範囲]ドロップダウンリストで使用できるワークシートを選択します。

実際に名前が定義されているセルと、そのシートレベルの名前を使用できるワークシートは、異なっていてもかまいません。ややこしいですが、Sheet1のセルA1に、Sheet2でしか使用できない名前を定義することも可能です。なお、一度定義した名前のレベルは、後から変更できません。[名前の管理]ダイアログボックスで、すでに定義されている名前を選択し、[編集]ボタンをクリックすると、名前の名称や参照先などを後から変更できますが、このとき使用できる「範囲」は編集不可です。

では、ブックレベルとシートレベルで、それぞれに同じ名称の名前を定義したらどうなるのでしょう。

上図はSheet1です。セルA1にはブックレベルで「合計」という名前を定義しています。もうひとつ、セルA2には、このSheet1でしか利用できないシートレベルの「合計」という名前を定義しました。数式内で名前を使用するため、[数式]タブの[数式で使用]ボタンをクリックすると、「合計」という名前は1つしか表示されません。

さて、この「合計」はブックレベルなのか、あるいはシートレベルなのか。選択してみましょう。

結果は「シートレベル」でした。ブックレベルとシートレベルで、同じ名称の名前が定義されているとき、シートレベルの名前を使用できるワークシートでは、シートレベルの名前が優先されます。てゆーか、そのワークシートでブックレベルの名前は使用できません。名前ボックスの▼をクリックして表示されるリストにも、シートレベルの名前しか表示されません。

名前のメリットとデメリット

さて、こうした「セルの名前」を使う、メリットとデメリットを考えてみましょう。まずはメリットから。セルに独自の名前を定義しておくと、何と言っても数式の"意味"が分かりやすいです。

上図の数式を見たとき、この数式では「ああ、売上から原価を引いているから利益を求めているのか、んで、その利益に掛け率を掛けているんだな」と、その数式が何を計算しているのかを理解できます。こうした、数式の可読性を高めるのが、最大のメリットです。単にセルのアドレスを記述しただけでは、数式の意味が分かりにくいですからね。

一方で、セルに独自の名前を定義し、その名前を数式内で使用するデメリットは、今の話とは表裏一体です。では、もしあなたが、上図の数式で「掛け率を変更したい」のように考えたとしましょう。さて、"掛け率"とは、いったいどのセルですか？もちろん、名前ボックスや[名前の管理]ダイアログボックスを確認すれば、名前が定義されているセルは分かります。しかし、そうしたExcelの操作に不慣れな方だったら。そう、数式内での名前は、数式の"意味"が分かりやすくなるというメリットの反面、その数式で参照しているセルの"場所"が分かりにくくなるというデメリットがあります。

もし、この数式が、上図のように名前を使用していなかったら、掛け率を変更するにはSheet3を開けばいいと分かります。さらに、先頭のE17はシート名を指定していませんので、この同じワークシート内だということも理解できます。

数式の"意味"を優先するのか、あるいは参照しているセルの"場所"を重要視するのか、その判断は難しいです。一方を高めると一方が下がるという関係ですので、最も大切なことは"バランス感覚"であり、どうしたら良い折り合いをつけられるかという"感性"でしょう。とはいえ、こうした「自分で考えて最適な方法を採用する」という行為は、多くのExcelユーザーが超苦手としています。今まで、数え切れないほどのユーザーにExcelを教えてきましたが、ほとんどの人が何かというと「どっちがいいんですか？教えてください！ご教授ください！」みたいに"唯一の答え"を求めてきます。そんなもの、ありませんってｗどっちがいいかは自分で考えるんです。そうした発想を持たないと、セルの名前機能を有効活用はできないでしょうし、そうした発想がないから、名前機能の便利さが分からないんです。すみません、最後は少し苦言じみましたね。まぁ、がんばってください。

名前とコピーの関係

では、最後に、多くのユーザーが理解していなくて、だから多くのユーザーが悩んでいる「セルの名前とコピーの関係」について解説します。ここでいう"コピー"は、同一ブック内でのセルコピーではなく、別のブックからコピーしてくる話です。しかも、先にネタバレしておきますが、別ブックから「セルをコピー」するのか「シートごとコピー」するのかで、結果が変わってきます。

今回は、2つのブックを扱いますし、さまざまなケースを解説しますので、少しややこしいです。落ち着いて考えてください。まず、今回使うブックは「Book1.xlsx」と「Book2.xlsx」の2つです。「Book2.xlsx」のセルやワークシートを「Book1.xlsx」にコピーします。

セルをコピーする

まず、コピー元であるBook2.xlsx側で名前を定義します。

ここでは、Sheet1のセルA1に、ブックレベルの名前「Data」を定義しました。この、名前を定義したセルA1を、Book1.xlsxのSheet1にコピーしてみます。

特に深い意味はありませんが、アドレスの異なるセルB2にコピーしてみました。どこにコピーしても同じです。さて、コピー先であるBook1.xlsxは、どうなったでしょう。

Book1.xlsxで[名前の管理]ダイアログボックスを開いてみました。名前は何も定義されていません。つまり、ブックレベルの名前が定義されたセルを、ただコピーしただけでは、コピー先ブックに名前は定義されないということです。では、シートレベルの名前ではどうでしょう。コピー元であるBook2.xlsxで、シートレベルの名前を定義してみます。

シートレベルの名前を定義したセルC1を、Book1.xlsxにコピーします。

結果は下図のとおりです。コピー先であるBook1.xlsxに名前は定義されていません。

ここまでで分かったことは「名前が定義されているセルをコピーしても、コピー先では名前が定義されない」ということ。つまり、名前は引き継がれません。

では、次です。コピー元のBook2.xlsx側で「名前が定義されているセルを数式内で参照」してみます。

これら、数式が入力されているセルを、Book1.xlsxにコピーしてみます。順番にいきましょう。

おや、何やら気になる結果になりました。コピー先のBook1.xlsxでは、次のような数式が入力されています。

さて、この名前「Data」とは、どのセルなのでしょうか。コピー先のBook1.xlsxで[名前の管理]ダイアログボックスを開いてみます。

名前の定義がコピーされました。実際に名前を定義した、コピー元のBook2.xlsxでは、「Data」というブックレベルの名前をSheet1のセルA1に定義していました。それと同じ状態になるように、コピー先のBook1.xlsxでも、Sheet1のセルA1に同じ名前が定義されています。では、シートレベルの名前を使った数式セルをコピーしてみましょう。

こちらも結果は同じです。コピー先であるBook1.xlsxに、同じようにシートレベルの名前が定義されました。ここで分かったことは「名前を使っている数式セルをコピーすると、コピー元と同じ状態に名前定義される」ということです。コピーによって定義されるセルの位置も同じです。コピー元で、セルA1に定義していた名前は、コピー先でもセルA1に定義されます。したがって、次のようにコピーすると困ったことになります。なお、この操作をする前に、コピー先Book1.xlsxでは、すべての名前定義を削除しました。

循環参照していると怒られてしまいました。循環参照とは、セルに入力した数式が、自分自身を参照している状態です。

上図では、セルA1に入力した数式で、セルA1の値に1を加えています。0に1足したら1です。でも、その1にまた1を足すのですから2になります。だけど、さらに1を加えるのですから…キリがありませんよね。これ、答えが決まりません。これが循環参照です。今回は、コピー元Book2.xlsxでSheet1のセルA1に「Data」という名前を定義しました。そのセルをBook2.xlsxにコピーすると、同じようにSheet1のセルA1に「Data」という名前が定義されます。でも、その「Data」を参照する数式をセルA1つまり「Data」にコピーしたのですから、まさに循環参照です。

ブック内に、循環参照している数式が存在すると、Excelのステータスバーに「循環参照している数式があるよ」みたいなアラートが表示されます。

このように、循環参照というのは困ったヤツなのですが、セルには入力できてしまいます。実は、この循環参照を"あえて"使って、データを分析する機能がExcelにはあります。たとえば、ゴールシークです。

循環参照は、無限回行われるから答えが決まらないのですが、これが有限回だったらいつか答えが出ます。それを利用したのがゴールシークです。なので、循環参照の数式も、セルには入力できるようになっています。

さて、ここまでは、コピー元のBook2.xlsxとコピー先のBook1.xlsxともに、同じSheet1という名前のシートを使いました。このシート名が異なっていたらどうなるでしょう。コピー先Book1.xlsxのシート名を「test」に変更して、上記「名前を使った数式が入力されているセル」をコピーしてみます。結果は次のとおり。

シート名が異なっていても、結果は同じでした。ってことはですよ、これって、こういうことですよね。

コピー元では、Sheet1のセルA1に名前を定義し、同じSheet1の別セルに入力した数式内で、この名前を使いました。その状態の数式セルを別ブックにコピーしたら、数式セルをコピーしたのと同じシート内に、名前が定義されました。だったら、コピー元ブックで、もし別シートで名前を参照していたらどうなるのでしょう。

上図右側はコピー元Book2.xlsxのSheet2です。名前を定義しているセルはSheet1です。この数式セルをBook1.xlsxにコピーしてみます。もちろん、コピー操作の前に、コピー先Book1.xlsxでは、すべての名前を削除しています。さて、数式セルをコピーすることで、コピー先Book1.xlsxには、どんな名前が定義されるのでしょうか。

結果は、上図のとおりです。分かりますか？今までと同じように、コピー先Book1.xlsxにも、名前「Data」が定義されますが、その定義先は、コピー先のBook1.xlsx内のセルではなく、コピー元Book2.xlsxのセルです。いわば、別ブックを参照している名前定義です。

実務では、別ブックのセルをコピーするという操作が、ひんぱんに行われます。きっとあなたも経験あるでしょう。そのとき、そのコピー元セルに数式が入力されていて、その数式内で名前が使われているかどうかを意識していますか？さらに、その名前が、数式セルと同じシート上で定義されているのか、あるいは別シートに定義された名前なのかを意識していますか？していませんよね。でも、それによって結果は異なるんです。このように「別ブックのセルに定義した名前」が存在すると、いろいろと困ったことが起こります。上記の解説では、Book1.xlsxとBook2.xlsxが両方Excel上に開かれています。では、Book2.xlsxを閉じてみましょう。

「別ブックのセルに定義した名前」とは、いわば"別ブックへの参照"です。別ブックへの参照は、参照しているブック(ここではBook2.xlsx)がExcel上で開かれているとき「=[Book2.xlsx]Sheet1!$A$1」のようにブック名から示されますが、参照しているブックを閉じている状態では「='C:\Work\[Book2.xlsx]Sheet1'!$A$1」とブックが保存されているパスが表示されます。今回のBook2.xlsxはC:\Workフォルダに保存していました。もしこのブックがデスクトップやドキュメントフォルダに保存されていると、パスには、Windowsのログインユーザー名や、クラウドのストレージであるOneDriveのURLなどが含まれます。あるいは、もしコピー元ブックが企業内の共有フォルダ的な場所に保存されているとしたら、ケースによっては企業内で使っているサーバー名が記録される場合もあります。まさか、ブック内にそんな情報が記録されているとは思いませんよね。でも、そのブックを第三者に渡すと、そうした情報がダダ漏れになります。悪意を持つ者は、そういう情報をもとにして攻撃をしかけてきます。

さらに、こうした「別ブックのセルに定義した名前」が存在すると、そのブックを開いたとき次のようなアラートが表示されます。

「セキュリティの警告」なんて言われると、誰でもドキッとしますよね。「リンクの自動更新が無効にされました」って。Excelで"リンク"と言えば、一般的には"外部ブックへのリンク"です。セルに入力する数式内で「=[Book2.xlsx]Sheet1!$A$1」みたいに別ブックのセルを参照している状態だけを、多くのユーザーは"リンク"だと勘違いしていますが、違います。名前定義でも"リンク"はできるんです。

上記の「セキュリティの警告」は、一度「コンテンツの有効化」をクリックすれば、次回以降は確認されません。これは、Excel 2010からの仕様です。しかし、こうした外部リンクで最も怖いことは「リンクされているブック(ここではBook2.xlsx)側では、自分がどのブックからリンクされているかが分からない」ということです。これを調べる術はありません。もし、リンクされているブックのファイル名や保存されているフォルダを変更したり、あるいはファイル自体を削除したりすると、次のようなメッセージが表示されます。

いかがですか？見覚えがある方もいるでしょう。こちらは、ブックを開くたびに毎回表示されます。「セルには数式を入れていないのに、ブックを開くたびに毎回"更新しますか？"って聞かれるんですよ～」という話を、今まで何万回聞いたことだか。理由は、安易に"別ブックからのコピー"をしたからです。

まだ終わりじゃありません。ここまでは「セルに入力した数式で、名前を使っている」という話です。しかし、定義された名前を使えるのは、何も"セルに直接入力する数式"だけではありません。

上図は、セル範囲A1:A4に「名簿」という名前を定義しました。この定義した名前を使って、入力規則の「リスト」を設定します。

入力規則を設定したセルを、別のブックにコピーします。

さて、コピー先のブックでは、

はい、「別ブックのセルに定義した名前」となります。ここでは、名前を定義したセル範囲と、入力規則を設定したセルが、同じシートでした。これは、別シートのセルでも同じことになります。まだあります。

2022年の祝日リストを作りました。A列に入力した日付のところに名前「祝日」を定義しました。この祝日リストを使って、セルに入力した日付のうち「祝日だったら文字色を赤にする」という条件付き書式を設定してみます。

まぁ、鉄板のやり方ですね。さて、このように、定義した名前を使って条件付き書式を設定したセルを、別ブックにコピーします。

はい、こうなります。

いかがですか？入力規則や条件付き書式が設定されているセルをコピーしただけで、名前の定義まで引き継ぐなんて、想像もしなかったでしょ。ここで重要なことは「他ブックからのセルコピー」というのは、いろいろなことが起きるという認識を持つことです。今回のテーマからは離れるので触れませんが、ほかにも表示形式やスタイルなどもコピーされます。みなさん、セルのコピーって"セルの中に入っている値"をコピーしているってイメージしていませんか？実際は違うんですよ。Excelって奥が深いんです。

さて、ここまでの解説では、ときどき「なお、この操作をする前に、コピー先のBook1.xlsxでは、すべての名前定義を削除しておきました」みたいなことを書きました。ここからは「じゃ、もしコピー先ブックに、同じ名称の名前定義が存在したら」どうなるのかを見てみましょう。

まずは状況を説明します。上図の右側がコピー元のBook2.xlsxです。こちらでは、セルA1に「Data」という名前を定義しています。この名前はブックレベルです。上図左側がコピー先のBook1.xlsxです。コピー先にも同じようにブックレベルの「Data」という名前を、セルB1に定義しています。先に解説しましたが、ブックレベルの名前は、ブック内でどのシートからも参照できるので、同じ名称の名前を定義することはできません。さて、コピー元で「=Data+10」という数式をセルに入力し、この数式セルをコピーします。これも上で解説しましたが、この操作をすると、コピー先のセルA1に「Data」という名前が定義されます。

「名前 'Data' は既に存在します。この名前にする場合は [はい] をクリックします。移動またはコピーを行うために 'Data' の名前を変更する場合は、[いいえ] をクリックします。」ということなんですが、この説明って分かりにくいですよね。さらに、[はい]ボタンと[いいえ]ボタンのほかに、[すべて]ボタンもあります。それぞれ、ボタンの意味と挙動をご紹介します。まずは、[はい]ボタンをクリックした場合です。

コピーしたのは「=Data+10」という数式です。この数式の中で使われている名前「Data」は、これからコピーするコピー先ブックBook1.xlsx内に、すでに存在しています。コピーに伴って、新しく別の名前「Data」を定義するのではなく、すでに存在している名前「Data」を使って、この数式を計算する、というのが[はい]ボタンです。では、[いいえ]ボタンをクリックしたら、どうなるのでしょう。次のダイアログボックスが表示されます。

すでに存在する名前「Data」ではなく、もうひとつ別の名前を定義して、コピーする数式では、そちらの新しい名前で計算する、というのが[いいえ]ボタンです。もちろん、既存の「Data」と同じ名称の名前は定義できませんから、じゃ、新しく定義する名前を入力してね、という意味のダイアログボックスです。「移動またはコピーを行うために」っていう文言が分かりにくいですね。ここで新しい名前を指定すると、次のようになります。

ちょっと余談ですけど。上記の画像をキャプチャするために、"Data2"という名前を新しく定義しようとしたら、次のように怒られました。

しかし、コピー先Book1.xlsxの[名前の管理]ダイアログボックスを見ても、"Data2"という名前は定義されていません。試しに"Data3"としたら定義できました。なぜ、存在しないはずの"Data2"が拒否られたのか？実はExcelでは「非表示の名前」というのも定義できます。一般的には、Excelが内部で使用する名前として、こっそり定義されます。手動操作では定義できません。でも、もしかしたら、"Data2"という非表示の名前定義が存在するのだろうかと、VBAで調べてみました。

この「1」というのは、セルB1に定義した「Data」のことです。それ以外に非表示の名前定義は存在していません。おそらく、何らかの不具合だと思います。まぁ、こんなこともあります。別に驚きません。たいていこういうときは、ブックを上書き保存するか、開き直すと解決します。ちなみに今回は、上書き保存ではダメで、開き直したら"Data2"を定義できるようになりました。

では最後に、[すべて]ボタンをクリックすると、いったいどうなるのでしょう。まずは次のようにしました。

コピー元ブックBook2.xlsx側で、もうひとつ別の名前を定義しました。セルC1に「Sample」という名前です。セルB3には、これら2つの名前を使った数式が入力されています。この数式セルをコピーします。コピー先のBook1.xlsxでは、セルB1に「Data」という名前が定義されています。上で解説した"既存の名前"です。一方の「Sample」という名前は定義されていません。さて、コピーすると、どうなるでしょう。

ここで、[すべて]ボタンをクリックします。

バッティングする名前「Data」は自動的に既存のものが使われ、コピー先に存在しない「Sample」は新しく定義されました。まぁ、この挙動自体は納得いくんですけど、これを[すべて]の一言で片付けるのは如何なものかと。想像できますか？この動作を表現するのなら[すべて自動]とか[すべておまかせ]みたいな感じですかね。バッティングしている名前に関しては、ひとつずつ確認されますので、もし数式内で大量の名前を使っていて、コピー先にも既存の名前が大量に定義されていて、バッティングしている名前はすべて既存のを使いたいと。それらを、ひとつずつ指定するのは面倒くさい、みたいなときに役立つボタンですね。つまり、めったに使わないボタンだということです。

シートごとコピーする

ここまでで、おそらく相当に"お腹いっぱい"の状態だと思います。しかし、さらに追い打ちをかけます。ここまでは、セルをコピーする話でしたが、今度はシートごとコピーします。なお、ここからの検証を進めるにあたって、あらためて新規ブックとしてBook1.xlsxとBook2.xlsxを作りました。これから、Book2.xlsxのシートを、Book1.xlsxにコピーしてみます。

まず、コピー元Book2.xlsxでの操作です。セルA1にブックレベルの名前「Data」を定義しました。

さて、このBook2.xlsxには、Sheet1とSheet2という2枚のシートがあります。Sheet2には何もしていません。未使用です。大事なことなのでもう一度言います。Sheet2は未使用のシートです。この未使用のSheet2を、シートごとBook1.xlsxにコピーします。みなさんは、シートごと別ブックにコピーするとき、どういう操作をしますか？ブックの画面を並べて、シート見出しをドラッグ＆ドロップしますかね？もちろん、それでもかまいません。今回は画面的に分かりやすく、シート見出しの右クリックメニューから実行してみます。

表示される[移動またはコピー]ダイアログボックスでは、[移動先ブック]で「Book1.xlsx」を選択します。[コピーを作成する]チェックボックスをオンにしましたので、シートの移動ではなく、シートのコピーが行われます。

[OK]ボタンをクリックしました。特に問題なく、Sheet2がBook1.xlsxにコピーされています。繰り返しますが、今回のコピー先であるBook1.xlsxは新規ブックです。何もしていません。真っ新なブックです。さて、Sheet2をコピーしたBook1.xlsxで、[名前の管理]ダイアログボックスを開いてみましょう。

何もありません。ん？まさか、この操作だけで名前定義もコピーされちゃうと思いましたｗ？それほど甘くないです。では次に、もう一度Book2.xlsxのSheet1を開きます。どこでもいいんですけど、どこかのセルで、セルA1に定義している「Data」を参照してみます。この参照式は、Sheet1で入力しています。

さあ、この状態で"真っ新な"未使用のSheet2を、もう一度Book1.xlsxにシートごとコピーしてみます。

ちなみに、Book1.xlsx側は、こんな感じになります。

では、コピー先のBook1.xlsxで、[名前の管理]ダイアログボックスを開いてみましょう。

あ…ありのまま、いま起こった事を話します。未使用のSheet2をシートごとコピーしたら「別ブックのセルに定義した名前」が登場しました。しかも、一度目はなかったのに、二度目にコピーしたときに。な…何を言っているのか分からねえと思いますが、私も何が起きたのか分かりません。

今の操作を、正確に考えてみます。

真っ新な新規ブックとして、Book1.xlsxとBook2.xlsxを用意した
Book2.xlsxのSheet1に「Data」というブックレベルの名前を定義した
Book2.xlsxのSheet2(未使用)をBook1.xlsxにシートごとコピーした
Book1.xlsxには名前定義がない
Book2.xlsxのSheet1で名前「Data」への参照式を入力した
Book2.xlsxのSheet2(未使用)をBook1.xlsxにシートごとコピーした
Book1.xlsxに「別ブックのセルに定義した名前」が登場した

ということです。この解説を読んで、実際に試してみる方は、手順を間違えないようにしてください。私も、何度か試してみましたが、そのつど、名前定義を削除したり、Excelを起動し直したりしました。この現象のポイントは2つあります。ひとつめは「名前を定義していないシートを別ブックにコピーしたら、コピー先に名前が定義された」ということです。上記の解説で、数式内で名前を参照しているセルをコピーしたとき、コピー先にも名前が定義されました。それは分かります。でも、そうした数式が存在しない、未使用のシートをコピーしただけで名前定義が引き継がれるということです。そんなこと、知ってる人はほとんどいないでしょう。しかも、注目すべきふたつめのポイントは、この現象が"毎回起こるのではない"ということです。「コピー元ブックで定義した名前(今回はData)を使っていない(一度も参照していない)ときは名前定義がコピーされず、一度でも参照するとコピーされる」ということ。これは厄介です。何か変なことが起こったとき、一番困るのは"ときどき起きる"という現象です。もちろん、Excelもプログラムである限り、発生因子は必ずあるはずです。しかし、そうした原因を特定できないと、注意したり改善することは難しいです。

まぁ、実務的には、定義した名前を使わないというのは考えにくいです。名前は、それを使うから定義するはずです。であるなら、一般的には、この現象は毎回起きると考えてもいいでしょう。名前定義とは関係のないシートをコピーしても、名前定義までコピーされることがあると。しかもその名前は「別ブックへの参照」です。意図しない「別ブックへの参照」があると、いろいろと困るのは上述したとおりです。この件も、根本的な解決策はありません。そりゃそうです。これはバグではなく、Excelの仕様なのですから。こうして名前定義がコピーされないと困るケースだってあるはずです。じゃ、われわれExcelユーザーは、どうすべきかと言えば、とにかく名前に気をつけるという意識を持つしかありません。別ブックから、セルやシートをコピーするときは「むむっ！ちょっと待てよ、名前は大丈夫か？」って、気にしてください。コピーする前と、コピーした後は、必ずCtrl + [F3]を押して、どんな名前が定義されているか見るクセをつけてください。

本稿は、タイトルを「セルの名前について詳しく」としましたので、詳しく書きました。「俺、Excelはけっこう知ってるし」みたいな方は、途中まで「ああ、知ってる～」って思ったかも知れません。でも、セルやシートをコピーしたときの挙動とか、同じ名称の名前が存在したときの動作とか、別ブックへの参照になってしまう件などは、おそらく正確にはご存じなかったのではないかと。本当を言えば、書きたいことはまだあります。テーブルと名前の関係とか、Excelが内部で使う非表示の名前とか、その非表示の名前を使うテクニックとか、数値や文字列や計算式に名前を定義する方法とか、VBAで名前を扱うときのポイントなどなど。でも、そのへんの話は、別のコンテンツとしてやりましょう。おそらくこのコンテンツ、Office TANAKAサイトの中で最も長い話かもしれません。最後まで読んだ方、お疲れ様でした。