[名前ボックス]で名前定義

1.セルやセル範囲を選択
2.名前ボックス（数式バーの左横）をクリック
3.名前を入力
4.Enterキーを押す

【解説】
　セルやセル範囲に名前をつける場合には、選択されたセルまたはセル範囲の絶対参照（例えば$A$1）が既定値として参照範囲に入力されます。

　定義する名前には以下のようないくつかの制約があります。
−名前の先頭に数字を使用することはできません。例えばどうしても「5月」という名前をつけたい場合には、アンダーバーなどを先頭につけて「_5月」というふうにします。先頭文字以外には数字を使うことができます。
−A1やR1C1などのセル参照と同じ名前を使用することはできません。
−RとC、一文字だけを名前につけることはできません。
−名前の中にスペースは使用できません。
−名前の大文字と小文字は区別されません。
−印刷範囲の設定を行うと印刷範囲に「Print_Area」という名前がつきます。

---

[名前の定義]ダイアログボックスで名前定義

1.セルやセル範囲を選択
2.Ctrl+F3（Ctrlキーを押しながらファンクションキーF3を押す）
3.名前を入力
4.Enterキーを押す

【解説】
　Ctrl+F3は、メニュー[挿入]−[名前]−[定義]でもできます。
　セルやセル範囲だけでなく、定数や、数式にも名前をつけることができます。
　例えば、「0.05」という定数に「消費税」という名前をつけるには以下のようにします。

1.Ctrl+F3（Ctrlキーを押しながらファンクションキーF3を押す）
2.[名前]に「消費税」と入力
3.[参照範囲]のセル参照を削除して、「0.05」と入力
4.Enterキーを押す

　これで、
=100*消費税
と入力すると、セルに「5」と表示されるようになります。

---

[名前の作成]ダイアログボックスで名前定義

1.名前を定義するセル範囲の隣のセル（上、下、右、左のどれか）に定義したい名前を入力（表の見出しをそのまま名前にしたい場合には新たに入力する必要はありません）
2.入力した名前の入ったセルと名前定義したいセル範囲を選択
3.Ctrl+Shift+F3（CtrlキーとShiftキーを押しながらファンクションキーF3を押す）
4.[名前の作成先]で上端行、左端列、最下行、右端列のうち定義したい名前の入っているセルの位置にチェック（複数チェック可能）
5.Enterキーを押す

【解説】
　Ctrl+Shift+F3はメニュー[挿入]−[名前]−[作成]でもできます。
　表の各列や各行に一度に名前を定義したい場合には、[名前の作成]ダイアログボックスを使うやり方が便利です。
　例えば、B2:B10、C2:C10、D2:D10にそれぞれ、B1、C1、D1セルに入っている見出しの名前をつけたい場合には、B1:D10を選択しておいて、Ctrl+Shift+F3、上端行にチェックが入っていることを確認してEnterキーを押します。

---

名前定義の三つの方法の整理

1.[名前ボックス]を使う
2.[名前の定義]ダイアログボックスを使う（Ctrl+F3）
3.[名前の作成]ダイアログボックスを使う（Ctrl+Shift+F3）

　定数や数式に名前を定義するのは、上記２のCtrl+F3しかできません。
　セルやセル範囲に名前を定義するのは、上記三つのどれでもできます。通常は[名前ボックス]を使うのが一番簡単です。
　参照範囲を初期設定の絶対参照ではなく相対参照にするといった場合にはCtrl+F3を使います。
　表の各列、各行にいっぺんに名前を定義する場合はCtrl+Shift+F3を使います。

---

複数の選択範囲に名前をつける

　例えばA1:A10、C1:C10に名前をつけるには、以下の手順で行います。

1.マウスでC1:C10を選択して、次にCtrlキーを押しながらマウスでA1:A10を選択
する（これで、A1:A10、C1:C10が選択されている状態になります）
2.名前ボックス（数式バーの左横）をクリック
3.名前（例えば「複数範囲」）を入力
4.Enterキーを押す

　これで、A1:A10、C1:C10に複数範囲という名前がつきます。
　
【解説】
　A1:A10にデータを入力した後にC1:C10にデータを入力するような作業がある場合などは、上記のように名前をつけておくと便利です。
　今回の例では範囲の数は2でしたが、2以上の範囲に名前をつけることも可能です。ただしいくつでも複数範囲を選択できるわけではなく、「参照範囲の文字数が255文字を超えない」という制限があるようです。
　上記の手順を実行してから、Ctrl+F3で「複数範囲」をクリックすると[参照範囲]は「=Sheet1!$C$1:$C$10,Sheet1!$A$1:$A$10」というようになっているのがわかります。ここの文字数が255文字を超えることができないのです。
　キーボードだけで、複数範囲の選択をする場合は以下のようにします。
1.C1セルを選択してからShiftキーを押しながら↓（下矢印キー）を押してC10まで選択
2.Shift+F8（Shiftキーを押しながらファンクションキーのF8を押す。これでステータスバーの右の方に「ADD」と表示されます）
3.A1セルを選択してからShiftキーを押しながら↓（下矢印キー）を押してA10まで選択

---

名前をつけたセル範囲の複数選択

　名前ボックス（数式バーの左横）をクリックして、まず一つ目の名前をクリックします。続けて、Ctrlキーを押しながら二つ目の名前をクリックします。

【解説】
　[参照範囲]は255文字を超えることができない、この制限をオーバーしてしまうような場合には、選択したい複数範囲を二つ以上に分けて名前をつけて、今日ご紹介した[名前をつけたセル範囲の複数選択]で全てのセル範囲を選択することができるようになります。

---

アクティブになる順番を意識して複数の選択範囲に名前をつける

1.最初に、二番目にアクティブにしたいセルを選択。
　続いて、三番目にアクティブにしたいセルをCtrlキーを押しながら選択。
　続いて、四番目に．．．
　最後に、一番目にアクティブにしたいセルをCtrlキーを押しながら選択。
2.名前ボックス（数式バーの左横）をクリック
3.つけたい名前を入力
4.Enterキーを押す

【例題】
　A2,A4,A6に「順番」という名前をつけてください。ただし、名前ボックスで「順番」をクリックしたときに、A2がアクティブセルになっていて、Enterを押す度にA4、A6が順にアクティブになるようにつけてください。

【例題の回答】
1.セルA4を選択
2.Ctrlキーを押しながらA6を選択
3.Ctrlキーを押しながらA2を選択
4.名前ボックス（数式バーの左横）をクリック
5.「順番」と入力
6.Enterキーを押す

　上記手順を実行後、名前ボックスで「順番」をクリックすると、セルA2がアクティブになっていて、Enterを押す度にA4、A6が順にアクティブになります。
　Ctrl+F3（または、メニュー[挿入]−[名前]−[定義]）で「順番」をクリックして、[参照範囲]を見てみると
=Sheet1!$A$4,Sheet1!$A$6,Sheet1!$A$2
のようになっています。
　[参照範囲]は、選択した順番に並んでいます。
　名前で範囲を選択すると、アクティブセルはこの[参照範囲]の一番最後のセルになります。もう一度Enterを押すと順番が一番目のセルがアクティブになり、Enterを押すごとに次がアクティブになります。

---

別々のシートで同じ名前を使う

　Sheet1,Sheet2,Sheet3のセルA1に「売上高」と同じ名前をつけたい場合には

1.Sheet1のセルA1を選択
2.Ctrl+F3（Ctrlキーを押しながらファンクションキーF3を押す）
3.名前に「Sheet1!売上高」と入力
4.Enterキーを押す

　Sheet2,Sheet3のセルA1についても同様に、「Sheet2!売上高」、「Sheet3!売上高」というふうに「シート名!名前」と入力します。

【解説】
　「シート名!名前」というふうにつけられた名前はシートレベルの名前です。あたまに「シート名!」をつけない場合はブックレベルの名前です。
　シートレベルの名前をそのシート内で参照する時は「名前」だけでできますが、別のシートで参照する時には「シート名!名前」とする必要があります。
　ブックレベルとシートレベルで同じ名前が使われている場合には、シートレベルの名前が優先されます。
　Ctrl＋F3で[名前の定義]ダイアログボックスを開くと、ブックレベルの名前とアクティブシートのシートレベルの名前が表示されます。シートレベルの名前は名前の右にシート名が表示されています。
　名前定義（ブックレベル／シートレベル）されたセルを含むシートのコピーを同じブック内で行うと、新しく作成されたコピー先シートの当該セルにはシートレベルの名前がつきます。
　シートのコピーは、「コントロールキー(Ctrl)を押しながらシート見出しをドラッグ＆ドロップ」でできます。　
　なお、「シートレベルで定義された名前を参照すると、実行時エラーが発生する現象」があるようです。詳しくはマイクロソフト・サポート技術情報の以下の記事をご覧ください。
[XL2000] シートレベルの名前を参照すると実行時エラー発生
http://support.microsoft.com/default.aspx?scid=kb;ja;213540　

---

名前の一覧の作成

1.メニュー[挿入]-[名前]-[貼り付け]
→[名前の貼り付け]ダイアログボックスが表示される
2.[一覧の貼り付け]ボタンをクリック

【解説】
　アクティブセルを起点として、アクティブセルの列に「名前」が、アクティブセルの右の列に「参照範囲」が表示されます。
　一覧が表示される範囲にデータが入っている場合、警告なしに上書きされてしまうので、その下に何も入力されていない 2 つの列がある領域で[一覧の貼り付け]を実行しましょう。
　印刷範囲の設定（メニュー[ファイル]−[印刷範囲]−[印刷範囲の設定]）を行っている場合には、「Print_Area」という名前が表示されます。
　メニュー[挿入]-[名前]-[貼り付け]-[一覧の貼り付け]では、ブックレベルの名前とアクティブシートのシートレベルの名前だけが表示されます。
　以下のようなマクロを使えば、アクティブシート以外のシートレベルの名前も表示させることができます。

Sub ListAllNames()
　Dim i As Integer

　With ActiveWorkbook
　　For i = 1 to .names.count
　　　ActiveCell.Offset(i - 1, 0).Value = .Names(i).Name
　　　ActiveCell.Offset(i - 1, 1).Value = .Names(i)
　　Next
　End With
　ActiveCell.EntireColumn.AutoFit
End Sub

　上記のマクロを実行した場合、非表示の名前も表示されるので、ソルバーを実行したことがあれば、"solver_est"、オートフィルタなどを実行したことがあれば、"FilterDatabase"といった名前も表示されます。

---

数式のセル参照を名前に変更する

1.セル参照を名前に置き換えたい数式があるセル範囲を選択
2.メニュー[挿入]-[名前]-[引用]
3.[名前の引用]ボックスの一つまたは複数の名前をクリックしてEnter

【解説】
　名前の引用は、数式を入力した後で名前の定義を行い、数式の引数をセル参照から名前に替えたい場合などに使います。
　例えば、
セルB2　=VLOOKUP(A2,$E$2:$F$11,2,0)
セルB3　=VLOOKUP(A3,$E$2:$F$11,2,0)
セルB4　=VLOOKUP(A4,$E$2:$F$11,2,0)
という数式が入力してあって、その後、$E$2:$F$11に「コード」という名前をつけたとします。
　名前を付けても、数式内のセル参照は自動的には名前になりません。
　例えば、B2:B3の数式内の引数を「コード」に置き換えたい場合には、
1.B2:B3を選択
2.メニュー[挿入]-[名前]-[引用]
3.[名前の引用]ボックスで「コード」をクリックしてEnter
とします。
　これで、
セルB2　=VLOOKUP(A2,コード,2,0)
セルB3　=VLOOKUP(A3,コード,2,0)
となります。
　セルB4は、=VLOOKUP(A4,$E$2:$F$11,2,0)　のままです。

　ワークシートのすべての数式の参照を名前に変更するには、任意のセルを選択します。つまり、B2:B3のようにセル範囲を選択しないで、例えば、B2だけを選択してから、メニュー[挿入]-[名前]-[引用]を行います。
　ただし、[名前の引用]は、
=VLOOKUP(A2,Sheet2!$A$2:$B$11,2,0)
のように他のシートのセル範囲を参照していて「シート名!」があたまについているセル参照の場合には、「置換する参照はありません。」というメッセージが出て、置き換えはされません。
　その場合には、以下の手順で名前に置き換えます。
1.B2:B3を選択
2.数式バーで名前に置き換えたいセル参照（この場合だと「Sheet2!$A$2:$B$11」）
を選択して、Ctrl+C
3.Cancel(Escapeキー)
4.Ctrl+H（Ctrlキーを押しながらH）
→[置換]ダイアログボックスが表示される
5.「検索する文字列」でCtrl+V（これで「Sheet2!$A$2:$B$11」が貼り付けされます）
6.「置換後の文字列」に「名前」（この場合だと「データ」）を入力
7.[すべて置換]ボタンをクリック

　ワークシートのすべての数式のセル参照を名前に変更する場合には、名前の引用と同様に、1.のところで特定のセル範囲ではなく一つのセルを選択してから上記手順を実行します。
　先に名前が定義されているセル範囲を、数式入力の際にマウスでクリックして選択した場合には、セルのアドレスではなく、自動的に定義名になります。（Excel95までのバージョンではならないようです）
　引数をマウスのクリックなどで選択しないで、数式内でセルのアドレスを直接記述すると、名前ではなくセル参照で数式を作ることができます。

---

名前を定義する利点−数式がわかりやすくなる

　例えば、セルA2に商品の売上金額が入っていて、セルB2に消費税金額を算出する式を入力するとして、
=A2*0.05
とするよりも、「0.05」という定数に「消費税」という名前をつけておいて、
=A2*消費税
とする方が、数式の意味がわかりやすくなります。

　もう一つ、セル範囲に名前をつけた例をあげます。
　例えば、Sheet1のA1:B3に以下が入力されているとします。
1000 りんご
1001 ばなな
1002 みかん
　別のシートのA1に「1000」などのコードが入力されたら、B1に「りんご」という品名が表示されるようにするのに、VLOOKUP関数がよく使われます。
=VLOOKUP(A1,Sheet1!$A$1:$B$3,2,0)
　Sheet1のA1:B3に「コード表」という名前をつけておけば、
=VLOOKUP(A1,コード表,2,0)
というふうによりわかりやすくなります。

---

名前を定義する利点−数式の引数の入力が楽にできる

　数式入力の途中で　F3　

【解説】
　例えば、B2:B10に「売上高」という名前をつけておきます。
　=SUM(　まで入力しておいて、F3（ファンクションキーのF3）を押すと[名前の貼り付け]ダイアログボックスが表示されます。
　「売上高」を選択して、Enterキーを押します。
　「)」を入力してEnterを押します（ここでは「)」を入力しないで、Enterを２回押してもできます）。
　すると「=SUM(売上高)」となり、B2:B10の合計が算出されます。

　二つ目の例です。
　セルA1に「田中」と入力して、「顧客氏名」という名前をつけておきます。
　他のセル、例えばD1をアクティブ（選択）にしておいて、F3を押して[名前の貼り付け]ダイアログボックスを表示させます。
　「顧客氏名」を選択しEnterを押します。すると、「=顧客氏名」と表示され、もう一回Enterを押すとD1には「田中」と表示されます。

　三つ目の例です。
　セルB10に「100」という数値が入力して、「東京」という名前をつける。
　セルC10に「200」という数値が入力して、「名古屋」という名前をつける。
　セルD10に「300」という数値が入力して、「大阪」という名前をつける。
　セルE10にB10,C10,D10の合計を出すには、F3を押して「東京」を選択してEnter、続いてF3を押して「名古屋」を選択してEnter、続いてF3を押して「大阪」を選択してEnter、すると「=東京+名古屋+大阪」となり、最後にもう一度Enterを押すとE10に合計の「600」が表示されます。

　二つ目と三つ目の例では、「=」や「+」などを入力しないでもできることに注目ください。
　なおF3は、メニュー[挿入]−[名前]−[貼り付け]でもできます。
　引数は「ひきすう」と読みます。

---

名前を定義する利点−数式に名前を付けることで、数式の入力が楽にできる

　「ワークシート名を表示する関数」に名前をつけてみましょう。

1.Ctrl+F3（Ctrlキーを押しながらファンクションキーF3を押す）
2.[名前]に例えば、「sheet」と入力
3.[参照範囲]に以下の式を入力
=MID(CELL("filename",!$A$1),FIND("]",CELL("filename",!$A$1))+1,31)
4.Enterキーを押す

　シート名を表示させたいセルでF3を押して、「sheet」を選んでEnter。もう一回Enterを押して確定させると、セルにシート名が表示されます。もちろんセルに直接、「=sheet」と入力しても同じです。
　上記3.で[参照範囲]に入力する式には、「$A$1」ではなく、「!$A$1」と「!」がついていることに注意してください。「!」がついていない式を参照範囲に入力すると、現在アクティブなシート名を表示するようになります。
　[参照範囲]に上記式を貼り付けするには、Ctrl+Vでできます。
　新規ファイルを作成してまだ保存していない場合には、表示されません。一回保存すれば表示されるようになります。

---

名前を定義する利点−参照範囲の変更が比較的簡単にできる

　例えば、複数のセルに=SUM(B2:B13)という数式が入っているとします。合計する範囲がB2:B13からセルが一つ増えてB2:B14になった場合、数式を一つ一つ修正することになります。（いずれ解説する置換（Ctrl+H）を使えば一発でできます）

　セルB2:B13に「売上高」という名前をつけていて
　=SUM(売上高)　
としておいたとすると、Ctrl+F3で、「売上高」を選択し[参照範囲]をB2:B14に変更することで、一発で複数の数式の参照範囲を変更することができます。

　それから定数に名前を定義している場合、例えば、　
1.Ctrl+F3（Ctrlキーを押しながらファンクションキーF3を押す）
2.[名前]に「消費税」と入力
3.[参照範囲]のセル参照を削除して、「0.05」と入力
4.Enterキーを押す
　として、「消費税」という名前に「0.05」という定数を定義している場合、=100*消費税　は5となります。

　消費税が5%から例えば7%に引き上げられた時に、名前を使わないで、=100*0.05　としていた場合には、*0.05としている数式を一つ一つ*0.07に修正する必要がありますが、名前を使っている場合には、
1.Ctrl+F3
2.[名前]で「消費税」を選択
3.[参照範囲]の「0.05」を「0.07」に修正
4.Enterキーを押す
とすることで、一発で修正できます。

---

名前を定義する利点−ジャンプが簡単にできる

　名前ボックス（数式バーの左横）をクリックして名前をクリックすると、当該セルまたはセル範囲にジャンプします。

【解説】

　Ctrl+Gで名前を選択してEnter、あるいは、メニュー[編集]−[ジャンプ]で名前を選択してEnterでも、セルまたはセル範囲にジャンプします。

　名前ボックスには、セルのアドレスを直接入力してジャンプすることもできます。
　例えば「C10」のようにセルのアドレスを直接入力してEnterを押すとC10にジャンプします。
　その他に、以下のようなジャンプができます。

　「A:C」　A列からC列が選択される。
　「1:10」　1:10行が選択される。
　「A:C,1:10」　A列からC列と1:10行が選択される。
　「R」　アクティブセルの行が選択される。
　「C」　アクティブセルの列が選択される。

---

名前を定義する利点−メモリの節約になる

　マイクロソフトのサポート技術情報の「メモリを節約するワークシートの作成方法」をご覧ください。

---

名前を定義する利点−ネスト７つまでの制限を超えられる

　数式には、7レベルまでのネストした関数を指定することができます。
　関数Aの引数として関数Bを指定すると、関数Bは第2レベルの関数となります。たとえば、下記式の AVERAGE関数とSUM関数は、IF関数の引数なので第2レベルの関数となります。さらに、AVERAGE関数にネストする関数があった場合、その関数は第3レベルになります。
=IF(AVERAGE(F2:F5)>50,SUM(G2:G50),0)
　名前定義を使えば、このネストの制限を超えることができます。
　以下の質問＆回答をご覧ください。

【質問】「住所の番地を漢数字に置換するには？」

　住所録を作ったのですが番地は全角数字で入力しました。
　そのデータを元にして年賀状を印刷しようとしたら、番地は全角数字ではなくて漢数字の方がいいことに気がつきました。うまい置換の方法はないですか？

【回答】
　A列に全角数字で入力した住所が入っていて、漢数字に置き換えた住所をB列に表示させるとします。

1.セルB1を選択
2.Ctrl+F3（または、メニュー[挿入]-[名前]-[定義]）
3.名前に「OTE」 と入力
4.参照範囲に以下の式を入力

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(!A1,"１","一"),"２","二"),"３","三"),"４","四"),"５","五"),"６","六"),"７","七"),"８","八")

5.Ctrl+F3（または、メニュー[挿入]-[名前]-[定義]）
6.名前に「CN」 と入力
7.参照範囲に以下の式を入力
=SUBSTITUTE(SUBSTITUTE(OTE,"９","九"),"０","○")

　これで、A1に「大手町１２−３４−５６ハイツ大手町７８９０号室」と入力されていた場合、B1に　=CN と入力すると、「大手町一二−三四−五六ハイツ大手町七八九○号室」　と表示されます。

【解説】
　SUBSTITUTE関数は、文字列中の指定された文字を他の文字に置き換えます。
　SUBSTITUTE(文字列, 検索文字列, 置換文字列, 置換対象)　という引数をとります。

「文字列」： 置き換える文字を含む文字列を指定します。目的の文字列が入力されたセル参照を指定することもできます。

「検索文字列」： 置き換える文字列を指定します。

「置換文字列」： 検索文字列 と置き換える文字列を指定します。

「置換対象」：文字列に含まれるどの検索文字列を置換文字列と置き換えるかを指定します。置換対象を指定した場合、文字列中の置換対象番目の検索文字列だけが置き換えられます。省略した場合は、文字列中のすべての検索文字列が置き換えの対象となります。
　この式では、「置換対象」を省略していますので、文字列中のすべての検索文字列が置き換えの対象となっています。

　例えば、セルA1に「１丁目２番地」と入力されているとします。
　=SUBSTITUTE(A1,"１","一")　で、A1に入っている文字列「１丁目２番地」のうち、"１"を"一"にすべて置き換えます。その結果「一丁目２番地」と表示されます。

　次に、=SUBSTITUTE(SUBSTITUTE(A1,"１","一"),"２","二")という式だと、=SUBSTITUTE(A1,"１","一")の返り値「一丁目２番地」のうち、"２"を"二"にすべて置き換えます。その結果「一丁目二番地」と表示されます。

　このように０〜９について、SUBSTITUTE関数をネストさせて式を作りたい。しかし、以下のような式をセルに入力しようとしても、ネスト７つの制限があるのでできないのです。そこで、名前定義を使って、ネストの壁を超えたわけです。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"１","一"),"２","二"),"３","三"),"４","四"),"５","五"),"６","六"),"７","七"),"８","八"),"９",九""),"０","〇")

　上記手順の4の式中に「!A1」と"!"がついています。こうしておくと、他のシートでも同じように使えます。"!"なしだと名前定義したシートの「A1」を参照しにいきます。
　今回のケースの場合、アクティブセルをB1にしてから名前を定義することがポイントです。
　B列に　=CN を入力後‖a列の循所に変郊をしても自動妬に再計斯されないという問題があります｜これについては‖a列の循所変郊が壮て淑了後‖b列で=CNが入っている一番上の行でフィルハンドルをダブルクリックして、ファンクションキーF2を押してEnterを押せば、計算されます。
　名前定義でネスト７つの制限は超えられますが、実際にはあまり使うことはないかと思います。作業セルを使うとか、VLOOKUP、MATCH、CHOOSEなどを使って条件分岐させるなどといったやり方の方が一般的です。

　今回のようにセルに数値と文字が両方入っているのではなく、数値だけが入っている場合には、NUMBERSTRING関数や、表示形式で漢数字にすることができます。
　例えば、A1に「1234567890」が入っているとして、NUMBERSTRING関数を使うと以下のように表示されます。

=NUMBERSTRING(A1,1)　→　十二億三千四百五十六万七千八百九十
=NUMBERSTRING(A1,2)　→　壱拾弐億参阡四百伍拾六萬七阡八百九拾
=NUMBERSTRING(A1,3)　→　一二三四五六七八九〇

　書式設定を使う場合は以下の手順です。
1.A1セルを選択して、Ctrl+1（またはメニュー[書式]-[セル]
2.[表示形式]タブの[ユーザー定義]をクリック
3.[種類]に　[DBNum1]　と入力してEnter

　これで、A1に「十二億三千四百五十六万七千八百九十」と表示されます。
　上記3のところで、
[DBNum2]とすれば、「壱拾弐億参阡四百伍拾六萬七阡八百九拾」
[DBNum3]とすれば、「十２億３千４百５十６万７千８百９十」
のようになります。

---

[データ]-[入力規則]-[リスト]でリストからセルに入力できるデータを制限

　メニュー[データ]-[入力規則]-[リスト]で、「リストからセルに入力できるデータを制限する」ことができます。
　A列に以下のD2:D4の部署名のリストを使って入力したいとします。

	D
1	部署
2	企画部
3	人事部
4	総務部

　手順は以下の通り。

1.列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスに、=$D$2:$D$4 と入力
5.[空白を無視する] チェック ボックスをオフに
6.[ドロップダウン リストから選択する] チェックボックスをオンに
7.Enter

　これで、A列（B列）のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）で入力する部署名（氏名）をリストから選択することができるようになります。

【解説】
　ファイルをダウンロードされた方はシート「同一シート」をご覧ください。
　制限が設定されたセルをクリックしたときに入力項目のリストからデータを選択できるようにするには、[ドロップダウンリストから選択する] チェックボックスをオンにします。
　制限が設定されたセルまたは有効なデータのリストが空白のときに無効なデータとして処理するには、[空白を無視する]チェックボックスをオフにします。
　別のシート（例えばシート「リスト」）にあるリストを参照しようと、例えば、上記4の手順のところで、=リスト!$D$2:$D$4　を入力して最後にEnterを押すと「抽出条件 データの入力規則 で、他のワークシートまたはブックへの参照は使用しません。」というメッセージが出てしまい、「=シート名!セル参照」では他の
シートのリストを[元の値] ボックスに入力することができません。
　しかし、リストが入っているシートと入力用のシートは別にした方がなにかと便利です。
　名前定義することで別のシートのリストを使えるようになります。

---

名前定義で、[データ]-[入力規則]-[リスト]で他のシートの範囲を利用

　シート「基本」のA列を以下のシート「リスト」のA2:A4の部署名のリストを使って入力したいとします。

A
1 部署
2 企画部
3 人事部
4 総務部

　まずは、名前定義です。

1.シート「リスト」のA2:A4を選択
2.名前ボックス（数式バーの左横）をクリック
3.「部署」と入力
4.Enterキーを押す

　次に[データ]-[入力規則]-[リスト]の設定です。

1.シート「基本」の列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスでF3（ファンクションキーのF3）を押す
5.「部署」を選択してEnterを押す
6.[空白を無視する] チェック ボックスをオフに
7.[ドロップダウン リストから選択する] チェックボックスをオンに
8.Enter

　これで、シート「基本」のA列のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）で入力する部署名をシート「リスト」のリストから選択することができるようになります。

---

可変範囲の名前定義で、リストへの追加／削除に自動的に対応

　シート「可変範囲」のA列を、以下のシート「リスト」のA列の部署名のリストを使って入力したいとします。

A
1 部署
2 企画部
3 人事部
4 総務部

　以下のようにA2:A4に名前をつけていると、A5に部署が追加された場合、自動的に範囲が拡張されません。

1.シート「リスト」のA2:A4を選択
2.名前ボックス（数式バーの左横）をクリック
3.「部署」と入力
4.Enterキーを押す

　部署を追加／削除に自動的に対応するには、以下のようにします。

　まずは、名前定義です。

1.シート「リスト」を選択（選択しているセルはどこでもかまいません）
2.Ctrl＋F3
3.「名前」に「部署2」と入力
4.「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」
5.Enterキーを押す

　次に[入力規則]−[リスト]の設定です。

1.シート「可変範囲」の列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスでF3（ファンクションキーのF3）を押す
5.「部署」を選択してEnterを押す
6.[空白を無視する] チェック ボックスをオフに
7.[ドロップダウン リストから選択する] チェックボックスをオンに
8.Enter

　これで、シート「可変範囲」のA列のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）で入力する部署名をシート「リスト」のリストから選択することができるようになります。
　シート「リスト」に部署名を追加／削除した場合、自動的に追加／削除されるようになっています。

【解説】
　今回の手順の前回との違いは名前定義のところの「参照範囲」だけです。

　名前の定義の手順の1でシート「リスト」を選択としています。1を他のシートを選択した状態で行う場合には、4で
=OFFSET(リスト!$A$2,0,0,COUNTA(リスト!$A:$A)-1,1)
と入力する必要があります。

　シート「リスト」を選択して「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」とした場合でも、あとで、Ctrl＋F3で「部署2」の参照範囲を見ると
=OFFSET(リスト!$A$2,0,0,COUNTA(リスト!$A:$A)-1,1)
のように自動的にシート名がついています。

　「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」を解説します。

　まずは、=COUNTA($A:$A)-1です。

　COUNT関数はその範囲内で数値が入力されているセルの個数を返します。
　一方、COUNTA関数は、数値だけでなく、論理値、文字列、またはエラー値が入力されているセルの個数を返します。

　この式の場合、引数を「$A:$A」とA列全体を指定しています。A列には、部署名が入っているセルがいくつあるかをカウントするわけです。「-1」としているのは、「部署」が入っているセルA1もカウントされているのでそれを引くためです。

　部署がセルA4の「総務部」までしか入力されていない場合には、「3」を返します。

　OFFSET関数は、OFFSET(基準, 行数, 列数, 高さ, 幅)　という引数をとります。

　基準のセルまたはセル範囲から指定された行数と列数だけシフトした位置にある高さと幅のセルまたはセル範囲の参照を返します。

「基準」：基準となるセル範囲の参照を指定します。
　この式の場合、「部署」はA2から始まってますので、$A$2としています。

「行数」： 基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。行数に5を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから 5 行下方向へシフトします。行数に正の数を指定すると下方向へシフトし、負の数を指定すると上方向へシフトします。

　すなわち、基準のセルから行を上下にいくつずらすか、ということですが、この式の場合、基準をずらす必要がないので、「0」としています。

「列数」：基準の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。列数に5 を指定すると、オフセット参照の左上隅のセルは、基準 の左上隅のセルから 5 列右方向へシフトします。列数 に正の数を指定すると右方向へシフトし、負の数を指定すると左方向へシフトします。

　すなわち、基準のセルから列を左右にいくつずらすか、ということですが、この式の場合、基準をずらす必要がないので、「0」としています。

　「行数」、「列数」をずらす必要がない場合には、「O」のかわりに何も入力しないでもできます。例えば、この式だと以下の式になります。
「=OFFSET($A$2,,,COUNTA($A:$A)-1,1)」

「高さ」：オフセット参照の行数を指定します。高さは正の数である必要があります。

　この式の場合、=COUNTA($A:$A)-1の返り値「3」です。

「幅」：オフセット参照の列数を指定します。幅は正の数である必要があります。

　この式の場合、「幅」をかえる必要がないので、「1」としています。

　「高さ」または「幅」を省略すると、基準 のセル範囲と同じ行数または列数であると見なされます。

　さて、もう一回整理しましょう。

　A2が基準になってます。
　行は移動しません。
　列は移動しません。
　高さは3にします。
　幅は1にします。

　これで、A2:A4を参照していることになります。

　例えばシート「リスト」のどこかのセルに
=OFFSET($A$2,,,COUNTA($A:$A)-1,1)
を入力してみてください。
　「#VALUE!」となります。
　そのセルでF2（ファンクションキーのF2）を押してから、F9（ファンクションキーのF9）を押してみてください。
数式バーなどに　={"企画部";"人事部";"総務部"}と表示されます。これがA2:A4を参照しているということなのです。

　F2-F9の後でEnterを押すと、数式が={"企画部";"人事部";"総務部"}に変わります。セルには、一番始めの「企画部」が表示されます。
　F2-F9の後でCancelキーを押すともともとの数式のままです。

---

相対参照を利用して、一つの名前定義で複数列のリストを利用可能に

　シート「一つの名前定義」のA列は以下のシート「リスト」のA列の部署名のリストを、B列は以下のシート「リスト」のB列の氏名のリストを使って入力したいとします。

A
1 部署
2 企画部
3 人事部
4 総務部

B
1 氏名
2 田中
3 佐藤
4 鈴木
5 山田
6 山本
7 小林
8 小川
9 中村
10 斉藤

　まずは、名前定義です。

1.シート「リスト」のA列のセルどこかを選択
2.Ctrl＋F3
3.「名前」に「LIST」と入力
4.「参照範囲」に「=OFFSET(A$2,0,0,COUNTA(A:A)-1,1)」と入力
5.Enterキーを押す

　次に[入力規則]−[リスト]の設定です。

1.シート「一つの名前定義」の列番号Aをクリック
2.ドラッグして列A:Bを選択
→これでA1がアクティブで列A:Bを選択した状態になります。
3.メニュー[データ]-[入力規則]
4.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
5.[元の値] ボックスでF3（ファンクションキーのF3）を押す
6.「LIST」を選択してEnterを押す
7.[空白を無視する] チェック ボックスをオフに
8.[ドロップダウン リストから選択する] チェックボックスをオンに
9.Enter

　これで、シート「一つの名前定義」のA列（B列）のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）で入力する部署名（氏名）をシート「リスト」のリストから選択することができるようになります。

【解説】
　名前定義の手順において、1.で「A列を選択」しているのは、4の4.「参照範囲」の「=OFFSET(A$2,0,0,COUNTA(A:A)-1,1)」の「A:A」と対応させるためです。
　「LIST」という名前を定義してから、B列のセルどこかを選択してから、Ctrl+F3で名前「LIST」をクリックしてみてください。「参照範囲」は「=OFFSET(リスト!B$2,0,0,COUNTA(リスト!B:B)-1,1)」のように「B:B」になっています。
　[入力規則]−[リスト]の設定のところで、1,2の手順で「A1がアクティブで列A:Bを選択」としているのも同様の理由です。

---

縦横可変範囲の名前定義でリスト

　今回は、「一行目に部署名が入っていて、その下に所属の部員名といったリストをもとに、入力規則を使ってA列に部署名をリストから入力、B列にはA列に入力された部署に所属する部員をリストから入力。しかも部署名も氏名も可変範囲にする。」です。

　シート「リスト2」に以下のリストが入っているとします。

A B C
1 企画部 人事部 総務部
2 田中 山本 中村
3 佐藤 小林 斉藤
4 鈴木 小川
5 山田

　シート「縦横可変範囲」のA列は、シート「リスト2」の「部署名」からリストで入力できるようにする。
　シート「縦横可変範囲」のB列は、シート「リスト2」の「氏名」からリストで入力できるようにする。
　「部署名」「氏名」ともに追加／削除に自動的に対応できるようにする。

　まずは、部署名の名前定義です。

1.シート「リスト2」を選択（選択しているセルはどこでもかまいません）
2.Ctrl＋F3
3.「名前」に「部署3」と入力
4.「参照範囲」に「=OFFSET($A$1,0,0,1,COUNTA($1:$1))」
5.Enterキーを押す

　次に氏名の名前定義です。

1.シート「縦横可変範囲」のセルB2を選択
2.Ctrl＋F3
3.「名前」に「氏名」と入力
4.「参照範囲」に「=OFFSET(リスト2!$A$2,0,MATCH(!$A2,部署3,0)-1,COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1,1)」と入力
5.Enterキーを押す

　続いて[入力規則]−[リスト]の設定です。部署名の方から。

1.シート「縦横可変範囲」の列番号AをクリックしてA列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスでF3（ファンクションキーのF3）を押す
5.「部署3」を選択してEnterを押す
6.[空白を無視する] チェック ボックスをオフに
7.[ドロップダウン リストから選択する] チェックボックスをオンに
8.Enter

　最後に氏名の[入力規則]−[リスト]の設定です。
1.シート「縦横可変範囲」の列番号BをクリックしてB列を選択
2.メニュー[データ]-[入力規則]
3.[設定]タブの[入力値の種類]ボックスの[リスト]をクリック
4.[元の値] ボックスでF3（ファンクションキーのF3）を押す
5.「氏名」を選択してEnterを押す
6.[空白を無視する] チェック ボックスをオフに
7.[ドロップダウン リストから選択する] チェックボックスをオンに
8.Enter

　これで、全ての設定が完了しました。

　シート「縦横可変範囲」のA列のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）とすると、シート「リスト2」の一行目の部署名のリストが出てきます。
　シート「リスト2」の一行目に部署名を追加／削除すると、それがリストに自動的に反映されるようになっています。

　シート「縦横可変範囲」のB列のセルでドロップダウンリストの矢印をクリックまたは、Alt+↓（Altキーを押しながら下矢印キーを押す）とすると、A列に入力されている部署名に所属する部員だけのリストが表示されます。
　シート「リスト2」に氏名を追加／削除すると、それがリストに自動的に反映されるようになっています。

　例えばシート「縦横可変範囲」のA2に「総務部」と入力されていた場合には、B2でAlt+↓で、「中村、斉藤」の二名の名前がリストに出てきます。

【解説】
　「氏名」の名前定義の4の「参照範囲」に入力する式の中にシート名がその前についていない"!"があります。
　これは、ペーストするときに消し忘れたわけではありません。わざと残してます。"!"を頭につけておくと、他のシートでも同じように使えます。
　"!"なしだと名前定義したシートを参照しにいきます。すなわち、この式だと常に「縦横可変範囲」のA列を参照しにいくようになっちゃいます。

　これは、No.018に出てきた
=MID(CELL("filename",!$A$1),FIND("]",CELL("filename",!$A$1))+1,31)
と同じです。

　氏名の名前定義の手順の4.「参照範囲」に入力する以下の式について解説します。

=OFFSET(リスト2!$A$2,0,MATCH(!$A2,部署3,0)-1,COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1,1)

　まずは、OFFSETを各引数に分解してみましょう。

基準セル：リスト2!$A$2
移動する行数：0、すなわち行は2行目のまま。
移動する列数：MATCH(!$A2,部署3,0)-1
高さ（行数）の変更：COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1
幅（列数）の変更：1、すなわち列数は1列のまま

　移動する列数：MATCH(!$A2,部署3,0)-1から説明します。シート「縦横可変範囲」のセルA2に「総務部」と入力されているとします。
　MATCH(検査値, 検査範囲, 照合の型)という引数をとります。検査値が検査範囲の中で何番目にあるか、を返します。

　検査値は、この式の場合、セルA2の「総務部」です。
　検査範囲は、「部署3」という名前が参照している範囲、すなわち　{"企画部","人事部","総務部"}です。
　照合の型に0を指定すると、検査値に一致する値のみが検索の対象となります。

　「総務部」は{"企画部","人事部","総務部"}の3番目にあるので、返り値は3となります。
　OFFSETの移動する行数、列数は基準セルのままだと0なので、-1として調整して、2（=3-1）列移動する、としているわけです。

　次に、高さ（行数）の変更：COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1　について、説明します。
　まず、OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1)を各引数に分解してみましょう。

基準セル範囲：リスト2!$A:$A
移動する行数：0
移動する列数：MATCH(!$A2,部署3,0)-1　→上で説明した通り、この式では2です。
高さ（行数）の変更：省略しています。省略した場合には、基準セル範囲と同じ行数のままです。すなわち行全体です。
幅（列数）の変更：省略しています。省略した場合には、基準セル範囲と同じ列数のままです。すなわち1列です。

　つまり、基準セル範囲：リスト2!$A:$Aから、2列右に移動した列全部という意味で、リスト2!$C:$Cを参照しています。リスト2!$C:$Cは、「総務部」の部員のリストが入っている列です。

　COUNTA(リスト2!$C:$C)で、リスト2!$C:$C中に文字列などが入ったセルが何個あるかをカウントします。この例の場合だと、C1に総務部、C2に中村、C3に斉藤が入っているので、3を返します。「総務部」など部署名はカウント対象外なので、-1しているわけです。結果、返り値は2となります。

　さて、それでは、最初の式、
=OFFSET(リスト2!$A$2,0,MATCH(!$A2,部署3,0)-1,COUNTA(OFFSET(リスト2!$A:$A,0,MATCH(!$A2,部署3,0)-1))-1,1)

をそれぞれ返り値にしてみると、以下のようになります。

=OFFSET(リスト2!$A$2,0,2,2,1)

　つまり、基準セルリスト2!$A$2から、行は移動せず2行目のまま、列は右に2移動してC列。新たな基準C2を起点に行数を2行に、列数は1列に、ということになり、C2:C3、すなわち{"中村","斉藤"}を参照している、ということになるのです。

---

名前定義で4.0マクロ関数を使う

　名前定義で4.0マクロ関数を使うことができます。
　A列にいろんな色のついたセルがあるとします。

1.セルB2を選択
2.Ctrl＋F3
3.［名前］に「背景」と入力
4.[参照範囲]に「=GET.CELL(63,!A2)+NOW()*0」と入力
5.Enter

　名前の定義が終わったら
1.セルB2を選択
2.F3（ファンクションキーのF3）
3.「背景」を選択
4.Enter

　これで、セルの色を示す 1 〜 56 の数値が返ってきます。セルの色が "自動"に設定されているときは 0 が返されます。

【解説】
　名前定義の参照範囲で相対参照（A2）を使っているので、名前定義する時にはセルB2を選択している必要があります。
　=背景　を入力したあとで、セルの色を変更した場合にはF9キーを押せば更新されます。
　セルの色が赤のものだけをカウントする、オートフィルタで抽出する、などの場合に上記の技が使えます。

　サンプルファイルでは、シート「セルの色カウント」のセルE1に
=COUNTIF(B2:B101,D1)
という式が入っていて、セルD1の数値に対応した色をカウントしています。
　シート「セルの色カウント」B列のオートフィルタ矢印をクリックして、適当な数値をクリックすると、その数値に対応した色がついているセルを抽出できます。
　「セルの色変更」ボタンを押すとA列のセルの色が変わります。
　シート「セルの色一覧」にはA列にセルの色一覧がついたセルが、B列に　=背景　が入っています。C列には　=interiorcolor(A1)　といったユーザー定義関数が入っています。
　サンプルファイルには以下のマクロが入っています。

1.シート「セルの色一覧」ボタンを押すと以下のマクロがはしります。A列に56色のカラーパレットの一覧を表示します。

Sub AllInteriorColor()
　Dim i As Integer

　ActiveCell.Select
　Application.Calculation = xlcalculationmanual
　　For i = 1 to 56
　　　With Cells(i, 1)
　　　　.Value = i
　　　　.Interior.ColorIndex = i
　　　End With
　　Next
　Application.Calculation = xlcalculationautomatic
End Sub

2.選択したセルの色に対応する数値を返すユーザー定義関数です。複数セルを選択するとエラーになります。

Function InteriorColor(MyRange As Range) As Integer
　Application.Volatile True
　　If MyRange.Interior.ColorIndex = -4142 Then
　　　InteriorColor = 0
　　Else
　　　InteriorColor = myrange.interior.colorindex
　　End If
End Function

3.シート「セルの色カウント」の「セルの色変更」ボタンを押すと以下のマクロが走ります。A2:A101の各セルにランダムに色をつけます。

Sub RandInteriorColor()
　Dim i As Integer

　ActiveCell.Select
　Application.Calculation = xlcalculationmanual
　　For i = 2 to 101
　　　Cells(i, 1).Interior.ColorIndex = int((56 * Rnd) + 1)
　　Next
　Application.Calculation = xlcalculationautomatic
End Sub

　実際に4.0マクロ関数を使う機会はほとんど無いと思います。「名前定義使ってこんなこともできる」ってことで、今回ご紹介させていただきました。