Access2003 クロス集計で集計値を四捨五入

質問

☆以下のようなテーブルで、フィールド[ID]～[点数]があり、文字および数字のデータが混在しています。
　　↓　　↓　　↓　　↓　　↓　　↓　　↓　　↓
===========================================
（テーブル(1)）
[ID] 　[クラス] 　[科目]　 [点数]　　 　
1　　　　　A　 　　 英語　　 　11　　　
2　　　　　B　 　　 英語　　 　22　　　
3　　　　　C 　　 　数学　　 　33　　　
4　　　　　A 　　 　国語　　　 44　　
5　　　　　B　 　　 数学　 　　55　　　
6　　　　　C 　　 　国語　　 　66　　　
7　　　　　B　 　　 国語　 　　77　　
8　　　　　C 　　 　英語　　 　88　　　　
9　　　　　A 　　 　数学　　　 99　　　　
===========================================

☆上記のテーブルをクロス集計クエリで集計する
・フィールド[クラス]を行見出しに設定、
・フィールド[科目]を列見出しに設定、
・フィールド[点数]を集計する項目にし主計方法を「合計」に設定する。
===========================================
[クロス集計結果]
　　合計　　　英語　　国語　　数学　　　　　　
A　　154　　　11　　　 44　　　 99　　　 　　　
B　　154　　　22　　　 88　　　 55　　　　
C　　187　　　99　　　 77　　　 33　
===========================================

[課題]　クロス集計クエリで、集計値を四捨五入する

(1)　フィールド[クラス]を行見出しに設定、
　　 フィールド[科目]を列見出しに設定、
　　 フィールド[点数]を集計する項目にし主計方法を「合計」に設定する。

(２)　クロス集計の値を、下1桁で四捨五入した値を表示する。

(３)　「合計」の値を、下1桁で四捨五入した値を表示する。

※ クロス集計した合計値を、四捨五入した値を表示する
　　↓　　↓　　↓　　↓　　↓　　↓　　↓　　↓
===========================================
[クロス集計結果]
　　合計　　　英語　　国語　　数学　　　　
A　　150　　　10　　　 40　　　100　　　 　　　
B　　150　　　20　　　 80　　　 60　　　　
C　　190　　　90　　　 70　　　 30　

===========================================
　　↑　　↑　　↑　　↑　　↑　　↑　　↑　　↑　　　
上記のように、クロス集計クエリでの集計値を四捨五入して
表示させたいのですが、どうすればよろしいでしょうか？
また、クエリだけでは出来ない場合は、SQLで教えていただければ
幸いでございます。

お手数お掛けしますが、何とぞ宜しくお願い致します。

回答No.10

30246kiku · ベストアンサー

実現する方法は１つでないと思いますので・・・
実際の環境に合うものなのか等々、自己責任にてお願いします。

前提条件）

・提示されたサンプルデータのテーブル名を「TB_A」とします。
・「クラス」と「科目」の組み合わせで、重複がないものとします。
（例えば、クラス「A」で科目「英語」のデータは１件）
（提示サンプルも重複がないみたいなので）

クエリのSQLビューは、

TRANSFORM ((First(点数)+5)\10)*10 AS 点数の値
SELECT クラス, ((Sum(点数)+5)\10)*10 AS 合計
FROM TB_A
GROUP BY クラス
PIVOT 科目;

ここで、クラス「B」の合計は、３科目の合計になっていませんが、
それはそれで良いでしょうか。20 + 80 + 60 = 160 ≠ 150

３科目の合計に合わせるのなら、

TRANSFORM ((First(点数)+5)\10)*10 AS 点数の値
SELECT クラス, Sum(((点数+5)\10)*10) AS 合計
FROM TB_A
GROUP BY クラス
PIVOT 科目;

合計部分で、
Sum した結果を四捨五入するか、
四捨五入したものを Sum するかの違いです。

四捨五入は、5を足して、10で割った商だけを求めて、10をかけて。
（過去回答で、「\」円マークが「?」に置き換わったことがあります）
（もし「?」になっているとしたら、そこは円マークです）

蛇足）

上記前提条件で、今回提示のテーブルデータを使い、
前回質問の平均行を追加した以下表示について、

クラス　合計　　英語　　国語　　数学
A　　　　154　　11　　　44　　　99
B　　　　154　　22　　　77　　　55
C　　　　187　　88　　　66　　　33
平均　　165　　40　　　62　　　62

サブクエリは使いますが、クロス集計クエリ１つでできそうです。

TRANSFORM IIF(S=0,First(点数),Int(Avg(点数))) AS 点数の値
SELECT クラス, IIF(S=0,Sum(点数),Int(Sum(点数)/S)) AS 合計
FROM (
SELECT 0 AS S, クラス, 科目, 点数 FROM TB_A
UNION ALL
SELECT T1.CT, '平均', TB_A.科目, TB_A.点数 FROM TB_A,
(SELECT Count(*) AS CT FROM (SELECT DISTINCT クラス FROM TB_A)) AS T1
) AS Q1
GROUP BY S, クラス
PIVOT 科目;

できる/使えるは違うと思います。
条件が変わった・・・問題発生すると思います。
クエリ２つの方が後々わかりやすい/使いやすいかも。

ちなみに、これであれば、科目に追加があっても変更する必要はありません。

[ID] 　[クラス] 　[科目]　 [点数]　　 　
10　　　　D 　　 　数学　　　 44　　　　
11　　　　D 　　 　物理　　　 55　　　　

が追加された時の結果は、

クラス　合計　英語　国語　数学　物理
A　　　　154　11　　44　　99　　空白
B　　　　154　22　　77　　55　　空白
C　　　　187　88　　66　　33　　空白
D　　　　99　空白　空白　　44　　55
平均　　148　40　　62　　　57　　55

科目部分の平均は、空白以外のもので計算されます。
空白部分を 0 として解釈したい場合は、

TRANSFORM IIF(S=0,First(点数),Int(Avg(点数))) AS 点数の値
↓
TRANSFORM IIF(S=0,First(点数),Int(Sum(点数)/S)) AS 点数の値

とすると、平均行部分は

クラス　合計　英語　国語　数学　物理
平均　　148　　30　　46　　57　　13

となり、

TRANSFORM IIF(S=0,First(点数),Round(Sum(点数)/S,2)) AS 点数の値
SELECT クラス, IIF(S=0,Sum(点数),Round(Sum(点数)/S,2)) AS 合計

と（小数点以下３桁目を四捨五入）すると、平均行部分は

クラス　合計　　英語　国語　数学　　物理
平均　　148.5　30.25　46.75　57.75　13.75

となります。

参考になれば・・・自己責任で・・・

お礼

30246kiku様

ご回答をありがとうございました。
早速試したところ、数値の四捨五入と平均行の追加の両方共に、私の希望通りの結果を得られました。SQL文１つで実現できるとは、感激いたしました！
また、SQLについて分かりやすく説明してくださり、とても勉強になりました。
これを活用できるよう、もっとSQLを勉強してみたいと思っております。
また質問させていただくこともあるかと存じますが、今後とも宜しくお願い致します。

回答No.11

30246kiku

＃１０です

>（もし「?」になっているとしたら、そこは円マークです）

「\」円マーク部分はバックスラッシュになったようです。

四捨五入計算部分になります。

全角で書くと、（Ｓｕｍ（点数）＋５）￥１０＊１０　のようになります。

回答No.9

layy

合計、英語、国語、数学
と出したクロス集計クエリに対し、
デザイン画面で、合計列をコピーし、平均とする。集計も平均とすれば、
合計、平均、英語、国語、数学
のクロス集計クエリが出来上がります。クロス集計クエリ１つです。

参考で。

回答No.8

piroin654

#5、#6は二つのクエリで出す平均です。平均は
(2)のユニオンクエリで表示されます。
なお、#4のクエリに表示ミスがありました。
以下に変更してください。

TRANSFORM funcSG(Sum([テーブル].[C])) AS Cの合計
SELECT [テーブル].[A], Sum(funcSG([テーブル].[C])) AS [合計 C]
FROM テーブル
GROUP BY [テーブル].[A]
PIVOT [テーブル].[B];

関数funcSGの中で、

'数字の右端の判定

とは、要するに下1桁の数値の判定です。

回答No.7

layy

（テーブル(1)）
[ID] 　[クラス] 　[科目]　 [点数]　 [四捨五入]　　 　
1　　　　　A　 　　 英語　　 　11　　　10
2　　　　　B　 　　 英語　　 　22　　　20
3　　　　　C 　　 　数学　　 　33　　　30

としないのでしょうか？。

各明細の四捨五入を求めてからクロス集計（の合計）と
各明細のクロス集計結果をそのまま四捨五入、
とで誤差がでていいのか、一応確認してください。

1　　　　　A　 　　 英語　　 　13
だったら
合計は[150]でなくて、[13+44+99=156=160]では？。

まず、各明細で四捨五入を求めるのでは？。

クロス集計の作り方を考える「課題」であればどちらでもいいですが・・・。

お礼

layy 様

早々のご回答をありがとうございました。
上記の集計結果のようにしない理由は、クロス集計クエリで作成した集計表の列と行の両方に集計値(合計や平均など)を表示する方法を知りたかったのです。また、クロス集計した値を四捨五入し、その四捨五入した値の集計（合計や平均など）を算出したかったたため合計の値がズレてしまっておりました。
色々とアドバイスをいただき、大変勉強になりました。早速、活用してみたいと思います。
また質問等をさせていただくこともあるかと思いますが、宜しくお願い致します。

回答No.6

piroin654

すみません。訂正です。
(2)の中の"英数国クロス集計"は"英数国"の
つもりでしたが、ついでですから、変更しやすい
クロス集計クエリの名前を、"英数国クロス集計"
で保存してください。

回答No.5

piroin654

(1)
クロス集計クエリの作成：
以下のSQLを新しいクエリのSQLビューに貼り付け、
英数国という名前で保存してください。
テーブルはテーブル名のつもりです。実際に
合わせて変更してください。

TRANSFORM Sum(テーブル.C) AS Cの合計
SELECT テーブル.A, Sum(テーブル.C) AS [合計 C]
FROM テーブル
GROUP BY テーブル.A
PIVOT テーブル.B;

(2)
同様に、以下のSQL文を新しいクエリのSQLビューに
貼り付け、適当な名前で保存してください。長いので、
コピー、貼り付けに注意をしてください。

SELECT 英数国クロス集計.A, 英数国クロス集計.[合計 C], 英数国クロス集計.英語, 英数国クロス集計.国語, 英数国クロス集計.数学
FROM 英数国クロス集計
UNION SELECT '平均' AS A平均, Int(Avg([合計 C])) AS 合計平均, Int(Avg([英語])) AS 英語平均, Int(Avg([国語])) AS 国語平均, Int(Avg([数学])) AS 数学平均
FROM 英数国クロス集計;

(2)のクエリはユニオンクエリです。たぶん、
クエリがよく分かっている人はこの方法に
気づいていると思いますが。

なお、この場合、たとえば、国語などに
点数が一部無い場合、点数を何もテーブルに
入れない状態ですと、平均はその無い場合を
カウントせずに平均をだします。しかし、
0を入れるとその0をカウントして平均を
出します。つまり、テーブルのNullと0
を分けてカウントし、平均を出します。

説明がごちゃごちゃしましたが、テーブルに
データが無い場合、クエリでその部分を
0に表示し、なおかつ、0をカウントしないで
平均を出したいということになると、
また、ややこしいことをするようになるので
先に述べておきます。

普通に使うにはこれで十分です。

回答No.4

piroin654

別に$MSの関数が、という
わけではありませんが、今回の場合に
即して、自作関数を設定してみました。

下一桁で四捨五入なので、以下を
標準モジュールに設定します。

Function funcSG(ByVal x As Long) As Long
Dim y As Long
If x > 0 Then
'数字の右端の判定
y = Right(x, 1)
If y > 4 Then
funcSG = x - y + 10
Else
funcSG = x - y
End If
End If
End Function

クロス集計クエリの設定：
"テーブル名"は実際のテーブル名を入れてください。

TRANSFORM funcSG(Sum([テーブル名].[C])) AS Cの合計
SELECT テーブル名.A, funcSG(Sum([テーブル名].[C])) AS [合計 C]
FROM テーブル名
GROUP BY テーブル名.A
PIVOT テーブル名.B;

なお、
http://oshiete1.watch.impress.co.jp/qa6455150.html
は二つのクエリでできます。必要なら表示します。

お礼

piroin654 様

ご回答くださりありがとうございました。
自作関数をモジュールに設定し、SQLを実行してみましたところ、おかげ様で希望通りに四捨五入の集計結果を得ることができました。

いつも、ご丁寧なご説明をいただき大変感謝いたしております。
また質問させて頂くこともあると思いますが、今後とも宜しくお願い致します。

補足

piroin654 様

早々のご回答をありがとうございました。早速、試してみたいと思います。

「クロス集計クエリでの合計と平均」について、二つのクエリでできるとのアドバイスをいただきましたが、よろしければクエリでの作成方法を教えていただけると助かります。

お手数お掛け致しますが、宜しくお願い致します。

回答No.3

nicotinism

Access の Round関数は銀行型丸めです。
例
round(85/10,0.1)*10 も round(75/10,0.1)*10 も結果は、80 になります。

なので＃１で紹介されている関数を標準モジュールにいれます。
なお関数名はややこしくなるのでMyRoundにします。
また通貨型で返ってきますので、今回の場合はDouble型で十分でしょう。

Function MyRound (X As Double, s As Integer) As Double
　　Dim t As Double　　　　　　 ’変数の宣言をします。
　　t = 10 ^ Abs(s)　　　　　　　 ’ Abs 関数で s の絶対値に 10 を掛けます。
　　If s > 0 Then　　　　　　　　 ’ s > 0 の時は、
　　　 MyRound = Int(X*t + 0.5) / t　’ Int( X * t　+ 0.5 ) / t の値を返します。
　　 Else　　　　　　　　　　　　　’ s <= 0 の時は、
　　　 MyRound = Int(X/t + 0.5) * t　’ Int( X / t +0.5) * t の値を返します。
　　 End If
End Function

あるいは int(数字/10+0.5)*10でも今回の場合は大丈夫です。

せっかくなのでMyRoundを使うとして
クエリのデザインビューが多分こうなっているかと思いますが
（T1はテーブル名です）

クラス　　　科目　　　　数の合計　　合計:点数
T1　　　　　T1　　　　　　　T1　　　　　　　T1
グループ化　グループ化　　　合計　　　　　合計
行見出し　　列見出し　　　　　値　　　　　行見出し

これを

クラス　　　科目　　　　点数の合計:myround(sum(T1.点数),1)　合計:myround(sum(T1.点数),1)
T1　　　　　　　　　　　　　　　　　　　　　
グループ化　グループ化　　　演算　　　　　　　　　　　　　　　演算
行見出し　　列見出し　　　　値　　　　　　　　　　　　　　　　行見出し

にしてみてください。
SQLビューだと、
TRANSFORM myround(Sum(T1.点数),1) AS 点数の合計
SELECT T1.クラス, myround(Sum(T1.点数),1) AS 合計
FROM T1
GROUP BY T1.クラス
PIVOT T1.科目;

※SQL文をグラフィック的に見やすくしたのがクエリです
SQL≒クエリ

お礼

nicotinism 様
ご回答いただき、ありがとうございました。
標準モジュールやSQLについて、大変分かりやすく説明してくださり、とても勉強になりました。
これを活用できるよう、もっとSQLを勉強してみたいと思っております。
また、質問させていただくこともあるかと存じますが、今後とも宜しくお願い致します。

補足

nicotinism 様
早々のご回答をありがとうございました。
早速、モジュールを設定しSQLを試してみたのですが、私のスキル不足のためか、四捨五入での集計結果をえることができませんでした。
クエリを実行すると、通常の合計値が表示されてしまいました。。

もしよろしければ、再度ご教授いただだければ幸いでございます。
宜しくお願い致します。

回答No.2

kmetu

2003だったんですね(件名のところを見落としてました)その場合Round関数は元々ありますので自作する必要がありませんから

たとえば、連結テキストボックスにクエリの「英語」を連結し非表示にした後
非連結テキスト ボックスを作成しそのコントロールソースを

=Round([英語]/10,0)*10

にします。

お礼

Kmetu様
ご回答いただき、ありがとうございました。
これを活用できるよう、もっとSQLを勉強してみたいと思っております。
また、質問させていただくこともあるかと存じますが、今後とも宜しくお願い致します。

回答No.1

kmetu

自作関数を作成してそれをフォームなどで使う方法です

[ACC1]四捨五入の計算方法

http://support.microsoft.com/kb/402836/ja

補足

早々のご回答をありがとうございました。
上記サイトを参考にしてみたのですが、私のスキル不足のため理解できませんでした。
フォームを使い四捨五入できるようですが、どうすればこの値をクロス集計の結果に反映できるのか分かりませんでした。
お手数お掛けして恐縮ですが、教えていただけると助かります。