いまさら聞けないExcelの使い方講座

【Excel】複雑な数式を手打ちするのはミスの元！エクセルでよく使う数式を簡単に使い回せるようにするテクニック

トップスタジオ

2019年3月25日 06:55

数式を直感的に入力できるようにしたい！

　Excelで数式を作成する時、いろいろな計算や処理をしたりすると、セル内の数式が複雑になってしまいますよね。複雑な数式を一生懸命作っても、後から見た時に「この式はいったい何の目的で作った式だっけ……？」とすっかり忘れてしまっていることもあるのではないでしょうか。長い数式は、ぱっと見で数式の意味がわかりにくくなりがちですし、何度も入力するとなると面倒ですよね。

　今回は、長い数式に名前を付けて、表の中で使い回せるようにするテクニックを解説します。

「名前を定義」で数式にわかりやすい名前を付ける

　「●●ショップ　商品価格一覧表」（①）のB列には、お店で取り扱っている商品の税抜価格が入力されています。この税抜価格をもとにして隣のC列に税込価格を求める数式を入力する例を考えてみましょう。もちろん、セルに数式を直接入力しても良いですが、今回は数式を作成して名前を付け、その数式を使って計算する方法を説明します。

　表の中の任意のセルをクリックしてアクティブにしておき、［数式］タブ（②）→［名前の定義］（③）をクリックします。

　［新しい名前］ダイアログボックスが表示されます。［名前］欄に、数式に付けたい名前（ここでは「税込価格」）（④）を入力し、［参照範囲］欄に数式を入力していきます（数式に付けられる名前についてはいくつかルールがあります。この後の部分で解説します）。ここでは消費税込みの価格を計算したいので、「税抜価格に1.08を掛け、ROUNDDOWN関数を使って端数を切り捨てる」という数式を作ります。本記事ではROUNDDOWN関数についての説明は省きますが、以前の記事で詳しく解説しているので、読んでみてくださいね。

　［参照範囲］欄に「=ROUNDDOWN(」（⑤）と入力し、続けてセルB3（⑥）をクリックします。すると、先ほど［参照範囲］欄に入力した数式に続けて「Sheet1!$B$3」（⑦）と表示されます。

　このままだと、この数式をC列の他のセルにコピーした時にも数式がセルB3を参照してしまうので、「$B$3」を「$B3」（⑧）に修正します。この時、カーソルを矢印キーで移動させると参照するセルが変わってしまうので、数式を一番後ろから［Backspace］キーで削除して入力し直すか、削除したい「$」の直前をクリックして削除するようにしましょう。あるいは、［F4］キーを押して「$」の有無を切り替えることもできます。

　参照するセルが正しく入力できたら、数式を続けて入力していきましょう。「$B3」に続けて、「*1.08,0)」（⑨）と入力します。これで、「税抜価格に1.08を掛ける計算を行った結果に対して、ROUNDDOWN関数で小数点以下を切り捨てる」という計算をする数式に「税込価格」という名前を付けることができました。このような複雑な計算内容に対して「税込価格」という簡単な名前を付けられると、わかりやすいですよね。ここまで入力できたら、［OK］（⑩）をクリックしてダイアログボックスを閉じます。

　続けて、名前を付けた数式をセルに入力してみましょう。セルC3に「=税込価格」（⑪）と入力し、［Enter］キーを押します。最初に入力する「=」は、数式の一部なので半角で入力してください。定義した数式の名前が正しく入力されると、通常セルに数式を直接入力していく時と同じように、入力した文字の色が変わり、参照しているセルに色付きの枠が表示されるようになります。

　セルC3に税込価格（⑫）が表示されました。

　通常の数式と同様に、オートフィルで数式を他のセルにコピーすることもできます。数式をセルC10までコピー（⑬）すると、表内にある各商品について、小数点以下が切り捨てられた税込価格が表示されます。また、オートフィルを使わなくても、C列の他のセルに「=税込価格」と入力するだけで、そのセルの左側に入力されている商品の税込価格を求めることができます。

　他にも、セール価格を求めるための数式を定義しておけば、簡単にセール価格を求めることができます。例えば、「=ROUNDDOWN($B3*0.7*1.08,0)」という数式に「三割引」という名前を付けておけば、「=三割引」（⑭）と入力するだけで、3割引きにした時の税込価格（⑮）を簡単に求めることができます。

　値引き率が異なる場合でも、数式に名前を付けておけば、すぐにセール価格を表示させることができます。例えば「=ROUNDDOWN($B3*0.5*1.08,0)」という数式に「半額」と名前を付けておけば、「=半額」（⑯）と入力するだけで半額の税込み価格（⑰）が計算できます。このように、数式に名前を付けると、数式を直感的に入力できるようになります。

　最後に、数式に名前を付ける時の注意点を挙げておきますので、覚えておきましょう。

数式に付ける名前の最初の文字は、アルファベット、ひらがな、カタカナ、漢字、「_（アンダースコア）」でなければなりません。数値から始まる名前を付けることはできません。数値や「.（ピリオド）」は最初の文字以外の場所であれば使えます。
全角、半角ともにスペース（空白）を名前に含めることはできません。
アルファベットの大文字と小文字は区別されません。例えば、「ZEIKOMI」という名前を付けた後に「zeikomi」という名前を付けようとしても、同じ名前と認識されてしまい、登録できません。
セル参照と同じ名前や、ブック内の他の場所（セル範囲など）で付けている名前を数式に付けることはできません。
「R（r）」「C（c）」は「列」「行」を表す言葉なので、名前としては使えません。
数式に付けられる名前は255文字までです。