Excelでいろいろな計算を自動化できる数式の使い方と知っておくと役に立つ関数を紹介します。

Excel活用のヒント入門編「基礎から振り返る数式と関数」イメージ画像
Excel活用のヒント入門編「基礎から振り返る数式と関数」イメージ画像
ソリューション最終更新日: 20180117

仕事に役立つExcel活用のヒント―入門編「基礎から振り返る数式と関数」

  • このエントリーをはてなブックマークに追加

Excelでは数式や関数を使うことで、色々な計算を自動化することができます。ここでは数式の使い方と知っておくと役に立つ関数を紹介します。なお、Excelのバージョンによって機能に違いがありますが、ここでは最新のバージョンであるExcel 2016を元に解説します。

「数式」と「セル参照」をおさらい

おそらくすでに多くの方が一度は使ったことがあるかもしれませんが、Excelでは、セルの中に数式を記入し、その結果を表示させることができます。

例えば、任意のセルに、「=1+2*3-8/4」という文字列を記入してみると、そのセルに「5」の数字が表示されます。これは「1+2×3-8÷4」を計算した結果を表しています。実際に算数(数学)の数式のように、掛け算や割り算が足し算、引き算より優先される点も同じです。Excelでは、最初に「=」が入力されるとそのセルに対するそれ以降の入力は数式であるとみなします。

セル参照とは

もちろん、上記のような単純計算だけであれば電卓でもできますので、あえてExcelを使うこともありません。数式には演算だけでなく、この後述べるセル参照や関数、日付、時間、文字列などもさまざまなものが使われます。まずは以下を見てください。

消費税計算時の画面消費税を計算する

D2のセルでは数式バーに「=B2*C2」と表示されており、B2のセルの値とC2のセルの値を掛け算するというものです。なお、C2のセルには「8%」と表示されていますが、これはこのセルの書式をパーセント表示にしているためで、数値としては0.08になります。

セル参照をして消費税を計算している画面消費税込みの合計額の計算

上記の例では、E2には「=B2+D2」が入力されています。もちろん「=B2+B2*C2」と入力しても同じ結果になります。このように数式にセル名を入れることを「セル参照」と言います。これはセル名自体を計算するのではなくセルの内容を計算に使うものです。

数式への入力はセルのクリックが楽

セルへの数式への入力は、キーボードから直接入力することもできますが、もっと簡単な方法があります。E2のセルに入力する場合は、①まずE2のセルを選択した状態でキーボードから「=」と入力します。②次にB2のセルをクリックします。すると数式バーに「=B2」と表示されます。③キーボードから「+」を入力します。④続いてD2のセルをクリックします。数式バーには「=B2+D2」と表示されます。⑤Enterキーを押します。このように、セルクリックと簡単な入力だけで数式バーへの入力ができます。

セルをクリックして数式を入力している画面①キーボードから「=」を入力、②B2のセルをクリック、③キーボードから「+」を入力、④D2のセルをクリックしてEnterキーをクリックする

このように数式としては、直接数字を入力するだけでなく、セルの値を参照してその値で計算するように指定できます。しかも、元の数字、例えばB2の値が変更されると、即座に結果であるD2とE2の値も変更されます。

セルの値を参照して別のセルの値が自動的に変更された結果の画面B2の値が変更されるとD2、E2の値も自動的に変更される

数式をコピーする

次の画面のように商品を増やした場合を考えてみましょう。

商品の数を増やした場合の消費税計算表画面商品の数を増やした場合

商品名と価格は手入力するとして、消費税のセル (C列) は同じ値なので、C2からC8のどれかを下のセルにコピーすれば済みます。同様に消費税額(D列)と合計額(E列)のセルもコピーしてみましょう。①D2とE2をドラッグして選択した状態でコピー(ctrl + c)した後、②D9からE13をドラッグして選択した状態でペースト(ctrl + p)します。

セルの参照先を含めた範囲指定コピー実行時の画面D2とE2の内容をD9からE13の範囲にコピー&ペーストする

この結果、次の画面のように9~13行目も正しく消費税額と合計額が入っていることがわかります。例えば、コピーされたD9のセルの数式バーを見ると「=B9*C9」となっています。コピー元のD2のセルの数式は「=D2*C2」でしたので、Excelが自動的に参照先のセル名を書き換えてコピーしてくれたのです。

セルの参照先を含めた範囲指定コピー実行後の画面参照先のセル名が自動的に変更されてコピーされる。

数式のセルをコピーすると、数式の計算結果でなく数式そのものが、参照先をコピー先のセルに合わせた形に変更されてコピーされます。ただ、この賢さが時に余計なお世話になることもあります。

数式をコピーしても参照先が変わらない「絶対参照」

次のような場合を見てみましょう。売上数ランキングを作るためにE6のセルの値(1,944)をI3のセルにコピーしたところエラーになりました。この時の数式バーを見ると、「=F3+H3」となっています。しかしF3とH3のセルには有効な値が入っていませんのでエラーになったわけです。このような場合はどうしたらよいでしょうか。

数式コピー後のエラー表示時画面E6内の数式をI3のセルにコピーしたらエラーになった

1つ目の方法は、I3のセルに数字の「1,944」を直接入力する方法です。しかしこれでは将来、商品の価格が変更になったり、消費税率が変更になったりした場合には、I3のセルも入力し直さなければなりません。

一番簡単で確実な方法は次のとおりです。①E6のセルを選択し、数式バーの数式をドラッグして反転させます。②[F4] キーを押します。すると数式バーの内容が「=$B$6+$D$6」に変わりますので、Enterキーを押します。③次に普通にE6をI3にコピーします。結果のI3のセルの数式バーを見るとコピー元と同じ「=$B$6+$D$6」になっています。

数式バーの数式をドラッグして選択している画面E6のセルで数式バーの数式をドラッグして反転させ、[F4] キーを押す

ここでの「$」という記号は「絶対参照」を示す記号です。「$B$6」はB6のセルを参照するものですが、どのように演算してもこの位置は動かないという意味で絶対参照と呼ばれます。絶対参照の数式をコピーしても参照先は変わりません。

一方、これまでに解説してきた通常の参照を「相対参照」と呼びます。Excelのセル参照は、標準では相対参照になりますので、絶対参照を使う場合は明示的に指定する必要があります。手入力で「$」記号を入力することもできますが、この場合は [F4] キーが便利です。[F4] キーを繰り返して押していくと「B6」→「$B$6」→「B$6」→「$B6」→「B6」のように参照形式が切り替わっていきます。

ちなみに「B$6」と「$B6」は「複合参照」と呼ばれる参照です。「B$6」は、B列は相対参照で6行は絶対参照の形を取り、「$B6」は、B列が絶対参照で6行は相対参照の形を取ります。

知っておくと便利な関数―その1:合計を計算する

合計を計算するSUM関数はExcelで最もよく使われる関数の1つです。下図のような表があるとします。ここでE3からE14までの売上額の合計を計算しE15に入れるには、E15のセルに「=SUM(E3:E14)」と記入します。より簡単な方法としては、E3からE15までをドラッグし、メニューバーから [Σ] をクリックしても入力されます。

SUM関数のボタン表示画面SUM関数はメニューバーに [Σ]のボタンがすでに用意されている

さらに便利なショートカットとして 、[Σ] をクリックする代わりに [Alt] + [Shift] + [=] キーを押しても同様の結果が得られます。

E3とE14の間の「:」は連続した範囲を示す記号で、次の例のようにとびとびのデータの合計には別の記号を使います。

例えば、E6、E4、E5、E10といったとびとびのセルの合計をI3のセルに入力する場合です。これは、「=E6+E4+E5+E10」と入力してもよいのですが、SUM関数を使う場合は、「SUM(E6,E4,E5,E10)」と入力し、Enterキーを押します。

なお、SUM関数の中の「E6,E4,E5,E10」の部分は、[Ctrl] キーを押しながらE6、E4、E5、E10のセルを順番にクリックしても入力可能です。

セルをクリックして合計したいセルを指定する画面[Ctrl] キーを押しながらE6、E4、E5、E10のセルを順番にクリックすると入力が楽だ

このように、とびとびのセルを指定する場合は「,」記号を使います。もちろん、「=SUM(E4:E6,E10)」のように「:」と「,」を混在させることも可能です。

知っておくと便利な関数―その2:平均値・最大値・最小値

平均値を求める

AVERAGE関数は平均値を計算し、MAX関数は最大値を、MIN関数は最小値を見つけます。下図のようにC16セルにC3からC14までの平均額、C17セルにC3からC14までの最大額、C18セルにC3からC14までの最小額を計算してみます。

AVERAGE関数で平均値を計算する前の画面C3からC14までの平均値をC16に、最大額をC17に、最小額をC18に計算する場合

①C16セルを選択した状態で、メニューバーの [Σ] の右側にある [▼] をクリックし、プルダウンメニューから [平均(A)] をクリックします。数式バーに「=AVERAGE(A3:D15)」と表示されます。②次に参照先セルを変更するために、数式バーの「A3:D15」をマウスでドラッグして選択します。③マウスでC3からC14までをドラッグします。④Enterキーを押して確定します。これで、C3からC14までの平均額「4,149」がC16に表示されます。

AVERAGE関数の参照先指定画面参照先の範囲はセルをドラッグして変更できるが、数式バーの値を直接修正してもよい

最大値を求める

最大値を求める場合もほぼ同様にできます。①C17セルを選択した状態で、メニューバーの [Σ] の右側にある [▼] をクリックし、プルダウンメニューから [最大値(M)] をクリックします。数式バーに「=MAX(C16)」と表示されます。②次に参照先セルを変更するために、数式バーの「C16」の部分をマウスでドラッグして選択します。③マウスでセルのC3からC14までをドラッグします。④Enterキーを押して確定します。これで、C3からC14の範囲内でも最も値の大きい「7,344」がC17のセルに表示されます

MAX関数の入力時画面MAX関数では指定の範囲内でもっとも値の大きい数字を表示する

最小値を求める

最小値を求める場合も同じです。①C18セルを選択した状態で、メニューバーの [Σ] の右側にある [▼] をクリックし、プルダウンメニューから [最小値(I)] をクリックします。②参照先セルを変更するために、数式バーの「C16:C17」をマウスでドラッグして選択します。③マウスでC3からC14までをドラッグします。④Enterキーを押して確定します。これで、C3からC14の範囲内でもっとも値の小さい「1,512」がC18に表示されます

MIN関数の入力時画面MIN関数では指定の範囲内でもっとも値の大きい数字を表示する

最大値や最小値は、上記のようにデータが少ない場合は画面から簡単に見つけることができますので、あえて関数を使うまでもないのですが、対象データが多い場合は関数で見つける方が簡単になります。ここで紹介した関数以外にも便利な関数がたくさんありますが、それはまた別の機会で紹介します。

ライタープロフィール パソコン工房NEXMAG[ネクスマグ]編集部

パソコンでできるこんなことやあんなこと、便利な使い方など、様々なパソコン活用方法が「わかる!」「みつかる!」記事を書いています。

記事を
シェア