公開日 2021年7月9日 最終更新日 2021年7月20日
分析したい値が複数のファイルにあるとき、VLOOKUPは必要ありません。
この投稿は
・無料販売データ「家計調査_家計収支編_二人以上の世帯_用途分類_月次_2021年5月 (列指向形式)」
・無料販売データ「家計調査_家計収支編_二人以上の世帯_用途分類_月次_2021年5月_調査対象世帯について (クロス集計形式)」
を使用して説明しています。
複数ファイルをリレーションシップする方法は、テキストファイルをどのような方法でエクセルで開いたのかによって前段の手順に違いがあります。
複数ファイルをエクセルでリレーションシップするためには、データ表を「テーブル」へ変換する必要があります。
① テキストファイルをエクセルへ変換する方法で開いているとき
データをテーブル化する必要があります。
② エクセルからテキストファイルへ接続する (エクセルへテキストファイルをインポートする)方法で開いているとき
テーブル化されているのでテーブル化の手順は必要ありません。
目次
前提条件
エクセルで複数ファイルをリレーションシップするための前提条件が2つあります。
テーブルデータであること
テキストファイルをエクセルへ変換する方法で開いているときに必要な手順です。へーブルデータへの変換手順は以下で解説していますのでご覧ください。
リレーションシップするすべてのデータに結合句 (Key) になる列がある。
データ「消費支出額」とデータ「世帯マスタ」をリレーションシップします。このとき結合句 (Key) になる列は、B列の「area_code」です。
互いのデータの列名が異なることは構いませんが、値について重要なポイントがあります。
・データ「消費支出額」の「area_code」にある値と同一の値がデータ「世帯マスタ」にもある。
同一の値がないときはリレーション結果がNULLになります。
・データ形式を同一にする。
一方が文字列ならもう一方も文字列、一方が日付ならもう一方も日付にしてください。
＜重要＞
・一方のデータの結合句 (Key) 列の値に重複がなく、ユニークになっていること。
画像の場合、データ「消費支出額」の「area_code」列の値に重複がありますが、データ「世帯マスタ」の「area_code」列の値に重複はありません。
つまり、一方のデータは列指向形式でも構いませんが、もう一方のデータはクロス集計形式である必要があります。
・エクセルでリレーションシップの結合句 (Key) に設定できるのは1列に限ります。
では、画像のようなときはどうするのか？データ「世帯マスタ」の「area_code」列に重複した値があります。
・複数の列をつなぎ合わせて重複がない値を作成します。
・作成した列を結合句 (Key) に設定すればリレーションシップできます。
① テキストファイルをエクセルへ変換する方法で開いているとき
テキストファイルをエクセルへ変換する
すでに右側のテキストファイルを「① テキストファイルをエクセルへ変換する方法で開いている」とします。
ほほからは、左側のテキストファイルとリレーションシップ手順の解説です。
左側のファイルも「① テキストファイルをエクセルへ変換する方法」で開きます。
エクセルで開いた左側のファイルをすでにエクセルで開いている右側のデータのエクセルブックへ移動します。
・シート名を右クリック
・「移動またはコピー」を選択します。
・すでに開いているエクセルブックを選択して「OK」です。
シート名が製品名になっているとややこしいため、わかりやすい名前へシート名を変更します。
00300-mofa-00200561-a101-full-list-202105 →「消費金額」
00300-mofa-00200561-a101-extract-cross-202105_setai →「世帯数マスタ」
テーブルデータへ変換
・セルA1へカーソルを合わせます。
・「挿入」タブ
・「テーブル」をクリックします。
・データの範囲を確認して「OK」です。
セルA1へカーソルを合わせていないときや、データに空白行列が差し込まれているとデータの範囲が正しく指定されないことがあります。
「デザイン」タブが出現すれば、テーブル化されています。
・「デザイン」タブ
・「テーブル名」をタイプします。
テーブル名は簡素でわかりやすいものへ変更してください。（重要なポイントです）
・タイプしたら必ずパソコンキーボードの「エンターキー」を2回くらい押してください。
・両方のシートをテーブル化します。
これで2つのデータのテーブル化が完成しました。
② エクセルからテキストファイルへ接続する (エクセルへテキストファイルをインポートする)方法で開いているとき
エクセルブックへ追加するテキストデータをインポートします。
・「デザイン」タブ
テーブル名を簡素でわかりやすいものへ変更してください。（重要なポイントです）
テーブルデータをリレーションシップ
ここからは、開き方にかかわらずテーブル化したデータの共通手順です。
リレーションシップ
・「データ」タブ
・「リレーションシップ」をクリックします。
・「新規作成」をクリック
下向きの三角をクリックするとドロップダウンがあらわれます。
・「消費金額」のワークシートテーブルを選択します。
「関連テーブル」の窓で
・「世帯数マスタ」のワークシートテーブルを選択します。
2つのデータに共通している列が結合句 (Key) になります。
右側の窓は上下ともに
・「area_code」を選択します。
・「OK」です。
「OK」をクリックしたとき「テーブル間のリレーションシップが必要である可能性があります」このようなメッセージがあらわれることがあります。
＜ポイント＞
下の「関連テーブル」へ重複行がないデータを設定します。この設定の上下が逆になっているときにメッセージがあらわれます。
メッセージがあらわれても、そのまま進んでください。エクセルが自動で上下を入れ替えてリレーションシップしてくれます。
・「OK」をクリックして窓を閉じます。
これで2つのテーブルデータのリレーションは完成です。
リレーションしたデータを使用するピボット
・「挿入」タブ
・「ピボットテーブル」をクリックします。
・「このブックのデータモデルを使用する」のラジオボタンがクリックできる状態になっていればクリックして「OK」です。
「このブックのデータモデルを使用する」のラジオボタンがクリックできないときは次の手順へ進んでください。
クリックできないのは壊れているのではなくて、「このデータをモデルに追加する」にチェックを入れたピボットをエクセルブック内に挿入しているかいないかで、「このブックのデータモデルを使用する」のラジオボタンがクリックできるかどうかが決まるようです。
・「外部データソースを使用」のラジオボタンをクリックして
・「接続の編集」を押します。
・「テーブル」タブ
・リレーションシップした「2テーブル」をクリック
・「開く」です。
・「OK」で窓を閉じます。
ピボットテーブルのフィールドの窓へ2つのテーブルが現れている。アイコンの上部の縁が黒色になっていれば成功です。
この記事は「リレーションしている2つのデータの値を使用する計算 (エクセルピボット編)」へ続きます。
