【データリテラシー】第3回 セルの参照と集計
セルの参照
セルの参照とはセルの場所を列番号と行番号で指定してそのセルの値を参照することである。
セルの参照には相対的参照と絶対参照の二通りの方法がある。
列番号か行番号の前に「$」を付けると絶対参照、何も付けないと相対参照になる。
例えば、列番号が「B」で行番号が「4」のセルの参照方法は以下のように4通りある。
| セルの参照方法 | 列番号 | 行番号 |
|---|---|---|
B4 | 相対参照 | 相対参照 |
$B4 | 絶対参照 | 相対参照 |
B$4 | 相対参照 | 絶対参照 |
$B$4 | 絶対参照 | 絶対参照 |
- 列番号と行番号は半角で入力する。
- 列番号は大文字と小文字を区別しない。小文字で入力しても大文字になる。
相対参照と絶対参照の違い
相対参照と絶対参照では、コピー&ペーストのときに動作が異なる。
相対参照が入っているセルをコピー&ペーストすると、貼り付け先のセルで列番号、もしくは行番号が相対的に変化する。
それに対して、絶対参照ではそのような変化は起こらない。
例えば、次のようなセルを用意する。
| A | B | |
|---|---|---|
| 1 | りんご | すいか |
| 2 | みかん | メロン |
列番号と行番号の相対参照
- セル C1 に「
=A1」と入力すると、セル A1 の内容が表示される。 - セル C1 をコピーしてセル D1 に貼り付けると、セル D1 は「
=B1」となり、セル B1 の内容が表示される。 - セル C1 をコピーしてセル C2 に貼り付けると、セル C2 は「
=A2」となり、セル A2 の内容が表示される。
| C | D | |
|---|---|---|
| 1 | =A1 | =B1 |
| 2 | =A2 |
列番号と行番号の絶対参照
- セル C1 に「
=$A$1」と入力すると、セル A1 の内容が表示される。 - セル C1 をコピーしてセル D1 に貼り付けると、セル D1 は「
=$A$1」となり、セル A1 の内容が表示される。 - セル C1 をコピーしてセル C2 に貼り付けると、セル C2 は「
=$A$1」となり、セル A1 の内容が表示される。
| C | D | |
|---|---|---|
| 1 | =$A$1 | =$A$1 |
| 2 | =$A$1 |
列番号を相対参照、行番号を絶対参照
- セル C1 に「
=A$1」と入力すると、セル A1 の内容が表示される。 - セル C1 をコピーしてセル D1 に貼り付けると、セル D1 は「
=B$1」となり、セル B1 の内容が表示される。 - セル C1 をコピーしてセル C2 に貼り付けると、セル C2 は「
=A$1」となり、セル A1 の内容が表示される。
| C | D | |
|---|---|---|
| 1 | =A$1 | =B$1 |
| 2 | =A$1 |
シートの参照
別のシートのセルを参照したい場合は「シート名.セル」のようにシート名とセルを「.」で繋げて記述する。
例えば、Sheet2 の B2 セルを参照したい場合は「Sheet2.B2」となる。
セル範囲の参照
連続する複数セルから成る範囲をまとめてセル範囲として参照できる。
セル範囲の参照は「セル1:セル2」のように「:」を使用する。
ここで「セル1」は指定したい範囲の一つの角のセル、「セル2」は指定したい範囲の反対の角のセルである。
「セル1」「セル2」にも相対参照と絶対参照の参照方法が使える。
セル範囲はマウスでドラッグしても参照できる。
行のセル全部、列のセル全部という参照もできる。
例えば、「A:A」で列 A のセル全部、「2:4」で行 2 から行 4 までのセル全部という参照になる。
数式
セルに「=」で入力を始めると数式として認識される。
数式は数字と演算子と関数を組み合わせることで様々な計算や処理ができる。
数式の中で文字列は「"」で囲む必要がある。
| 入力例 | 説明 |
|---|---|
=1+2 | $1+2$ の結果を表示する。 |
=B5 | セル B5 を参照して内容を表示する。 |
=C5+7 | セル C5 の内容に 7 を足した結果を表示する。 |
=B1&C1 | セル B1 の内容とセル C1 の内容を結合して表示する。 |
=IF(A5="koeki","公益","") | セル A5 が “koeki” という文字列なら “公益” と表示し、それ以外なら何も表示しない。 |
=SUM(B2:E5) | セル B2 からセル E5 までのセル範囲の内容を合計した結果を表示する。 |
算術演算子
数値計算を行うために用いる演算子である。
| 算術演算子 | 意味 | 優先順位 |
|---|---|---|
+ | 加算（足し算） | 低 |
- | 減算（引き算） | 低 |
* | 乗算（かけ算） | 中 |
/ | 乗算（わり算） | 中 |
^ | べき乗 | 高 |
算術演算子の優先順位によって演算の順序が決まる。
「()」を用いることで演算の順序を変更できる。
| 入力例 | 計算式 | 結果表示 |
|---|---|---|
=5+4*3 | $5+4\times 3=5+12$ | 17 |
=2^10/5 | $2^{10}\div 5=\frac{2^{10}}{5}=\frac{1024}{5}$ | 204.8 |
=(5+4)*3 | $(5+4)\times 3=9\times 3$ | 27 |
=2^(10/5) | $2^{(10\div 5)}=2^2$ | 4 |
比較演算子
IF 関数を使用して「真（TRUE）」または「偽（FALSE）」の答えを返す数式に使用する演算子である。
| 比較演算子 | 意味 |
|---|---|
= | 左辺と右辺が等しいか？ |
<> | 左辺と右辺が等しくないか？ |
< | 左辺が右辺より小さいか？ |
<= | 左辺が右辺以下か？（$\le$） |
> | 左辺が右辺より大きいか？ |
>= | 左辺が右辺以上か？（$\ge$） |
テキストの演算子
& はテキストとして結合するための演算子である。
| 入力例 | 結果表示 |
|---|---|
=A1&"円" | “100円”（セル A1 が 100 の場合） |
関数
表計算ソフトにおいて関数とは、ある入力に対して計算や処理を行ってその結果を出力するものである。
関数は以下の形をしている。
関数名(引数)
引数（ひきすう）は関数に渡す入力で、引数が複数の場合は「,」で区切って記述する。
関数名は半角の大文字と小文字を区別しない。
LibreOffice Calc にはあらかじめ多くの関数が用意されており、Microsoft Excel と互換性がある。
関数を利用するには数式バーの関数ウィザードを使ってもよいが、慣れれば直接入力した方が早い。
SUM 関数
| 関数 | 説明 |
|---|---|
| SUM(引数) | 引数の合計を計算する。 引数には数値・セル・セル範囲を記述できる。 引数には「 ,」で区切って複数記述できる。 |
例えば、以下のシートを用意する。
| A | B | C | |
|---|---|---|---|
| 1 | 5 | 9 | -3 |
| 2 | 3 | 6 | 0 |
| 3 | 12 | 1 | 3 |
列 A の合計
列Aのセル（A1 から A3 まで）の合計を計算する。
- セル A4 に「
=SUM(A1:A3)」と入力する。
この他に以下のいずれかでも同じことができる。
- セル A4 に「
=A1+A2+A3」と入力する。 - セル A4 に「
=SUM(A1,A2,A3)」と入力する。
この二つのようにセルを一つずつ記述する方法は、セルが多くなると大変になるので、最初の SUM 関数にセル範囲を使う方法が楽である。
列 B の合計
列 B のセル（B1 から B3 まで）の合計は、列 A の合計と同じように入力してもよいが、先ほどセル A5 に入力した数式を相対参照で利用すると楽である。
- セル A4 をコピーしてセル B4 に貼り付ける。
貼り付け先のセルの数式では、セルの列番号が 1 つ増えて列 A から列 B になる。
行 1 の合計
行 1 のセル（A1 から C1 まで）の合計を計算する。
- セル D1 に「
=SUM(A1:C1)」と入力する。
全てのセルの合計
全てのセルの合計を計算する。
- セル D4 に「
=SUM(A1:C3)」と入力する。
SUMPRODUCT 関数
| 関数 | 説明 |
|---|---|
| SUMPRODUCT(セル範囲1, セル範囲2) | セル範囲1とセル範囲2のセルの積の合計を計算する。 |
これはベクトルの内積と同じ計算である。
例えば、以下のシートを用意する。
| A | B | C | |
|---|---|---|---|
| 1 | 5 | 9 | -3 |
| 2 | 3 | 6 | 0 |
| 3 | 12 | 1 | 3 |
行1 と行2 のセル同士の積の合計
$\mathrm{A1\times B1+A2\times B2+A3\times B3}$ を計算する。
- 空のセルに「
=SUMPRODUCT(A1:A3,B1:B3)」と入力する。
$\mathrm{(A1-5)^{2}\times B1+(A2-5)^{2}\times B2+(A3-5)^{2}\times B3}$ を計算する。
- 空のセルに「
=SUMPRODUCT((A1:A3-5)^2,B1:B3)」と入力する。
論理関数
| 関数 | 説明 |
|---|---|
| IF(論理式, 真の場合の値, 偽の場合の値) | 論理式が「真」か「偽」かに応じて値を返す。 |
| AND(引数) | 引数の論理式のすべてが「真」なら「真」を返し、それ以外は「偽」を返す。 引数には論理式を2つ以上「 ,」で区切って記述する。 |
| OR(引数) | 引数の論理式のどれか一つが「真」なら「真」を返し、それ以外は「偽」を返す。 引数には論理式を2つ以上「 ,」で区切って記述する。 |
| NOT(論理式) | 論理式が「真」なら「偽」、「偽」なら「真」を返す。 |
論理式には比較演算子を用いて記述する。
| 入力例 | 結果表示 |
|---|---|
=IF(A1>=B1,A1-B1,B1-A1) | A1-B1の値 （セル A1 がセル B1 以上の場合） B1-A1の値 （セル A1 がセル B1 未満の場合） |
=IF(A1=0,"零",IF(A1>0,"正","負")) | "零" （セル A1 が 0 の場合）"正" （セル A1 が 0 より大きい場合）"負" （セル A1 が 0 より小さい場合） |
=IF(AND(A1>=3,A1<5),"正常","異常") | "正常" （セル A1 が 3 以上かつ 5 未満の場合）"異常" （セル A1 が 3未満または 5 以上の場合） |
=IF(NOT(D1="男"),0,1) | 0 （セル D1 が "男" 以外の場合）1 （セル D1 が "男" の場合） |
=IF(D1<>"男",0,1) | 0 （セル D1 が "男" 以外の場合）1 （セル D1 が "男" の場合） |
2つ目の例のように IF 関数を入れ子にすると複雑で分かりにくくなるので、代わりに IFS 関数を使うと簡潔に書ける場合がある。
演習：IF 関数
- 使用するデータセット：データセット2
これはある架空の学生の集団 500人の授業外学修時間と架空の科目の評価の点数のデータである。
ダウンロードした CSV ファイルを LibreOFfice Calc で開くと、列 E から列 J に各科目の評価の点数のデータが並んでいる。 行 1 は各列の項目を表すラベルである。 列 A の「番号」は個人（個体）を識別するためのものである。
各科目の点数から GP（グレード・ポイント）を求める。 GP とは以下のように点数に応じて 0 点から 4 点までを割り当てた得点のことである。
| 点数 | GP |
|---|---|
| 90点以上 | 4 |
| 80点以上90点未満 | 3 |
| 70点以上80点未満 | 2 |
| 60点以上70点未満 | 1 |
| 60点未満 | 0 |
まず、セル K1 から P1 に次のようにラベルを入力する。
| K | L | M | N | O | P | |
|---|---|---|---|---|---|---|
| 1 | 経営学基礎のGP | 地域政策論のGP | 地域福祉演習のGP | 実用英語のGP | 観光論GP | 情報学のGP |
経営学基礎のGP
- セル K2 に以下のいずれかを入力すると 番号1 の学生の「経営学基礎の GP 」が求められる1)。
- 「
=IF(E2>=90,4,IF(E2>=80,3,IF(E2>=70,2,IF(E2>=60,1,0))))」 - 「
=IF(E2>=90,1,0)+IF(E2>=80,1,0)+IF(E2>=70,1,0)+IF(E2>=60,1,0)」
- セル K2 をコピーして、セル K3 から K501 に貼り付けると、セル E3 から E501 の点数に応じて「経営学基礎の GP」が求められる。
他の科目のGP
- セル K2 をコピーして、セル L2 から P501 に貼り付けると、他の科目の GP が求められる。
演習：並べ替えとフィルター
- 使用するデータセット：データセット1
これはある架空の集団 500 人の「性別」「年齢」「身長」「体重」のデータである。
ダウンロードした CSV ファイルを LibreOffice Calc で開くと、以下のようなデータが並んでいる。
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | 番号 | 性別 | 年齢[歳] | 身長[cm] | 体重[cm] |
| 2 | 1 | 女 | 22 | 160.6 | 67.2 |
| 3 | 2 | 男 | 19 | 171.6 | 60.0 |
| 4 | 3 | 男 | 21 | 173.2 | 70.8 |
| ⋮ | ⋮ | ⋮ | ⋮ | ⋮ | ⋮ |
行 1 は各列の項目を表すラベルである。 列 A の「番号」は個人（個体）を識別するためのものである。
このようなデータにおいて、性別ごとに分けたり、年齢順に並べて見たいことがある。 そのときに役立つのが並べ替え（ソート）とフィルターである。
オートフィルター
表計算ソフトには並べ替えやフィルターを細かく行える機能がついている。 ここでは、簡単で使いやすいオートフィルターについて説明する。
表のセル（どれでもよい）を選択してメニューバーの [データ]→[オートフィルター] を選択するか、ツールバーの [オートフィルター] のボタンをクリックする。
すると、表のラベルの各セルに [▼] のマークがつく。
この [▼] をクリックして現れるメニューから項目を選択することで並べ替え（ソート）や選別（フィルター）が行える。
オートフィルターを解除するには、もう一度同じ操作を行う。
並べ替え（ソート）
並べ替え（ソート）は、各列を数字の小さい順（昇順）、もしくは大きい順（降順）に並べ替える機能である。 文字列の場合、文字コードの順2)になる。
オートフィルターでは、並べ替えをしたい列のラベルにある [▼] をクリックして、メニューから [昇順でソート] もしくは [降順でソート] を選択する。
複数の列に優先順位をつけたり、行方向の並べ替えを行いたい場合は、メニューバーの [データ]→[並べ替え] で行う。
選別（フィルター）
選別（フィルター）は、列の中で指定した条件に合う行だけに絞り込む機能である。
オートフィルターでは、フィルターをしたい列のラベルにある [▼] をクリックして、メニューから選択する。
| 項目 | 機能 |
|---|---|
[トップ10] | 値の大きい方からトップ10のセルを選別する。 |
[空白] | 空白のセルを選別する。 |
[空白でない] | 空白以外のセルを選別する。 |
[標準フィルター] | 値の大小や複数の列など様々な条件で選別を行う。 |
[検索アイテム] | 抽出したい文字を入力して抽出する。 |
| チェックリスト | チェックリストから手動で選別したい文字列や数字を選択する。 |
フィルターの解除はチェックリストからできる3)が、オートフィルターごと解除した方が手っ取り早い。
課題
演習で求めた データセット2 の各科目の GP を使って GPA（グレード・ポイント・アベレージ）を列 Q に計算しなさい。 簡単のために各科目の単位数は 2 とする。
GPA の定義は以下の通りである。 \[ \mathrm{GPA}=\frac{(各科目の\mathrm{GP}\times 単位数)の合計}{単位数の合計} \]
例えば、演習で求めた番号1 の学生の GP は以下の通りである。
| K | L | M | N | O | P | |
|---|---|---|---|---|---|---|
| 1 | 経営学基礎のGP | 地域政策論のGP | 地域福祉演習のGP | 実用英語のGP | 観光論GP | 情報学のGP |
| 2 | 2 | 1 | 4 | 4 | 3 | 2 |
この 6 科目の GP より、GPA は \begin{eqnarray*} \mathrm{GPA}&=&\frac{2\times 2+1\times 2+4\times 2+4\times 2+3\times 2+2\times 2}{2+2+2+2+2+2}\\ &=&\frac{(2+1+4+4+3+2)\times 2}{6\times 2}\\ &=&\frac{8}{3}\\ &=&2.66\cdots\\ &=&2.7 \end{eqnarray*} と計算される。 最後はできれば セルの書式設定 で小数第1位までの表示とする。
作成したファイルは ODFスプレッドシート（拡張子 .ods）で提出すること。
ファイル形式を ODFスプレッドシート（拡張子 .ods）にするには ファイル形式の変更 を参照。