1. Excel でのデータ管理

Excel は表計算ソフトウェアである。様々な帳票を作成するのによく利用される。電気電子工学コースでは、実験などのデータ処理でもよく活用される。式などをうまく利用するとシミュレーションなども可能となる。

ただし、Excel 自体はセル記述の自由度が高いため、通常の利用だとたくさんの設定作業が必要となる。この授業では、セルにある制約を設定することで作業量を大幅に低減できるテーブル機能を紹介する。また、Excel を使う上でのちょっとした Tips なども多く紹介したい。

2. 復習: これまでの使い方

まず、高専1年生くらいでやりそうな正弦波のグラフ作成 (0.01秒刻みで0.1秒までの10Hzの正弦波波形)をこれまでの知識で書いてみる。

A列: 時刻 t[s] を設定する。0.0 から 0.1 までのデータを入れる。
B列: 電圧 v[V] を設定する。今回は大きさは1としておく

2.1 ラベルと時刻の設定

以下の手順で実施する。

ラベルを A1, B1 のセルに記述
ラベル設定
A2に0.0を記述
A2セルの設定
A3に「=」をタイプ
A2のセルをクリック
「+0.01」をタイプ
A3セルの設定
A3の右下の点にカーソルを合わせる
A3セルの右下の点
点をドラッグしてA12まで引っ張る
A12までドラッグ
成功すると最後の図のようになる
最終形

2.2 電圧列の設定

次に B 列を設定する。 $v = \cos(2\pi ft)$ で計算すればよいので、A列の時刻を使って各行の値を導出する。

B2 に「=cos(2*PI()*10*」まで入力する
A2をクリック
「)」を入力して確定
B2セルの設定
B2のセルを選択
B2のセルの右下の点をダブルクリック
B2セルの右下の点
Aの列と同じ行数だけ自動的に展開される
B3からB12が自動設定

2.3 グラフの描画

工学で主に作成するのは「散布図」である。間違っても「折れ線」などで書かないこと(折れ線だと横軸の幅が均等になってしまう)。

グラフを描画したいデータエリアを範囲選択する必要がある
- A1をクリック
- シフトキーを押したまま、右にカーソルを移動
- シフトキーは押したまま、ctrlキー(macOS の場合は Command キーでもよい)を押す
- その状態で下にカーソルを移動
挿入リボンを開き、散布図を選択する
散布図
グラフが作成される
作成されたグラフ

2.4 近似曲線の追加

以下の手順で近似曲線を追加する * グラフをクリック * グラフ要素の追加・近似曲線・その他の近似曲線オプションを選択

f:id:hkob:20200516125949p:plain — 近似曲線の追加

* cos は偶関数なので偶数次の多項式近似をするとよい。こんな感じになる。サンプル点が荒いときには近似曲線は有効である

f:id:hkob:20200516130027p:plain — 追加された近似曲線

2.5 見た目の修正

軸ラベルなどが設定されていないので、各自で試してみよう

軸ラベルの追加
x軸の数字の位置を下に(設定で変更可能)
タイトルを下に

やり方がわからない人は脚注¹のヒントを参照

f:id:hkob:20200516130229p:plain — 見た目を修正したグラフ

3. これまでのやり方のダメなところ

3.1 問題1 マジックナンバー問題

問題1

0.01[s]というマジックナンバーがA列全体に存在
10[Hz]というマジックナンバーがB列全体に存在
1[V]というマジックナンバーがB列全体に存在

解決1

Excel の名前機能を利用する

3.2 誤差が累積する

問題2

0.01 という数値を累積加算している

解決2

行番号を利用する

4. 問題の解決

4.1 名前の設定

以下の手順で名前を設定し、それを利用する

1から3行目まで全体を選択(左端の数字の部分を選択)
1〜3行を選択
右ボタンで挿入を選択
挿入した3行に必要事項(dt, f, A)を入力
可変な部分のセルに色を塗っておく
必要事項の記載
B1を選択し、名前欄に「_dt」と書く
名前 dt の設定
B2を選択し、名前欄に「_f」と書く
名前 f の設定
B3を選択し、名前欄に「_A」と書く
名前 _A の設定

ここで、dt, f, A はそのまま列名になってしまうので，ぶつからないように _ を忘れないこと．

4.2 名前を使った数式の変更

マジックナンバーの部分を図のように名前で設定したセルの内容に変更する
0.1 の部分を選択し、dt をクリックして置き換え

振幅のA*を追加、10の部分を_fに置き換え

名前のおかげで式がかなり見やすくなる。前述の通り，名前はセル名と区別できるように「_」で始まる文字列にしている。日本語なら「_」は必要ない。

4.3 行番号による計算

Excel では ROW() という関数で行番号が得られる。これを使って以下のように時刻列を書き換える。

A5のセルを=(ROW()-5)*_dtという式に修正する。表データが5行目からスタートするので、5を引いた上で前述の「_dt」を掛け算する。この結果、誤差が累積することがなくなる。

f:id:hkob:20200516134103p:plain — 行番号を使った計算

* A5セルの右下をダブルクリックして下までコピーすると、A 列が全て行番号から計算されたことになる。

5. テーブルを利用する

5.1 なぜテーブルを使うのか

これまでのやり方
- 我々が利用する工学データは列はみんな同じものが多い
- 毎回コピーするのは面倒だ
  - → テーブルの活用
テーブルの場合
- 列は全て同じ式になる
- 式が見やすくなる

5.2 テーブルを使った書き方

以下の手順でテーブルで表を作り直してみる。

A4をクリック
シフトキーを押しながら，右カーソルを押す(A4とB4が選択)
そのままctrl (macOS の場合はコマンドキーでも可)も押しながら，下カーソルを押す(A4からB15が選択)
A4からB15までを選択
挿入リボンを開き、テーブルをクリックする(左図)。表示されるダイアログで「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して「OK」を押す
テーブルの挿入
せっかくなのでテーブルで式を計算するために式を全て消す
セルデータの削除
A5のセルに「=(ROW()-5)*_dt」とすると，A列が全て設定される。ここで「_dt」はタイプするのではなく、B1セルをクリックすること。
A列の設定
B5 のセルに「=_A*COS(2*PI()*_f*[@[時刻t’[s’]]])」とする。ここで「A」「f」「[@[時刻t'[s']]]」の部分はタイプするのではなく、それぞれB3、B2、A5セルをクリックすること。
B列の設定

テーブルでは列の式が自動的に全て同じになることを確認する．

5.3 テーブルによるグラフの作成

先ほど作成したグラフを消し，テーブルからグラフを作成
テーブルのどこかをクリックした後で，挿入から散布図を選択
近似曲線，軸の最大値・最小値以外を設定(後で値を修正するため)
_dt, _f, _A の変更でグラフが変わることを確認
家庭用交流50[Hz]，100[V]のグラフを1周期だけ表示するには?
- _dt = ?
- _f = ?
- _A = ?

6. 本日の課題

家庭用交流の実効値と平均値をデータから計算する。

6.1 計算する数式

家庭用交流の実効値 $V _ {rms}$ と平均値 $V _ {ave}$ は以下の式で計算できる。

$\displaystyle{ V_{rms}=\sqrt{\frac1T\int_{0}^T v^2(t)dt} }$

$\displaystyle{ V_{ave}=\frac1T\int_{0}^T |v(t)|dt }$

このような積分はコンピュータで計算できないので、以下のような方形近似を行う。

$\displaystyle{ V_{rms}=\sqrt{f\sum_{i=0}^{n-1} v^2(i\Delta_t)\cdot\Delta_t} }$

$\displaystyle{ V_{ave}=f\sum_{i=0}^{n-1} |v(i\Delta_t)|\cdot\Delta_t }$

6.2 手順

手順を以下に示す

現在の波形は1周期+1点計算してしまっているので、最後の行(15行目)を選択し、削除する
15行目の削除
C4 をクリックして，「v²」と記述 → テーブルが自動拡張
v²列の追加
C5 において「=」をタイプ後に B5 をクリック，「*」とタイプ，再度 B5をクリックすると以下のようになる =[@[電圧v’[V']]]*[@[電圧v'[V']]]
v²の計算
D4をクリックして，「v^2dt」と記述 → テーブルが自動拡張
v^2dt列の追加
D5 において「=」をタイプ後に C5 をクリック、「*」をタイプ、「_dt」(B1)をクリックすると以下のようになる=[@[v^2]]*_dt
v^2dtの計算
テーブルリポンを選び，集計行のチェックを追加
テーブルリポンで集計行のチェック
B15 のプルダウンで「合計」を選択して「0」になることを確認
D15 のプルダウンで「合計」にして「0.05」になることを確認．これが式のΣまでの値
D16 で実効値を計算 (ここは考えてください)
D17 で実効値の理論値を計算
D18 で誤差率を計算

$\displaystyle{ 誤差率=\frac{|計算値-理論値|}{理論値}\times 100 }$

E列に|v|，F列に|v|dt を用意して，平均値も計算
E17 で平均値の理論値を計算
E18 で誤差率を計算
最終形

6.3 提出する課題

10点の計算では平均値の誤差が大きい。テーブルを修正し，100点で計算してみる

実効値と平均値の理論値も同時に計算
計算値と理論値の誤差率を比較  
グラフは元の $v(t)$ のみの結果にすること(テーブルにすると余計なものが追加されてしまう)
100点の場合の実効値と平均値の誤差について考察
作成した Excel ファイルを添付し，考察をオンラインテキストで提出

hkob.hatenablog.com

見た目の修正のヒント: A. グラフ要素を追加: 軸ラベル: 第1横軸: 軸名を「時刻t[s]」。B. グラフ要素を追加: 軸ラベル: 第1縦軸: 軸名を「電圧v[V]」。C. タイトルを「正弦波電圧波形」とし，一番下へ。D. 横軸をクリックし，軸の書式設定: 軸のオプションで最大値を0.1に。E. 同じくラベル: ラベルの位置を「下端/左端」。F. 縦軸をクリックし，軸の書式設定: 軸のオプションで最大値を1.0，最小値を−1.0に↩