表計算２

数式と関数

関数は、Excel を初めとする表計算ソフトにおいて、とても重要な機能の一つです。関数を用いることで、色々な種類の複雑な計算を一瞬で導き出すことができます。

次のファイルをダウンロードして、Excelで開いてください。そして、表を完成させてみましょう。

いちご狩りファイル(ichigo.xlsx)

いちご狩りファイル

数式処理

数式処理はセルの先頭が（半角の）= で始まります。これをセルに直接入力、もしくは数式バーから入力します。

数式の入力例

Excelで用いる主な算術演算子は以下のようなものがあります。

記号	処理内容
+	和（加算）
-	差（減算）
*	積（乗算）
/	商（除算）
^	べき乗
%	パーセント（百分率）

算術演算子はいずれも半角で入力します。演算の優先順位は、基本的には通常通りですが、括弧 () を用いることもできます。また、これ以外にも多くの関数による演算が可能です。

参考資料演算子についての補足

SUM関数

SUM(数値1,[数値2],...)

SUM() は、 () 内の数値の総和を求める関数です。この関数を用いて、それぞれのいちごの "合計(g)" を求めます。前回説明していますので、復習のつもりでやってみましょう。

"F3" を選択します。
次に[ホーム]リボン - [編集]タブ - [オート SUM]をクリックします。

オートSUM
すると、自動的に選択範囲が関数に入力されます。[Enter]を押すと、総和が "F3" に入力されます。

SUM関数の挿入

SUM() のように頻繁に利用する関数は、オートSUM 機能で簡単に挿入できます。もちろん、関数を式として直接数式バーに入力しても構いません。

相対参照と絶対参照

Excel でデータを扱う上で、参照という概念を理解する必要があります。ここで登場する 絶対参照 と 相対参照 を使い分けることで、より手軽に演算できるようになります。

相対参照

オートフィルを使って、全ての合計を出します。選択したセルの右下にある四角の上にカーソルを持って行き、"F12" までドラッグします。

フィルハンドルをドラッグ

オートフィル結果
Excelの数式の部分に注目します。 "F3" の数式が表示されています。

F3 の数式
次に "F12" を選択します。すると、数式が変わりました。

F12 の数式"

数式の変化

オートフィルはコピー＆ペーストと同じ機能を果たしています。しかし、ペーストされたのは、セル番号が違う数式です。つまり、コピーされたセルに記録されていたのは、セル番号ではなく、セルの位置です。これを 相対参照 と呼びます。

相対参照は入力されているセルを基点として、各セルの位置を決める参照方法です。そのため、コピー＆ペーストした結果、ペーストされたセルから同じだけ移動した位置にあるセルを参照します。

参照部分の移動

AVERAGE関数

AVERAGE(数値1,[数値2],...)

AVERAGE() は、 () 内の数値の平均を求める関数です。この関数を用いて、それぞれが平均でどのくらいのいちごを採ったのか調べてみましょう。

まず "E13" を選択し，平均 と入力します。
次に、"F13" を選択します。[ホーム]リボン - [編集]タブ - [オートSUM] の下向き矢印をクリック - [平均]をクリックします。

編集パネルから平均を選択

AVERAGE 関数を挿入した状態
[Enter] を押します。すると、平均が求められました。

平均の計算結果

RANK関数

RANK(数値,範囲,順序)

RANK() は、範囲内の数値が何番目の順位かを導き出します。順序には 0（降順／高い方が順位が高い）か 1（昇順／低い方が順位が高い）のいずれかを設定します。

誰が一番いちごを採ったのかを求めることとします。

"A3" を選択します。
[ホーム]リボン - [編集]タブ - [オート SUM]の右下 - [その他の関数]をクリックします。もしくは、[数式]リボン - [関数ライブラリ]タブ - [関数の挿入]をクリックします。

編集パネルその他の関数
関数の検索で検索をかければ出てきますが、ここでは[関数の分類] - [統計] - RANK.EQ - [OK]をクリックします。

関数の挿入ダイアログ統計を選択

リストから RANK.EQ を選択
関数の引数のウインドウの数値に順位を調べたい数値を入れます。ここでは、 "F3" を選択します。
次に範囲を比べる数値の全体を選択します。ここでは "F3" から "F12" までを選択します。さらに順序を降順にするために 0 と入れます。

関数の引数（ひきすう）ダイアログ

関数の設定項目
[Enter]を押します。すると、上杉さんの収穫量の順位が出ました。

演算結果

絶対参照

一人目について順位が求められたため、オートフィルを用いてそれぞれの収穫量の順位を求めることとします。

"A3" から "A12" までドラッグしてオートフィルを用います。
A 列に順位が求められました。しかし、"A5" と "A6" 、 "A11" を見ると 2 が３つあります（緑枠）。これは順位としておかしいので、修正しなくてはなりません。
"A12" を選択して、数式を確認してください。相対参照しているため、RANK関数の範囲がずれていることがわかります。（赤枠がA3の範囲、青枠がA12の範囲を表す）

相対参照でオートフィルが正しく機能しない例
ここで、絶対参照を使って、正しい順位を導き出しましょう。まず "A4" から "A12" までを選択 - 右クリック - [数式と値のクリア]でクリアします。
"A3" を選択 - 数式バーの範囲の部分の F3 にカーソルを移動 - [F4] キーを押します。

参照セルの固定
F3が$F$3に変わりました。$` がついた列・行は、固定された状態になります。これが絶対参照の状態です。同様に "F12" も [F4] キーで絶対参照の状態にします。
オートフィルで先ほどと同じように "A3" から "A12" まで伸ばします。
先ほどとは違う結果が出ました。 "A12" を選択して、範囲が移動していないことを確認してください。

正常化された順位

これが 絶対参照 です。絶対参照は表計算ソフト上のセル番号で位置を記録しておく参照方法です。どの位置にあっても、同じセルを参照することができます。

絶対参照と相対参照をうまく使い分けることによって、より便利にExcelを使うことができます。

表を整える

同じようなものですが、多少変わった視点から表作りをしましょう。四則演算や総和、オートフィルも再び練習します。人口統計表の作成と重複する部分もありますが、何回も練習・確認することにより技術を磨きましょう。

以下の表を前回作った rensyu.xlsx の Sheet2 に入力しますが、その前にセルの表示形式を設定します。 rensyu.xlsx にシートを追加します。この追加された Sheet2 に新たに値を入力します。

整形前の表

入力が終わったら、先にセルの表示形式を設定します。

セルの表示形式（通貨スタイル）

ここでデータ入力前にセル "B3" から "E12" までの書式を通貨としましょう。操作は以下の通りです。

"B3" から "E12" までをドラッグし、選択します
[ホーム]リボン内の[数値]パネルの標準を通貨に変更します
この書式を設定した後に、上の画像のデータを入力します。ただし、¥1,000 と入力せずに、単に 1000 と入力してください。
先ほどの設定によって、[表示形式]が通貨になっているため、Excelが自動的に通貨記号とカンマを補います。

入力が終わったら、集計計算を行います。簡単な整頓を行って、表を完成させましょう。

"A6" に 用紙分支出 と入力します。
4月の用紙消費金額を求めるため、"B6" に数式を入力します。
4月の支出総額を求めるため、"B12" に数式を入力します。
5月の用紙消費金額と支出総額を求めるための数式を、それぞれ "C6" 、 "C12" にオートフィルで入力します。
"D1" に 6月 と入力します。
6月の用紙消費金額と支出総額を求めるための数式を、それぞれ "D6" 、 "D12" にオートフィルで入力します。各備品の支出額はまだ空欄のままで問題ありません。
"E3" から "E10" まで、及び "E12" を各項目の4月から6月の総和として処理します。
6月の各項目に適当な数値を入力します。その総和と数式処理のセルがその入力値に従って変化することを確認してください。