名前をつけた範囲の変更
Excel 2016, 2013, 2010
スポンサーリンク
「名前の定義でセル範囲に名前をつける」では、図のような表のA6からA11番地に「支店名」という名前を、
B6からB11番地に「全支店売上」という名前をつけてみました。
そして、「定義した名前を数式やドロップダウンリストで使う」では、登録しておいた名前を、ドロップダウンリストの設定に使ったり、
数式に使うことで、便利さを実感していただきました。
また、普段なら数式の設定に絶対参照が必要なケースでも、名前を使うことで絶対参照の設定が不要になって、数式の設定が簡単になることも、「定義した名前を数式やドロップダウンリストで使う」のページで実感していただきました。
目次
スポンサーリンク
データが増えたり減ったりして、名前をつけておいた範囲を変更したい時には
そんな、なかなかツカエる名前ですが、新たなデータが増えたので、その名前をつけておいた範囲を変更したい場合にはどうすればいいでしょう？
図のように、支店が増えたので、管理するデータが増えたなんて、あるあるです。
【練習用データを操作しながらご覧いただいている方】
図のようにデータを追加してみてください。
データだけ入力できればそれでいいので、増やした箇所の罫線や行の高さなどまで整えなくても大丈夫です。
実際に「支店名」という名前で登録してある範囲はA6からA11番地ですし、「全支店売上」という名前で登録してある範囲もB6からB11番地です。
データが増えたので、それぞれの名前に登録してあるセル範囲も、データを追加したセルも含むよう変更したいところ。
それでは早速、その変更の操作をしてみましょう！
どのセルが選択されていても構わないので、［数式］タブ、［定義された名前］グループの［名前の管理］ボタンをクリックすると、
その文書に登録されている名前が一覧になったダイアログボックスが表示されます。
まずは範囲の変更を行いたい名前を選択します。
今回は「支店名」という名前も、「全支店売上」という名前も範囲の変更を行いたいので、どちらから変更を行っていもいいのですが、今回は最初に「支店名」という名前のついた範囲を変更してみることにしましょう。
一覧で「支店名」という名前をクリックで選択すると、ダイアログボックス下部の［参照範囲］に、その名前に登録されている範囲が表示されます。
その［参照範囲］に表示されているものを拡大したのがこれ。
「！」は「シート」という意味、「：」は「から」という意味なので、選択した「支店名」という名前の範囲は、「Sheet1のA6番地からA11番地ですよ」ということになります。
この範囲を変更したいので、［参照範囲］の右端にある
をクリックすると、
ダイアログボックスが小さくなり、現在その名前に登録されている範囲が、「アリの行列」のようなグルグル動く点線で囲まれます。
今回は、A6からA12番地を正しい範囲としたいので、そのA6からA12番地を範囲選択します。
この時、せっかくダイアログボックスが小さくなって範囲選択しやすくなったのに、それでも範囲選択するセルのところにダイアログボックスが覆いかぶさってしまい、範囲選択できない場合は、ダイアログボックスを移動してから、範囲選択します。
改めて範囲選択したことで、ダイアログボックスに表示されている範囲を変更することができました！
ダイアログボックスを元の大きさに戻すために、右端にあるボタンをクリックすると、
ダイアログボックスを元の大きさに戻すことができます。
でもここで気を抜いてはいけません！
この変更を有効にするため、図のようなチェックマークのボタンをクリックすると、ここで初めて変更が有効になります！
続いて、「全支店売上」という名前で登録されている範囲も変更したいので、一覧から「全支店売上」をクリックで選択します。
そう、ダイアログボックスをいちいち閉じなくても、変更の操作を続けて行うことができるんです。
一覧で「全支店売上」という名前を選択したので、ダイアログボックス下部の［参照範囲］に、その名前に登録されている範囲が表示されます。
この範囲を変更したいので、［参照範囲］の右端にあるをクリックすると、
ダイアログボックスが小さくなり、現在その名前に登録されている範囲が、「アリの行列」のようなグルグル動く点線で囲まれます。
今回は、B6からB12番地を正しい範囲としたいので、そのB6からB12番地を範囲選択します。
この時、せっかくダイアログボックスが小さくなって範囲選択しやすくなったのに、それでも範囲選択するセルのところにダイアログボックスが覆いかぶさってしまい、範囲選択できない場合は、ダイアログボックスを移動してから、範囲選択します。
改めて範囲選択することで、ダイアログボックスに表示されている範囲を変更することができました！
ダイアログボックスを元の大きさに戻すために、右端にあるボタンをクリックすると、
ダイアログボックスを元の大きさに戻すことができます。
繰り返しますが、ここで気を抜いてはいけません！
この変更を有効にするため、図のようなチェックマークのボタンをクリックすると、ここで初めて変更が有効になります！
これで、範囲を変更したかったもの全ての変更が終わったので、最後に［閉じる］ボタンをクリックしてダイアログボックスを閉じます。
スポンサーリンク
名前をつけた範囲を変更するだけで、あらゆる箇所の表示が変わった！
データの追加に合わせて、名前に登録されている範囲を変更しただけなのに、な、な、なんと！
その名前を使っている数式の結果が変わりました！
支店が追加された分、全支店数を表示しているB3番地も、順位を表示しているC6からC11番地も、それに合わせて結果の表示が変わっています！
なぜ数式の結果が変わったのか？
もうお気付きの方もいらっしゃいますね？
例えば、B3番地を選択すると、そのセルに設定されている数式の中身を数式バーで確認できるわけですが、B3番地に設定されている数式は「「支店名」という名前のついた箇所の、空白以外のセルの個数を数えてね」という意味であり、けして「何番地から何番地までのセルの個数を数えてね」という数式ではありません。
更に、順位欄の先頭のセルであるC6番地を選択して、数式バーでそのセルの数式の中身を確認してみます。
やはりこちらも、「「全支店売上」という名前のついた中で順位付けしてね」という意味で、けして「何番地から何番地の中で順位付けしてね」という数式ではありません。
数式自体が、登録された名前を見に行って計算しているので、大元となる名前がついた範囲を変更したら、それに合わせて計算結果も変わったというわけです！
もちろん、これは数式以外の箇所も同様で、B2番地のドロップダウンリストも、「「支店名」という名前のついた箇所を、リストの一覧として表示してね」という設定になっているので、ドロップダウンリストの［▼］をクリックすると、
リストに表示される支店名も、新たに追加した支店まで表示されるようになっています！
「定義した名前を数式やドロップダウンリストで使う」では、名前を使う利点をいくつかご紹介していますが、今回ご紹介したような、
名前に登録された範囲を変更するだけで、その名前を使った数式や設定の結果表示まで一気に変わる
というのも、名前を使う利点で、これが最大の利点と言っても過言ではありません！
名前の変更とは関係ないのですが、気になる方もいらっしゃると思うので、追加した支店の欄にも順位が表示されるようにし、表を完成させることにしましょう。
このC12番地の数式は、1つ上のC11番地の数式をコピーすればいいだけなので、
コピー元となる数式が入っているC11番地を選択し、
オートフィルすれば、
追加した欄にも数式を埋めることができました。
スポンサーリンク
利点がいまいち実感できなければ、あえて不便さを実感してみよう
さて、前の章では、
名前に登録された範囲を変更するだけで、その名前を使った数式や設定の結果表示まで一気に変わる
という、名前を使う最大の利点と言っても過言ではない利点をご紹介しましたが、中にはこの利点を実感できない方もいらっしゃるかもしれません。
そんな場合もご心配なく。
「便利さ」を実感できなければ、「不便さ」を実感すれば、おのずと「うわぁ、これ便利〜」ってなります。
【練習用データを操作しながらご覧いただいている方】
画面左下のシート見出しをクリックして、「Sheet2」に切り替えておいてください。
この表は、先程までと同じ作りの表ですが、支店の名前が入力されているセル、売上が入力されているセルそれぞれに、名前を登録していません。
名前を登録していないので、B2番地のドロップダウンリストの設定にも、当然名前を使っていません。
実際にその設定を確認してみましょう。
B2番地を選択し、
［データ］タブ、［データツール］グループにある、［データの入力規則］ボタンをクリックしますが、このボタンは二層構造になっているボタンなので、［▼］じゃない絵柄の方のボタンをクリックします。
表示されたダイアログボックスの［設定］タブで、［入力値の種類］が［リスト］になっていることから、これがドロップダウンリストの設定であることが分かります。
このダイアログボックスの［元の値］欄が、リストで一覧表示されるデータの在りかを設定するところなわけですが、ここには「A6からA11番地」のデータが表示されるように設定されていることが分かります。
名前ではなく、セル番地で指定されているわけですね。
確認が終わったら、［キャンセル］ボタンをクリックして、ダイアログボックスを閉じておきます。
B3番地に設定されている数式も確認してみましょう。
B3番地を選択すると、数式バーでそのセルに入力されている数式の中身を確認できます。
このセルには、「A6からA11番地」の空白以外のセルの個数を数えるよう、数式が設定されています。
やはりこの数式も、名前ではなく、セル番地で指定されています。
更に、順位欄の先頭のセルであるC6番地を選択して、数式バーでそのセルの数式の中身を確認してみましょう。
ここに設定されている数式は、「B6からB11番地」の中で順位付けするよう、数式が設定されています。
やはりこの数式も、名前ではなく、セル番地で指定されています。
ここで新たに支店名と、その売上の数字を追加しました。
追加入力したことで、Excelの「リストオートフィル」という機能が働き、C12番地に自動的に数式が入力されますが、エラーメッセージが出ています。
これについては後でご説明しますので、今は先に進みます。
【練習用データを操作しながらご覧いただいている方】
データだけ入力できればそれでいいので、増やした箇所の罫線や行の高さなどまで整えなくても大丈夫です。
名前を登録していた表の場合は、この後、データを追加したところも範囲に含むよう、名前に登録された範囲の変更を行ったわけですが、今回は名前を登録すらしていないので、当然その操作は必要ありません。
データを追加したので、追加したデータも含めて設定されなければならない箇所を、もう一度確認していきましょう。
ドロップダウンリストが設定してあるB2番地を選択して、
［データ］タブ、［データツール］グループにある、［データの入力規則］ボタンをクリックしますが、このボタンは二層構造になっているボタンなので、［▼］じゃない絵柄の方のボタンをクリックします。
ダイアログボックスの［元の値］欄には、「A6からA11番地」というセル番地のデータがリストとして表示されるように設定されていますが、増えたA12番地のデータも含むよう、この設定を変更しなければなりません。
設定にセル番地を使っているので、その設定で使われる範囲を変えたければ、その設定を直接編集するしかないからです。
ところがもし、「「支店名」という名前のついた箇所を、リストの一覧として表示してね」のように、設定に名前を使っていれば、ドロップダウンリストには常に「支店名」という名前が付いた範囲のデータが表示されるので、支店のデータが増えようが減ろうが、名前に登録された範囲を変更するだけいいので、そもそもこの設定を直接変更する必要はありません。
今回はダイアログボックスの設定を変更せず、確認しただけで先に進みましょう。
［キャンセル］ボタンをクリックして、ダイアログボックスを閉じておいてください。
今度は、B3番地に設定してある関数。
B3番地を選択して、数式バーでそのセルの中身を確認します。
このセルには、「A6からA11番地」というセル番地の範囲で、空白以外のセルの個数を数えるよう数式が設定されているため、増えたA12番地のデータも個数に含まれるよう、この数式を変更しなければなりません。
数式にセル番地を使っているので、計算に使われる範囲を変えたければ、その数式を直接編集するしかないからです。
もし、「「支店名」という名前のついた箇所の、セルの個数を数えてね」のように、設定に名前を使っていれば、このセルには常に「支店名」という名前が付いた範囲のセルの個数が表示されます。
支店のデータが増えようが減ろうが、名前に登録された範囲を変更するだけいいので、そもそもこの数式を直接修正する必要はありません。
C12番地は、データを追加した時に、リストオートフィルというExcelの機能が自動的に働いて、勝手に入力されたものです。
そのC12番地を選択して、数式バーを見ると、「B12番地に入力されている売上は、B6からB11番地の売上の中で何位ですか」という数式になっていることが分かります。
全支店の売上の中で順位付けしたいのに、追加したB12番地の売上を含まず順位付けするよう設定されてしまっています。
これは、「順位」欄の先頭であるC6番地の数式も同様で、「B6からB11番地」というセル番地を使って順位付けするよう設定されているため、増えたB12番地のデータも順位付けに含まれるよう、この数式も変更しなければなりません。
数式にセル番地を使っているので、計算に使われる範囲を変えたければ、その数式を直接編集するしかないからです。
でも、もし、「「全支店売上」という名前のついた中で順位付けしてね」のように、数式に名前を使っていれば、セルには常に「全支店売上」という名前が付いた中での順位が表示されます。
支店のデータが増えようが減ろうが、名前に登録された範囲を変更するだけいいので、そもそも数式を直接修正する必要はありません。
と、ここまで見ていただいたように、数式や設定でセル番地を指定している場合には、「このセル番地を見に行ってくださいね」という指定になっているため、データが増えたり減ったりすると、その範囲を使っている数式1つ1つ、設定1つ1つをいちいち変更していかなければなりません。
それに対して名前を使っていれば、「この名前の部分を見に行ってくださいね」という指定になるため、データが増えたり減ったりしたとしても、名前の範囲の変更をするだけで良く、数式や設定を1つ1つ変更する必要が無いというわけなんです。
今回の支店のように、その支店そのものが増減する可能性があって、尚且つそのデータを基に様々な数式や設定を行う場合には、名前を使うと変更の手間を一気に減らせるというわけです！
スポンサーリンク
かつて、こんな後悔のご経験はありませんか？
もっと早くからパソコンを触っておけば...
今まさしく、あの時とは比べものにならない大きな進化が、今まで経験したことのない驚異的なスピードで私たちを巻き込んでいます。
そうです、AIです。
あの時の何倍も何十倍も大きな後悔をしないために、本当に必要な情報とテクニックが手に入る場所、それがBe Cool UsersのAI特化メンバーシップです！
Be Cool Users管理人の略歴・得意なことをまとめました！
お仕事のご依頼も「自己紹介・お仕事のご依頼につきまして」からご連絡くださいませ。
「こんなことできる？」もお気軽にご相談ください。
