定義した名前を数式やドロップダウンリストで使う

解説記事内の画像はExcel 2016のものですが、操作方法は下記のバージョンで同じです。
  • Excel 2016, 2013, 2010
Excel 2016・2013・2010用 練習ファイル
(name-use.xlsx 9.66KB)
名前の定義でセル範囲に名前をつける」から続けてご覧いただいている方は、そこで使った練習用ファイルを、そのまま続けてお使いいただけます。

定義した名前を数式やドロップダウンリストで使う01名前の定義でセル範囲に名前をつける」では、図のような表のA6からA11番地に「支店名」という名前を、

定義した名前を数式やドロップダウンリストで使う02 B6からB11番地に「全支店売上」という名前をつけてみました。
ちなみに、名前ボックスに表示される名前が欠けていてよく分からない場合は、[▼]をクリックすると

定義した名前を数式やドロップダウンリストで使う03 一覧が表示されるので、どんな名前で登録されているか確認できます。

さて、今回のお題は、セル範囲に名前をつけると、こんなに便利になっちゃうよ、というのをご紹介すること。
そして、このような名前を使い始めたら絶対に知っておきたいショートカットキーもご紹介します!

ドロップダウンリストの設定に名前を使ってみる

定義した名前を数式やドロップダウンリストで使う14 [▼]をクリックしたら選択肢の一覧を表示できるドロップダウンリスト
実はこのドロップダウンリストを設定する時にも、名前を使うことで設定が楽ちんになります。

定義した名前を数式やドロップダウンリストで使う04 今回は、B2番地のセルに、選択肢としてA6からA11番地の支店の名前が表示されるドロップダウンリストを設定したいと思います。

定義した名前を数式やドロップダウンリストで使う05 ドロップダウンリストが表示されるようにしたいのはB2番地なので、まずはB2番地を選択し、

定義した名前を数式やドロップダウンリストで使う06 [データ]タブ、[データツール]グループにある、[データの入力規則]ボタンをクリックしますが、このボタンは二層構造になっているボタンなので、[▼]じゃない絵柄の方のボタンをクリックします。

定義した名前を数式やドロップダウンリストで使う07 表示されたダイアログボックスの[設定]タブを使って、ドロップダウンリストを設定します。
[入力値の種類]で[リスト]を選択し、[元の値]の欄でクリックすると、その欄に文字カーソルを入れることができます。
そして、この[元の値]欄には、ドロップダウンリストの選択肢となるものがどこに入力されているのかを指定するので、

定義した名前を数式やドロップダウンリストで使う08 支店の名前が選択肢として表示されるよう、普段ならA6からA11番地を範囲選択して指定するわけですが、今回はその操作は不要です!
なぜなら、選択肢として表示させたいデータのあるA6からA11番地は、既に「支店名」という名前で登録されているから。

定義した名前を数式やドロップダウンリストで使う09 というわけで、ダイアログボックスの[元の値]欄に文字カーソルがある状態で、

定義した名前を数式やドロップダウンリストで使う10 [数式]タブ、[定義された名前]グループの、[数式で使用]ボタンをクリックすると、

定義した名前を数式やドロップダウンリストで使う11 その文書に登録されている名前が一覧表示されるので、今回は「支店名」をクリックで選択すると、

定義した名前を数式やドロップダウンリストで使う12 ダイアログボックスの[元の値]欄に、指定した名前を設定することができました!
これで、「支店名」という名前のついた箇所のデータを、ドロップダウンリストの選択肢として表示させてね、という設定ができたので、[OK]ボタンをクリックします。

定義した名前を数式やドロップダウンリストで使う13 設定したB2番地のセルに[▼]が表示され、その[▼]をクリックすると、

定義した名前を数式やドロップダウンリストで使う14 支店名が選択肢として表示されるようになりました!

定義した名前を数式やドロップダウンリストで使う05 【練習用ファイルを操作しながらご覧いただいている方】
次の操作のために、[元に戻す]ボタンを使って、ドロップダウンリストを設定する前の状態まで戻しておいてください。

スポンサーリンク

名前を使う時のショートカットキー

定義した名前を数式やドロップダウンリストで使う11 登録してある名前を設定に使う際、先ほどは[数式で使用]ボタンを使いましたが、ショートカットキーを使う方法もあります。

定義した名前を数式やドロップダウンリストで使う05 B2番地のセルに、もう一度、先程と同じドロップダウンリストを設定してみます。
今度はショートカットキーを使ってみますヨ。
まずは、ドロップダウンリストが表示されるようにしたいB2番地を選択し、

定義した名前を数式やドロップダウンリストで使う06 [データ]タブ、[データツール]グループにある、[データの入力規則]ボタンをクリックします。
この時、このボタンは二層構造になっているボタンなので、[▼]じゃない絵柄の方のボタンをクリックします。

定義した名前を数式やドロップダウンリストで使う07 表示されたダイアログボックスの[設定]タブで設定します。
[入力値の種類]で、[リスト]を選択し、[元の値]の欄でクリックすると、その欄に文字カーソルを入れることができます。
先程はここで名前を指定するために、[数式]タブ、[定義された名前]グループの、[数式で使用]ボタンを使ったわけですが、今回はショートカットキーを使います!

定義した名前を数式やドロップダウンリストで使う09 [元の値]欄に文字カーソルが入っていることを確認して、キーボードの[F3]キーをポンッと押すと、

定義した名前を数式やドロップダウンリストで使う15 その文書に登録されている名前が一覧になったダイアログボックスが表示されます。
「支店名」という名前をクリックで選択し、[OK]ボタンをクリックします。

定義した名前を数式やドロップダウンリストで使う12 ドロップダウンリストの設定をしているダイアログボックスの[元の値]欄に、指定した名前を設定することができました!
これで、「支店名」という名前のついた箇所のデータを、ドロップダウンリストの選択肢として表示させてね、という設定ができたので、[OK]ボタンをクリックします。

定義した名前を数式やドロップダウンリストで使う13 設定したB2番地のセルに[▼]が表示され、

定義した名前を数式やドロップダウンリストで使う14 ドロップダウンリストの設定ができました!
せっかくなので、この一覧から好きなものを選択してみてください。

定義した名前を数式やドロップダウンリストで使う16 ドロップダウンリストを使って、データを簡単に入力することができました。

スポンサーリンク

名前を数式の設定に使う

定義した名前を数式やドロップダウンリストで使う17 今度は、B3番地に、この表にある支店は全部でいくつあるのかという、全支店数を表示させたいと思います。
全支店数を数えるには、支店の名前が入力されているA6からA11番地の、セルの個数を数えればいいわけですから、使うのはCOUNTA関数
COUNT関数ではなく、COUNTの後ろに「A」が付く、COUNTA関数です。

定義した名前を数式やドロップダウンリストで使う18 それではB3番地にCOUNTA関数を設定していきましょう。
B3番地を選択し、数式バーにある[関数の挿入]ボタンをクリックします。

定義した名前を数式やドロップダウンリストで使う19 関数を指定するためのダイアログボックスが表示されるので、[関数の分類]で「すべて表示」を選択すると、すべての関数が[関数名]欄にABC順で表示されます。
「COUNTA」をこの一覧から選択しますが、この時、「COUNTA」の頭文字である「C」を頭文字に持つ関数のところまで、一瞬でスクロールする技を使うと便利です。
「COUNTA」を見つけたら、クリックで選択し、[OK]ボタンを押します。

定義した名前を数式やドロップダウンリストで使う20 関数の引数を指定するダイアログボックスが表示されるので、その[値1]の欄に、どの範囲のセルの個数を数えればいいのかを指定します。

定義した名前を数式やドロップダウンリストで使う21 今回セルの個数を数えたい範囲は、支店の名前が入力されているA6からA11番地なので、普段ならこのセルを範囲選択して指定するわけですが、今回はその操作は不要です!
なぜなら、セルの個数を数えたい範囲であるA6からA11番地は、既に「支店名」という名前で登録されているから。

定義した名前を数式やドロップダウンリストで使う20 というわけで、[値1]欄に文字カーソルが置いてある状態で、

定義した名前を数式やドロップダウンリストで使う10 [数式]タブ、[定義された名前]グループの、[数式で使用]ボタンをクリックすると、

定義した名前を数式やドロップダウンリストで使う11 その文書に登録されている名前が一覧表示されるので、今回は「支店名」をクリックで選択すると、

定義した名前を数式やドロップダウンリストで使う22 [値1]欄に「支店名」とういう名前のついた範囲を設定することができました。
ダイアログボックス右下の[OK]ボタンをクリックすると、

定義した名前を数式やドロップダウンリストで使う23 B3番地にCOUNTA関数を設定することができました!
B3番地を選択した状態で数式バーを見ると、設定したCOUNTA関数の内容を確認できます。

定義した名前を数式やドロップダウンリストで使う25 この数式を解釈すると、「「支店名」という名前がついた箇所の、空白以外のセルの個数を数えてね」となります。

定義した名前を数式やドロップダウンリストで使う24 もし、今回セルの個数を数えた範囲に「支店名」という名前をつけていなかったら、このような数式になります。

定義した名前を数式やドロップダウンリストで使う25 名前を数式で使うと、ググッと数式の意味を理解しやすくなるわけですね。
今回は簡単な数式を例にしてみましたが、複雑な数式を組む時に、その数式で使う範囲にあらかじめ名前をつけておくと、その数式が理解しやすくなるというわけです。

スポンサーリンク

数式で名前を使うと、更にこんなにいいことが!

定義した名前を数式やドロップダウンリストで使う26 今度はC6番地に、京都駅前支店の売上が、全支店の売上の中で何位なのかを表示させたいと思います。
順位をつける時に使うのがRANK関数

定義した名前を数式やドロップダウンリストで使う27 早速、C6番地にRANK関数を設定していきましょう。
まずは関数を設定したいC6番地を選択し、数式バーにある[関数の挿入]ボタンをクリックします。

定義した名前を数式やドロップダウンリストで使う28 関数を指定するためのダイアログボックスが表示されるので、[関数の分類]で「すべて表示」を選択すると、すべての関数が[関数名]欄にABC順で表示されます。
「RANK」をこの一覧から選択しますが、この時、「RANK」の頭文字である「R」を頭文字に持つ関数のところまで、一瞬でスクロールする技を使うと便利です。
「RANK」を見つけたら、クリックで選択し、[OK]ボタンを押します。

定義した名前を数式やドロップダウンリストで使う29 引数を指定するダイアログボックスが表示されるので、その[数値]欄に、順位付けしてほしい数字が入力されているセルを指定します。
今回は京都駅前支店の売上が何位なのかを知りたいわけですから、京都駅前支店の売上が入力されているB6番地をクリックで選択すると、

定義した名前を数式やドロップダウンリストで使う30 ダイアログボックスの[数値]欄にB6番地を指定することができました。
[数値]欄の設定が終わったので、キーボードの[Tab]キーをポンッと押すと、

定義した名前を数式やドロップダウンリストで使う31 次の[参照]欄に文字カーソルを移すことができました。
この[参照]欄には、全支店の売上が入力されている範囲を指定するので、

定義した名前を数式やドロップダウンリストで使う32 普段ならB6からB11番地をを範囲選択して指定するわけですが、今回はその操作は不要です!
なぜなら、全支店の売上が入力されているB6からB11番地には、既に「全支店売上」という名前がつけられているから。

定義した名前を数式やドロップダウンリストで使う31 というわけで、今回はショートカットキーを使って設定をしてみましょう。
[参照]欄に文字カーソルがあることを確認して、キーボードの[F3]キーをポンッと押すと、

定義した名前を数式やドロップダウンリストで使う33 その文書に登録されている名前が一覧になったダイアログボックスが表示されます。
今回は「全支店売上」をクリックで選択し、[OK]ボタンをクリックします。

定義した名前を数式やドロップダウンリストで使う34 [参照]欄に「全支店売上」とういう名前のついた範囲を設定することができました。
ここで、普段からRANK関数を使っている方は、この[参照]欄に指定した範囲に絶対参照を設定しなければならないと思われると思うのですが、実は今回のように名前を使って範囲を指定した場合には、絶対参照を設定する必要はありません。
その理由は後ほどゆっくりご説明しますので、まずは数式を完成させてしまいましょう。
[参照]欄の設定が終わったので、キーボードの[Tab]キーをポンッと押すと、

定義した名前を数式やドロップダウンリストで使う35 最後の[順序]欄に文字カーソルを移すことができます。
今回の順位付けは売上が多い方から1位、2位・・・としたいので、「0」を入力します。
ダイアログボックス右下の[OK]ボタンをクリックすると、

定義した名前を数式やドロップダウンリストで使う36 C6番地に、京都駅前支店の売上が、全支店の売上の中で何位なのかを表示させることができました!
C6番地を選択した状態で数式バーを見ると、設定したRANK関数の内容を確認できます。

定義した名前を数式やドロップダウンリストで使う38 この数式を解釈すると、「B6番地の数字は、「全支店売上」という名前がついている範囲の中で、大きい方から数えて何位ですか?」となります。

定義した名前を数式やドロップダウンリストで使う37 もし、「全支店売上」という名前をつけていなかったとしたら、このような数式になります。
この数式だけをパッと見た場合、「B6:B11」って何が入力されているところだっけ?と、実際の表を確認することになると思います。
そして更に、この「B6:B11」には絶対参照を設定する必要があるわけですが、

定義した名前を数式やドロップダウンリストで使う38 名前を使った数式には、「全支店売上」のところに、絶対参照を示す$マークはついていません。

定義した名前を数式やドロップダウンリストで使う02 そもそも、B6からB11番地が「全支店売上」という名前だよ、という風に登録していたということは、逆を言えば、「全支店売上」という範囲は、B6からB11番地のことでしかないわけですから、

定義した名前を数式やドロップダウンリストで使う38 数式の中に「全支店売上」という名前を使った時点で、何が何でもB6からB11番地を見に行くことになるんです。
「全支店売上」という名前がついた範囲は、絶対にB6からB11番地のことを指すので、絶対参照を指定する必要が無いというわけです。

定義した名前を数式やドロップダウンリストで使う39 というわけで、RANK関数を設定したC6番地を選択し、オートフィルすると、

定義した名前を数式やドロップダウンリストで使う40関数の数式のどこを固定すべきか判断できる人になるには」でご紹介しているような、失敗作の数式の結果になることなく、ちゃんと順位付けされます!

名前を使うとここが便利! まとめ

ここまでで、セル範囲につけた名前を、どのように利用するのか、その使用例をご紹介してきましたが、その中で便利な部分を実感していただけたと思います。

  • 様々な設定時に名前を使うと、範囲選択せずに名前を指定するだけでいいので、設定が手早く済む
  • 数式で名前を使うと、数式の意味が分かりやすくなる
  • 数式で名前を使うと、絶対参照を指定しなくてもよくなる

でも、だからと言って、何でもかんでもセルに名前をつけようということではありません。
その文書内に設定する様々な数式で、同じセル範囲を指定するなら、その範囲にあらかじめ名前をつけておくと絶対に便利ですが、特にそんなことは無いのであれば、名前をつける手順が発生するだけかえって手間が増えます。
セル範囲に名前をつけておくかどうかは、必要に応じて使い分けてください。

そして、名前を使う利点はまだこれだけではありません!
あぁ、こんな時にも威力を発揮するのか!というところを「名前をつけた範囲の変更」でもご紹介していますので、ぜひ更なる便利さを味わってみてください!

スポンサーリンク