Excelでセルの名前を使って式を書くと後からでも理解できます!

Excelは便利!でも、式の意味が分からなくなることがありませんか?

Excelって便利です。複雑な式を文句も言わずに黙々と計算してくれます。
なので、ついつい複雑な計算をさせてしまいます。

でも、日時が経ってからシートを見返すと、計算式の意味が分からないことがしょっちゅうあります。数日前の自分は他人ですから、考えたことが理解できない場合があるんです。歳をとってくると尚更その傾向が強まります。

マクロなら、コメントを入れられますから、まだコードの意味を理解することができます。でも、Excelシート上で作る式は行き当たりばったりで作ることが多く、本当にお手上げです

セルの名前を使って式を書くと後から見ても意味が理解できます

Excelではセルに名前を付けることができます。
名前を使って式を書くと、後で見たときに分かり易くなります。

“= A2 * B2 + A3 * B3″という式と、”= 梅おにぎり値段 * 数量 + お茶値段 * 数量”という式とで、どちらが分かり易いですか?

名前を用いることで後者の式が書けるのです。

こうしておけば、月日が経ってからシートを見直しても意味が理解できますね。

セルの名前を使った式を書いてみましょう

セルに名前を付けてみましょう

ではセルの名前を使った式を書いてみましょう。

まず、タイトル行を作ります。A1~D1にそれぞれ、「商品」「金額」「数量」「小計」と入力しましょう。

次に、商品の種類として、A2、A3にそれぞれ「梅おにぎり」「お茶」と入れます。
さらに、この金額と数量として、B2、B3に「108」、「150」、C2、C3に「3」、「2」と入力します。
見栄えを良くするために、A4~C4をつなげて、「合計」としておきましょう。

セルに名前をつける

セルに名前をつける

ここからセルに名前を付けていきます。

B2のセルを選択した後、A1セルの少し上の白い欄に、「梅おにぎり値段」と入力し、「Enter」を押しましょう。これで、B2セルの名前が「梅おにぎり値段」になりました。

同様に、B3に「お茶値段」、C2とC3セルを同時に選んで「数量」と名前を付けましょう。

さらに、D2とD3セルを同時に選んで、「小計」という名前を付けましょう。

名前の管理

名前の管理

「数式」⇒「名前の管理」を見てみると、命名状況を確認することができます。
間違えた場合は、その行を選択して、削除すれば、名前を付け直すことができます。

なお、RとCは、行と列を表す予約語ですので1文字では名前として使えません。使いたい場合は前にアンダスコアを付けて、_Rや_Cとして使いましょう

名前を使って式を書いてみましょう

D2、D3、D4の各セルにそれぞれ「=梅おにぎり値段*数量」、「=お茶値段*数量」、「=SUM(小計)」と入力します。

合計金額が計算された

合計金額が計算された!

そうすると合計金額が計算されるではありませんか!

「Ctrl+Shift+‘」で式を確認する

「Ctrl+Shift+‘」で式を確認する

なお、式を確認したい場合には、Ctrl+Shift+‘(バッククォート)で式が表示されます。もう一度押すと元に戻ります。バッククォートは「@」キーにあります。

ブック全体で有効な名前と各シートで有効な名前

名前には、ブック全体で有効な名前と各シートで有効な名前があります
今回説明した方法はブック全体で有効な名前になります

各シートで有効な名前は、AシートのxとBシートのxで、違う値に設定して使うことができます。そのため、複数のシートを用いる場合には、各シートで有効な名前を使った方がいい場合があります。

各シートで有効な名前を付けたければ、「数式」⇒「名前の管理」⇒「新規作成」⇒「範囲」と進んだところで所望のシート名を選びます。

各シートで有効な名前を他のシートから使いたい場合は、「シート名!名前」と、シート名に感嘆符を付けて限定すれば、呼び出すことができます。

複数のセルからなる領域に名前を付けた場合の振る舞い

上述の「数量」や「小計」のところで既に使っていることですが、長方形の範囲であれば、複数のセルにまたがっていても名前を付けることができます。

例えば、「数量」は、縦続きの2セルに名前を付けています。

同じ数量を掛け算していても、B2の梅おにぎり値段にかける場合はC2の「3」として働き、B3のお茶値段にかける場合はC3の「2」として働きます。つまり、行ごとに働いています。

「小計」でも、縦続きの2セルに名前を付けています。

しかし、=SUM(小計)とした場合には、324と300を合わせた値である624が返ってきます。1つのセルが採用される場合と複数のセルが採用される場合がありますが、このあたりの癖は僕にはうまく説明ができません。使って慣れることが一番だと思います。

物理定数を1つのExcelファイルにまとめておくと便利です

ちなみに、僕は以前、物理に近い仕事をしていたときに、理科年表から物理定数を拾い集めて、Excelのテンプレートファイルにしていました。

ファイル内の定数のセルには名前を付けていましたので、記号で計算式が書けました。例えば、真空中の特性インピーダンスの値を求めたい場合には、「=SQRT(μ0/ε0)」で、およそ377Ωの値を得ることができました。(マニアックすぎますね)

実はこの本から学びました

式に名前を使うという手法は、もともと次の本から学びました。

田沼晴彦:Excelで遊ぶ 手作り数学シミュレーション、ブルーバックス、講談社、2004

名著です。僕がExcelやソフトウェアについて読んだの本の中で、一番役に立った本です。古い本ですし、Excelのバージョンが進んでいるので、現在では若干の修正を加えながら読まないといけないことでしょう。しかし、今から読んでも損はない本だと思います。

余談ですが、僕はブルーバックスが好きで過去に50冊くらいは買ったと思います。そのブルーバックスの中で、この本は1、2を争います。ちなみに、他に名著と思う本は、竹内淳氏の本(例えば、高校数学でわかるシュレディンガー方程式)です。

まとめ

後で見直す可能性があるExcelファイルでは、名前を使って式を書きましょう。
圧倒的に分かり易く書けます。

コメント

タイトルとURLをコピーしました