☆以下のようなテーブルで、フィールド[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) フィールド[クラス]を行見出しに設定、
フィールド[科目]を列見出しに設定、
フィールド[点数]を集計する項目にし主計方法を「合計」に設定する。
(2) クロス集計の値を、下1桁で四捨五入した値を表示する。
(3) 「合計」の値を、下1桁で四捨五入した値を表示する。
※ クロス集計した合計値を、四捨五入した値を表示する
↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
===========================================
[クロス集計結果]
合計 英語 国語 数学
A 150 10 40 100
B 150 20 80 60
C 190 90 70 30
===========================================
↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑
上記のように、クロス集計クエリでの集計値を四捨五入して
表示させたいのですが、どうすればよろしいでしょうか?
また、クエリだけでは出来ない場合は、SQLで教えていただければ
幸いでございます。
お手数お掛けしますが、何とぞ宜しくお願い致します。
No.11
- 回答日時:
#10です
>(もし「?」になっているとしたら、そこは円マークです)
「\」円マーク部分はバックスラッシュになったようです。
四捨五入計算部分になります。
全角で書くと、(Sum(点数)+5)¥10*10 のようになります。
No.10ベストアンサー
- 回答日時:
実現する方法は1つでないと思いますので・・・
実際の環境に合うものなのか等々、自己責任にてお願いします。
前提条件)
・提示されたサンプルデータのテーブル名を「TB_A」とします。
・「クラス」と「科目」の組み合わせで、重複がないものとします。
(例えば、クラス「A」で科目「英語」のデータは1件)
(提示サンプルも重複がないみたいなので)
クエリのSQLビューは、
TRANSFORM ((First(点数)+5)\10)*10 AS 点数の値
SELECT クラス, ((Sum(点数)+5)\10)*10 AS 合計
FROM TB_A
GROUP BY クラス
PIVOT 科目;
ここで、クラス「B」の合計は、3科目の合計になっていませんが、
それはそれで良いでしょうか。20 + 80 + 60 = 160 ≠ 150
3科目の合計に合わせるのなら、
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
サブクエリは使いますが、クロス集計クエリ1つでできそうです。
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 科目;
できる/使えるは違うと思います。
条件が変わった・・・問題発生すると思います。
クエリ2つの方が後々わかりやすい/使いやすいかも。
ちなみに、これであれば、科目に追加があっても変更する必要はありません。
[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 合計
と(小数点以下3桁目を四捨五入)すると、平均行部分は
クラス 合計 英語 国語 数学 物理
平均 148.5 30.25 46.75 57.75 13.75
となります。
参考になれば・・・自己責任で・・・
30246kiku様
ご回答をありがとうございました。
早速試したところ、数値の四捨五入と平均行の追加の両方共に、私の希望通りの結果を得られました。SQL文1つで実現できるとは、感激いたしました!
また、SQLについて分かりやすく説明してくださり、とても勉強になりました。
これを活用できるよう、もっとSQLを勉強してみたいと思っております。
また質問させていただくこともあるかと存じますが、今後とも宜しくお願い致します。
No.9
- 回答日時:
合計、英語、国語、数学
と出したクロス集計クエリに対し、
デザイン画面で、合計列をコピーし、平均とする。集計も平均とすれば、
合計、平均、英語、国語、数学
のクロス集計クエリが出来上がります。クロス集計クエリ1つです。
参考で。
No.8
- 回答日時:
#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
- 回答日時:
(テーブル(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
- 回答日時:
すみません。
訂正です。(2)の中の"英数国クロス集計"は"英数国"の
つもりでしたが、ついでですから、変更しやすい
クロス集計クエリの名前を、"英数国クロス集計"
で保存してください。
No.5
- 回答日時:
(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
- 回答日時:
別に$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 様
早々のご回答をありがとうございました。早速、試してみたいと思います。
「クロス集計クエリでの合計と平均」について、二つのクエリでできるとのアドバイスをいただきましたが、よろしければクエリでの作成方法を教えていただけると助かります。
お手数お掛け致しますが、宜しくお願い致します。
piroin654 様
ご回答くださりありがとうございました。
自作関数をモジュールに設定し、SQLを実行してみましたところ、おかげ様で希望通りに四捨五入の集計結果を得ることができました。
いつも、ご丁寧なご説明をいただき大変感謝いたしております。
また質問させて頂くこともあると思いますが、今後とも宜しくお願い致します。
No.3
- 回答日時:
Access の Round関数は銀行型丸めです。
例
round(85/10,0.1)*10 も round(75/10,0.1)*10 も結果は、80 になります。
なので#1で紹介されている関数を標準モジュールにいれます。
なお関数名はややこしくなるので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を試してみたのですが、私のスキル不足のためか、四捨五入での集計結果をえることができませんでした。
クエリを実行すると、通常の合計値が表示されてしまいました。。
もしよろしければ、再度ご教授いただだければ幸いでございます。
宜しくお願い致します。
nicotinism 様
ご回答いただき、ありがとうございました。
標準モジュールやSQLについて、大変分かりやすく説明してくださり、とても勉強になりました。
これを活用できるよう、もっとSQLを勉強してみたいと思っております。
また、質問させていただくこともあるかと存じますが、今後とも宜しくお願い致します。
No.2
- 回答日時:
2003だったんですね(件名のところを見落としてました)その場合Round関数は元々ありますので自作する必要がありませんから
たとえば、連結テキストボックスにクエリの「英語」を連結し非表示にした後
非連結テキスト ボックスを作成しそのコントロールソースを
=Round([英語]/10,0)*10
にします。
Kmetu様
ご回答いただき、ありがとうございました。
これを活用できるよう、もっとSQLを勉強してみたいと思っております。
また、質問させていただくこともあるかと存じますが、今後とも宜しくお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Access(アクセス) Access クエリ 同一テーブル内 複数フィールドの同時集計のやり方について 1 2022/05/18 19:01
- Excel(エクセル) 別シートに毎回異なるデータをコピーする 7 2022/06/24 09:02
- Excel(エクセル) エクセルのセルの書式設定・ユーザー定義の条件設定について 1 2022/08/17 21:56
- Access(アクセス) お世話になっています いまクエリを作っています。。 デザインビューの画面の集計の欄で、「グループ化」 4 2022/09/17 17:03
- MySQL 下記の問合せを行うクエリを、PhpMyAdminで作成して実行せよ。 第二回模試の3科目の各得点と合 1 2023/04/25 18:02
- Excel(エクセル) IF 関数で「〇〇 という文字を含む場合」の分岐処理で表示された数字はSUMで数字集計できますか? 3 2022/08/02 16:29
- MySQL 三科目合計点のクラス別平均点求めるクエリ式を教えてください 1 2023/07/04 09:44
- その他(Microsoft Office) ピボットテーブルへの集計フィールド挿入 1 2023/02/26 11:33
- C言語・C++・C# C言語 プログラミング 4 2022/05/22 11:53
- 統計学 母集団分布を平均 μ, 分散 σ2 の正規分布と想定し, 母集団から無作為抽出した標本のデータ(標本 4 2023/01/30 20:25
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
クロス集計クエリ
-
Accessのクロス集計から構成比...
-
【アクセス】クエリで、行と列...
-
年齢層で集計したい
-
ピボットでの時間集計
-
エクセルのピポットテーブル
-
Access 2003、2007 なぜクロス...
-
販売在庫管理ソフトの内容で教えて
-
accessでクロス集計した結果を...
-
差込印刷での全角表示について...
-
日付型のフィールドに空白を入...
-
SQL文で パラメータが少なすぎ...
-
エクセルデータをワードで差し...
-
テキストボックスにクエリ結果...
-
アクセスのエラー「クエリには...
-
ACCESS クエリの抽出条件に他の...
-
ACCESSの時刻の引き算
-
Accessの桁区切りについ...
-
ACCESSで2つフィールドの日付...
-
実行時エラー '3464': 抽出条件...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ピボットでの時間集計
-
【アクセス】クエリで、行と列...
-
エクセルのピボットで、集計値...
-
Access 条件に合うレコード数を...
-
Accessのクロス集計から構成比...
-
ACCESS2000で複数のクエリをフ...
-
Access2003 クロス集計で集計値...
-
統計学 ファイ係数
-
お世話になっています いまクエ...
-
クロス集計クエリの合計 テキ...
-
【アクセス】空白があると集計...
-
access2010 クロス集計後の計算...
-
Access 構成比 クエリ集計
-
年齢層で集計したい
-
Excelで売上げデータの中の任意...
-
Access クロス集計 レポート...
-
アクセスの集計クエリの件
-
Access2003 クロス集計クエリ...
-
クロス集計の日付表示について
-
Accessの更新クエリについて質...
おすすめ情報