本記事はマイネット Advent Calender16日目の記事です。
今回はプログラマー暦1年6ヶ月なのに成長が全く感じられない@hashiがお送りさせていただきます。
はじめに
みなさん、日々の業務やプライベートの中でExcelやGoogle SpreadSheetをよく使用されると思います。
これの便利なところはなんといっても数式に関数を入れることで自動的に計算結果を表示したり集計を行ったりしてくれることだと思います。
弊社のようなソーシャルゲームの運営に携わる会社としてはDAUやARPPUなど様々なKPIを集計し資料を作成することで施策の方針やテコ入れなど様々な判断材料をする際の大きな道標になります。
そんな時、ExcelやGoogle SpreadSheetには非常に便利なツールとして我々の手助けをしてくれます。
今回は、実際に集計作業を行っていた時に「これができたら便利なのに」と思ったことと、それをどのようにして関数を使って実現したかをお話できればと思います。
vlookup関数の限界
ExcelやGoogle SpreadSheetを使っている方にとって非常に馴染みの深い関数「vlookup」
ご存知の通り
[検索したい値] を
[検索範囲の一番左の列] から縦に検索して
[一致した行の左からN番目にある値を取得する]
という関数ですね。
ただ、この関数「右からN番目にある値を取得」することはできるんですが、逆の
「左からN番目にある値を取得」ということが一番左の列を検索する仕様上できません。
そのためvlookup関数が使えるように列の並び順を変えたりしないといけません。
個人で使用する資料であればいいかもしれませんが
業務などで大人数が編集をすることのある資料の場合は
おいそれと列や行をいじるわけにもいきません。
なので既存の関数をうまく組み合わせて
[検索したい値] を
[検索範囲の一番右の列] から縦に検索して
[一致した行の右からN番目にある値を取得]
ができるようにやってみました。
数式を作ってみた
- index関数
- match関数 この2つの関数を組み合わせて実現しました。
ちなみに
index関数
[指定した範囲] の
[上からN番目の行] と
[左からN番目の列] を指定すると
一致する箇所の値を取得
してくれる関数です。
match関数
[検索したい値] を
[１行または１列で指定された検索範囲] の中から検索して
一致した場所が検索範囲の何番目（行の場合は上から、列の場合は左から数えた値）にある値かを返す
関数です。
なので
=index(検索範囲, match(検索したい値, 検索したい列, 0), 左から数えて何番目か)
※match関数内の0は完全一致で検索させるためのオプションです
とう感じに
ますはmatch関数を使用して「検索したい値」がどこの行に存在するか算出します。
あとはindex関数で検索範囲を指定して算出した行の値を目印にして最終的に抽出したい値が列の何番目かを指定します。
そうすれば一致した行の左からN番目の値を算出することができるようになります。
例えば
| A | B | C | D | |
|---|---|---|---|---|
| 1 | id | 名前 | 性別 | 部署ID |
| 2 | 1 | 孫 太郎 | 男 | C |
| 3 | 2 | 田中 ミク | 女 | D |
| 4 | 3 | 初音 悟空 | 女 | F |
（記入されている名前はアニメや実際の人物の名前とは全く関係ありません）
というテーブルがあったとして
部署ID（例えば部署ID：D）から該当する名前を算出したいとなった場合、vlookupでは検索できませんね？
そんな時こそ先ほどの数式の出番です。
部署IDをもとに名前を抽出したいので
=index(B2:D4, match(D3, D2:D4, 0), 1)
これで部署IDに応じた名前を抽出することができます。
ちなみに性別を抽出したい場合は
=index(B2:D4, match(D3, D2:D4, 0), 2)
idを抽出したい場合は少し検索範囲が変わります
=index(A2:D4, match(D3, D2:D4, 0), 1)
このように検索する列の左側にある値を抽出することができるようになりました。
おわりに
いかがでしたでしょうか？
正直、ネットで調べればすぐに出てくる情報だったのですが最初知った時、個人的にはすごく革命的だと感じました。
関数の可能性に驚きを隠せなかったです。
ただ、一つだけ問題があり「数式が少し複雑なのでメンテナンスが大変な場合がある」ということです。
ある程度、関数を理解しているのであれば大丈夫かもしれませんが、初心者の人が最初に見た時は必ず固まると思います（多分、自分もいきなり見せられたら半べそかくと思います）。
なので数式を組み合わせて表現するのもいいですが、もし可能であればvlookup関数だけなど1つの関数で済む列の構成にするなど、できる限り誰でも安全に運用できるような配慮をしたほうがいいと思います。
そんな中でどうしてもという時に、少しでもこの記事が参考になれば幸いです。
さてさて明日は@jozakiさんになります。
引き続きマイネット Advent Calendarをお楽しみくださいませ。
ありがとうございました。