N0ラボ（仮）
Excel編
関数だけでフィルタっぽくデータを複数抽出
多少Excelに慣れた人ならVLOOKUP関数と言えばどういうことをするものかわかると思う。
| No. | 所属 | 名前 |
|---|---|---|
| 16 | 営業部 | 田中 |
| 22 | 開発部 | 宮本 |
| 51 | 総務部 | 大村 |
| 29 | 営業部 | 平山 |
例えばこういう表に対して、No.22の人の名前は何か？→宮本さんである、という検索をしてくれるのがVLOOKUP関数である。
=VLOOKUP(22,表の範囲,3(列目),FALSE)
と書けばこの関数の結果は宮本になる。
しかし、VLOOKUPは微妙に不便でもある。例えば、この表から「平山さんはどこの所属だっけ？」という検索をしようとしてもVLOOKUPではうまくいかない。 なぜなら、VLOOKUPが検索してくれるのは第２引数で指定した範囲のうち最も左側であり、検索する列より左側にある値は取り出しようがない。
この問題を解決するのが、INDEX+MATCHという手法である。INDEXは配列または表から指定した行数・列数にある値を取り出してくる関数で、 MATCHはある配列に対して検索する値が何番目に登場するかを調べる関数である。
なので、上述の、No.22の人の名前を調べる関数はこうも書ける。
=INDEX(名前列,MATCH(22,No列,0))
では、平山さんの所属を調べるには？
=INDEX(所属列,MATCH("平山",名前列,0))
これで解決する。
じゃあ、営業部の人を抽出してその名前を表示…ということを考えてみたのだが、ここで行き詰まった。 VLOOKUPもINDEX+MATCHも、最初にヒットするものしか検索してくれないのである。つまり、
=INDEX(名前列,MATCH("営業部",所属列,0))
のような書き方で、営業部の最初に書いてある田中さんを検索することはできるが、営業部２人目の平山さんは検索されない。 もちろん、フィルタ（オートフィルタ）を使えば、２人抽出することはできる。しかし、検索した結果を用いて何かしたい、 となるとフィルタの結果を別のシートに貼り付けて云々することになり、面倒である。
だが、これをなんとか関数だけでやりたいんだあ！
ヒントはINDEX+MATCHにある。上のようにネストさせて書いてしまうのはVLOOKUPとの互換性がわかりやすいだけにすぎない。 別にINDEXとMATCHをばらばらに書いたっていい。
| A | B | C | |
|---|---|---|---|
| 1 | No. | MATCH | INDEX |
| 2 | 51 | =MATCH(A2,No列,0) | =INDEX(名前列,B2) |
| 3 | 29 | =MATCH(A3,No列,0) | =INDEX(名前列,B3) |
| 4 | 16 | =MATCH(A4,No列,0) | =INDEX(名前列,B4) |
こう書くと結果はこうなる。
| A | B | C | |
|---|---|---|---|
| 1 | No. | MATCH | INDEX |
| 2 | 51 | 4 | 大村 |
| 3 | 29 | 5 | 平山 |
| 4 | 16 | 2 | 田中 |
このB列に、必要なインデックスが出るようにすれば、C列はそのままでいい。では、どうやってインデックス(B列)を作るか？
ここで、Excelの一見意味不明ながらものすごいポテンシャルをもった関数を使う。OFFSETという関数。 そもそもOFFSET関数がヘルプを読んでも「参照を返します」とか意味不明なことしか書いていないし、 関数の挿入ダイアログで挙動を見ようと思っても「可変」としか出てこないから余計意味がわからない。
Excelでは範囲指定をA4:C8とか表現するが、この範囲指定を動的に関数で指定しようというのがOFFSETなのである。
例えば、=OFFSET(A3,1,0,5,3)という書き方はA4:C8を表す。
A3セルから、+1行、+0列の位置（つまりA4)から、高さ5列、幅3列という範囲−ということでA4:C8という意味になる。
ではそろそろ、解答１。
| A | B | C | |
|---|---|---|---|
| 1 | 所属 | MATCH | INDEX |
| 2 | ダミー行 | 0 | |
| 3 | 営業部 | =MATCH($A$3,OFFSET($所属列$1行目,B2,0,ROWS(所属列)-B2,1),0)+B2 | =IF(ISERROR(B3),"",INDEX(名前列,B3)) |
| 4 | =MATCH($A$3,OFFSET($所属列$1行目,B3,0,ROWS(所属列)-B3,1),0)+B3 | =IF(ISERROR(B4),"",INDEX(名前列,B4)) | |
| 5 | =MATCH($A$3,OFFSET($所属列$1行目,B4,0,ROWS(所属列)-B4,1),0)+B4 | =IF(ISERROR(B5),"",INDEX(名前列,B5)) |
| A | B | C | |
|---|---|---|---|
| 1 | 所属 | MATCH | INDEX |
| 2 | ダミー行 | 0 | |
| 3 | 営業部 | 2 | 田中 |
| 4 | 5 | 平山 | |
| 5 | #N/A |
解答１は、INDEX+MATCHの応用としてデータの行数を複数導くというアプローチをとっていた。 実は別解もあり、これは逆にデータ側に、条件を満たす何番目のものであるというインデックスを付与する方法がある。 では解答２。
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | No. | 所属 | 名前 | 営業部 | MATCH | INDEX | |
| 2 | 16 | 営業部 | 田中 | =COUNTIF(B$2:B2,$D$1) | =MATCH(ROW()-1,D:D,0) | =IF(ISERROR(F2),"",INDEX(C:C,F2)) | |
| 3 | 22 | 開発部 | 宮本 | =COUNTIF(B$2:B3,$D$1) | =MATCH(ROW()-1,D:D,0) | =IF(ISERROR(F3),"",INDEX(C:C,F3)) | |
| 4 | 51 | 総務部 | 大村 | =COUNTIF(B$2:B4,$D$1) | =MATCH(ROW()-1,D:D,0) | =IF(ISERROR(F4),"",INDEX(C:C,F4)) | |
| 5 | 29 | 営業部 | 平山 | =COUNTIF(B$2:B5,$D$1) | =MATCH(ROW()-1,D:D,0) | =IF(ISERROR(F5),"",INDEX(C:C,F5)) |
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | No. | 所属 | 名前 | 営業部 | MATCH | INDEX | |
| 2 | 16 | 営業部 | 田中 | 1 | 2 | 田中 | |
| 3 | 22 | 開発部 | 宮本 | 1 | 5 | 平山 | |
| 4 | 51 | 総務部 | 大村 | 1 | #N/A | ||
| 5 | 29 | 営業部 | 平山 | 2 | #N/A |
２つ解答を挙げてみたが、一長一短である。 式がわかりやすいのは断然解答２である。 しかし、解答２の場合、検索のための判断列をデータ側に用意する必要が出てきてしまう。 つまり、検索キーがデータ側に寄ってしまい分離がうまくいっていない感じがする。 解答１はそこはすっきりしていて、検索キーは抽出部側に寄っている。 どちらが良いかは、場面や作成者の好みによると思う。
余談だが、同じように範囲を操作できる関数としてINDIRECTがあるが、 OFFSETとINDIRECT、全然Excelの処理の速さが違う。たいしてやってることは変わらないと思うのになぜ。
