ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

知ってると便利な「名前」を使いこなす(マクロを使った修正)

Excelの関数はセルの範囲を参照するものが多いことは経験的におわかりいただけると思います。セル範囲といっても所詮は行と列の番地に過ぎないので、わかりにくい・覚えにくいという側面があることは否定できません。
ところが、Excelにはセルの範囲に名前をつけることができるという機能があって、これを活用すると表の作成とメンテナンスの効率が大幅にアップします。

今回は、「名前」についてご紹介します。

なお、例によって、 本稿で紹介しているサンプルファイルをインターネット上で公開しています。「名前」というファイル名です。Excel2003以前のもの(拡張子xls)と、Excel2007以降のもの(拡張しxlsm)と2種類あります。興味のある方は下記のリンクをクリックしてダウンロードしてください。

http://bit.ly/JmX4VJ



1.「名前」とは何か
たとえば、次のような数式があるとします。

=Sum(C3:C90)

=Vlookup(A3,C2:F500,2,false)

これらの数式が何を意味しているかは、その参照範囲を見ない限り理解することはできません。またデータを追加した場合、その範囲を参照しているすべての数式を修正しなければならないということになります。
けれども、セルの範囲に名前をつけると、上記の数式は次のように書き換えることができます。

=Sum(4月度売上げ)

=Vlookup(A3,2011年売上げ,2,false)

このように、数式の意味がわかりやすくなるだけでなく、元のセル範囲を修正しても数式をいちいち修正する必要はありません。(下図参照)


c0136904_2242877.jpg



c0136904_22432325.jpg




2.名前の作成
セルの範囲に名前をつけるには、数式バーの左側にある「名前」ボックスを使うのが簡単です。(下図参照))


c0136904_22435480.jpg



(1)まず、名前をつけたいセルの範囲をドラッグして選択状態にします。
このとき、「名前」ボックスにはD1:E17と表示されます。

(2)次に、「名前」ボックスに名前を入力します。ここでは「データベース」と入力します。

セルの範囲に名前をつけるのはこれで完了です。「名前」ボックスの使い方に慣れるために、いったん他のセルをクリックしてから、「名前」ボックスの右端にある「▼」をクリックしてみましょう。「データベース」という名前が表示されるので、それをクリックしてみてください。すると、さきほどのようにD1:E17が選択状態になります。

3.数式や関数で名前を使用する
下図のように、セルH15に次の式を入力します。

=VLOOKUP(G15,データベース,2,FALSE)

c0136904_22451153.jpg



セルG15の値を変えると、セルH15に表示される都道府県がそれに応じて変化します。


4.名前とセル範囲の関連づけの修正
一度定義した名前の管理は、「数式」タブの「名前の管理」をクリックします。(下図参照)

c0136904_2246529.jpg



「名前の管理」ダイヤログボックスが表示されるので、「参照範囲」を書き換えればよいのです。


c0136904_22483353.jpg

このことも、以前に申し上げた「細かいパーツに分けて考える」ということに該当します。ひとつのまとまりとして考えるのではなく、全体を細かいパーツに分解して考えることで、修正が必要になったときはその部品だけを直せばいいようになるからです。


5.名前で定義した範囲を修正するマクロ
名前で定義したセルの範囲は、「名前の管理」によって修正すればよいのですが、データベースのように日々更新される場合、いちいち手作業で修正するのは面倒です。そこで、名前の範囲を修正してくれるマクロについてご紹介しておきます。

(1)事前準備
名前をつけて定義したセルの範囲というのは、先頭セルの番地と最終セルの番地で囲まれた範囲をいいます。セルの範囲が変わるというのは、通常は最終セルの番地が変わる訳ですから、その値を取得するようにすればいいわけです。
ここでは、そのために次の2つの数式を用いています。

(セルH6の数式)
=COUNTA(E:E)

これはE列に入力されている文字列の個数を数える数式です。先ほどの図を見ると、図を最初の行からまんべんなくデータが入力されていることがわかるので、こういうふうに数式を入力しました。
けれども、データベースによってはE列の途中で空白のセルができることもあると思います。そうすると、COUNTA関数は空白のセルを数えないので、これでは正しい結果が得られなくなります。その場合、データベースの先頭列(通常この列に空白セルができることはありません)に対し、COUNT関数を使うことで最終行の値を求めることができるようになります。

(セルH7の数式)
="=Sheet1!R1C4:R"&H6&"C5"

セルH6に表示されているデータベースの最終行の値を使って、名前で定義するセル範囲を指定するための数式です。普段見慣れない表示方法になっていると思いますが、これはR1C1形式というスタイルでセルの範囲を指定しているためです。
この形式は、セルの番地を行番号と列番号の組合わせで表示するというもので、「R1C4」というのは「1行目の左端から4列目のセル」という意味になります。同様に最終セルの番地をこの形式で表現してやればいいのですが、セルの番地は文字列であることから、数式の中に組み込むためには、文字列を” ”で囲み、式と結合するために「&」という演算子を使っています。
したがって、セルH7に入力されている数式は、「Sheet1の1行目の左端から4列目のセルから最終行の左端から5列目のセルの間」という意味になります。

(2)マクロの記述
VBAエディタを起動して、「標準モジュール」のModule1をクリックしたら、「表示」メニューの「コード」をクリックすると、コードを記述するウィンドウが開きます。そこに次のようにコードを入力します。(面倒くさい人は、後述する赤い文字列のコードをコピペしてください。)

c0136904_22503149.jpg



Sub 範囲()
'
' 範囲 Macro
'

'
Dim 最終行 As String

最終行 = Range("H7").Value


With ActiveWorkbook.Names("データベース")
.Name = "データベース"
.RefersToR1C1 = 最終行
.Comment = ""
End With

End Sub



(コードの解説)
Dim 最終行 As String

名前で定義するセルの範囲の値を取得するために「最終行」という変数を定義しています。Stringというのは変数「最終行」が文字列型であることを指定するための記述です。


最終行 = Range("H7").Value

変数「最終行」に、セルH7の値を代入するというコードです。


With ActiveWorkbook.Names("データベース")
.Name = "データベース"
.RefersToR1C1 = 最終行
.Comment = ""
End With



ひとつの処理対象に対して、一連の処理をするときに、With~End With という構文(Withステートメントといいます)を使います。
ここでの処理対象は、「ActiveWorkbook.Names("データベース")」であり、これは、今開いているブックの中にある『データベース』という名称をつけた「名前」であることを示しています。
ここで重要なのは、RefersToR1C1 = 最終行 というコードです。(あとは決まり文句のようなものですから、深く考える必要はありません。)これは、『データベース』という「名前」の範囲がR1C1形式では右辺の値になるということを意味するコードです。実際には、右辺には変数「最終行」が指定されているので、その値はその前に代入されたセルH7の値ということになります。
なお、Withステートメントの最後には、End Withというコードを記述しておきます。


6.マクロをボタンに登録する
せっかく名前の範囲を修正してくれるマクロを作成したのですから、ワンクリックでマクロを実行できるようにしておきます。そのために、「挿入」→「図形」→「四角形」でボタンを作成し、右クリックで「マクロの登録」を選択します。ここで先ほど作成したマクロ「範囲」を割り当てれば完成です。
試しに、このシートのセルD18に「17」、E18に「新潟県」と入力し、セルG15に「17」と入力してみましょう。すると、下図のようにエラーとなります。

c0136904_22521575.jpg



そこで、更新ボタン(マクロを登録してあります)をクリックすると、正常に新潟県と表示されるようになります。
by t_am | 2012-05-13 22:54 | Excel のあの手この手