Q&A
タグには ACCESS がありますが、質問文の画像はEXCELのピボットテーブルのようですね。
回答者にどのような解決方法を期待されているのでしょうか？
(1) EXCELのデータに対して、EXCEL上でセル関数やピボットテーブルを駆使して、望む出力結果を得たいのか
(2) ACCESSのテーブルに対して、ACCESSのクエリ(SQL)の結果で望む出力結果を得たいのか
(3) あるいはそれ以外
いずれでしょうか？
前提・実現したいこと
収集データから傾向を調べるために月次のクロス集計表を作成します。
収集間隔がまちまちのため、日付は非連続です。
収集データから月次の在宅率と全期間の在宅率を1つの表にすることを目指しています。
（追記）
収集データは4月以降も増えます。4月以降も毎月出力データと同様の形式でレポートを出す想定です。
（追記２）
下記サンプルデータはExcelを使って作成していますが、データ構造や処理はAccessやExcelのPowerQueryでも実装できると思っています。表現はExcelなどデータソースと別でも構いません。
入力データ
収集データ（配達テーブル）
カレンダーテーブル
求める出力データ
これらのテーブルから、以下のクロス集計表を作成することが目的です。
A社の場合、1月の配達回数＝4回、在宅回数＝2回、よって在宅率は2/4=50％です。
2月はA社B社とも配達がなかったため収集データはありませんが、2月の列は明示したいため両方とも0％です。
この明示のためにカレンダーテーブルを用意しました。
発生している問題・エラーメッセージ
クロス表を作成したとき在宅率をどう計算させたらよいかわかりませんでした。
単純にピボットテーブルを使うと下図のように配達回数や在宅数は個々に集計できますが、集計値の演算方法がわかりません。
（３）です。
質問作成時にデータ構造、データ処理の部分はAccessやExcelのPowerQuery、データ表現の部分はExcelやPowerBIなどBIツールを想定しています。
今回の質問サンプルを作っている過程でExcelのみ残りました。
その旨追記します。
ご指摘ありがとうございました。
回答5件
0
クエリのデータ表記については
横持ちの列に yyyymm 形式で年の数字を含めると
複数年のデータ表示が必要になった際に
記述が複雑になり、都度SQL文の編集も発生しますから
年(度)は、縦持ちで表示させておくことをお薦めします。
収集データは4月以降も増えます。
4月以降も毎月出力データと同様の形式でレポートを出す想定です。
クロス表を作成したとき在宅率をどう計算させたらよいかわかりませんでした。
常時12ヶ月分の列を表示することで特に問題なさそうですね。
だとすると、以下でいかがでしょうか。
( データを横持ちに変更するロジックは既に提示されていますから、単一テーブルでの記述に過ぎませんけど )
SQL
1SELECT 配達先 2 , year( 配達日 ) as 年 3 , int( 4 sum( 在宅数 ) / sum( 配達回数 ) * 100 5 ) as 通期 6 , int( 7 sum( iif( month( 配達日 ) = 1, 在宅数, 0 ) ) 8 / nz( sum( iif( month( 配達日 ) = 1, 配達回数 ) ), 1 ) * 100 9 ) as _1月在宅率 10 , sum( iif( month( 配達日 ) = 1, 在宅数, 0 ) ) as _1月在宅数 11 , sum( iif( month( 配達日 ) = 1, 配達回数, 0 ) ) as _1月配達回数 12 , int( 13 sum( iif( month( 配達日 ) = 2, 在宅数, 0 ) ) 14 / nz( sum( iif( month( 配達日 ) = 2, 配達回数 ) ), 1 ) * 100 15 ) as _2月在宅率 16 , sum( iif( month( 配達日 ) = 2, 在宅数, 0 ) ) as _2月在宅数 17 , sum( iif( month( 配達日 ) = 2, 配達回数, 0 ) ) as _2月配達回数 18 , int( 19 sum( iif( month( 配達日 ) = 3, 在宅数, 0 ) ) 20 / nz( sum( iif( month( 配達日 ) = 3, 配達回数 ) ), 1 ) * 100 21 ) as _3月在宅率 22 , sum( iif( month( 配達日 ) = 3, 在宅数, 0 ) ) as _3月在宅数 23 , sum( iif( month( 配達日 ) = 3, 配達回数, 0 ) ) as _3月配達回数 24 , int( 25 sum( iif( month( 配達日 ) = 4, 在宅数, 0 ) ) 26 / nz( sum( iif( month( 配達日 ) = 4, 配達回数 ) ), 1 ) * 100 27 ) as _4月在宅率 28 , sum( iif( month( 配達日 ) = 4, 在宅数, 0 ) ) as _4月在宅数 29 , sum( iif( month( 配達日 ) = 4, 配達回数, 0 ) ) as _4月配達回数 30 , int( 31 sum( iif( month( 配達日 ) = 5, 在宅数, 0 ) ) 32 / nz( sum( iif( month( 配達日 ) = 5, 配達回数 ) ), 1 ) * 100 33 ) as _5月在宅率 34 , sum( iif( month( 配達日 ) = 5, 在宅数, 0 ) ) as _5月在宅数 35 , sum( iif( month( 配達日 ) = 5, 配達回数, 0 ) ) as _5月配達回数 36 , int( 37 sum( iif( month( 配達日 ) = 6, 在宅数, 0 ) ) 38 / nz( sum( iif( month( 配達日 ) = 6, 配達回数 ) ), 1 ) * 100 39 ) as _6月在宅率 40 , sum( iif( month( 配達日 ) = 6, 在宅数, 0 ) ) as _6月在宅数 41 , sum( iif( month( 配達日 ) = 6, 配達回数, 0 ) ) as _6月配達回数 42 , int( 43 sum( iif( month( 配達日 ) = 7, 在宅数, 0 ) ) 44 / nz( sum( iif( month( 配達日 ) = 7, 配達回数 ) ), 1 ) * 100 45 ) as _7月在宅率 46 , sum( iif( month( 配達日 ) = 7, 在宅数, 0 ) ) as _7月在宅数 47 , sum( iif( month( 配達日 ) = 7, 配達回数, 0 ) ) as _7月配達回数 48 , int( 49 sum( iif( month( 配達日 ) = 8, 在宅数, 0 ) ) 50 / nz( sum( iif( month( 配達日 ) = 8, 配達回数 ) ), 1 ) * 100 51 ) as _8月在宅率 52 , sum( iif( month( 配達日 ) = 8, 在宅数, 0 ) ) as _8月在宅数 53 , sum( iif( month( 配達日 ) = 8, 配達回数, 0 ) ) as _8月配達回数 54 , int( 55 sum( iif( month( 配達日 ) = 9, 在宅数, 0 ) ) 56 / nz( sum( iif( month( 配達日 ) = 9, 配達回数 ) ), 1 ) * 100 57 ) as _9月在宅率 58 , sum( iif( month( 配達日 ) = 9, 在宅数, 0 ) ) as _9月在宅数 59 , sum( iif( month( 配達日 ) = 9, 配達回数, 0 ) ) as _9月配達回数 60 , int( 61 sum( iif( month( 配達日 ) = 10, 在宅数, 0 ) ) 62 / nz( sum( iif( month( 配達日 ) = 10, 配達回数 ) ), 1 ) * 100 63 ) As _10月在宅率 64 , sum( iif( month( 配達日 ) = 10, 在宅数, 0 ) ) as _10月在宅数 65 , sum( iif( month( 配達日 ) = 10, 配達回数, 0 ) ) as _10月配達回数 66 , int( 67 sum( iif( month( 配達日 ) = 11, 在宅数, 0 ) ) 68 / nz( sum( iif( month( 配達日 ) = 11, 配達回数 ) ), 1 ) * 100 69 ) as _11月在宅率 70 , sum( iif( month( 配達日 ) = 11, 在宅数, 0 ) ) as _11月在宅数 71 , sum( iif( month( 配達日 ) = 11, 配達回数, 0 ) ) as _11月配達回数 72 , int( 73 sum( iif( month( 配達日 ) = 12, 在宅数, 0 ) ) 74 / nz( sum( iif( month( 配達日 ) = 12, 配達回数 ) ), 1 ) * 100 75 ) as _12月在宅率 76 , sum( iif( month( 配達日 ) = 12, 在宅数, 0 ) ) as _12月在宅数 77 , sum( iif( month( 配達日 ) = 12, 配達回数, 0 ) ) as _12月配達回数 78FROM 収集データ 79GROUP BY 配達先 80 , year( 配達日 ) 81;
※ データが無い月の集計は Null ではなく 0 にしています。
result
1配達先 年 通期 _1月在宅率 _1月在宅数 _1月配達回数 ... _12月在宅率 ... 2---------------------------------------------------------------------------------- 3 A 2021 71 50 2 4 ... *** ... 4 B 2021 50 100 2 2 ... ** ...
投稿2021/09/27 23:53
総合スコア335
0
ベストアンサー
ACCESSで実現する方法を紹介します
[配達テーブル]と[カレンダーテーブル]をACCESSに作成します
以下のSQLでクエリー[Q1]を作成します
SQL
1SELECT 2 [配達先] 3, Format([配達日], "YYYY/MM") AS [配達月] 4, SUM([配達回数]) AS 月別配達回数 5, SUM([在宅数]) AS 月別在宅数 6, SUM([在宅数])/SUM([配達回数]) AS 在宅率 7FROM [配達テーブル] 8GROUP BY 9 [配達先] 10, Format([配達日], "YYYY/MM")
実行すると
| 配達先 | 配達月 | 月別配達回数 | 月別在宅数 | 在宅率 |
|---|---|---|---|---|
| A | 2021/01 | 4 | 2 | 0.5 |
| A | 2021/03 | 3 | 3 | 1 |
| B | 2021/01 | 2 | 2 | 1 |
| B | 2021/03 | 2 | 0 | 0 |
以下のSQLでクエリー[Q2]を作成します
SQL
1SELECT 2 T1.[配達先] 3, T2.[年月] 4FROM (SELECT DISTINCT [配達先] FROM [配達テーブル]) AS T1 5 , (SELECT DISTINCT Format([日付], "YYYY/MM") AS 年月 6 FROM [カレンダーテーブル] 7 WHERE [日付] BETWEEN #2021/01/01# AND #2021/03/31# 8 ) AS T2
期間を指定したい時はWHERE [日付] BETWEEN #2021/01/01# AND #2021/03/31#部分を変更してください
実行すると
| 配達先 | 年月 |
|---|---|
| A | 2021/01 |
| A | 2021/02 |
| A | 2021/03 |
| B | 2021/01 |
| B | 2021/02 |
| B | 2021/03 |
最後にクロス集計クエリーの以下のSQLを実行します
SQL
1TRANSFORM SUM(Q1.[在宅率]) 2SELECT Q2.[配達先] 3, SUM(Q1.[月別在宅数])/SUM(Q1.[月別配達回数]) AS 通期 4FROM Q2 5LEFT JOIN Q1 6 ON Q2.[配達先] = Q1.[配達先] 7 AND Q2.[年月] = Q1.[配達月] 8GROUP BY Q2.[配達先] 9PIVOT Q2.[年月]
結果
| 配達先 | 通期 | 2021/01 | 2021/02 | 2021/03 |
|---|---|---|---|---|
| A | 0.714286 | 0.5 | 1 | |
| B | 0.5 | 1 | 0 |
投稿2021/09/28 14:38
総合スコア359
Format([配達日], "YYYY/MM")
でグループ化するんですね！ここは思いつきませんでした。
カレンダーテーブルを拡張すれば四半期ごとの集計もできそうです。
ありがとうございました！
0
投稿2021/09/28 09:18
編集2021/09/28 09:20
総合スコア26
0
「4月以降も毎月増えていきます。」とのこと
例えば、フォームをクリックしたとき、サブフォームに年単位で表示できるようにするという方法は下記のようにすればよいかと思います。作る配列 myMonth() で範囲を決めることができます。
Private Sub コマンド0_Click()
Dim RES As Variant
Dim myYear As Long
Dim I As Long
Dim myMonth() As String
Dim mySQL As String
Dim BUF1 As String
Dim BUF2 As String
RES = InputBox("年を指定してください。", "年の指定", Format(Date, "yyyy/m")) If IsDate(RES) Then myYear = DatePart("yyyy", RES) If MsgBox("年は " & myYear & " でよろしいですか。", vbOKCancel, "確認") = vbCancel Then Exit Sub End If Else MsgBox "年の指定に誤りがあります。" Exit Sub End If ReDim myMonth(12) For I = 1 To 12 myMonth(I) = Format(DateSerial(myYear, I, 1), "yyyy年mm月") Next I mySQL = "SELECT AAA.配達先" For I = 1 To 12 mySQL = mySQL & ", IIF(NZ(AAA.[" & myMonth(I) & "配達回数]) = 0, 0, NZ(AAA.[" & myMonth(I) & "在宅数]) / AAA.[" & myMonth(I) & "配達回数] *100) AS " & myMonth(I) Next I BUF1 = "NZ(AAA.[" & myMonth(1) & "配達回数])" BUF2 = "NZ(AAA.[" & myMonth(1) & "在宅数])" For I = 2 To 12 BUF1 = BUF1 & " + NZ(AAA.[" & myMonth(I) & "配達回数])" BUF2 = BUF2 & " + NZ(AAA.[" & myMonth(I) & "在宅数])" Next I mySQL = mySQL & ", IIF(" & BUF1 & " = 0, 0,(" & BUF2 & ") / (" & BUF1 & ") * 100) AS 通期" mySQL = mySQL & " FROM (SELECT" mySQL = mySQL & " AA.配達先" For I = 1 To 12 mySQL = mySQL & " , MAX(IIF(AA.年月 = '" & myMonth(I) & "', AA.配達回数, NULL)) AS " & myMonth(I) & "配達回数" mySQL = mySQL & " , MAX(IIF(AA.年月 = '" & myMonth(I) & "', AA.在宅数, NULL)) AS " & myMonth(I) & "在宅数" Next I mySQL = mySQL & " FROM" mySQL = mySQL & " (SELECT YY.配達先, YY.年月, XX.配達回数, XX.在宅数" mySQL = mySQL & " FROM (SELECT X.配達先, Y.年月" mySQL = mySQL & " FROM (SELECT DISTINCT 収集データ.配達先 FROM 収集データ) AS X" mySQL = mySQL & " , (SELECT * FROM" mySQL = mySQL & " (SELECT '" & myMonth(1) & "' AS 年月 FROM Degit" For I = 2 To 12 mySQL = mySQL & " UNION" mySQL = mySQL & " SELECT '" & myMonth(I) & "' AS 年月 FROM Degit" Next I mySQL = mySQL & ") AS DUMMY) AS Y) AS YY" mySQL = mySQL & " LEFT JOIN" mySQL = mySQL & " (SELECT A.配達先, Format(A.配達日, 'yyyy\年mm\月') AS 年月, SUM(A.配達回数) AS 配達回数, SUM(A.在宅数) AS 在宅数" mySQL = mySQL & " FROM 収集データ AS A" mySQL = mySQL & " GROUP BY A.配達先, Format(A.配達日, 'yyyy\年mm\月')) AS XX ON YY.配達先 = XX.配達先 AND YY.年月 = XX.年月) AS AA" mySQL = mySQL & " GROUP BY AA.配達先) AS AAA;" Me.サブフォームコントロール.Form.RecordSource = mySQL
End Sub
投稿2021/09/26 09:57
総合スコア26
0
全くのド素人ですが、アクセスで考えてみました。
ユニオンで年月のテーブルを、集計テーブルから重複のない配達先テーブルを作り、それをクロス結合して配達先と年月のテーブルをマスターとします。それから、集計テーブルの配達日をFormat関数で年月に変換して、配達先と年月でグループ化して集計、配達先と年月で２月を落とさないように左外部結合しています。後は、IIf関数で場合分けして、月数集計、通期の集計をしています。
ユニオンのDegitはテーブルなら何でもいいようです。ないとエラーになります。
SELECT AAA.配達先
, IIF(NZ(AAA.[2021年01月配達回数]) = 0, 0, AAA.[2021年01月在宅数] / AAA.[2021年01月配達回数] *100) AS 2021年01月
, IIF(NZ(AAA.[2021年02月配達回数]) = 0, 0, AAA.[2021年02月在宅数] / AAA.[2021年02月配達回数] *100) AS 2021年02月
, IIF(NZ(AAA.[2021年03月配達回数]) = 0, 0, AAA.[2021年03月在宅数] / AAA.[2021年03月配達回数] *100) AS 2021年03月
, IIF(NZ(AAA.[2021年01月配達回数]) + NZ(AAA.[2021年02月配達回数]) + NZ(AAA.[2021年03月配達回数]) = 0, 0,(NZ(AAA.[2021年01月在宅数]) + NZ(AAA.[2021年02月在宅数]) + NZ(AAA.[2021年03月在宅数])) / (NZ(AAA.[2021年01月配達回数]) + NZ(AAA.[2021年02月配達回数]) + NZ(AAA.[2021年03月配達回数])) * 100) AS 通期
FROM
(SELECT
AA.配達先
, MAX(IIF(AA.年月 = '2021年01月',AA.配達回数,NULL)) AS 2021年01月配達回数
, MAX(IIF(AA.年月 = '2021年01月',AA.在宅数,NULL)) AS 2021年01月在宅数
, MAX(IIF(AA.年月 = '2021年02月',AA.配達回数,NULL)) AS 2021年02月配達回数
, MAX(IIF(AA.年月 = '2021年02月',AA.在宅数,NULL)) AS 2021年02月在宅数
, MAX(IIF(AA.年月 = '2021年03月',AA.配達回数,NULL)) AS 2021年03月配達回数
, MAX(IIF(AA.年月 = '2021年03月',AA.在宅数,NULL)) AS 2021年03月在宅数
FROM
(SELECT YY.配達先, YY.年月, XX.配達回数, XX.在宅数
FROM (SELECT X.配達先, Y.年月
FROM (SELECT DISTINCT 収集データ.配達先 FROM 収集データ) AS X
, (SELECT * FROM
(SELECT '2021年01月' AS 年月 FROM Degit
UNION
SELECT '2021年02月' AS 年月 FROM Degit
UNION
SELECT '2021年03月' AS 年月 FROM Degit) AS DUMMY) AS Y) AS YY
LEFT JOIN
(SELECT A.配達先, Format(A.配達日, 'yyyy\年mm\月') AS 年月, SUM(A.配達回数) AS 配達回数, SUM(A.在宅数) AS 在宅数
FROM 収集データ AS A
GROUP BY A.配達先, Format(A.配達日, 'yyyy\年mm\月')) AS XX ON YY.配達先 = XX.配達先 AND YY.年月 = XX.年月) AS AA
GROUP BY AA.配達先) AS AAA
投稿2021/09/26 06:47
編集2021/09/26 06:52総合スコア26
ご回答ありがとうございます。
SQLがネストするとAccessのほうが扱いやすいですよね。
「SELECT '2021年01月' AS 年月 FROM Degit」の書き方は初めて知りました。
収集データテーブルは、4月以降も毎月増えていきます。そのため今回作成したクエリは継続して使用されることを想定していました。質問に追記いたします。
あなたの回答
tips
プレビュー
