Bird
楽々QRマスター・楽々QRセルズ
 ホーム楽々QRセルズ→Access連携


ホーム
楽々QRセルズ
詳しい説明
Access連携
お試し版の入手
サンプル帳票
楽々QRマスター
お試し版の入手
Access対応
Word対応
Excel対応
QRコード付帳票配布
QRライブラリ
楽々QRサーバ
楽々QRチェッカー
楽々シール
楽々QR伝票入力

活用事例
Q&A
お問い合わせ/ご購入
伝票入力自動化
会社情報

Access連携

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

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


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

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

本ページでは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」は次の3つのテーブルと1つのクエリーから構成されます。

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

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

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

顧客テーブル

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

商品テーブル

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

受注テーブル

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

受注クエリ

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

納品伝票.xlsの説明

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

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


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

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

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


各セルに組み込まれている数式は以下の表に説明します。ここに示す手法は一般的であり、種々の業務に使用する伝票に応用できます。特に10必要な行数だけ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」を指定します。


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


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

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

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


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


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


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

「伝票」シートの作成

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


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


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


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


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


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