Excelで表の連結

下のA表とB表の二つの表を連結します。データベースソフトを使ってリレーションをつくる(とる?)ようなことをExcelでやってみます。大きな表(数千行にまたがるようなもの)はとても時間がかかるので注意してください(不安定なOSやメモリが少ないと飛びます)。

1 2 3 1 番号 名前 国語 2 2 山田 70 3 3 佐藤 80 4 1 鈴木 100

1 2 3 1 番号 名前 算数 2 2 山田 90 3 1 鈴木 100 4 4 加藤 80

共通項のみからなる表をつくる

A表とB表の両方に存在するデータからなる表を作成します。

1. 並べ替え

   二つの表を隣にならべ、それぞれの表の中で番号順になるように「データ」-「並べ替え」を実行します。

   	1	2	3	4	5	6	7
   1	番号	名前	国語		番号	名前	算数
   2	1	鈴木	100		1	鈴木	100
   3	2	山田	70		2	山田	90
   4	3	佐藤	80		4	加藤	80

2. vlookup関数を使って共通項を取り出す

   vlookup関数は表の中から検索値を探し、その行から値を見つける関数です。これを使って共通項を取り出します。

   	1	2	3	4	5	6	7
   1	番号	名前	国語		番号	名前	算数
   2	1	鈴木	100	=VLOOKUP(RC[-3],R2C5:R4C5,1,FALSE)	1	鈴木	100
   3	2	山田	70	=VLOOKUP(RC[-3],R2C5:R4C5,1,FALSE)	2	山田	90
   4	3	佐藤	80	=VLOOKUP(RC[-3],R2C5:R4C5,1,FALSE)	4	加藤	80

   vlookupの最初の引数は検索値です。これは次の表の中から探すべき文字を指定します。二番目の引数は表の範囲です。多くの場合作成した数式をコピーするので表の範囲は絶対参照(WindowsならF4、Macintoshならコマンド+T)にしておきます。三番目の引数は表の中の何列目のデータを表示するかというものです。今回は共通する番号を取り出したいので1列目にしておきます。最後の引数は検索値が見つからなかった場合の処理を指定します。falseにしておくと値が見つからなかったときにエラーを表示します。trueにしておくと次の行の値を表示します。重量別の料金表(1kgまでは100円、2kgまでは200円といったもの)を作成するときに便利です。

   vlookupの結果表示された番号が共通項となります。これをどこか別のところにコピーしておきます(形式を選択して貼り付けで値を選ぶ)。#N/Aと番号がまぜこぜになっている場合は貼り付けたデータを再度並べ替えをして#N/Aを除きます。

3. vlookupで表を再構築

   取り出した番号をキーに(データベースの用語では主キーといいます)元の表からデータを抽出し表を再構築します。

   	1	2	3	4
   1	1	2	3	3
   2	番号	名前	国語	算数
   3	1	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)
   4	2	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)

   ポイントはvlookup関数の引数にセルを使用することと、その参照に絶対参照と相対参照をうまく混ぜることです。そうすると数式をコピーするだけで簡単に表が埋まります。

   共通項の連結はこれでおしまいです。必要に応じて値を固定(コピー&形式を選択して貼り付け)しておきます。

片方にのみ存在するものを取り出す

A表にのみ存在するものを取り出します。

1. 並べ替え

   さっきと同じ。

2. A表に存在するものを表示

   A表にあってB表にないものは#N/Aとなることを利用します。

   	1	2	3	4	5	6	7	8
   1	番号	名前	国語		番号	名前	算数
   2	1	鈴木	100	=VLOOKUP(RC[-3],R2C5:R4C5,1,FALSE)	1	鈴木	100	=if(iserror(RC4), RC1, "")
   3	2	山田	70	=VLOOKUP(RC[-3],R2C5:R4C5,1,FALSE)	2	山田	90	=if(iserror(RC4), RC1, "")
   4	3	佐藤	80	=VLOOKUP(RC[-3],R2C5:R4C5,1,FALSE)	4	加藤	80	=if(iserror(RC4), RC1, "")

   iserror関数は引数がエラー値ならtrueを返します。そしてそのときに1列目の値を表示させ、そうでないときはなにも表示させないようにします。

3. vlookupで表を再構築

   さっきと同じ。

   	1	2	3	4
   1	1	2	3	3
   2	番号	名前	国語	算数
   3	1	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)
   4	2	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)
   5	3	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)

どちらかに存在するものの表のつくりかた

B表にのみ存在するものを取り出しさっきの表に追加します。共通項、Aのみ、Bのみをあわせると全部になります。

	1	2	3	4
1	1	2	3	3
2	番号	名前	国語	算数
3	1	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)
4	2	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)
5	3	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)
6	4	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C1:R4C3,R6C,FALSE)	=VLOOKUP(RC1,R2C5:R4C7,R6C,FALSE)

名前を表示するセルは参照する表を変えておくとエラーが表示されなくなります。

エラーを表示しないようにするには上記の表を参照する表を作成します。

	1	2	3	4
1	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])
2	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])
3	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])
4	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])
5	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])	=if(iserror(RC[-6]), "", RC[-6])

終わりに

SQLが使えると「select A.番号, A.名前, 国語, 算数 from A, B where A.番号=B.番号;」で終わってしまうんですけどねぇ。