定義した名前を数式やドロップダウンリストで使う
Excel 2016, 2013, 2010
スポンサーリンク
「名前の定義でセル範囲に名前をつける」では、図のような表のA6からA11番地に「支店名」という名前を、
B6からB11番地に「全支店売上」という名前をつけてみました。
ちなみに、名前ボックスに表示される名前が欠けていてよく分からない場合は、［▼］をクリックすると
一覧が表示されるので、どんな名前で登録されているか確認できます。
さて、今回のお題は、セル範囲に名前をつけると、こんなに便利になっちゃうよ、というのをご紹介すること。
そして、このような名前を使い始めたら絶対に知っておきたいショートカットキーもご紹介します！
スポンサーリンク
ドロップダウンリストの設定に名前を使ってみる
［▼］をクリックしたら選択肢の一覧を表示できるドロップダウンリスト。
実はこのドロップダウンリストを設定する時にも、名前を使うことで設定が楽ちんになります。
今回は、B2番地のセルに、選択肢としてA6からA11番地の支店の名前が表示されるドロップダウンリストを設定したいと思います。
ドロップダウンリストが表示されるようにしたいのはB2番地なので、まずはB2番地を選択し、
［データ］タブ、［データツール］グループにある、［データの入力規則］ボタンをクリックしますが、このボタンは二層構造になっているボタンなので、［▼］じゃない絵柄の方のボタンをクリックします。
表示されたダイアログボックスの［設定］タブを使って、ドロップダウンリストを設定します。
［入力値の種類］で［リスト］を選択し、［元の値］の欄でクリックすると、その欄に文字カーソルを入れることができます。
そして、この［元の値］欄には、ドロップダウンリストの選択肢となるものがどこに入力されているのかを指定するので、
支店の名前が選択肢として表示されるよう、普段ならA6からA11番地を範囲選択して指定するわけですが、今回はその操作は不要です！
なぜなら、選択肢として表示させたいデータのあるA6からA11番地は、既に「支店名」という名前で登録されているから。
というわけで、ダイアログボックスの［元の値］欄に文字カーソルがある状態で、
［数式］タブ、［定義された名前］グループの、［数式で使用］ボタンをクリックすると、
その文書に登録されている名前が一覧表示されるので、今回は「支店名」をクリックで選択すると、
ダイアログボックスの［元の値］欄に、指定した名前を設定することができました！
これで、「支店名」という名前のついた箇所のデータを、ドロップダウンリストの選択肢として表示させてね、という設定ができたので、［OK］ボタンをクリックします。
設定したB2番地のセルに［▼］が表示され、その［▼］をクリックすると、
支店名が選択肢として表示されるようになりました！
【練習用ファイルを操作しながらご覧いただいている方】
次の操作のために、［元に戻す］ボタンを使って、ドロップダウンリストを設定する前の状態まで戻しておいてください。
名前を使う時のショートカットキー
登録してある名前を設定に使う際、先ほどは［数式で使用］ボタンを使いましたが、ショートカットキーを使う方法もあります。
B2番地のセルに、もう一度、先程と同じドロップダウンリストを設定してみます。
今度はショートカットキーを使ってみますヨ。
まずは、ドロップダウンリストが表示されるようにしたいB2番地を選択し、
［データ］タブ、［データツール］グループにある、［データの入力規則］ボタンをクリックします。
この時、このボタンは二層構造になっているボタンなので、［▼］じゃない絵柄の方のボタンをクリックします。
表示されたダイアログボックスの［設定］タブで設定します。
［入力値の種類］で、［リスト］を選択し、［元の値］の欄でクリックすると、その欄に文字カーソルを入れることができます。
先程はここで名前を指定するために、［数式］タブ、［定義された名前］グループの、［数式で使用］ボタンを使ったわけですが、今回はショートカットキーを使います！
［元の値］欄に文字カーソルが入っていることを確認して、キーボードのF3キーをポンッと押すと、
その文書に登録されている名前が一覧になったダイアログボックスが表示されます。
「支店名」という名前をクリックで選択し、［OK］ボタンをクリックします。
ドロップダウンリストの設定をしているダイアログボックスの［元の値］欄に、指定した名前を設定することができました！
これで、「支店名」という名前のついた箇所のデータを、ドロップダウンリストの選択肢として表示させてね、という設定ができたので、［OK］ボタンをクリックします。
設定したB2番地のセルに［▼］が表示され、
ドロップダウンリストの設定ができました！
せっかくなので、この一覧から好きなものを選択してみてください。
ドロップダウンリストを使って、データを簡単に入力することができました。
スポンサーリンク
名前を数式の設定に使う
今度は、B3番地に、この表にある支店は全部でいくつあるのかという、全支店数を表示させたいと思います。
全支店数を数えるには、支店の名前が入力されているA6からA11番地の、セルの個数を数えればいいわけですから、使うのはCOUNTA関数。
COUNT関数ではなく、COUNTの後ろに「A」が付く、COUNTA関数です。
それではB3番地にCOUNTA関数を設定していきましょう。
B3番地を選択し、数式バーにある［関数の挿入］ボタンをクリックします。
関数を指定するためのダイアログボックスが表示されるので、［関数の分類］で「すべて表示」を選択すると、すべての関数が［関数名］欄にABC順で表示されます。
「COUNTA」をこの一覧から選択しますが、この時、「COUNTA」の頭文字である「C」を頭文字に持つ関数のところまで、一瞬でスクロールする技を使うと便利です。
「COUNTA」を見つけたら、クリックで選択し、［OK］ボタンを押します。
関数の引数を指定するダイアログボックスが表示されるので、その［値1］の欄に、どの範囲のセルの個数を数えればいいのかを指定します。
今回セルの個数を数えたい範囲は、支店の名前が入力されているA6からA11番地なので、普段ならこのセルを範囲選択して指定するわけですが、今回はその操作は不要です！
なぜなら、セルの個数を数えたい範囲であるA6からA11番地は、既に「支店名」という名前で登録されているから。
というわけで、［値1］欄に文字カーソルが置いてある状態で、
［数式］タブ、［定義された名前］グループの、［数式で使用］ボタンをクリックすると、
その文書に登録されている名前が一覧表示されるので、今回は「支店名」をクリックで選択すると、
［値1］欄に「支店名」とういう名前のついた範囲を設定することができました。
ダイアログボックス右下の［OK］ボタンをクリックすると、
B3番地にCOUNTA関数を設定することができました！
B3番地を選択した状態で数式バーを見ると、設定したCOUNTA関数の内容を確認できます。
この数式を解釈すると、「「支店名」という名前がついた箇所の、空白以外のセルの個数を数えてね」となります。
もし、今回セルの個数を数えた範囲に「支店名」という名前をつけていなかったら、このような数式になります。
名前を数式で使うと、ググッと数式の意味を理解しやすくなるわけですね。
今回は簡単な数式を例にしてみましたが、複雑な数式を組む時に、その数式で使う範囲にあらかじめ名前をつけておくと、その数式が理解しやすくなるというわけです。
スポンサーリンク
数式で名前を使うと、更にこんなにいいことが！
今度はC6番地に、京都駅前支店の売上が、全支店の売上の中で何位なのかを表示させたいと思います。
順位をつける時に使うのがRANK関数。
早速、C6番地にRANK関数を設定していきましょう。
まずは関数を設定したいC6番地を選択し、数式バーにある［関数の挿入］ボタンをクリックします。
関数を指定するためのダイアログボックスが表示されるので、［関数の分類］で「すべて表示」を選択すると、すべての関数が［関数名］欄にABC順で表示されます。
「RANK」をこの一覧から選択しますが、この時、「RANK」の頭文字である「R」を頭文字に持つ関数のところまで、一瞬でスクロールする技を使うと便利です。
「RANK」を見つけたら、クリックで選択し、［OK］ボタンを押します。
引数を指定するダイアログボックスが表示されるので、その［数値］欄に、順位付けしてほしい数字が入力されているセルを指定します。
今回は京都駅前支店の売上が何位なのかを知りたいわけですから、京都駅前支店の売上が入力されているB6番地をクリックで選択すると、
ダイアログボックスの［数値］欄にB6番地を指定することができました。
［数値］欄の設定が終わったので、キーボードのTabキーをポンッと押すと、
次の［参照］欄に文字カーソルを移すことができました。
この［参照］欄には、全支店の売上が入力されている範囲を指定するので、
普段ならB6からB11番地をを範囲選択して指定するわけですが、今回はその操作は不要です！
なぜなら、全支店の売上が入力されているB6からB11番地には、既に「全支店売上」という名前がつけられているから。
というわけで、今回はショートカットキーを使って設定をしてみましょう。
［参照］欄に文字カーソルがあることを確認して、キーボードのF3キーをポンッと押すと、
その文書に登録されている名前が一覧になったダイアログボックスが表示されます。
今回は「全支店売上」をクリックで選択し、［OK］ボタンをクリックします。
［参照］欄に「全支店売上」とういう名前のついた範囲を設定することができました。
ここで、普段からRANK関数を使っている方は、この［参照］欄に指定した範囲に絶対参照を設定しなければならないと思われると思うのですが、実は今回のように名前を使って範囲を指定した場合には、絶対参照を設定する必要はありません。
その理由は後ほどゆっくりご説明しますので、まずは数式を完成させてしまいましょう。
［参照］欄の設定が終わったので、キーボードのTabキーをポンッと押すと、
最後の［順序］欄に文字カーソルを移すことができます。
今回の順位付けは売上が多い方から1位、2位・・・としたいので、「0」を入力します。
ダイアログボックス右下の［OK］ボタンをクリックすると、
C6番地に、京都駅前支店の売上が、全支店の売上の中で何位なのかを表示させることができました！
C6番地を選択した状態で数式バーを見ると、設定したRANK関数の内容を確認できます。
この数式を解釈すると、「B6番地の数字は、「全支店売上」という名前がついている範囲の中で、大きい方から数えて何位ですか？」となります。
もし、「全支店売上」という名前をつけていなかったとしたら、このような数式になります。
この数式だけをパッと見た場合、「B6:B11」って何が入力されているところだっけ？と、実際の表を確認することになると思います。
そして更に、この「B6:B11」には絶対参照を設定する必要があるわけですが、
名前を使った数式には、「全支店売上」のところに、絶対参照を示す＄マークはついていません。
そもそも、B6からB11番地が「全支店売上」という名前だよ、という風に登録していたということは、逆を言えば、「全支店売上」という範囲は、B6からB11番地のことでしかないわけですから、
数式の中に「全支店売上」という名前を使った時点で、何が何でもB6からB11番地を見に行くことになるんです。
「全支店売上」という名前がついた範囲は、絶対にB6からB11番地のことを指すので、絶対参照を指定する必要が無いというわけです。
「関数の数式のどこを固定すべきか判断できる人になるには」でご紹介しているような、失敗作の数式の結果になることなく、ちゃんと順位付けされます！
名前を使うとここが便利！ まとめ
ここまでで、セル範囲につけた名前を、どのように利用するのか、その使用例をご紹介してきましたが、その中で便利な部分を実感していただけたと思います。
- 様々な設定時に名前を使うと、範囲選択せずに名前を指定するだけでいいので、設定が手早く済む
- 数式で名前を使うと、数式の意味が分かりやすくなる
- 数式で名前を使うと、絶対参照を指定しなくてもよくなる
でも、だからと言って、何でもかんでもセルに名前をつけようということではありません。
その文書内に設定する様々な数式で、同じセル範囲を指定するなら、その範囲にあらかじめ名前をつけておくと絶対に便利ですが、特にそんなことは無いのであれば、名前をつける手順が発生するだけかえって手間が増えます。
セル範囲に名前をつけておくかどうかは、必要に応じて使い分けてください。
そして、名前を使う利点はまだこれだけではありません！
あぁ、こんな時にも威力を発揮するのか！というところを「名前をつけた範囲の変更」でもご紹介していますので、ぜひ更なる便利さを味わってみてください！
スポンサーリンク
かつて、こんな後悔のご経験はありませんか？
もっと早くからパソコンを触っておけば...
今まさしく、あの時とは比べものにならない大きな進化が、今まで経験したことのない驚異的なスピードで私たちを巻き込んでいます。
そうです、AIです。
あの時の何倍も何十倍も大きな後悔をしないために、本当に必要な情報とテクニックが手に入る場所、それがBe Cool UsersのAI特化メンバーシップです！
Be Cool Users管理人の略歴・得意なことをまとめました！
お仕事のご依頼も「自己紹介・お仕事のご依頼につきまして」からご連絡くださいませ。
「こんなことできる？」もお気軽にご相談ください。
