業務がスプレッドシート芸人兼GoogleAppScript芸人と化しつつある最近です。こんにちは。
と言いつつまだ全然使いこなせてないのですけど、最近のスプレッドシートの学びをメモついでに。
たとえば、
| date | page | pv |
|---|---|---|
| 2015/05/01 | top | 10 |
| 2015/05/02 | top | 15 |
| 2015/05/02 | entry | 10 |
| 2015/05/03 | top | 20 |
| 2015/05/03 | entry | 15 |
こんなマスタデータがあったとして、これを保ちつつ、
| 2015/05/01 | 2015/05/02 | 2015/05/03 | |
|---|---|---|---|
| top | 10 | 15 | 20 |
| entry | - | 10 | 15 |
こんな具合に出力したシートを作りたいというケースを考えてみます。
support.google.com
単純にフィルタしたいだけなら同シートでフィルタ追加すればいい話なのですけど、別シートでごにょる場合はFILTER関数が便利です。
まずは以下のような状態を用意
| 2015/05/01 | 2015/05/02 | 2015/05/03 | |
|---|---|---|---|
| top | |||
| entry |
マスタデータのシートを「source」としたとき、topと書いてある横のセルにぶっこむ数式は以下のようにします。
=FILTER(source!$C:$C,source!$B:$B=$A2,source!$A:$A=B$1)
ざっくり言うと、第一引数は表示に使用する範囲を示します。今回は別シートから参照するので範囲の前に「source!」とついてます。
第二引数と第三引数は条件です。双方に合致したものが表示されるような形になります。
エクセルやスプレッドシートを活用されてる皆々様には今更なアレだと思うのですけど、「$」は絶対参照を示すものです。
例えばB1を含む数式を埋め込んだとき、下のセルにコピペするとB2に、右のセルにコピペするとC1に置き換わります。こちらは相対参照です。
これを防ぐには、置き換えてほしくない行または列を示すものの前に「$」を入れる必要があります。
まとめると、
- 縦方向の変更を許容しない：B$1
- 横方向の変更を許容しない：$B1
- 縦横両方向の変更を許容しない：$B$1
となるわけです。
上に記載したフィルタは他のセルにもコピペする想定で必要な部分を絶対参照にしているので、これを他のセルにもコピペすると概ね要件通りの表ができあがります。わーい。
が、概ねと言った通り完璧じゃありません。何が完璧じゃないかというと、2015/05/01のentryのデータがないので、「#N/A」と言われてしまいます。
今は欠如したデータが1つなのでまだいいですけど、たくさんあるととても悲しい気持ちになるので、エラー時のハンドリングをしたいところです。
そんなときに便利なのがIFERRORさんです。
support.google.com
というわけでこんな具合にしてみると今度は完璧です。わいわい。
=IFERROR(FILTER(source!$C:$C,source!$B:$B=$A3,source!$A:$A=B$1),"-")
第二引数はエラー時に置き換える文字で、省略可能です。省略すると何も出ません。
ちなみに今回はFILTERの結果が1件になるような形で進めているのですけども、たとえばこのFILTERが
=FILTER(source!$C:$C,source!$B:$B=$A2)
だとした場合、＄A2は「top」を示すので、結果は3件表示されます。
これはこれで便利なのですけど、マスタデータが縦だとやっぱりこの結果も縦で表示されます。
でも僕は横で表示したいんだ…縦は嫌なんだ…というときはTRANSPOSEさんが待ち構えています。スプレッドシートさんすごい。なんでもある。
support.google.com
例えばFILTERと組み合わせる場合はこんな具合です。
=TRANSPOSE(FILTER(source!$C:$C,source!$B:$B=$A2))
もしくは元データを行列入れ替えてしまうという手もあります。
範囲をコピーして右クリック、「特殊貼り付け」＞「転置して貼り付ける」を選択するとあら不思議。
行列が入れ替わったデータが出来上がります。すてき！抱いて！
あとFILTERに似た用途で使えるものとして、QUERYさんがいます。
support.google.com
この存在を同僚に聞いたとき、えっ。えっ？何言ってんの？エイプリルフール？という気持ちになったのですけど、そんなこともなくGoogleVisualizationAPIのクエリ仕様に則ったクエリを書くことでデータを抽出することができます。
もう一体何ができないのという気持ちすらあって戸惑う毎日ですが、引き続きスプレッドシート芸を磨いていきたい所存です。
取り急ぎ全セルにコピペするとかダルかったのでその解決方法を模索したい今日この頃です。
