人気ブログランキング | 話題のタグを見る
ブログトップ

カクレ理系のやぶにらみ

tamm.exblog.jp

時間のある方はお読みください。軽い気持ちで読み始めると頭が痛くなります。

複雑な表をシンプルに

 Excelは奥の深いアプリケーションであり、それだけにファンも多いことはご存知の通りです。勉強した結果、Excelを使ってできることが広がっていくと、それに応じて作成する表もしだいに複雑なものになっていきます。個人のスキルと作成する表の構造の複雑さはほぼ正比例するようです。
 ものごとにはすべてプラス面とマイナス面があるように、表の構造が複雑になるとそれだけ高度なことができるようになりますが、後で修正や機能を追加したいと思ったときに手間がかかることになります。表のメンテナンスに時間がかかるというのは、暇つぶしにはいいかもしれませんが、できるだけ楽にメンテナンスできた方が、後々のことを考えると、いいに決まっています。ユーザーにとってExcelは判断するためのツールであって、主人ではないのですから。
 そこで、複雑で高度な表をなるべくシンプルなものにするにはどうしたらいいかという考え方をいくつかご紹介したいと思います。

1.データや条件を入力するシートと計算するシートを分ける
 1枚のワークシートにすべて盛り込むといろいろと不都合なことが起こります。

(例)
・データや条件を入力する場所が分散してしまい、使いにくくなる恐れがある。
・他人が使う場合、誤操作(計算式が入力されているセルに入力して上書きしてしまう、あるいは間違って消してしまう)の恐れがある。
・表のレイアウトを変更したい場合、思うようにいかないことがある。または非常に手間がかかる。

 そこで考えられるのは、データや条件を入力するところを別なワークシートに独立させることです。
 マクロをお使いの方はおわかりいただけるとおもいますが、複雑なマクロを作成するには、いくつかのブロックに分けて考えた方がつくりやすくなります。また、作成済みのマクロを必要に応じて呼び出すことで、さらに複雑な操作ができるようになります。
 表を作成するというのもまったく同じであって、全体をいくつかのパーツ(データや条件を入力するところ、計算するところ)に分けて考えて、それぞれワークシートを分けてしまった方が何かと使いやすくなるのです。

 たとえば、前年の実績に基づいて来年度の予算を作成する場合を考えてみましょう。
 前年の実績は各項目ごとの生データがデータベースに保存されているものとします。(これでワークシートが1枚使われることになります。)これを別シートで、月別項目別に集計する(これを「クロス集計」といいます)わけです。次に、別なシートで来年度の予算案を月別項目別に入力する表を作成するわけですが、このとき、入力した値と前年比が比較できるようにした方が作業は格段にやりやすくなります。

1)シートA:前年の実績データを入れる。
2)シートB:シートAのデータを月別項目別にクロス集計する。
3)シートC:シートBで集計した月別項目別の前年実績が読み込まれて表示されており、予算の数値を入力すると瞬時に前年比が計算される。

 前年実績のデータをシートAに取り込むには、データベースからエクスポートする方法とExcelからデータベースに接続してインポートする方法とがあります。
 Wordの差し込み印刷という機能もExcelなどで作成したデータファイルを取り込んで印刷するというものですから、インポートの一種であると考えられます。これに対し、エクスポートというのは、データベースの方でExcelなどのアプリケーションで扱えるファイル形式でデータを出力します。その際に、クエリを使って必要なデータだけを抽出することができます。その代わり、データベースが更新されてもその変更内容はExcelに反映されません。
 Excelからデータベース(別のExcelのブックでも可)に接続するインポートでは、いったん全部のデータを読み込み、その中から必要なデータを抽出して集計する必要があります。(ただし、データベースにあらかじめクエリが作成されている場合、そのクエリに接続すれば必要なデータの抽出はクエリが行います。)インポートには、「ファイル」メニューの「開く」からファイルの種類を指定してインポートするやり方と、「データ」メニューの「外部データの取り込み」を使うやり方の2種類があります。
 「開く」からインポートした場合、元のデータベースが変更されてもその内容はExcelに反映されませんが、「外部データの取り込み」からインポートした場合は、元のデータベースが変更されればその内容がExcelに反映されます。
 どちらを利用するかはユーザーの選択に任されています。データベースの最新の内容を用いる必要があるのであれば、反映可能なインポート(外部データの取り込み)を使うことになりますし、そうでないのであれば、エクスポートや「開く」を用いたインポートを使用することになります。

(参考)Excel2010で別のExcelブックからデータを取り込む方法
1)「データ」タブを選択し、「既存の接続」をクリックします。
2)「既存の接続」ダイヤログボックスが表示されるので「参照」ボタンをクリックします。
3)「データファイルの選択」ダイヤログボックスが表示されるので、取り込みたいExcelブックがあるフォルダを開きます。
4)Excelブックが表示されない場合は、「ファイル名」の右側にある「すべてのデータソース」をクリックして、ドロップダウンリストを展開させて、「Excelファイル」を選択します。
5)取り込みたいExcelのブックをクリックして、「開く」ボタンをクリックします。
6)取り込みたいExcelブックの中にワークシートが複数ある場合、「テーブルの選択」というダイヤログボックスが表示されるので、取り込みたいデータのあるシートを選択して「OK」をクリックします。
7)「データのインポート」というダイヤログボックスが表示されるので、表示方法を「テーブル」、データを返す先を「新規ワークシート」として「OK」をクリックします。。


 なお、シートBをつかって行うクロス集計のやり方には、ピボットテーブルを使うやり方とデータベース関数とデータテーブル機能を組み合わせて行うやり方があります。ピボットテーブルの方はよく知られていますが、データベース関数を用いた方法については改めてご紹介することにします。
by T_am | 2011-03-06 15:28 | Excel のあの手この手

by T_am