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

ワードとエクセルの練習問題の動画を「Youtube」で無料公開しています

【Excel】ピボットテーブルにはミスを発見するという使い方がある

ピボットテーブル(Pivot Table)は集計するだけではありません。別の記事でも解説しましたが、重複なくデータを抽出したり、コード表を作ったりするときにも使います。

その他に、ピボットテーブルを用いて、元の表のデータが正しく入力されているかを確認することができます。事前にミスがないことを確認することも重要な集計作業の一つです。不正なデータを早く見つけることができたら、何回も集計をやり直さなくても済みます。

そこで、今回はピボットテーブルを用いて入力データの点検作業をすることについて出題します。

目次

1.準備(サンプルファイルはこちら)

わえなび公式サイト(waenavi.jp)では、この記事で使用しているExcelのデータを無料で配布しています。ご自由にお取りください。7-27、7-27-2の「サンプルファイル(ZIP)」です。

 

2.何を点検すればよいか

問題

48名の学生を、A、B、Kの3班に分けて、それぞれ班長と副班長を1名ずつ選出した。さらに1組~8組に分けて、A班、B班、K班の学生が2名ずつになるようにした。データに誤りがないことを確認するには、どのようなことを点検すればよいか全て挙げなさい。

f:id:waenavi:20181122044104j:plain 

 

解説

(1)レコード件数

ピボットテーブルを挿入します。

f:id:waenavi:20181122045134j:plain

 

氏名を「値」のフィールドにドラッグします。

f:id:waenavi:20181122045137j:plain

 

これで48人の学生が入力されていることが確認できます。リストで最初に確認するのは、全部で何件のデータがあるかを確認することです。これをレコード件数といいます。

f:id:waenavi:20181122045141j:plain

 

(2)重複がないことの確認

48件のレコードに重複がないことを確認します。氏名を「行」のフィールドにドラッグします。

f:id:waenavi:20181122045147j:plain

 

全部「1」になります。

f:id:waenavi:20181122045150j:plain

 

これで48名の学生がダブることなく入力されているものと推定できます。このようにピボットテーブルを用いてレコードの数を求めることによって、重複や矛盾がないことが確認できます。これによって、正しい作業をしているものと推定できます。

f:id:waenavi:20181122045153j:plain

 

推定できるだけであって、元のデータが完全に正しいことを保証するものではありませんが、ミスを未然に防いだり、ミスの原因を探すことができます。

f:id:waenavi:20181122045156j:plain

 

例えば、リストを作った人からチェックを頼まれた場合、ピボットテーブルでチェックをします。

f:id:waenavi:20181122045159j:plain

 

(3)グループが正しく入力されているか

次に氏名の代わりに班を「行」のフィールドにドラッグします。

f:id:waenavi:20181122045202j:plain

 

16人ずつになります。これで同じ人数になっていることがわかります。

f:id:waenavi:20181122045205j:plain

 

仮に、班が割り当てられていない人がいれば、空白の数が表示されるはずです。このように数を数えることによってミスを見つけることができます。

f:id:waenavi:20181122045208j:plain 

f:id:waenavi:20181122045211j:plain

 

代表者を列のフィールドにドラッグします。

f:id:waenavi:20181122045217j:plain

 

班長と副班長が1名ずつ選出され、そのほかに14人ずついることがわかります。これで正しくグループ分けができたことを確認することができます。

f:id:waenavi:20181122045221j:plain

 

(4)クロス集計によるカウント

組を「行」のフィールドにドラッグします。氏名を「値」のフィールドにドラッグします。

f:id:waenavi:20181122045225j:plain

 

これで6人ずつに分かれていることがわかります。

f:id:waenavi:20181122045227j:plain

 

班を「列」のフィールドにドラッグします。

f:id:waenavi:20181122045230j:plain

 

2人ずつに分かれていることがわかります。

f:id:waenavi:20181122045233j:plain

 

3.変更によるピボットテーブルの更新

問題

48名の学生を3チームに分けて、それぞれ班長と副班長を1名ずつ選出した。

f:id:waenavi:20181122045239j:plain

このリストを用いてピボットテーブルを作成した。

f:id:waenavi:20181122045243j:plain

さらに、次のように変更しなさい。

(1)学生番号02番 班長に昇格
(2)学生番号05番 Aチーム(班長)に移籍
(3)学生番号07番 Bチームに移籍、副班長に昇格
(4)学生番号08番 総監督に昇格

 

解説

Bチーム副班長だった学生番号02番が班長に昇格したとします。これでBチームの班長は2人になるはずです。

f:id:waenavi:20181122045526j:plain

 

しかし、ピボットテーブルを見ても変わっていません。

f:id:waenavi:20181122045529j:plain

 

更新のボタンを押します。2人になりました。

f:id:waenavi:20181122045532j:plain

 

元のリストを変えた場合、それに基づいてピボットテーブルを作っている場合には、必ず更新します。

f:id:waenavi:20181122045535j:plain

 

学生番号05番のチームをAにします。学生番号07番のチームをBにして副班長にします。そして、学生番号08番を総監督にします。

f:id:waenavi:20181122045538j:plain

 

ピボットテーブルを更新します。各チームの人数は16人ずつで、班長と副班長が1人ずつになっています。これで元のリストが正しく変更できたものと推定することができます。

f:id:waenavi:20181122045542j:plain

 

4.応用問題:アンケート回答の点検

問題

来場者100名からアンケートの回答を得たので、その質問と100人分の回答を入力した。
入力にミスがないか確認しなさい。

f:id:waenavi:20181122043813j:plain

 

解説

(1)バックアップを取ること

このように大量のデータを人間の目で確認することは不可能です。そこでピボットテーブルを用いて点検しましょう。

f:id:waenavi:20181122052712j:plain

 

まず点検をする前にバックアップを取ります。シート自体をコピーするか、別の名前を付けてファイルを保存します。

f:id:waenavi:20181122052718j:plain

 

データを点検するときには、点検をしている最中にデータが消えるのを防ぐため、かならずバックアップを取ります。

f:id:waenavi:20181122052724j:plain

 

(2)データの個数に矛盾がないこと

次にデータの個数に矛盾がないことを確認します。

f:id:waenavi:20181122052731j:plain

 

8行目以降の回答データでピボットテーブルを作ります。

f:id:waenavi:20181122052734j:plain

 

回答用紙のナンバーを「値」のフィールドにドラッグします。「値」フィールドの設定を変えて、データの個数にします。

f:id:waenavi:20181122052737j:plain

 

101になります。

f:id:waenavi:20181122052740j:plain

 

同じデータを2回入力している可能性があります。

f:id:waenavi:20181122052743j:plain

 

8行目にオートフィルタを設定します。オートフィルタを設定します。オートフィルタを使えば不正なデータを素早く見つけることができます。同一のデータが2つあります。

f:id:waenavi:20181122052746j:plain

 

行を削除します。

f:id:waenavi:20181122052752j:plain

 

ピボットテーブルを更新して確認します。

f:id:waenavi:20181122052755j:plain

 

(3)数値データが正しい範囲に収まっていること

質問1から順に回答数を確認します。空白があることがわかります。実際の回答用紙を見て、入力漏れなのか無回答なのかを確認します。

f:id:waenavi:20181122052801j:plain

 

同じように質問2を見ます。回答数は正しいですが、6という回答があります。

f:id:waenavi:20181122052804j:plain

 

質問2は1から5までの選択肢しかありません。

f:id:waenavi:20181122052807j:plain

 

数値データの場合はその範囲内に収まっていることも同時に確認します。

f:id:waenavi:20181122052810j:plain

 

オートフィルタで確認します。6という入力がありますがこれは明らかに間違いです。

f:id:waenavi:20181122052813j:plain

 

実際の回答用紙を見て確認します。

f:id:waenavi:20181122052816j:plain

 

修正したら必ず更新ボタンを押して確認します。

f:id:waenavi:20181122052822j:plain

 

質問3は複数回答です。複数回答の場合は、回答で選んでいる選択肢に1と入力して、フラグを立てています。

f:id:waenavi:20181122052828j:plain

 

選択肢4で2という数値が見つかりました。

f:id:waenavi:20181122052834j:plain

f:id:waenavi:20181122052831j:plain

 

1に修正します。

f:id:waenavi:20181122052837j:plain

 

選択肢5は数値ではないものが見つかりました。

f:id:waenavi:20181122052840j:plain

 

実際の回答用紙を見て、確認します。

f:id:waenavi:20181122052846j:plain

 

また、フラグを「1」にしているので、合計を求めると回答個数になります。複数回答で回答した個数をSUM関数で求めます。

f:id:waenavi:20181122052849j:plain

 

これもフィルタで確認します。0=回答していないものが見つかりました。

f:id:waenavi:20181122052855j:plain

 

実際の回答用紙を見て、入力漏れなのか無回答なのかを確認します。

f:id:waenavi:20181122052858j:plain 

 

(4)同じ意味で異なる表記をしていないこと

最後に質問4ですが、回答は東京または大阪しかありません。しかし、なぜか東京も大阪も2つに分かれています。

f:id:waenavi:20181122052901j:plain

 

大阪は漢字の入力のミス、東京はスペースが入っていることが原因です。

f:id:waenavi:20181122052904j:plain


日本語で入力されているものは、同じ意味で違う入力がされていないか、入力ミスがないを確認します。

f:id:waenavi:20181122052908j:plain 

 

(5)日本語を大量に入力するのは間違い

なお、日本語で大量のデータを入力するのは間違いです。

f:id:waenavi:20181122052911j:plain

 

このように数値や記号で入力するようにします。

f:id:waenavi:20181122052914j:plain

 


解説は以上です。


5.動画版はこちら(無料)

この記事は、わえなびファンダメンタルExcel Program7-27、7-27-2 の動画の内容を書き起こし、加筆修正したものです。

動画版(完全版)は、Youtubeにすべて無料で公開しております。ぜひ、ご覧ください。

ファンダメンタルExcel 7 データベース(全28回)【わえなび】 - YouTube
https://www.youtube.com/playlist?list=PLRaY8kd5CoxOnpijKZLSbMRv37GQXv1Ko



Youtube わえなび公式チャンネル
http://www.youtube.com/c/waenavijp

チャンネル登録(無料)をすると最新の動画をチェックできます。
初心者向けの動画が大量にあります。

 

 

 

 

 

Copyright(C)2018 waenavi, All rights reserved. [www.waenavi.com 定礎 平成30年8月]