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

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

集計用ファイルのダウンロード http://www.cc.kochi-u.ac.jp/~kitagawa/ へアクセス

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

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

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

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

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

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

コードの解読方法 コード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ならば保体選択，それ以外なら保体選択なし 一番右の桁：………

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

コード解析に必要な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セルで割ったあまり

コード解析に必要な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を返す

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

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

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

エクセルへの実装３ 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)’ と入力（改行せずに一行で入力する）

もしこの条件が正しいときは’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で 割ったあまり 1234 → 234 D2セル下三桁の 十の位以下を切り捨て 234 → 200 求めた値が400，500，600もしくは700のとき 動作チェック もしこの条件が正しいときは’1’，正しくないときは’0’を返す

エクセルへの実装４ 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)’

エクセルへの実装５ 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)’

エクセルへの実装５ 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)’

エクセルへの実装６ 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)’

エクセルへの実装７ 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)’

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

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

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

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