エクセルを用いた簡単な技術計算と作図について

エクセルを 用 いた 簡 単 な 技 術 計 算 と 作 図 について 信 号 処 理 配 付 資 料 ( 岡 山 理 科 大 学 澤 見 英 男 2011 年 作 成 ) 表 計 算 ソフト エクセル を 用 いた 簡 単 な 技 術 計 算 と 作 図 について 紹 介 しま す 例 として 正 弦 波 の 標 本 化 と 周 波 数 特 性 の 計 算 を 取 り 上 げることにします (1) 正 弦 波 の 標 本 化 をグラフで 理 解 しよう 先 ず 表 計 算 ソフト エクセル を 立 ち 上 げます 以 下 の 様 な 表 示 が 現 れます この 中 のA 欄 を 横 座 標 軸 ( 工 学 単 位, 度 に 相 当 )に 割 り 当 てます そしてB 欄 には 正 弦 関 数 sin(x*π/180)を 計 算 し 求 めることにします 最 初 に, A1とB1のそれぞれにセルに 入 る 変 数 の 意 味 を 書 き 込 むのが 良 いでしょう 次 に,A2 欄 から 始 めて, 計 算 に 用 いる 角 度 の 値 を 入 力 します A2 欄 には0, A3 欄 には 計 算 式 =A2+10 を 入 力 してみましょう その 結 果 A3 欄 は10に なるはずです 720 度 まで 計 算 できるようにしておきましょう 1

続 くA4 欄 以 降 には, 先 に 入 力 した 計 算 式 をコピーします その 結 果, 例 えば, A4 欄 も 計 算 式 =A3+10 となり 望 む 結 果 が 得 られます この 様 な 目 的 のコピ ーには,マウスでセルの 右 下 隅 を 指 示 しカーソルが 矢 印 無 し 十 字 十 になっ てから 下 の 欄 に 向 かってドラッグ ペーストするのが 良 いでしょう 円 周 率 π=3.14159265358979 ( 理 学 単 位 )は 180 度 ( 工 学 単 位 )に 対 応 し ます これより,セル A2 を 用 いて 計 算 するようセル B2 に 計 算 式 を 入 力 します ( 例 では10 度 刻 み) 式 中 にある 行 と 列 の 位 置 は($を 用 いた 絶 対 表 現 ではなく) 相 対 表 現 にしておきます この 相 対 位 置 指 定 によって, 各 角 度 に 対 応 した 関 数 値 をセル 毎 に 得 ることができます ところで, 計 算 式 中 にある 円 周 率 πを 関 数 (pi())により 与 えることもできます また, 円 周 率 とか 何 度 刻 みにするかを 特 定 セルに 記 憶 し,これを 絶 対 位 置 指 定 で 参 照 して 計 算 することもできます 前 と 同 様 にして,マウスでセルB2の 右 下 隅 を 指 示 し,カーソルが 矢 印 無 し 十 字 十 になってから 下 の 欄 に 向 かってドラッグ ペースト 操 作 をし, 指 定 し た 範 囲 (0~720 度 )について, 全 てのセルにて SIN の 値 が 計 算 されること になります さあ!あと 一 息 でグラフの 完 成 です,もう 一 頑 張 りしましょう 角 度 を 横 軸 に, 値 sin(x)を 縦 軸 にして 作 図 します 次 に 示 したように, 作 図 範 囲 をマウスを 用 いたドラッグ 操 作 により 指 定 します そして,メニューバーから 挿 入 タブを 選 びリボン 表 示 内 にあるグラフから 散 布 図 を 選 び 出 します 2

グラフの 形 式 は,データポイントを 折 れ 線 でつないだ 散 布 図 にします OKボタンを 押 すと 次 のようにグラフ 表 示 が 現 れます グラフのタイトルには, ここではB2セルに 記 入 した 内 容 sin(x) が 使 われています グラフの 体 裁 を 少 しだけ 整 えることにしましょう グラフ タイトル(sin(x)) はそのままで, 凡 例 ( )の 表 示 位 置 を 変 えてみることにします 3

そのためには, 凡 例 の 表 示 されている 部 分 をマウス ポインタで 指 し,ポッ プアップメニューから 凡 例 の 書 式 設 定 (E)を 選 びます では, 表 示 位 置 下 を 選 んでみましょう 表 示 場 所 が 下 に 移 りましたか もう 少 し 作 業 を 続 けて, 座 標 軸 の 意 味 を 分 かり 易 くし,より 見 易 いグラフに 4

してみましょう そのためには,グラフツールからデザイン タブをクリック し,グラフのスタイル スタイル2 を 選 んでみます. 次 はレイアウト タブ から 軸 ラベル 主 横 軸 ラベル(H)> 軸 ラベルを 軸 の 下 に 配 置 を 選 びます 同 様 にして 縦 軸 についても 軸 ラベルを 記 入 できるようにします グラフ 中 に 表 示 されている, 軸 ラベル 部 をクリックして 縦 軸 ラベル 値 と 横 軸 ラベル 角 度 (degree) を 記 入 します 縦 軸 ラベルは 記 入 後 クリックしてラベ ルの 書 式 設 定 メニュー 配 置 から 配 置 左 に 90 度 回 転 にしておきます 5

ここまでの 作 業 で, 次 のような 結 果 が 得 られたはずです どうでしょう? 座 標 軸 (x 軸 )を 選 び,クリックして 軸 の 書 式 設 定 を 呼 び 出 します 軸 のオプ ションを 選 び, 描 画 範 囲 に 合 わせて 最 大 値 などを 設 定 します セルの 計 算 範 囲 (0 度 から720 度 まで 計 算 している)に 合 わせて 以 下 のように 設 定 します 目 盛 間 隔 を 指 定 して 目 盛 を 入 れることも 出 来 ます 縦 軸 についても 同 様 にして 表 示 範 囲 (-1から1まで)を 指 定 しておきます 6

以 上 の 様 な 操 作 により, 次 の 結 果 が 得 られます 上 手 くできましたか? この 結 果 は,1 周 期 (360 度 ) 分 の 値 を10 度 刻 みで 計 算 ( 標 本 化 )して いるので, 周 波 数 fの 入 力 信 号 を, 周 波 数 を36fそして 位 相 差 をゼロにして 標 本 化 した 場 合 の 再 生 信 号 に 該 当 します このような 作 図 を 通 して,シャノン による 標 本 化 定 理 が 単 なる 必 要 条 件 にしか 過 ぎないことを 確 認 できます (2) 周 波 数 特 性 の 計 算 アナログ 回 路 によるローパスフィルタの 特 性 関 数 H(f)=1/(1+j0.01*2πf ) の 周 波 数 特 性 ( 対 数 軸 )を 例 として, 計 算 作 図 してみましょう 新 しいワーク シートを 開 き, 最 初 に 表 題 を 入 力 し, 次 にセルA2に 周 波 数 1(Hz)を 設 定 し, セルA3 以 降 は 計 算 式 により 必 要 な 周 波 数 範 囲 を 設 定 します 周 波 枢 軸 を 対 数 表 示 することから, 周 波 数 を 決 める 計 算 式 は 掛 け 算 になります それでは, 周 波 数 範 囲 が100kHz 以 上 になるよう,この 計 算 式 を 入 力 したセルA3を 下 向 きにドラッグ ドロップして 同 様 の 計 算 処 理 を 適 用 しましょう この 場 合, 最 後 尾 セルの 値 が10 万 (Hz) 以 上 になるのは,A54セル 辺 りになるもの と 思 います 次 に, 節 目 となる 周 波 数 は10の 指 数 乗 にしておきましょう 少 し 大 きめの 値 のセルを 指 定 して, 周 波 数 の 値 を 直 接 入 力 します ここでの 例 で は,セルA15 が10の1 乗 に 近 い 値 (10.69932) になっているので,このセル だけ 手 動 入 力 して 値 を10にします 以 下 同 様 に100,1000 近 辺 の 節 目 となる 周 波 数 値 が10の 指 数 乗 に 揃 うよう 値 を 入 力 しておきます 7

特 性 関 数 の 利 得 G= H(f) は 1/sqrt(1+(0.01*2πf)^2)となります 一 方, 位 相 角 θは -atan(0.01*2πf ) ラジアンとなります ところで, 利 得 に 関 して は G の 常 用 対 数 を 20 倍 にしたデシベル 表 記 (db)を, 角 度 に 関 しては 工 学 単 位 ( 度 )を 用 いることにします それぞれの 計 算 結 果 を 次 に 示 しておきます 式 は 20*LOG10( 1/SQRT( 1 + (0.01*2*3.1415926535*A2)^2) ) でもOKです 式 は -ATAN(0.01*2*3.1415926535*A2)*(180/3.1415926535) でもOKです 8

作 図 範 囲 をマウスを 用 いたドラッグ 操 作 により 指 定 します そして,メニュ ーバーから 挿 入 タブを 選 びリボン 表 示 内 にあるグラフから 散 布 図 を 選 び 出 し, 作 図 すると 次 のような 結 果 が 得 られます ただし 凡 例 の 書 式 設 定 をして, 表 示 場 所 をグラフの 下 側 に 設 定 し 直 してあります 周 波 数 ( 横 軸 )を 対 数 表 記 にします 座 標 軸 (x 軸 )を 選 び,クリックして 軸 の 書 式 設 定 を 呼 び 出 し, 対 数 目 盛 を 表 示 する(L)にし, 例 えば 最 小 値 を 10, 最 大 値 を 100000 などとすると 以 下 の 結 果 が 得 られます 利 得 (db)と 位 相 ( 度 )は 単 位 も 値 も 異 なるので, 該 当 の 先 頭 セルに 単 位 を 9

記 入 しておきます また,マーカ 表 示 をやめる 場 合 にはグラフをクリックし, 系 列 グラフの 種 類 の 変 更 を 呼 び 出 しましょう この 例 では, 位 相 角 度 の 大 きさ は 最 大 で-90 度 であることから, 軸 の 書 式 設 定 を 呼 び 出 して, 軸 のオプショ ン 最 小 値 を-90にしておきます 次 のような 表 示 が 得 られましたか? グラフの 体 裁 をもう 少 し 整 えてみます レイアウト 軸 ラベル から 主 横 軸 ラ ベル(H)を 辿 り 軸 ラベルを 軸 の 下 に 配 置 を 選 び 文 字 列 frequency(hz) を 入 力 するとか, 挿 入 テキストボックス により, 横 軸 の 下 に 座 標 軸 説 明 文 の 記 入 をします また, 横 軸 座 標 値 をクリックしメニュー 補 助 目 盛 り 線 の 追 加 (N) を 適 用 しておきます グラフの 寸 法 などもマウスを 操 作 して 調 整 し 見 た 目 をそ れらしく 仕 上 げてみましょう 10

例 では,タイトルを 英 文 にし, 特 性 曲 線 の 座 標 軸 目 盛 を 個 別 に 表 示 するなど の 変 更 を 加 えてあります 第 2 軸 を 用 いて 位 相 特 性 を 表 示 するには,グラフをク リックしデータ 系 列 の 書 式 設 定 系 列 のオプション から 第 2 軸 ( 上 / 右 側 )(S) を 選 んでおきます そしてグラフツールからレイアウト 軸 を 選 び, 第 2 横 軸 (S)の 項 目 対 数 目 盛 で 軸 を 表 示 を 選 びます 各 軸 ラベルの 向 きは 軸 部 分 を クリックし,ラベルの 書 式 設 定 配 置 から 好 みのものを 選 びます 余 分 に 表 示 された 目 盛 は 必 要 なければ 削 除 しましょう 上 手 くできましたか? 課 題 1:これで, 表 計 算 ソフト エクセル を 利 用 して 簡 単 な 計 算 と 作 図 が 出 来 るようになりました 試 しに, 簡 単 な2 次 アナログ ローパス フィルタ H(f)=1/(1-j2παf )^2 の 特 性 を 求 めてみることをお 勧 めします ところで 係 数 (α)の 値 は, 例 えば,0.001 等 とすれば 良 いでしょう また, 後 でも 触 れます が, 計 算 に 用 いるこれら 定 数 を 特 定 セルに 記 憶 しておき, 計 算 式 中 で 絶 対 位 置 指 定 により 使 うようにしておくと, 各 種 定 数 に 対 応 した 特 性 を, 特 定 セルの 値 を 操 作 することにより 簡 単 に 作 図 することができてとても 便 利 です ヒント: 関 数 ATANを 用 いると, 得 られる 値 は-π/2 からπ/2 までの 範 囲 に 限 られます 一 方 フィルタの 位 相 特 性 は 範 囲 -πからπで 求 めるのが 一 般 的 なので,この 場 合, 関 数 ACOSを 用 いるのが 良 いでしょう 先 の 計 算 例 は1 次 の 複 素 関 数 なので, 関 数 ATANで 計 算 しても 計 算 範 囲 が 問 題 になることは 在 りませんでした しかし, 課 題 の 関 数 は2 次 の 複 素 関 数 なので, 範 囲 が0か らπになる 関 数 ACOSを 用 いて 位 相 を 求 めるのが 良 いでしょう ところで 一 般 には, 位 置 を 絶 対 指 定 したセルを 用 いて 係 数 や 円 周 率 などを 表 し 計 算 するの が 便 利 でしょう 関 数 1/H(f) の 大 きさが 1+(2παf )^2 そして 実 部 は 1-(2πα f )^2 になることから, 係 数 αをセルe2にそして 円 周 率 πをセルf2に 記 入 し ておいた 場 合,ローパス フィルタ H(f)=1/(1-j2παf )^2 の 位 相 セルC2 における 計 算 式 ( 角 度 を 工 学 単 位 で 計 算 )は 以 下 のようになります ACOS( ( 1 - (2*$E$2*$F$2*A2)^2 ) / ( 1 + (2*$E$2*$F$2*A2)^2 ) ) * 180/$F$2 11

式 中 の 絶 対 位 置 表 現 で 指 定 したセル $E$2 は 係 数 α 0.001 を,そして セル $F$2 は 円 周 率 3.141592653589793238462643383279 を 表 します この 様 にして 得 られたグラフを 整 理 すると 以 下 のようになります ご 苦 労 様! ここまで 来 れば,エクセルによる 計 算 と 作 図 にすっかり 慣 れて きたのではないかと 思 います それでは 応 用 編 として,3 次 元 表 示 による 周 波 数 特 性 図 を 描 いて 見 ることにしましょう なおこの 図 はメニュー 3-D 回 転 を 呼 び 出 して, 見 る 方 向 を 変 えることが 出 来 ます (3) ラプラシアン フィルタの 周 波 数 特 性 画 像 処 理 に 用 いるものに 次 に 示 すようなラプラシアン フィルタがあります (1) (1)(-4)(1) (1) 長 方 形 領 域 で 定 義 された 画 像 にフィルタを 適 用 した 場 合 の 周 波 数 特 性 を 調 べ る 場 合, 領 域 の 端 における 取 り 扱 いが 重 要 になってきます ところで 離 散 フー リエ 解 析 では, 解 析 の 対 象 である 領 域 を 周 期 展 開 し 全 空 間 に 広 げたものとして 取 り 扱 います 一 方 この 解 析 領 域 を 偶 対 象 鏡 映 展 開 により 全 空 間 に 周 期 展 開 し, 解 析 することも 出 来 ます( 離 散 コサイン 変 換 ;DCT) 簡 単 のため,ここでは 正 方 領 域 上 (N N 点 )で 定 義 された 画 像 を 扱 うことにしましょう 例 として, 領 域 の 境 界 上 に 標 本 点 を 置 くDCTタイプIを 用 いて 周 波 数 特 性 を 導 出 してみ ます DCTタイプIでは, 変 換 基 底 として 次 のようなものを 用 います cos(kmπ/n)cos(lnπ/n) ただし 指 標 kとlはそれぞれxおよびy 座 標 方 向 の 周 波 数 を 表 し, 指 標 mとn 12

はそれぞれxおよびy 座 標 方 向 の 標 本 点 の 位 置 を 表 しています これより,ラ プラシアン フィルタの 周 波 数 特 性 ( 利 得 )を 簡 単 に 求 めることが 出 来 ます G(k, l) = 2( cos(kπ/n) + cos(lπ/n) ) - 4 次 に,N=18とした 場 合 の 周 波 数 特 性 G(k, l) を 表 示 してみます ここ では,A 列 目 ($A)を 指 標 kに2 行 目 ($2)を, 指 標 l(エル)に 対 応 さ せています ところで, 利 得 G(k, l) をセルの 相 対 表 現 と 絶 対 表 現 を 用 いて 計 算 すると 便 利 です 定 数 πとnは 絶 対 表 現 で$C$1セルと$F$1セルに 記 憶 しておきます これより, 例 えば,B3セル(k=l=0に 対 応 )に 関 し ては, 以 下 のように 書 き 表 すことにより 値 G(k, l) を 計 算 できます =ABS( 2*( COS($A3*$C$1/$F$1) + COS(B$2*$C$1/$F$1) )-4 ) 計 算 式 を 全 セルに 適 用 するには, 例 えば,まず 最 初 にB3セルを 横 方 向 にコ ピーしておき, 次 に3 行 目 のセル 全 体 を 縦 方 向 にコピーします こうすること により 全 てのセルで 計 算 が 行 われます なお, 最 初 にB3セルを 縦 方 向 にコピ ーしておき,こうして 得 られたB 列 を 横 方 向 にコピーするのも 良 いでしょう 計 算 の 終 わった 全 セル(この 例 では A2~T21)を 指 定 し, 以 下 のようにして, 挿 入 その 他 のグラフ, 等 高 線 から,ワィヤーフレーム3-D 等 高 線 を 選 び ます こうして 得 られた3 次 元 グラフの,グラフタイトルや 軸 ラベルの 表 示 内 容 は,グラフをクリックし,グラフツール レイアウト から 選 んで 行 います 13

次 は 得 られたグラフを 好 みに 合 わせて 表 示 してみましよう 表 示 されたグラフを 見 る 方 向 を 変 えるには, 回 転 角 度 などの 指 定 をする 必 要 があります そのためには(a)グラフツールからレイアウト 3-D 回 転 を 選 ぶか,(b) 表 示 されているグラフをクリックしてポップアップメニューを 呼 び 出 し, 項 目 3D 回 転 を 選 ぶかすると 良 いでしょう 14

現 れたメニュー 画 面 グラフエリアの 書 式 設 定 を 通 して 表 示 設 定 をします なおこのメニューをグラフとは 重 ならない 位 置 に 移 し,グラフを 見 ながら 数 値 を 変 えるようにすると, 操 作 内 容 の 直 感 的 な 理 解 が 易 しくなるでしょう 15

先 に 触 れたように 領 域 を 広 げ(2N 2N 点 ) 同 様 の 操 作 をすると, 離 散 フー リエ 解 析 に 対 応 する 結 果 を 得 ることができ, 以 下 のようになります 課 題 2: 以 上 見 てきた 作 図 機 能 を 用 いて, 以 下 の 平 均 値 フィルタの 周 波 数 特 性 G(k,l) を 求 め, 先 のラプラシアン フィルタと 較 べてみましょう (1/9)( 1/9)( 1/9) (1/9)( 1/9)( 1/9) (1/9)( 1/9)( 1/9) G(k,l) = ( 1+2cos(kπ/N))(1+2cos(lπ/N) ) /9 ところで,レポート 提 出 期 限 は 指 定 されたものを 守 ってください この 資 料 は 授 業 の 補 助 資 料 であり, 商 業 目 的 以 外 であれば 自 由 に 利 用 できま す 最 新 のものが http://cafe.mis.ous.ac.jp/sawami/ にて 公 開 されています 16