###### tags: `LT`

# SS勉強会 2021

## はじめに

表計算ツールの
- 長期運用に向いている使い方
- マニアックだけど便利な関数

を紹介していきます。

個人的な意見が多いので基本参考程度に
読んでいただければと思います。

## ゴール

一つでも情報を持って帰ってもらって、
業務のエクセル/SSが改善されること。

## 長期運用に向いている表計算ツールの使い方

表計算 ≒ エクセル/スプレッドシート

表計算ツールは
便利な反面、すぐに情報が散乱したり、
属人化して更新し辛くなります。

長期運用するために必要な基礎部分を
自分なりにまとめました。

### 目的

目的：「長期での運用のしやすさ」

以下のような属性が必要

- 汎用性
- 使いまわせる
- 別の目的にも使える
- 保守性
- 直しやすい
- 可読性
- 誰でも理解できる
- すぐに理解できる
- 完全性/整合性
- 抜け漏れ/ミスが起き辛い

具体的になにに気をつけるべきかを以下で解説します。

### point1. 表計算ツールの入力箇所は最小限に

「ベタ書き、手動での更新を極限まで減らす」

関数で出せるものはすべて関数で出す

##### 理由

当たり前ですが、
"人為的な変更"がミスや抜け漏れの原因です。

ミス/抜け漏れを減らすには、全力で入力箇所を減らしましょう

### point2. シートの分け方

「データと作業場所を分ける」

表計算ツールは、
よくもわるくもデータ管理と作業環境が同じ場所で行える。

混同していくとどんどんカオスになるので
明確に分けると長期運用に大きなメリットとなる。

（DBと管理画面の関係性に似ている）

#### 考え方①

「データ/作業用・破損してもいいかどうか」
の指標でシートを分割する

| - | 壊れたらだめ | 壊れてもいい |
| -------- | -------- | -------- |
| データ | ① | ② |
| 作業用 | ③ | ④ |

1. マスターデータ
- 全ての元となるデータ一覧
- 元データに少し手を加えてマスターデータとする場合も。
- GAやリダッシュのデータをそのまま載せるイメージ
- 更新すれば他のシートも最新情報になるのが理想
- 管理者以外触らない方がいい
2. ビュー
- 一時的に利用するデータ一覧
- 作業用シートごと作ってもいい、作らなくてもいい
- ここで作業をしようとしてはいけない
3. 爆弾
- 「壊れちゃダメな作業シート」なんてものを
作ってはいけない
- 大体、事件の犯人はこいつ
- 理由：
- 作業用シートは、いつ誰が変更するかわかりません
- 消えたら作り直せばいい..くらいの存在にしておくと良い
4. 作業用シート
- 日々作業をするようなもの
- チェックリストやグラフ

#### 考え方②

ある程度、正規化する
（特に手入力するマスターデータは気をつける）

ざっくり言うと、情報を分けましょうということ。

例）

<details>
<summary>例</summary>

◯非正規形
| 商品 | 担当 | 店舗 |
| -------- | -------- | -------- |
| あんパン（2００円） | Aさん | 一号店 |
| しょくパン（５００円） | Bさん | 二号店 |
| カレーパン（3００円） | Aさん | 一号店 |
| しょくパン（５００円） | Cさん | 一号店 |

---

◯正規形

商品表：
| ID | 商品名 | 単価(円) |
| -------- | -------- | -------- |
| 1 | あんパン | 200 |
| 2 | しょくパン | 500 |
| 3 | カレーパン | 300 |

店舗表：
| ID | 店舗名 |
| -------- | -------- |
| 1 | 一号店 |
| 2 | 二号店 |

店員表：
| ID | 店舗ID | 店員名 |
| -------- | -------- | -------- |
| 1 | 1 | Aさん |
| 2 | 1 | Bさん |
| 3 | 2 | Cさん |

担当表（リレーションテーブル）：
| 店員ID | 商品ID |
| -------- | -------- |
| 1 | 1 |
| 2 | 2 |
| 1 | 3 |
| 3 | 1 |

</details>

正規化の詳しい説明は[こちら](https://hackmd.io/EaUPVO-aQnO6tyjq78-I6g?view#%E6%AD%A3%E8%A6%8F%E5%8C%96)

## マニアックだけど便利な関数

### 前提知識

参考：
- [SSの関数一覧](https://support.google.com/docs/table/25273?hl=ja)
- [SSのショートカット一覧](https://support.google.com/docs/answer/181110?co=GENIE.Platform%3DDesktop&hl=ja)

#### 絶対参照

指定する範囲の手前に`$`をつける
→　ドラックアンドドロップしても選択範囲が移動しなくなる

例）
行も列も固定する場合：`$A$1`
列だけ固定したい場合：`$A1`
行だけ固定したい場合：`A$1`

#### 上限なしの参照

参照先の一部を指定しない場合、上限なしの指定ができる

例）
B列の一番下まで：`A1:B`
9行目の一番右まで：`A1:9`

#### 文字列連結

スプレッドシートのセル同士やセルと文字列を連結
する場合は`&`で繋ぐ

A1とB1を連結する場合：`A1&B1`
A1の後に、「円」をつける場合：`A1&"円"`

#### ショートカット

- 下方向へのコピー
- windows: `Ctr+D`
- mac: `⌘+D`
- 配列数式を挿入(ARRAYFORMULA)
- windows: `Ctr+shift+return`
- mac: `⌘+shift+return`

#### 表の結合

- 縦の結合： `{範囲A; 範囲B; 範囲C…}`
- 横の結合： `{範囲A, 範囲B, 範囲C…}`

### 今回紹介する関数一覧

- UNIQUE
- ARRAYFORMULA
- QUERY
- (おまけ)IMPORTRANGE

### UNIQUE関数

- `UNIQUE(範囲)`
- [詳細](https://support.google.com/docs/answer/3093198)

概要：表の重複削除ができる

#### Q&A

Q. 表計算ツールの重複削除機能でよくない？
A. よくない

一時的な作業であれば"表計算ツールの重複削除機能"
で問題ないです。

長期運用ではそうもいきません。

例えば
- 元データが追加された
- 元データが変わった
- 元データを更新する人が変わった

場合など、

毎回"重複削除"をしていると
手間だけじゃなく、抜け漏れの原因にもなります

それをなくせる。そう、UNIQUE関数ならね。

### ARRAYFORMULA関数

- `ARRAYFORMULA(配列数式)`
- [詳細](https://support.google.com/docs/answer/3093275)

配列数式から返された値を
複数行または複数列に表示したり、
非配列関数で配列を使用したりすることができます

どういうこと？

一言で言うと、「ドラック&ドロップをやってくれる」

#### 例

| A(数値１) | B(数値２) | C（数値1 + 数値2) |
| -------- | -------- | -------- |
| 10 | 9 | 19 |
| 20 | 8 | 28 |
| 30 | 7 | 37 |

上記のような表を作りたい場合に、

Cには、
`=A2+B2`

こんな感じに書いてドラック&ドロップ
すると思いますが

ARRAYFORMULA関数を使うと

`ARRAYFORMULA(A1:A3 + B1:B3)`
これをC1に書くだけでOK

ドラック&ドロップをしなくてOK

#### メリット

これも
- データが変わった
- 増えた

場合に、
ドラック&ドロップする必要がなくなります。

そもそも関数の管理が１つのセルで完結するので
管理が非常に楽です

（計算方法変えるたびにドラック&ドロップをしなくてよい）

### QUERY関数

- `QUERY(データ, クエリ, 見出し)`
- [詳細](https://support.google.com/docs/answer/3093343)

Google Visualization API の
クエリ言語を使用して、
データ全体に対するクエリを実行します

要するに、
用意したデータに対して
問い合わせができる
（SQLと似たことができます）

具体的には

表に対して
- select
- この列とこの列のデータだけ欲しい
- 列の順番(左右)を入れ替えたい
- where
- 特定の条件で絞り込みたい
- group by
- グルーピングして合計とかカウント出したい
- order　by
- 並び替えたい
- 昇順（asc）、降順（desc）
- pivot
- 列と行を入れ替えたい
- label
- こういう名前のヘッダーをつけたい

みたいなことができます。

#### Q&A

Q. 表計算ツールのフィルター/ソート機能でよくない？
A. よくない。理由は上に同じ。

他にも、現在日時を条件にして、
自動で絞り込み条件を変更したり、
既存のpivot機能では手が届かない痒いところも
１行で書けたりします。

### IMPORTRANGE関数

- `IMPORTRANGE(spreadsheet_url, range_string)`
- [詳細](https://support.google.com/docs/answer/3093340)

`指定したスプレッドシートからセルの範囲をインポートします`

スプレッドシートAのシート１を
スプレッドシートBのシート２に表示することができます。

この時、スプレッドシートBでは
編集はできないのでご注意ください。

#### メリット

チームで話し合う際に、
一つのシートに情報をまとめたい！
ってことありますよね。

けどまとめると、
チームでつかうスプレッドシートになるので
個人計算用のシートとか作りづらい..
そんな時に

別シートのシート１だけ
チームメンバーに共有するっという感じで使えます。

Aさんのスプレットシートウォッチしたいから

IMPORTRANGEで個人シートに表示だけしとくか..
もできます。

## 最後に

- 表を分けるのに時間がかかる
- 関数覚えるの大変
- 自分がわかれば良い

と思うかもしれないですが、
最初に少し時間をかけてでも
将来のことを考えながら丁寧に作ることで

長期で見ると工数削減や属人化の解消に
繋がると思います。

また、調べながら作る..で全然良いと思います。

全部覚えるのではなく、
「そういえばこんなことできる関数あったな〜、調べてみよう」
っと思えるだけでも大きな変化だと思うので

なにか一つでも使えそうな知識を
持って帰っていただければ幸いです。