注
Microsoft Access では、秘密度ラベルが適用された Excel データのインポートはサポートされていません。 回避策として、インポート前にラベルを削除し、インポート後にラベルを再度適用することができます。 詳細については、「 Office のファイルとメールに機密ラベルを適用する」を参照してください。
この記事では、Microsoft Excel と Access を一緒に使用できるように、Excel から Access にデータを移動し、データをリレーショナル テーブルに変換する方法について説明します。 まとめると、データのキャプチャ、保存、クエリ、共有には Access が最適で、データの計算、分析、および視覚化には Excel が最適です。
「Access または Excel を使用してデータを管理する」と「Excel で Access を使用する 10 の理由」という 2 つの記事では、特定のタスクに最適なプログラム、および Excel と Access を組み合わせて実用的なソリューションを作成する方法について説明しています。
Excel から Access にデータを移動する場合、プロセスには 3 つの基本的な手順があります。
注
Access におけるデータのモデリングとリレーションシップの詳細については、「 データベース設計の基本」を参照してください。
手順 1: Excel から Access にデータをインポートする
データのインポートは、時間をかけてデータを準備してクリーンにすると、はるかにスムーズに進む操作です。 データのインポートは、新しい家に引っ越すようなものです。 引っ越す前に持ち物をクリーンに整理しておけば、新しい家に落ち着くのがはるかに楽になります。
インポート前にデータをクリーンアップする
Excel で、Access にデータをインポートする前に、次のことを行ってください。
- 非アトミック データ (つまり、1 つのセルに複数の値) を含むセルを複数の列に変換します。 たとえば、「C# プログラミング」、「VBA プログラミング」、「Web デザイン」など、複数のスキル値を含む「スキル」列のセルは、それぞれに 1 つのスキル値のみを含む個別の列に分割する必要があります。
- TRIM コマンドを使用して、先頭、末尾、および複数の埋め込みスペースを削除します。
- 印刷されない文字を削除します。
- スペル ミスと句読点の誤りを見つけて修正します。
- 重複する行または重複するフィールドを削除する。
- データの列に、特にテキストとして書式設定された数値や数値として書式設定された日付など、さまざまな形式が混在していないことを確認します。
詳細については、次の Excel ヘルプ トピックを参照してください。
注
データ クリーニングのニーズが複雑な場合、または自分でプロセスを自動化する時間やリソースがない場合は、サードパーティ ベンダーの使用を検討してください。 詳細については、Web ブラウザでお気に入りの検索エンジンで「データ クレンジング ソフトウェア」または「データ品質」を検索してください。
インポート時に最適なデータ型を選択する
Access でのインポート操作中は、手動による操作を必要とする変換エラーが (あればある場合) ほとんど発生しないように、適切な選択をする必要があります。 次の表は、Excel から Access へデータをインポートするときに Excel の数値形式と Access データ型がどのように変換されるかをまとめたものです。また、スプレッドシートのインポート ウィザードで選択する最適なデータ型に関するヒントも示されています。
| Excel の数値の表示形式 | Access のデータ型 | コメント | ベスト プラクティス |
|---|---|---|---|
| テキスト | テキスト、メモ | Access テキスト データ型には、最大 255 文字の英数字データが格納されます。 Access メモ データ型には、最大 65,535 文字の英数字データが格納されます。 | データの切り捨てを避けるには、[ メモ ] を選択します。 |
| 数値、パーセンテージ、分数、指数 | 数字 | Access には、フィールド サイズ プロパティに応じて異なる 1 つの数値データ型 (バイト、整数、長整数、単一、倍精度浮動小数、10 進数) があります。 | データ変換エラーを回避するには、[ 倍精度浮動小数点] を選択します。 |
| 日付 | 日付 | Access と Excel の両方で、同じシリアル日付番号を使用して日付を格納します。 Access では、日付範囲がより大きく、-657,434 (西暦 100 年 1 月 1 日) から 2,958,465 (西暦 9999 年 12 月 31 日) までです。 Access では 1904 年の日付システム (Macintosh 版 Excel で使用) が認識されないため、混乱を避けるために Excel または Access のいずれかで日付を変換する必要があります。 詳細については、「 日付システム、形式、または 2 桁の年の解釈を変更する 」および 「Excel ブックのデータのインポートまたはリンクの設定」を参照してください。 |
日付を選びます。 |
| 時間 | 時間 | Access と Excel の両方で同じデータ型を使用して時刻の値が格納されます。 | [ Time] を選択します。これは通常既定値です。 |
| 通貨、会計 | 通貨 | Access では、Currency データ型は、小数点以下 4 桁までの精度を持つ 8 バイトの数値としてデータを格納し、財務データを格納し、値の丸めを防止するために使用されます。 | [ 通貨] を選択します。これは通常既定値です。 |
| Boolean | はい/いいえ | Access では、すべての Yes 値に -1、すべての No 値に 0 を使用します。一方、Excel では、すべての TRUE 値に 1、すべての FALSE 値に 0 を使用します。 | [はい/いいえ] を選択すると、基になる値が自動的に変換されます。 |
| ハイパーリンク | ハイパーリンク | Excel と Access のハイパーリンクには、クリックしてフォローできる URL または Web アドレスが含まれています。 | [ ハイパーリンク] を選択する。そうしないと、Access で既定でテキスト データ型が使用される可能性があります。 |
データが Access に保存されたら、Excel データを削除できます。 元の Excel ブックを削除する前に、必ずバックアップしてください。
詳細については、Access のヘルプ トピック「 Excel ブックのデータにインポートまたはリンクする」を参照してください。
簡単な方法でデータを自動的に追加する
Excel ユーザーが抱える一般的な問題は、同じ列を持つデータを 1 つの大きなワークシートに追加することです。 たとえば、Excel で開始した資産追跡ソリューションが、現在は多くのワークグループや部門のファイルを含むように拡大したとします。 このデータは、異なるワークシートやブック内にある場合や、他のシステムからのデータ フィードであるテキスト ファイル内にある場合があります。 Excel にはユーザー インターフェイス コマンドや、同様のデータを追加する簡単な方法はありません。
最適な解決策は、スプレッドシートのインポート ウィザードを使って 1 つのテーブルにデータを簡単にインポートしたり追加したりできる Access を使用することです。 さらに、1 つのテーブルに多数のデータを追加できます。 インポート操作を保存したり、スケジュールされた Microsoft Outlook タスクとして追加したり、マクロを使用してプロセスを自動化したりすることもできます。
手順 2: テーブル アナライザー ウィザードを使用してデータを正規化する
一見すると、データの正規化プロセスを段階的に進めるのは困難な作業のように思えるかもしれません。 幸いなことに、テーブル アナライザー ウィザードのおかげで、Access でのテーブルの正規化ははるかに簡単に行うことができます。
1. 選択した列を新しいテーブルにドラッグし、リレーションシップを自動的に作成する
2. ボタン コマンドを使用して、テーブルの名前の変更、主キーの追加、既存の列の主キーの作成、直前の操作の元に戻す
このウィザードを使用して、次のことを行うことができます。
- テーブルを小さなテーブルのセットに変換し、テーブル間に主キーおよび外部キーのリレーションシップを自動的に作成します。
- 一意の値を含む既存のフィールドに主キーを追加するか、オートナンバー型を使用する新しい ID フィールドを作成します。
- 連鎖更新を使用して参照整合性を適用するためのリレーションシップを自動的に作成します。 データを誤って削除するのを防ぐために連鎖削除が自動的には追加されませんが、後で連鎖削除を簡単に追加できます。
- 新しいテーブルで冗長なデータや重複するデータ (同じ顧客が 2 つの異なる電話番号を持っている場合など) を検索し、必要に応じて更新します。
- 元のテーブルをバックアップし、名前に "_OLD" を追加して名前を変更します。 次に、元のテーブルを元のテーブル名で再構築するクエリを作成し、元のテーブルに基づく既存のフォームまたはレポートが新しいテーブル構造で機能するようにします。
詳細については、「 テーブル アナライザーを使用してデータを正規化する」を参照してください。
手順 3: 接続して Excel からデータにアクセスする
データが Access で正規化され、元のデータを再構築するクエリまたはテーブルが作成されたら、Excel から Access データに接続するだけです。 これでデータは外部データ ソースとして Access に格納されたため、データ接続を介してブックに接続できます。データ接続は、外部データ ソースの検索、ログオン、外部データ ソースへのアクセスに使用される情報のコンテナーです。 接続情報はブックに保存されますが、Office データ接続 (ODC) ファイル (.odc ファイル名拡張子) やデータ ソース名ファイル (.dsn 拡張子) などの接続ファイルに保存することもできます。 外部データに接続した後、Access でデータが更新されるたびに、Access から Excel ブックを自動的に最新の情報に更新 (または更新) することもできます。
詳細については、「外部データ ソースからデータをインポートする (Power Query)」を参照してください。
データを Access に取得する
このセクションでは、データの正規化の次のフェーズについて説明します。販売員列と住所列の値を最も原子的な部分に分割し、関連する主題を独自のテーブルに分割し、Excel から Access にそれらのテーブルをコピーして貼り付け、新しく作成された Access テーブル間にキー リレーションシップを作成し、情報を返す簡単なクエリを Access で作成して実行します。
正規化されていない形式のサンプル データ
次のワークシートでは、[販売員] 列と [住所] 列に非原子値が含まれています。 両方の列を 2 つ以上の個別の列に分割する必要があります。 このワークシートには、販売員、製品、顧客、および注文に関する情報も含まれています。 この情報は、主題ごとに個別のテーブルにさらに分割する必要があります。
| 営業担当者 | 受注 ID | 受注日 | 製品 ID | 数量 | 価格 | Customer Name | Address | 携帯電話 |
|---|---|---|---|---|---|---|---|---|
| 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 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Adams, Ellen | 2350 | 3/4/09 | F-198 | 6 | $5.25 | Adventure Works | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Adams, Ellen | 2350 | 3/4/09 | B-205 | 1 | $4.50 | Adventure Works | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Hance, Jim | 2351 | 3/4/09 | C-795 | 6 | $9.75 | 楽市食品株式会社 | 2302 Harvard Ave Bellevue, WA 98227 | 425-555-0222 |
| Hance, Jim | 2352 | 3/5/09 | A-2275 | 2 | $16.75 | Adventure Works | 1025 Columbia Circle Kirkland, WA 98234 | 425-555-0185 |
| Hance, Jim | 2352 | 3/5/09 | D-4420 | 3 | $7.25 | Adventure Works | 1025 Columbia Circle Kirkland, 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 の [テキストを列に] コマンドを使用して、セルの "アトミック" な部分 (番地、市区町村、都道府県、郵便番号など) を個別の列に区切ることができます。
次の表は、すべての値をアトミックにするために分割された後の、同じワークシート内の新しい列を示しています。 [販売員] 列の情報は [姓] 列と [名] 列に分割され、[住所] 列の情報は [番地]、[市区町村]、[州]、および [郵便番号] 列に分割されていることに注意してください。 このデータは "第 1 正規形" です。
| 姓 | 名 | 番地 | 都市 | 目的の状態 | 郵便番号 |
|---|---|---|---|---|---|
| Li | Yale | 2302 Harvard Ave | 恵比寿 | WA | 98227 |
| Adams | Ellen | 1025 Columbia Circle | Kirkland | WA | 98234 |
| Hance | Jim | 2302 Harvard Ave | 恵比寿 | WA | 98227 |
| Koch | リード | 7007 Cornell St Redmond | Redmond | WA | 98199 |
Excel でデータを整理された主題に分割する
以下のサンプル データのいくつかの表は、販売員、製品、顧客、および注文のテーブルに分割された後の Excel ワークシートと同じ情報を示しています。 テーブルのデザインは最終的なものではありませんが、正しい軌道に乗っています。
"販売員" テーブルには、営業担当者に関する情報のみが含まれています。 各レコードには一意の ID (営業担当者 ID) があります。 [営業担当者 ID] の値は、注文を営業担当者に接続するために [受注] テーブルで使用されます。
| 営業担当者 | ||
|---|---|---|
| 営業担当者 ID | 姓 | 名 |
| 101 | Li | Yale |
| 103 | Adams | Ellen |
| 105 | Hance | Jim |
| 107 | Koch | リード |
[製品] テーブルには製品に関する情報のみが含まれています。 各レコードには一意の 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 |
"顧客" テーブルには、顧客に関する情報のみが含まれています。 各レコードには一意の ID (顧客 ID) があります。 [顧客 ID] の値は、顧客情報を "注文" テーブルに接続するために使用されます。
| Customers | ||||||
|---|---|---|---|---|---|---|
| 得意先コード | 名前 | 番地 | 都市 | 目的の状態 | 郵便番号 | 携帯電話 |
| 1001 | 楽市食品株式会社 | 2302 Harvard Ave | 恵比寿 | WA | 98227 | 425-555-0222 |
| 1003 | Adventure Works | 1025 Columbia Circle | Kirkland | WA | 98234 | 425-555-0185 |
| 1005 | サクラ食品販売株式会社 | 7007 Cornell St | Redmond | WA | 98199 | 425-555-0201 |
"注文" テーブルには、注文、販売員、顧客、および製品に関する情報が含まれています。 各レコードには一意の ID (注文 ID) があります。 このテーブルの情報の一部は、注文の詳細を含む追加のテーブルに分割する必要があるため、[受注] テーブルには、一意の受注 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 や数量などの注文の詳細は、[受注] テーブルの外に移動され、[受注明細] テーブルに格納されます。 9 つの注文があるため、このテーブルに 9 つのレコードがあるのは理にかなっています。 "受注" テーブルには、"受注明細" テーブルから参照される一意の ID (受注 ID) があります。
"受注" テーブルの最終的なデザインは、次のようになります。
| 注文 | |||
|---|---|---|---|
| 受注 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 |
[受注明細] テーブルには、一意の値を必要とする列は含まれていない (つまり、主キーがない) ため、いずれかの列またはすべての列に "冗長な" データが含まれてもかまいません。 ただし、このテーブルの 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 に移動した後、テーブル間のリレーションシップを作成して、さまざまな主題に関する情報を返すクエリを作成できます。 たとえば、09/3/05 から 09/3/08 の間に入力された注文の注文 ID と販売員の名前を返すクエリを作成できます。
さらに、フォームやレポートを作成して、データ入力や販売分析を容易にすることもできます。
補足説明
Excel 技術コミュニティの専門家にいつでも質問するか、コミュニティでサポートを受けることができます。