範囲名の操作

データ分析を行っているときに、参照がセル参照ではなく名前(単一のセルまたはセルの範囲)である場合、さまざまなデータを参照する方が意味があり、簡単です。たとえば、割引率と一連のキャッシュフローに基づいて正味現在価値を計算する場合、次の式は

Net_Present_Value = NPV (Discount_Rate, Cash_Flows)

よりも意味があります

C10 = NPV(C2、C6:C8)

Excelを使用すると、データのさまざまな部分に意味のある名前を作成して使用できます。範囲名を使用する利点は次のとおりです。

  • 意味のある範囲名(Cash_Flowsなど)は、範囲アドレス(C6:C8など)よりもはるかに覚えやすいです。

  • 名前を入力すると、セルまたは範囲のアドレスを入力するよりもエラーが発生しにくくなります。

  • 数式に名前を間違って入力すると、Excelは #NAME? エラー。

  • 定義された名前を使用して、ワークシートの領域にすばやく移動できます。

  • 名前を使用すると、数式がより理解しやすく、使いやすくなります。たとえば、式Net_Income = Gross_Income – Deductionsは、C40 = C20 –B18よりも直感的です。

  • 範囲名を使用した数式の作成は、セルアドレスまたは範囲アドレスを使用するよりも簡単です。数式のオートコンプリートを使用して、セル名または範囲名を数式にコピーできます。

この章では、次のことを学びます。

  • 名前の構文規則。
  • セル参照の名前を作成します。
  • 定数の名前を作成します。
  • 名前の管理。
  • 定義した名前の範囲。
  • 名前の編集。
  • 名前のフィルタリング。
  • 名前の削除。
  • 名前を付ける。
  • 数式で名前を使用する。
  • ブック内の名前の表示。
  • 貼り付け名と貼り付けリストを使用します。
  • 範囲の交差に名前を使用する。
  • 名前付きの数式をコピーします。

数式オートコンプリートを使用して名前をコピーする

数式に名前の最初の文字を入力します。ドロップダウンボックスが表示され、関数名と範囲名が示されます。必要な名前を選択します。数式にコピーされます。

範囲名の構文規則

Excelには、名前について次の構文規則があります-

  • 文字、数字、記号(アンダースコア、バックスラッシュ、ピリオド)を任意に組み合わせて使用​​できます。他の記号は使用できません。

  • 名前は、文字、アンダースコア、または円記号で始めることができます。

  • 名前を数字で始めたり(例-1stQuarter)、セルアドレスに似たりすることはできません(例-QTR1)。

  • このような名前を使用する場合は、名前の前にアンダースコアまたはバックスラッシュを付けます(例-\ 1stQuarter、_QTR1)。

  • 名前にスペースを含めることはできません。名前の2つの単語を区別したい場合は、アンダースコアを使用できます(例-キャッシュフローの代わりにCash_Flows)

  • 定義した名前は、Excelの内部で定義された名前(次のような)と衝突しないようにする必要があります。 Print_Area, Print_Titles, Consolidate_Area, and Sheet_Title。同じ名前を定義すると、Excelの内部名が上書きされ、エラーメッセージは表示されません。ただし、そうしないことをお勧めします。

  • 最大255文字を使用できますが、名前は短くてもわかりやすいものにしてください。

範囲名の作成

範囲名は2つの方法で作成できます-

  • を使用して Name box

  • を使用して New Name ダイアログボックス。

  • を使用して Selection ダイアログボックス。

名前ボックスを使用して範囲名を作成する

範囲名を作成するには、 Name数式バーの左側にあるボックスが最速の方法です。以下の手順に従ってください-

Step 1 −名前を定義する範囲を選択します。

Step 2 − [名前]ボックスをクリックします。

Step 3 −名前を入力し、Enterキーを押して名前を作成します。

[新しい名前]ダイアログボックスを使用して範囲名を作成する

[数式]タブの[新しい名前]ダイアログボックスを使用して、範囲名を作成することもできます。

Step 1 −名前を定義する範囲を選択します。

Step 2 − [式]タブをクリックします。

Step 3−「定義済みの名前」グループで「名前の定義」をクリックします。ザ・New Name ダイアログボックスが表示されます。

Step 4 − [名前]の横のボックスに名前を入力します

Step 5−選択して[参照]ボックスに表示されている範囲が正しいことを確認します。[OK]をクリックします。

[選択から名前を作成]ダイアログボックスを使用して範囲名を作成します

を使用して範囲名を作成することもできます Create Names 範囲に隣接するテキスト値がある場合は、[数式]タブの[選択]ダイアログボックスから。

Step 1 −名前を含む行/列とともに、名前を定義する範囲を選択します。

Step 2 − [式]タブをクリックします。

Step 3 −クリック Create from Selection[定義済みの名前]グループにあります。ザ・Create Names from Selection ダイアログボックスが表示されます。

Step 4 −選択範囲の一番上の行にテキストが表示されるので、一番上の行を選択します。

Step 5−選択され、[参照]の横のボックスに表示された範囲が正しいことを確認します。[OK]をクリックします。

これで、=の範囲で最大値を見つけることができます。Sum(学生名)、以下に示すように-

複数選択して名前を作成することもできます。以下の例では、各生徒のマークの行に生徒の名前を付けることができます。

これで、=を使用して各学生の合計点数を見つけることができます。Sum (学生名)、以下のように。

定数の名前の作成

ブック全体で使用される定数があるとします。セルに配置せずに、直接名前を割り当てることができます。

以下の例では、貯蓄銀行の金利は5%に設定されています。

  • [名前の定義]をクリックします。
  • [新しい名前]ダイアログボックスで、[名前]ボックスに「Savings_Bank_Interest_Rate」と入力します。
  • [スコープ]で、[ワークブック]を選択します。
  • [参照]ボックスで、内容をクリアして5%と入力します。
  • [OK]をクリックします。

名前 Savings_Bank_Interest_Rate一定の5%に設定されます。これは、NameManagerで確認できます。値が0.05に設定されていることがわかります。Refers to = 0.05が配置されます。

名前の管理

Excelブックには、名前付きのセルと範囲をいくつでも含めることができます。これらの名前は、名前マネージャーを使用して管理できます。

  • [数式]タブをクリックします。

  • クリック Name Manager の中に Defined Namesグループ。ザ・Name Managerダイアログボックスが表示されます。現在のブックで定義されているすべての名前が表示されます。

のリスト Names 定義された状態で表示されます Values, Cell Reference (シート名を含む)、 Scope そして Comment

ネームマネージャーには、次のオプションがあります。

  • を定義する New の名前 New ボタン。

  • Edit 定義された名前。

  • Delete 定義された名前。

  • Filter カテゴリ別の定義済みの名前。

  • 定義された名前の範囲を変更する Refers to

名前の範囲

ザ・ Scopeデフォルトで名前のはワークブックです。あなたは見つけることができますScope 下の名前のリストから定義された名前の Scope の列 Name Manager

あなたは定義することができます ScopeNew Name を使用して名前を定義する場合 New Nameダイアログボックス。たとえば、Interest_Rateという名前を定義しています。次に、あなたはそれを見ることができますScopeNew Name Interest_Rateは Workbook

あなたが欲しいとしましょう Scope これに制限されたこの金利の Worksheet のみ。

Step 1−スコープボックスの下矢印をクリックします。使用可能なスコープオプションがドロップダウンリストに表示されます。

スコープオプションには次のものがあります Workbook、およびブック内のシート名。

Step 2−現在のワークシート名(この場合はNPV)をクリックし、[OK]をクリックします。ワークシートタブでシート名を定義/検索できます。

Step 3 −スコープがワークシートであることを確認するには、をクリックします Name Manager。[スコープ]列には、Interest_RateのNPVがあります。つまり、名前Interest_Rateは、ワークシートNPVでのみ使用でき、他のワークシートでは使用できません。

Note −名前のスコープを定義すると、後で変更することはできません。

エラー値のある名前の削除

場合によっては、さまざまな理由で名前定義にエラーが発生することがあります。次のように名前を削除できます-

Step 1 −クリック Filter の中に Name Manager ダイアログボックス。

次のフィルタリングオプションが表示されます-

  • フィルターをクリア
  • ワークシートにスコープされた名前
  • ワークブックにスコープされた名前
  • エラーのある名前
  • エラーのない名前
  • 定義された名前
  • テーブル名

応募できます Filterdefined Names これらのオプションの1つ以上を選択することによって。

Step 2 −選択 Names with Errors。エラー値を含む名前が表示されます。

Step 3 −取得したリストから Names、削除したいものを選択してクリックします Delete

削除を確認するメッセージが表示されます。[OK]をクリックします。

名前の編集

あなたは使用することができます Edit のオプション Name Manager ダイアログボックスから-

  • 変更 Name

  • を変更します Refers to 範囲

  • 編集する CommentName

名前を変更する

Step 1 −関数を含むセルをクリックします Large

ご覧のとおり、さらに2つの値が配列に追加されていますが、それらはArray1の一部ではないため、関数には含まれていません。

Step 2 −をクリックします Name で編集したい Name Managerダイアログボックス。この場合、Array1

Step 3 −クリック Edit。ザ・Edit Name ダイアログボックスが表示されます。

Step 4 −変更する Name に必要な新しい名前を入力して Name Box

Step 5 −をクリックします Range の右側にあるボタン Refers to ボックスに入れて、新しいセル参照を含めます。

Step 6 −を追加します Comment (オプション)

そのことに注意してください Scope は非アクティブであるため、変更できません。

[OK]をクリックします。加えられた変更を観察します。

名前の適用

次の例を考えてみましょう-

ご覧のとおり、名前はPMT関数で定義および使用されていません。この関数をワークシートの別の場所に配置する場合は、パラメーター値が正確にどこにあるかも覚えておく必要があります。名前を使用する方が良いオプションであることはご存知でしょう。

この場合、関数は名前のないセル参照ですでに定義されています。名前を定義して適用することはできます。

Step 1 −使用 Create from Selection、名前を定義します。

Step 2−数式を含むセルを選択します。

隣をクリックDefine Name の中に Defined Names 上のグループ Formulasタブ。ドロップダウンリストから、をクリックしますApply Names

Step 3Apply Namesダイアログボックスが表示されます。を選択Names あなたがしたいこと Apply [OK]をクリックします。

選択した名前が選択したセルに適用されます。

あなたもすることができます Apply Names ワークシートを選択して上記の手順を繰り返すことにより、ワークシート全体に移動します。

数式での名前の使用

あなたは使用することができます NameFormula 次のように-

  • を入力する Name あなたがそれを覚えているなら、または

  • 最初の1文字または2文字を入力し、 Excel Formula Autocomplete 特徴。

  • [式]タブの[定義済みの名前]グループの[式で使用]をクリックします。

    • 定義された名前のドロップダウンリストから必要な名前を選択します。

    • その名前をダブルクリックします。

  • を使用して Paste Name ダイアログボックス。

    • 定義された名前のドロップダウンリストから[名前の貼り付け]オプションを選択します。[名前の貼り付け]ダイアログボックスが表示されます。

    • を選択 Name の中に Paste Names ダイアログボックスをダブルクリックします。

ワークブックでの名前の表示

あなたはすべてを得ることができます Names 彼らと一緒にあなたのワークブックに References そして Save それらまたは Print それら。

  • コピーする空のセルをクリックします Names あなたのワークブックで。

  • クリック Use in Formula の中に Defined Names グループ。

  • クリック Paste Names ドロップダウンリストから。

  • クリック Paste List の中に Paste Name 表示されるダイアログボックス。

名前のリストとそれに対応する参照は、以下のスクリーンショットに示すように、ワークシートの指定された場所にコピーされます。

範囲の交差に名前を使用する

Range Intersections 共通の2つの範囲を持つ個々のセルです。

たとえば、以下のデータでは、範囲B6:F6と範囲C3:C8に共通のセルC6があります。これは、実際には、試験1で学生のコデダアダムが採点した点数を表しています。

これをより意味のあるものにすることができます Range Names

  • 作成する NamesCreate from Selection 学生と試験の両方のために。

  • 君の Names 次のようになります-

  • タイプ=Kodeda_Adam Exam_1 B11で。

ここでは、2つの範囲の間のスペースである範囲交差操作を使用しています。

これにより、セルC6に示されている試験1のアダムであるコデダのマークが表示されます。

名前付きの数式をコピーする

次の名前で数式をコピーできます Copyそして Paste 同じワークシート内。

名前の付いた数式を別のワークシートにコピーすることもできます。 copy そして paste、すべてを提供 names の中に formula 持ってる workbook なので Scope。それ以外の場合は、#VALUE エラー。