roy > naoya > 実用情報 > (7)表計算ソフト[3] 絶対参照と論理関数
(7) 06/01の授業内容:表計算ソフト[3] 絶対参照と論理関数
[1]前回の復習
前回はアンケートデータに、自分の回答を追加した上で、無効データの除外を行った。この際に用いた関数が、count、counta、countblank、countifであったが、使い方は覚えられただろうか。これらの関数の使い方をマスターするため、前回と同一の作業を行ってみよう。
以下のデータは、先週配布したデータに、実用情報履修者（皆さん）のデータを追加したものである。ダウンロードしてzドライブに保存後、もう一度無効データを除去してみよう。
本日の作業1
無効データ除去の基準は以下の通りとする（先週と同じ）。
- 3、4に回答漏れのあるデータ（countblank）
- 3、4に数字以外の文字列が入力されているデータ（counta-count）
- 3、4に0以下の数字が入力されているデータ（countif）
- 3に4を超える数字が入力されているデータ（countif）
- 4に2を超える数字が入力されているデータ（countif）
1行目の見出しが常に見えるよう、ウィンドウ枠の固定も行っておくこと。
[2]逆転項目への対応
次に、前回学んだオートフィルについて復習するため、逆転項目への対応を行う。質問紙の3はコンピュータ不安について4段階で評価をするものであったが、
- コンピュータは人間の弱点を補ってくれる便利な機械だ
- 私はコンピュータの前に座っただけでとても緊張してしまうだろう
というそれぞれの質問に対し、いずれも右端の「全くそう思わない」に○をつけたとする。この場合、右端なのでいずれも「4」と入力するが、両者が持つ意味は異なっている。それゆえ、このままの状態で平均などを求めても、得られた結果は何を意味するかわからなくなってしまう。
得られた結果が意味を成すためには、回答の方向をそろえる必要がある。今回はコンピュータ不安に関する質問であるため、「値が大きいほど不安を感じている」という結果になっていた方が、直感的に読み取りやすい。そこで、これとは反対の関係になっている項目を逆転項目とみなすことにする。
逆転項目は、左から1、2、3、4と番号を振るのではなく、右から1、2、3、4（＝左から4、3、2、1）と番号を振る。一つずつ手直ししていくのは大変なので、一括で変換してしまおう。
1を4に、2を3に、3を2に、4を1にするためには、それぞれ5から引けばよい。
- 1を4にする：5-1=4
- 2を3にする：5-2=3
- 3を2にする：5-3=2
- 4を1にする：5-4=1
これを踏まえて次の作業を行おう。
本日の作業2
EK列に21列追加し、新規に3-1から3-21という見出しをつける。その上で、逆転項目は5から元の値を引いた値を計算し、通常項目はそのままの値を複写する。
「DP2」というセルに1と入力されている場合を例にすると、それぞれ次のように入力する。
- 逆転項目の場合：=5-DP2
- 通常項目の場合：=DP2
21問の質問項目につき、それぞれ1人目のデータだけ、上記のルールに則った値の変換を行い、残りの人についてはオートフィルで複写する。
なお、逆転項目は、2、4、5、6、8、11、12、13、14、16、17、18、19、21の14項目である。
[3]習熟率の合計得点の算出
2番は、コンピュータの機能や各種ソフトウェアについてどれくらい習熟しているかをたずねる質問項目であった。全部で44項目あるが、6つのカテゴリに分類されている。そこで、カテゴリごとに合計点を算出してみよう。
本日の作業3
- DP列に6列挿入する。見出しを順番に「基礎知識」「共通操作」「インターネット」「メール」「ワープロソフト」「表計算ソフト」とする
- これら6つのカテゴリについて、それぞれ合計得点を求める。sum関数を使用する。1人目だけ式を入れたら、残りの人はオートフィルで複写する
- 各質問項目とカテゴリとの対応状況は以下の通り
- 基礎知識：2-1から2-8
- 共通操作：2-9から2-13
- インターネット：2-14から2-18
- メール：2-19から2-23
- ワープロソフト：2-24から2-35
- 表計算ソフト：2-36から2-44
[4]コンピュータ不安の合成得点の算出
3番は、コンピュータの不安に関する質問であり、21の質問項目から構成されていた。これらの項目は似通った内容のものもある。それゆえ各質問に対して回答するにあたり、根底にある「機械への苦手意識」や「新しいものを避ける傾向」などの意識が影響していると考えることができる。
しかし、こうした意識は目には見えないため、直接的に質問することができない。それゆえ、多くの項目について質問し、その回答の類似性から、回答の背後にある意識構造を探っていく手法として、因子分析という手法が用いられることがある。
因子分析という手法そのものは、統計学の授業で学ぶべき内容なのでここでは説明しない。今回はすでに因子分析を実施しており、それぞれの質問項目が3つのグループ（因子）に分かれることが明らかになっている。
- 第1因子：オペレーション不安（質問2、6、16）
- 第2因子：テクノロジー不安（質問11、14、18）
- 第3因子：肯定的評価（質問1、3、7）
これを見ると、いずれの因子にも所属しない項目があることがわかる。これらの項目はどの因子との関連もそれほど高くないということになる。
本日の作業3
- 3は先ほど得点の変換を行ったが、その後ろの3列に「オペレーション不安」「テクノロジー不安」「肯定的評価」を見出しとして記入する
- ここで各因子に属する質問項目の合計を求める。今回は計算に使用する値が連続していないが、このような場合でもsum関数を使うことができる
[5]セキュリティに関する理解度の得点化
4番は、セキュリティに関する質問項目である。このうち（1）は用語を知っているかどうかをたずねる認知度の項目であるが、（2）は内容を知っているかどうかたずねる理解度に関する項目であった。
（2）は○を「1」、×を「2」と入力しているが、このままでは正解か不正解かわからない。そこで、正解の場合は「1」、不正解の場合は「2」となるよう、数値を変換してみよう。この際に使用するのが論理関数のifと絶対参照である。
相対参照と絶対参照
これまでcountやsumなどいくつかの関数を使用してきた。いずれも最初の1人目のみ関数を用いた式を書いておき、2人目以降はその式を複写することで結果を得てきた。例えば、sum関数では合計を求める範囲を指定する必要があるが、式を複写しても結果が出るということは、複写した際に指定した範囲も自動的に変化してくれているということになる。
このように、式を複写した際に参照するセルが相対的に変化する機能のことを、相対参照と呼ぶ。
相対参照を使うことで、作業が極めて容易になる。同じ計算を100回繰り返す場合、最初の1行目だけ式を書いておけば複写できるためである。しかし、相対参照がうまく使えないケースもある。以下の例を見てみよう。
| A | B | C | |
| 1 | 名前 | 得点 | 50 |
| 2 | 一郎 | 30 | -20 |
| 3 | 二郎 | 50 |
ここではA列に名前、B列にテストの点が入力されている。C1には平均点が記載されている。ここでC2以降で平均点との差を求めることにする。C2には「=B2-C1」と入力し、計算結果の-20が表示されているものとする。
では、先ほどと同様にC2をコピーしてC3に複写してみよう。二郎さんは50点をとっており平均点と同じなので0になるはずである。しかし結果は70になる。セルにどのような式が入力されているか調べると「=B3-C2」となっていることが確認できる。
セル参照を用いて式を書いた場合、式を複写すると参照するセルも変化する。1つ下のセルに複写したため参照するセルもそれぞれ1つ下に移動している。これは正しい働きであるが、このケースでは参照するセルが変化すると都合が悪い。より厳密に言うと、B2は1つ下のセルに移動してB3に移動してほしいが、C1は1つ下のC2に移動するとまずいことになる。
式を複写した際に参照するセルを変化させない方法もある。自動的に変化するのが相対参照であるが、固定する方法を絶対参照という。
絶対参照は以下の3種類がある。手前に$をつけることで、つけた部分を固定することができる。
- $C1：列（C)のみ固定する。式を横に複写する場合に有効。
- C$1：行（1）のみ固定する。式を縦に複写する場合に有効。
- $C$1：行列いずれも固定する。式を全く別の場所に複写する場合に有効。
今回は下に複写した際にC1がC2に変更しないようにしたいため、1を固定する必要がある。よってC2に「=B2-C$1」と書き、C3に複写すればよい。
論理関数if
| A | B | |
| 1 | 1 | |
| 2 | 2 | |
| 3 | 3 | |
| 4 | 1 | |
| 5 | 4 |
上記は4択問題に対する5名の回答であるとする。正解が1の場合、2名が正解で残りの3名は不正解となる。いちいち「正解」「不正解」と入力するのは大変なので、関数を使ってみよう。例えばB1のセルには次のように書けばよい。
もしもA1が1ならば正解と表示する。そうでない場合は不正解と表示する。
これは「A1は1である」という命題に対する真偽の判断となる。真であるならば「正解」を表示し、偽であるならば「不正解」と表示する。真偽判断に用いる関数がifである。if関数の基本構造は以下の通りとなる。
=if(条件式,真の場合,偽の場合)
()内に記載するのは3つである。それぞれの境界はカンマであるが、Calcの場合はカンマではなくセミコロンとなる。今回の場合は次のように書く。真の場合、偽の場合に表示するのは数字ではなく文字であるため""をつけている。
=if(A1=1,"正解","不正解")
Enterを押すと、真偽の判断が行われB1には正解と表示される。
なお、真の場合、偽の場合に表示する値が数字の場合には""は不要である。
=if(A1=1,1,0)
if文の条件式の書き方
- A1が2である：A1=2
- A1とA2が等しい：A1=A2
- A2がB3以上：A2>=B3
- A2が10を超える：A2>10
- A3が20以下：A3<=20
- A3がA5未満：A3<A5
- A4が100：A4=100（数字なので""不要）
- A4がりんご：A4="りんご"（文字なので""必要）
ifの入れ子
| A | B | |
| 1 | 70 | |
| 2 | 85 | |
| 3 | 45 | |
| 4 | 65 | |
| 5 | 90 |
先ほどの例では「正解」「不正解」の2通りしかなかったが、さらに複雑な場面もある。例えば次のようなケースを考えてみよう。
- 80点以上：A
- 70点以上：B
- 70点未満：C
if関数はある条件式を記述し、それを満たす場合と満たさない場合という2つの選択肢しか記述することができない。このため単純にifを使うだけでは上記の問題を解決できない。このような場合はif関数を入れ子にする。具体的には以下のように書く。
=if(A1>=80,"A",if(A1>=70,"B","C"))
A1が80以上という条件式を満たす場合、Aが表示される。満たさない場合は2つ目のif関数が適用され、2つ目の条件式のA1が70以上であるという条件式を満たすかどうか判定が行われる。満たす場合はBが表示され、満たさない場合はCが表示される。このようにif関数を2つ入れ子にすることで3種類の処理を行うことができる。処理内容が4つある場合はif関数を3つ入れ子にすればよい。
これらについて、練習用ファイルを使いながら、用法を確認してみよう。
本日の作業4
- 4番の(2)の後ろに30列挿入する。その上で見出しに「4-2-1」から「4-2-30」まで連番を振る。
- 下の表を参考に、300行目あたりの邪魔にならない箇所に解答を入力する。
- 各自の入力データと解答を比較し、一致する場合は「1」、一致しない場合は「0」に変換する
- さらに10列追加し、見出しに「ウィルス」「セキュリティホール」「スパム」「フィッシング」「スパイウェア」「ボット」「ファーミング」「ワンクリック」「DoS」「ソーシャル」と入力する。
- 各項目について3問の合計得点を求める。
| 問1 | 問2 | 問3 | 問4 | 問5 | 問6 | 問7 | 問8 | 問9 | 問10 |
| 2 | 1 | 1 | 2 | 1 | 1 | 1 | 2 | 1 | 1 |
| 問11 | 問12 | 問13 | 問14 | 問15 | 問16 | 問17 | 問18 | 問19 | 問20 |
| 2 | 2 | 1 | 1 | 2 | 1 | 2 | 1 | 1 | 1 |
| 問21 | 問22 | 問23 | 問24 | 問25 | 問26 | 問27 | 問28 | 問29 | 問30 |
| 2 | 1 | 2 | 1 | 2 | 1 | 2 | 1 | 2 | 1 |
[6]出席課題
raw_data0601.xlsを添付ファイルで提出する。
提出要領
- To:課題提出用メールアドレス
- Subject:実用情報(7)