2014年08月15日

Excelの関数を使って新しい集計フィールドを追加してみた/Excel/ピボットテーブル

Excelのピボットテーブルで関数を使った新しい集計フィールドを追加してみます。
前回、演算子(“+”、“-”、“*”、“/”等)を使った新しい集計フィールドを追加してみましたが、今度はExcel関数を使ってみます。
ただ、どんな関数も使えるというわけではないので注意してくださいね
20140815_00関数を使った集計フィールド.jpg

例えばこんな受注データのピボットテーブルがあります。
20140815_01.JPG

ちなみに、フィールドリストはこんな感じです。
20140815_02.JPG

さて、この“受注日”から“YEAR関数”で“年”を取り出して表示してみます。
「ピボットテーブルツール」「オプション」「ツール」「数式」から「集計フィールド」を選択します。
20140815_03.JPG

「集計フィールドの挿入」画面が開きます。
名前は“年”と入力します。
数式には“=year(” と入力した後に、「フィールド」から“受注日”を選択して「フィールドの挿入」を押します。
20140815_04.JPG

そして“)” で閉じます。“数式”に“year関数”を使った式が入力できました。
20140815_05.JPG

「OK」すると、“受注日”の隣に“合計/年”というフィールドが追加されました。
関数で使用した“受注日”の書式が日付設定だったため、年の書式も日付になってしまっています。
20140815_06.JPG

書式を“標準”に戻します。
フィールドリストの「合計/年」を左クリックして「値フィールドの設定」を選択します。
20140815_07.JPG

「値フィールドの設定画面」で「表示形式」を押します。
20140815_08.JPG

いつもの「セルの書式設定」画面が開きますので「標準」を選択して「OK」します。
20140815_09.JPG

すると、受注日から年(4桁の整数)が取り出されて表示されました。
20140815_10.JPG
それから、ここでは行ラベルに“受注コード”を指定していますので、全て“1件”のデータになります。なので、“年“の値フィールドでの集計方法は“合計”になっていますが“平均”でも構いません(が、“データの個数”ではダメですよ。“1”になっちゃうので。。)

フィールドリストを見ると“年”というデータソース(元データ)には存在しないフィールド追加されています。
20140815_11.JPG

ついでに、“MONTH関数”で受注日から“月”も取り出してみましょう。
年と同じように「集計フィールドの挿入」画面を開いて、
名前は“月”、数式に“MONTH関数”を入力します。
20140815_12.JPG

すると、受注日から“月”だけが取り出されました。(これも、書式を“標準”に戻します)
20140815_13.JPG

但し、新しく作った集計フィールドは値専用なので、“年”や“月”を行ラベルや列ラベルに設定することはできませんので悪しからず。。。

さて、あとはどんな関数が使えるかというと。。。
こんな、検査データを集計したピボットテーブルがあって、
20140815_14.JPG

フィールドリストはこんな感じになっています。
20140815_15.JPG

この検査データを“ROUND関数”で小数点第一位で四捨五入してみましょう。
「集計フィールドの挿入」画面を開いて、数式に“ROUND関数”を入力します。
“ROUND関数”では引数が2つありますが、問題ありません。
20140815_16.JPG

すると、こんなふうに四捨五入できました。
20140815_17.JPG

新しく作る集計フィールドは「Σ値」専用のフィールドになります。行ラベル、列ラベルには使用できません。
「Σ値」の集計方法は数値以外は“データの個数”になるので、結局使える関数も、求める結果が数値にならないと使えないと言うことになります。
それから、“合計”や“平均値”のような引数に範囲を指定しなければならない関数も使用できません。
ということで、せっかくのExcel関数ですが、用途と活用はかなり制限されちゃいます。。

関連ページ
新しい集計フィールドを追加する


posted by haku1569 at 18:43| Excel ピボットテーブル | このブログの読者になる | 更新情報をチェックする
×

この広告は180日以上新しい記事の投稿がないブログに表示されております。