例題0. 共通の項目で表をつなぎ合わせるとは？
以下のような表1と表2に入っているデータを、表1と表2に共通している項目「会社コード」でつなぎ合わせ、表3のようなデータをつくること。表3の空欄①〜②に入るべきデータを表1と表2から探して手作業でいれてみよう。手作業でできないことをコンピュータにやらせることはできない。（data07m.xlsx 手作業 シート）。
表1. 会社名・会社所在地・業主
| 会社コード | 会社名 | 所在地 | 業種 |
|---|---|---|---|
| 1000 | たぬき産業 | 武蔵野 | うどん製造・販売 |
| 1005 | いなり食品 | 大阪 | 油揚げ製造 |
| 1010 | きつね物産 | 四国 | 冷凍うどん製造 |
| 2010 | タヒチ トラベル | 仏領ポリネシア | 旅行代理店 |
| 2022 | かわうそ旅行 | 米国ハワイ州 | 旅行業 |
| 2031 | サンタ興業 | フィンランド | 観光エージェント |
表2. 従業員数・売上高・純利益
| 会社コード | 従業員数 | 売上高 | 純利益 |
|---|---|---|---|
| 1000 | 10 | 1000 | 20 |
| 1010 | 20 | 6000 | 100 |
| 2022 | 300 | 20000 | 200 |
| 2031 | 600 | 56000 | 5000 |
表3. 会社名・従業員数・純利益
| 会社コード | 会社名 | 従業員数 | 純利益 |
|---|---|---|---|
| 1000 | たぬき産業 | 10 | 20 |
| 1010 | きつね物産 | 30 | 100 |
| 2022 | かわうそ旅行 | 20 | ① |
| 2031 | サンタ興業 | 500 | ② |
表1と表2に含まれている会社が同じならばそのままコピーして貼り付ければOKだが、表1には表2に含まれない会社も入っているためそのまま貼り付けると行がずれてしまう。そこで丁寧にチェックしていくことになる。
Step 1. 表3から必要な会社コードをチェック
①に相当する会社コードは、一番左の列を見て「2022」であることがわかる。
Step 2 表2から会社コードに対応するデータを探す
純利益のデータは表2に載っているので、表2から会社コードが2022である行を選び、純利益の列を読めばよい。つまり①には200が入る。
同様に②は5000である。
このように、複数の表をつなぎ合わせるために用いる共通の項目を「キー項目」と呼ぶ。同じ表の中に2つ同じキー項目の値があると結合結果が変になるので、原則としてキー項目は表の中で行を特定できるような値を付けておくことが望ましい。
例題1. Excel関数を使って表の連結を行う
上で行ったような作業は確かに手作業でもできるが、表の行数が1000行もあるとかなり辛い作業になる。更に、転記ミスの可能性も高くなる。機械的にできることはコンピュータにやらせるのが吉。そこで、VLOOKUPという関数を使ってこの作業を自動化してみよう。
ここでは、業種（中分類）が食品製造であるような企業について、企業情報を整理した表を作成する作業を VLOOKUPで行ってみよう。データは data07m.xlsx VLOOKUPで結合 シート（*1）に入っている。
(*1) VLOOKUPで結合 シートは説明のため1枚のシートに3種類の表を入れているが、実際のデータではこのような表の作り方はしない。別の種類の表は別のシートにが大原則。
Step 1. 表2から「業種（中分類）」が「食品製造業」である会社を選んで、その会社コードを表3のA列にコピーする
特定のデータを抽出するには？ → Excel編 Q49
Step 2. 会社名の情報を表1から得る
検索値をもとにデータから値を抽出するには？ → Excel編 Q28
とりあえず、以下の計算式を入力する。その際、できる限りコピーでラクをできるように考えること
セル B24 ← =VLOOKUP(A24,$A$4:$D$9,2,FALSE)
セル B25 ← =VLOOKUP(A25,$A$4:$D$9,2,FALSE)
セル B26 ← =VLOOKUP(A26,$A$4:$D$9,2,FALSE)
こんな感じになるはず
Step 3. 従業員数・純利益の情報を表2から得る
ここも、以下の計算式を入力する。できる限りコピーでラクをできるように考えることも同様。
セル C24 ← =VLOOKUP(A24,$A$4:$D$9,4,FALSE)
セル C25 ← =VLOOKUP(A25,$A$4:$D$9,4,FALSE)
セル C26 ← =VLOOKUP(A26,$A$4:$D$9,4,FALSE)
セル D24 ← =VLOOKUP(A24,$A$14:$D$19,4,FALSE)
セル D25 ← =VLOOKUP(A25,$A$14:$D$19,4,FALSE)
セル D26 ← =VLOOKUP(A26,$A$14:$D$19,4,FALSE)
できあがりはこんな具合。確かに従業員数と純利益のデータが正しく入力できていることを確認しておこう。
練習問題1. data07m.xlsx VLOOKUPで結合 シートを使って以下の作業を行いなさい
data07m.xlsxの成績評価 シートには受験者の受験者番号が入っている。また試験1、試験2シートにはそれぞれ1回目および2回目の試験の結果が入っている。
©2014, Hiroshi Santa OGAWA, Mai OKUDA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.