わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

Excelデータクレンジングの「議論」をしよう(総務省ICTスキル総合習得プログラム期末テスト)

困ったことに、情報処理の専門家が集まって情報処理の教材を作ると、知識が豊富すぎて「あれも大事、これも大事」と言いながらいろんなことを盛り込んでしまい、結局、初心者に見向きもされない教材が出来上がってしまいます。国家試験であるITパスポート試験もいろんな知識を盛り込んだ結果、何のパスポートにもならない暗記の試験になっています。

そして、総務省が2018年に公表した「ICTスキル総合習得プログラム」も例外ではありません。

www.youtube.com

 

大学の眠たい授業のように、偉い先生がYoutubeでマニアックな知識を得意げに披露したところで誰が関心を持つのでしょうか??

データ収集や処理、分析といった情報処理の基本を学ぶには、なぜそのような処理が必要なのかを考え、手を動かし、ときにはマイケルサンデル先生のように受講生を議論に参加させるなど、「疑問と議論」を重視した授業を展開するべきです。

そこで、総務省のICTスキル総合習得プログラムの教材(以下、教材という)のうち、コース3-2のデータクレンジングに関する問題を中心に出題します。教材に完全に準拠した問題を出題しますから、教材をボーっと眺めるだけで本当に知識が習得できるものなのか、ぜひ試してみてください。

教材のPDFとExcelのデータはこちら。
http://www.soumu.go.jp/ict_skill/
http://www.soumu.go.jp/ict_skill/pdf/ict_skill_c3_set.pdf
http://www.soumu.go.jp/ict_skill/dc/ict_3_2data.zip

 

目次

なお、さまざまな回答が考えられるかもしれませんが、あくまで教材で学習した内容を回答してください。それ以外の回答は(仮にそれが正しい答えであったとしても)学習内容を習得できていないものとして不正解とします。

f:id:waenavi:20190520173929j:plain

1.基本用語

問題

次の文章は、表計算ソフトウェアMicrosoft Excel(以下、Excelという)で用いられる用語を説明したものである。文章中の空欄に当てはまる語句をそれぞれ答えなさい。ただし、同じ番号の空欄には同じ語句が入る。

 Excelは多くのパソコンにインストールされ、また習得している者が多いため、組織内でデータクレンジング(データクリーニング)の作業を分担して行うツールとしては最適である。

 Excelの形式で保存されたファイルは( 1 )と呼ばれ、1枚または複数枚の( 2 )で構成されている。( 2 )は、1枚分の作業スペースであり、画面の下部の見出しを用いて容易に切り替えることができる。

 ( 2 )のうち格子状に区切られているものは、データを入力または編集することのできるセルという枠が縦横にたくさん並んでいる。この画面の横方向を( 3 )、縦方向を( 4 )といい、番号が付けられている。( 3 )と( 4 )の番号の組み合わせで表されたセルの位置のことを( 5 )という。

 

議論のテーマ

  • なぜ、Excelでは水平方向を行といい、垂直方向を列というのかを議論するべきです。行=横、列=縦といった暗記は絶対にダメです。「Wordは「行」しかないのに対して、Excelは縦の線で区切られているから列の概念がある」ということをしっかりと理解しなければなりません。もちろん、Wordでも表を挿入すれば縦方向を列といいます。

 

正解例

(1)ブック
※ワークブック(Workbook)でもよい。

(2)シート
※ワークシート、グラフシート、マクロシートなどがありますが総称してシート(Sheet)といいます。

(3)
※画面の横方向のことを水平方向ということがある。

(4)
※画面の縦方向のことを垂直方向ということがある。

(5)セル番地
※セル番号、アドレス(Address)、絶対番地などでもよい。

 

2.データクレンジングの目的

問題

携帯型の電話機(スマートフォン等)を保有する学生に、契約している携帯電話会社について図のようなアンケートをしたところ、次のような回答があった。これについて、あとの各設問に答えなさい。なお、各設問の指定文字数は目安です。

f:id:waenavi:20190520154851j:plain

(回答の例)
NTT、docomo、au、au、AU、Rモバイル、ソフトバンク、ドコモ、ドモコ、エーユー、三木谷、三太郎、やわらか銀行、犬、孫、・・・

 

(1)英語と比べて、日本語のほうが集計しにくい理由を20字程度で述べなさい。

(2)人間が手作業で集計する場合とExcelを用いて集計する場合の違いを踏まえて、これらの回答について、データクレンジングを行わなければならない理由を70字程度で述べなさい。

(3)このアンケートの回答方法は不適切である。データクレンジングの手間を減らすという観点でどのように修正するべきか、30字程度で述べなさい。

(4)いっぱんにデータとして企業名を入力する場合、法人マイナンバーを利用する方法がある。どのような利点があるか、50字程度で述べなさい。

 

議論のテーマ

  1. ひらがな、カタカナ、漢字とその送り仮名がある日本語は、英語と比べて、どういった表記ゆれがあるのか?どういった回答パターンが考えられるのか?そして、これらをしっかりと統一しなければどのような弊害があるのか、具体例を使って考察するべきです。
  2. 回答パターンを想定して、表記をどのように統一したらよいかというルールを実際に作ってみるのもいいと思います(これを標準化という)。
  3. 国民に総背番号を付けたり、法人に番号を付けることにはリスクがありますが、もちろん必要性と利便性があるからやっているわけで、データクレンジング作業の観点からどのようなメリットがあるのか理解するべきです。

 

正解例

(1)
漢字や仮名などの表記ゆれが多い。
1つのデータを表すのに表現のパターンが多い。

(2)
人間は文字列が異なっていても同一の企業と判断することが可能であるが、Excelは文字列が異なる場合、データ集計において異なる企業として扱われるから。
※表記を統一しなければ、同一の会社なのに異なるデータとみなされるといった内容であればOK。

(3)
おもな企業にコード番号を付けて、番号で回答させる。
会社表記を標準化してリストから選択できるようにする。

(4)
正式な企業名に統一することができ、同じ名前の企業が複数ある場合でも企業を特定することができる。
※表記ゆれが防げる点と同じ企業名でも区別できる点の2点が記述できていればOK。
※入力が簡単というのはクレンジングとは無関係(そういう問題ではない)なので不可。

 

3.データ整理の準備

問題

次の図は、ある文房具店の売上データの一部を示している。

f:id:waenavi:20190520155947j:plain

 

いま、Excelでデータを整理するための準備作業を終えたところである。

f:id:waenavi:20190520160046j:plain

 

上司と部下の会話中の空欄に当てはまる字句を、それぞれできるだけ簡潔に答えなさい。

上司「Excelで売上の合計を求めるだけなのに、なんでそんなに時間がかかってるのかね。もっとサッサとできないものか?」

部下「きれいなデータだけであれば集計するのは簡単ですが、( 1 )のため、大変な時間がかかっています。」

上司「なんだこれは?一番左に通し番号というものがあるが、これはもともとあった数字か?」

部下「いいえ、データ整理のために私が付けたものです。」

上司「あのねぇ~、Excelにはもともと画面の左端に連番があるんだから、わざわざこんな無駄なもの付けなくてもいいだろ!削除しておくぞ。」

部下「それは困ります。このようなデータを整理するには必ず通し番号を付けなければなりません。それは、( 2 )おそれがあるからです。また、データ集計の際には( 3 )を常に確認しながら作業をしなければなりませんから、通し番号は絶対に必要です。」

上司「じゃあ、Excelで入力したデータは何でも通し番号を付けておけばよいのかね。」

部下「いいえ、データセットに( 4 )がある場合には通し番号を付けなくても良いです。」

上司「あぁそう、でも連番をいちいち入力するのは大変じゃないか。」

部下「( 5 )をします。右下の黒色の四角をダブルクリックで・・・」

上司「ふーん・・・それにしても、さっきから一番上の項目のところだけ動かないんだけど、Excelが壊れちゃったのかね。直してくれたまえ。」

部下「あぁ、それはExcelの( 6 )の機能によって、項目名が動かないようにしています。縦にスクロールしても項目名が隠れなくて済みます。」

(このExcelファイルを閉じた後・・・)
上司「へぇそんなに大変なのかねぇ。あっ、ファイルが消えちゃった。ゴミ箱にもない。」

部下「何やってるんですか??まあ、事前に( 7 )を取っておいたので良かったですけど気を付けてくださいよ。」

 

議論のテーマ

  1. 恐竜の化石や昔の土器を発掘して分析するのに最も時間がかかる地味な作業は「泥を落とすこと」です。クリーニングです。真のデータを傷つけないように汚れを落とす除去作業の大変さは実際にやってみなければ分かりません。100件のデータでどれだけ体力を消耗するのか実際にやってみたらよいと思います。
  2. 連番やコード番号の必要性が分かっていない人が多すぎて困ります。行を一意に特定することはデータ分析にとってどのようなメリットがあるのか?どのような場合に連番を付けるべきか?並べ替えをしたまま保存をしたり、マクロを動かしたりしたらどうなるのか?
  3. オートフィル、ウィンドウ枠の固定、バックアップといったカタカナ語が覚えられないという人がいますが、単なる甘えです。こういう基本用語はしっかりと覚えてください。アクセルとブレーキも分からずに運転できますか?
  4. データ分析の作業を遅らせる要因は何だと思いますか?会話の上司のように40代以上の人たちは学校で情報処理を習ったことが無いのに、その自覚もなく偉そうに振る舞い、若い人の足を引っ張っていることが一番の原因ではないかと思います。

 

正解例

(1)表記ゆれが多くデータ分析の障害になっており、表記を統一する作業が煩雑
※データ分析で最も時間と人手がかかるのがクレンジングだということが理解できていればOK。

(2)並べ替えをしたときに元の順序に戻せなくなる

(3)レコードの件数

(4)ID
※コード番号、コード、識別番号などレコードを識別できるものならOK。左端に識別するためのIDが無いデータセットは、連番を振るのが基本です。

(5)オートフィル
※フィルだけでもよい。教材では、1と2を入力してダブルクリックでオートフィルをするように書いてありますが、1だけでダブルクリックをして連続データに変えることも可能です(オートフィルオプション)。また、ホームタブのフィル(連続データの作成)を用いる方法もあります。

(6)ウィンドウ枠の固定
※先頭行の固定でもよい。ウィンドウ分割は不可。

(7)バックアップ
※自動でバックアップを取る方法もありますが、破損や紛失に備えて、念のためコピーを別の場所に保存しておくのが安全です。

 

4.数値の欠損・異常値

問題

次の図は、ある文房具店の売上データの一部を示している。Excelの画面上部の「F」をクリックしたところ、画面の右下に次のように表示された。

f:id:waenavi:20190520160513j:plain

(画面右下)

f:id:waenavi:20190520160605j:plain

(1)画面の下には、合計値やデータの個数のほか、画面の表示倍率、マクロの開始のボタンなどが表示されている。この部分の名称を7文字で答えなさい。

(2)数値の個数や最大値、最小値が表示されない場合は、どのような操作をすれば表示されるか。

(3)データの個数が1032の場合、すべて正常な数値が入力されていれば、数値の個数はいくつと表示されるか。

(4)図の表示を見て、どのようなデータの異常があると考えられるか述べなさい。

(5)「商品名」には、ひらがなやカタカナが混在し、前後に空白が挿入されているなど、データ集計の障害となるデータが入力されている。これを確認するにはどうすればよいか簡潔に述べなさい。

 

議論のテーマ

  1. ステータスバーは状況を表示するもので、フィルタは抽出するものですが、データクレンジングの作業においては必ずしもそのような使い方はしません。データの健全性の確認をするツールであることを理解しなければなりません。
  2. 最大最小についてどうして9999や-1という値があるのか?入力ミスにしては不自然な数値だと思いませんか?いわゆるフラグと考えられますが、なぜこのような値を用いるのでしょうか?

 

正解例

(1)ステータスバー

(2)右クリックをして該当項目にチェックを入れる

(3)1031
※数値の個数は、データの個数より1小さい(項目名は数値ではない)

(4)数値ではないデータが32個あり、それ以外に、数量として通常あり得ない負の数や異常に大きい値が入力されている。

(5)フィルタ(オートフィルタ)を設定する

f:id:waenavi:20190520160947j:plain

 

5.関数その1

問題

次の図で、セルH2に数式を入力しようとしている。セルF2の値が、0以上1000未満の場合はセルF2の値をそのまま出力し、それ以外の値を「NaN」と表示したい。セルH2に入力すべき数式を正確に記述しなさい。

f:id:waenavi:20190520161409j:plain

 

議論のテーマ

  1. ANDとORを暗記で覚えてはいけません。条件式が1つだけ(F2>=0)の場合と比較して、AND(F2>=0,F2<1000)のように2つ目の条件を付加すると、条件は厳しくなるのか?それとも緩くなるのか?
  2. イコールがあるのとないのとではどのような結果の違いがあるのか?

 

正解例

=IF(AND(F2>=0,F2<1000),F2,"NaN")

※1000未満はイコール不要。

 

6.関数その2

問題

次の各設問の記述はデータクレンジングに用いられるExcelの主な関数について説明したものである。関数名をそれぞれアルファベットで答えなさい。

(1)余分な空白を除去するとともに、文字列の前後の空白を削除する
(2)アルファベットを大文字に統一する
(3)アルファベットを小文字に統一する
(4)半角に統一する
(5)全角に統一する

 

議論のテーマ

  1. 単に関数名を暗記するだけでなく、データクレンジングとして使える関数であることを理解しなければなりません。「文字列の変換」ではなく「表記の統一」として使用していることに注意します。これらの関数を用いることによってどのように統一されるのか実際に作業してみると良いと思います。
  2. 文字コードの概念が分からない文系人間に表記の統一を理解させるのはなかなか難しいことです。スペースがあったらダメなんですか?大文字と小文字が混ざったらダメなんですか?全角じゃダメなんですか?2位じゃダメなんですか?

 

正解例

(1)TRIM
(2)UPPER
(3)LOWER
(4)ASC
(5)JIS

 

7.関数その3

問題

次の図は、商品名の「シャーペン」を「シャープペン」に置換する画面を示している。

f:id:waenavi:20190520162602j:plain

 

(1)データクレンジング作業の際、このように置換の機能を用いて文字列の置き換えをすることがあるが、この時に注意する点を簡潔に述べなさい。

(2)関数を用いて置換をすることがある。J列の「シャーペン」を「シャープペン」に置換する場合、セルK2に入力すべき数式を正確に記述しなさい。

f:id:waenavi:20190520162705j:plain

 

議論のテーマ

  • 置換をすると作業手順や置換した内容が残らないので、置換をした方法とその範囲をどこかに記録しておいたほうが良いです。置換に限らずデータクレンジングの作業をExcelで行うと、作業の記録が残りにくいので、記録を残していつでも見直しができるように注意すべきです。

 

正解例

(1)作業記録を残す
(2)=SUBSTITUTE(J2,"シャーペン","シャープペン")

 

8.マクロ

問題

次の図は、セルJ2にPHONETIC関数を入力した様子を表している。

f:id:waenavi:20190520161626j:plain

 

PHONETIC関数は振り仮名を出力する関数であるが、振り仮名の初期設定が全角カタカナなので、ひらがなや半角カタカナはPHONETICによって全角カタカナに置換される。
しかし、必ずしもPHONETICで取得することができないこともある。このような場合、商品名に標準的な振り仮名を付けるにはイミディエイトウィンドウにどのように入力すればよいか。

f:id:waenavi:20190520161954j:plain

 

議論のテーマ

  1. PHONETIC関数やSetPhoneticメソッドで完全に正しい振り仮名が打てる保証はありません。「東京の三田と兵庫の三田は読み方が違う」という有名な例がありますが、他にどのような例があるでしょうか。
  2. 数式が入力されているセルやCSVを取り込んだデータはPHONETICで振り仮名が出てきませんがその理由は何でしょうか?(IMEなどで漢字変換をするときにその読み方がセルに保存されるからです)

 

正解例

商品名(I列)の列を選択して「selection.setphonetic」と入力してEnter。

※SetPhoneticメソッドを使っていれば正解とする。Visual Basic Editor(VBE)のイミディエイトウィンドウに入力する。
※「range("i:i").setphonetic」「columns(9).setphonetic」などでもよい。この場合は選択不要。

 

9.不要レコードの非表示

問題

次の図は、商品名にアスタリスク***が入力されているものを非表示にする様子を示している。

f:id:waenavi:20190520163034j:plain

 

この結果1000件のレコードが抽出された。このあと、1000件のレコードを用いて、データ集計を行うには最初にどのような作業をするべきか、簡潔に述べなさい。

f:id:waenavi:20190520163140j:plain

 

議論のテーマ

  1. データクレンジングの作業またはその後で、別のシートに値を貼り付けるということをしますが、これは何のためにするのでしょうか?普通にコピーをするだけではダメなのでしょうか?また、これをしなかったらどうなるのでしょうか?
  2. データクレンジングの作業と、分析の作業は分けるべきです。クレンジングと抽出の作業が終わったレコードは別のシートに貼り付けて分析します。また、関数などの数式を消すため、必ず、値で貼り付けます。

 

正解例

1000件のレコードを全部をコピーして、別のシート(または新規ブック)に値のみ貼り付ける

 

10.ピボットテーブルとグラフ

問題

次の図はデータクレンジングとデータ抽出作業を終えたリストである。

f:id:waenavi:20190520163710j:plain

 

セルB2の日付は文字列なので、1列挿入してセルC2に次のような数式が入力してシリアル値を求めた。また、セルG2に単価X数量を求めた。

  • セルC2:=DATEVALUE("2019年"&B2)
  • セルH2:=F2*G2

f:id:waenavi:20190520163833j:plain

 

このリストをもとにピボットテーブルを挿入した。

(1)日別売上合計を求めて折れ線グラフを作成しなさい。

(2)商品別売上合計を求めて降順で円グラフを作成しなさい。

(3)曜日別平均売上額を求めて横棒グラフを作成しなさい。

(4)商品別、曜日別売り上げ合計を求めて、横棒グラフまたは積み上げ横棒グラフを作成しなさい。

f:id:waenavi:20190520163940j:plain

 

議論のテーマ

  1. 教材では、曜日別集計や日別集計を関数を用いて求めていますが、ピボットテーブルを用いる方法もあります。関数を用いて求める場合と、ピボットテーブルを用いる場合ではどのような違いがあるのでしょうか?(どちらにもメリットとデメリットがあります)
  2. ピボットテーブルの場合、グラフを描くとピボットグラフになりますが、あえてピボットグラフではなく、普通のグラフを作りたいときは、値の貼り付けによって別の場所に貼り付けをしてから、グラフを作成すればよいです。

 

正解例

(1)

f:id:waenavi:20190520164537j:plain

 

(2)降順で並べ替えるときは、右クリック→並べ替え です。

f:id:waenavi:20190520164854j:plain

f:id:waenavi:20190520165149j:plain

 

(3)

f:id:waenavi:20190520165400j:plain

 

(4)
XXX別YYY別売上とは、XXXのグループの中にYYYの内訳を表示することを意味しています。「商品別、曜日別」売り上げ合計は、商品別が第1優先であることを表しています。なお、ピボットテーブルの場合は2通りの表示の仕方があります。

(行集計の場合)

f:id:waenavi:20190520170052j:plain

f:id:waenavi:20190520170245j:plain

 

(クロス集計の場合)

f:id:waenavi:20190520170829j:plain

 


解説は以上です。


 

 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]