Excel と Access の間でデータを交換 (コピー、インポート、エクスポート)

Excel の使用は広く普及しており、Excel は多くの場合、データ入力、分析、および操作のための頼りになる Office アプリケーションです。 Microsoft Access は、コンパクトなデスクトップ環境でリレーショナル データベース機能を提供します。データを処理するための多くの機能があり、インターフェイスがユーザー フレンドリーであるにもかかわらず、Access を使用する人はほとんどいません。 Access には、テーブルの作成、各テーブルのデータ型の指定と制限、単純なクエリと高度なクエリの作成、SQL 操作、ユーザー入力用のフォーム作成など、リレーショナル データベース アプリケーションのすべての機能があります。 Excel は、より使い慣れたインターフェイスであることに加えて、高度なグラフ作成機能と、拡張されたピボット テーブル機能とオプションを備えています。

Excel と Access の統合により、Access のリレーショナル データベース インターフェイスおよび環境と組み合わせて、Excel のデータ入力の容易さと高度なグラフ作成機能を利用できます。

2 つの Office アプリケーション間でデータを交換するための便利なヒントを次に示します。

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

前述のように、Access はリレーショナル データベースです。これは、Access データベース内のテーブルがすべて相互に関連していることを意味します。 Access データベースの各レコードは、一意に識別できる必要があります。これは、主キーを使用して実現されます。通常、この主キーは 1 つのフィールドで構成されますが、場合によっては、複数のフィールドの組み合わせになることもあります。この主キーは必須であり、Access はテーブルの主キーとして機能する ID 列を自動的に追加します。 Excel では、テーブルを作成するときに主キーは必須要件ではないため、ユーザーはしばしば省略します。

この状況を以下に示します。最初の画像は、データシート ビューとデザイン ビューの両方の Access テーブルと、自動的に追加される主キーを示しています。 2 番目の画像は、Excel テーブルと、データが Excel に入力される可能性が最も高い方法を示しています。

Excel テーブルのデータ入力は間違っていません。ただし、Excel をデータ入力ソフトウェアとして使用し、後で表を Access にインポートする予定がある場合 (これは、Access の使いやすいフォームとして Excel を使用することに似ています)、最初からそれが最善です。 Excel でスプレッドシートを設定するときに、Excel テーブルに主キーを追加します。

これは、ユーザーが主キーの概念に慣れ親しんでいることを確認し、早い段階でデータの冗長性と重複を可能な限り排除するためです。これは、Excel でテーブルを作成するときに Excel で行うのに十分簡単です。主キー列がまだ含まれていない比較的小さなテーブルがあり、Excel テーブルに重複や冗長性がない場合は、この列を簡単に追加できます。テーブルの最初の列のセルを右クリックし、[挿入]、[テーブルの列を左に] を選択するだけです。次に、この列の名前を変更し、ID と呼びます。この列にデータを入力するには、列の最初のセルを選択して 1 を入力し、列の 2 番目のセルに移動して 2 を入力します。次に、上の 2 つのセルを強調表示し、オートフィルを使用して下にドラッグして、列の残りの部分を入力します。 .

また、覚えておくと便利なヒントとして、Access テーブルのフィールド名は Excel テーブルの列見出しと同じです。

表のデータを Access からコピーして、Excel の新しいシートに貼り付けることもできます。これは、少量のデータを含む Access テーブルに適しています。コピーする Access のすべての行を選択し、[ホーム] タブに移動し、[クリップボード] グループで [コピー] を選択するか、CTRL-C を押します

次に、Excel ワークブックの新しいシートで CTRL-V を押します。 キーボードの をクリックして、Access テーブルを Excel シートに貼り付けます。 Access テーブルは見出し付きの範囲として貼り付けられ、範囲内のセルを選択して CTRL-T を押すと、Excel テーブルに変換できます。 キーボードで。範囲を Excel テーブルに変換するために、[テーブルにヘッダーがある] チェックボックスがオンになっていることを確認し、[OK] をクリックします。

以上のポイントから進めます。 Access では、情報は主要なエンティティまたはサブジェクトに分割されます。たとえば、小さな店舗用に設計された Access データベースには、Products、Orders、Customers、および Employees という個別のテーブルが含まれている可能性が高くなります (リレーションシップが指定されています)。 Excel では、以下に示すように、Access では別々のテーブルに格納されていた情報が 1 つの Excel テーブルに結合されることは珍しくありません。

前述のとおり、データを Excel テーブルに入力する方法は間違っていません。ただし、Excel と Access を統合する場合、Excel に関して言えば、Excel はデータ入力ソフトウェアであり、Access はクエリとデータベース機能を提供します。 1 つの大きなテーブルに格納されている Excel の情報を 4 つの小さなテーブルに分割し、以下に示すように各テーブルを個別のシートに配置することをお勧めします。また、混乱を避けて一貫性を保つために、Access のテーブルのフィールドの名前を Excel のテーブルの列の見出しと一致させることをお勧めします。

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

多くの場合、多数のデータ行を含む Excel テーブルでは、一意の値を一目で確認するのは困難です。 Excel ワークブックから収集した情報を使用して Access で効率的なテーブルを設計するために、100 行のデータを含む Excel テーブルの一意の製品の数を確認したいとします。この例の Excel ブックは、架空のオンライン小売業者のデータに基づいています。 Excel テーブルには、以下に示すように、製品が販売された日付、製品の名前、および販売された数量が含まれています。

まず、ワークブック内のテーブルを含むスプレッドシートのコピーを作成して、元のデータをそのまま保持し、コピーで作業します。 Excel テーブルのセルを選択した状態で、状況依存のテーブル ツール オプションを有効にするには、[デザイン] タブを選択し、[ツール] グループで、以下に示すように [重複を削除] オプションを選択します。

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

得られた Excel のテーブルを使用して、オンライン小売業者が提案した Access データベースの Access テーブルの情報を収集できます。

続きを読む:Excel で一意の値を別のワークシートにコピーする方法 (5 つの方法)

3) TRIM Function() を使用して不要なスペースを削除し、アンダースコアを使用して Access のフィールド名と Excel の列見出しの間の一貫性を維持する

Access フィールド名は先頭のスペースで始めることはできないため、Excel のテーブルの列見出しの名前で TRIM function() を使用して、スペースを削除できます。

また、最終的に SQL Server や Oracle などの別のデータベースにデータをインポートする必要があるとしましょう。したがって、統合ソリューションの流れは、Excel でデータを入力し、次にこのデータを Access にエクスポートし、次に Access テーブルを SQL Server や Oracle などのエンタープライズ レベルのデータベースにインポートすることになります。他のデータベースは、フィールド名の間のスペースをうまく読み取りません。そのため、Excel テーブルの見出し列の名前を呼び出す代わりに、Name of Product を Name_of_Product と呼びます。アンダースコアを使用して、他のデータベースへの統合を容易にします。また、SQL は、スペースを含む名前よりも、このテーブルの命名スタイルをより適切に処理します。

Access でキャプション プロパティを使用できるため、テーブルのフィールド名が 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] を選択します。

Get External Data – Excel Spreadsheet Wizard が表示されます。データを含む Excel ワークブックに移動します。 [リンク テーブルを作成してデータ ソースにリンク] オプションを選択します。ユーザーに Excel でデータを入力してもらいたいので、これにより Excel でのデータ入力が可能になります。 Excel でこのデータに加えられた変更はすべて Access に反映されますが、Access で編集することはできません。データシート ビューでのデータ入力に関しては、Access ではほとんどすぐに使用できます。 [OK] をクリックします。

表が Excel で正しく設定されている場合、Access は、以下に示すように、最初の行に列見出しが含まれていることを自動的に検出して確認します。

[次へ] をクリックすると、プロセスの最終ステップに進み、リンク テーブル名を入力して [完了] をクリックするよう求められます。

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

これにより、Access 環境で使用できる追加の SQL 機能と、複雑なクエリを作成するための式ビルダーに Excel データが公開されます。複雑なクエリの結果は、後で Excel ダッシュボードで使用できます。 Excel のデータ入力テーブルと Access データベースとリンクされたテーブルを同じフォルダーまたはディレクトリに保持することは推奨されますが、必須ではありません。

• シナリオ 2 では、空の Access データベースまたは入力済みの Access データベースがあり、Excel テーブルをインポートしたいが、それらをリンクしたくない場合です。アクセスでそれらを編集したい。このようにして、後で Excel シートに加えられた変更は Access に反映されず、データは完全に Access に保存され、編集およびクエリが実行されます。これは通常、テーブルに入力されたデータの種類を厳密に管理する必要がある場合に選択されるオプションです。

再度、Access データベースを開いた状態で、[外部データ] タブに移動し、[インポートとリンク] グループで、表示されているように [Excel] を選択します。

この場合、ウィザードが表示されたら、ソース データを現在のデータベースの新しいテーブルにインポートすることを選択して、元の Excel テーブルとのリンクが解除されたテーブルのコピーを Access に作成します。 [OK] をクリックします。

次の画面で [次へ] をクリックします。

次に、以下に示すように、インポートするフィールドに関する情報を指定します。ウィザードでは、スキップするフィールドを選択することもできます。多くの場合、Excel テーブルには実際には Access の他のテーブルに必要なデータが含まれている可能性があるため、これらの列を現在のテーブルにインポートすることを避けることができます。

次に、Access に主キーを追加させるか、独自の主キーを選択させることができます。この場合、テーブルには既に Excel で指定された ID 列があるため、2 番目のオプションが選択されました。

[次へ] をクリックします。

次に、Access でテーブルの名前を選択し、[完了] をクリックします。

インポート手順を保存するかどうか尋ねられますが、今は [閉じる] をクリックしてください。 The table is now in Access for further editing, entry, and manipulation and is unlinked to the Excel workbook.

5) Using Table Analyzer to normalize imported data and split the data into separate tables in Access

Later versions of Access, have the Table Analyzer tool, which helps users separate one table into many tables. This is especially useful if one did import the data from a big Excel table and one is struggling to separate the tables. One can launch this wizard as part of the import process and check the box, I would like a wizard to analyze my table after importing the data as shown below.

Or one can access it, if the tables are already imported as standalone Access tables, by going to Database Tools, in the Analyze group, choose Analyze Table.

The Table Analyzer wizard should be launched, which will guide you through the steps of creating separate tables.

Table Analyzer, however, does not always guess right, so use it with caution.

6) Importing data from Access into Excel and using VLOOKUP to link the data and populate columns

One can import Access tables into Excel worksheets. With an Excel workbook opened, go to the Data tab, in the Get External Data group, choose From Access as shown below.

Navigate to the Access database containing the tables, you want to import into your Excel spreadsheet.

Select the table one wants to import, in this case, the ProductT.

Click Ok.

Choose to Import the data as a Table, on the existing worksheet, choose cell A1, and then Click Ok.

The table is imported as an Excel table.

The same was done for the three other tables in the database, namely Orders, Employees, and Customers tables.

One now has an Orders table which looks like the one below.

This table with numbers in Customer_ID, Product_ID, and Employee_ID can be counter-intuitive and confusing for Excel users not used to the relational database theory and tables. Thus one could add three helper columns to the table called Actual_Customer, Actual_Product, and Actual_Employee as shown below.

In Cell F2, the following formula was used:

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

Where, [@[Customer_ID]] refers to cell C2, which contains the lookup value, Table_TablesToBeImportedIntoExcel.accdb5 refers to the imported Customers table on the Customers sheet, (Excel gives this table a default name), highlight the data, but not the column headings, the same way one would for a normal VLOOKUP, the customer name is in column 2, and the type of lookup is an exact match.

Double click and send the formula down to populate the Actual_Customer column.

Do the same thing to populate the Actual_Product and Actual_Employee columns. So one can quickly repopulate Excel tables with actual text values as needed, using the VLOOKUP functionality in order to orientate the Excel users.

7) Using data already set up in a relational Access database, for Power Pivot manipulation

Power Pivot is a Business Intelligence tool available with Excel 2013 and later versions of Excel. It also requires data to be input in a relational manner. So, what you can do when you are importing Access tables into Excel (already normalized and designed using relational data logic), is to add the imported tables to the data model.

In a blank workbook, go to Data, get External data and choose from Access as for the case above. However, this time when asked to select a table, check the Enable selection of multiple tables option in the Select Table dialog box and select all the tables as shown below:

Click Ok.

Make sure the PivotTable Report option is selected, the Add this data to the Data Model should be checked because we imported multiple tables. If you only import one table at a time, you will need to check this option yourself. If you click on the drop-down arrow next to Properties, you should see that the relationships between the tables will also be imported.

Click Ok to create the Pivot TableReport, with the tables already added to the Data Model. The relationships should be visible in the PowerPivot for Excel Window, Diagram view. Thus the data is ready for further manipulation using Power Pivot functionality.

Download Files

Exchange-Data-Between-Access-and-Excel

結論

Excel and Access integration allow one tallow advantage of the combination of features in Excel and Access. The key to integrating Excel and Access successfully is understanding a little bit about relational database design basics.

Understanding a little bit of relational design and table structure will also set you in good stead with respect to taking advantage of Power Pivot and related Business Intelligence tools in the Excel environment.

Please feel free to comment and tell us if you integrate Excel and Access, whether or not you use Power Pivot, and any other tips for integrating Excel and Access, that you may have.

関連記事

• Excel VBA:Copy Cell Value and Paste to Another Cell
• Excel VBA:Copy Range to Another Workbook
• [Fixed]:Right Click Copy and Paste Not Working in Excel (11 Solutions)
• Difference Between Paste and Paste Special in Excel
• How to Copy Alternate Rows in Excel (4 Ways)
• How to Apply VBA PasteSpecial and Keep Source Formatting in Excel