Excel のテーブルを利用したデータ管理およびグラフ作成 - 情報処理IIレジュメ(2021-3)

BYOD Mac Windows 授業資料

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 のセルに記述します
    f:id:hkob:20200516120724p:plain
    ラベル設定
  • A2に0.0を記述します
    f:id:hkob:20200516120939p:plain
    A2セルの設定
  • A3に「=」をタイプします
  • A2のセルをクリックします
  • 「+0.01」をタイプします
    f:id:hkob:20200516121053p:plain
    A3セルの設定
  • A3の右下の点にカーソルを合わせます
    f:id:hkob:20200516121203p:plain
    A3セルの右下の点
  • 点をドラッグしてA12まで引っ張ります
    f:id:hkob:20200516121237p:plain
    A12までドラッグ
  • 成功すると最後の図のようになりました
    f:id:hkob:20200516124226p:plain
    最終形

2.2 電圧列の設定

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

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

2.3 グラフの描画

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

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

2.4 近似曲線の追加

以下の手順で近似曲線を追加します

  • グラフをクリックします
  • グラフ要素の追加・近似曲線・その他の近似曲線オプションを選択します
    f:id:hkob:20200516125949p:plain
    近似曲線の追加
  • cos は偶関数なので偶数次の多項式近似をするとよいです。 こんな感じになりました。 サンプル点が荒いときには近似曲線は有効です。
    f:id:hkob:20200516130027p:plain
    追加された近似曲線

2.5 見た目の修正

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

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

やり方がわからない人は脚注1のヒントを参照してください

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行目まで全体を選択します(左端の数字の部分を選択)
    f:id:hkob:20200516132430p:plain
    1〜3行を選択
  • 右ボタンで挿入を選択します
  • 挿入した3行に必要事項(dt, f, A)を入力します
  • 可変な部分のセルに色を塗っておきます(どこが変更できるかが後からでもわかるようにするためです)
    f:id:hkob:20200516132800p:plain
    必要事項の記載
  • B1を選択し、名前欄に「_dt」と書きます
    f:id:hkob:20200516133235p:plain
    名前 _dt の設定
  • B2を選択し、名前欄に「_f」と書きます
    f:id:hkob:20200516133305p:plain
    名前 _f の設定
  • B3を選択し、名前欄に「_A」と書きます
    f:id:hkob:20200516133332p:plain
    名前 _A の設定

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

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

  • マジックナンバーの部分を図のように名前で設定したセルの内容に変更します
    f:id:hkob:20200516133607p:plain
    0.1 の部分を選択し、_dt をクリックして置き換え
    f:id:hkob:20200516133731p:plain
    振幅の_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が選択された状態になります)
    f:id:hkob:20200516135707p:plain
    A4からB15までを選択
  • 挿入リボンを開き、 テーブルをクリックします(左図)。 表示されるダイアログで 「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確認して「OK」を押します
    f:id:hkob:20200516140425p:plainf:id:hkob:20200516140456p:plain
    テーブルの挿入
  • せっかくなのでテーブルで式を計算するために式を全て消します
    f:id:hkob:20200516140957p:plain
    セルデータの削除
  • A5のセルに「=(ROW()-5)*_dt」とします。テーブルであるため、コピーすることなく，A列が全て設定されました。ここで「_dt」はタイプするのではなく、B1セルをクリックすると楽です。
    f:id:hkob:20200516141107p:plainf:id:hkob:20200516141139p:plain
    A列の設定
  • B5 のセルに「=_A*COS(2*PI()*_f*[@[時刻t’[s’]]])」とします。 ここで「_A」「_f」「[@[時刻t'[s']]]」の部分はタイプするのではなく、それぞれB3、B2、A5セルをクリックすると楽です。
    f:id:hkob:20200516141442p:plainf:id:hkob:20200516141512p:plain
    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行目)を選択し、削除します
    f:id:hkob:20210417112703p:plainf:id:hkob:20210417112753p:plain
    15行目の削除
  • C4 をクリックして，「v^2」と記述します → テーブルが自動拡張されました
    f:id:hkob:20210417112932p:plain
    v^2列の追加
  • C5 において「=」をタイプ後に B5 をクリック，「*」 とタイプ，再度 B5をクリックすると以下のようになります=[@[電圧v’[V']]]*[@[電圧v'[V']]]
    f:id:hkob:20210417113125p:plain
    v^2の計算
  • D4をクリックして，「v^2dt」と記述します → テーブルがさらに自動拡張されました
    f:id:hkob:20210417113958p:plain
    v^2dt列の追加
  • D5 において「=」をタイプ後に C5 をクリック、「*」をタイプ、「_dt」(B1)をクリックすると以下のようになります=[@[v^2]]*_dt
    f:id:hkob:20210417114207p:plain
    v^2dtの計算
  • テーブルリポンを選び，集計行のチェックを追加します
    f:id:hkob:20200516150411p:plain
    テーブルリポンで集計行のチェック
  • B15 のプルダウンで「合計」を選択して「0」になることを確認します
  • D15 のプルダウンで「合計」にして「200」になることを確認．これが式のΣまでの値になります。
\displaystyle\sum_{i=0}^{n-1} v^2(i\Delta_t)\cdot\Delta_t

f:id:hkob:20210417114529p:plain
v の合計とv^2dt の合計

  • D16 で実効値を計算します (ここは考えてください)
  • D17 で実効値の理論値を計算します (_A の値だけを使って計算してください)
  • D18 で誤差率を計算します
\displaystyle{ 誤差率=100\times \frac{|計算値-理論値|}{理論値} }
  • E列に|v|，F列に|v|dt を用意して，E15 に合計を計算します
  • F16 に平均値を計算します (ここは考えてください)
  • F17 で平均値の理論値を計算します (_A の値だけを使って計算してください)
  • F18 で誤差率を計算します
    f:id:hkob:20200516151931p:plain
    最終形

6.3 提出する課題

10点の計算では平均値の誤差が大きいです。 テーブルを修正し，100点で計算してください。 その際、以下の点に気をつけてください。

  • 実効値と平均値の理論値も同時に計算します
  • 計算値と理論値の誤差率を比較

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

付録

f:id:hkob:20210417120523p:plainf:id:hkob:20210417120613p:plain
10点と100点の近似の違い (実効値)

f:id:hkob:20210417120824p:plainf:id:hkob:20210417120853p:plain
10点と100点の近似の違い (平均値)

hkob.hatenablog.com

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