スポンサーサイト 


上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

--/--/--

Category: スポンサー広告

TB: --  /  CM: --

top △

クロス集計でどう? 


めっきり寒くなってきましたね。
今日は、雪もチラつきました・・・

そんな事はどうでも良いんで・・・・

以下の様な質問がありました

an商品金額担当A担当B
1 リンゴ300 田中小林
2 メロン1000 田中 
3 バナナ500 山本田中
4 マンゴー800 小林 
5 キウイ200 山本小林
というデータがあった時

田中_商品田中_金額小林_商品小林_金額山本_商品山本_金額
 バナナ250  キウイ100  キウイ100 
 メロン1000  マンゴー800  バナナ250 
 リンゴ150  リンゴ150  計350 
 計1400  計1050   
としたい・・・・

また、
担当金額
田中1400 
小林1050 
山本350 
2800 
っていうものも作りたい・・・・

つまり、金額は、
・担当A のみの時には丸々
・担当B も埋まっていたら、折半
・・・・

で、私が回答した内容は以下になるのですが・・・

テーブル名を ★★ と仮定します。
SQL ビューで記述します。

1つ目(やりたい事と違うかもしれませんが、添付図上側)
クロス集計を使います。

TRANSFORM Sum(金額) AS 値
SELECT 商品 FROM (
SELECT 0 AS S, 商品, IIF(担当B Is Null,★★.金額,★★.金額\2) AS 金額, 担当A AS 担当 FROM ★★
UNION ALL
SELECT 0, 商品, 金額\2, 担当B FROM ★★ WHERE 担当B Is Not Null
UNION ALL
SELECT 1, '計', IIF(担当B Is Null,金額,金額\2), 担当A FROM ★★
UNION ALL
SELECT 1, '計', 金額\2, 担当B FROM ★★ WHERE 担当B Is Not Null
) AS Q1
GROUP BY S, 商品
PIVOT 担当;

2つ目

SELECT 担当, Sum(Q1.金額) AS 金額 FROM (
SELECT 0 AS S, IIF(担当B Is Null,★★.金額,★★.金額\2) AS 金額, 担当A AS 担当 FROM ★★
UNION ALL
SELECT 0, 金額\2, 担当B FROM ★★ WHERE 担当B Is Not Null
UNION ALL
SELECT 1, IIF(担当B Is Null,金額,金額\2), '計' FROM ★★
UNION ALL
SELECT 1, 金額\2, '計' FROM ★★ WHERE 担当B Is Not Null
) AS Q1
GROUP BY S, 担当;

※ 1つ目、2つ目でのデータの並び順(計金額の多い人順)を考えているのなら
 1つ目については VBA を使う事になると思います。
 2つ目については、最後の
GROUP BY S, 担当
の後に
ORDER BY S, Sum(Q1.金額) DESC
を追加すれば良いと思います。

※ 1つ目はやりたい事と違うと思いますので、他の方の回答を待ってください。
 また、レポート上の事であれば他の方の回答を待ってください。

で、添付していた画像は

y_q14116172494.jpg

レポートの事はてんでわかりません。
データの再利用を主に、Excel へ展開するのが多いので・・・ チンプンカンプン

回答において、

※ 1つ目、2つ目でのデータの並び順(計金額の多い人順)を考えているのなら
 1つ目については VBA を使う事になると思います。

と記述していた部分を、詳しく?書いてみたいと思います。
 
実現するには、細工が何個か必要になります。

元々のテーブルを「T1」とし、以下の様になっていると仮定します
(オートナンバ「an」は必須じゃありません)
an商品金額担当A担当B
1 リンゴ300 田中小林
2 メロン1000 田中 
3 バナナ500 山本田中
4 マンゴー800 小林 
5 キウイ200 山本小林

これを元に、クエリ「Q_T1」を作成します。
SELECT 商品, Sum(Q1.金額) AS 金額, 担当
FROM (SELECT 商品, IIF(担当B Is Null, T1.金額, T1.金額\2) AS 金額, 担当A AS 担当 FROM T1
UNION ALL
SELECT 商品, 金額\2, 担当B FROM T1 WHERE 担当B Is Not Null) AS Q1
GROUP BY 商品, 担当;

これの表示は以下の様になります。
商品金額担当
 キウイ100 山本
 キウイ100 小林
 バナナ250 山本
 バナナ250 田中
 マンゴー800 小林
 メロン1000 田中
 リンゴ150 小林
 リンゴ150 田中

これは・・・ つまり、商品、担当での金額合計を求めておくものになります。
で、求めたいのは、1人に対して2項目になるので、レコード数を倍にして・・・
××の時にはこれで・・・○○の時にはあれで・・・集計をずらしてやる必要があります。
レコード数を単に倍にする方法として、テーブルとテーブルの直積を使う方法とします。

テーブル「T1A」を用意しますが、フィールドは「F」(長整数)のみで
1 と 2 のデータだけを入れておきます。
で、クエリ「Q_T1」とテーブル「T1A」の直積指定してみると
SELECT * FROM Q_T1, T1A;

で結果を見ていると
商品金額担当F
 キウイ100 山本1
 キウイ100 小林1
 バナナ250 山本1
 バナナ250 田中1
 マンゴー800 小林1
 メロン1000 田中1
 リンゴ150 小林1
 リンゴ150 田中1
 キウイ100 山本2
 キウイ100 小林2
 バナナ250 山本2
 バナナ250 田中2
 マンゴー800 小林2
 メロン1000 田中2
 リンゴ150 小林2
 リンゴ150 田中2
となります。

これらを使って、クロス集計「Q_T1クロス」で整形してみると
TRANSFORM First(Choose(Q2.F,Q1.商品,Q1.金額)) AS 値
SELECT Q1.CT AS 件数
FROM (SELECT Count(*) AS CT, QA.商品, QA.金額, QA.担当 FROM Q_T1 AS QA INNER JOIN Q_T1 AS QB ON (QA.担当=QB.担当) AND (QA.商品>=QB.商品)
GROUP BY QA.商品, QA.金額, QA.担当
UNION ALL
SELECT Count(*)+1, '計', Sum(金額), 担当 FROM Q_T1 GROUP BY 担当) AS Q1, T1A AS Q2
GROUP BY Q1.CT
PIVOT Q1.担当 & Choose(Q2.F,"_商品","_金額");

元々のテーブル「T1」には、レコード順を求める事が出来るものは存在しません。
よって、その担当者が扱った商品の名前順で並べる事とします。
SELECT Count(*) AS CT, QA.商品, QA.金額, QA.担当 FROM Q_T1 AS QA INNER JOIN Q_T1 AS QB ON
(QA.担当=QB.担当) AND (QA.商品>=QB.商品)
GROUP BY QA.商品, QA.金額, QA.担当;
で、
CT商品金額担当
1 キウイ100 山本
1 キウイ100 小林
2 バナナ250 山本
1 バナナ250 田中
2 マンゴー800 小林
2 メロン1000 田中
3 リンゴ150 小林
3 リンゴ150 田中
が求められます。
また
SELECT Count(*)+1, '計', Sum(金額), 担当 FROM Q_T1 GROUP BY 担当;

Expr1000Expr1001Expr1002担当
3350 山本
41050 小林
41400 田中
が求まります。
さらに、
SELECT * FROM (
SELECT Count(*) AS CT, QA.商品, QA.金額, QA.担当 FROM Q_T1 AS QA INNER JOIN Q_T1 AS QB ON
(QA.担当=QB.担当) AND (QA.商品>=QB.商品)
GROUP BY QA.商品, QA.金額, QA.担当
UNION ALL
SELECT Count(*)+1, '計', Sum(金額), 担当 FROM Q_T1 GROUP BY 担当) AS Q1, T1A AS Q2;
では
CT商品金額担当F
1 キウイ100 山本1
1 キウイ100 小林1
2 バナナ250 山本1
1 バナナ250 田中1
2 マンゴー800 小林1
2 メロン1000 田中1
3 リンゴ150 小林1
3 リンゴ150 田中1
3 計350 山本1
4 計1050 小林1
4 計1400 田中1
1 キウイ100 山本2
1 キウイ100 小林2
2 バナナ250 山本2
1 バナナ250 田中2
2 マンゴー800 小林2
2 メロン1000 田中2
3 リンゴ150 小林2
3 リンゴ150 田中2
3 計350 山本2
4 計1050 小林2
4 計1400 田中2
のようになります。

で、このデータを元に前述したクロス集計「Q_T1クロス」の整形で表示されるものは
件数山本_金額山本_商品小林_金額小林_商品田中_金額田中_商品
1100  キウイ100  キウイ250  バナナ
2250  バナナ800  マンゴー1000  メロン
3350  計150  リンゴ150  リンゴ
4  1050  計1400  計
の様になります。
※ 行見出しには何か指定しないといけないようなので、CT(件数)を指定する事に
※ なお、金額部分を右詰に表示していましたが、実際には左詰めになります
※ 1人に対して項目が2つになりますが、この2つで扱えるデータの型は1つ(文字列? Variant?)

何か違和感ありますね・・・・
そう・・・ 商品と金額の表示順が逆ですね・・・
これは、「山本_商品」「山本_金額」を比較してみると、「山本_金額」の方が上(文字列比較)なんですね
順を揃えるには、
PIVOT Q1.担当 & Choose(Q2.F,"_商品","_金額");
のところで、文字列として順を決めれるように
PIVOT Q1.担当 & Choose(Q2.F,"_1商品","_2金額");
にするとか・・・・
また、全体的に逆順であれば、
TRANSFORM First(Choose(Q2.F,Q1.商品,Q1.金額)) AS 値
SELECT Q1.CT AS 件数
FROM (SELECT Count(*) AS CT, QA.商品, QA.金額, QA.担当 FROM Q_T1 AS QA INNER JOIN Q_T1 AS QB ON
(QA.担当=QB.担当) AND (QA.商品>=QB.商品)
GROUP BY QA.商品, QA.金額, QA.担当
UNION ALL
SELECT Count(*)+1, '計', Sum(金額), 担当 FROM Q_T1 GROUP BY 担当) AS Q1, T1A AS Q2
GROUP BY Q1.CT
ORDER BY Q1.担当 & Choose(Q2.F,"_商品","_金額") DESC
PIVOT Q1.担当 & Choose(Q2.F,"_商品","_金額");
とすれば以下の様な表示にはなります。

件数田中_商品田中_金額小林_商品小林_金額山本_商品山本_金額
1 バナナ250  キウイ100  キウイ100 
2 メロン1000  マンゴー800  バナナ250 
3 リンゴ150  リンゴ150  計350 
4 計1400  計1050   

ただね、「計」金額順にしたわけではないので・・・・

「計」金額順に並べたい・・・ これは VBA を使わないと・・・ でしょ・・・
VBA で何をしたいのかを言っておくと、「列見出し」を設定したいだけです・・・
列見出しを設定すると、その設定した順で、設定したものしか表示しない・・・・ ここまで OK ですか
列見出し・・・ っていうと
PIVOT Q1.担当 & Choose(Q2.F,"_商品","_金額")
のところに
PIVOT Q1.担当 & Choose(Q2.F,"_商品","_金額") In ('田中_商品','田中_金額','小林_商品','小林_金額')
って記述すると
'田中_商品','田中_金額','小林_商品','小林_金額' の分だけ、記述した順番に表示するものになります。

そこで、クロス集計「Q_T1クロス」を元に、列見出しを付加した「Q_T1クロス順」を作りましょうか
以下のものを VBA で記述します。(標準モジュール「Module1」で)
Public Sub QryMake2()
  Dim rs As New ADODB.Recordset
  Dim qdf As DAO.QueryDef
  Dim sSql As String
  Dim sS As String
  Dim v As Variant
  Const CQryName As String = "Q_T1クロス"

  On Error Resume Next
  sS = ""
  rs.Source = "SELECT 担当 FROM Q_T1 GROUP BY 担当 ORDER BY Sum(金額) DESC, 担当;"
  rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  While (Not rs.EOF)
    For Each v In Array("_商品", "_金額")
      sS = sS & ", '" & rs(0) & v & "'"
    Next
    rs.MoveNext
  Wend
  rs.Close
  If (Len(sS) > 0) Then
    sS = " In (" & Mid(sS, 3) & ");"
    sSql = CurrentDb.QueryDefs(CQryName).SQL
    sSql = Replace(sSql, ";", sS)
    Set qdf = CurrentDb.QueryDefs(CQryName & "順")
    If (qdf Is Nothing) Then
      CurrentDb.CreateQueryDef CQryName & "順", sSql
    Else
      qdf.SQL = sSql
    End If
    Set qdf = Nothing
    DoCmd.OpenQuery CQryName & "順"
  End If
End Sub

 
これは、クエリ「Q_T1」(担当者と商品での金額合計したもの)をみて、
・金額の多い人から文字列を作成して
・クロス集計「Q_T1クロス」の最後の「;」を「 In (・・・・・);」に書き換えたもので「Q_T1クロス順」を作る
つまり
TRANSFORM First(Choose(Q2.F,Q1.商品,Q1.金額)) AS 値
SELECT Q1.CT AS 件数
FROM (SELECT Count(*) AS CT, QA.商品, QA.金額, QA.担当 FROM Q_T1 AS QA INNER JOIN Q_T1 AS QB ON (QA.担当=QB.担当) AND (QA.商品>=QB.商品)
GROUP BY QA.商品, QA.金額, QA.担当
UNION ALL
SELECT Count(*)+1, '計', Sum(金額), 担当 FROM Q_T1 GROUP BY 担当) AS Q1, T1A AS Q2
GROUP BY Q1.CT
PIVOT Q1.担当 & Choose(Q2.F,"_商品","_金額");

TRANSFORM First(Choose(Q2.F,Q1.商品,Q1.金額)) AS 値
SELECT Q1.CT AS 件数
FROM (SELECT Count(*) AS CT, QA.商品, QA.金額, QA.担当 FROM Q_T1 AS QA INNER JOIN Q_T1 AS QB ON (QA.担当=QB.担当) AND (QA.商品>=QB.商品)
GROUP BY QA.商品, QA.金額, QA.担当
UNION ALL
SELECT Count(*)+1, '計', Sum(金額), 担当 FROM Q_T1 GROUP BY 担当) AS Q1, T1A AS Q2
GROUP BY Q1.CT
PIVOT Q1.担当 & Choose(Q2.F,"_商品","_金額") In ('田中_商品', '田中_金額', '小林_商品', '小林_金額', '山本_商品', '山本_金額');
に書き換えて・・・・表示しましょうか・・・ というもの

上記と同じように、単なるクロス集計「Q_回答クロス集計」の表示は
商品山本小林田中
 キウイ100 100  
 バナナ250  250 
 マンゴー 800  
 メロン  1000 
 リンゴ 150 150 
 計350 1050 1400 
ですが
クエリの内容を
TRANSFORM Sum(金額) AS 値
SELECT 商品
FROM (SELECT 0 AS S, 商品, IIF(担当B Is Null, T1.金額, T1.金額\2) AS 金額, 担当A AS 担当 FROM T1
UNION ALL
SELECT 0, 商品, 金額\2, 担当B FROM T1 WHERE 担当B Is Not Null
UNION ALL
SELECT 1, "計", IIF(担当B Is Null, T1.金額, T1.金額\2), 担当A AS 担当 FROM T1
UNION ALL
SELECT 1, "計", 金額\2, 担当B FROM T1 WHERE 担当B Is Not Null
) AS Q1
GROUP BY S, 商品
PIVOT 担当;

TRANSFORM Sum(Q1.金額) AS 値
SELECT Q1.商品
FROM (SELECT 0 AS S, 商品, IIF(担当B Is Null, T1.金額, T1.金額\2) AS 金額, 担当A AS 担当 FROM T1
UNION ALL
SELECT 0, 商品, 金額\2, 担当B FROM T1 WHERE 担当B Is Not Null
UNION ALL
SELECT 1, "計", IIF(担当B Is Null, T1.金額, T1.金額\2), 担当A AS 担当 FROM T1
UNION ALL
SELECT 1, "計", 金額\2, 担当B FROM T1 WHERE 担当B Is Not Null
) AS Q1
GROUP BY Q1.S, Q1.商品
PIVOT Q1.担当 In ('田中', '小林', '山本');
とするために、以下の VBA を記述しました(標準モジュール「Module1」で)
Public Sub QryMake1()
  Dim rs As New ADODB.Recordset
  Dim qdf As DAO.QueryDef
  Dim sSql As String
  Dim sS As String
  Const CQryName As String = "Q_回答クロス集計"

  On Error Resume Next
  sS = ""
  rs.Source = "SELECT 担当 FROM Q_T1 GROUP BY 担当 ORDER BY Sum(金額) DESC, 担当;"
  rs.Open , CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  While (Not rs.EOF)
    sS = sS & ", '" & rs(0) & "'"
    rs.MoveNext
  Wend
  rs.Close
  If (Len(sS) > 0) Then
    sS = " In (" & Mid(sS, 3) & ");"
    sSql = CurrentDb.QueryDefs(CQryName).SQL
    sSql = Replace(sSql, ";", sS)
    Set qdf = CurrentDb.QueryDefs(CQryName & "順")
    If (qdf Is Nothing) Then
      CurrentDb.CreateQueryDef CQryName & "順", sSql
    Else
      qdf.SQL = sSql
    End If
    Set qdf = Nothing
    DoCmd.OpenQuery CQryName & "順"
  End If
End Sub

 

おおざっぱに説明すると、こんな感じですね。

※ 元クエリの SQL を使って××する時、テーブル修飾がつく場合があります。
例えば、
「Q_回答クロス集計」に記述していた
TRANSFORM Sum(金額) AS 値
部分が
TRANSFORM Sum(Q1.金額) AS 値
になったり・・・
何か弊害があるわけではありません。
むしろ、初めからテーブル修飾した書き方にしていれば同じものになると思います。

なお、VBA の最後で
    DoCmd.OpenQuery CQryName & "順"
として、出来上がったクエリを表示していましたが、処理に合わせてもらえればと・・・

※ 私は ADO で勉強し始めたので、レコードセットを扱う時には ADO を使ってしまいます。
 けど、クエリとか扱う時には DAO が楽なので DAO を使っています。
 レコードセットを扱う部分を DAO にしても記述の大差なく実現できると思いますので
 ご自分の環境にあったものを使われたらと思います。


事前に用意していたクエリ一覧

「Q_T1クロス」:「Q_T1」を利用したクロス集計(VBAで扱う元クエリ、QryMake2 にて)
「Q_T1クロス1」:上記の逆順表示
「Q_T1クロス2」:上記で件数を求める時に DCount を使ってみた
「Q_回答クロス集計」:回答した内容(VBAで扱う元クエリ、QryMake1 にて)
「Q_T1」:商品と担当で合計金額を求めるクエリ
「Q_回答合計」:回答した内容
「Q_回答合計順」:上記の合計金額降順

VBA を実行して出来上がるクエリ

「Q_T1クロス順」:合計金額順に並べる列見出しを付加(SQL のベースは 「Q_T1クロス」)
「Q_回答クロス集計順」:合計金額順に並べる列見出しを付加(SQL のベースは 「Q_回答クロス集計」)


サンプルは以下
 バージョン 20002003 (2002)2007
 ファイル kEnt186_2000.zipkEnt186_2003.zipkEnt186_2007.zip
 サイズ 19,13119,56221,308
※ ファイルは zip 形式
※ 2007 以外は、2007 保存時に変換 & 各バージョンで動作確認 & 最適化


【追記】11/11
クロス集計を Excel 出力しながら整形する・・・って
過去記事「 クロス集計のExcel出力 」でやってみていますので、興味あれば参照ください

関連記事

2013/11/11

Category: クエリ

TB: --  /  CM: 0

top △

この記事に対するコメント

top △

コメントの投稿

Secret

top △


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。