Excel 関数・数式の活用（計算・検索）【第6章,難易度★★★★☆基本編】

開催中のセール・キャンペーン情報

(難易度：★★★★☆）

このページではMicrosoft Excelの基本操作について説明します。
Excelには様々な関数が用意されています。この関数を利用して、様々な計算・検索等が行われています。関数を自在に扱えることで飛躍的に作業効率を上げることも可能です。

今回は、単純な計算から関数の使用方法について、筆者の業務上でよく使う関数に絞ってご説明します。

Excel 四則演算の方法,やり方(初心者むけ簡易版)

まずは関数を扱う前に簡単な演算について説明します。Excelでは簡単な演算は直観的に計算することができます。
例えば、1+1を計算するときは、計算結果を表示するセルに”=1+1″のように、頭に”=”を入れて計算式を入力します。

逆に”=”を入れ忘れると文字列として扱われるので注意しましょう。また、逆に”=1+1″という文字列を入力したいときは、頭に ‘ (シングルクオーテーション)を頭に入れると文字列として扱われます。

数式の入力と文字列の入力

数式を入力するとき、”+”のように、計算に使用する記号を”演算子”と呼びます。計算で利用する主な演算子は下記の通りです。

- 加算　：+

例) 3+2 →　”=3+2″(セルには5と表示される)

- 減算　：-

例) 3-2 → “=3-2″(セルには1と表示される)

- 乗算　：*

例) 3×2 → “=3*2″(セルには6と表示される)

- 除算　：/

例) 3÷2 → “=3/2″(セルには1.5と表示される)

- べき乗：^

例) 3^2 → “=3^2″(セルには9と表示される)

これと計算順序を指定する”(“,”)”を組み合わせることで四則演算が実行できます。

Excelの演算例

目次へ戻る

Excel 関数の使い方

関数を用いる時も四則演算のやり方と同じように”=”を頭文字として入力し、演算子でつなぎます。
例えば最小値を算出する”MIN”という関数があります。例えばテストの点数の最低点+10点を基準に補習をかけたいなどというとき、この基準点を計算するときは
”=MIN(30,40,50,60)+10″
と記載すると、30,40,50,60の最小値である30に10を足した、40が表示されます。

MIN関数演算例

このように、関数で得られる値が数値の場合は関数を混ぜた四則演算ができます。

目次へ戻る

Excel 相対参照・絶対参照

前述の例では関数に直接数字を入れましたが、Excelを活用する際にこのような例はまれで、実際はセルを指定してセルの値を基に計算します。
このときセル番号を指定することを参照と呼びます。例えばセルA2,A3,A4,A5の最小値を求めるときは、
“=MIN(A2,A3,A4,A5)” または
“=MIN(A2:A5)”
という形で表します。A2:A5はA2からA5までの連続のセルという意味で、A1:B1、A1:C6、A:Cなどのようにあらわします。

セル名は直接入力しても良いですが、マウスのクリック＆ドラッグやキーボードカーソルでセル範囲を指定することでも入力できます。範囲を入力する位置に文字入力のカーソルがあるタイミングで、参照先のセルをクリック（＆ドラッグ）してみましょう。数式バーにはセル番号が表示されるはずです。これを用いて、ほかのセルに入力された値を用いた計算を行うことができます。
一例として、先ほどのMIN関数を用いた計算を下図のように実行することもできます。

参照を用いた計算例

この参照には相対参照と絶対参照があり、それぞれの特徴について説明します。

相対参照

前述のようにセルを指定すると、通常は相対参照になります。これは参照元と参照先の位置関係からこのように呼ばれます。
例えばX3セルに”=V3″と入力されているとします。これでX3にはV3の値が表示されます。V3はX3の2つ左ですね。このときにX3セルをコピーしてX5セルへ貼り付けると、X5セルは”=V5″となります。やはりV5はX5の2つ左です。このように相対的な位置関係が崩れないため、”相対参照と呼ばれます。

相対参照

この相対参照はExcelの入力効率化にとても役立ちます。2つ例を挙げてみます。
１つ目は、1から順番に数字を並べたいとき、コピーもできないしかといってすべて打ち込むのはとても大変ですよね。そんな時の効率的に入力する１つのやり方に相対参照を用いる方法が有効です。
たとえばB2に1を打ち込み、B3には”=B2+1″と打ちこみます。このときB2をコピーしてB3に貼り付けると、相対参照のためB3には”=B2+1″が入ります。これを繰り返すことで、コピー＆ペーストで数字を並べて打つことができます。

相対参照の利用例１

２つ目は、行ごとや列ごとに最大・最小・平均などを求める場合です。例えば下図のように縦に学籍番号、横に各教科の点数が入力されている表で教科ごとの平均点を計算することを考えます。

まずは国語の平均点を求めるため、C16に”=AVERAGE(C3:C15)”と入力します。これでC16には国語の点数の平均が表示されます。次にC16をコピーしてD16～G16に貼り付けます。相対参照で関数を作成しているため、D16には”=AVERAGE(D3:D15)”、E16には=”AVERAGE(E3:E16)”が入力されます。

相対参照の利用例２

このように行ごと・列ごとに計算したいときには相対参照が有効となります。

絶対参照

相対参照が参照元・参照先の位置関係を保存する参照だったのに対して、参照元を”固定”するイメージになるのが絶対参照です。つまりコピー＆ペーストしても同じ場所を参照することになります。なぜこんな回りくどい言い方をするかというと、この”固定”するのは列のみ、行のみ、セルの３パターンあるからです。

相対参照との比較をしてみましょう。先ほどと同じように、X3にV3を参照した値を表示させます。ただし今回は絶対参照で指定します($を付けます)。すると、これをX5にコピーしても、参照している値は固定されるため、V3の値が表示されます。

絶対参照

もう少し例をあげましょう。少しややこしいので、下の図を見ながら追いかけてみてください。例えばaX+aY+Cを求めるとします。aはA列に、cはA2に、X,YはそれぞれB,C列に入力されており、aXをD列,aYをE列、aX+aY+Cの計算結果をF列に表示するとします。

このとき少ない手数でD～F列に入力を行おうとすると、例えばまずはD4に、”=$A4*B4″と入力します。これはA4のAの前に$がついているため、Aが固定されています。一方でB4は何もついていないので相対参照となっています。この状態で、D4をD5にコピペすると、D5には”=$A5*B5″が参照され行番号は固定されていないことがわかりますが、E4に貼り付けるとE4には”=$A4*C4″が表示され、A4のAが固定されていることが確認できます。

絶対参照利用例１

同様にF4には”=D4+E4+$A$2″と入力し、最後にD4～F4をコピーしてD～Fの該当列に貼り付ければすべての計算を終了できます。

絶対参照利用例２

最初はややこしく感じるかもしれませんが、使っていくうちに自然と慣れていきます。慣れれば入力効率は格段に上がります。是非どんどん使ってみましょう。

目次へ戻る

Excelでよく使う関数の例

関数には上述のように数値計算を行う関数や、検索・条件分岐に使える関数等があります。それぞれよく使う関数の例を説明します。

目次へ戻る

Excelの数値計算を行う関数

MAX、MIN、AVERAGE、SUM

MAXは最大値、MINは最小値、AVERAGEは平均値、SUMは合計値、どれも選択した範囲内でそれぞれの値を算出する関数です。
非常に使いやすく便利なので、使用頻度が高い関数です。使用例は図を参照ください。

MAX,MIN,AVERAGE,SUMの使用例

VAR.P、VAR.S、STDEV.P、STDEV.S

こちらはややマニアックです。統計量として、分散値、標準偏差を算出するときに使用します。
選択した範囲内のデータを標本として、各統計量を算出します。.Pと.Sの差異は、データが母集団全てであれば.Pを用い、標本（母集団の一部）であるならば.Sを用いる、というものです。用途に合わせて使い分けてみてください。

VAR.P、VAR.S、STDEV.P、STDEV.Sの使用例

目次へ戻る

Excelの条件分岐・検索で使える関数

IF、COUNTIF、SUMIF

条件分岐が絡む関数が使えると一気にExcelの活用幅が広がります。その中でもよく使うのはこの３つです。意外と使われていない関数もありますが、是非覚えたい関数です。
条件の書き方は各関数若干の癖がありますので、こういうことができることを覚えて置き使用する際に状況に合う条件の書き方を調べるやり方が良いと思います。

IF関数・・・条件を満たしている場合と満たしていない場合の処理を分ける関数。

使い方：IF(条件 , 条件を満たす場合の処理や数式や参照 , 条件を満たさない場合の処理や数式や参照）

COUNTIF関数・・・範囲内で条件を満たすセルの数を数える関数。

使い方：COUNTIF(範囲 , 検索条件)

SUMIF関数・・・検索範囲内で条件を満たすセルをすべて加算する関数。検索するセルと加算するセルは別のセルで指定することもできる（分ける方ことが多い)。

使い方：SUMIF(検索範囲 , 検索条件 , [加算範囲]) ※[]内は入力任意。入力が無い場合は検索範囲と同じ範囲として処理される。

使用例を下図で示しますので、条件の書き方等参考に是非使ってみてください。

IF関数・COUNTIF関数の使用例（補習受講生徒の抽出、学生毎の補習数のカウント）

SUMIF関数の使用例（文系・理系各科目のみの合計）、

VLOOKUP、HLOOKUP

こちらも非常によく使う関数です。特定の文字列や値を探し、発見した行や列の特定セルに入力されている値を参照します。文字列を発見した”行”のセルを参照する関数がVLOOKUP関数、文字列を発見した”列”のセルを参照する関数がHLOOKUPとなります。エクセルの性質上VLOOKUPを使う機会が非常に多いですが、合わせてHLOOKUPも覚えておくとまわりに一歩差をつけられます。
VLOOKUP関数・・・検索文字列（値）を探し、同じ行の特定セルの値を参照する関数

使い方：VLOOKUP(検索値 , 検索・参照範囲 , 検索列から参照列までの列数 , 完全一致か否か) ※HLOOKUPも使い方は同じ。

検索・参照範囲：指定した範囲の一番左の列が検索列でかつ、参照する列が含まれる範囲で指定
検索列から参照列までの列数：検索列を1として、一つ右なら2,二つ右なら3,・・・という具合に数値を入力
完全一致か否か：FALSE：完全一致　TRUE：完全一致と限らない　※FALSEを指定すること推奨

Lookup関数の使用例(学籍番号で検索する）

この関数は非常に便利ですが、一つのブックで大量に使用するとExcelの動作が重くなるという欠点も持っています。まずはどんどん使っていき、もし重くなり始めたら別案も並行して考えながらうまく使いこなしていきましょう。

COUNTA

上記の２つの関数と比較すると使う場面は少ないですが、縁の下の力持ち的に力を発揮する関数です。こちらは指定した範囲内で、何かしらの値・文字列が入力されているセルの個数を表示します。在庫管理をするときなど、入力行が何行か確認したり、データ抜けの数を見積もるなどいろいろな場所で力を発揮します。

countAの使用例

使用方法は範囲指定のみで使いやすい関数です。初期はあまり使用頻度が多くないかもしれませんが、是非頭に入れておきたい関数の一つです。

目次へ戻る

Excelの文字列操作で使用する関数

LEFT,RIGHT

こちらも、意外と困ったときに役立つ関数です。参照先の文字列から、右〇文字、左〇文字だけ抜きたい、というときにその抜取りを行える関数です。

RIGHT関数・・・文字列の右から指定文字数分を切り取る関数

使い方：RIGHT(参照する文字列のセル番号 , [切り取る文字数]) ※LEFTも使い方は同じ。
※[切り取る文字数]は入力が無い場合は1文字を切り取る。それ以外は入力された数字の文字数だけ抽出して表示する。

RIGHT関数の使用例

文字列からINDEX情報だけを取得したいときなど、意外と使う関数なので是非覚えておきましょう。

目次へ戻る

Excelの関数を検索する方法

関数について、理解は進みましたでしょうか？ほかにどのような関数があるか気になった方もおられるかもしれませんね。紹介したいところですが、非常に多くの関数があるのでここでは関数の調べ方について説明します。

今の時代、ネットで調べる方が早い場合も多いですが、Excelの機能の中に関数検索機能があります。
目的が明確で、かつよく使われる処理は関数化されていることが多いです。一度調べてみるのも良いでしょう。

検索方法は、関数が入力されていないセルをアクティブセルにした状態で数式バーの横の”fx”ボタンをクリックします。ジャンルがわかる場合は関数の分類を選択します。わからない場合も、重要なワードがある場合はそのワードを入力し、検索開始をクリックします。候補が下に出てくるため、説明を読み目的の関数か判断しましょう。

関数検索の手順