計測工学I 【第2回】Excelによる移動平均の計算

今日の内容

•  【第2回】Excelによる移動平均の計算 • 実際にパソコンを使って移動平均の計算を行い、ディジタルフィルタ、FIRフィルタの考え方を導入する。計測データの処理方法に慣れる。

• この方法は単に移動平均に用いるだけではなく、 一般的なフィルタ処理にも用いることができます。

• ディジタルフィルタ処理の入門として、移動平均を扱います。

Excelの構造

• Excelは表計算ソフトで、データを行と列に入力することができます。

• C言語などのコンピュータプログラムで、 従来「配列」で処理していた「データ」を セルに直接書き込み 関数を使ってプログラムを 書くことができます。

for( i=0; i<n-1; i++ ) { ecg_new[i] = (ecg[i] + ecg[ii+1]) / 2; }

移動平均とは

• 自分自身の前後数点のデータを、平均し、その点のデータとする処理です。

• 式の読み方 •  i番目の要素を計算する時 •  kの値を i-nからi+nまで変えて •  加算する。 •  その結果を2n+1で割って

平均する。

xi = 12n+1

xkk=i−n

i+n

∑

生データ

• 測定したままで、何も処理していない データを「生データ」と（俗に）呼びます。 •  英語では、Raw Data

• 今回は、manabaから ECG-sample.xlsxを ダウンロードして開いて下さい。

提出用データ • データは150秒あります。各自の学籍番号の下3桁を秒に読み替えて、そこを起点に5秒分のデータを切り出して下さい。 •  例：学籍番号110XXX100番の人

•  100秒から105秒までのデータを切り出して、 新たにSheet1にA1のセルからコピーして下さい。

• 下の[+]のタブをクリックし、「新しいシート」を挿入する。 •  5秒分のデータを選択し、「コピー」する。 • 新しく作ったシートの[A1]のセルに貼付ける。 • 全員、1001行のデータに なっているはずです。

Excelでの範囲選択

•  1000行分を、1行ずつ下向き矢印で移動すると時間がかかります。 •  範囲選択は、[Shift]キーを押しながらカーソル移動のキーを使います。

•  ここでは、「ページ単位」でスクロールします。 •  パソコンによって[Page Up] [Page Down]だったり、[Pg Up] [Pg Dn]と

書かれているキーが「ページアップ」「ページダウン」のキーです。 •  ノートPCの場合、[Fn]キーなどと組み合わせる場合があります。 •  MacBookの場合は、[Command]+カーソルの[　↑　][　↓　]です。

•  つまり、PCによっては、[Shift]キーを押しながら、[Fn]キーと[Pg Dn]を押すというふうに、三つのキーを押すと、ページ単位で移動しながら範囲選択ができます。

•  また、さらに範囲が広い場合には、[Shift]キーを押しながら起点のセルをクリックした後、[Shift]キーを押したまま、スクロールバーで範囲を選び、[Shift]を押し続けたまま終点のセルをクリックするのが、一番オススメの方法です。

商用交流雑音

• 元のデータは0.005秒間隔です。　→　周波数200Hz • この上に、50Hzの商用周波数が乗っています。 • 何点で移動平均したらいいでしょうか？

移動平均点数の計算

•  50Hzのデータは、0.02秒で1周期分の変化をします。

• サンプリング間隔は0.005秒です。交流雑音は 0.02 ÷ 0.005 = 4で、4区間分に1周期が乗ってきます。

•  これを、「前2点」「後2点」に分けて平均します。 • 区間は4つですが中心にある自分自身も数えるので+1します。 •  n=2で以下の式を読みます。

•  kは、自分（ i ）の2点前から、 自分の2点後まで変化させ、

•  1/5倍（5で割って）平均します。

xi = 12n+1

xkk=i−n

i+n

∑

Excelでの計算（Step 1）

• A1からA1001まで、生データがコピーされているはずです。 • ここで、B3のセルに

=average(A1:A5) • と入力します。

Excelでの計算（Step 2：操作の考え方）

• 入力されたB3のセルの式を、B999までコピーします。

• こういう時に便利なのは、Excelのジャンプ機能です。 •  [Ctrl]キーを押しながら上下左右のキーを押すと、

連続したデータの切れ目まで一気にジャンプします。 •  データのある状態が続いているときは、 後のデータ

まで、データのない状態が続いていると、 初のデータが現れるまで、ジャンプします。

•  でも、B列にはデータがありません。ですので、一旦A列に移動し、A列の一番下(A1001)までジャンプしてから、一つ右のB列に移動し、今度は、コピー先のB999からB4まで「範囲選択」します。

Excelでの計算（Step 2）

•  まず、B3のセルをコピーします。（Ctrl-C） •  B3のセルの周囲が点滅します。

•  一つ左のA列（A3）にカーソルを移動させ、 [Ctrl]を押しながら下向き矢印を押します。 •  A1001までジャンプします。

•  一つ右、二つ上のB999に移動します。 カーソルキーで移動します。

•  B999で、[Shift]と[Ctrl]を押しながら、 上向き矢印↑を押します。 •  一気に、B999からB3までが選択されます。

•  [Shift]キーだけ押したまま、一つ下に戻り コピー先の範囲（B4:B999）を選びます。

•  範囲を選んだら、「貼付け」(Ctrl-V）します。

Ctrl ＋　↓

Ctrl ＋Shift +　↑

Shift +　↓

Excelの式の相対参照

• Excelの関数式は、$マークをつけなければ「相対参照」になります。

• このため、 •  =AVERAGE(A1:A5)

• が1行下にコピーされると •  =AVERAGE(A2:A7)

• になり、 後のB999のセルでは •  =AVERAGE(A997:A1001)

• になります。

• これで、計算式の入力はOkです。

グラフ表示

• A3:B999まで、計算済みの範囲を選んで下さい。 •  A3をクリックし、 •  [Shift]を押しながら、右向き矢印（→）を1回押してB3に移動し、 •  さらに[Ctrl]も押しながら、下向き矢印（↓）を押すと、一気にA3:B1001を

選ぶことができます。 •  [Shift]だけを押した状態で、上向き矢印（↑）を2回押すと、範囲を

A3:B999に狭めることが可能になります。

• これを、折れ線グラフで表示して下さい。

ディジタルフィルタ

• 移動平均の場合には、右の式の 計算を行いました。

• これ以外にも、同様の手法で、 右のような式を与えることができます。

• これがディジタルフィルタです。

• 移動平均の場合には、係数行列akが1/(2n+1)の定数であると 考えることが出来ます。

xi = 12n+1

xkk=i−n

i+n

∑

yi = akxi+kk=−n

n

∑

微分フィルタ

• 単純に、　a0 = -1, a1 = 1　と置くと、微分フィルタになります。

• 連続量の微分の定義は以下のようになります。 • 微小時間でのx(t)の増分を 求めれば良いので、直前の 値との差を求めれば、 微分の形になります。

•  （厳密には、サンプリング周期 で割ります。）

ddtx(t) = lim

Δt→0

x(t)− x(t −Δt)Δt

yi = xi − xi−1X(t)-x(t-Δt)

Δt

Excelでの微分フィルタ計算

• B列の「移動平均」の結果をそのまま使います。

• C4のセルに •  =B4-B3

• の式を入力し、この式をC999までコピーします。

ピークの強調

• 係数列として、{ -2, 1, 2, 1, -2 }というフィルタを設計します。 • 特性の計算は、「Z変換」で調べると わかりますが、この科目では扱いません。

• ですが、同様の方法で計算することが できます。 •  （下図は、わかりやすくするため、200のオフセットを加算しました。）

• 心電図のQRSのピークを強調できるフィルタです。

一般的なディジタルフィルタ

•  FIRフィルタで調べてみて下さい。 •  Finite Impulse Response Filter(有限インパルス応答フィルタ）です。

• 掛け合わせる係数列を様々に変えることによって、ノッチフィルタ、低域濾過フィルタ、高域濾過フィルタなど、様々な特性を持つフィルタを作れます。

• 相関フィルタとして、特定の波形パターンを抽出するのにも使えます。

• より厳密な処理を行うためには、サンプリング周波数を高くとって（オーバサンプリングして）フィルタの次数（計算する係数の数）を増やすことで、特性を 改善することができます。

画像引用元：Wikipedia

今日のまとめ

• EXCELを用いて、移動平均処理を行うことができる。ディジタルフィルタの作り方をEXCELで検証できる。

• Excelの操作法で、CtrlキーやShiftキーを組み合わせると、少ないキー操作・マウス操作で簡単に計算やグラフの作成を行うことができる。

• 同様の手法で、微分フィルタ、ノッチフィルタ、低域濾過フィルタ、高域濾過フィルタ、パターン抽出フィルタなど、簡単に実現することができる。

• フィルタは、FIRフィルタの考え方で、厳密に計算式を立てることができる。

今日の提出課題

• データを切り出したシートで、移動平均と、微分フィルタの計算処理を行ってグラフを作成し、 •  学籍番号-02.xlsx

• のファイル名で保存し、メール添付で提出して下さい。

• 提出先は、授業中にお示しします。 •  EXCELの拡張子（xlsx)は、自動的に付加されるので、重複していない

か、提出前によく確認して下さい。

• 配点6点：移動平均の正しいグラフがある。（4点） •  微分フィルタの計算結果のグラフがある。（2点）

•  ボーナス点：　QRS強調のフィルタのグラフが添付されている（2点）