はじめに
MetapsAdventCalendar2024 17日目の記事です🏃♂️
GoogleスプレッドシートのQuery関数を利用することで、スプレッドシート上のデータに対してSQLライクな抽出やグループごとの集計（Group By）が行えます。今回はそんなQuery関数が役立った事例を紹介してみようと思います。
記事執筆時点では、EXCEL上でQuery関数は利用できません。
やりたいこと
2回目の処理で成功したデータに対して、1回目の処理結果のエラーコード毎に、2回目の処理で成功した件数を以下のように集計し、1回目のエラーコードがどういったものであれば、2回目で成功しやすいのかを分析したい状況でした。
| エラーコード | 成功した件数 |
|---|---|
| エラーコード1 | 3 |
| エラーコード999 | 20 |
今回は、上記のようなエラーコード毎の集計をQuery関数を利用して取得し、
その結果から、どういったエラーコードが2回目の処理で成功しやすいのかを分析します。
やってみる
処理結果をスプレッドシートに転記
データベースに保管されている処理結果を、スプレッドシートにコピペで記入します。
A〜C列は1回目の処理結果、E~G列は2回目の処理結果です。
エラーコードが空欄の場合は、処理が成功していることを示しています。
1回目と2回目で差分があるか
まずは、差分があるかをざっと確認したいので以下をI列3行目に記入します。
=IF(EXACT($C3,$G3), "差分無し", "差分有り")
EXACTによりC3とG3が同じ場合はTRUEを返し、異なる場合はFALSEを返します。
IFを利用して、EXACTの結果を受け取り「差分無し」か「差分有り」を出力させています。
Tips: EXACTは大文字小文字を区別しますが、特に大文字小文字を区別する必要がない場合は=IF($C3=$G3, "差分無し", "差分有り")のようにしても問題ありません。
I3から連続データとしてコピーしました。
こうみると、やはり2回目の結果は、異なる場合が多々ありそうなことがわかりました。
2回目で成功したデータをわかりやすくする
G列のエラーコードが空セルの場合、2回目の処理は成功したことを示していますが、
わかりにくいのでJ3に以下を入力し、連続データとしてJ52までコピーしました。
=IF(ISBLANK($G3) = TRUE, "2回目で成功", $G3)
A3:J52に対して、以下の条件付き書式を設定して、2回目に成功したデータは黄色背景色にしました。
・カスタム数式
・=(AND($I3="差分有り", $J3="2回目で成功"))
全体で何件成功したのか
まずは全件を表示するために、L3に以下を入力して件数を出しました。
今回は、セル入力があれば件数としてカウントしてくれるCOUNTAを使ってみます。
-2しているのは、A1とA2のカウント分を除くためです。
=(COUNTA($A1:$A10000) - 2 )
J列に2回目で成功したかどうかが出ているので、成功した件数を集計してみます。
COUNTIFの方が簡易的に集計できますが、今回はエラーコード別の成功件数を求めるために、後ほど使うことになるQuery関数の練習を兼ねて、そちらを利用することにしてみます。
L5に以下を入力して、成功した件数を出してみました。
=QUERY($J$3:$J$10000, "select count(J) where J = '2回目で成功' label count(J) '2回目で成功した件数'")
上記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)にてエラーコード別に成功件数を出しています。
ちょっとだけ見栄えを整えます。
以上で、「2回目の処理で成功するエラーコードの大半はエラーコード999であった」という答えを出すことができました。また、SUMIFを使って集計する場合は、エラーコード毎にSUMIFを書いていく必要がありますが、Query関数を利用した場合、group byが利用できる関係上、記述箇所が1箇所で済みます（便利）。
最後に
Query関数を利用して、簡単に1回目のエラーコードごとの成功件数を出すことができました。データベース上では結果を残しておくことができないデータ等に対して、スプレッドシート上に対象データを持ってきて、SQLライクな抽出や分析を行いたい場合、非常に有用でしたので共有させていただきました。
※今回作成したスプレッドシートはこちらにて公開しています。