カテゴリー: Excel

Excel VBAからWorkbooks.Openにてファイルを開くときに、WorｋBook_Openに書かれたマクロの自動実行を防止するにはファイルを開く前後において、Application.EnableEventsにおいてイベント処理を停止しておくと良い。

Application.EnableEvents = False
WorkBooks.Open(～)
Application.EnableEvents = True

縦横に小計や合計が入っているエクセルシートを間違えずに作るために、意識すると良い点を紹介してゆきます。

見せるための表とデータを保持するための表を分ける。
ユーザーに綺麗に見せるためのシートと、集計するためのデータを保持するためのシートは、分けて作ります。集計するシートには直接データを入力せずに、データ保持用のシートから、式を使って見せるためのシートにデータを取り込みます。
データ用のシートは誰かに見せるための物では無いので、集計のしやすさを優先して作成します。必ず１データを一行で表示し、セルの結合などは使いません。
データを管理する部分と、表示のための機能を分ける考え方は、モデルビューコントローラーモデルと呼ばれ、1990年前後からソフトウェアの設計に取り入れられるようになった方法です。モデルはデータを管理する部分、ビューは表示のための部分、コントローラーはモデルを操作するための部分でExcelがそれに当たります。
このようにデータと表示を分けておくと、表示を様々に変化させてもデータが影響を受けることが無いため、データを誤って書き換えてしまう心配がありません。頻繁なレイアウト変更の要求にも応えやすくなります。また同じデータを複数の方法で表示しているとき、データに変更が発生しても、複数の表示用シートに簡単に反映させることが出来ます。

小計や合計は式を入れる。
個々の値をsumで足しあわせて小計を、小計を足しあわせて合計を求めます。
当たり前のようですが、オートsumで上手く選択出来なかったり、自動の書式指定で上手く表示出来ないと、そこであきらめて数字を直接入力してしまったりする人は少なくありません。きっちり調べて式や表示書式で実現するようにします。
またsubtotalと言う関数がありますが、これは使いません。標準のドキュメントでは、subtotalは選択範囲内にある小計などの計算式を無視して合算を表示してくれるので、小計と合計が混在している表で容易に合計を求められる関数として例示されています。ですがこのような集計の方法をとっていると、小計計算式の選択範囲が誤っていても、合計は正しい値になってしまうため、小計が間違っていることに気がつき難くなります。合計は小計を足し合わせる式として書いていれば、小計が間違っているときに合計も誤った値になるので気がつきやすくなります。

合計や小計が正しいことを手早く判断する。
合計や小計が正しいことを手早く判断するには、合計や小計の計算元となっているシート全体を選択したときに右下に表示される合算が、合計の整数倍になっていることを確認します。もしどこかの指揮が誤っているなら、合計の整数倍にはならないので、各式を精査して誤っている箇所を探します。

以上のようなことに気をつけると、手早く、誤りないExcelの資料を作れます。

Ｅｘｃｅｌで複雑な計算式を多数入力していると、自動計算やＦ９による再計算で、一部の数式が再計算されずに正しい値を表示しない事があります。
ＣＴＲＬ＋ＡＬＴ＋ＳＨＩＦＴ＋Ｆ９で再計算する
Ｅｘｃｅｌは値が変更されたセルや、再計算が必要なセルを記憶しており、必要最低限の計算量で再計算をおこなうようになっています。稀にこれが正しく動作せずに再計算が行われなくなる場合があります。この場合はＣＴＲＬ＋ＡＬＴ＋ＳＨＩＦＴ＋Ｆ９を押すことで、変更されているか否かにかかわらず、すべての式を計算します。これによって次回から変更されたセルに関する情報がリセットされ、正しい計算結果を返すようになります。
数式のどこかにエラーがある
数式のどこかで循環参照などのエラーがあると、そこで計算をやめてしまい、その他の式が計算されなくなる場合があります。この場合は循環参照を起こしている数式を正しく修正することで、正しい値を返すようになります。