最近思ったこと
ちかごろ、営業チームからエンジニアチームへspreadsheetに関する相談が増えてきました。
- 〜な一覧データをspreadsheetで出してほしい
- このシートのAAAという値の絞り込んだ表がほしい
- 昨日出した一覧シートXと今日出した一覧シートYのデータを組み合わせた集計結果が知りたい...などなど
よくよく考えると、エンジニアチームが何かしらのデータをDBから加工抽出してデータのcsv吐かないといけないのは最初の1回だけで、残りのほとんどがやり方さえ覚えればspreadsheetの操作で完結できるものでした。
良い機会だったので、表計算芸に不慣れなメンバーにも覚えて欲しい小ネタについてまとめてみました。
はじめに
最強なのは公式ドキュメントです。↓ 
Google スプレッドシートの関数リスト
イロハのイ
セルとは
表のマス目のことを「セル」と呼びます。
また、行列のアルファベットと数字を組み合わせて各セルを「A1」「C2」などと呼びます。
先頭行、先頭列の固定化
左上にある灰色の太い線はドラッグ&ドロップで動かすことができます。
太い線より上/左のセルはスクロールしても動かなくなります。
ヘッダー行などを見やすくするのに便利です。
値を検索する
キーボードショートカットのCtrlとFを同時押しすると検索ボックスが出てきて、値を入力できます。ヒットしたセルはハイライトされます。
Macならcommand+Fです。
選択しているセルの合計値を知る
ドラッグしてセルを範囲選択すると、右下にセレクトボックスが現れて合計値とかをパッと知ることができます。他にも個数とかも選べます。
一気に表の末尾のセルに飛ぶ
command(windowsだとcontrol?)を押しながら矢印キーで、値が埋まっている領域の端まで飛ぶことができます。
一気に範囲選択する
ついでにshift+command+矢印キーで全体の範囲選択ができます。
特定の文字列の入ったセルに色をつける
色をつけたい領域を範囲選択 > 右クリック > 条件付き書式、から「条件付き書式設定ルール」を開くことができます。
ここから値の大小や一致などの条件によってセルのスタイルを変更するルールを設定できます。
セルの操作
セルを参照して計算する
セルに半角で「=」と入力すると、そのあとの記入内容は数式として扱われます。
「= A1」 とすると、単純にA1セルを参照している意味合いとなります。
数字の場合は参照を使って四則演算をすることもできます。
たとえば「= A1 + A2」とすると、A1セル(数字)とA2セル(数字)の足し算結果が得られます。
参照なので、参照元セルの値が変わると、再計算されます。
連続した値を入れる
「1」「2」の値が入った次のセルは「3」が入って欲しいですね。
「1」「2」のセルを範囲選択し、右下の部分を掴んでドラッグすると、連続値を入れてくれます。
日付でも同様です。
固定位置のセルの値を参照
計算などでも連続した値を入れることはできるのですが、その際に2列のセルを使っていると、連続値はどちらのセルも1つずつずらして参照してくれます。
ずらすのでなく、とあるセルだけ固定で参照させたい場合は、「$A$1」と表記すれば可能です。
以下のgifは、次の2ケースを試してみたものです。
- 「
= A2 * B2」として連続コピーして、次の値が「= A3 * B3」となってしまって計算が失敗するケース - 「
= $A$2 * B2」として連続コピーして、次の値が「= $A$2 * B3」 となって意図通りに計算できるケース
関数を使う(基本編)
表計算ソフトには便利な計算を行ってくれる関数がたくさん存在します。
いくつかピックアップして紹介したいと思います。
範囲の書き方
関数では計算する値の範囲を左上のセル:右下のセルという書き方で表します。
A2:C4 と書くと、A列2行目~C列4行目の9つのセルの範囲を指します。
B:B と書くとB列全てを選択していることになります。
2:2 と書くと2行目の全てを選択していることになります。
SUM / 値の合計を得る
=SUM(B2:B10) と入力すると、 B2:B10 の範囲の数値の合計値を知ることができます。
COUNTIF, COUNTUNIQUE / いくつあるか数える
=COUNTIF(範囲, 条件) と書くことで、条件に合致するセルの数を知ることができます。
条件を "*巻き" のように書いたとき、この *(アスタリスク)はどんな文字が入っていても良い(ワイルドカード)ということを示します。そのため、"カッパ巻き" "鉄火巻き" などの値を検索することができます。
また、 & は文字列と文字列をくっつける役割を果たします。条件に ">=" & E11 と書かれていた場合は、E11のセルに入っている値（例では300)とくっついて ">=300" という条件を表現しています。
=COUNTUNIQUE(範囲) は範囲の中で重複しない値の数を数えます。
例でいうと、お皿の色は 赤/緑/青/黒/金 の5種類なので、5と判断されます。
UNIQUE / 重複しない値を得る
=UNIQUE(範囲) と記入すると、範囲の中で重複しない値を抽出してくれます。
IF / 条件によって値を出し分ける
=IF(条件, "o", "x") と書くと、条件が成立したときにo、不成立のときにxが表示されます。もちろん、o、 x のところは好きな文字列を書き込んで大丈夫です。
関数を使う（応用編)
VLOOKUP / 垂直方向で関連の値を検索する
=VLOOKUP(検索キー, 範囲, 列指定番号, 並び替え済みかどうか) で、該当の範囲から検索キーの値と同じ行にあるデータを取得してくることができます。
文章で書いてもピンときませんが、実際の活用法をみると一番お世話になっている関数かもしれません。
画像の C5 はVLOOKUPを利用し たまご の値段を 価格表から取得しています。
関数の中の値の意味を見ていくと、次のようになります。
=VLOOKUP(A5, $F$3:$G$7, 2, false)
↓
=VLOOKUP("たまご", 価格表の範囲, (価格表の範囲の)2(列目が欲しい), false)
検索したい"たまご"という値を、指定した価格表の範囲から探し、その範囲の 2(列目) のデータを取得してくると 120 となります。
最後に記入のあるfalseは 並び替え済みではない という意味合いです。ここをtrueにすると、対象範囲が並び替え済みであること前提で検索処理が走ります。万が一対象範囲の並びがおかしいと、意図通りの値が取れない場合がありますので、falseと書いておくほうが無難です。
VLOOKUPを使う上で重要なのが検索対象のデータを範囲の先頭列に置いておくことです。上記の例の価格表自体の先頭列はIDですが、メニュー名で調べたかったので、VLOOKUPで指定している範囲はあえてメニュー列が先頭になるようにしています。
IFERROR / エラーのときに指定の値を出す
もしVLOOKUPで調べた値が価格表になかったとき、関数は #N/A (該当なしのエラー)を返します。
エラーが出るということ自体が 価格表に値が存在しない ことを示す重要な結果ではあるのですが、もっとわかりやすい表示にしたいと思う場合もあります。その時は次の関数が利用できます。
=IFERROR(エラーが出るかもしれない値, "エラーが出た時の表示")
この関数は、エラーが出るかもしれない値 を確認して、
エラーがでなければそのまま値を、エラーが出たら "エラーが出た時の表示" を返します。
上記例では VLOOKUP を入れ子にして、エラーが出たところに 価格なし を表示させるようにしています。
注文記録と価格表から支払価格を計算する(総合編)
これまで紹介してきた関数などを活用することで、例えば、「メニュー/注文個数」と「メニュー/価格]の別々の表があったときに、お会計の金額を計算することができます。
空欄を埋めてみます。
VLOOKUP で求めた単価と注文個数を掛け算して（ B3*C3 など ）、D列の結果を合計(SUM)することでお会計金額を求めることができました。
余談
別のシートを参照する
例では見やすくするために意味合いの違う表を1シートに詰めてしまいましたが、
本当は別々のシートに分割しておく方がわかりやすいです。
セルを参照する際に、 シート名!A1 などのように書くことで、そのシート名のA1セルの値を参照することができます。
なので、総合編の例として出した 注文記録 価格表 は別シートに分けてもまったく問題ありません。
フィルタを使う
メニューバーにある漏斗のアイコンから「フィルタ」を使うことができます。
フィルタではデータの絞り込みや並べ替えを行うことができます。
▼ フィルタで特定の値の項目のみに絞り込んで表示
▼ A→Zで並べ替え
おわりに / 公式ドキュメントを確認しよう
大切なことなのでもう一度言いますが、最強なのは公式ドキュメントです。↓
紹介した全ての関数は公式ドキュメントにうまくまとまっていますし、使い方もきちんと書かれています。
Google スプレッドシートの関数リスト
便利なのでぜひ活用してください！