GoogleスプレッドシートのQuery関数が便利だったお話

## はじめに
[MetapsAdventCalendar2024](https://qiita.com/advent-calendar/2024/metaps)　17日目の記事です🏃‍♂️
GoogleスプレッドシートのQuery関数を利用することで、スプレッドシート上のデータに対してSQLライクな抽出やグループごとの集計（Group By）が行えます。今回はそんなQuery関数が役立った事例を紹介してみようと思います。

:::note warn
記事執筆時点では、EXCEL上でQuery関数は利用できません。
:::

## やりたいこと
2回目の処理で成功したデータに対して、1回目の処理結果のエラーコード毎に、2回目の処理で成功した件数を以下のように集計し、1回目のエラーコードがどういったものであれば、2回目で成功しやすいのかを分析したい状況でした。

| エラーコード | 成功した件数 |
|---|---|
| エラーコード1 | 3 |
| エラーコード999 | 20 |

今回は、上記のようなエラーコード毎の集計をQuery関数を利用して取得し、
その結果から、どういったエラーコードが2回目の処理で成功しやすいのかを分析します。

## やってみる

### 処理結果をスプレッドシートに転記
データベースに保管されている処理結果を、スプレッドシートにコピペで記入します。
A〜C列は1回目の処理結果、E~G列は2回目の処理結果です。
エラーコードが空欄の場合は、処理が成功していることを示しています。
![シート1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/690d759c-6258-dc65-8740-10b476697648.png)


### 1回目と2回目で差分があるか
まずは、差分があるかをざっと確認したいので以下をI列3行目に記入します。
`=IF(EXACT($C3,$G3), "差分無し", "差分有り")`
EXACTによりC3とG3が同じ場合はTRUEを返し、異なる場合はFALSEを返します。
IFを利用して、EXACTの結果を受け取り「差分無し」か「差分有り」を出力させています。

:::note info
Tips: EXACTは大文字小文字を区別しますが、特に大文字小文字を区別する必要がない場合は`=IF($C3=$G3, "差分無し", "差分有り")`のようにしても問題ありません。
:::

![シート2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/4c21bb8c-be3e-ef81-5daa-1682a143c81f.png)


I3から連続データとしてコピーしました。
こうみると、やはり2回目の結果は、異なる場合が多々ありそうなことがわかりました。
![シート3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/ee8f5e6b-c808-a51f-fb15-0b8879650582.png)


### 2回目で成功したデータをわかりやすくする
G列のエラーコードが空セルの場合、2回目の処理は成功したことを示していますが、
わかりにくいのでJ3に以下を入力し、連続データとしてJ52までコピーしました。
`=IF(ISBLANK($G3) = TRUE, "2回目で成功", $G3)`
![シート4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/7c2d2c0d-b7a3-710f-9630-53bca229063c.png)


A3:J52に対して、以下の条件付き書式を設定して、2回目に成功したデータは黄色背景色にしました。
・カスタム数式
・`=(AND($I3="差分有り", $J3="2回目で成功"))`
![シート5(FIX).png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/42023b73-a408-0d73-0b98-e6a01ac078da.png)



### 全体で何件成功したのか
まずは全件を表示するために、L3に以下を入力して件数を出しました。
今回は、セル入力があれば件数としてカウントしてくれるCOUNTAを使ってみます。
-2しているのは、A1とA2のカウント分を除くためです。
`=(COUNTA($A1:$A10000) - 2 )`
![シート6-2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/0db1b93e-bc16-3dd4-ac9e-827f09fa3727.png)


J列に2回目で成功したかどうかが出ているので、成功した件数を集計してみます。
COUNTIFの方が簡易的に集計できますが、今回はエラーコード別の成功件数を求めるために、後ほど使うことになるQuery関数の練習を兼ねて、そちらを利用することにしてみます。
L5に以下を入力して、成功した件数を出してみました。
`=QUERY($J$3:$J$10000, "select count(J) where J = '2回目で成功' label count(J) '2回目で成功した件数'")`
![シート7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/01ccd7e8-ee96-cb07-8747-d4cabd4a6d81.png)


上記Queryの説明をします。
Queryの第一引数である`$J$3:$J$10000`は抽出対象の範囲を指定します。FROM句のようなイメージです。
第二引数はどのようにSQLライクに抽出するかを指定します。

`where J = '2回目で成功'`の通り、J列が「2回目で成功」であれば、`select count(J)`の指定通りJ列をカウントしています。`label count(J) '2回目で成功した件数'`の部分はselect句のcount(J)に対して抽出後のヘッダラベルを設定しています。

※Queryを練習したかったので、Queryを使っていますが、COUNTIFを利用して`=COUNTIF($J$3:$J$10000, "2回目で成功")`のように書いた方が簡易的にできます。

### 2回目で成功しやすいエラーコードを探す
お待たせしました。本題のQuery関数を利用して、Group Byによる集計を行なっていきます。

L8に以下を入力します。
`=QUERY($A$3:$J$10000, "select C, count(C) where J = '2回目で成功' group by C label C '1回目のエラーコード', count(C) '2回目で成功した件数'")`
こちらで行なっているのは`where J = '2回目で成功'`にて2回目で成功したデータ行を対象に、`group by C`と`count(C)`にてエラーコード別に成功件数を出しています。

![シート8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/96a433fb-9e98-7720-6e49-018de0e6b0d4.png)

ちょっとだけ見栄えを整えます。
![シート9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2990943/53506113-98a7-b0eb-8530-10c84411def8.png)


以上で、「2回目の処理で成功するエラーコードの大半はエラーコード999であった」という答えを出すことができました。また、SUMIFを使って集計する場合は、エラーコード毎にSUMIFを書いていく必要がありますが、Query関数を利用した場合、group byが利用できる関係上、記述箇所が1箇所で済みます（便利）。

## 最後に
Query関数を利用して、簡単に1回目のエラーコードごとの成功件数を出すことができました。データベース上では結果を残しておくことができないデータ等に対して、スプレッドシート上に対象データを持ってきて、SQLライクな抽出や分析を行いたい場合、非常に有用でしたので共有させていただきました。

※今回作成したスプレッドシートは[こちら](https://docs.google.com/spreadsheets/d/e/2PACX-1vQGp8Qj4XOQqajeB0G0CFqrJHh4NSyZZ8ONhorgVuVcxHghubzZQPxL-yfgxYCiQG4JLbXT3zFf7OlE/pubhtml
)にて公開しています。