この投稿は インタープリズム的「俺達私達の進捗を上げる25個前後のTips」 Advent Calendar 2015 - Qiitaの21日目 の記事です。
エクセルはマイクロソフトが開発したソフトの中で最も普及したアプリケーションソフトだと思ってます。
さまざまな立場の人がさまざまな用途で、かつ、さまざまな使い方でエクセルを利用していることと思うのですが、今回はエクセルファイルをデータベースとして使う手法を紹介したいと思います。
扱う関数
vlookupmatchindex
表の結合
以下のような表を考えます。
売上データ
| 日付 | 商品コード | 商品名 | 単価 | 個数 | 小計 |
|---|---|---|---|---|---|
| 1/3 | 101 | 鉛筆 | ￥30 | 2 | ￥60 |
| 1/3 | 102 | 消しゴム | ￥50 | 1 | ￥50 |
| 1/4 | 101 | 鉛筆 | ￥30 | 3 | ￥90 |
| 1/4 | 103 | ノート | ￥100 | 2 | ￥200 |
この場合 商品名 単価 は 商品コードに依存 しており、必ずセットでなければなりません。 この表を人間の手で作成する場合、商品コード、商品名、単価がずれることのないよう 大変な集中力 が必要とされます。
この時 リレーショナルDB なら 商品マスターテーブル
| 商品コード | 商品名 | 単価 |
|---|---|---|
| 101 | 鉛筆 | ￥30 |
| 102 | 消しゴム | ￥50 |
| 103 | ノート | ￥100 |
を用意して、 売上テーブル には 商品名カラム 、単価カラム、 小計カラム を持たずに、
商品マスターテーブルと売上テーブルを join してデータを取得するのが一般的です。
これをエクセルで実現する場合は以下のように VLOOKUP を使って対応するのが一般的です。
VLOOKUP(検索値, 範囲, 列番号, 検索方法)
- 検索値: 参照先のマスターテーブルの主キーを指定します。
- 範囲: 参照先のマスターテーブルを指定します。(主キーが最も左の列になければなりません)
- 列番号: マスターテーブル上の参照先の列番号を指定します。
- 検索方法:
- FALSE=検索値がマスターテーブル上の主キーに存在する場合のみ値を返す。
- TRUE=検索値がマスターテーブル上の主キーに一致するか、検索値未満で最も大きい数をもつ行に一致させます。
まずは、RDBのときと同様に商品マスターテーブルを別のシートに作成します。
商品マスター
| A | B | C | |
|---|---|---|---|
| 1 | 商品コード | 商品名 | 単価 |
| 2 | 101 | 鉛筆 | ￥30 |
| 3 | 102 | 消しゴム | ￥50 |
| 4 | 103 | ノート | ￥100 |
その次に売上データの 商品名、単価のデータ が入ったセルを 商品マスターテーブルを参照する数式 に変更します。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 日付 | 商品コード | 商品名 | 単価 | 個数 | 小計 |
| 2 | 1/3 | 101 | =VLOOKUP($B2,'商品マスター!$A:$C', 2, false) | =VLOOKUP($B2,'商品マスター!$A:$C', 3, false) | 2 | =$E2*$F2 |
| 3 | 1/3 | 102 | =VLOOKUP($B3,'商品マスター!$A:$C', 2, false) | =VLOOKUP($B3,'商品マスター!$A:$C', 3, false) | 1 | =$E3*$F3 |
| 4 | 1/4 | 101 | =VLOOKUP($B4,'商品マスター!$A:$C', 2, false) | =VLOOKUP($B4,'商品マスター!$A:$C', 3, false) | 3 | =$E4*$F4 |
| 5 | 1/4 | 103 | =VLOOKUP($B5,'商品マスター!$A:$C', 2, false) | =VLOOKUP($B5,'商品マスター!$A:$C', 3, false) | 2 | =$E5*$F5 |
このように VLOOKUP を使うことで、
商品コードを入力すると 自動的に商品名、単価がその商品コードのものに置き換わる
ようになります。
$ をうまく使う
セルを指定するとき $ をうまく活用すると、コピーペーストで思い通りに参照先を変えることができ、生産性が圧倒的に向上します。
例えば上の例で、 C2 のセルにある数式を 3行目 にコピーするとき、
$B2 というように列番号のBの前に $ がつくことで、コピペした時に
B列 はキープされた状態で、 行だけがコピー元からの相対的行数分だけずれる ため、
コピー後細かい編集をしなくても行に応じた商品コードを指すように自動的に編集させながら貼り付けることができます。
この列を丸ごと D列 に移すと 商品マスターのキーとなる(RDB用語で外部キーと呼ぶ)B列は固定 されるため、やはり意図通りにコピーされます。
ただ、 D列 は単価を参照したいため、 VLOOKUP の 3つめのパラメータ である商品マスターテーブルの
列番号を 2(商品名の列) から 3(単価の列) に一つずらさなければなりません。
先頭のセル(この例では D2の数式 )だけ VLOOKUP の 3つめのパラメータ を 2 から 3 に修正して、
その後そのセルを D列の最下部 までコピーすれば済む話ではありますが、
その修正をしないでもうまくいく方法があります。
それが MATCH 関数です。
F4キー
MATCH 関数の解説に入る前に、もうひとつ知っておくと便利な機能を紹介します。
セル内の関数を編集する際、数式内のセル変数にカーソルがある状態で、
F4 キーを押すと、先ほど説明した $ を順々に切り替えてくれます。
例えば =VLOOKUP(B2 という感じでまずは特に $ を使わずに入力し、
カーソルが B2 の直後にある状態で、F4 キーを連続で押下すると
$B$2⇒B$2⇒$B2⇒B2⇒$B$2⇒B$2⇒ ...
という感じでセル変数に対する $ 指定のバリエーションを順々に切り替えてくれます。
MATCH(検索値, 検索範囲, 照合方法)
この関数は 検索値 が 検索範囲 の中で、何列目もしくは何行目にあるかを返します。
検索範囲は1行もしくは1列でなければなりません。
照合方法には 完全一致(0を指定) と 以下(1) 、 以上(-1) の3パターンがあります。
これを使って上の表の C2セルの数式 を書きかえると以下のようになります。
before
=VLOOKUP($B2,'商品マスター!$A:$C', 2, false)
after
=VLOOKUP($B2,'商品マスター!$A:$C', MATCH(C$1,$1:$1,0), false)
このようにすることで、 C2セルのみ数式を編集 してそれを C2からD7までコピペ することで、 一切の編集をせずに、一気に売上テーブルを完成させることが可能となります。
多くのケースでは、 VLOOKUP と MATCH 関数で、エクセルをRDBシステムのように効率的に利用することが可能になりますが、
VLOOKUP には、 選択されたマスターテーブルの左端の列に主キーとなるカラムを配置しなければならない という制約があります。
RDB の場合は、ユーザインターフェースを別途作成するため、 テーブルを定義する際に 列の順序というものは本質的にあまり重要ではありません。 (システムを設計、開発、運用する際のメンテナンス性を考えた場合にはとても重要ですが)
エクセルをRDBシステムの代わりとして使う場合、 全てのテーブル(エクセルのシート)が、
- データストレージとしての機能
- データを入出力するためのUIとしての機能
の2つの機能を同時にもつため、オペレーションを行う方にとって見やすい列の順序で配置したいというニーズは無視することはできず、 必ずしも 主キーデータをマスターテーブルの左端に配置できないケース もあり得ます。
例えば商品マスターテーブルに対し、以下のように主キーとなる商品コードの左の列に商品カテゴリという列を挿入したとします。
商品マスター
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品カテゴリ | 商品コード | 商品名 | 単価 |
| 2 | 事務用品 | 101 | 鉛筆 | ￥30 |
| 3 | 事務用品 | 102 | 消しゴム | ￥50 |
| 4 | 事務用品 | 103 | ノート | ￥100 |
| 6 | 衣類 | 201 | 靴下 | ￥500 |
| 5 | 衣類 | 202 | Tシャツ | ￥2000 |
売上データ
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 日付 | 商品カテゴリ | 商品コード | 商品名 | 単価 | 個数 | 小計 |
| 2 | 1/3 | 事務用品 | 101 | 鉛筆 | ￥30 | 2 | ￥60 |
| 3 | 1/3 | 事務用品 | 102 | 消しゴム | ￥50 | 1 | ￥50 |
| 4 | 1/4 | 事務用品 | 101 | 鉛筆 | ￥30 | 3 | ￥90 |
| 5 | 1/4 | 事務用品 | 103 | ノート | ￥100 | 2 | ￥200 |
| 6 | 1/4 | 衣類 | 201 | 靴下 | ￥500 | 2 | ￥100 |
| 7 | 1/5 | 衣類 | 202 | Tシャツ | ￥2000 | 1 | ￥2000 |
このようなケースで活躍するのが INDEX 関数です。
INDEX(対象範囲, 行番号, 列番号)
この関数では 対象範囲 の矩形の左上セルから数えて
指定する 行番号 、 列番号 が交差する位置のセルの値を返します。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | 日付 | 商品カテゴリ | 商品コード | 商品名 | 単価 | 個数 | 小計 |
| 2 | 1/3 | =INDEX('商品マスタ!$A:$D', MATCH('商品マスタ!$C:$C', $C2), MATCH('商品マスタ!$1:$1', B$1)) | 101 | 鉛筆 | ￥30 | 2 | ￥60 |
| 3 | 1/3 | 事務用品 | 102 | 消しゴム | ￥50 | 1 | ￥50 |
| 4 | 1/4 | 事務用品 | 101 | 鉛筆 | ￥30 | 3 | ￥90 |
| 5 | 1/4 | 事務用品 | 103 | ノート | ￥100 | 2 | ￥200 |
| 6 | 1/4 | 衣類 | 201 | 靴下 | ￥500 | 2 | ￥100 |
| 7 | 1/5 | 衣類 | 202 | Tシャツ | ￥2000 | 1 | ￥2000 |
INDEX の第一引数では、 VLOOKUP の範囲指定と同様に
マスターデータを含む矩形範囲を指定します。
第二引数の売上データ上の商品コードを含む商品マスター上の行番号を指定します。
行番号は商品コードから算出するために MATCH 関数を使って求めます。
第三引数は商品マスター上の "商品カテゴリ" を持つ列番号を指定します。
これも MATCH を用いて算出するのですが、
直接"1"と指定したり、たとえ MATCH を使ったとしても
MATCH('商品マスター!$1:$1',"商品カテゴリ")
というように、数式内の具体的な情報を記入してしまうとコピペで一気に表を完成することができなくなるため、
MATCH('商品マスター!$1:$1',B$1)
のように可能な限りコピペに強い数式を完成させることが重要です。
まずこのように一つのセルのみ数式を完成させ、後は参照したいセルにコピペしていきます。
今回の例では、 B2 のセルを B2～B5 、D2～D5 、 E2～E5 にコピーすることでペースト後に一切編集をしなくても、適切な形で表が完成します。
INDEX は VLOOKUP の機能をほぼ内包しているため、
VLOOKUPは一切使わず、INDEXのみでマスターテーブルを参照する形
にすることもできますが、列番号を指定する MATCH の記述の分だけ 冗長 となるため、
キー列が左端にある場合は VLOOKUP を使った方が見通しはだいぶ良くなるかと思います。
終わりに
データを編集する人が 1～2人程度に限定されている 場合であれば、
- 社員データ
- 売上データ
- 在庫データ
- 仕訳データ
といった会社の基幹となるようなデータであっても高価な RDBMSを導入しなくても、Excelに加えて、 vss や svn 、git といった ファイル履歴管理システムを組み合わせることで、安価にシステムを構築できると思います。
仮に将来的には基幹システムをRDBMSで構築する予定であっても、 その前に、今回ご紹介したような形でエクセルを利用してプロトタイプとなる システムを構築し、実際に運用してみて、その後RDBMSを構築することにより より精度の高いシステム構築が可能になるのではと感じています。
RDBMSと違いエンジニアでなくても、手軽にそして素早く作成できるのが最も大きいメリットではないかと思います。
ただ、同時に数式部分を壊してしまうと 簡単にシステムが壊れてしまう ので、 数式セルは背景の色を変える 等、 ミスを少なくする工夫も大切になってくると思います。
インタープリズム的「俺達私達の進捗を上げる25個前後のTips」 Advent Calendar 2015 - Qiitaの22日目の記事
