注: Microsoft Access では、機密ラベルが適用されたExcel データのインポートはサポートされていません。 回避策として、インポートする前にラベルを削除し、インポート後にラベルを再適用できます。 詳細については、「Officeのファイルと電子メールに秘密度ラベルを適用する」を参照してください。

この記事では、データを Excel から Access に移動し、データをリレーショナル テーブルに変換して、Microsoft Excelと Access を一緒に使用できるようにする方法について説明します。 要約すると、Access はデータのキャプチャ、格納、クエリ、および共有に最適であり、データの計算、分析、視覚化にはExcelが最適です。

Access または Excel を使用してデータを管理する方法と、Excelで Access を使用する上位 10 の理由の 2 つの記事では、特定のタスクに最適なプログラムと、Excelと Access を組み合わせて使用して実用的なソリューションを作成する方法について説明します。

Excelから Access にデータを移動する場合、プロセスには 3 つの基本的な手順があります。

3 つの基本的な手順

注: Access のデータ モデリングとリレーションシップの詳細については、「 データベース設計の基本」を参照してください。

手順 1: Excelから Access にデータをインポートする

データのインポートは、データの準備とクリーンアップに時間がかかると、よりスムーズに行くことができる操作です。 データのインポートは、新しい自宅に移動するようなものです。 引っ越す前に持ち物を整理して整理すると、新しい家に落ち着く方がずっと簡単です。

インポートする前にデータをクリーンアップする

Access にデータをインポートする前に、Excelでは次のことをお勧めします。

  • 非アトミック データを含むセル (つまり、1 つのセル内の複数の値) を複数の列に変換します。 たとえば、"C# プログラミング"、"VBA プログラミング"、"Web デザイン" などの複数のスキル値を含む "Skills" 列のセルは、それぞれが 1 つのスキル値のみを含む個別の列に分割する必要があります。

  • TRIM コマンドを使用して、先頭、末尾、および複数の埋め込みスペースを削除します。

  • 印刷以外の文字を削除します。

  • スペル チェックと句読点のエラーを見つけて修正します。

  • 重複する行または重複するフィールドを削除します。

  • データの列に混合形式 (特に、数値として書式設定されたテキストまたは日付として書式設定された数値) が含まれていないことを確認します。

詳細については、次のExcelヘルプ トピックを参照してください。

注: データ クリーニングのニーズが複雑な場合、またはプロセスを自動化する時間やリソースがない場合は、サード パーティベンダーの使用を検討してください。 詳細については、Web ブラウザーで、お気に入りの検索エンジンで "データ クレンジング ソフトウェア" または "データ品質" を検索します。

インポート時に最適なデータ型を選択する

Access のインポート操作中に、手動による介入が必要な変換エラーが少ない (ある場合) ように、適切な選択を行いたいと考えています。 次の表は、Excelから Access にデータをインポートするときにExcelの数値形式と Access データ型を変換する方法をまとめたものであり、スプレッドシートのインポート ウィザードで選択する最適なデータ型に関するいくつかのヒントを示しています。

Excel番号の形式

Access のデータ型

コメント

ベスト プラクティス

テキスト

テキスト、メモ

Access Text データ型には、最大 255 文字の英数字データが格納されます。 Access Memo データ型には、最大 65,535 文字の英数字データが格納されます。

データを切り捨てないようにするには、[ メモ] を選択します。

数値、パーセンテージ、分数、科学

Access には、フィールド サイズ プロパティ (Byte、Integer、Long Integer、Single、Double、Decimal) に基づいて異なる 1 つの数値データ型があります。

データ変換エラーを回避するには、[ Double ] を選択します。

日付

日付

アクセスとExcelの両方で、同じシリアル日付番号を使用して日付を格納します。 Access の日付範囲は、-657,434 (A.D. 100 年 1 月 1 日) から 2,958,465 (9999 年 12 月 31 日) まで大きくなります。

Access は 1904 年の日付システム (Macintosh のExcelで使用) を認識しないため、混乱を避けるためにExcelまたは Access で日付を変換する必要があります。

詳細については、「日付システム、書式、または 2 桁の年の解釈を変更する」を参照し、Excel ブック内のデータをインポートまたはリンクする方法に関するページを参照してください

[日付] を選択します。

時間

時間

同じデータ型を使用して、両方のストア時間値にアクセスしてExcelします。

時間 (通常は既定値) を選択します。

通貨、会計

通貨

Access では、Currency データ型は、小数点以下 4 桁の有効桁数を持つ 8 バイトの数値としてデータを格納し、財務データを格納し、値の丸めを防ぐために使用されます。

通貨 (通常は既定値) を選択します。

Boolean

はい/いいえ

Access では、すべての Yes 値に -1、No 値すべてに 0 が使用されますが、Excelではすべての TRUE 値に対して 1、すべての FALSE 値に 0 が使用されます。

[ はい]/[いいえ]を選択すると、基になる値が自動的に変換されます。

ハイパーリンク

ハイパーリンク

Excelと Access のハイパーリンクには、クリックしてフォローできる URL または Web アドレスが含まれています。

[ハイパーリンク] を選択し、それ以外の場合は既定で [テキスト] データ型を使用できます。

データが Access になったら、Excel データを削除できます。 元のブックを削除する前に、最初に元のExcelブックをバックアップすることを忘れないでください。

詳細については、Access のヘルプ トピック「Excel ブック内のデータのインポートまたはリンク」を参照してください。

簡単な方法でデータを自動的に追加する

ユーザー Excelよくある問題は、同じ列を持つデータを 1 つの大きなワークシートに追加することです。 たとえば、Excelで始まった資産追跡ソリューションが、多くのワークグループや部署のファイルを含むようになりました。 このデータは、異なるワークシートやブック、または他のシステムからのデータ フィードであるテキスト ファイルに含まれる場合があります。 ユーザー インターフェイス コマンドや、同様のデータをExcelに追加する簡単な方法はありません。

最適な解決策は、Access を使用することです。ここでは、スプレッドシートのインポート ウィザードを使用して、データを簡単にインポートして 1 つのテーブルに追加できます。 さらに、多くのデータを 1 つのテーブルに追加できます。 インポート操作を保存したり、スケジュールされた Microsoft Outlook タスクとして追加したり、マクロを使用してプロセスを自動化したりすることもできます。

手順 2: テーブル アナライザー ウィザードを使用してデータを正規化する

一見すると、データを正規化するプロセスをステップ実行すると、困難な作業に思える場合があります。 幸いにも、Access でのテーブルの正規化は、Table Analyzer ウィザードのおかげで、はるかに簡単なプロセスです。

テーブル正規化ウィザード

1. 選択した列を新しいテーブルにドラッグし、リレーションシップを自動的に作成する

2. ボタン コマンドを使用してテーブルの名前を変更し、主キーを追加し、既存の列を主キーにし、最後のアクションを元に戻す

このウィザードを使用して、次の操作を行うことができます。

  • テーブルを小さいテーブルのセットに変換し、テーブル間に主キーと外部キーのリレーションシップを自動的に作成します。

  • 一意の値を含む既存のフィールドに主キーを追加するか、AutoNumber データ型を使用する新しい ID フィールドを作成します。

  • 連鎖更新を使用して参照整合性を適用するリレーションシップを自動的に作成します。 データを誤って削除しないように、連鎖削除は自動的に追加されませんが、後で連鎖削除を簡単に追加できます。

  • 冗長または重複するデータ (2 つの異なる電話番号を持つ同じ顧客など) を新しいテーブルで検索し、必要に応じてこれを更新します。

  • 元のテーブルをバックアップし、名前に "_OLD" を追加して名前を変更します。 次に、元のテーブルに基づく既存のフォームまたはレポートが新しいテーブル構造で動作するように、元のテーブル名を使用して元のテーブルを再構築するクエリを作成します。

詳細については、「 Table Analyzer を使用してデータを正規化する」を参照してください

手順 3: ExcelからデータにアクセスするConnect

Access でデータが正規化され、元のデータを再構築するクエリまたはテーブルが作成された後は、Excelから Access データに接続するだけで済みます。 これで、データは外部データ ソースとして Access になり、データ接続を介してブックに接続できます。これは、外部データ ソースの検索、ログオン、およびアクセスに使用される情報のコンテナーです。 接続情報はブックに格納され、Office データ接続 (ODC) ファイル (.odc ファイル名拡張子) やデータ ソース名ファイル (.dsn 拡張子) などの接続ファイルに格納することもできます。 外部データに接続した後、Access でデータが更新されるたびに、Access からExcelブックを自動的に更新 (または更新) することもできます。

詳細については、「外部データ ソースからデータをインポートする (Power Query)」を参照してください。

Access にデータを取得する

このセクションでは、データを正規化するフェーズについて説明します。Salesperson 列と Address 列の値を最もアトミックな部分に分割し、関連するサブジェクトを独自のテーブルに分離し、それらのテーブルをコピーして Access にExcelから貼り付け、新しく作成した Access テーブル間にキーリレーションシップを作成し、Access で簡単なクエリを作成して実行して情報を返します。

正規化されていない形式のデータの例

次のワークシートには、[Salesperson] 列と [住所] 列に非アトミック値が含まれています。 両方の列を 2 つ以上の別々の列に分割する必要があります。 このワークシートには、営業担当者、製品、顧客、注文に関する情報も含まれています。 この情報は、件名別にさらに別のテーブルに分割する必要もあります。

営業担当者

受注 ID

受注日

商品 ID

数量

価格

顧客名

住所

携帯電話

Li、Yale

2349

3/4/09

C-789

3

$7.00

サクラ食品販売株式会社

7007 Cornell St Redmond, WA 98199

425-555-0201

Li、Yale

2349

3/4/09

C-795

6

$9.75

サクラ食品販売株式会社

7007 Cornell St Redmond, WA 98199

425-555-0201

Adams、Ellen

2350

3/4/09

A-2275

2

$16.75

Adventure Works

1025 コロンビア 円カークランド、WA 98234

425-555-0185

Adams、Ellen

2350

3/4/09

F-198

6

$5.25

Adventure Works

1025 コロンビア 円カークランド、WA 98234

425-555-0185

Adams、Ellen

2350

3/4/09

B-205

1

$4.50

Adventure Works

1025 コロンビア 円カークランド、WA 98234

425-555-0185

Hance、Jim

2351

3/4/09

C-795

6

$9.75

楽市食品株式会社

2302 ラベ・ベルビュー(WA 98227)

425-555-0222

Hance、Jim

2352

3/5/09

A-2275

2

$16.75

Adventure Works

1025 コロンビア 円カークランド、WA 98234

425-555-0185

Hance、Jim

2352

3/5/09

D-4420

3

$7.25

Adventure Works

1025 コロンビア 円カークランド、WA 98234

425-555-0185

Koch、Reed

2353

3/7/09

A-2275

6

$16.75

サクラ食品販売株式会社

7007 Cornell St Redmond, WA 98199

425-555-0201

Koch、Reed

2353

3/7/09

C-789

5

$7.00

サクラ食品販売株式会社

7007 Cornell St Redmond, WA 98199

425-555-0201

最も小さい部分の情報: アトミック データ

この例のデータを操作すると、Excelの Text to Column コマンドを使用して、セルの "アトミック" 部分 (住所、市区町村、州、郵便番号など) を個別の列に区切ることができます。

次の表は、すべての値をアトミックにするために分割された後、同じワークシート内の新しい列を示しています。 Salesperson 列の情報は、[姓] 列と [名] 列に分割されており、[住所] 列の情報は [住所]、[市区町村]、[都道府県]、[郵便番号] の各列に分割されていることに注意してください。 このデータは "最初の通常の形式" です。

 

番地

都市

State

郵便番号

エール

2302[

恵比寿

WA

98227

アダムス

エレン

1025 コロンビア 円

Kirkland

WA

98234

Hance

Jim

2302[

恵比寿

WA

98227

コッホ

リード

7007 コーネル St Redmond

Redmond

WA

98199

Excelの整理されたテーマにデータを分割する

次のサンプル データの複数のテーブルは、営業担当者、製品、顧客、および注文のテーブルに分割された後、Excel ワークシートから同じ情報を示しています。 テーブルのデザインは最終的なものではなく、正しい軌道に置かれます。

Salespersons テーブルには、営業担当者に関する情報のみが含まれています。 各レコードには一意の ID (SalesPerson ID) があることに注意してください。 SalesPerson ID の値は、注文を営業担当者に接続するために Orders テーブルで使用されます。

Salespersons

Salesperson ID

101

エール

103

アダムス

エレン

105

Hance

Jim

107

コッホ

リード

Products テーブルには、製品に関する情報のみが含まれています。 各レコードには一意の ID (製品 ID) があることに注意してください。 製品 ID の値は、製品情報を注文の詳細テーブルに接続するために使用されます。

製品

商品 ID

価格

A-2275

16.75

B-205

4.50

C-789

7.00

C-795

9.75

D-4420

7.25

F-198

5.25

Customers テーブルには、顧客に関する情報のみが含まれています。 各レコードには一意の ID (顧客 ID) があることに注意してください。 顧客 ID の値は、顧客情報を Orders テーブルに接続するために使用されます。

Customers

得意先コード

会社名

番地

都市

State

郵便番号

携帯電話

1001

楽市食品株式会社

2302[

恵比寿

WA

98227

425-555-0222

1003

Adventure Works

1025 コロンビア 円

Kirkland

WA

98234

425-555-0185

1005

サクラ食品販売株式会社

7007 Cornell St

Redmond

WA

98199

425-555-0201

Orders テーブルには、注文、営業担当者、顧客、および製品に関する情報が含まれています。 各レコードには一意の ID (Order ID) があることに注意してください。 このテーブルの一部の情報は、注文の詳細を含む追加のテーブルに分割する必要があります。これにより、Orders テーブルには、一意の注文 ID、注文日、営業担当者 ID、および顧客 ID の 4 つの列のみが含まれます。 ここに示すテーブルは、[注文の詳細] テーブルにまだ分割されていません。

注文

受注 ID

受注日

SalesPerson ID

得意先コード

商品 ID

数量

2349

3/4/09

101

1005

C-789

3

2349

3/4/09

101

1005

C-795

6

2350

3/4/09

103

1003

A-2275

2

2350

3/4/09

103

1003

F-198

6

2350

3/4/09

103

1003

B-205

1

2351

3/4/09

105

1001

C-795

6

2352

3/5/09

105

1003

A-2275

2

2352

3/5/09

105

1003

D-4420

3

2353

3/7/09

107

1005

A-2275

6

2353

3/7/09

107

1005

C-789

5

製品 ID や数量などの注文の詳細は、Orders テーブルから移動され、Order Details という名前のテーブルに格納されます。 このテーブルには 9 つのレコードが含まれているので、9 つの注文があることに注意してください。 Orders テーブルには、Order Details テーブルから参照される一意の ID (Order ID) があることに注意してください。

Orders テーブルの最終的な設計は次のようになります。

注文

受注 ID

受注日

SalesPerson ID

得意先コード

2349

3/4/09

101

1005

2350

3/4/09

103

1003

2351

3/4/09

105

1001

2352

3/5/09

105

1003

2353

3/7/09

107

1005

Order Details テーブルには、一意の値を必要とする列がないため (つまり、主キーがないため)、すべての列に "冗長" データを含めても問題ありません。 ただし、このテーブル内の 2 つのレコードが完全に同一である必要はありません (この規則は、データベース内の任意のテーブルに適用されます)。 この表には、17 個のレコードが存在する必要があります。各レコードは、個別の順序で製品に対応します。 たとえば、順序 2349 では、3 つの C-789 製品が注文全体の 2 つの部分の 1 つを構成します。

したがって、[注文の詳細] テーブルは次のようになります。

受注明細

受注 ID

プロダクト ID

数量

2349

C-789

3

2349

C-795

6

2350

A-2275

2

2350

F-198

6

2350

B-205

1

2351

C-795

6

2352

A-2275

2

2352

D-4420

3

2353

A-2275

6

2353

C-789

5

Excelから Access へのデータのコピーと貼り付け

営業担当者、顧客、製品、注文、注文の詳細に関する情報がExcelの別のサブジェクトに分割されたので、そのデータを Access に直接コピーし、テーブルにすることができます。

Access テーブル間のリレーションシップの作成とクエリの実行

データを Access に移動した後、テーブル間のリレーションシップを作成し、さまざまなサブジェクトに関する情報を返すクエリを作成できます。 たとえば、3/05/09 から 3/08/09 の間に入力された注文の注文 ID と営業担当者の名前を返すクエリを作成できます。

さらに、フォームとレポートを作成して、データ入力と販売分析を容易にすることができます。

補足説明

Excel 技術コミュニテでは、いつでも専門家に質問できます。また、Answers コミュニティでは、サポートを受けられます。

ヘルプを表示

スキルを磨く
トレーニングの探索
新機能を最初に入手
Microsoft Office Insider に参加

この情報は役に立ちましたか?

言語の品質にどの程度満足していますか?
どのような要因がお客様の操作性に影響しましたか?

ご意見をいただきありがとうございます。

×