teratail
回答率
85.49%
質問するログイン新規登録
トップGoogle スプレッドシートに関する質問スプレッドシートQuery関数で、別条件のデータをまとめて1つのコードで出力したい

編集履歴

回答編集履歴

15

リファレンスを明記

2022/07/19 14:00

投稿

退会済みユーザー
test CHANGED
@@ -78,3 +78,6 @@
78
78
  )
79
79
  })
80
80
  ```
81
+ 参考：
82
+ [Query Language Reference (Version 0.7) ](https://developers.google.com/chart/interactive/docs/querylanguage)
83
+

14

修正

2022/07/18 11:12

投稿

退会済みユーザー
test CHANGED
@@ -73,7 +73,7 @@
73
73
  QUERY('発送管理表'!C:F,
74
74
  "select sum(F)
75
75
  where C is not null and (D='80g' or D='1kg' or D='20g')
76
- group by year(C), month(C)+1 pivot D and (D='80g' or D='1kg' or D='20g')
76
+ group by year(C), month(C)+1 pivot D
77
77
  label sum(F) 'の販売重量 (kg)'"
78
78
  )
79
79
  })

13

修正

2022/07/18 11:10

投稿

退会済みユーザー
test CHANGED
@@ -66,12 +66,14 @@
66
66
  =ARRAYFORMULA({
67
67
  QUERY('発送管理表'!C:F,
68
68
  "select year(C), month(C)+1, sum(F)
69
- where C is not null group by year(C), month(C)+1 and (D='80g' or D='1kg' or D='20g')
69
+ where C is not null and (D='80g' or D='1kg' or D='20g')
70
+ group by year(C), month(C)+1
70
71
  label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'"
71
72
  ),
72
73
  QUERY('発送管理表'!C:F,
73
74
  "select sum(F)
75
+ where C is not null and (D='80g' or D='1kg' or D='20g')
74
- where C is not null group by year(C), month(C)+1 pivot D and (D='80g' or D='1kg' or D='20g')
76
+ group by year(C), month(C)+1 pivot D and (D='80g' or D='1kg' or D='20g')
75
77
  label sum(F) 'の販売重量 (kg)'"
76
78
  )
77
79
  })

12

　

2022/07/18 10:31

投稿

退会済みユーザー
test CHANGED
@@ -51,3 +51,28 @@
51
51
  })
52
52
  ```
53
53
 
54
+
55
+ -----
56
+
57
+ コメントで「イレギュラーな重量は除外したい」とのことだったので、
58
+ たとえば、20g、80g、1kg だけ抽出したい場合は
59
+ 下記のようになります。
60
+
61
+ （WHERE句に
62
+ and (D='80g' or D='1kg' or D='20g')
63
+ を追加すればいいだけです）
64
+
65
+ ```
66
+ =ARRAYFORMULA({
67
+ QUERY('発送管理表'!C:F,
68
+ "select year(C), month(C)+1, sum(F)
69
+ where C is not null group by year(C), month(C)+1 and (D='80g' or D='1kg' or D='20g')
70
+ label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'"
71
+ ),
72
+ QUERY('発送管理表'!C:F,
73
+ "select sum(F)
74
+ where C is not null group by year(C), month(C)+1 pivot D and (D='80g' or D='1kg' or D='20g')
75
+ label sum(F) 'の販売重量 (kg)'"
76
+ )
77
+ })
78
+ ```

11

　

2022/07/18 07:23

投稿

退会済みユーザー
test CHANGED
@@ -28,7 +28,7 @@
28
28
 
29
29
  ---
30
30
  ただし上記の数式の連結では、以下の制限があります。
31
- ・その重量がない月が存在する場合、エラーになる（例：「6月は1kg、20g、80gのデータがあるが、7月は1kgと80gのデータしか存在しない」）
31
+ ・その重量がない月が存在する場合、エラーになる（例：「6月は1kg、20g、80gのデータがあるが、7月は1kgと80gのデータしか存在しない」というような場合）
32
32
  ・決まった重量種類しか対応できず、仮に途中で新しい重量が追加された場合は、数式を修正する必要が生じる。
33
33
 
34
34
  これらの制限は、下記のようにPIVOTを使用することで回避できます。

10

　　

2022/07/18 07:22

投稿

退会済みユーザー
test CHANGED
@@ -31,13 +31,14 @@
31
31
  ・その重量がない月が存在する場合、エラーになる（例：「6月は1kg、20g、80gのデータがあるが、7月は1kgと80gのデータしか存在しない」）
32
32
  ・決まった重量種類しか対応できず、仮に途中で新しい重量が追加された場合は、数式を修正する必要が生じる。
33
33
 
34
- 下記のようにPIVOTを使用すれば、自動的に追加されます。
34
+ これらの制限は、下記のようにPIVOTを使用することで回避できます。
35
35
  ```
36
36
  =QUERY('発送管理表'!C:F,
37
37
  "select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D
38
38
  label year(C) '年', month(C)+1 '月', sum(F) 'の販売重量 (kg)'")
39
39
  ```
40
+ これだけだと、各重量カテゴリの小計だけの表示になっており、各年月の重量合計が表示されていません。
40
- （ただしこの場合、各重量カテゴリの小計だけの表示であり、各年月の重量合計を同時に表示する方法はありません。各年月の重量合計も一緒に表示したい場合は、下記のように 重量カテゴリの合計を集計するQUERY式をARRAYFORMULAで連結する必要があります）
41
+ 各年月の重量合計も一緒に表示したい場合は、下記のように 重量カテゴリの合計を集計する QUERY 式を ARRAYFORMULA で連結する必要があります。
41
42
 
42
43
  ```
43
44
  =ARRAYFORMULA({

9

　

2022/07/18 07:21

投稿

退会済みユーザー
test CHANGED
@@ -27,7 +27,9 @@
27
27
  同様に、80g、1kgも追加すれば可能です。
28
28
 
29
29
  ---
30
+ ただし上記の数式の連結では、以下の制限があります。
31
+ ・その重量がない月が存在する場合、エラーになる（例：「6月は1kg、20g、80gのデータがあるが、7月は1kgと80gのデータしか存在しない」）
30
- ただし上記の数式の連結では、決まった重量種類しか対応できず、仮に途中で新しい重量が追加された場合は、数式を修正する必要が生じます。
32
+ ・決まった重量種類しか対応できず、仮に途中で新しい重量が追加された場合は、数式を修正する必要が生じる。
31
33
 
32
34
  下記のようにPIVOTを使用すれば、自動的に追加されます。
33
35
  ```

8

　

2022/07/18 06:06

投稿

退会済みユーザー
test CHANGED
@@ -38,20 +38,13 @@
38
38
  （ただしこの場合、各重量カテゴリの小計だけの表示であり、各年月の重量合計を同時に表示する方法はありません。各年月の重量合計も一緒に表示したい場合は、下記のように 重量カテゴリの合計を集計するQUERY式をARRAYFORMULAで連結する必要があります）
39
39
 
40
40
  ```
41
- =ARRAYFORMULA(
41
+ =ARRAYFORMULA({
42
- {
43
- QUERY('発送管理表'!C:F,
44
- "select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D
42
+ QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1
45
- label year(C) '年', month(C)+1 '月', sum(F) 'の販売重量 (kg)'"
43
+ label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'"
46
44
  ),
47
- QUERY(
48
- QUERY('発送管理表'!C:F,
49
- "select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1
45
+ QUERY('発送管理表'!C:F,"select sum(F) where C is not null group by year(C), month(C)+1 pivot D
50
- label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'"
46
+ label sum(F) 'の販売重量 (kg)'"
51
- ),
52
- "SELECT Col3"
53
47
  )
54
- }
48
+ })
55
- )
56
49
  ```
57
50
 

7

2022/07/18 06:03

投稿

退会済みユーザー
test CHANGED
@@ -5,7 +5,7 @@
5
5
  　
6
6
  一般化すると：
7
7
  ```
8
- =ARRAYFORMULA({QUERY式、QUERY式、...})
8
+ =ARRAYFORMULA({QUERY式, QUERY式, ...})
9
9
  ```
10
10
  たとえば、「年、月、全商品の合計、20gの商品の合計」の4列を表示したい場合
11
11
 

6

　

2022/07/18 05:58

投稿

退会済みユーザー
test CHANGED
@@ -27,7 +27,7 @@
27
27
  同様に、80g、1kgも追加すれば可能です。
28
28
 
29
29
  ---
30
- ただし上記の場合、決まった重量種類の場合しか対応できず、途中で別の重量が追加されるたびに数式を修正する必要が生じます。
30
+ ただし上記の数式の連結では、決まった重量種類しか対応できず、仮に途中で新しい重量が追加された場合は、数式を修正する必要が生じます。
31
31
 
32
32
  下記のようにPIVOTを使用すれば、自動的に追加されます。
33
33
  ```

5

　

2022/07/18 05:56

投稿

退会済みユーザー
test CHANGED
@@ -10,7 +10,18 @@
10
10
  たとえば、「年、月、全商品の合計、20gの商品の合計」の4列を表示したい場合
11
11
 
12
12
  ```
13
- =ARRAYFORMULA({QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'",2),QUERY('発送管理表'!C:F,"select sum(F) where D = '20g' group by year(C), month(C)+1 label sum(F) '20gの販売重量 (kg)'",2)})
13
+ =ARRAYFORMULA(
14
+ {
15
+ QUERY('発送管理表'!C:F,
16
+ "select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1
17
+ label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'",2
18
+ ),
19
+ QUERY('発送管理表'!C:F,
20
+ "select sum(F) where D = '20g' group by year(C), month(C)+1
21
+ label sum(F) '20gの販売重量 (kg)'",2
22
+ )
23
+ }
24
+ )
14
25
  ```
15
26
  とします。
16
27
  同様に、80g、1kgも追加すれば可能です。
@@ -20,20 +31,25 @@
20
31
 
21
32
  下記のようにPIVOTを使用すれば、自動的に追加されます。
22
33
  ```
34
+ =QUERY('発送管理表'!C:F,
23
- =QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D label year(C) '年', month(C)+1 '月', sum(F) 'の販売重量 (kg)'")
35
+ "select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D
36
+ label year(C) '年', month(C)+1 '月', sum(F) 'の販売重量 (kg)'")
24
37
  ```
25
38
  （ただしこの場合、各重量カテゴリの小計だけの表示であり、各年月の重量合計を同時に表示する方法はありません。各年月の重量合計も一緒に表示したい場合は、下記のように 重量カテゴリの合計を集計するQUERY式をARRAYFORMULAで連結する必要があります）
26
39
 
27
40
  ```
28
41
  =ARRAYFORMULA(
29
42
  {
43
+ QUERY('発送管理表'!C:F,
30
- QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D
44
+ "select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D
31
- label year(C) '年', month(C)+1 '月', sum(F) 'の販売重量 (kg)'"
45
+ label year(C) '年', month(C)+1 '月', sum(F) 'の販売重量 (kg)'"
32
46
  ),
33
47
  QUERY(
48
+ QUERY('発送管理表'!C:F,
34
- QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1
49
+ "select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1
35
50
  label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'"
51
+ ),
36
- ),"SELECT Col3"
52
+ "SELECT Col3"
37
53
  )
38
54
  }
39
55
  )

4

　

2022/07/18 05:49

投稿

退会済みユーザー
test CHANGED
@@ -28,7 +28,7 @@
28
28
  =ARRAYFORMULA(
29
29
  {
30
30
  QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D
31
- label year(C) '年', month(C)+1 '月', sum(F) '販売重量 (kg)'"
31
+ label year(C) '年', month(C)+1 '月', sum(F) 'の販売重量 (kg)'"
32
32
  ),
33
33
  QUERY(
34
34
  QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1

3

　

2022/07/18 05:49

投稿

退会済みユーザー
test CHANGED
@@ -13,7 +13,7 @@
13
13
  =ARRAYFORMULA({QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'",2),QUERY('発送管理表'!C:F,"select sum(F) where D = '20g' group by year(C), month(C)+1 label sum(F) '20gの販売重量 (kg)'",2)})
14
14
  ```
15
15
  とします。
16
- 同様に、50g、80g、1kgも追加すれば可能です。
16
+ 同様に、80g、1kgも追加すれば可能です。
17
17
 
18
18
  ---
19
19
  ただし上記の場合、決まった重量種類の場合しか対応できず、途中で別の重量が追加されるたびに数式を修正する必要が生じます。

2

　

2022/07/18 05:47

投稿

退会済みユーザー
test CHANGED
@@ -12,12 +12,30 @@
12
12
  ```
13
13
  =ARRAYFORMULA({QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'",2),QUERY('発送管理表'!C:F,"select sum(F) where D = '20g' group by year(C), month(C)+1 label sum(F) '20gの販売重量 (kg)'",2)})
14
14
  ```
15
+ とします。
16
+ 同様に、50g、80g、1kgも追加すれば可能です。
15
17
 
16
18
  ---
19
+ ただし上記の場合、決まった重量種類の場合しか対応できず、途中で別の重量が追加されるたびに数式を修正する必要が生じます。
20
+
17
- 下記のようにPIVOTを使用する方法もあります。
21
+ 下記のようにPIVOTを使用すれば、自動的に追加されます。
18
22
  ```
19
23
  =QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D label year(C) '年', month(C)+1 '月', sum(F) 'の販売重量 (kg)'")
20
24
  ```
21
- （ただしこの場合、各重量カテゴリの小計だけの表示であり、各年月の重量合計を同時に表示する方法はありません。各年月の重量合計も一緒に表示したい場合は、上記のように 重量カテゴリの合計を集計するQUERY式をARRAYFORMULAで連結する必要があります）
25
+ （ただしこの場合、各重量カテゴリの小計だけの表示であり、各年月の重量合計を同時に表示する方法はありません。各年月の重量合計も一緒に表示したい場合は、下記のように 重量カテゴリの合計を集計するQUERY式をARRAYFORMULAで連結する必要があります）
22
26
 
27
+ ```
28
+ =ARRAYFORMULA(
29
+ {
30
+ QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1 pivot D
31
+ label year(C) '年', month(C)+1 '月', sum(F) '販売重量 (kg)'"
32
+ ),
33
+ QUERY(
34
+ QUERY('発送管理表'!C:F,"select year(C), month(C)+1, sum(F) where C is not null group by year(C), month(C)+1
35
+ label year(C) '年', month(C)+1 '月', sum(F) '全商品の販売重量 (kg)'"
36
+ ),"SELECT Col3"
37
+ )
38
+ }
39
+ )
40
+ ```
23
41
 

1

　

2022/07/18 05:37

投稿

退会済みユーザー
test CHANGED
@@ -1,6 +1,6 @@
1
1
  （GASに組み込んだ形での回答は省略し、コアとなる数式のみ提示します）
2
2
 
3
- > Query関数では、別条件のデータを1つの式に表す方法
3
+ > Query関数で、別条件のデータを1つの式に表す方法
4
4
  ARRAYFORMULA関数と配列とカンマを使用します。
5
5
  　
6
6
  一般化すると：