【Excel】名前の定義を数式に使う方法|参照先・有効範囲の確認と管理

=B2*C2*(1+D2)という式は、参照先の見出しを読まないと何の計算か分かりません。B2を「単価」、C2を「数量」、D2を「税率」と名付ければ、=単価*数量*(1+税率)と書けます。名前の定義は、セルや範囲に意味の分かる名前を付けて参照する機能です。

便利なのは、何度も使う設定値や集計範囲です。一方、名前を付けただけで参照ミスがなくなるわけではありません。「参照範囲」と「有効範囲」を確認し、使っている式を確かめてから変更・削除することが大切です。以下はWindowsのデスクトップ版Excelを中心に説明します。

1. 名前は値のコピーではなく参照先を表す

「見積」というシートのB2に1200、C2に3、D2に10%を入力します。B2を「単価」と定義した場合、「単価」という名前が1200の独立したコピーになるわけではありません。B2を参照する名前なら、B2の値を変更したとき、名前を使った計算にも反映されます。

名前にはセル以外にも定数や数式を指定できます。初心者が管理しやすいのは、値と説明をシート上に置き、そのセルを名前で参照する方法です。税率なら値の隣に対象や適用条件を書けるため、設定を後から確認しやすくなります。ここでの10%は掛け算を確かめる例で、個々の取引の税率判断をするものではありません。

名前を使う前後で計算の意味は同じです。1200×3×1.1=3960となります。名前を付けたことで丸め処理が追加されるわけでもなく、明細ごとの端数処理が必要なら別途ルールに合う数式を組み込みます。

2. 名前ボックスか「名前の定義」で登録する

1. 「見積」シートのB2を選択します。
2. 数式バーの左にある、B2と表示された名前ボックスをクリックします。
3. 単価と入力してEnterで確定します。
4. C2に「数量」、D2に「税率」も同じように定義します。

名前ボックスを使うと、選択した範囲のブックレベルの名前を作れます。登録後は名前ボックスの一覧から「単価」を選び、B2が選択されるか確認してください。同じ名前がすでにある場合は、既存の定義を調べてから別名を選びます。

有効範囲や説明も指定する場合は、「数式」タブの「名前の定義」を使います。名前を「単価」、範囲を「ブック」、参照範囲を=見積!$B$2にします。画面によってシート名が引用符で囲まれることもありますが、シート名・列・行が合っているかを見てください。

見出しからまとめて作る場合は、見出しとデータを選んで「数式」→「選択範囲から作成」を使います。横に見出しが並ぶ表なら「上端行」、左側に見出しが並ぶ表なら「左端列」を指定します。見出しそのものを計算範囲へ含める操作ではありません。作成後に名前の管理で各範囲を確認します。

3. 数式に名前を入れて結果を照合する

空いているF2へ次の式を入力します。先ほどの3つの名前が登録済みなら、セル番地を使った計算と同じ3960が期待値です。

=単価*数量*(1+税率)

名前を手で入力する代わりに、「数式」タブの「数式で使用」から選べます。Windows版では、定義済みの名前がある状態でF3を押すと「名前の貼り付け」を使えます。名前の途中で候補一覧が出る場合も、正しい名前を選んで入力します。

比較用にG2へ=B2*C2*(1+D2)を入力し、F2と同じ値か確かめます。次に数量のC2を4に変えれば、どちらも5280になるはずです。確認後はC2を3へ戻し、両方が3960に戻ったことを確認します。計算方法が手動の場合は、各変更後に再計算してください。

すでにあるセル番地の式は、名前を登録しただけで自動的に名前の式へ書き換わるとは限りません。まず1セルの式を置き換え、結果を比較してから利用範囲を広げます。名前の登録と入力方法はMicrosoftの「数式内の名前」にまとめられています。

4. 「ブック」と「シート」の有効範囲を区別する

ブック範囲の名前は、同じブックの別シートからも名前だけで使えます。シート範囲の名前は、そのシート内で名前だけを使うための定義です。別シートから参照する場合は、=見積!単価のようにシート名を付けます。「シート範囲の名前は他のシートから一切使えない」という意味ではありません。

同じ文字の名前を、ブック範囲とシート範囲の両方に作ることもできます。ただし、そのシートではシート範囲の名前が優先されるため、同じ式を書いているのに別の値を参照する原因になります。共通の設定には用途の分かるブック範囲の名前を使い、むやみに同名を重ねない方が確認しやすくなります。

もう一つの注意点は、絶対参照です。セルを選んで登録した名前は通常$B$2のような絶対参照になりますが、名前には相対参照を設定することもできます。「名前なら必ず絶対参照」とは限りません。コピーした式だけ結果が違うときは、名前の管理でドル記号の有無を確認します。

たとえば、すべての商品行へ「単価」という1セルの名前を使うと、各行の単価ではなく同じセルを参照します。行ごとに値が変わる明細では、相対参照やテーブルの構造化参照の方が意図を表しやすい場合があります。名前は共通値と明細値を区別して使います。

5. 名前の管理で変更し、削除前に使用先を調べる

「数式」→「名前の管理」、またはCtrl+F3で一覧を開きます。名前、範囲、値、参照範囲を確認し、変更したい項目を選んで「編集」を使います。税率の置き場所をD2からD5へ移すなら、先にD5の値を確認してから参照範囲を変更します。

変更前の名前と参照先を控え、変更後に利用中の式を再計算します。結果がおかしければ控えた参照先へ戻してください。名前を削除すると、それを使う式が#NAME?になるなどの影響があります。#REF!の名前も、使用されていないとは限りません。見つけた順に削除せず、元の参照先を復元できないか確認します。

検索で名前を探す場合は検索場所をブック、検索対象を数式にして調べます。ただし、名前はグラフや入力規則などにも使われます。数式検索で見つからなかっただけで未使用とは判断せず、共有ブックでは作成者に用途を確認します。管理画面の操作と相対参照の注意は名前の管理の公式資料を参照してください。

6. 増える一覧には固定範囲とテーブルを使い分ける

=見積!$B$2:$B$10という固定範囲に名前を付けても、B11へ入力した値まで必ず含まれるわけではありません。末尾へ行を追加する運用なら、名前の参照範囲を確認し、合計に追加分が入ったかをチェックします。

一覧が継続的に増える場合は、Ctrl+Tでテーブルにして列名を使う方法もあります。「売上表」というテーブルの「金額」列なら、=SUM(売上表[金額])のように列を指定できます。テーブルの中に追加された行を対象にできるため、固定セル範囲の手直しを減らせます。

OFFSETやINDEXで増減する範囲を定義することもできますが、途中の空白や見出しの位置まで含めた設計が必要です。まず固定範囲で計算を確かめ、追加データが範囲へ入る仕組みを選びます。テーブルの書式と名前の参照は別機能なので、単に色付きの表にしただけと混同しないでください。

7. 分かりやすい命名と確認をセットにする

名前に空白は使えず、数字で始まる名前やセル番地と同じ名前も使えません。「売上_上期」「対象税率」など、何を指すか分かる短い名前にします。英字の大文字・小文字だけを変えて別の名前として管理することもできません。

名前を使った式が読めることと、正しいセルを参照していることは別です。登録時に参照先を選び直し、計算結果をセル番地の式と比較し、変更後にも確認する。この3点をそろえると、引き継ぐ人が式の意味と設定場所を追いやすくなります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】