データベース連携：データベースからExcelシートにQRコードを生成

　「楽々QRマスター」と「楽々QRセルズ」はExcelの「外部データ取り込み」機能を利用してAccessデータベースからデータを取り込み、ＥｘｃｅｌシートにQRコードを作成することができます。データベースはAccess以外にSQLServer、OracleやPostgresなどExcelの「外部データ取り込み」が利用できるデータベース（ODBCインターフェースをサポート）であれば可能です※１。Excelの「外部データ取り込み機能」により「楽々QRマスター」と「楽々QRセルズ」の利用範囲が大きく広がります※２。

なお、Accessで直接QRコードを作成したい場合はこちらをご覧ください。→「楽々QRマスター」Access対応

---

※１	データベースは「Oracle」、「SQLServer」、「Postgres」などODBCインターフェースを備えたものであれば自社開発・ベンダー商品を問わず可能です。
※２	ご購入又はご購入予定のお客様には、データベースの諸情報[データベース名、接続ID・パスワード（ReadOnly)、フィールド名]を教えて頂ければデータベースと連携したExcelファイルをご提供致します。※３
※３	ご相談と簡単なもののご提供は無償、複雑なものは有償になります。お気軽にご相談ください。→こちら

サンプルデータベースと帳票のダウンロード

本ページではAccessで作成した販売管理データベースのサンプルを使い活用事例を紹介します。
ダウンロード：AccessSample.zip（Zip形式）：>2012年3月13日

上記ファイルを ダウンロードし解凍すると、以下のAccessで作成した販売管理データベース「販売管理.mdb」とExcelファイル「納品伝票.xls」が得られます 。「販売管理.mdb」はフォルダ「AccessDB」の下に存在します。

ファイル	説明	保管する場所
販売管理.mdb	Accessデータベースファイル	C:\AccessDB
納品伝票.xls	Excelファイル	任意（デスクトップなど）

動作させるには、これらのファイルを上記の保管する場所にコピーします。「AccessDB」フォルダはそのままCドライブ直下にコピーしてください。「納品伝票.xls」は既にAccessDB「販売管理.mdb」から外部データ取り込みをするよう設定しており、「納品伝票.xls」起動すると「販売管理.mdb」にリンクした状態で機動します。

販売管理.mdbの説明

サンプルの「販売管理.mdb」は次の３つのテーブルと１つのクエリーから構成されます。

	・	顧客テーブル	：	顧客情報を入れる
	・	商品テーブル	：	商品情報を入れる
	・	受注テーブル	：	受注情報を入れる
	・	受注クエリ	：	顧客名、商品名などを展開した受注情報の参照

各テーブルとクエリーの関係

各テーブルとクエリーの関係は下図のようになります。「受注クエリ」は「受注テーブル」を参照し、「顧客テーブル」から顧客名、「商品テーブル」から商品名や単価を参照します。

顧客テーブル

顧客テーブルのサンプルを以下に示します。

商品テーブル

商品テーブルのサンプルを以下に示します。

受注テーブル

受注テーブルのサンプルを以下に示します。

受注クエリ

受注クエリのサンプルを以下に示します。

納品伝票.xlsの説明

「納品伝票.xls」の「データ」シートにはAccessデータベースファイル「販売管理.mdb」の「受注クエリ」のデータをExcelの「外部データ取り込み」機能により下図に示す様に取り込んでいます。

「納品伝票.xls」の「データ」シート

「データ」シート上でExcelのオートフィルタにより受注日と受注コード指定し、受注コードを抽出すると、下図の様に受注コードﾞ「12022301」を抽出した状態になります。

「納品伝票.xls」のQRコード付の「伝票」シート

受注コードを抽出した「データ」シートの状態で、「伝票」シートを開くと受注コードﾞ「12022301」に対応した納品伝票が自動的に作成されます。各セルには伝票を自動的に作成する数式が置かれています。また、商品コードと数量をQRコードに変換する楽々QRセルズの「QRCode」関数が組み込まれています。


各セルに組み込まれている数式は以下の表に説明します。ここに示す手法は一般的であり、種々の業務に使用する伝票に応用できます。特に１０の必要な行数だけQRコードに変換する方法にご注目ください。

「伝票」シート状の各セルの数式の説明
ｾﾙ	数式	説明
１	=SUBTOTAL(4,データ!A:A)	｢ﾃﾞｰﾀ｣ｼｰﾄより抽出した｢受注ｺｰﾄﾞ｣をE2ｾﾙにｾｯﾄする。 SUBTOTAL関数は最大値の指定(4)とする（最小値でも可）。
２	=MATCH($E$2,データ!$A$2:A$32761,0)	E2ｾﾙにある｢受注ｺｰﾄﾞ｣を｢ﾃﾞｰﾀ｣ｼｰﾄの領域A2:A32767より検索し、見つかったとき行の相対値（行番号-1)をｾﾙF10にｾｯﾄする。
３	=IF(ISERROR($F10),"",OFFSET(データ!$A$1,$F10,6))	ｾﾙF10がｴﾗｰではないとき｢ﾃﾞｰﾀ｣ｼｰﾄA1ｾﾙからの相対行数(F10ｾﾙの値)と相対列数6の位置のｾﾙの値（商品名)をｾｯﾄする。
４	=IF(ISERROR($F10),"",OFFSET(データ!$A$1,$F10,5))	３と同様に相対列数5の位置のｾﾙの値(商品ｺｰﾄﾞ)をｾｯﾄする。
５	=IF(OR(D10="",C10=""),"",D10*C10)	D10ｾﾙとC10ｾﾙが空白でないとき、両者を掛けた値をｾｯﾄする。
６	=MATCH($E$2,INDIRECT(CONCATENATE("データ!", ADDRESS($F10+2,1),":$A$32767")),0)+$F10	｢ﾃﾞｰﾀ｣ｼｰﾄでF10ｾﾙの値から検索始行（次の行）を求め、32767行までの領域を生成し同領域で｢受注ｺｰﾄﾞ｣を検索し、見つかったとき行の相対値にｾﾙF10の値を加わえた値（行番号-1)をｾﾙF11にｾｯﾄする。
７	=IF(ISERROR($F11),"",OFFSET(データ!$A$1,$F11,6))	３と同様
８	=MATCH($E$2,INDIRECT(CONCATENATE("データ!", ADDRESS($F16+2,1),":$A$32767")),0)+$F16	６と同様であるが、「受注ｺｰﾄﾞ｣検索して存在しなかった為、エラーとなり｢#N/A｣となる。
９	=IF(ISERROR($F17),"",OFFSET(データ!$A$1,$F17,6))	８がエラー｢#N/A｣の為空白になる。
10	=QRCode(2,0,2,1,1,$E$2,INDIRECT(CONCATENATE( "$B$10:",ADDRESS(COUNT($C$10:$C$29)+9,3))))	QRｺｰﾄﾞに変換する領域の右下隅のｾﾙｱﾄﾞﾚｽを「COUNT」関数を使って求め、左上隅のｾﾙｱﾄﾞﾚｽB10と結合して領域を示す文字列を作り、「INDIRECT｣関数によりQRCode関数の引数とします。この数式により必要な行数だけQRコードに変換できます。
11	=SUM(E10:E29)	合計金額計算
12	=0.05*E30	消費税計算
13	=E30+E31	消費税込合計金額
14	=CONCATENATE(IF(ISERROR($F$10),"", OFFSET(データ!$A$1,$F$10,4)),"　様")	ﾃﾞｰﾀ｣ｼｰﾄA1ｾﾙからの相対行数(F10ｾﾙの値)と相対列数6の位置のｾﾙの値（顧客名)をｾｯﾄし文字列"　様"と結合する。
15	−	カレンダーコントロール
16	−	カレンダーコントロールの選択値がセットされる。

納品伝票.xlsの印刷イメージ

納品伝票.xlsを印刷すると以下の様にQRコード付き伝票が作成されます。

ExcelにAccessデータベースからデータ取り込みをする方法

ダウンロードしたサンプルファイルを使ってExcelからAccessデータベースのデータ取り込み方法を以下に説明します。業務で作成したAccessデータベースへの応用が可能です。

Accessデータベースからのデータ取り込み方法

Excelを起動し新しいBookを作成しSheet1を開きメニューの「データ」→「外部データの取り込み」→「データの取り込み」を選択します。


「データファイルの選択」画面が表示されるので、先ほど保存した「販売管理.mdb」を選択し「開く」ボタンを押します。


「表の選択」画面が表示されるので「受注クエリ」を選択し「OK」ボタンを押します。


「データのインポート」画面が表示されるので、データを返す先を「既存のワークシート」にしてセルを「$A$1」を指定します。


Sheet１にAccessデータベース「販売管理.mdb」の「受注クエリ」が読み込まれます。


次にSheet1のシート名を「データ」に変更し、Bookを一旦保存します。ここでは「納品伝票１.xls」としています。

ここで「データ」シートにオートフィルタを作成します。オートフィルタを作成することにより「データ」シートで必要な受注コードのデータを抽出ができるようになり、「伝票」シートに受注コードに対応した伝票が作成できるようになります。

下図のように「納品伝票1.xls」の「データ」シート上でメニューの「データ」→「フィルタ」→「オートフィルタ」を選択します。


すると、下図の用に「データ」シートの１行目の項目名に「▼」マークが表示されます。


このオートフィルターを使って特定受注コードが抽出できます。まず、「受注日」を選択して受注日を絞り込み、次に「受注コード」を選択して受注コードを抽出します。


以上でExcelでAccessデータベースからデータ取り込みの説明を終了します。

「伝票」シートの作成

次に、ダウンロードした「納品伝票.xls」を使って「納品伝票１．ｘｌｓ」に「伝票」シートを作成する方法を説明します。まず、保存してある「納品伝票.xls」を同じExcel上で開きます。


次にメニューの「編集」→「シートの移動またはコピー」で「コピー」をチェックし、「移動先のブック名」を「納品伝票1.xls」にします。


「納品伝票1.xls」に「納品伝票.xls」の「伝票」シートがコピーされます。しかし、このままでは「納品伝票1.xls」の「伝票」シートの参照先が「納品伝票.xls」のままなので自身のBookに変える必要があります。それにはメニューの「編集」→「リンクの設定」を選択し、下記画面を表示し「リンク元の変更」ボタンを押します。


「リンク元の変更」画面が表示されるので、「納品伝票１.xls」を選択します。


以上の操作でAccessデータベースからExcelにデータを取り込み、それを元にした伝票の作成ができます。Accessデータベースを更新したときExcelの「データ」シートも合わせて更新するには「データ」シートを開きメニューの「データ」→「データの更新」を選択します。


以上でAccessデータベースにExcelファイルをリンクする方法の説明は終了です。以上を参考にして業務に応用してください。