スポンサーリンク


マネースクリプト診断

ポテンシャル年収診断


2019年10月13日

本質から学ぶExcel講座(2):モデルとビューの分離を理解する

モデルとビューの分離

 ども勉三です。「本質から学ぶExcel講座」の第2回目。今回からいよいよ実際の解説に入っていきます。最初のテーマとして取り上げるのは「モデルとビューの分離を理解する」ということ。

モデルとビューの分離とは?

 「モデルとビューの分離」はソフトウェアエンジニアリングの世界で常識となっている設計原則です。これは、ソフトウェア開発の際、データ構造やその関係性といった「モデル」の部分と、それをユーザに分かりやすく表示するための「ビュー」の部分は、可能な限り分離した方がいいという経験則です。

 このようにしてソフトウェアを設計することにより、データの集計の仕方や見せ方を変えるだけであればビューだけを変更し、モデルを変更する必要が無くなります。データの集計方法や見せ方というのは頻繁に変わるものなので、その部分だけを独立させておけば変更の影響を最小限に留めることができるというわけです。

 この「モデル」を理解するためには、「リレーショナルデータベース」という概念も説明しておいた方が良いでしょう。別にモデルはどのように表現してもよいのですが、多くの場合、リレーショナルデータベースという形で表現されます。

リレーショナルデータベースとは?

 リレーショナルデータベースという言葉を聞いたことのない人も多いと思います。でも Oracle や Microsoft Access といった言葉は聞いたことがあるのではないかと思います。

 リレーショナルデータベースについて抽象的に説明するよりも、実例を見て頂いた方が分かりやすいと思いますので、よくある Excel シートから、ビューとモデルを分離し、モデルをリレーショナルデータベース風に表現するとどうなるのかを見ていきたいと思います。

よくある Excel シートの設計上の長所と短所

 まずは下記のシートをご覧ください。

図1:よくある Excel シート

006_001.GIF

 これは、ある企業の2016〜18年の支店別売上高を集計したシートと考えてください。この図だけだと「売上高と書いてない」とか「金額の単位」が書いていないといった問題点はありますが、そこは一旦目をつぶって見てください。

 見やすさという観点ではどうでしょうか? 構成上大きな問題は無いのではないかと思います。支店が地方ごとにまとめられていて合計金額も表示されており分かりやすいです。ここから支店別の売上高の折れ線グラフをつくったりすることも容易にできるでしょう。

 しかし、これを「ビューとモデルの分離」という原則から見た場合、色々と問題があります。

一次データと二次データの混在

 まず1つは一次データと二次データが混在している点です。このシートでは合計の行は数式を使って他のセルの値から計算させています(例:C10セルであれば =SUM(C4:C9) など)。一方で、合計以外の行は値を直打ちしています(例:C4セルであれば 30 など)。つまり、ぱっと見では同じ数値データに見えても、それ自体が生の数値である一次データと、一次データを集計して作られた二次データとが1つのシートに混在していることになります。

 このように一次データと二次データを混在させることができるのが Excel というツールの柔軟性であり長所でもあるのですが、ミスの温床にもなります。例えば、合計値はもしかしたら数式ではなく生の値が入力されているケースもあります。例えば財務諸表などでは、決算書で表示されていない端数の影響により、要素を足し合わせても合計値にならないことがあります。そのような場合には、合計値を財務諸表の値に揃えるため、シート内で数式で計算するのではなく値を直打ちすることもあるでしょう。しかし、どのように実装されているかはセルの中身を見てみないと分かりません。

 一次データと二次データが混在している場合、一次データを修正する際にどのセルを手動で修正すれば良いのか、どのセルは自動で修正されるのかが分かりにくくなります。今回の例のような小規模なシートであれば、さほど問題はないかもしれません。しかし、もっと大規模で複雑なシートになると、作成した本人でさえ把握が困難になります。

 ちなみに、この問題に対処するため一次データは青字、二次データは黒字にするなど、セルの文字色や背景色によって区別するという手法が取られることもあります。投資銀行やコンサルで財務モデリングをExcelで作る際にはそういった工夫をしています。

セル結合している

 もう1つの問題点はセル結合です。セル結合すると見る側には分かりやすくなるのですが、編集の手間もかかるようになりますし、他のシートにコピペなどもしづらくなります。また、このシートをもとに、別にさらに地方別合計だけを取り出した表を作りたい時などにも、vlookup などで参照しづらくなります。

 誤解しないで頂きたいのですが、「セル結合するな」と言っているわけではありません。人に見せるためには関連する項目は結合されていたほうが分かりやすいです。ここでの本質的な問題は「見せるための表と、分析するための表では求められるものが異なる」ということです。それを1つの表で実現しようとすることが問題なのです。

 そして、これこそがまさに「ビューとモデルの分離」が重要である理由なのです。

モデルを分離し、リレーショナルデータベース風に設計した場合

 では次に、図1の「よくある Excel シート」から、見せるための要素を除外し、純粋にデータだけを分析しやすい形に抽出したものを見て頂きます。それが下の図2です(長くなるので図1の秋田支店までの部分のみになっています)。

図2:モデルを分離したもの

006_002.GIF

 いかがでしょうか? おそらく、何をやっているかは何となくわかって頂けるのではないかと思います。そして、これこそがビューとモデルに分離するということです。なお、図2ではモデル部分のみになりますが、ビュー部分に関しては別のビューシートを1つ作り、このモデルシートを参照させて図1のような見やすい表を作ることになります(それほど難しくありません)。

行と列からなるテーブル状にデータを整理する

 ここでやっていることは、まず行(縦)と列(横)から構成され、セル結合などを行わない、質素で武骨なテーブル状にデータを整理することです。

 その際の基本原則として、列はデータが増えても変わらない普遍的な性質のみにするということです。図1では2016〜18年の売上高を列方向に並べていましたが、各年の売上高のデータというものは毎年増えていく性質のものです。そういったものは列ではなく行方向に並べることになります。そのため、C列で年、D列で売上高という形にします。

 大事な点として、地域ごとの合計といった二次データは含まれていない点です。そういったものは見せる用の別シートで計算すればよいだけなので、モデルだけを抽出したテーブルには含める必要がありません。

 また、セル結合を使っていないことも大きな変更点です。セルA2〜A4やB2〜B4などは内容が同じなので重複観があり、ついつい結合してしまいたくなりますが、してはいけません。このようにした方がデータの分析がしやすいのです。

リレーショナルデータベース風に一次データを持つことの利点

 このようにデータを持つことがリレーショナルデータベースと考えて頂ければ、おおよその理解としては問題ないと思います。リレーショナルデータベースと聞くと難しく感じますが、要は見栄えを排除した純粋なテーブルにデータを整理するということです。

 もちろん、これだけだとプライマリーキーもないですし、テーブル間のリレーションもなく、正規化もされていないわけで、完全なリレーショナルデータベースになっているわけではありません。あくまでリレーショナルデータベース「風」です。

 ですが大切なのは、これまで Excelで作成していたシートには、見せるための部分(ビュー)と実際のデータ(モデル)が混在していることを理解し、そのモデル部分のみを抽出した際にはこのようなテーブル構造として表現できるということを理解することです。この理解ができるだけでも随分見通しがよくなります。

 そして、このようなテーブル構造で純粋な一次データだけを集計しやすい形で分離することにより、いくつかのメリットがあります。

 まず、データの変更にあった場合、一次データの入ったモデルシートのみを変更すればよいので分かりやすいことです。合計値などは別に作成するビューシートで自動的に集計されるので、同じシートに一次データと二次データが混在することはありません。

 また、逆に見せ方を変えることになった場合にも、ビューシートのみを変更すればよいので、誤って一次データを変更してしまうといったこともありません。

 さらに副次的な利点として、他のツールに移行したり、あるいは他のツールと連携しやすいというメリットがあります。図2のような形でデータを持っておけば、Oracle や Postgresql などの本格的なリレーショナルデータベース、あるいは Microsoft Access などに移行する際も最小限の調整で済みます。データ件数が少ないうちは Excel で試験的にデータを管理し、実稼働しデータ数が増えてきたら、きちんとしたリレーショナルデータベース化するという柔軟な対応が取れるようになります。つまりスケーラビリティの面で有利と言うことです。

 TSVやCSV(これらはタブやカンマで値を区切ることでテーブル構造をテキスト形式で表現するもの)などにもそのままエクスポートできるので、Tableau や R といった他の解析ツールでも解析しやすく、Java や Python といったプログラミング言語で解析やデータ加工を行いたい場合にも便利です。Excel内でもピボットテーブルなどによる解析も、図2のようなテーブル状のデータを想定しているため分析しやすいです。

必ずモデルとビューを分けろと言っている訳では無い

 ただ、誤解しないで頂きたいのですが、「必ずモデルとビューを分けて Excel ファイルを作れ」と言っている訳ではないのです。今回お見せした例(図1)のような場合、モデルとビューでシートを分離させずとも、図1の設計のままで行くのも十分にアリでしょう。

 より本質的に大切なのは、「モデルとビューを分離した設計が想像できること」(実際に分離するかどうかに限らず有用)、そして「異なる設計間の pros/cons を比較し、最適な設計を選択できること」です。

 例えば、図1のようなモデルとビューが混在した普通の設計にも、分かりやすさ、スピード、構造の単純さとというメリットがあります。対して図2の設計は、スケーラビリティ、柔軟性、分析容易性といったメリットがあるでしょう。この両者を天秤にかけ、現在自分がしたいこと、行わないといけないことは、どちらの設計が適しているかを決定することができる。それが「Excel を本質から理解し使える」ということだと思います。

タグ:Excel講座

スポンサーリンク




posted by 勉三 at 13:53 | Comment(0) | TrackBack(0) | IT
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント:

この記事へのトラックバックURL
http://blog.sakura.ne.jp/tb/186677797
※ブログオーナーが承認したトラックバックのみ表示されます。

この記事へのトラックバック