こんにちは、kadota です。 この記事は actindi Advent Calendar 2018 の15日目の記事です。
普段、PCおじさんとして管理表をいじったり、フォームの回答を集計したり、調べたものを一覧にするなど、Googleスプレッドシートのお世話になっています。
今回は（今回も？）、スプレッドシートの小ネタを書いてみます。
どちらかというと、あまりスプレッドシートに慣れてない方むけです。
リスト同士を比較して、印をつけたい
例えば、各条件毎に該当するもののリストを持っているとして、それをシート上で比較してみたい場合があるとします。
- リストA = A列に流し込み
- リストB = B列に流し込み
そんな場合に印をつける方法です。
※前提としてリストに重複がなく、リストBはリストAに包含されるものとします。
※画像の例は、リストAが、私が娘と行くお出かけ先のいくつか、リストBが、なかでも娘が好きな水族館です。
リストBに存在するものを拾い出す
C列の、見出し行を除いた頭(C2)に次の数式を入れ、セルの右下をつまんでぐいっと下にドラッグして、ドラッグ範囲のセルにいっぺんにコピーします。
=IF(COUNTIF($B$2:$B,A2)=1,"○",)
「あるAがリストBに含まれる」→含まれるものに○印を付ける、というやり方です。
補足：セルの指定方法について
数式でのセルの指定は A1, A2 が 個別のセル。A のみでA列全体を意味します。
ドル記号のついた $B$1, $B$2 は、絶対参照といって、ほかのセルにコピーされても位置が調整されません。
ドル無しのほうは、ほかのセルにコピーされるとコピー元のセルから相対的に位置が調整されるので、A2のところがA3, A4... と変化してコピーされます。
上記の数式では、リストBの範囲を固定し、あるA（リストAの特定セル）は相対で指定してます。
リストBに存在しないものを拾い出す
これもD列に連続して次の数式を入れます。
=IF(COUNTIF($B$2:$B,A2)=0,"✗",)
「あるAがリストBに含まれない」→含まれないものに✗印を付ける、です。
条件と出力記号をちょっと変えるだけです。かんたんですね！
差分を詰めて表示したい
上の例を少しいじって、印ではなくAの中身を表示させれば差分のリストを作れます。
E列に連続して次の数式を入れます。
=IF(COUNTIF($B$2:$B,A2)=0,A2,)
ただこれだと見た目が歯抜けになってしまいますね。
この出力を「値のコピー」で別の列に出してから、ソートして詰めるというのも手ではありますが、これを数式で済ませたいときは、QUERY()で引っ張ってこれます。SQLライクで便利です。
ここでは、D列を利用して差分リストを出してみます。
F列の一番上(F2)のみに次の数式を入れます。
=QUERY(A:D, "select A where D='✗'")
かんたんですね！
複数条件での集計
別のシートに、コメントなど文字列が書き込まれている列があるとして、「何かが書かれているが、内容が "-" ではない箇所」の数を手軽に集計したいとします。
ただ「なにかが入ってるセル」をカウントするだけなら、COUNTA()を使えばよいのですが、この場合はCOUNTIFS()を使います。
=COUNTIFS(C2:C10, "*", C2:C10, "<>-")
COUNTIF()と似ていますが、条件を複数並べて絞り込むことができます。
文字列を扱う条件としては、
"*"または"<>"で「空ではないセル」"<>文字列A"で「文字列Aではないセル」"文字列A*"で「文字列Aから始まるセル」"*文字列A"で「文字列Aで終わるセル」"*文字列A*"で「文字列Aを含むセル」
など使うことができます。
IF()とは違って、条件を記号含めて文字列に書いて渡します。
これはQUERY()を使っても同じようなことができます。
=QUERY(A2:C10, "select count(C) where C<>'' and C<>'-'")
かんたんですね！
なにか参考になるところがあれば幸いです。
