回答編集履歴
15
リファレンスを明記
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
修正
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
|
76
|
+
group by year(C), month(C)+1 pivot D
|
77
77
|
label sum(F) 'の販売重量 (kg)'"
|
78
78
|
)
|
79
79
|
})
|
13
修正
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
|
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
|
-
|
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
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
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
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
|
-
|
41
|
+
各年月の重量合計も一緒に表示したい場合は、下記のように 重量カテゴリの合計を集計する QUERY 式を ARRAYFORMULA で連結する必要があります。
|
41
42
|
|
42
43
|
```
|
43
44
|
=ARRAYFORMULA({
|
9
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
test
CHANGED
@@ -38,20 +38,13 @@
|
|
38
38
|
(ただしこの場合、各重量カテゴリの小計だけの表示であり、各年月の重量合計を同時に表示する方法はありません。各年月の重量合計も一緒に表示したい場合は、下記のように 重量カテゴリの合計を集計するQUERY式をARRAYFORMULAで連結する必要があります)
|
39
39
|
|
40
40
|
```
|
41
|
-
=ARRAYFORMULA(
|
41
|
+
=ARRAYFORMULA({
|
42
|
-
{
|
43
|
-
QUERY('発送管理表'!C:F,
|
44
|
-
|
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
|
-
|
45
|
+
QUERY('発送管理表'!C:F,"select sum(F) where C is not null group by year(C), month(C)+1 pivot D
|
50
|
-
|
46
|
+
label sum(F) 'の販売重量 (kg)'"
|
51
|
-
),
|
52
|
-
"SELECT Col3"
|
53
47
|
)
|
54
|
-
|
48
|
+
})
|
55
|
-
)
|
56
49
|
```
|
57
50
|
|
7
test
CHANGED
@@ -5,7 +5,7 @@
|
|
5
5
|
|
6
6
|
一般化すると:
|
7
7
|
```
|
8
|
-
=ARRAYFORMULA({QUERY式
|
8
|
+
=ARRAYFORMULA({QUERY式, QUERY式, ...})
|
9
9
|
```
|
10
10
|
たとえば、「年、月、全商品の合計、20gの商品の合計」の4列を表示したい場合
|
11
11
|
|
6
test
CHANGED
@@ -27,7 +27,7 @@
|
|
27
27
|
同様に、80g、1kgも追加すれば可能です。
|
28
28
|
|
29
29
|
---
|
30
|
-
ただし上記の
|
30
|
+
ただし上記の数式の連結では、決まった重量種類しか対応できず、仮に途中で新しい重量が追加された場合は、数式を修正する必要が生じます。
|
31
31
|
|
32
32
|
下記のようにPIVOTを使用すれば、自動的に追加されます。
|
33
33
|
```
|
5
test
CHANGED
@@ -10,7 +10,18 @@
|
|
10
10
|
たとえば、「年、月、全商品の合計、20gの商品の合計」の4列を表示したい場合
|
11
11
|
|
12
12
|
```
|
13
|
-
=ARRAYFORMULA(
|
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
|
-
|
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
|
-
|
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
|
-
|
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
|
-
|
52
|
+
"SELECT Col3"
|
37
53
|
)
|
38
54
|
}
|
39
55
|
)
|
4
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
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
|
-
同様に、
|
16
|
+
同様に、80g、1kgも追加すれば可能です。
|
17
17
|
|
18
18
|
---
|
19
19
|
ただし上記の場合、決まった重量種類の場合しか対応できず、途中で別の重量が追加されるたびに数式を修正する必要が生じます。
|
2
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
|
-
(ただしこの場合、各重量カテゴリの小計だけの表示であり、各年月の重量合計を同時に表示する方法はありません。各年月の重量合計も一緒に表示したい場合は、
|
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
test
CHANGED
@@ -1,6 +1,6 @@
|
|
1
1
|
(GASに組み込んだ形での回答は省略し、コアとなる数式のみ提示します)
|
2
2
|
|
3
|
-
> Query関数で
|
3
|
+
> Query関数で、別条件のデータを1つの式に表す方法
|
4
4
|
ARRAYFORMULA関数と配列とカンマを使用します。
|
5
5
|
|
6
6
|
一般化すると:
|