Googleデータアナリティクス：表計算ソフトのデータクリーニング関数

# はじめに

本記事は、Googleデータアナリティクスのプロフェッショナル認定証のプログラムより、参照させて頂いています。興味を持った方は、是非受講してみてください。

https://www.coursera.org/professional-certificates/data-analytics-japanese

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/b583930d-fecb-13ad-85a2-3f44e309d8a5.png" width="800">

# データクリーニングに使える表計算ツールの関数

### <font color="MediumBlue">COUNTIF</font>

***<font color="MediumVioletRed">『指定された値と一致する セルの数を返す関数』</font>***

基本的には、ある値が セル範囲に何回表示されるかを 数えるものです。 この例では、 協会の会費額が 正確に記載されているか 確認したいと思います。 COUNTIF を使用して、マイナスの数や 本来あるべき値よりも大幅に低い値、 または大幅に高い値などといった よくある問題を確認します。 

まず、最も安い会費である Student Associate、 つまり学生会員の 100 ドルを探します。 これが、この列に存在する 最も小さな数字になります。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/247f2918-4291-c588-f5a2-c3555afff637.png" width="700">

100 よりも小さい値を持つセルがあれば、 COUNTIF が警告を発します。 そこで表計算ソフトの一番下に 行を追加し、H 列の下に「member dueS less than 100USD 」 （100USD 以下の会員）と 入力します。次に、 その隣のセルに関数を入力します。 すべての関数には、その関数が 動作するための構文が必要です。 COUNTIF 関数の構文は 次のようになります。 =COUNTIF、開き括弧、 範囲、カンマ、 引用符で囲まれた指定値、 閉じ括弧、です。 つまりこのようになります。範囲は I2 から I72 で、 値は 100 未満です。 この関数で、I 列の範囲で 100 未満の数値を含む すべてのセルの数を返すよう指示します。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/3cf77083-e1c9-20ec-5f48-e1f6ae58aed1.png" width="700">

結果、1 つだけありました。 データをスクロールすると 1 つのデータが誤ってマイナスで 入力されていることがわかります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/a1810367-5f73-93e3-b410-225d7f7fb41d.png" width="700">

これを修正しましょう。

次に、 COUNTIF を使って、あるべき値より 大きな値を検索してみます。 最も高いのは Corporate Member、 つまり法人会員の 500 ドルです。 セルに関数を入力しましょう。今度はこのようになります。 I2 から I72 までを範囲としますが 500 よりも大きい値です。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/accaf5eb-de45-accf-8089-970e0346f8f0.png" width="700">

これもありましたね。 見てみましょう。このエントリーには余分なゼロがあります。 これは 100 ドルにすべきです。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/6687cffb-6c7f-31d4-ac22-48141e4b9ac3.png" width="700">

### <font color="MediumBlue">LEN</font>

***<font color="MediumVioletRed">『文字列の文字数を数えることで、 その文字列の長さを 教えてくれる関数』</font>***

これは、スプレッドシートの中に 特定の長さの情報を含まなければならない、 といった場合の データ クリーニングに便利です。 たとえばこの協会では 6 桁の会員 ID を使用しています。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/b7811c94-faf3-670b-a9ef-5f1a8b0e022c.png" width="700">

このデータをインポートして コードがすべて正しい桁数であることを 確認するのに、LEN を使用します。 LEN の構文は、=LEN、 開きカッコ、範囲、そして 閉じカッコです。 会員 ID の後に新しい列を挿入します。そして、イコール記号と LEN を入力し 開き括弧を追加します。 範囲は、A2 の最初の会員 ID です。 最後に括弧を閉じて関数を終了します。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/e9bb7e4a-fb40-80d9-ba3a-34e3272b3a46.png" width="700">

セル A2 には 6 つの文字があることが 分かります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/c6c6f636-ddf6-a3a8-68d9-173d717ee86b.png" width="700">

この関数を列全体にわたって 実行してみましょう。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/51c591e2-70b0-6d85-d1be-78ad22a9963e.png" width="700">

6 文字でないものがあるかどうか見るのに スプレッドシートを 手作業で検索するのではなく 条件付き書式を使用しましょう。 これは、 値が特定の条件を満たしたときに セルの表示方法を変更する 表計算ソフトの機能です。 早速実践してみましょう。 B 列をヘッダー以外すべて選択します。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/66b55951-f171-1812-7425-b9e6245c82f1.png" width="700">

次に、表示形式から 条件付き書式を選びます。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/839f2bf7-a893-8701-d1d4-d397448153c6.png" width="700">

書式ルールの条件設定は 6 と等しくない場合とします。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/3f8f7d86-58cf-ab2c-93fc-5d5961b5edfb.png" width="700">

完了をクリックします。すると、 7 が入っているセルが ハイライトされます。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/3b0b655c-78a0-227e-00bb-ec5fc5b155ec.png" width="700">

### <font color="MediumBlue">LEFT</font>

***<font color="MediumVioletRed">『テキスト文字列の 左側から 設定された文字数を返す関数』</font>***


### <font color="MediumBlue">RIGHT</font>

***<font color="MediumVioletRed">『テキスト文字列の 右側から 設定された文字数を返す関数』</font>***

スプレッドシートには 商品コードが含まれています。 それぞれ 5 桁の数字コードと 4 文字のテキスト識別子があります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/e24e8fb2-6dec-8d96-2927-3012a1705bcb.png" width="700">

このどちらか一方だけを 操作したいとします。 そんな時、LEFT や RIGHT を使えば 必要な文字や数字の数を 指定できます。 まずは LEFT 関数を使った データクリーニングを練習しましょう。 LEFT の構文は、=LEFT、開き括弧、 範囲、カンマ、 文字列の左側から 何文字かを示す数、 閉じ括弧、です。 今回は、 5 桁の数字だけが必要だとしましょう。 別の列に、

=LEFT、開き括弧、 そして範囲 A2 と入力します。 次に カンマを追加し、 5 桁の商品コードを示す 5 と入力します。 最後に、閉じ括弧で関数を終了します。 このように関数が表示されるはずです。 Enter を押してください。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/45f60b66-0425-d4b5-2d36-b8913083be20.png" width="700">

これで、 商品コードの番号部分のみを表す 部分文字列ができました。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/be690d62-5ff9-89c0-0545-9a612911c41f.png" width="700">

この関数をクリックして 列全体にドラッグすると、 残りの商品コードを番号だけで区切れます。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/7dc4359e-dbc1-7496-cd01-7dd03e9d856b.png" width="700">

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/d962b486-d90a-a3a4-55d8-22d87cc2016a.png" width="700">

あるいは、4 文字のテキスト識別子のみを 必要としているとします。

その場合、RIGHT 関数を 隣の列に入力しましょう。 構文は、=RIGHT、 開き括弧、範囲、カンマ、 求める文字数、そして 最後に閉じ括弧をつけます。 では、入力をしてみましょう。 =RIGHT、開き括弧、 範囲はまだ A2 です。 カンマを追加します。 今回は、右から 4 文字を 求めると指示します。 括弧を閉じて、Enter を押します。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/e574e276-7a32-8f60-fdc8-c1a253ce826e.png" width="700">

そして、関数を列全体にドラッグします。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/6fd5f38c-3d2e-f7a9-6bc3-39d68181556a.png" width="700">

### <font color="MediumBlue">MID</font>

***<font color="MediumVioletRed">『文字列の中間のセグメントを 得るための関数』</font>***

この化粧品メーカーでは 顧客コードですべての顧客を リストアップしています。 コードは顧客が住む
- 都市の 最初の 3 文字
- 州の略称
- 3 桁の識別子

で 構成されています。 

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/d259062a-dd9f-9fa0-7bff-41e412b0205c.png" width="700">

しかしデータアナリストが 真ん中にある州だけを使用して 作業する必要があるとします。 MID の構文は、=MID、 開き括弧、範囲、カンマの順です。 MID を使用する場合、 常に参照点を指定する必要があります。 つまり、関数を開始する位置を 設定する必要があります。 その後に、もう 1 つカンマを入れ 中間の文字をいくつにするか指定します。 この場合、範囲は D2 です。 新しい列で関数を開始しましょう。=MID、開き括弧、D2 と入力します。 次に、最初の 3 文字は 都市名を表しているので、 4 文字目がスタート地点、 ということになります。 カンマと 4 を追加します。 さらに、中間の文字がいくつ必要かを 指示する必要があります。 カンマを 1 つ追加して、2 とします。 州の略称は 2 文字だからです。 Enter を押すと、 州の略称が出ましたね。 MID 関数を列の残りの部分にも適用します。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/91df3108-68c5-01d0-e2cb-20f48e2f80d9.png" width="700">

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/e52051ac-dcaf-cb01-a729-0d3803dd5902.png" width="700">

### <font color="MediumBlue">CONCATENATE</font>

***<font color="MediumVioletRed">『2 つ以上のテキスト文字列を 結合する関数』</font>***

構文は =CONCATENATE、 開き括弧の中に 結合したい各文字列を カンマで区切って指定します。 そして、閉じ括弧で関数を終了します。 ここでは左右の文字列を再結合し、 1 つの商品コードにする 練習をしましょう。 新しい列で、関数を開始します。

=CONCATENATE と入力し、 次に開き括弧を入力します。 結合したい最初の文字列は H2、 次にカンマを追加します。 2 つ目は I2、 閉じ括弧を追加し、Enter を押して 列全体にドラッグします。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/a419dcc5-1041-4614-092b-5ce2ccc1e9f0.png" width="700">

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/356b2c8f-d10c-2057-b22f-a46b694d24fe.png" width="700">

:::note info
CONCATENATE は文字列を結合するので、スペースを挿入する指示を追加できます。
 =CONCATENATE(D2, " ", E2) 
:::

### <font color="MediumBlue">TRIM</font>

***<font color="MediumVioletRed">『データの先頭、末尾、 繰り返しといった不要なスペースを 削除する関数』</font>***

データを取り込むと セルに余分なスペースが入ってしまい 分析の邪魔になることがあります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/46312e0f-36f3-e04f-fe40-d56ca4ba6753.png" width="700">

たとえば、この化粧品メーカーが 特定の顧客名を調べようと思ったとき 余分なスペースがあると 検索に引っかかりません。 これを解決してくれるのが TRIM です。 TRIM の構文は、=TRIM、 開き括弧、範囲、閉じ括弧です。 別の列に、

=TRIM と開き括弧を入力します。 範囲は、顧客の名前を 確認したいので、C2 とします。 括弧を閉じて Enter を押します。 最後に、この関数を列の下に続けます。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/f0d3d8f0-ba89-065a-845e-9b4cca973052.png" width="700">

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2358573/3e336e93-317d-37e8-a440-28c3925da851.png" width="500">

TRIM を使うことで、余分なスペースが 修正されました。