月別、年別、四半期別などで集計する

投稿日:2017年9月22日 更新日:

[Access 2016/2013/2010/2007]

月や年、四半期など、一定の期間でグループ化して集計したいことがあります。この記事では、図1の[Q_受注明細]クエリのレコードを、月ごとにグループ化して集計する手順を例に集計方法を説明します。

図1a [Q_受注明細]クエリに、2016年4月~2018年3月の売上データがまとめられている。ここでは、[受注日]フィールドの月ごとに[金額]を合計して…(→図1b)。

図1b このような集計表を作りたい。

なお、クエリでの集計や抽出について、詳しくは「【初心者向け】クエリで集計する基本の基本」「【初心者向け】集計クエリでの抽出の基本」を参照してください。

スポンサーリンク

「年月」でグループ化して集計する

「年月」でグループ化して集計を行うには、[受注日]フィールドから「年月」を取り出す必要があります。

Accessでは日付から年や月を取り出すための関数が豊富にありますが、中でもFormat関数(構文1、正式な構文はFormat関数のページを参照)は万能選手です。引数[書式]の設定次第で、日付からさまざまな情報を取り出せます。

構文1 データに書式を設定して返す
Format(データ, 書式)

日付から「2016年01月」の形式で年月を取り出すには、Format関数の引数[書式]に「"yyyy\年mm\月"」を指定します(式1)。「\」は、次に入力された文字をそのまま表示するための記号です。クエリで「"yyyy年mm月"」と入力すると、自動的に「\」が補われます。

式1 [受注日]フィールドから「年月」を取り出す
受注月: Format([受注日],"yyyy\年mm\月")

新規クエリに[集計]行を表示し、[受注月]フィールドで[グループ化]、[金額]フィールドで[合計]を指定すると(図2)、年月ごとに金額を集計できます(図3)。

図2
1)新規クエリで[データ]タブの[集計]ボタンをクリックして[集計]行を表示しておく。
2)[フィールド]欄に式1を入力して、[グループ化][昇順]を選択する。
3)[フィールド]欄に「合計金額: 金額」と入力して[合計]を選択する。

図3 「年月」ごとに集計できた。

なお、今回、[受注日]から「月」のほかに「年」も取り出したのは、[受注日]フィールドに複数年にわたる日付が入力されているからです。「月」だけでグループ化すると、異なる年の同じ月が1つのグループにまとめられてしまうので注意してください。

「年」「四半期」「月」の取り出し方

「年」「四半期」「月」などでグループ化するときに役立つ関数式を紹介します。結果が文字列になるか数値になるかは、次項で解説する並べ替えに影響します。

([日付]は「1991/04/03」とする)

結果 結果の種類
 Year([日付])  1991  数値
 Year([日付]) & "年"  1991年  文字列
 DatePart("yyyy",[日付])  1991  数値
 Format([日付],"yyyy")  1991  文字列
 Format([日付],"yyyy\年")  1991年  文字列
 Format([日付],"gggee\年")  平成03年  文字列
 Format([日付],"gee")  H03  文字列

四半期([日付]は「1991/04/03」とする)

結果 結果の種類
 DatePart("q",[日付])  2  数値
 "第" & DatePart("q",[日付]) & "四半期"  第2四半期  文字列
 "第" & Choose(Month([日付]),4,4,4,1,1,1,2,2,2,3,3,3) & "四半期"  第1四半期  文字列
 Format([日付],"yyyy""年第""q""四半期""")  1991年第2四半期  文字列
 Format(DateAdd("m",-3,[日付]),"yyyy""年第""q""四半期""")  1991年第1四半期  文字列

※ 四半期を求める考え方については「日付が第何四半期にあたるかを調べるには」を参照してください。

([日付]は「1991/04/03」とする)

結果 結果の種類
 Month([日付])  4  数値
 Month([日付]) & "月"  4月  文字列
 DatePart("m",[日付])  4  数値
 Format([日付],"mm")  04  文字列
 Format([日付],"mm\月")  04月  文字列

並べ替えについての補足

並べ替えについて補足しておきます。文字列の月を昇順に並べる場合、図2でやったように月を2桁にしておくと正しい順序で並びます。

 01月、02月、03月、04月、……、11月、12月

「月」を1桁にして「1月」~「12月」を昇順に並べ替える場合は、

× 10月、11月、12月、1月、……、8月、9月

となり、正しい順序になりません。文字列の月を並べるときは、注意してください。

ちなみに、Year関数やMonth関数の戻り値は数値データなので、図4図5のように数値だけを取り出して並べ替える場合は、「月」を2桁にしなくても正しい順序になります。

図4 Year関数で「年」、Month関数で「月」を取り出して、グループ化し、昇順で並べ替える。

図5 「年」「月」フィールドは数値なので、きちんと昇順に並ぶ。

スポンサーリンク

関連記事

Copyright© Officeのチカラ , 2018 All Rights Reserved.