Numbers(excel)でリレーショナル・データベースっぽくデータ連結をする方法

税制度のインボイス制度を会社でちゃんと対応するために、今使っている自作の会社のERPシステム（経理のデータ入力システム）を大改修しています。 これまで使いづらかった機能を一掃して作り直しをしているので、これに膨大な時間を取られていますが、他の人を巻き込んでいないので、モチベーション高く進められています。 大体の構築は終わっていて、データを移行する時に、今まで使っていたシステムと新しいシステムで、マスターデータのIDが違っているため、それらを変換する必要があったんですよ。 それを、Macにデフォルトでインストールされている、Numbersというエクセルのようなアプリで便利に設定ができたので、その備忘録を残しておくことにします。

データベースの基本は、マスターと変動データ

データベースについての説明をするわけではないんですが、データベースって、たくさんの色々なテーブルを作って、それらをリレーションという連携を行い、データを便利に活用していくというのが一般的な構造です。 世の中には、データベースを使ったwebサービスやアプリケーションなどがたくさんありますが、このデータベース構造の設計がちゃんとできているかどうかが、サービスの品質につながると思ってもいいかもしれませんね。 そんなデータベースのデータって、マスターデータと変動データという２種類で構成されているので、良くわからないという人は、この点だけはちゃんと理解してから、今回のブログを読んでみて下さい。

マスターデータ

他のデータで使われる、共通データの事。 固定的なデータで、追加や変動が少ないのが特徴。

変動データ

常に書き換えや、追記が行われるデータの事。 ログデータや、ツィートデータなどのように、ボリュームが大きくなることも特徴的。

サンプルデータ

Numbersを使って、リレーショナル・データベースを再現するための基本データ（サンプル）は次のような構造です。

取引会社

ID	name
1	株式会社ABC
2	有限会社ドレミ
3	会社法人いろは

担当者

ID	name
1	山田太郎
2	鈴木一郎

帳簿データ

ID	取引会社	担当者	金額
1	株式会社ABC	山田太郎	10,000
2	株式会社ABC	鈴木一郎	15,000
3	有限会社ドレミ	山田太郎	20,000
4	会社法人いろは	鈴木一郎	5,000

リレーショナルデータベースっぽい設定方法

IDの置き換え方法

上記のような３つのテーブルを作り、帳簿データの取引会社と担当者を、それぞれのテーブルのID値に置き換えたいと思います。 まず、マスターデータの横に、変換されたIDの値が入る列を作っておきます。

セルの式 : LOOKUP

関数ヘルプに書かれている内容は下記のとおりです。

LOOKUP LOOKUP関数は、1つ目の集合から検索値に合致するものを検索し、2つ目の集合から対応する位置にあるセルの値を返します。 LOOKUP(検索値, 検索範囲, 対応範囲) 検索値: 検索する値。 「検索値」には、任意の値またはREGEX文字列を含めることができます。 検索範囲: 検索する値を含む集合。 「検索範囲」には任意の値を含めることができます。 対応範囲: 検索結果に基づき返される値を含む集合（オプション）。 「対応範囲」には任意の値を含めることができます。 参考 通常は、「検索範囲」と「対応範囲」の両方が含まれます。これらは1次元です（複数の列または複数の行で、両方ではありません）。ただし、別のスプレッドシートアプリケーションとの互換性を保つために、「検索範囲」を2次元（複数の列と複数の行）で指定できます。「対応範囲」は省略可能です。 「検索範囲」が2次元で「対応範囲」が指定されている場合は、一番上の行または左端の列（セルの数が多い方）が検索され、対応する値が「対応範囲」から返されます。 「検索範囲」が2次元で「対応範囲」が省略されている場合は、最後の行（集合に含まれる列数が行数より多い場合）または最後の列（集合に含まれる行数が列数より多い場合）の対応する値が返されます。

難し目に書かれているのを読むよりも実行したほうが早そうです・・・ とりあえず、取引先のIDを取ってくるため、B2のセルに式を書きます。

検索値

C行の値を参照するので、"C2"で登録。

検索範囲

この箇所をクリックして、取引先テーブルに切り替えて、名前の箇所を次のようにエリア選択します。

対応範囲

検索範囲と同じ手順で、取引先のIDの箇所をエリア選択。

セルを固定化する

この設定をしておかないと、値がズレてしまいます。

他のセル全てにコピー

うまく登録できたら、同じ列全てにコピー＆ペーストします。 無事にIDが連動されましたね。

総仕上げ

担当者IDも同じ様にセットしたら、下記のように連動できました。

あとがき

上記のようにセットできたら、csv出力をしてデータベースにインポートする事が可能になります。 なかなか面倒くさい設定ですが、システム移行期にこうした作業ができるようになったら、作業も捗るでしょうね。