Notエンジニアなメンバーに知ってほしいスプレッドシートのイロハ

# 最近思ったこと

ちかごろ、営業チームからエンジニアチームへspreadsheetに関する相談が増えてきました。

* 〜な一覧データをspreadsheetで出してほしい
* このシートのAAAという値の絞り込んだ表がほしい
* 昨日出した一覧シートXと今日出した一覧シートYのデータを組み合わせた集計結果が知りたい...などなど


よくよく考えると、エンジニアチームが何かしらのデータをDBから加工抽出してデータのcsv吐かないといけないのは最初の1回だけで、残りのほとんどがやり方さえ覚えればspreadsheetの操作で完結できるものでした。
良い機会だったので、表計算芸に不慣れなメンバーにも覚えて欲しい小ネタについてまとめてみました。

# はじめに
最強なのは公式ドキュメントです。↓ :sparkles: 
[Google スプレッドシートの関数リスト](https://support.google.com/docs/table/25273)


# イロハのイ

## セルとは
表のマス目のことを「セル」と呼びます。
また、行列のアルファベットと数字を組み合わせて各セルを「A1」「C2」などと呼びます。
![スクリーンショット 2017-10-01 15.43.24.png](https://qiita-image-store.s3.amazonaws.com/0/24870/f590227b-4e17-d909-8569-9d8deb146280.png)


## 先頭行、先頭列の固定化
![sheet_header.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/16ea8e2f-7be8-9bad-1c65-fa48bd51baee.gif)

左上にある灰色の太い線はドラッグ&ドロップで動かすことができます。
太い線より上/左のセルはスクロールしても動かなくなります。
ヘッダー行などを見やすくするのに便利です。


## 値を検索する
キーボードショートカットのCtrlとFを同時押しすると検索ボックスが出てきて、値を入力できます。ヒットしたセルはハイライトされます。
Macならcommand+Fです。

![スクリーンショット 2017-10-01 15.49.41.png](https://qiita-image-store.s3.amazonaws.com/0/24870/59da39f7-a190-cd43-cb4e-50a0aba77e06.png)


## 選択しているセルの合計値を知る
ドラッグしてセルを範囲選択すると、右下にセレクトボックスが現れて合計値とかをパッと知ることができます。他にも個数とかも選べます。
![sheet_count.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/3bb9f6ce-2cf1-ee94-c834-d7a853c55942.gif)

## 一気に表の末尾のセルに飛ぶ
command(windowsだとcontrol?)を押しながら矢印キーで、値が埋まっている領域の端まで飛ぶことができます。

![sheet_jump.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/3eda55f9-6688-2629-72c5-be93ea564bcd.gif)

## 一気に範囲選択する
ついでにshift+command+矢印キーで全体の範囲選択ができます。
![sheet_all_select.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/127af9bd-aeb6-6225-80f1-d62827897a88.gif)

## 特定の文字列の入ったセルに色をつける
色をつけたい領域を範囲選択 > 右クリック > 条件付き書式、から「条件付き書式設定ルール」を開くことができます。
ここから値の大小や一致などの条件によってセルのスタイルを変更するルールを設定できます。

![スクリーンショット 2017-10-02 9.58.58.png](https://qiita-image-store.s3.amazonaws.com/0/24870/cb93a4ce-fe47-0de6-ae5d-1fa2b9d09e51.png)



# セルの操作
## セルを参照して計算する
セルに半角で「`=`」と入力すると、そのあとの記入内容は数式として扱われます。
「`= A1`」 とすると、単純にA1セルを参照している意味合いとなります。
数字の場合は参照を使って四則演算をすることもできます。
たとえば「`= A1 + A2`」とすると、A1セル(数字)とA2セル(数字)の足し算結果が得られます。
参照なので、参照元セルの値が変わると、再計算されます。

![sheet_cell_calc.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/b3574938-f04b-adb8-8089-3da2e6354fe1.gif)


## 連続した値を入れる
「1」「2」の値が入った次のセルは「3」が入って欲しいですね。
「1」「2」のセルを範囲選択し、右下の部分を掴んでドラッグすると、連続値を入れてくれます。
日付でも同様です。
![sheet_full.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/2eb703b1-c599-1517-3c8f-4b05770de43c.gif)


## 固定位置のセルの値を参照
計算などでも連続した値を入れることはできるのですが、その際に2列のセルを使っていると、連続値はどちらのセルも1つずつずらして参照してくれます。
ずらすのでなく、とあるセルだけ固定で参照させたい場合は、「`$A$1`」と表記すれば可能です。
以下のgifは、次の2ケースを試してみたものです。

-  「`= A2 * B2`」として連続コピーして、次の値が「`= A3 * B3`」となってしまって計算が失敗するケース
-  「`= $A$2 * B2`」として連続コピーして、次の値が「`= $A$2 * B3`」 となって意図通りに計算できるケース

![sheet_calc_continue.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/a0321d3e-f1c9-41ad-50ac-cb18685e608d.gif)



# 関数を使う(基本編)
表計算ソフトには便利な計算を行ってくれる関数がたくさん存在します。
いくつかピックアップして紹介したいと思います。

## 範囲の書き方
関数では計算する値の範囲を`左上のセル:右下のセル`という書き方で表します。

`A2:C4` と書くと、A列2行目~C列4行目の9つのセルの範囲を指します。
![スクリーンショット 2017-10-03 12.33.12.png](https://qiita-image-store.s3.amazonaws.com/0/24870/71de4712-89a8-544b-17e2-24726bba74d1.png)


`B:B` と書くとB列全てを選択していることになります。
![スクリーンショット 2017-10-03 12.32.31.png](https://qiita-image-store.s3.amazonaws.com/0/24870/f55e9cc6-c30a-e55a-ac09-8f98060544d8.png)


`2:2` と書くと2行目の全てを選択していることになります。
![スクリーンショット 2017-10-03 12.33.43.png](https://qiita-image-store.s3.amazonaws.com/0/24870/e60074d3-1325-3d8f-2452-de9e2a00857b.png)


## SUM / 値の合計を得る

`=SUM(B2:B10)` と入力すると、 `B2:B10` の範囲の数値の合計値を知ることができます。

![sheet_sum.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/ae3ca6c4-e979-5036-8a36-2c225ee3b6ef.gif)


## COUNTIF, COUNTUNIQUE / いくつあるか数える

`=COUNTIF(範囲, 条件)` と書くことで、条件に合致するセルの数を知ることができます。
条件を `"*巻き"` のように書いたとき、この `*`(アスタリスク)はどんな文字が入っていても良い(ワイルドカード)ということを示します。そのため、`"カッパ巻き"` `"鉄火巻き"` などの値を検索することができます。
また、 `&` は文字列と文字列をくっつける役割を果たします。条件に `">=" & E11` と書かれていた場合は、E11のセルに入っている値（例では`300`)とくっついて `">=300"` という条件を表現しています。

`=COUNTUNIQUE(範囲)` は範囲の中で重複しない値の数を数えます。
例でいうと、お皿の色は 赤/緑/青/黒/金 の5種類なので、`5`と判断されます。

![スクリーンショット 2017-10-03 23.29.17.png](https://qiita-image-store.s3.amazonaws.com/0/24870/75f124c3-e46c-95e0-25e4-884cf4030fdb.png)

## UNIQUE / 重複しない値を得る
`=UNIQUE(範囲)` と記入すると、範囲の中で重複しない値を抽出してくれます。
![スクリーンショット 2017-10-03 23.45.21.png](https://qiita-image-store.s3.amazonaws.com/0/24870/ecffd9a6-6622-956d-7179-990f2579500e.png)

## IF / 条件によって値を出し分ける
`=IF(条件, "o", "x")` と書くと、条件が成立したときに`o`、不成立のときに`x`が表示されます。もちろん、`o`、 `x` のところは好きな文字列を書き込んで大丈夫です。

![スクリーンショット 2017-10-05 23.18.24.png](https://qiita-image-store.s3.amazonaws.com/0/24870/3d94f47a-01d3-2825-acd7-fb415a123376.png)


# 関数を使う（応用編)

## VLOOKUP / 垂直方向で関連の値を検索する
`=VLOOKUP(検索キー,　範囲, 列指定番号, 並び替え済みかどうか)` で、該当の範囲から検索キーの値と同じ行にあるデータを取得してくることができます。
文章で書いてもピンときませんが、実際の活用法をみると一番お世話になっている関数かもしれません。
![スクリーンショット 2017-10-06 0.08.31.png](https://qiita-image-store.s3.amazonaws.com/0/24870/7cdda471-ec05-b610-7e56-fd48ff147af9.png)

画像の `C5` はVLOOKUPを利用し `たまご` の値段を **価格表から取得しています。**
関数の中の値の意味を見ていくと、次のようになります。

```
=VLOOKUP(A5, $F$3:$G$7, 2, false)

↓

=VLOOKUP("たまご", 価格表の範囲, (価格表の範囲の)2(列目が欲しい), false)
```

検索したい`"たまご"`という値を、指定した`価格表の範囲`から探し、その範囲の `2(列目)` のデータを取得してくると `120` となります。
最後に記入のあるfalseは 並び替え済み**ではない** という意味合いです。ここをtrueにすると、対象範囲が並び替え済みであること前提で検索処理が走ります。万が一対象範囲の並びがおかしいと、意図通りの値が取れない場合がありますので、falseと書いておくほうが無難です。

![解説.png](https://qiita-image-store.s3.amazonaws.com/0/24870/f5335e90-098a-78df-16d8-038374f2c7ec.png)

VLOOKUPを使う上で重要なのが**検索対象のデータを範囲の先頭列に置いておく**ことです。上記の例の価格表自体の先頭列はIDですが、メニュー名で調べたかったので、VLOOKUPで指定している範囲はあえてメニュー列が先頭になるようにしています。


## IFERROR / エラーのときに指定の値を出す
もしVLOOKUPで調べた値が価格表になかったとき、関数は `#N/A` (該当なしのエラー)を返します。
![スクリーンショット 2017-10-06 0.47.41.png](https://qiita-image-store.s3.amazonaws.com/0/24870/e956004f-a6a2-e6ca-8163-89cbacb76fc6.png)

エラーが出るということ自体が `価格表に値が存在しない` ことを示す重要な結果ではあるのですが、もっとわかりやすい表示にしたいと思う場合もあります。その時は次の関数が利用できます。

```
=IFERROR(エラーが出るかもしれない値, "エラーが出た時の表示")
```

この関数は、`エラーが出るかもしれない値` を確認して、
エラーがでなければそのまま値を、エラーが出たら `"エラーが出た時の表示"` を返します。

![スクリーンショット 2017-10-06 0.48.24.png](https://qiita-image-store.s3.amazonaws.com/0/24870/d1ab4c31-2304-11c6-4434-282a14bb4900.png)

上記例では VLOOKUP を入れ子にして、エラーが出たところに `価格なし` を表示させるようにしています。


# 注文記録と価格表から支払価格を計算する(総合編)
これまで紹介してきた関数などを活用することで、例えば、「メニュー/注文個数」と「メニュー/価格]の別々の表があったときに、お会計の金額を計算することができます。

![スクリーンショット 2017-10-06 1.24.02.png](https://qiita-image-store.s3.amazonaws.com/0/24870/21f5f2c8-2ec3-00e0-6493-ef4f2a0835d6.png)

空欄を埋めてみます。

![sougou.png](https://qiita-image-store.s3.amazonaws.com/0/24870/93b0f3ee-a9f8-2cca-a05d-b167705e1d66.png)

`VLOOKUP` で求めた単価と注文個数を掛け算して（ `B3*C3 など` ）、D列の結果を合計(`SUM`)することでお会計金額を求めることができました。

# 余談
## 別のシートを参照する
例では見やすくするために意味合いの違う表を1シートに詰めてしまいましたが、
本当は別々のシートに分割しておく方がわかりやすいです。
セルを参照する際に、 `シート名!A1` などのように書くことで、そのシート名のA1セルの値を参照することができます。

![other_sheet.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/46e85a3e-7924-4fa9-d1b5-c028045e15cd.gif)

なので、総合編の例として出した `注文記録` `価格表` は別シートに分けてもまったく問題ありません。

![スクリーンショット 2017-10-06 12.53.58.png](https://qiita-image-store.s3.amazonaws.com/0/24870/6d794f9b-b27d-74e8-640f-3779f1017a0e.png)

![スクリーンショット 2017-10-06 12.54.18.png](https://qiita-image-store.s3.amazonaws.com/0/24870/d5d6ad14-3b07-9f59-ad82-a5802d0f17b6.png)


## フィルタを使う
メニューバーにある漏斗のアイコンから「フィルタ」を使うことができます。
フィルタではデータの絞り込みや並べ替えを行うことができます。


▼ フィルタで特定の値の項目のみに絞り込んで表示
![filter.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/4c7efd00-7c3d-ed43-d622-d4f707196c18.gif)

▼ A→Zで並べ替え
![filter1.gif](https://qiita-image-store.s3.amazonaws.com/0/24870/e203a03b-93c3-b857-f897-fd47aa47459b.gif)



# おわりに / 公式ドキュメントを確認しよう
大切なことなのでもう一度言いますが、最強なのは公式ドキュメントです。↓ :sparkles: 
紹介した全ての関数は公式ドキュメントにうまくまとまっていますし、使い方もきちんと書かれています。
[Google スプレッドシートの関数リスト](https://support.google.com/docs/table/25273)
便利なのでぜひ活用してください！