情報処理 第１４回：Excelを用いた表計算 その４ July 28, 2016.

Presentation on theme: "情報処理 第１４回：Excelを用いた表計算 その４ July 28, 2016."— Presentation transcript:

1 情報処理 第１４回：Excelを用いた表計算 その４ July 28, 2016

2 今日のテーマ Excelを用いたアンケート集計の工夫 計算機の歴史

3 集計用ファイルのダウンロード へアクセス

4 アンケートの集計 次のようなアンケートの問の集計をしたい． 「卒業要件以外の中学校教員免許のうち，
取得予定であるものをすべて答えてください．」 回答者 Alice： 社会，家庭 Bob： なし Clare： 理科，技術，数学 David： 音楽，保健体育，美術 Eve： 保健体育，美術，家庭 Flora： 国語，美術，音楽 George： 理科 … 順不同 名簿の該当欄には1を入れる．

5 困難なアンケート集計 不特定多数，順不同の解答がある アンケートの回答の集計はやっかい 入力が大変 時間がかかる 入力ミスが発生する
正しい集計ができない 効率よく，かつ誤りを少なく集計する方法はないだろうか？ Web上のアンケートシステムを構築し， 入力結果を自動集計するプログラムを作る？ コンピュータがないと回答できない 後日回答では，忘れ去られる可能性がある

6 アンケート集計方法の工夫 その場で回答してもらえる紙ベースのアンケートで， 集計を楽にする工夫はないだろうか？ 各項目に0/1の番号を振る．
国語 社会 数学 理科 音楽 美術 保体 技術 家庭 英語 逆にコード が決まれば，選択された項目 数学，理科，保健体育が特定できる 桁数が多くて大変！ コードの桁数を減らすことができないか？

7 一点鎖線より右の数字をどのように組み合わせても，左の数字を表現できない
アンケート集計方法の工夫２ 各項目に 2 𝑛−1 (𝑛≥1)の番号を振る． 国語 社会 数学 理科 音楽 美術 保体 技術 家庭 英語 =200 一点鎖線より右の数字をどのように組み合わせても，左の数字を表現できない 任意の数字の足し合わせから， 唯一無二のコードが生成される． 200を512で割る： 0あまり200 200を256で割る： 0あまり200 200を128で割る： 1あまり72 72を 64で割る： 1あまり8 8を 32で割る： 0あまり8 … 数学 理科 … コードの桁は少ないが， 各番号の足し算が大変

8 一点鎖線より右の数字をどのように組み合わせても，左の数字を表現できない
アンケート集計方法の工夫３ 各項目に以下の通りの番号を振る． 国語 社会 数学 理科 音楽 美術 保体 技術 家庭 英語 =310 一点鎖線より右の数字をどのように組み合わせても，左の数字を表現できない 任意の数字の足し合わせから， 唯一無二のコードが生成される． 310を1000で割る： 0あまり310 310を400で割る： 0あまり310 310を200で割る： 1あまり110 110を100で割る： 1あまり10 10を 40で割る： 0あまり10 … コードの桁が少なく， 各番号の足し算も楽 数学 理科 … 労力と効率の程よいバランス

9 アンケート問題用紙の工夫 卒業要件以外で取得予定の免許について，当てはまるものに ○をつけてください．桁ごとに教科の下の数字を加えたものを
一部回答者に協力してもらう アンケート問題用紙の工夫 卒業要件以外で取得予定の免許について，当てはまるものに ○をつけてください．桁ごとに教科の下の数字を加えたものを 所定の欄に記入してください． 国語 社会 数学 理科 音楽 美術 保体 技術 家庭 英語 例：数学，理科，保健体育を選択する場合 0310 国語 社会 数学 理科 音楽 美術 保体 技術 家庭 英語 2+1 3 1

10 コードの解読方法 コード0310を 例にとる． 一番左の桁：1なら国語選択，0なら選択なし 左から二番目の桁：
　　　　例にとる． 一番左の桁：1なら国語選択，0なら選択なし 左から二番目の桁： 4,5,6,7ならば社会選択，それ以外なら社会選択なし 2,3,6,7ならば数学選択，それ以外なら数学選択なし 1,3,5,7ならば理科選択，それ以外なら理科選択なし 左から三番目の桁： 4,5,6,7ならば音楽選択，それ以外なら音楽選択なし 2,3,6,7ならば美術選択，それ以外なら美術選択なし 1,3,5,7ならば保体選択，それ以外なら保体選択なし 一番右の桁：………

11 コードの解読方法 コード1234を 例にとる． 一番左の桁を取り出す：百の位を切り捨てる 1234 → 1000
　　　　例にとる． 一番左の桁を取り出す：百の位を切り捨てる → 左から二番目の桁を取り出す：1000で割ったあまりの 十の位を切り捨て 1234= → → 200 左から三番目の桁を取り出す：100で割ったあまりの 一の位を切り捨て 1234= → → 30 一番右の桁を取り出す：10で割ったあまり 1234= → 4

12 コード解析に必要なExcelの関数 ROUNDDOWN関数：所定の桁で値を切り捨てる MOD関数：ある数の割り算をしたときの余りを求める
123 二つ目の引数で， 切り捨てる桁を指定 123.4 100 MOD関数：ある数の割り算をしたときの余りを求める =MOD(7,3) 1 7÷3=2 あまり 1 ⇔7≡1 mod 3 =MOD(A2,B3) A2セルをB3セルで割ったあまり

13 コード解析に必要なExcelの関数 IF関数：条件によって，セルの値を変える =IF(論理式, [真の場合], [偽の場合]) A2=1
それ以外なら0を返す =IF(A2=1,1,0) =IF(OR(C2=2,C2=3,C2=6,C2=7),1,0) OR関数 C2の値が2,3,6もしくは7なら1を， それ以外なら0を返す

14 エクセルシートの準備 E2セルを選択 項目は固定，データのみスクロールする

15 エクセルへの実装１ 動作テスト E2セルに， ‘=IF(ROUNDDOWN(D2,-3)=1000,1,0)’ と入力

16 エクセルへの実装２ 下のセルにコピー

17 エクセルへの実装３ F2セルに， ‘=IF(OR(ROUNDDOWN(MOD($D2,1000),-2)=400,
絶対参照に注意 F2セルに， ‘=IF(OR(ROUNDDOWN(MOD($D2,1000),-2)=400, ROUNDDOWN(MOD($D2,1000),-2)=500, ROUNDDOWN(MOD($D2,1000),-2)=600, ROUNDDOWN(MOD($D2,1000),-2)=700),1,0)’ と入力（改行せずに一行で入力する）

18 もしこの条件が正しいときは’1’，正しくないときは’0’を返す
構文の解説 =IF(OR(ROUNDDOWN(MOD($D2,1000),-2)=400, ROUNDDOWN(MOD($D2,1000),-2)=500, ROUNDDOWN(MOD($D2,1000),-2)=600, ROUNDDOWN(MOD($D2,1000),-2)=700),1,0) D2セルの値を1000で 割ったあまり → 234 D2セル下三桁の 十の位以下を切り捨て 234 → 200 求めた値が400，500，600もしくは700のとき 動作チェック もしこの条件が正しいときは’1’，正しくないときは’0’を返す

19 エクセルへの実装４ F2セルをG2セルにコピー，一部を書き換える
‘=IF(OR(ROUNDDOWN(MOD($D2,1000),-2)=200, ROUNDDOWN(MOD($D2,1000),-2)=300, ROUNDDOWN(MOD($D2,1000),-2)=600, ROUNDDOWN(MOD($D2,1000),-2)=700),1,0)’

20 エクセルへの実装５ G2セルをH2セルにコピー，一部を書き換える
‘=IF(OR(ROUNDDOWN(MOD($D2,1000),-2)=100, ROUNDDOWN(MOD($D2,1000),-2)=300, ROUNDDOWN(MOD($D2,1000),-2)=500, ROUNDDOWN(MOD($D2,1000),-2)=700),1,0)’

21 エクセルへの実装５ H2セルをI2セルにコピー，一部を書き換える
‘=IF(OR(ROUNDDOWN(MOD($D2,100),-1)=40, ROUNDDOWN(MOD($D2,100),-1)=50, ROUNDDOWN(MOD($D2,100),-1)=60, ROUNDDOWN(MOD($D2,100),-1)=70),1,0)’

22 エクセルへの実装６ I2セルをJ2セルにコピー，一部を書き換える
‘=IF(OR(ROUNDDOWN(MOD($D2,100),-1)=20, ROUNDDOWN(MOD($D2,100),-1)=30, ROUNDDOWN(MOD($D2,100),-1)=60, ROUNDDOWN(MOD($D2,100),-1)=70),1,0)’

23 エクセルへの実装７ J2セルをK2セルにコピー，一部を書き換える
‘=IF(OR(ROUNDDOWN(MOD($D2,100),-1)=10, ROUNDDOWN(MOD($D2,100),-1)=30, ROUNDDOWN(MOD($D2,100),-1)=50, ROUNDDOWN(MOD($D2,100),-1)=70),1,0)’

24 エクセルへの実装８ L2セルに ‘=IF(OR(MOD($D2,10)=4, MOD($D2,10)=5, MOD($D2,10)=6,
を入力（一行で入力）

25 エクセルへの実装９ L2セルをM2セルにコピー， 一部を書き換える ‘=IF(OR(MOD($D2,10)=2,

26 エクセルへの実装９ M2セルをN2セルにコピー， 一部を書き換える ‘=IF(OR(MOD($D2,10)=1,

27 コードの入力 作業が終わったら， 上書き保存しておく