前回に引き続き、今回もデータを比較しやすい形に加工する方法を紹介していこう。分析するデータの内容によっては、そのままの形でデータを比較するのが難しかったり、数値以外の形でデータが取得される場合もある。このような場合も数式や関数を利用することで、データを比較しやすい形に加工することが可能である。

移動平均を利用してデータ推移を把握しやすくする

今回も、具体的な例を使って解説していこう。以下の図は、あるWebサイトのアクセス数をグラフ化したものとなる。このWebサイトは開設してからまだ間もないため、アクセス数が少なく、アクセス数の上下動が激しいことから「どのようにデータが推移しているのか？」を把握しづらい面もある。

  • アクセス数をまとめた表とグラフ

このように上下動が激しいデータを扱うときは、「移動平均」と呼ばれる手法を使うとデータの動向を把握しやすくなる。「移動平均」は株式などの金融分野でよく使われている手法で、「過去▲日間についての平均値」を求めたものとなる。

たとえば、先ほどの例で「過去10日間についての移動平均」を求める場合は、以下のようにExcelを操作すればよい。

まず、03/01～03/09の期間は、過去10日分のデータが揃っていないため「データなし」として扱う。続いて、3/10の移動平均は「03/01～03/10のデータを平均した値」、すなわち「=AVERAGE(C4:C13)」と関数を入力して計算する。

  • 3/10の移動平均を求める関数AVERAGE()

同様に、3/11の移動平均は「03/02～03/11のデータを平均した値」、3/12の移動平均は「03/03～03/12のデータを平均した値」という具合に、範囲を1日ずつずらしながらデータを平均していく。

  • 3/11の移動平均を求める関数AVERAGE()

この関数AVERAGE()を下方向へコピーすると、引数のセル参照（セル範囲）が1行ずつ自動修正されながらコピーされていくことになる。よって、この関数はオートフィルを使ってコピーすることが可能だ。

  • オートフィルを使った関数のコピー

このように移動平均を求めていくと、以下のようなデータを得ることができる。このデータをグラフ化することで、データの推移をわかりやすく示すことが可能となる。

  • 「アクセス数」と「移動平均」の推移を示したグラフ

このグラフを見ると、移動平均（赤線）は穏やかではあるが上昇傾向にあることがわかる。つまり、「アクセス数は少しずつ上昇している」と判断できる。

このように上下動が激しいデータは、移動平均を使うとデータの動向を把握しやすくなる。もちろん、平均する期間は10日間でなくても構わない。より長期にわたってデータ推移を確認したい場合は、過去15日間や過去30日間といった期間について平均値を求めていくと、より滑らかなグラフを得ることができる。日付ではなく、時間ごとに推移するデータの場合は「過去▲時間の平均値」などのように単位を変えて平均値を求めていけばよい。

アンケート結果を比較するには？

続いては、アンケート調査の結果をまとめた表を例に、データの加工方法を紹介していこう。以下の表は、「操作画面の使いやすさ」に関するアンケート結果を世代別にまとめた表となる。

  • アンケート結果をまとめた表

ただし、この表を漠然と眺めていても各世代の傾向は把握できない。このような場合は、適当な形でデータを点数化してあげるとよい。たとえば、「とても使いやすい」を5点、「使いやすい」を4点、「普通」を3点、・・・という具合にそれぞれの項目に点数を付けてあげると、比較しやすいデータになる。

まずは、以下の図のように表を作り直す。続いて、それぞれの項目のスコアを算出する。たとえば、「10～29才」の「とても使いやすい」の場合は、（5点）×（206人）を計算する数式を入力してスコアを算出する。

  • 各項目のスコアを求める数式

同様の手順で各項目のスコアを算出していき、その合計を関数SUM()で求めると、以下のような表ができあがる。

  • 「スコアの合計」を求めた表

ただし、このままでは「回答数の多い世代」のスコアが大きくなってしまうので、それぞれの「スコアの合計」を「回答数の合計」で割り算して「平均スコア」を求める。

  • 「平均スコア」を求める数式

  • 各世代の「平均スコア」を求めた表

このように処理するとアンケート結果を点数化することができ、世代別の比較を行いやすくなる。

今回の例では、若い世代（10～29才）は「使いやすい」と感じている人が多いが、中年世代（30～49才）は「少し使いづらい」と感じている人が多いようだ。なお、50才以上の世代も高評価になっているが、こちらは回答数が極端に少ないため、「たまたま高評価になっただけ」とも考えられる。むしろ「使いやすさ」を気にするより、高齢者の「回答数が少ない」＝「利用者が少ない」という点を心配すべきであろう。

結果を百分率（パーセント）で示すには

アンケート結果の場合は、各項目の割合をパーセントで示すのも効果的である。この場合は、（各項目の回答数）÷（合計の回答数）を求める数式を入力すればよい。

  • 割合を求める数式

続いて、表示形式に「パーセンテージ」を指定すると、以下のように結果を示すことができる。

  • 表示形式に「パーセンテージ」を指定

ただし、このままの状態では数式をコピーできないことに注意する必要がある。この数式をオートフィルでコピーすると、セル参照が自動修正されるため、以下のように数式がコピーされてしまう。

  • オートフィルでコピーした数式

この場合、空白セル（C11）で割り算を行うことになり、「#DIV/0!」のエラーが表示される。このようなミスを避けるには、「変化させないセル」を絶対参照で指定しておくと、数式を正しくコピーできるようになる。

絶対参照でセルを指定するときは、列番号や行番号の前に「$」の記号を付けてセル参照を入力すればよい。いちいち「$」の記号を入力するのが面倒な場合は、普通に「C10」などと入力してから「F4」キーを押して絶対参照に変換することも可能だ。

  • 分母を絶対参照で入力した数式

このように数式を入力しておくと、分子の部分だけセル参照が自動修正され、分母のセル参照は常に固定されるようになる。その結果、数式をオートフィルで正しくコピーすることが可能となる。

  • 各項目の「割合」を求めた表

割合を算出する際などに役立つテクニックなので、こちらも併せて覚えておくとよいだろう。

見た目にわかりやすくデータを表示するには？

そのほか、色を付けたり、数値データをグラフ化して、表を見やすくする方法もある。データの傾向を直感的に把握したい場合は、以下の図に示したような手法も便利に活用できると思われる。

  • 各項目をデータバーでグラフ化した表

ただし、こういった処理を行うには「条件付き書式」の使い方をマスターしておく必要がある。そこで、次回からは「条件付き書式」を使いこなす方法を紹介していこう。