エクセルで大量のデータを等分割して統計処理したいとき
卒業論文，場合によっては修士論文などのデータ処理でも使えるエクセル・スキルをご紹介します．
大量のデータ群を等分割し，それぞれで平均値や合算値を算出して分析したい場合があります．
例えば以下のようなデータです．
想定されるのは，条件Aと条件B（被験者A，被験者Bなど）において，ある課題をやらせた際の時系列によるパフォーマンス・スコアや心拍数データなどを分析するといった場合です．
ところが，ここで困ってしまうのはサンプリングされたデータの数です．
条件Aと条件Bで比較したいんだけど，素直に上図のように並べてしまうと，まるで両者が異なる結果のように見えてしまう・・・．
つまり，そこで「困ってしまっている」こととは，「本当はこの両者に「違いが無い」ということを言いたいのに，グラフや表にすると「条件間でデータが異なる」と見えてしまうことです．
たしかに，例えば「課題をこなすために要した時間が異なる」ため，その時系列データは条件間で違ってきてしまう．でも，ここで気にしたいのは「課題達成時間」はもちろんですが，その課題を達成する中にあって「変動したデータのパターン」である，という人は少なくありません．
これをなんとかしようと，以下を試みる人がいるでしょう．
このように，例えばデータを５等分して，それぞれの平均値を算出しようというものです．
条件Aは上図のように，そして条件Bはこんな感じで↓
条件Bはデータ数が12個ですので，５等分することができません．
そこで，ちょっとずつズラしながら平均値を算出．
（最後は飛ぶことになりますが↓）
こうして平均値を算出し，グラフを作成してみると，こうなります↓
目論見どおり，条件間で当該データの変動パターンは同じであることが分かります．
今回例示しているデータは分かりやすくするため15個と12個のデータにしていますが，実際こうしたものを前にして困っている状況というのは，データが何百何千とある，そして，被験者や条件がたくさんあるという場合です．
しかも，とりあえず５等分してみたけど，やっぱり10等分にした方がいいとか，20等分の方が都合が良かった，なんてことで分析方法を変更しなければならないこともよくあります．
つまり，これをいちいち手作業していたら，冗談じゃなく本当に日が暮れてしまうという状況ですね．
それを，エクセル関数を使って自動化してしまおうというのが今回ご紹介するものです．
さっそく以下より手順を示しましょう．
条件Aの５等分平均値をF列２行目以降に算出しようとしています．
長いので，この図の下に関数と式を示しました．
=AVERAGE(OFFSET(B$2,COUNTA(B$2:B$16)/5*$E2,0,COUNTA(B$2:B$16)/5+1,1))
解説していきます．
AVERAGE関数の中身に「OFFSET関数」を使うことで，データの等分処理が容易になります．
OFFSET関数の後半部分である［高さ］の部分ですが，ここが参照範囲を選ぶところです．ここもCOUNTA関数でデータ個数を出し，それを「5」で割って５等分します．
一発で条件Aの５等分データが出来上がります．
一瞬にして条件Bの５等分データも出来上がるのです．
=AVERAGE(OFFSET(B$2,COUNTA(B$2:B$2000)/20*$G2,0,COUNTA(B$2:B$2000)/20+1,1))
さすがにこういうデータは手作業ではできませんが，この手法を用いれば，条件間でデータ数が異なる場合の処理も容易です．
最近の測定器やデータ処理ソフトでは，上述してきた相対的な加工・処理を施したものを出力してくれるものもありますので，このような作業をすることは少なくなっています．
しかし，どうしても自力でやらなければならない場合も当然あるわけで．
「面倒だからそういう処理をするのはやめよう」と諦めないためにも，知っていおいて損はないでしょう．
その後の統計処理については，こういう記事を参考にしてください．
関連記事
■卒論・ゼミ論で統計学的有意差が出ない時に読むブログ記事
■Excelで多重比較まとめ
■ExcelでTukey法による多重比較
■ノンパラメトリック検定で多重比較したいとき
■ノンパラメトリック版Tukey法による多重比較「Steel-Dwass法」
■エクセルExcelでの簡単統計（対応のあるt検定と多重比較）
ちょっとした統計処理上のエクセル小技はこちら
■エクセルで相関係数のｐ値を出す
■エクセル散布図で相関関係・相関係数を確認する便利な方法
■エクセルで大量のデータを処理したいとき
■エクセルだけで統計処理する卒論・ゼミ論用アンケート調査のオススメ方法 part1
■点数・得点を段階評価するためのエクセルシートの作成
その他，こういう怪しいブログ記事よりも，ちゃんと勉強になる書籍もご紹介しておきます．
詳しくは，
■独学で統計処理作業をスキルアップさせるための本
を御覧ください．
大量のデータ群を等分割し，それぞれで平均値や合算値を算出して分析したい場合があります．
例えば以下のようなデータです．
ところが，ここで困ってしまうのはサンプリングされたデータの数です．
条件Aと条件Bで比較したいんだけど，素直に上図のように並べてしまうと，まるで両者が異なる結果のように見えてしまう・・・．
つまり，そこで「困ってしまっている」こととは，「本当はこの両者に「違いが無い」ということを言いたいのに，グラフや表にすると「条件間でデータが異なる」と見えてしまうことです．
たしかに，例えば「課題をこなすために要した時間が異なる」ため，その時系列データは条件間で違ってきてしまう．でも，ここで気にしたいのは「課題達成時間」はもちろんですが，その課題を達成する中にあって「変動したデータのパターン」である，という人は少なくありません．
これをなんとかしようと，以下を試みる人がいるでしょう．
このように，例えばデータを５等分して，それぞれの平均値を算出しようというものです．
条件Aは上図のように，そして条件Bはこんな感じで↓
条件Bはデータ数が12個ですので，５等分することができません．
そこで，ちょっとずつズラしながら平均値を算出．
（最後は飛ぶことになりますが↓）
こうして平均値を算出し，グラフを作成してみると，こうなります↓
目論見どおり，条件間で当該データの変動パターンは同じであることが分かります．
今回例示しているデータは分かりやすくするため15個と12個のデータにしていますが，実際こうしたものを前にして困っている状況というのは，データが何百何千とある，そして，被験者や条件がたくさんあるという場合です．
しかも，とりあえず５等分してみたけど，やっぱり10等分にした方がいいとか，20等分の方が都合が良かった，なんてことで分析方法を変更しなければならないこともよくあります．
つまり，これをいちいち手作業していたら，冗談じゃなく本当に日が暮れてしまうという状況ですね．
それを，エクセル関数を使って自動化してしまおうというのが今回ご紹介するものです．
さっそく以下より手順を示しましょう．
条件Aの５等分平均値をF列２行目以降に算出しようとしています．
長いので，この図の下に関数と式を示しました．
=AVERAGE(OFFSET(B$2,COUNTA(B$2:B$16)/5*$E2,0,COUNTA(B$2:B$16)/5+1,1))
解説していきます．
AVERAGE関数の中身に「OFFSET関数」を使うことで，データの等分処理が容易になります．
OFFSET関数に関する説明は他のサイトに譲るとして，ここで何をしているのかというと，
B列２行目を基準として，B列のデータ数の５分の１個のデータ数×範囲数の行と列を参照しています．
等分するためのデータ数を数えなければいけませんので，COUNTA関数でデータ個数をカウントしています．それを「5」で割っているのです．
その後ろで，E列に用意した区間番号を参照して掛け算しています．
これで，基準セル（B列2行目）からデータの何等分離れたところを選択するのか指示しているのです．「0」のところは，基準値から0区間目，つまりB列2行目から参照開始しますよ，ということです．
OFFSET関数の後半部分である［高さ］の部分ですが，ここが参照範囲を選ぶところです．ここもCOUNTA関数でデータ個数を出し，それを「5」で割って５等分します．
そして，その後ろに「+1」を付けています．これは何かと言うと，データ個数が等分したい数字では割り切れない場合，OFFSET関数では余ったセルを自動的に省いてしまいますので，そうならないようにするためです．詳細はこの下で述べます．
これをオートフィルすれば↓
一発で条件Aの５等分データが出来上がります．
さらにこれを右へオートフィルすれば↓
一瞬にして条件Bの５等分データも出来上がるのです．
グラフにしてみればこうなります↓
え？ さっきとグラフや算出値が違うじゃないかって？
そりゃそうです．OFFSET関数の後半部分を思い出してください．
【 COUNTA(B$2:B$16)/5+1 】
【 COUNTA(B$2:B$16)/5+1 】
というように，平均値を参照する範囲を手作業と違い１つ増やしているからです．
だからセル1個分のデータの影響を受けることになります．
これについての解釈はデータの質にもよりますけど，このような15個や12個のデータでは影響が表出しやすいですが，何十何百というデータになれば誤差範囲になると考えられます．
（そもそも，例に示したようなデータ数なら手作業でやったほうが早い）
（そもそも，例に示したようなデータ数なら手作業でやったほうが早い）
それを言い出したら，「+1」を付けなくてもいいんじゃないか？ と考えられなくもないのですが，これは分析上の好みの問題かもしれません．
いずれにせよ，こちらが提示したいのは「抽出された範囲全体を100％として，それを等分するとこうなります」ということですから，その分析目的にかなっていればOKです．
つまり，生データであれば違いがあるように見えるが，相対的なデータに加工すると類似パターンが現れてきた，という統計処理と示し方ができるようになるのです（もちろん，その逆も然り）↓
この方法を用いれば，データ処理の変更も容易です．
５等分じゃなくて３等分にしたいと思えば↓
=AVERAGE(OFFSET(B$2,COUNTA(B$2:B$16)/3*$E2,0,COUNTA(B$2:B$16)/3+1,1))
このように，先ほどのセルで５等分を指示した部分を「3」にすればいいのです．
そうすれば，オートフィルを繰り返して↓
あっという間に３等分にしたデータ処理が出来上がります．
上述してきた例ではデータ数が少ないので無理がありますが，以下のように大量データと大量処理を前にすれば，この方法の真価が発揮されます．
4条件で最大1999個のデータを20等分したのがこちら↓
H列2行目のところに，こんなものを入れてオートフィルしています．
いずれにせよ，こちらが提示したいのは「抽出された範囲全体を100％として，それを等分するとこうなります」ということですから，その分析目的にかなっていればOKです．
つまり，生データであれば違いがあるように見えるが，相対的なデータに加工すると類似パターンが現れてきた，という統計処理と示し方ができるようになるのです（もちろん，その逆も然り）↓
この方法を用いれば，データ処理の変更も容易です．
５等分じゃなくて３等分にしたいと思えば↓
=AVERAGE(OFFSET(B$2,COUNTA(B$2:B$16)/3*$E2,0,COUNTA(B$2:B$16)/3+1,1))
このように，先ほどのセルで５等分を指示した部分を「3」にすればいいのです．
そうすれば，オートフィルを繰り返して↓
あっという間に３等分にしたデータ処理が出来上がります．
上述してきた例ではデータ数が少ないので無理がありますが，以下のように大量データと大量処理を前にすれば，この方法の真価が発揮されます．
4条件で最大1999個のデータを20等分したのがこちら↓
H列2行目のところに，こんなものを入れてオートフィルしています．
=AVERAGE(OFFSET(B$2,COUNTA(B$2:B$2000)/20*$G2,0,COUNTA(B$2:B$2000)/20+1,1))
さすがにこういうデータは手作業ではできませんが，この手法を用いれば，条件間でデータ数が異なる場合の処理も容易です．
最近の測定器やデータ処理ソフトでは，上述してきた相対的な加工・処理を施したものを出力してくれるものもありますので，このような作業をすることは少なくなっています．
しかし，どうしても自力でやらなければならない場合も当然あるわけで．
「面倒だからそういう処理をするのはやめよう」と諦めないためにも，知っていおいて損はないでしょう．
その後の統計処理については，こういう記事を参考にしてください．
関連記事
■卒論・ゼミ論で統計学的有意差が出ない時に読むブログ記事
■Excelで多重比較まとめ
■ExcelでTukey法による多重比較
■ノンパラメトリック検定で多重比較したいとき
■ノンパラメトリック版Tukey法による多重比較「Steel-Dwass法」
■エクセルExcelでの簡単統計（対応のあるt検定と多重比較）
ちょっとした統計処理上のエクセル小技はこちら
■エクセルで相関係数のｐ値を出す
■エクセル散布図で相関関係・相関係数を確認する便利な方法
■エクセルで大量のデータを処理したいとき
■エクセルだけで統計処理する卒論・ゼミ論用アンケート調査のオススメ方法 part1
■点数・得点を段階評価するためのエクセルシートの作成
その他，こういう怪しいブログ記事よりも，ちゃんと勉強になる書籍もご紹介しておきます．
詳しくは，
■独学で統計処理作業をスキルアップさせるための本
を御覧ください．
