時系列データ、連続データをグラフにする時に、単に折れ線グラフで表すよりも、棒グラフや円グラフが時間に沿ってグリグリと動くと便利だなーと思っていたんで、なるべく簡単にできるようにマクロを組んでみました。モーショングラフとか、モーフィンググラフとか、アニメーショングラフとか、ダイナミックグラフとか、なんて名前つけたらいいんだろう。
English page is here.
https://magtanate.blogspot.com/2021/09/Lang-English-3-steps-to-smooth-motion-chart.html
DIY360
達成したいこと（詳細）
時系列のデータって、よく出てきますよね。月次売上だったり、営業成績だったり、登録会員数の推移だったり、今後の見通しだったり・・・。ひょっとしたら天秤の秤量結果だったりするかもしれません。
こういったデータって、普通は折れ線グラフで表したり、棒グラフにしたりすると思います。
紙だったら、まあこれでいいんです。
ただ最近やっぱりプレゼンもオンラインでやることが多く、魅せる技みたいなのが必要で、単なる折れ線グラフや棒グラフだとイマイチ面白みがなかったり、躍動感に欠けたりして、それが最終的には説得力の不足につながってしまうこともあります。
説得力のないグラフなんて、使う意味ありません。せっかく使うなら興味を惹くものを使いたいですね。
https://magtanate.blogspot.com/2021/09/preference-mapping-by-sensominer.html
DIY360
Powerpointのアニメーション機能で似たようなのがあれば良いのですが、残念ながらありません。単に項目別にピローンって出てくるだけで、データに基づいた動作はしません。
Tableauというサービスを使うと割と楽にできるらしいんですが、、、有料です。ちょっとハードル高いですよね。投稿時点で調べた限りでは１０万円オーバー／人・年なんで、結構な額ですね。
またデータ解析に強いR言語やPythonを使えば、こういった動くグラフを比較的簡単に作ることはできますし、実際私も自分が使いやすいようにRのスクリプトを既に組んでいます。
ただRの場合、それっぽいパッケージ(ggplot2とかgganimationとか）を使うのですが、グラフがどうも論文っぽいっていうか、シンプルでいいんですけど、色の調整もやりにくかったりして、目指す形にするまでにかなりの労力を費やします。多分あれこれ悩みながらだと、１時間以上かかります。Pythonではやってないですが、同様と推測します。
一方でExcelだと、グラフをマウスを使って色々と気軽にいじれるんです。背景色とか、透明度とか、グラデーションとか、とにかくユーザーフレンドリーに作ってあるんです。Rでやればいいじゃんってずっと思ってて、実際そうやってたんですが、ユーザーフレンドリーなグラフ作成については、個人的にはExcelに勝るものは無いんじゃないかと。。
ついでにいうと、会社勤務の方はExcelを使用している方が多いと思いますが、Rについてはまだまだ、多分Excelの100分の１以下ではないかと思います。
というわけで、冒頭のGIFアニメのように、Excelでも簡単に時系列データを動くグラフで表現できるようにすることを目指しました。
やりたいことのステップ分け
このようなグラフを得るには、どうしたらいいか？ステップで分けて、一つづつ達成していくことにしました。
１．必要に応じたデータの補間
例えば以下のような半月毎のデータが１年分あるとします。
この場合、データとしては２３行ありまして、１行ごとにグラフを描いてパラパラ漫画の１コマにすることになりますので、２３枚のコマが出来上がります。仮に３コマ／秒の動画とすると、８秒弱の動画になります（２３÷３≒７．６７）。
そこで、本来は無いはずのデータを、補間という手法で補ってやることで、２３行のデータを、例えば１０倍の２３０行とか、１００倍の２３００行とかに水増しすることができます。この水増ししたデータを使って、より細かくグラフを出力することで、滑らかな動きと、十分な長さをもつ動画を作成することができます。
もちろん最初から数百、数千件の時系列なデータを持っているのであれば、補間する必要はないかもしれません。ですので、あくまでもこの工程は必要に応じて、となります。ちなみに私自身の業務においては、この工程が必須です。。
２．グラフやチャートの連続出力（パラパラ漫画のコマを自動で作成）
Excelで描いたグラフやチャートを、パラパラ漫画の１コマとして、連続して画像として出力するステップとなります。
例えば１ヶ月毎の売上の記録が１０年分あるとすると、１２×１０＝１２０枚のグラフを作ることになります。
これを手動で一個一個やっていくのはとても面倒です。右クリックして図として保存を選んで地道にやることもできますが、これを毎回毎回数百回以上もやってられませんので、マクロに処理してもらうことになります。
３．動画として出力（パラパラ漫画を繋げる）
ステップ２で出力したグラフの画像をつなげて動画にする工程となります。PowerPointでのプレゼンテーションで使用するということであれば、相性とか考えると今ならmp4で出力するのが無難ですね。
このステップ自体は、たとえば高機能動画編集ソフトのAviUtlなんかを使えば割と簡単にできるんですが、動画の長さ（秒）の指定とか、割とステップを踏まないといけなかったりして、実は自分自身も正確なやり方はよく分かってないです（調べればいいんだろうけど）。あとmp4出力用プラグインも入れないといけないですね。これも少しだけだけど、面倒。
ということで、今回はAviUtlなど動画編集ソフトを使うのではなく、ffmpegというコマンドラインツールをマクロで呼び出して使用することとします。
またこの時に、Excelのワークシートのセルに希望する動画の長さ（秒）を指定できるように、あるいは、希望する滑らかさ（コマ／秒）を指定できるようにしておきたいと思います。秒を指定した場合は滑らかさは自動で決定、滑らかさを指定した場合は動画の長さ（秒）が自動で計算されてる仕組みです。
ステップ１：必要に応じたデータの補間
今回はデータの補間のステップについてトライしてみたいと思います。
どの補間方法を用いるか？
補間というと、いくつか方法があるようです。
- 線形補間
- ラグランジュ補間
- スプライン補間
- 秋間補間
などなど。それぞれ一長一短があるのと、弱点を補った派生型もあったりして少々複雑な状況に見えましたが、まずは主流の手法を使って以下のデータを補間してみましょう。
線形補間
生データの間を直線で結んだ形での補間となります。
長所としては、最もシンプルな補間方法であり、以下に紹介するその他の補間方法で見られるような「最大値や最小値をオーバーするような」補間が起こらないので、変なツッコミも少ないと言えます。
短所としては、これを動くグラフとした時に、場合によっては少しカクついた動きに見える点でしょうか。
ラグランジュ補間
生データを通る多項式を算出して補間値を計算する方法（自分的単純解釈、間違ってるかも）のようです。
長所は・・・よくわかりません。歴史がある、数学オリンピックにも出題された、とか色々ググると出てくるんですが、実際に何に活用されてるのかよくわかりません。
短所は、このグラフのように、末端部の振幅が大きくなりがちなことでしょうか。計算方法を工夫することでこの現象を防ぐ派生版も可能らしいですが、それが汎用可能なのかどうか自分（素人）にはわからないです。
ということで、僕はラグランジュ補間については、実際に活用したことが無いです。
スプライン補間
またまた自分（素人）的解釈ですが、上記ラグランジュ補間が生データ全体を通るような多項式を探すのに対して、スプライン補間では生データの前後2点のみに着目して式を立て、補間する方法です。
長所は、ラグランジュのように無理に全体の整合性を取ろうとしないので、このグラフからも見て取れる通り、直感的に自然な補間になることです。画像処理（例えば縮小時）なんかにも使われていますね。
短所は、やはり生データをオーバーするような結果が出ることです。例えば上記グラフの横軸が３，４，５の時、実際にデータは一定なんですが、少々上下に揺れていて、これを自然と感じる場合と感じない場合はケース・バイ・ケースなのかなと。
また横軸７の直後に（７．２くらい？）、７をオーバーするような数値が出てしまっています。これも自然と見える場合と見えない場合がありそうです。
秋間補間
１９７０年、秋間氏によって考案された方法だそうです。地図の等高線などに活用されています。
長所は、スプライン補間で見られるようなオーバー気味の補間結果が出にくいことでしょうか。グラフを比較して頂ければと思いますが、横軸３，４，５では振れが生じていませんし、横軸７．２くらいでも、７の時の値をオーバーするような事態には（今回の場合は）なっていません。
短所はとしては、、もしグラフの動きの滑らかさを第一に考えるのならば、スプライン補間には劣る、といった点でしょうか。横軸２や６の前後をスプライン補間と比べて頂けるとわかるように、秋間補間はやや線形補間に近いシャープな形になっています。
自分（素人）としては、線形補間とスプライン補間の間くらいのもの、との理解です。
シンプルでツッコミの少ない線形補間を選択、Excelで実行してみよう
動くグラフにした際の滑らかさとかを考えるとスプライン補間や秋間補間も良いんですが、あまり脚色が過ぎると社内外でのプレゼンとしてどうなのか？と思います。そうすると線形補間が無難ですね。
ということで、線形補間をExcelで実行してみたいと思います、素人ながらに考えながらやってみたいと思います。
おそらく手順としては、
- X軸の補間を実行する。例えば2倍に補間するなら、X1, X2, X3 → X1, X1.5, X2, X2.5, X3 って感じ。
- (Xn,Yn)と(Xn+1,Yn+1)の間に直線を引き、傾きを計算する（１次関数みたいな）
- 計算した傾きをもとに、補間値（Y1.5, Y2.5）を計算する
って感じでしょう（適当）。単純な比例計算を繰り返すだけなんで、色んな方法が考えられそうです。まあなんとかなるだろう、ってことで、考えてみました。美しくないかもしれませんが、許して。。
Sub 線形補間()
'X(i) 生X
'Y(i) 生Y
'Xip(ii) 補間後X
'Yip(ii) 補間後Y
'n_intp 補間後データ数 'Yrow 生データ数
Dim X(2001), Y(2001) As Double
Dim Xip(10001), Yip(10001) As Double
Dim Yrow, n_intp, i, ii As Long
n_intp = 91
Yrow = 10
'生データ（X)の読み込み
For i = 0 To Yrow - 1
X(i) = ActiveSheet.Cells(i + 2, 6).Value
Next
'次にXipの計算
For ii = 0 To n_intp - 1
Xip(0) = X(0)
Xip(ii + 1) = Xip(ii) + (X(Yrow - 1) - X(0)) / (n_intp - 1)
'Xipをシートに出力する
ActiveSheet.Cells(ii + 2, 9) = Round(Xip(ii), 12)
Next ii
'生データ（Y)の読み込み
For i = 0 To Yrow - 1
Y(i) = ActiveSheet.Cells(i + 2, 7).Value
Next
'さらにYipの計算
Yip(0) = Y(0)
i = 0
For ii = 0 To n_intp - 1
If Round(Xip(ii), 10) >= Round(X(i + 1), 10) Then i = i + 1'Xipが次の区間に入ったらiを増やす
Yip(ii + 1) = Yip(ii) + (Y(i + 1) - Y(i)) / (X(i + 1) - X(i)) * (Xip(ii + 1) - Xip(ii))
ActiveSheet.Cells(ii + 2, 10) = Yip(ii)
Next ii
End sub
セルのF列にX値を、G列にY値をそれぞれ生データとして１０行入力しておき、上記スクリプトを実行すると、それぞれの補間データがI列とJ列に入力されるはずです。
※Excelのメイン画面で、表示 → マクロ名に適当にTESTなど → 作成→ Sub TEST()とEnd Subを消し、上記スクリプトで置き換え → 再びExcelのメイン画面で、表示 → マクロ → 線形補間 を選び実行 で実行できます
なお、上記の例では生データの行数は最大２０００、補間後データ数の最大値は１万に設定してあります。動くグラフとする場合、これ以上多くなることはまず無いだろうし、逆にこれ以上多いとさすがに処理が重くなるだろうな、ということから仮に設定しました。
一応Xが均等間隔でない場合も対応できるハズなんですが、ほとんどのケースではXは時間軸とかで一定間隔となるのかなと思います。
また、補間後データ数n_intp = 91となっていますが、これ以外の数字にする時は、「（生データの行数－１）の倍数＋１の数字」としてやると、キリが良く補間できます。今回で言えば（（１０ー１）＊好きな整数、仮に１０）＋１＝９１ですね。
ステップ２：グラフやチャートの連続出力（パラパラ漫画のコマを自動で作成）
上記で作成した線形補間データを使ってやってみたいと思います。以下の画像のように、J列にグラフ化したい補間データがある状況です。
ソースの連続移動
マクロのスクリプト
以下をマクロに登録します。
Sub グラフのソースの連続移動()
Dim Yrow As Long
Yrow = 92'補間データが何行目まであるか
For Y = 2 To Yrow
ActiveChart.SetSourceData Source:=Union(Range(Cells(1, 10), Cells(1, 10)), Range(Cells(Y, 10), Cells(Y, 10)))
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Cells(Y, 9)
.FullSeriesCollection(1).XValues = "=Sheet1!$J$1"
End With
DoEvents
Next Y
End Sub
グラフの下準備
初めにグラフを作成しておく必要があります。
↓まずJ1：J2を選択します
↓続いて挿入→棒グラフを選びます。
↓続いてグラフを右クリックして、データの選択を選びます。
↓左側の、凡例項目（系列）で編集を選びます
↓系列名が$J$1となっているのを、矢印ボタンを押して変更することにします
↓変更先は、＄I$2です。
↓最後に、グラフの縦軸を最小を０、最大を１０００に設定します。こうしておかないと、縦軸が固定されないためフラフラしたグラフになります。
↓下準備ができたグラフがこちらとなります。
動かし方
下準備が終わったグラフを選択した状態（アクティブにした状態）で、表示→マクロ→グラフのソースの連続移動を選び、実行します。動画を用意しました。
スクリプト説明
Dim Yrow As Long
Yrow = 92'補間データが何行目まであるか
→Yrowを補間後データ数＋１に合わせて設定しています。＋１というのは、１行目に列名が入ってるからです。なお、今回は補間後データ数は最大でも１万と仮定しているので、Long型でなくてInteger型でも良かったですかね。まあいいや。
For Y = 2 To Yrow
ActiveChart.SetSourceData Source:=Union(Range(Cells(1, 10), Cells(1, 10)), Range(Cells(Y, 10), Cells(Y, 10)))
→Yを２からYrow（今回は92）まで一つずつ増やしていきます。その際に、増えていくYに合わせて、選択中のグラフの元データのソース（参照範囲）を連続的に変えていきます。
Union関数を使っているのは、Yが増えると１列目との距離が離れてしまい、グラフのソースとしてはそのままでは不適切となるからです。
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = Cells(Y, 9)
.FullSeriesCollection(1).XValues = "=Sheet1!$J$1"
End With
DoEvents
→選択中のグラフについて、細かくオプションを設定しています。
.HasTitleでグラフタイトルを表示させ、そのタイトルにI列の項目を設定しています。
.FullSeriesCollection(1).XValues = "=Sheet1!$J$1"で、軸の項目にJ列の１行目を設定しています。今回１列しか棒グラフにしないためか、明示してやらないと、最初の１枚だけ横軸ラベルが"1"と表示され、２枚目以降に"Yip"と表示される不具合がありました。
最後、DoEventsで進捗がグラフに逐次現れるようにしてあります。
グラフを１枚１枚画像として保存する
つづいて、以下を上記マクロの適切な位置に追記し、実行します。なお実行するとCドライブ直下にMovingGraph_workingfolderというフォルダが生成されます。
Dim targetPath As String
targetPath = "C:\MovingGraph_workingfolder\"
If Dir(targetPath, vbDirectory) = "" Then
MkDir targetPath
End If
ActiveChart.Export (targetPath & "Transition_" & Format(Y - 2, "00000") & ".png")
スクリプト説明
Dim targetPath As String
targetPath = "C:\MovingGraph_workingfolder\"
→targetPathという文字列を定義して、そこに画像が保存されるフォルダのパスを設定してあります。追記する場所は、「Yrow = 92'補間データが何行目まであるか」の次の行でいいでしょう。
If Dir(targetPath, vbDirectory) = "" Then
MkDir targetPath
End If
→もしtargetaPathに指定したフォルダが無い場合は、新たに作成するようにします。追記する場所は、さっき追記した「targetPath = "C:\MovingGraph_workingfolder\"」の次の行でいいでしょう。
ActiveChart.Export (targetPath & "Transition_" & Format(Y - 2, "00000") & ".png")
→targetPathで指定したフォルダに、Transition_00000.pngという名前でグラフを画像として保存する、という指示となります。最大値は99999で、Y-2からから自動で連番で保存されます。Yは２から始まるようにしてあるので、最初の画像はTransition_00000.pngとなります。追記する場所は、上記「End With」の次の行でいいでしょう。
ステップ３：動画として出力（パラパラ漫画を繋げる）
既にパラパラ漫画のコマは出来上がっているので、このままAviUtlなど優秀なソフトを使えば直ちに動画化することが出来ます。
ただ、わざわざ別のソフトを起動してってのが手間に感じる方も多いと思います。それから、AviUtlを使ってmp4として出力するには、それ用のプラグインを入れる必要がありますし、またほとんどの方は滅多に使わないソフトでしょうから、忘れない場所にショートカットでも作っておかないといけないかもしれません。
ということで、このプロジェクトでは、動画化には超有名な動画編集用コマンドラインツールであるFFmpegをExcelマクロで呼び出して使いたいと思います。
FFmpegの導入
ダウンロード
FFmpegの公式に行き、DownLoadアイコンをクリックします
↓移動先で、最新の（一番上にある）ffmpeg-〇〇〇〇-win64-gpl-shared.zipを選び、ダウンロードします。
フォルダの配置
↓ダウンロードしたファイルを展開します。
PC環境によりケース・バイ・ケースかと思いますが、通常ですとフォルダの中に同じ名前のフォルダが入っています。
↓その中身の方のフォルダについて、名前が長いので ffmpeg に変更します。
↓その、中身の方のffmpegフォルダを切り取って、Cドライブ直下に移動します。
↓このような構成になっていれば大丈夫です。
パスの設定
最後、パスの設定ですが、WindowsPowerShellを使う割と簡単な方法もあるようですが、信頼性がイマイチみたいなことが書いてあったんで、正攻法でいきます。なるべく丁寧に説明します。
↓画面左下のWindowsボタンを右クリックして、システムを選びます。
↓画面右側にあるシステムの詳細設定をクリックします
↓環境変数をクリックします
↓Pathをクリックします。
↓新規ボタンをクリックします
↓C:\ffmpeg\binと入力します
↓間違ってないことを確認したら、OKを押します
↓再度、OKを押します
以上で終わりです！
※もし心配な方は、コマンドプロンプトというアプリを起動して、ffmpegと入力してEnterを押してみましょう。これでFFmpegに関する情報がバーっと表示されれば大丈夫です。
作成したスクリプトと説明
スクリプト
作成したのは以下となります。短いですね。
Sub mp4出力()
ChDir "C:\MovingGraph_workingfolder"
Dim command As String
command = "ffmpeg -r 9.1 -i Transition_%05d.png -vcodec libx264 -pix_fmt yuv420p -crf 16 -t 10 -progress log.txt movie_graph.mp4"
Dim WSH2 As Object
Set WSH2 = CreateObject("Wscript.Shell")
WSH2.Run ("%ComSpec% /c " & command)
Set WSH2 = Nothing
End Sub
なお、上記スクリプトを登録する際に、以下の設定をしておく必要があります（書き忘れてましたごめんなさい）
VBAの画面で、ツール→参照設定に入ります
そして、"Windows Script Host Object Model"にチェックを入れてOKを押します。
このファイルをマクロつきファイルとして保存しておけば、次回このファイルを使用するときにはチェックは入ったままになっています。
説明
FFmpegとExcelマクロの連携について本当にズバリな情報がなくて、素人のワタシにはキツかったです。日本語で調べたり英語で調べたりで、なんとか上記に辿り着いた次第ですが、あんまり理解はできていません。話半分に読んで頂ければと思います。
ChDir "C:\MovingGraph_workingfolder"
→ここは、単にカレントディレクトリをC:\MovingGraph_workingfolderに変えているだけです。
Dim command As String
command = "ffmpeg -r 9.1 -i Transition_%05d.png -vcodec libx264 -pix_fmt yuv420p -crf 16 -t 10 -progress log.txt movie_graph.mp4"
→commandという文字列を定義して、そこにコマンドプロンプトでffmpegを呼び出すための司令を入れてます。
- -r 9.1 → 9.1フレーム／秒で
- -i Transition_%05d.png → インプットはTransition_に５桁の数字の連番がついたpng画像だよ
- -vcodec libx264 → 映像コーデックはlibx264で
- -pix_fmt yuv420p → 色空間？はyuv420pでね
- -crf 16 → 品質は16で（低品質（51）～高品質（0）、ちなみに0はロスレスらしい）
- -t 10 → 動画の時間（秒）は10秒で
- -progress log.txt → log.txtというファイル名で、結果（経過）のログを残してくれ
- movie_graph.mp4 → 出力する動画の名前はmovie_graph.mp4ね
という感じですね。 ちなみに仕様上、元画像のピクセルサイズが奇数だと動画化できません。例えば301x240の画像だと駄目です。300x240、あるいは302x240など、偶数のピクセルサイズになるように調整しておく必要があります。このあたりも、今後工夫を盛り込みたいですね。。
Dim WSH2 As Object
Set WSH2 = CreateObject("Wscript.Shell")
WSH2.Run ("%ComSpec% /c " & command)
Set WSH2 = Nothing
→すみません、正直、よく分からないです。僕が説明するより、ググって頂いた方が早いし正確です。Excelのマクロからコマンドプロンプトを呼び出す呪文のようなものかと。３行目でcommandに入れたffmpegへの司令が実行されていまして、最後の行で肩の荷を下ろしてもらってます。
実は、今回一番困ったのがこの部分でした。コマンドプロンプトを呼び出すのに.Runと.execの2種類があるらしいです。そして初めは.execの方でやってたんです。というのは、.execの方でコマンドプロンプトを呼び出している例が、確か海外サイトかな、あったもので。。
で.execでやった結果、ちゃんと動くことも多いのですが、コマの枚数が増えたり、秒数を増やしたり、あるいは解像度を上げたりすると、途端にFFmpegが動作しなくなるんです。何でだろう？って。メモリも足りてるのに・・・。
で悩んだ挙げ句、標準出力とかを直接返さない.Runの方に切り替えたら上手くいったんですが、切り替えるのも僕には一苦労で・・・。うーん。。
.execで上手くいかない原因は僕には全くわかりません。誰か記事書いて下さい・・。とにかくここでハマってしまって、凄く時間と体力を取られました。。ちなみにWSH2となっているのは、WSHでやってて.execで失敗した名残です。記念にとっておきました。。
最後に
上記をすべて実行すると、全て上手くいっていれば、C:\MovingGraph_workingfolderというフォルダに動画ファイルが出来るはずです。
今回は９１枚のコマを、9.1枚／秒で、１０秒間の動画にしていますが、これをスクリプト上で指定するのでなく、セル上で、かつ自動的に設定できると（例えば秒数を指定したらコマ速度は自動で設定される、あるいはコマ速度を設定したら秒数は自動で設定される、など）、更に便利なんだろうなと思いますので、最終版完成時にはこれを実装したいと思います。
ひとまずこれで一連の流れはできました。
次回は、これらを統合した上で、更に便利にしたものを作成したので、紹介したいと思います。
https://magtanate.blogspot.com/2021/08/easy-motion-graph-file.html
DIY360
0 件のコメント:
新しいコメントは書き込めません。