Excelを使ったデータベースの作成と活用法

〇 Excelを使ったデータベースについて

• データベースとは、特定の種類の情報（ex.複数の工事情報）を、一定の項目（ex.工事コード、工事名、工期、…）で整理したものです。通常のExcelの表で、データ件数が多いものと考えて下さい。情報をデータベース化することで、コンピュータ上で容易に検索・集計・分析などができるようになるため、業務の生産性向上が図れるほか、データを加工することで業務上の問題点の発見など付加価値の向上につなげることが可能です。
• Excelのデータベースでは、（一般的に）先頭行で横軸方向に項目名を区切り、縦軸方向2行目から1行ごとにデータを1件ずつ入れていきますが、横軸の項目数や縦軸のデータ件数の制限は事実上気にする必要がなく、また様々なデータベース機能も用意されているため、通常の事務業務ではExcelで十分なデータベースを構築できます。
• 「事務管理に役立つExcelの関数」の合計関数（SUBTOTAL）で説明したように、例えば工事情報のように検索条件に合う合計を求めるデータベースでは、1行目は合計欄とし、2行目に項目名、3行目以下にデータを置くようにすると、使いやすいものとなります。
• 特定の情報を入力・管理する市販のアプリケーションソフトでも入力情報をデータベース化してくれるものもありますが、アプリが提供する既定の検索画面や管理帳票では必要な結果が得られないことも少なくありません。この点、Excelであれば、実務の目的に合ったデータ加工が自分で容易にできるため、ローコストで質の高い業務資料を適時に作成することが可能となります。
• 既に特定の業務でアプリケーションソフトを利用している場合で、アプリ側に主たるデータをＣＳＶ出力できる機能があれば、アプリはデータ入力などでそのまま使用しながら、定期的にＣＳＶ出力したデータをExcel上に展開してデータベースとして活用する方法もお勧めです。

〇 有用なデータベースとは

• 日常的あるいは定期的に集計・検索・分析などを行うデータで、データ数が少なくとも100件程度以上あれば、業務の生産性向上には、データベース化しておくべきでしょう。
• 建設業の事務関係でデータベース化して有用と思われるものは、営業中工事情報、工事契約情報、施工中工事情報、決算済工事情報、入金情報、発注・支払情報、労災・事故情報、品質事故情報、得意先名簿、従業員名簿、協力会社名簿、など様々なものが考えられます。
• 例えば、工事に関する情報で、営業中工事情報、工事契約情報、施工中工事情報、決算済工事情報の4つを例示していますが、これらの情報を1つのデータベースとしてまとめることも可能です。データベースの数は少ない方が管理が楽ですし、1つのデータベースに様々な情報があることで、より複雑な検索や分析も可能となります。
• 一方で、それぞれの工事情報は管理する内容が異なるので、1つのデータベースとしてまとめると項目数がやたらと増えて扱いづらいものになるかもしれません。データの検索方法を誤り、決算済工事のデータだけを収集したつもりなのに未成工事情報を紛れ込ませてしまうかもしれません。更には、あらゆる情報を1つのデータベースに一元化したものは、サーバー上で共有化した場合に閲覧できる社員は限定せざるを得ず、またもし情報漏洩したりすると取り返しのつかないことになりかねません。
• どのような情報をどのような形でデータベース化するかは、データの収集方法や活用頻度も考慮したうえで、事前の十分な検討が欠かせません。ただ、これらの4つの工事情報をばらばらにデータベース化しても、キーとなる工事コードが同じで、なおかつデータベースが同じＰＣ（もしくはアクセス可能なサーバー）上に存在すれば、Excel関数を使って相互に自由に参照が可能です。個人的には、データベースは使用する目的に応じ、細分過ぎない範囲で分けて作成した方が無難と考えています。
• この他、社内外の関係者に日常的に対応した内容をメモ（記録）したものですら、工夫次第で立派なデータベースとなり、業務に大いに役立ったりします。要は、後で調べたり加工したりするデータで、その量が多いものは、整理してデータベース化すれば、業務の生産性や品質向上に役立つ可能性があるということです。ただ、データベース作成・維持に要する人件費とデータベースの利用価値については比較評価して「費用対効果」を計ることが肝心であり、費用対効果があるものだけに絞ってデータベース化するのは当然のことです。
• 既に事務業務で使用しているシステムがあり、そのシステムから定期的にデータをＣＳＶ出力してデータベースを作る場合はコストが相当低いため、多くのケースで費用対効果が出ますが、データベース化するためだけにデータを一から作る必要がある場合は作成にコスト（人件費）がかかり過ぎるため、日常的によほど活用できるものでないと割に合いません。

〇 Excelの主なデータベース支援機能

• Excelには様々なデータベース支援機能がありますが、数十件程度のデータならともかく、数百件、数千件のデータを扱う実務においては、必要性の薄いものもあります。
• << テーブルスタイル >> データベースの表の中にカーソルを置いて、［ホーム］タブの［テーブルとして書式設定］をクリックすると、［テーブルスタイル］を好みで選ぶことができて、データ1行おきに塗りつぶしがされたり、項目行にオートフィルターが設定されたりし、これがデータベース作成機能とされています。しかし、表の書式は自分で設定すればよく（大規模なデータでは余計な装飾は煩わしいし、塗りつぶしについては別途［条件付き書式］で使うので邪魔になります）、オートフィルターも必要に応じ自分で設定すればよいだけですので、必要性は感じません。
  
• << カード形式入力 >> データをExcelシートの表からではなくカード形式で入力できる機能もあります。カード形式の入力画面は、あらかじめクイックアクセスツールバーのユーザー設定（⇒「Excelの便利な機能・テクニック」参照）で、［コマンドの選択］→［すべてのコマンド］にある［フォーム］をクイックアクセスツールバーに追加しておきます。次に、データベースの表の中にカーソルを置いて、クイックアクセスツールバーの［フォーム］をクリックすることで表示されます。カード形式では、1件ずつデータの各項目を手入力していくことになりますが、実務では各項目の内容は前後など他のデータ内容を複写したり複写後に一部修正したりして入力することも多いため、表形式で入力する方がはるかに効率的で、わざわざカード形式を選ぶ必要性は薄いと思います。
  
• 一方で、Excelの次の機能は、データベース支援機能としても大変有効ですので、上手に活用すると良いでしょう。
• << オートフィルターによる抽出 >> データベースの表の中にカーソルを置いて、［データ］タブの［フィルター］をクリックすると、項目名の欄に［オートフィルター矢印］が表示されます（始めからオートフィルターを設定したい項目部分を選択してから［フィルター］をクリックしてもよく、その方が希望通り設定されて確実かもしれません）。項目の［オートフィルター矢印］をクリックし、検索条件として、文字であれば［テキストフィルター］、数値であれば［数値フィルター］をクリックし、［オートフィルターオプションダイアログボックス］で［抽出条件の指定］を行います。1つの項目について2つまで条件を入力でき、2つの条件を入れた場合は、各条件を AND か OR で関連付けます。ある列の項目で抽出条件を指定し、別の列の項目（、更に別の列の項目…）と 抽出条件を指定すると、これらの各抽出条件は AND で関連付けられます（OR で関連付けることはできません⇒※次項参照）。抽出結果を元に戻すには、［データ］タブの［フィルター］横の［クリア］をクリックします（［フィルター］そのものをクリックすると［オートフィルター矢印］が消えてしまいます）。
  
• ※オートフィルターの各項目に設定した抽出条件同士を OR で関連付けることはできないと書きましたが、新たに列を挿入して計算式を入れることで事実上 OR条件 による抽出も可能となります。「事務管理に役立つExcelの関数」の論理関数（IF、IFS、AND、OR）の事例で、G列の「重要工事」は OR条件（土木AND売上高3000以上 OR 建築AND売上高5000以上）を含む計算式で求めており、当該項目のオートフィルターで「重要工事」を抽出すれば、 OR で関連付けた抽出を行ったことになります。
• << 並べ替え >> データベースの表の中にカーソルを置いて、［データ］タブの［並べ替え］をクリックすると、並べ替える範囲が自動で指定され、項目行が並べ替えの項目見出しとなり、［並べ替えダイアログボックス］が開きます（もし並べ替える範囲が希望通りに指定されなかったら、自分で並べ替える範囲を項目行も含め指定してから［並べ替え］をクリックし、［並べ替えダイアログボックス］で［先頭行をデータの見出しとして使用する］にチェックをいれます）。［並べ替えダイアログボックス］で［並べ替え条件の指定］を、並べ替えるキーとなる項目名と昇順（数字なら小さい順、日付なら古い順）・降順（数字なら大きい順、日付なら新しい順）の別を指定して行います。キーとなる項目が複数ある場合は、［レベルの追加］をクリックし、新たなキー項目名と昇順・降順の別を指定します。キー項目の優先順序は、▲［上へ移動］▼［下へ移動］キーを使って変更できます。レベル数の上限は64なので、事実上制限を考える必要はありません。一度並べ替えを実行してブックを保存すると、同じシートでは次回［並べ替え］クリック時にも同じ設定が初期表示されるのも、手間が省けて大変助かります。
  
• << 条件付き書式 >> データベースではデータが大量にあるので、ある条件に合致したデータだけを視覚的に識別したいことがありますが、これを容易にできる方法が条件付き書式です。例えば、次の表は重要工事の行だけをピンクで色付けするものですが、セルA3にカーソルを合わせ、［ホーム］タブの［条件付き書式］の［新しいルール］をクリックすると、［新しい書式ルールダイアログボックス］が開くので、［数式を使用して、書式設定するセルを決定］を選び、［次の数式を満たす場合に値を書式を設定］欄に【 =$G3="重要工事" 】と入力し、［書式…］をクリックして塗りつぶしでピンク色を設定し、［適用先］欄に【 =$A$3:$G$9999 】と入力します。このケースでは、数式で $G3 と列は（$を付ける）絶対参照とし（Ｇ列に重要工事が表示されているため）、行は（$を付けない）相対参照とすることがミソで、このことによりセルA3だけに条件付き書式設定したにもかかわらず、データベース全体に条件付き書式が設定されます。この機能は様々な応用が可能で、例えば工事一覧のデータベースを毎月更新する場合、1つのブックに「当月工事一覧」のシートの他に「先月工事一覧」のシートを作っておき、「当月工事一覧」に先月の内容と異なる場合に塗りつぶしで色付けする条件付き書式を設定することで、全ての工事の全ての項目で先月と異なるものが視覚的に確認できるようになることから、データの更新漏れを防ぐ手助けとなります。ただ、条件付き書式設定は、列を挿入したり削除したりすると適用範囲が乱れることがあるので、このような場合はいったんシート全体からルールをクリアして再設定することを勧めます。
  
• << 印刷 >> Excelの印刷でも、一度印刷設定を行ってブックを保存すると、同じシートでは次回印刷プレビュー時にも同じ設定が初期表示されます。この機能を利用して、［ページレイアウト］タブの［印刷タイトル］をクリックして現れる［印刷範囲ダイアログボックス］で、［シート］タブの［印刷範囲］はデータベースの全ての列（ここでは A:G ）、［タイトル行］は全てのページで印刷する行（ここでは $1:$2 ）を設定しておきます。また、必要に応じ、［ヘッダー/フッター］タブの［ヘッダーの編集］で印刷タイトルや印刷年月日の設定を、［フッターの編集］でページの設定をしておきます。次に、クイックアクセスツールバーの［印刷プレビュー］をクリックし、用紙の大きさ（データベースが大きければ通常 ［A3］）、印刷方向（データベースが大きければ通常 ［横方向］）、拡大縮小（これは ［すべての列を1ページに印刷］ を設定）して、プレビューで確認し、良ければブックを保存しておきます。このように設定しておくと、データベースをオートフィルターでどのように抽出しても、また不必要な列をどのように非表示にしても、［印刷プレビュー］をクリックするだけで必要なデータベースの抽出結果を印刷でき、印刷に関するストレスが全くなくなります。なお、データベースの印刷物では、ヘッダーなどに印刷年月日が（必要に応じ時刻も）表示されるように設定しておくことは重要で、後でいつの時点のデータから作成した資料かを確認する唯一の手段となります。
  
• << ピポットテーブル >> ピポットテーブルは、データベースを容易にクロス集計してレポートを作成するツールで、データベースの各項目を行にも列にも設定できます。 データベースの表の中にカーソルを置いて、［挿入］タブの［ピポットテーブル］をクリックすると、［ピポットテーブルの作成ダイアログボックス］が開くので、テーブル範囲が適切か確認し（範囲が希望通りでない場合は設定し直す。なお、範囲の1行目は必ず項目行になっている必要がある）、［OK］をクリックします。別シートにレポート作成画面が現れるので、［ピポットテーブルのフィールド］で、「フィールド」の欄にある集計するキーとなる項目を選び、ドラッグ＆ドロップで「行」の欄や「列」の欄に移動させ（「行」の欄や「列」の欄に複数の項目を選んで階層化することも可能）、最後に集計する項目を「値」の欄に移動させると、クロス集計されたレポートが作成されます。工事一覧表では、例えば、決算期別・土建別の売上高や総利益、決算期別・得意先別の売上高や総利益といったものが容易に作成できます。なお、オートフィルターで抽出した状態で［ピポットテーブル］をクリックしても、抽出されたデータではなく全データに対しクロス集計されるので、このような場合、いったんオートフィルターで抽出したデータを別シートにコピーし、コピーしたデータに対し［ピポットテーブル］でクロス集計する方法を取ります。
  

〇 Excelブックにおけるデータベースの構築例

• Excelで使い易いデータベースを構築するには若干の工夫が必要ですが、データを1つのブック内に手順に3つのシート（これを仮に「元データ」、「基本データ」、「加工データ」と呼びます）に分けて生成しておくのもその一つです（各［シート見出し］を、それぞれ、元データ、基本データ、加工データと設定するものとします）。
• 例えば、工事情報に関するデータベースを作るに当たって、会社の経理システムがあり、そこから定期的（ex.毎月の締め後）に工事情報（工事コード、工事名、土建区分、工期始、工期終、受注高、施工高、取下高）に関するデータをCSV出力できる場合、まずはそのデータをダウンロードして、1つのシートに展開します（「元データ」）。
• 次に、別シートに、「元データ」を加工し、目的のデータベースを生成します。「元データ」の項目の左右順序を見易く変えたり、普段使用しない項目を省略したり、コードを対応する名称に変換したり（例えば「土建区分」が1や2のコードだった場合は1を「土木」、2を「建築」と変換するなど）、新たに計算式によって生成される項目を追加したり（例えば「工期」の開始日と終了日から、「工事期間」を日数で求めて表示するなど）、書式設定で見易くしたりしたものを作成します（「基本データ」）。「基本データ」の内容は、工事コード（=キーコード）以外は「元データ」から計算式で生成されるようにすることが大事で、これにより毎月、「元データ」を更新して、更新された「元データ」A列の工事コードを「基本データ」A列にコピーするだけで、自動的に「基本データ」全体が更新されます。
• 「基本データ」が目的のデータベースですが、「基本データ」に対しては抽出などのデータ加工操作をすることはせず、「基本データ」の内容を別シートに計算式を抜いて（値と書式のみ）複写します（「加工データ」）。データベースを、特定の条件で抽出したり、特定の項目を非表示にしたり、条件付き書式で特定のデータに色を付けたり、特定の項目の順序で並べ替えたり、ピポットでクロス集計するテーブルとするのは、必ず「加工データ」上で行うようにすれば、「加工データ」をいかように操作しても、また誤ってデータを削除したりしても、いつでも「基本データ」から復元できるので安心です。
• 実務では、複数の「元データ」から「基本データ」を生成することもあります。例えば、経理システムからCSV出力された「元データ」である工事情報に、当該工事の所長の社員コードはあるが社員名がない場合は、別途、人事システムなどから社員コード・氏名・部署・役職などをCSV出力し、（「元データ」の一つである）「社員マスタ」として別シートに展開しておくと、「基本データ」には社員コードから参照される所長名や所属部署・役職名などを表示することが可能となります。なお、この例では、「社員マスタ」を同一ブック内の別シートに展開せず、サーバー上の共有ファイルとして保存し、リンクを張って参照することも考えられますが、扱いやすさという観点では、ひと手間掛かっても同一ブック内の別シートに展開するほうが便利でしょう。
• 「基本データ」は「元データ」の内容を計算式で引用して生成しますが、その場合に使用するのが参照関数です。（⇒「事務管理に役立つExcelの関数」の参照関数（VLOOKUP）の項を参照）
• 「元データ」の内容は次の通りとします。
  
• 「基本データ」の内容は次の通り、「土建区分」が区分名と変わり、「工事期間」「決算期」「進捗率」「取下率」「重要工事」が新たに項目として追加されています。「基本データ」では、VLOOKUP関数の検索値(=キーコード）である「工事コード」以外は、全て計算式で求めます。即ち、工事コードのみ「元データ」から「基本データ」にコピーすれば「基本データ」生成は完了します。
  
• 「加工データ」の内容は次の通り、「基本データ」の内容を計算式を抜いて（値と書式のみ）複写したものです（1行目の合計欄の計算式を除く）。
  
• 「元データ」からデータを引用する「基本データ」の各列の3行目の計算式は次の表のようになります。計算式は、「基本データ」の項目を増やしたり入れ替えたりしても計算式を直す必要がないよう、「元データ」と「基本データ」の工事コードと項目名が合致すれば正しく引用されるようにしています。結果的に、VLOOKUP関数で「元データ」から引用する計算式は全て同じ計算式となっています（*印 の項目の計算式は1ヶ所の列名が相対参照によって変わっていますが、計算式をコピーすれば自動的に変更されます）。計算式が【 =IF($A3="","",…) 】で囲われているのは、もしA列に工事コードが入っていない場合にエラー表示されないようにするためです。【 MATCH(B$2,元データ!$1:$1,0),FALSE) 】の計算式は、「元データ」から引用したい項目が「元データ」の何列目にあるのかを求めて、VLOOKUP関数の引数としています。土建区分（名）と決算期は、「基本データ」シート上に作成したテーブルから値を求めています。

（列）項目名	計算式	書式
（B列）工事名*	=IF($A3="","",VLOOKUP($A3,元データ!$A:$H,MATCH(B$2,元データ!$1:$1,0),FALSE))
（C列）土建区分	=IF($A3="","",VLOOKUP(VLOOKUP($A3,元データ!$A:$H,MATCH(C$2,元データ!$1:$1,0),FALSE),$O:$P,2,FALSE))
（D列）工期始*	=IF($A3="","",VLOOKUP($A3,元データ!$A:$H,MATCH(D$2,元データ!$1:$1,0),FALSE))	yyyy/mm/dd
（E列）工期終*	=IF($A3="","",VLOOKUP($A3,元データ!$A:$H,MATCH(E$2,元データ!$1:$1,0),FALSE))	yyyy/mm/dd
（F列）工事期間	=IF($A3="","",DAYS(E3,D3)+1)	#,##0"日"
（G列）決算期	=IF($A3="","",VLOOKUP(E3,$R:$S,2,TRUE))
（H列）受注高*	=IF($A3="","",VLOOKUP($A3,元データ!$A:$H,MATCH(H$2,元データ!$1:$1,0),FALSE))	#,##0;[赤]-#,##0
（I列）施工高*	=IF($A3="","",VLOOKUP($A3,元データ!$A:$H,MATCH(I$2,元データ!$1:$1,0),FALSE))	#,##0;[赤]-#,##0
（J列）進捗率	=IF($A3="","",IF(H3=0,"",I3/H3))	0.0%;[赤]-0.0%
（K列）取下高*	=IF($A3="","",VLOOKUP($A3,元データ!$A:$H,MATCH(K$2,元データ!$1:$1,0),FALSE))	#,##0;[赤]-#,##0
（L列）取下率	=IF($A3="","",IF(H3=0,"",K3/H3))	0.0%;[赤]-0.0%
（M列）重要工事	=IF(C3="土木",IF(H3>=3000,"重要工事",""),IF(C3="建築",IF(H3>=5000,"重要工事",""),""))

〇 DSUM関数について

• データベース用の合計関数として、条件に合致するものだけを抽出して合計を求めてくれる DSUM関数 があります。条件を計算式に組み込むことはできず、条件欄をシート上にあらかじめ作成しておかなければいけないという不便さはありますが、AND条件とOR条件を同時に設定できるという特長があります。SUMIFS関数が登場するまでは便利な関数でしたが、現在は必要性が薄れているかと思います。
• 「加工データ」のシート上に、次の通り、DSUM関数で重要工事の受注金額を抽出して合計する欄を設けてみました。セルO2:P4が抽出条件欄で、横方向にAND条件、縦方向にOR条件となります。抽出結果を求めるセルO7の計算式は【 =DSUM(A2:M999,H2,O2:P4) 】となります。
  
• ちなみに、この計算をSUMIFS関数で表すと【 =SUMIFS(H3:H999,C3:C999,"土木",H3:H999,">=3000")+SUMIFS(H3:H999,C3:C999,"建築",H3:H999,">=5000") 】となり、抽出条件欄なしで1本の計算式で求めることができます（SUMIFS関数はAND条件しか指定できませんので、2つの計算式を＋記号で結ぶことでOR条件も実現しています）。