これまでの記事で、データ準備の基礎知識と、Excelでの外れ値・不正回答スクリーニングの考え方を解説してきました。今回はその総仕上げとして、実際に架空のアンケートデータ（60名分）を用意し、条件付き書式・関数を使って一つずつスクリーニングしていく過程を、実際の画面とともに紹介します。
「関数の説明を読んだだけでは、実際にどう手を動かせばいいのかイメージが湧かない」という人に向けて、今回はできるだけ具体的に、実際の数値・結果を示しながら進めます。
1. 今回使用するデータについて
今回使うのは、大学生60名を対象にした架空のアンケートデータです。「短尺動画の視聴習慣が学習集中力に与える影響」を検証する研究を想定し、以下の項目を含んでいます。
- 基本属性（性別、年齢、学年）
- 動画視聴時間（1日あたり・分）
- 学習集中力に関する4項目（Q1〜Q4、5段階のリッカート尺度。Q3は逆転項目）
- SNS利用頻度・SNS利用時間
- 回答時間（秒）
実際のデータ収集の過程で起こりがちな「怪しい回答」を、あらかじめ意図的に6種類・16件仕込んであります。これから、この16件をどうやって見つけ出すかを、順を追って見ていきます。
実際に使用したデータを張り付けておくので、練習でやってみたい方はダウンロードしてみてください。
2. ステップ1：データ全体を把握する
いきなり関数を組み立てる前に、まずはデータ全体をざっと眺めて、明らかな異常がないかを確認します。空いているセルに、以下のような集計を作ってみました。
| 列 | 件数 | 最小 | 最大 | 平均 |
|---|---|---|---|---|
| 年齢 | 60 | 3 | 150 | 23.0 |
| 動画視聴時間 | 60 | 0 | 900 | 125.7 |
| SNS利用時間 | 60 | 0 | 4.8 | 2.0 |
| 回答時間 | 60 | 8 | 395 | 225.2 |
COUNT・MIN・MAX といった基本的な関数だけで集計しましたが、この時点ですでに気になる数字が見えてきます。年齢の最小が「3」、最大が「150」というのは、どう考えても実際の大学生のデータとしてはありえません。動画視聴時間の最大「900分」（15時間）も、平均の125.7分と比べると突出しています。
このように、細かい条件分岐の前に、まず基本統計量でざっくりとおかしいところがないか把握しておくことが、次のステップ以降を進める上での見当をつける助けになります。
3. ステップ2：欠損値をチェックする
まず、回答に空欄（無回答）がないかを確認します。M列に以下の関数を入力しました。
=IF(COUNTBLANK(B2:L2)>0,"要確認","")COUNTBLANK は指定した範囲の中にある空白セルの数を数える関数です。性別から回答時間までの範囲（B列〜L列）に一つでも空欄があれば「要確認」と表示させています。
結果
3件が該当しました。
| 該当行 | 回答者ID | 欠損している項目 |
|---|---|---|
| 5行目 | id=4 | Q1 |
| 18行目 | id=17 | Q3 |
| 34行目 | id=33 | Q4 |
いずれも、学習集中力を測る4項目（Q1〜Q4）のうち1項目だけが未回答というパターンでした。この3件は、後ほど「学習集中力の合成得点を使う分析」からは除外し、それ以外の分析（視聴時間だけを見るなど）では活用する、という方針にします。
4. ステップ3：ストレートライニングをチェックする
次に、Q1〜Q4のすべてが同じ値になっている、いわゆる「ストレートライニング」がないかを確認します。N列に以下の関数を入力しました。
=IF(COUNTIF(F2:I2,F2)=4,"要確認","")COUNTIF(F2:I2,F2) で、「Q1〜Q4（F2〜I2）の中に、Q1（F2）と同じ値がいくつあるか」を数えます。4項目すべてが同じ値であれば、この結果は「4」になります。
結果
3件が該当しました。
| 該当行 | 回答者ID | Q1〜Q4の値 |
|---|---|---|
| 10行目 | id=9 | すべて3 |
| 25行目 | id=24 | すべて1 |
| 46行目 | id=45 | すべて5 |
特にid=24（全項目「1」＝全くそう思わない）とid=45（全項目「5」＝非常にそう思う）は、極端な値で統一されているため、内容を読まずに機械的に選択した可能性が高いと判断しました。id=9（全項目「3」＝どちらでもない）は無難な回答を選び続けた可能性もありますが、今回は同じ基準で3件とも要確認として扱うことにしました。
5. ステップ4：年齢の異常値をチェックする
O列に、年齢の妥当性をチェックする関数を入力しました。
=IF(OR(C2<18,C2>100),"要確認","")年齢（C列）が18歳未満、または100歳を超える場合に「要確認」とします。大学生を対象にした調査なので、18歳という下限は妥当な目安です。
結果
2件が該当しました。
| 該当行 | 回答者ID | 年齢 |
|---|---|---|
| 8行目 | id=7 | 3歳 |
| 40行目 | id=39 | 150歳 |
ステップ1の全体把握の段階で見えていた「最小3、最大150」という異常値が、ここで具体的にどの行かが特定できました。
6. ステップ5：SNS利用の矛盾回答をチェックする
P列に、SNS利用に関する矛盾をチェックする関数を入力しました。
=IF(AND(J2="全く利用しない",K2>0),"要確認","")SNS利用頻度（J列）が「全く利用しない」と回答しているのに、SNS利用時間（K列）が0より大きい、という矛盾を検出します。
結果
3件が該当しました。
| 該当行 | 回答者ID | SNS利用頻度 | SNS利用時間 |
|---|---|---|---|
| 14行目 | id=13 | 全く利用しない | 2.5時間 |
| 29行目 | id=28 | 全く利用しない | 2.9時間 |
| 53行目 | id=52 | 全く利用しない | 3.2時間 |
このパターンは、論理的に矛盾しているため、修正のしようがありません。除外が妥当と判断しました。
7. ステップ6：動画視聴時間の外れ値をチェックする
Q列に、統計的な外れ値をチェックする、少し複雑な関数を入力しました。
=IF(OR(E2>AVERAGE($E$2:$E$61)+3*STDEV($E$2:$E$61),E2<AVERAGE($E$2:$E$61)-3*STDEV($E$2:$E$61)),"要確認","")「平均 ± 標準偏差の3倍」の範囲を超える値を外れ値とみなす基準です。ここでのポイントは $E$2:$E$61 の $（絶対参照）です。これがないと、数式を下の行にコピーするたびに参照範囲がずれてしまい、正しく計算できません。
結果
2件が該当しました。
| 該当行 | 回答者ID | 動画視聴時間 |
|---|---|---|
| 21行目 | id=20 | 900分 |
| 57行目 | id=56 | 850分 |
この2件について、他の項目（年齢、回答時間、SNS利用頻度）に矛盾がないかも確認しましたが、特に矛盾は見当たりませんでした。単純な入力ミスとは言い切れないため、除外するか、あるいは分析上の上限値でキャップする（例：600分に置き換える）かは判断が分かれるところです。今回は「要検討」として保留し、最終的な卒論の中でこの判断基準を明記することにしました。
8. ステップ7：回答時間が極端に短い回答をチェックする
最後に、内容を読まずに回答した可能性がある、極端に短い回答時間をチェックします。まず、判定の基準となる閾値を別セルに用意しました（U1セルに「30」＝30秒）。
R列には以下の関数を入力しました。
=IF(L2<$U$1,"要確認","")閾値をセルに独立させておくことで、後から「30秒」を「20秒」に変えたいときも、U1セルの数字を書き換えるだけで、R列全体の判定が自動的に更新されます。
結果
3件が該当しました。
| 該当行 | 回答者ID | 回答時間 |
|---|---|---|
| 4行目 | id=3 | 14秒 |
| 42行目 | id=41 | 13秒 |
| 59行目 | id=58 | 8秒 |
全体の平均回答時間が約225秒（約3分45秒）だったことを踏まえると、8〜14秒という回答時間は明らかに短く、除外が妥当と判断しました。
9. 全体の集計と除外方針の決定
ここまでの7つのチェックをまとめると、60件中16件が何らかの「要確認」に該当しました。今回のデータでは、幸い一人の回答者が複数の問題を同時に抱えているケースはなく、それぞれ独立した16件でした。
| チェック項目 | 該当件数 | 除外方針 |
|---|---|---|
| 欠損値 | 3件 | 学習集中力を使う分析でのみ除外 |
| ストレートライニング | 3件 | 除外 |
| 年齢の異常値 | 2件 | 除外 |
| SNS利用の矛盾回答 | 3件 | 除外 |
| 動画視聴時間の外れ値 | 2件 | 要検討（保留） |
| 回答時間の極端な短さ | 3件 | 除外 |
このように整理すると、「確実に除外すべき11件」と「判断に迷う2件」を切り分けることができました。全体の27%（16件／60件）が何らかのチェックに引っかかったことになりますが、これは架空データにあえて多めに問題を仕込んだ結果であり、実際の調査ではここまでの割合にはならないことが多いです。とはいえ、事前にこうした基準を用意しておかないと、こうした問題のあるデータに気づかないまま分析を進めてしまうリスクがあることが、この作業からも実感できると思います。
10. おまけ：逆転項目の反転と合成得点の作り方
除外の判断とは別に、残った回答者については、Q3（逆転項目）を反転させ、学習集中力の合成得点を作る処理が必要です。
Q3は「授業中、他のことに気を取られやすい」という項目で、Q1・Q2・Q4（「集中できている」寄りの項目）とは、問いかけの向きが逆になっています。そのままでは平均に混ぜると意味が逆流してしまうため、反転処理が必要です。
S列：Q3の反転
=IF(H2="","",6-H2)5段階評価の場合、「6−元の値」で反転できます（1→5、2→4、3→3、4→2、5→1）。この「6」は、5段階評価の最小値（1）と最大値（5）を足した数です。もし7段階評価であれば「8−元の値」になります。
T列：学習集中力の合成得点
=IF(COUNTBLANK(F2:I2)>0,"",AVERAGE(F2,G2,S2,I2))Q1・Q2・反転後のQ3（S列）・Q4の4項目の平均を計算しています。ここで、H2（元のQ3）ではなくS2（反転後のQ3）を参照している点が重要です。ここを間違えると、せっかく反転させた意味がなくなってしまいます。
計算例
id=1の回答者の場合、Q1=1、Q2=5、Q3（元の回答）=1、Q4=5でした。Q3をそのまま平均に入れると、本来「気が散らない＝集中力が高い」ことを意味する回答なのに、数値としては最も低い「1」のまま計算されてしまいます。反転させると6−1＝5となり、他の項目と同じ「高いほど集中力が高い」という意味に揃い、正しく合成得点（この場合は4.0）を計算できました。
11. まとめ
今回は、記事で紹介した知識をもとに、実際の架空データ60件を使って、欠損値・ストレートライニング・年齢異常・SNS矛盾・外れ値・回答時間過短という6種類のチェックを一つずつ実践しました。
重要なのは、関数を組み立てること自体よりも、「要確認」となったデータをどう扱うか、その判断基準を自分の中で明確にし、卒論の本文（研究方法の章）に説明できる形で残しておくことです。今回のように、確実に除外すべきものと、判断が分かれるものを切り分けておくと、後から「なぜこのデータを除外したのか」を聞かれたときにも、自信を持って説明できます。
次は、この「除外・処理を終えたデータ」を使って、実際に記述統計や分析にかけていく段階に進んでいきます。
コメント