トップページ > Excel エクセル使いこなし技 > シート毎に同じ範囲を参照する同じ名前をつける方法

同じ範囲を参照する同じ名前を異なるシートでつける方法

シート毎に同じ範囲を参照する同じ名前をつける方法


同じワークブックにある複数のワークシートで、同じ位置にあるデータを同じ名前で参照出来れば便利な時がしばしば有ります。これをエクセルで実現するにはステップを幾つか踏む必要があります。
Parajumpers Outlaw
Parajumpers Owner
セル範囲に名前をつける時は、名前はワークブック・レベルであり、名前はワークブックのどこからであろうと、特定のワークシートにある特定のセル範囲を参照します。一度、名前が付けられると、同じ名前を別のワークシートにある範囲を表すのに使うことは出来ません。通常はこのようになりますが、これを回避する方法があります。

複数のワークシートで、ワークシート毎に同じ名前を使う方法

まず、3つのワークシートを持つワークブックがあるとします。3つのワークシートの名前は、単純に「Sheet1」「Sheet2」「Sheet3」としましょう。そして、「Sheet1」にいる時は「Sheet1」のA1:A10、「Sheet2」にいる時は「Sheet2」のA1:A10、「Sheet3」にいる時は「Sheet3」のA1:A10を参照する「集計範囲」という名前を付けたいとします。

これを行うには、まず、「Sheet1」を選択、A1:A10 を選択し、数式バーの左になる「名前ボックス」をクリックします。そして、「 Sheet1!集計範囲 」と書き、ENTERキーを押します。同じことを「Sheet2」「Sheet3」でも行い、「名前ボックス」には「 Sheet2!集計範囲 」「 Sheet3!集計範囲 」と各々入力します。

3つのワークシートで設定を終えた後に、いづれのワークシートでも、「名前ボックス」の下向きの三角マークをクリックしすれば、「 集計範囲 」という名前が1つだけあるのを確認できると思います。「集計範囲」をクリックすれば、A1:A10 に移ります。どの他のワークシート上でも同じ動きをし、どのワークシートでも、常に同じワークシートの A1:A10 に移動します。

これは、シート名と!(エクスクラメーション・マーク)を名前の前に付けたことにより出来ました。もし、ワークシート名がスペースを含む場合 ( 例:1年 1組 )、「1年 1組!集計範囲」と名前をつけることは出来ません。替わりに、「’1年 1組’!集計範囲」とワークシート名を ‘ (シングルクオテーション)で囲む必要があります。ワークシート名にスペースが入っていない場合でも、ワークシート名を ‘ (シングルクオテーション)で囲んでも使えるので常にそうしても良いかもしれません。

相対参照する名前を使う方法

名前でも相対参照を使うことができます。標準では名前を定義された範囲は絶対参照となっています。しかし、そのままにしておかなくても問題ありません。次のステップで相対参照の「名前」を試すことが出来ます。

1: セルA11を選択した状態で、「挿入」>「名前」>「定義」で「名前の定義」ダイアログボックスを表示します。
2:「名前」の下のボックスに「集計範囲」と入力します。
3:「参照範囲」に「 =A$1:A$10 」と入力し、「OK」をクリックします。
4: セルA1に「1」と入力します。
5: セルA1を選択し、セルの右下に表示される黒い四角マーク「フィルハンドル」にカーソルを移動し、マウスの左ボタンをクリックします。そして、CTRLキーを押しながら、セルA10までドラッグします。(CTRLキーを押すことで、1つづつ増える数値リストを作成することが出来ます。)
6: 次はセルB1に「1」と入力し、フィルハンドラをクリックし、セルB10までドラッグします。今回は、CTRLキーは同時には押しません。
7: セルA11に「 =SUM(集計範囲) 」と入力します。
8: セルB11に「 =SUM(集計範囲) 」と入力します。

これで、各々、55 と 10 という計算結果が返されます。これはセルA11を選択した状態で「A$1:A$10」を参照する「集計範囲」という名前を付けたからです。この名前は、列を相対参照、行は絶対参照で範囲参照します。

「集計範囲」という名前を数式で使う際は、常にその数式が入力されたセルの上にある列の1行目から10行目のセルを参照します。そして、もし、別のワークシートのセルA11で「 =SUM(集計範囲) 」と入力しても、「集計範囲」という名前を設定した時にアクティブだったワークシートの「A1:A10」を参照してしまいます。

同じ範囲を参照する同じ名前を異なるシートでつける方法

名前を使って計算をもっとシンプルにする方法

もっと計算をシンプルにしたい場合は、次のような方法があります。

1: セルA11を選択した状態で、「挿入」>「名前」>「定義」で「名前の定義」ダイアログボックスを表示します。
2:「名前」の下のボックスに「集計結果」と入力します。
3:「参照範囲」に「 =SUM(A$1:A$10) 」と入力し、「OK」をクリックします。
4: セルA1に「1」と入力します。
5: セルA1を選択し、セルの右下に表示される黒い四角マーク「フィルハンドル」にカーソルを移動し、マウスの左ボタンをクリックします。そして、CTRLキーを押しながら、セルA10までドラッグします。(CTRLキーを押すことで、1つづつ増える数値リストを作成することが出来ます。)
6: 次はセルB1に「1」と入力し、フィルハンドラをクリックし、セルB10までドラッグします。今回は、CTRLキーは同時には押しません。
7: セルA11に「 =集計結果 」と入力します。
8: セルB11に「 =集計結果 」と入力します。

これで先ほどの例と同じ計算結果をSUM関数をセルに入力せずに得れました。相対参照と絶対参照、関数をを名前に組み合わせることで、集計が便利になり、且つ、時間の短縮を図れる効率的なワークシートの作成が出来ます。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

*

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

トラックバックURL

http://excel.jiten.org/tips/same-name-diff-sheet/trackback/