ExcelとAccess間でデータを交換する方法まとめ(コピー・インポート・エクスポート)

Excelは利用者が非常に多く、データ入力・分析・加工の定番Officeアプリケーションとして広く使われています。一方、Microsoft Accessはコンパクトなデスクトップ環境でリレーショナルデータベース機能を提供するツールです。データ処理に便利な機能が豊富で操作性にも優れているにもかかわらず、Accessの利用者はExcelに比べて少ないのが実情です。Accessには、テーブル作成、各テーブルのデータ型の指定や制限、シンプルなクエリから高度なクエリの作成、SQLによる操作、ユーザー入力用フォームの作成など、リレーショナルデータベースアプリケーションとして必要な機能がすべて備わっています。その一方でExcelは、馴染みやすいインターフェースに加え、高度なグラフ作成機能や拡張されたピボットテーブル機能が強みです。

ExcelとAccessを統合的に活用すれば、Excelの手軽なデータ入力と高度なグラフ機能と、Accessのリレーショナルデータベース環境という両者の利点を掛け合わせることができます。

ここでは、2つのOfficeアプリケーション間でデータをやり取りするための実用的なヒントを紹介します。

1) Accessのテーブル構造と作成ロジックを理解する

前述のとおり、Accessはリレーショナルデータベースです。つまり、Accessデータベース内のテーブル同士はすべて互いに関連付けられています。Accessデータベースの各レコードは一意に識別できる必要があり、それを実現するのが「主キー」です。主キーは通常1つのフィールドで構成されますが、場合によっては複数フィールドの組み合わせになることもあります。この主キーは必須項目であり、Accessでは自動的に「ID」列が追加され、それがテーブルの主キーとして機能します。一方、Excelでテーブルを作成する際に主キーは必須ではないため、省略されることがよくあります。

下の画像をご覧ください。1枚目はAccessのテーブルをデータシートビューとデザインビューで表示したもので、自動的に追加された主キーが確認できます。2枚目はExcelのテーブルで、Excelで一般的に入力されるデータの形を示しています。

Excelでの入力形式自体は間違いではありません。しかし、Excelをデータ入力ソフトとして使い、後からそのテーブルをAccessへインポートする運用を想定しているなら(いわばExcelをAccessの使いやすい入力フォームとして使うイメージ)、最初の段階でExcelのテーブルにも主キーを設けておくのがベストです。

これにより、主キーの概念に早めに慣れることができ、データの冗長性や重複も早期に排除できます。Excelでテーブルを作成する際に主キー列を追加するのは簡単です。比較的小さなテーブルで主キー列がまだなく、重複や冗長データがない場合は、次の手順で簡単に追加できます。テーブルの先頭列のセルを右クリックし、「挿入」→「テーブルの左側に列を挿入」を選択します。その列に「ID」という名前を付けてください。値を入力するには、列の最初のセルに「1」、2番目のセルに「2」と入力し、その2つのセルを選択してオートフィルで下方向にドラッグすれば、残りの列も自動的に入力されます。

また、覚えておくと便利なポイントとして、Accessテーブルの「フィールド名」は、Excelテーブルの「列見出し」に相当します。

さらに、Accessテーブルのデータをコピーして、Excelの新しいシートに貼り付けることも可能です。これはデータ量の少ないAccessテーブルに適した方法です。コピーしたい行をAccessですべて選択し、「ホーム」タブのクリップボードグループにある「コピー」をクリックするか、キーボードのCtrl+Cを押します。

次に、Excelブックの新しいシート上でCtrl+Vを押すと、Accessテーブルが見出し付きの範囲として貼り付きます。範囲内のセルを選択してCtrl+Tを押せば、Excelテーブルに変換できます。「先頭行をテーブルの見出しとして使用する」チェックボックスにチェックを入れ、「OK」をクリックしてください。

ここまでの内容を踏まえると、Accessでは情報が主要なエンティティ(主体)ごとに分割されています。例えば、小規模な店舗向けに設計されたAccessデータベースには、「商品」「受注」「顧客」「従業員」など、それぞれ独立したテーブルが作成され、リレーションシップが設定されているのが一般的です。ところがExcelでは、Accessなら別々のテーブルに格納されるはずの情報が、1つの大きなテーブルにまとめられているケースがよく見られます(下図参照)。

この入力形式自体は誤りではありません。しかし、Excelをデータ入力担当とし、Accessがクエリやデータベース機能を担う形で両者を統合するのであれば、Excelの1つの巨大なテーブルを4つの小さなテーブルに分割し、それぞれを別々のシートに配置するのがおすすめです(下図参照)。あわせて、混乱を避けて一貫性を保つため、Accessテーブルのフィールド名とExcelテーブルの列見出しの名前を揃えておくことも推奨します。

2) Excelで重複を削除し、その後のテーブル設計やAccessへの統合をスムーズにする

大量の行データを含むExcelテーブルでは、一意の値を一目で把握するのは困難です。例えば、100行のデータを含むExcelテーブルから「一意の商品数」を確認したいとしましょう。これは、Excelブックから得た情報をもとに、Accessで効率的なテーブルを設計するために必要な作業です。この例のExcelブックは、架空のオンライン小売店のデータに基づいています。テーブルには商品の販売日、商品名、販売数量が含まれています(下図参照)。

まず、元データを保持しておくために、対象テーブルを含むシートのコピーを作成し、そのコピーに対して作業を行います。Excelテーブル内のセルを選択してテーブルツールのコンテキストメニューを表示させたら、「デザイン」タブの「ツール」グループにある「重複の削除」を選択します(下図参照)。

「重複の削除」ダイアログボックスが表示されたら、「販売日」と「数量」のチェックを外して「OK」をクリックします。削除された重複値の件数を知らせるメッセージボックスが表示されるので、「OK」をクリックします。

こうして得られたExcelのテーブルは、オンライン小売店向けAccessデータベースのテーブル設計に必要な情報を抽出する際に役立ちます。

関連記事: Excelで一意の値を別のワークシートにコピーする方法(5つの手法)

3) TRIM関数で不要なスペースを除去し、アンダースコアでAccessのフィールド名とExcelの列見出しの一貫性を保つ

Accessのフィールド名は先頭にスペースを付けることができません。そこで、Excelテーブルの列見出し名に対してTRIM関数を使えば、余分なスペースを除去できます。

また、最終的にSQL ServerやOracleなどの他のデータベースへデータをインポートする必要が出てくることも想定しましょう。その場合、統合ソリューションの流れは「Excelでデータ入力 → Accessへエクスポート → SQL ServerやOracleなどのエンタープライズ向けデータベースへAccessテーブルをインポート」となります。多くのデータベースは、フィールド名に含まれるスペースをうまく扱えません。そのため、Excelテーブルの列見出しを「Name of Product」ではなく「Name_of_Product」のようにアンダースコア区切りで命名しておくと、後続のデータベースへの統合が容易になります。SQLも、スペース入りの名前よりもこの命名スタイルの方がはるかに扱いやすくなります。

Accessでは「標題(Caption)プロパティ」を活用できます。例えば、テーブルのフィールド名が「Name_of_Product」であれば、デザインビューで標題(ラベル)として「Name of Product」と入力します。そうすると、データシートビューやフォーム上では「Name of Product」が表示され、内部のフィールド名にはアンダースコアが使われたままになります。これは主に、Accessデータベースのフロントエンドを見るユーザーのための見栄え上の配慮です。

類似の記事

• 実行時エラー1004:RangeクラスのPasteSpecialメソッドが失敗する原因と対処法
• Excelで値をコピー&ペーストする数式(5つの例)
• Excelでリンク貼り付けと行列を入れ替えて貼り付ける方法(8つの手順)
• 結合セルがある場合のExcelでのコピー&ペースト(2つの方法)
• VBAで見出しを除いた可視セルのみをコピーする方法

4) ExcelからAccessへのデータインポート – 2つの主要シナリオへの対応

• シナリオ1: 完全に空のAccessデータベースを用意し、Excelからデータをインポートしたいケースです(Excel側のテーブルにはIDが設定され、冗長性や重複は可能な限り排除済みとします)。Excelをデータ入力プログラム兼フォームとして使う運用です。Accessデータベースを開き、「外部データ」タブの「インポートとリンク」グループから「Excel」を選択します。

「外部データの取り込み – Excelスプレッドシート」ウィザードが表示されるので、データを含むExcelブックを指定します。そして「リンクテーブルを作成して、ソースデータにリンクする」オプションを選択します。これにより、ユーザーは引き続きExcelでデータ入力ができます。Excel側で行われた変更はAccessに反映されますが、Access側では編集できません。データシートビューでのデータ入力に関しては、Access側はほぼ読み取り専用となります。「OK」をクリックします。

Excel側でテーブルが正しく設定されていれば、Accessが自動的に検出し、下図のように「1行目を列見出しとして使用する」にチェックが入った状態で表示されます。

「次へ」をクリックすると最終ステップに進みます。リンクテーブル名を入力して「完了」をクリックします。

テーブルがリンクされたことを知らせるメッセージが表示されます。

これにより、ExcelのデータがAccess環境で利用可能なSQL機能や、複雑なクエリ作成のための式ビルダーの恩恵を受けられるようになります。複雑なクエリの結果は、後からExcelのダッシュボードにも活用できます。管理のしやすさの観点から、Excelのデータ入力テーブルと、リンクテーブルを含むAccessデータベースは同じフォルダ(ディレクトリ)に置いておくことが推奨されます(必須ではありません)。

• シナリオ2: 空または既存データのあるAccessデータベースに、Excelテーブルをリンクなしでインポートしたいケースです。Access側で編集できるようにしたい場合です。この方式では、後からExcelシートで行われた変更はAccessに反映されず、データは完全にAccess側で管理・編集・クエリされます。テーブルに入力されるデータ型を厳密に管理したい場合に、通常このオプションが選択されます。

この場合も、Accessデータベースを開いて「外部データ」タブの「インポートとリンク」グループから「Excel」を選択します。

ウィザードが表示されたら、「現在のデータベースの新規テーブルにソース データをインポートする」を選択します。これにより、元のExcelテーブルとはリンクされていないテーブルのコピーがAccess内に作成されます。「OK」をクリックします。

次の画面で「次へ」をクリックします。

次に、下図のようにインポートするフィールドに関する情報を指定します。ウィザードでは、スキップするフィールドを選択することもできます。Excelテーブルには本来Accessの別テーブルに入れるべきデータが含まれていることが多いため、そのような列を現在のテーブルへのインポート対象から除外できます。

続いて、Accessに主キーを自動生成させるか、自分で主キーを選択するかを指定します。この例では、Excel側のテーブルにすでにID列が用意されていたため、2番目のオプションを選択しています。

「次へ」をクリックします。

Access内でのテーブル名を決めて「完了」をクリックします。

インポート操作を保存するかどうかを尋ねられるので、ここでは「閉じる」をクリックします。これでテーブルがAccessに取り込まれ、以降はAccess上で自由に編集・入力・加工ができ、Excelブックとはリンクされていない状態になります。

5) テーブル分析ツールを使ってインポートしたデータを正規化し、Access内で複数テーブルに分割する

新しいバージョンのAccessには「テーブル分析」ツールが搭載されており、1つのテーブルを複数のテーブルに分割する作業を支援します。大きなExcelテーブルからデータをインポートしたものの、テーブルの分離に苦労している場合に特に有用です。インポート処理の中でこのウィザードを起動することもできます。その場合は、下図のように「インポート後にテーブルの分析を行う」チェックボックスにチェックを入れます。

あるいは、テーブルがすでにスタンドアロンのAccessテーブルとしてインポート済みの場合は、「データベースツール」タブの「分析」グループにある「テーブルの分析」からアクセスできます。

テーブル分析ウィザードが起動し、個別テーブル作成の手順をガイドしてくれます。

ただし、テーブル分析ツールの判定が常に正しいとは限らないため、使用する際は注意が必要です。

6) AccessからExcelへデータをインポートし、VLOOKUPでデータを連結して列を補完する

AccessのテーブルをExcelワークシートにインポートすることもできます。Excelブックを開き、「データ」タブの「外部データの取得」グループから「Access」を選択します(下図参照)。

インポートしたいテーブルを含むAccessデータベースを指定します。

インポートするテーブルを選択します。ここでは「ProductT」テーブルを選びます。

「OK」をクリックします。

「テーブル」としてデータをインポートすることを選択し、「既存のワークシート」のセルA1を指定して「OK」をクリックします。

テーブルがExcelテーブルとしてインポートされます。

同じ手順で、データベース内の他の3つのテーブル(受注、従業員、顧客)もインポートしました。

すると、下のような受注テーブルが得られます。

Customer_ID、Product_ID、Employee_ID が数値だけのこのテーブルは、リレーショナルデータベースの理論やテーブルに慣れていないExcelユーザーにとっては直感的でなく、分かりにくいものです。そこで、下図のように「Actual_Customer」「Actual_Product」「Actual_Employee」という3つのヘルパー列をテーブルに追加します。

セルF2には次の数式を入力しました。

=VLOOKUP([@[Customer_ID]],Table_TablesToBeImportedIntoExcel.accdb5,2,FALSE)

ここで、[@[Customer_ID]] は検索値を含むセルC2を指します。Table_TablesToBeImportedIntoExcel.accdb5 は「Customers」シートにインポートした顧客テーブルを指します(Excelが自動的に付けたデフォルトのテーブル名です)。通常のVLOOKUPと同様に、列見出しではなくデータ部分のみを選択します。顧客名は2列目にあり、検索方法は完全一致(FALSE)です。

数式をダブルクリックして下方向にコピーし、Actual_Customer 列全体を入力します。

同じ要領で Actual_Product 列と Actual_Employee 列も入力します。これにより、VLOOKUP機能を使って、Excelユーザーにとって分かりやすいよう、実際のテキスト値で素早くテーブルを補完できます。

7) リレーショナルに構築済みのAccessデータベースのデータをPower Pivotで活用する

Power Pivotは、Excel 2013以降で利用できるビジネスインテリジェンス(BI)ツールです。Power Pivotでも、データはリレーショナルな形式で入力されている必要があります。そこで、AccessテーブルをExcelにインポートする際(すでに正規化され、リレーショナルなデータロジックで設計済み)、インポートしたテーブルをデータモデルに追加することができます。

空白のブックで、「データ」→「外部データの取得」→「Accessから」と、前述のケースと同様に操作します。ただし今回は、テーブル選択時に「Select Table」ダイアログボックスの「複数テーブルの選択を可能にする」オプションにチェックを入れ、すべてのテーブルを選択します(下図参照)。

「OK」をクリックします。

「ピボットテーブル レポート」が選択されていることを確認し、複数テーブルをインポートするため「このデータをデータモデルに追加する」にチェックが入っていることを確認します。1度に1つのテーブルしかインポートしない場合は、このオプションに自分でチェックを入れる必要があります。「プロパティ」の横のドロップダウン矢印をクリックすると、テーブル間のリレーションシップも一緒にインポートされることが確認できます。

「OK」をクリックすると、テーブルがデータモデルに追加された状態でピボットテーブルレポートが作成されます。リレーションシップは、Power Pivot for Excel ウィンドウのダイアグラムビューで確認できます。これで、Power Pivot機能を使ったさらなるデータ加工の準備が整いました。

ダウンロードファイル

Exchange-Data-Between-Access-and-Excel

まとめ

ExcelとAccessを統合することで、両アプリケーションの特徴的な機能を組み合わせたメリットを最大限に享受できます。ExcelとAccessの統合を成功させる鍵は、リレーショナルデータベース設計の基礎を少し理解しておくことです。

リレーショナル設計とテーブル構造の基本を理解しておけば、Excel環境におけるPower Pivotや関連するBIツールを活用する際にも大いに役立ちます。

皆さんがExcelとAccessを統合しているかどうか、Power Pivotを使用しているかどうか、また、ExcelとAccessの統合に関するその他のコツなどがあれば、ぜひコメントでお聞かせください。

関連記事

• Excel VBA:セルの値をコピーして別のセルに貼り付ける方法
• Excel VBA:範囲を別のブックにコピーする方法
• [解決済み]:Excelで右クリックのコピー&ペーストができないときの対処法(11の解決策)
• Excelの「貼り付け」と「形式を選択して貼り付け」の違い
• Excelで交互の行をコピーする方法(4通り)
• Excel VBAでPasteSpecialを使い、書式を保持したまま貼り付ける方法