仕事が10倍捗るスプレッドシート神関数10
こんにちは。Tokyo Otaku Modeの安宅です。先日から、とあるプロジェクトにジョインして、久々にGoogleスプレッドシートをゴリゴリ活用して仕事をしています。そんな中、これは惚れた！というスプレッドシートの神関数を紹介したいと思います。
ARRAYFORMULA関数
これを活用し始めてから、関数をいちいち全部の行に埋め込んでいくことが、なんだかカッコ悪く感じるくらい、スマートでオシャレな関数です。例えば、100行のVLOOKUP関数を行うときには、これまではVLOOKUP関数を1つ作って、それを残り99個の行にVLOOKUP関数をコピーしていたと思いますが、ARRAYFORMULA関数を使うと、なんと1個の関数で100行分の値が出力されます。実際に動作も軽くなるのでカッコよさと実用性を兼ね備えた関数です。配列に対応する関数であれば、ほぼなんでも利用できるのも好感度が高いです。
B2セルに注目。わずかひとつのVLOOKUPでB列全体の出力が完了
このシートを確認する
COLUMNS関数
これ単体だけで見ると、列数を返すだけでシンプルな関数なのですが、これをVLOOKUP関数と組み合わせると・・・美しい！VLOOKUP関数の弱点として、値を引っ張ってくるときに「何列目」を数字で指定しますが、ここをCOLUMNS関数にすることで、後々、マスターデータの列数が増減した時にも、自動的に列数が増減してくれるので、いちいちVLOOKUP関数の列数を変更する必要がなくなります。また、引き出したい値が何行目かを目で数えたりする手間暇も自動化されるので、単純な数え間違いも起こらなくなります。
こんな表で仮にDとE列の間に列が増えても、COLUMNS関数を使えばVLOOKUP関数の出力はノープロブレム
このシートを確認する
IFERROR関数
この関数は表の値が完ぺきでないときにとても役に立つ関数です。VLOOKUP関数で引っ張ってきた値を、SUM関数で合計金額を出す時に、1つでも値が#N/Aが出てしまうと、合計金額が計算できなくなりますよね。IFERROR関数で#N/Aなどのエラーが出た時は0と設定しておけば、合計金額がそのまま出力される、などの使い方があります。
なにはともあれIFERROR関数をかましておくと2ステップ以上の計算時に便利
このシートを確認する
TRANSPOSE関数
表の列と行を関数で転置させることができる関数です。少しマニアックですが、知っていると意外と使いたいシーンがでてきます。
なにかしらの制約があり、どうしても表の列と行を変えたいシーンに便利
このシートを確認する
IMPORTXML関数
これを知ってから、Webスクレイピングをするのにプログラム言語をいちいち使わなくても、簡単なものであればサクッとできるようになりました。例えば自社サイトのプレスリリース記事のURLを一発で正確に出したい時に、こんな形で取得できます。
2つ目の項目に設定する「XPath」は慣れるまで少し手こずるかもしれませんが、知っておいて損のない関数です
このシートを確認する
INDEX関数
配列の行と列を指定したものだけを出力する関数で、配列を返すIMPORTXML関数と組み合わせると、非常に使い勝手が良いです。ここでの例は、さきほど出力したプレスリリースのURLの内、最新の記事（つまり1行1列のデータ）を引っ張ってきています。ここでは元の配列の列数は1列しかありませんが、2行以上の列や行も指定して出力できます。
IMPORTXML関数で、この行と列を指定して出力したい、という場面で役立ちます
このシートを確認する
ROW関数
スプレッドシートで表を作るときに、ナンバーを付けたいシーンは、わりと多いと思います。ただ、普通にナンバーを値で埋めてしまうと、行数を追加したり、順番を入れ替えると、いちいち正しい数字を入れなおす手間がかかってしまうと思います。そこでROW関数の出番です。行数を返すこの関数を使うと、簡単にナンバリングできるようになります。例えば、1行目は項目名として、2行目からナンバリングをするとしたら、「=ROW()-1」と指定すると、2行目に「1」が出力されます。これを行数分コピペでナンバリングするのもよし、先に紹介したARRAYFORMULA関数を組み合わせると、たった1セルでナンバリングが完成します。
※ ROW関数は変動するので固定のナンバーで管理したい場合には不向きです
ROW関数とARRAYFORMULA関数を使えば、セル1つで手間いらずのナンバリング
このシートを確認する
ここから先は、以前、拙ブログでも紹介した関数ですが、活用シーンが非常に多く、スプレッドシートの大きな魅力だと思いますので、改めて紹介したいと思います。
IMPORTRANGE関数
外部のスプレッドシートの1シートをすべてのセル、もしくは特定の範囲で絞り込んで別のスプレッドシートに読み込むことができる関数です。
言葉で説明するより、こちらを見ていただくほうが一目瞭然です。
こちらのシートを、
このシートを確認する
こちらのシートに呼び出しています。
このシートを確認する
1シート200万セルというスプレッドシートのセル数の限界を突破できるのと、ログデータなどのマスターシートをさまざまなシートで逐一更新する必要がなく、元になるマスターシートを1回更新するだけで、あらゆるシートが自動的に更新されるのです。使い方はまさに無限大∞。個人的にはExcelを習いたての時にVLOOKUP関数を知った時以上の感動がありました。
UNIQUE関数
こちらもスプレッドシートならではの関数で、特定の行でダブリがあるリストをユニークなリストにまとめてくれます。
FILTER関数
この関数を使うと、元のシートの特定のセルを条件にリストを絞り込んで表示できます。
スプレッドシートにデフォルトで付いている「フィルタ」機能と同様の動きをするのですが、マスターシートを「フィルタ」で絞り込むと、複数人で作業する場合に非常に厄介ですよね。そんなときに、別スプレッドシートでIMPORTRANGE関数を使ってマスターシートを読み込み、FILTER関数で見たいデータを絞り込んでおくと、確認したいデータをすぐに表示できます。
Tokyo Otaku Modeはまだまだ駆け出しのスタートアップで、こうした便利なツールを最大限に活用するためのノウハウを研究、共有しながら、僕らのビジョンである「オタク文化で世界をハッピーに」を、一歩一歩実現させようとしています。ご興味のある方は、こちらからご連絡ください！
今回の記事の中で１つでもみなさまのお役に立てたら幸いです。それでは！
