エクセル雑感
「Excel3ステップ理論」3階層システムの応用

ExcelマクロVBAとエクセル関数についての私的雑感
公開日:2016-10-30 最終更新日:2016-12-29

「Excel3ステップ理論」3階層システムの応用

エクセルでシステムを作成する時に、念頭に置くべき3階層の考え方になります、


本来はExcelに限った事ではなく、システム作成では常に3階層を考えて設計しますが、

さらに考えを推し進めて3ステップとして考えます、つまり、「Excel3ステップ理論」です。

データを入力するシートで、さらに集計し、さらに印刷までやろうとしたら、
一旦は作れたとしても、その後の修正に耐え切れず、いずれ破たんしてしまいます。


3階層システムとは
システム開発における3階層とは、一般的には以下のようなものです。

プレゼンテーション層 ・・・ ユーザインターフェース、人が見たり操作したりする
アプリケーション層 ・・・ ビジネスロジック、データを集計加工したりする
データ層 ・・・ データベース(DB)

クライアントサーバーシステム(クラサバ)なら、
プレゼンテーション層は、画面フォーム、ブラウザ
アプリケーション層は、ミドルウェア
データ層は、DB

実際にはWEB技術を使う事も多く、
その場合は、必然的にプレゼンテーション層にはWEBブラウザが使われます。

少なくとも、
プレゼンテーション層は、クライアントPCに置かれるものでも
データ層は、サーバーに存在するものになります。
3階層の本旨からすれば、アプリケーション層もサーバーに置くことになります。

何らかの変更が発生した時に、システム全てを変更するのではなく、
当該層で変更を吸収し、他の層の影響を最小限に収めるための手法でだという事です。
システム変更に対し、柔軟に対応できるようにする手段だということです。


3階層システムは、サーバースペック向上により実現されてきたものですが、
3階層の考え方自体は、ずっと以前からあったものです。
したがって、これらをシステムとして形式的にとらえる必要はないのです。
重要なのは、3階層の考え方です。


この3階層の考え方をもっと広義にとらえ、Excelシステム作成に活用する事を考えます。
エクセルに限った事ではなく、Accessでも他のソフトでも同じ考え方になります。
・ユーザインターフェース
・ビジネスロジック
・データ
この3層でシステムをとらえる事が重要です。


エクセルでの3階層とは
エクセルでの3階層の考え方は、

プレゼンテーション層 ・・・ 人が入力・出力する
アプリケーション層 ・・・ データを集計加工したりする
データ層 ・・・ データそのもの

このように考えて、

入出力
集計加工
データ

この3階層で考えるようにします。
それぞれを、シート単位にする場合もあれば、ブック単位にする場合も出てきます。
さらには、クライアントPCとサーバーにされざれを置くようなことも可能です。
少なくとも、ブック単位にした場合は、マクロVBAが必須になってきますが、
シート単位で作成するなら、関数とリンク貼り付けだけでも作成は可能です。


では、各層の具体的な考え方、注意点を、下層から順に見ていきましょう。


データ
データはデータベースとして考えます。
データベースとは、情報(Data)の基地(Base)です。
そこに行けば、あらゆる情報が手に入るデータ基地です。

ここで重要な事は、どんな情報が必要なのかを決める事です。
そして、その情報は、どんな形で蓄積すれば良いかという事です。

まず最初に考えなければならないのは、どんな情報を蓄積するかです。
売上日、支店、部署、担当、、顧客、商品、数量、単価・・・
次に、その情報をどのような形で蓄積するかです。
売上日は、年、月、日、に分けるか
支店、部署、担当、商品は、コードなのか名称なのか
金額は、単価×数量で求まるが、金額そのものは必用ないのか
と言ったことも、しっかりと考えて決めます。

忘れてはいけないのがマスタです。
・顧客マスタ
・都道府県マスタ
等々になります。
顧客マスタなら、
氏名、性別、生年月日、血液型、住所、電話番号・・・。
氏名は、姓と名に分けた方が良いかどうか
住所は、都道府県と市区町村とさらにそれ以下に分けた方が良いか
これらを、一つ一つの項目について検討しなければなりません。
指針としては、細分化できるものは細分化して、データを蓄積しておくことをお勧めします。
氏名なら、姓と名に分けるという事です。
データは結合するのは簡単ですが、分離するのはかなり困難な作業になるからです。
また、性別なら、「男」「女」なのか、「M」「F」なのかと言ったことも決めておく必要があります。

データは、必ずしもExcelのシートである必要はなく、AccessやMySqlといったDBでも可能です。
Excelシートを使う場合は、
シートの形式は、データベース形式(リスト形式)にのっとって作成します。

データベース形式(リスト形式)
各列に見出しがあり、列見出しの下に1行1件のデータが並んだ表です。
以下の用語を使います。
タイトル行:列見出しの行です。表の一番上の行で列見出しがフィールド名になります。
レコード:1行が1件のデータになります。1行が1レコードという事です。
フィールド:列見出しの名称をフィールド名と言います。それぞれの列を指します。
フィールドの並びとしては、主となるキー項目を一番左に配置します。
顧客マスタなら、顧客IDを一番左に配置するという事です。
コードと名称があれば、コードを左に配置してください。
これは必須ではないのですが、そのようにしておくことで扱いやすいデータとなります。

今更言うまでもない気もしますが、このシートでは、セル結合など絶対にダメです。


集計加工

データを集計加工する訳ですが、
例えば、
・都道府県別の売上集計
・顧客年代別の売上集計
・支店別の売上集計
・商品別の売上集計
このような集計を行うシートになります。


システム作成では、最も技術が必要な部分です。
3階層のど真ん中に位置しますので、
上下の各層の影響を直接受ける部分です。
それは、言い換えれば、上下の各層の変更を吸収し、他の層に直接的な影響を与えないようにできるという事です。

例えば、データにおいて、
性別が、「男」「女」と入っていたり、「M」「F」と入っていたりしても、
出力においては、同じものとして扱えるようにするという事です。
もちろん、「男」「女」と「M」「F」が混在するデータに問題があるのは言うまでもないのですが、
万一そのような事態になっても、この集計加工の部分で吸収できるという事です。

この段階では、セル結合が部分的にはあっても問題になりませんが、
しかし、そもそもこのシートは人に見せるためのものではないので、セル結合自体が不要で無意味です。


入出力

ユーザインターフェースですので、いかに人に優しくするかがポイントです。
優しくと言っても、簡単なら良いという事でもありません。
入力なら、迅速かつ間違いなく、人の挙動に合わせて、人の盲点をカバーする
出力なら、わかり易く漏れが無く、人の目線を意識して、人の思考の助けをする
言うのは簡単ですが、正解が決まっているわけではないので、試行錯誤の積み重ねになります。

Zの法則、Fの法則
ここは最低限押さえたうえで設計しなければなりません。
悪徳商法の契約書じゃないのですから、死角になる位置に小さい文字で書くなんてことが無いようにして下さい。
時には、良い意味でこれを逆手に取った設計も否定はしませんけど。

入力は、元となる資料(伝票等)に即したものにします。
資料と入力順が違っていると、人は間違えやすいものです。

出力は、見易さが重要ですが、忘れてはならないのが印刷です。
印刷する時の用紙サイズも意識して作成しなければなりません。

この段階では、セル結合はほとんど問題ありません。
少なくとも出力に限定するなら、やはり綺麗さ見易さというのは重要です。
その為にセル結合が必要なら問題ありません。
ただし後述するカメラ機能を知らないがために、無理矢理に方眼紙Excelでレイアウトするというのは論外です。


3階層と考えると、少々難しく感じられてしまうかもしれません。
そこで、さらに考え方を広げて、3ステップとして考えるようにします。


Excel3ステップ理論
3という数値は、とても不思議な力を持つ数値です。
空間の3点は、常に一つの平面上に存在します。
従って、三脚はどんな場所でも3つの脚が接地します。
正多面体の5つの内、3つまでは正三角形です。
というような、余談はこれくらいにして本題です。

これまでの3階層も、階層などと難しく考えずに、3ステップと考えるようにします。
3ステップは、人間の思考・行動にとても良くマッチしています。
プログラミングの基本としても、3ステップは重要です。
プログラミングの基本~ロジックの組み立て
プログラミングの基本というと、プログラミング言語の基本文法についての解説と思われるかもしれませんが、ここでは、プログラミングする上で最も大切な考え方、ロジックの組み立て方について解説します、本来は言語は問わないのですが、VBAのサイトですのでVBAを例に解説します。前処理、主処理、後処理 何かをしようとしたとき、
・前処理
・主処理
・後処理
この考えは、とても重要です。

そこで、Excelシステムを考える時も、3ステップで考えてみましょうという事です。

・入出力、集計加工、データ
・出力、集計加工、データ
・出力、集計加工、入力

各ステップをどのように配置するかは、アレンジがあってしかるべきです。
パターンは、いろいろと考えられると思います。
重要な事は、
データ(入力)があり、それを集計加工して、出力する
ここをしっかりと把握することです。


以上を踏まえたうえで、代表的なExcelシステムの設計を見てみましょう。
Excel3ステップ理論の代表3パターンです。


パターン1:出力、集計加工、入力
入力:データベース形式(リスト形式)のシート
集計加工:SUMIF(S)、COUTIF(S)、VLOOKUP等で集計加工するシート
出力:集計加工シートから、必要な結果部分だけをリンクして体裁を整えたシート

データベース形式(リスト形式)に直接データを入力します。
データの正確さを考えると、あまり良くはないのですが、実際にはExcelでは非常に多いパターンではないでしょうか。

この場合の注意点は、

入力
入力シートに、入力規則や条件付き書式を設定し間違い入力を極力減らします。
入力規則のリストは非常に有効です、入力データをリスト内容に限定できるので、表記違いは無くすことが出来ます。
さらに、入力規則のIMEの設定も役に立ちます、間違い防止だけでなく、操作性も向上します。
集計加工
ダブルチェックを行うようにします。
簡単に言えば、縦集計と横集計が一致しているかをチェック判定すると言うようなことです。

例としては、
名前が、A,B,C,D,Eの5つがあった時、
間違って、bと入力されている場合が想定されるのなら、
A,B,C,D,Eのそれぞれのカウントまたは集計と、
全合計のカウントまたは集計が一致しているかの判定セルを設けておいて、
NGの場合は、IF関数または条件付き書式でメッセージや色で直ぐに分かるようにしておくのです。
出力
集計した表が一つで、そのまま印刷できる場合は、ここを省略しても構いません。
しかし、ビジネスでのレポート作成においては、
複数の集計表を1枚(A4等)に集約して作成する場合も多くあります。

集計加工シートから、出力する範囲をリンク貼り付けしてシートを作成し、
罫線やフォントの体裁を整えて、さらに印刷設定を行います。

複数の表を1シートに収めようとすると、列幅が揃わずきれいに作成できない場合も出てきます。
そのような場合は、リンクされた図として貼り付けを使います。
ホーム→貼り付け→その他の貼り付けオプション→リンクされた図(I)
いわゆるカメラ機能と言うものです。
ワードやパワポに表を貼付けていることをよく見かけますが、Excel内でも同じことが可能です。


パターン2:出力、集計加工、データ

データ:CSVや他のExcelファイル
集計加工:SUMIF(S)、COUTIF(S)、VLOOKUP等で集計加工するシート
出力:集計加工シートから、必要な結果部分だけをリンクして体裁を整えたシート

データは外部から持ってくる場合です。
多いパターンとしては、他システムで出力されたCSVデータを使う場合になります。

この場合の注意点は、

データ
CSVをExcelで開いて、セル範囲をコピペする場合は、
Excelが書式やデータを自動編集しているので、Excelで開いたデータ内容をよく確認してください。
CSVで、
0123 ・・・ Excelでは123
1-2 ・・・ Excelでは日付
このような変換が行われてしまうので要注意です。
このようなデータが含まれている場合は、
データ→テキストファイル
この機能を使い、列ごとに書式を指定して取り込んでください。


CSVではなく、Excelブックの場合もあります。
この場合は、シートにゴミデータが入っていることが非常に多くあります。
BIツール等で出力されてExcelファイルであれば、まずゴミデータが入っていると考えておくことです。
シート全体をコピペせずに、
実際にデータの入っている範囲を指定して、値貼り付けで貼り付けるようにして下さい。
それでも空欄セルにゴミデータが残ることもありますが、シート全体よりははるかに良いです。

コピペは非常に生産性も悪く、何より間違いの多い操作です。
このパターンのシステムの場合は、この部分だけもマクロVBAを作成する事をお勧めします。
データの取り込みだけなら、自動記録でも概ね作成可能ですし、
VBAを勉強すると言っても、これを実現するだけなら数時間もあれば十分でしょう。
参考.
CSVの読み込み方法
エクセルのマクロVBAでのCSVの読込方法としては。・テキストファイルとして読み込む ・ワークブックとして読み込む ・クエリーテーブルを使う ・ADOを使う ・PowerQueryを使う 大別するとこのようになります。この記事を書いた当初は、エクセルのマクロVBAでCSVの読み込みについてネットで検索したところ、
ADOでCSVの読み込み(SQL)
VBAでADOを使用し、CSVデータを読み込みます。ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を省略します。ADO以外の方法については、「CSVの読み込み方法」を参考にして下さい。
集計加工
他システムのチェックが完璧なら良いですが、実際はなかなかそうもいきません。
出力、集計加工、入力のパターンと同様の注意は必用です。
ダブルチェックは必ず入れておきましょう。
出力
出力、集計加工、入力のパターンと全く同じです。
3階層の考え方が活きてくる部分です。
データが多少違っても、出力が同じレイアウトなら、このシートは使いまわせます。
データの差異は、集計加工で吸収するようにすれば良いという事です。


パターン3:入出力、集計加工、データ

データ:データベース形式(リスト形式)のシート、Access等のDBも可能
集計加工:マクロVBAで処理、部分的にシートを活用
入出力:体裁を整えたシートを事前に作成しておき、そこにマクロでデータを入れる

入力用のユーザーフォーム(シートの場合もあり)でデータを入力し、
フォームのボタンクリックで、データベースにデータを蓄積します。
出力は、ユーザーフォームに出すことも、シートに出すこともあります。

基本的には、全てマクロVBAで処理するようにしますが、
必要に応じて、シートに関数を入れておき集計等を行う事もあります。

この場合の注意点は、

データ
同一ブック内のシートでも良いし、別ブックでも良いですし、
さらに、Accessにデータを保管したり、MySql等のDBを扱う事も可能です。

ExcelシートにしろDBにしろ、注意することは、各フィールドのデータ形式になります。
特に日付については注意が必要です。

日付と言うのは、ソフトによりそれぞれ独自の扱いになっています。
そこで、もっとも簡単なのは8桁数値で扱う事になります。
他システム出力のCSVの日付が8桁数値の場合が多いのは、この理由によるものです。
集計加工
元データが同一ブック内にあれば、普通にシートのデータして扱います。
外部(他ブック、他のDB等)であれば、
必要なデータを取得して、ブック内のシートに入れてから処理するとマクロが作りやすいです。

他ブックの場合なら、そのまま使っても良いのですが、
読み取り専用で開いて競合を避けるとか、
あえて競合により他者の編集を止めるといった考慮も必要になります。

ExcelファイルをDBとして扱いADO等での取得も可能なので、これも考慮した方が良いでしょう。
むろん、その場合は、データベース形式になっていることが前提になります。
DBからレコードセットにデータを取得したら、素直にシートに出力してから処理を進めて下さい。
参考.ADO(ActiveX Data Objects)の使い方の要点
ADOはMicrosoftが提供するデータベースアクセスのためのソフトウェア部品です。OLEDBをActiveXコントロールの形で使えるようにしたプログラミングインターフェースになります。ここでは、ADOを使用したデータベースへの接続方法を解説します。

マクロVBAでの作成になりますので、編集加工用のシートは必ずしも必須ではありません。
ただし、全てをマクロだけでやろうとすると、かなりの技術が必要になってきます。

そして、集計加工するにあたっては、
適宜、シートを作成して関数を入れて置き、
参照しているセル範囲のデータをマクロで置き換えたり、その結果をマクロで使う等の工夫をすることで、
マクロ作成が格段に楽になります。
特に、
並べ替えやオートフィルタと言った、Excelの協力な機能は積極的に使ってマクロを書くようにして下さい。

途中段階のデータや最終出力の一歩手前のデータをシートに出力しておくと、
マクロ作成がとても楽になりますし、マクロ作成段階での確認にも処理結果の精査にも重宝します。

先にも述べましたが、マクロのコード記述においても、3ステップは重要です。
プログラミングの基本~ロジックの組み立て
プログラミングの基本というと、プログラミング言語の基本文法についての解説と思われるかもしれませんが、ここでは、プログラミングする上で最も大切な考え方、ロジックの組み立て方について解説します、本来は言語は問わないのですが、VBAのサイトですのでVBAを例に解説します。前処理、主処理、後処理 何かをしようとしたとき、
・前処理、主処理、後処理
・データ取得、加工編集、データ出力
常に3ステップで考えていくようにします。
入出力
入力には、ユーザーフォームを使う場合が多くなります。
もちろん、シートでも構いませんが、シートですと入力チェックが面倒になります。

出力については、ユーザーフォームの場合もシートの場合もあります。
シートに出力する場合は、全てをマクロで全て作ろうなどとは考えないようにします。
印刷するとしても、
あらかじめ印刷用に作成しておいたシートの一部をマクロで書き換えて済むように考えてください。

例えば、納品書や請求書の場合など、
あらかじめ雛形シートを作成しておき、そのシートをコピーしてそこにデータを入れるようにします。
このようにしておけば、
多少のレイアウト変更や、書式変更くらいなら、マクロの書き換えが発生しなくて済むようになり、
メンテナンス性が格段に向上します。

マクロ技術が伴えば、出力をワードやパワポにすることも可能です。
しかし、印刷してしまえばどれも同じですので、あえて他ソフトにデータを入れ込む必要性はないと思います。
もちろん、スライドショーでプレゼンするならパワポを使ってください。


最後に、Excelで、本来の3階層を実現する方法の一つを紹介しておきます。


Excelで3階層を実現する方法
エクセルでも、本来の3階層を形式的には実現することはできます。
要は、3階層を分離して、
プレゼンテーション層だけをクライアントPCに置き、他をサーバーに置くようにします。

プレゼンテーション層 ・・・ PCにインストールされているExcel
アプリケーション層 ・・・ サーバーに置いたマクロが入っているブック
データ層 ・・・ 各種DB

プレゼンテーション層は、PCのExcelと、起動用のExcelファイルです。
サーバーに置いたマクロが入っているブックを呼び出す機能のみ実装したExcelファイルになります。
マクロのブックは、競合対策として読み取り専用で開くようします。
直接開くことが困難な場合は、マクロが入っているブックをローカルにコピーしてきて開くといった方法も可能です。

アプリケーション層は、マクロが記述されたExcelファイルになります。
このファイルは、サーバーに置きます。
これが中核であり、作成にあたっては注意すべき点が多く、かなりのExcel技術が要求されます。

データ層のDBは、特に限定されません。
良く使われているDBなら、ほとんど全て扱えます。


長々と説明してきましたが、Excel3ステップ要点としては、
データ(入力)があり、それを集計加工して、出力する
ここをしっかりと意識して作成するという事につきます。


3ステップで物事を考えることはとても重要ですし、考えやすいものです。
この3ステップの考え方をしっかりと身に着けて、
ホップ、ステップ、ジャーーーンプ



同じテーマ「エクセル雑感」の記事

Excelのリンクの管理について
プログラミングとは
「ネ申Excel」問題 への同意と反論
「Excel3ステップ理論」3階層システムの応用
「ポケモンを確実に見つける方法」をExcelで数学してみた
エクセルで「もういくつ寝るとお正月」
エクセルで連立方程式を解く(MINVERSE,MMULT)
VBAが消えてしまった!マクロが壊れて動かない!
スピらない スピル数式 スピらせる
難しい数式とは何か?
いくつかの数式の計算中にリソース不足になりました。


新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ