Excelのピボットテーブルで関数を使った新しい集計フィールドを追加してみます。
前回、演算子（“+”、“-”、“*”、“/”等）を使った新しい集計フィールドを追加してみましたが、今度はExcel関数を使ってみます。
ただ、どんな関数も使えるというわけではないので注意してくださいね
例えばこんな受注データのピボットテーブルがあります。
ちなみに、フィールドリストはこんな感じです。
さて、この“受注日”から“YEAR関数”で“年”を取り出して表示してみます。
「ピボットテーブルツール」「オプション」「ツール」「数式」から「集計フィールド」を選択します。
「集計フィールドの挿入」画面が開きます。
名前は“年”と入力します。
数式には“=year(” と入力した後に、「フィールド」から“受注日”を選択して「フィールドの挿入」を押します。
そして“)” で閉じます。“数式”に“year関数”を使った式が入力できました。
「OK」すると、“受注日”の隣に“合計/年”というフィールドが追加されました。
関数で使用した“受注日”の書式が日付設定だったため、年の書式も日付になってしまっています。
書式を“標準”に戻します。
フィールドリストの「合計/年」を左クリックして「値フィールドの設定」を選択します。
「値フィールドの設定画面」で「表示形式」を押します。
いつもの「セルの書式設定」画面が開きますので「標準」を選択して「OK」します。
すると、受注日から年（4桁の整数）が取り出されて表示されました。
それから、ここでは行ラベルに“受注コード”を指定していますので、全て“1件”のデータになります。なので、“年“の値フィールドでの集計方法は“合計”になっていますが“平均”でも構いません（が、“データの個数”ではダメですよ。“1”になっちゃうので。。）
フィールドリストを見ると“年”というデータソース（元データ）には存在しないフィールド追加されています。
ついでに、“MONTH関数”で受注日から“月”も取り出してみましょう。
年と同じように「集計フィールドの挿入」画面を開いて、
名前は“月”、数式に“MONTH関数”を入力します。
すると、受注日から“月”だけが取り出されました。（これも、書式を“標準”に戻します）
但し、新しく作った集計フィールドは値専用なので、“年”や“月”を行ラベルや列ラベルに設定することはできませんので悪しからず。。。
さて、あとはどんな関数が使えるかというと。。。
こんな、検査データを集計したピボットテーブルがあって、
フィールドリストはこんな感じになっています。
この検査データを“ROUND関数”で小数点第一位で四捨五入してみましょう。
「集計フィールドの挿入」画面を開いて、数式に“ROUND関数”を入力します。
“ROUND関数”では引数が2つありますが、問題ありません。
すると、こんなふうに四捨五入できました。
新しく作る集計フィールドは「Σ値」専用のフィールドになります。行ラベル、列ラベルには使用できません。
「Σ値」の集計方法は数値以外は“データの個数”になるので、結局使える関数も、求める結果が数値にならないと使えないと言うことになります。
それから、“合計”や“平均値”のような引数に範囲を指定しなければならない関数も使用できません。
ということで、せっかくのExcel関数ですが、用途と活用はかなり制限されちゃいます。。
関連ページ
新しい集計フィールドを追加する
前回、演算子（“+”、“-”、“*”、“/”等）を使った新しい集計フィールドを追加してみましたが、今度はExcel関数を使ってみます。
ただ、どんな関数も使えるというわけではないので注意してくださいね
例えばこんな受注データのピボットテーブルがあります。
ちなみに、フィールドリストはこんな感じです。
さて、この“受注日”から“YEAR関数”で“年”を取り出して表示してみます。
「ピボットテーブルツール」「オプション」「ツール」「数式」から「集計フィールド」を選択します。
「集計フィールドの挿入」画面が開きます。
名前は“年”と入力します。
数式には“=year(” と入力した後に、「フィールド」から“受注日”を選択して「フィールドの挿入」を押します。
そして“)” で閉じます。“数式”に“year関数”を使った式が入力できました。
「OK」すると、“受注日”の隣に“合計/年”というフィールドが追加されました。
関数で使用した“受注日”の書式が日付設定だったため、年の書式も日付になってしまっています。
書式を“標準”に戻します。
フィールドリストの「合計/年」を左クリックして「値フィールドの設定」を選択します。
「値フィールドの設定画面」で「表示形式」を押します。
いつもの「セルの書式設定」画面が開きますので「標準」を選択して「OK」します。
すると、受注日から年（4桁の整数）が取り出されて表示されました。
それから、ここでは行ラベルに“受注コード”を指定していますので、全て“1件”のデータになります。なので、“年“の値フィールドでの集計方法は“合計”になっていますが“平均”でも構いません（が、“データの個数”ではダメですよ。“1”になっちゃうので。。）
フィールドリストを見ると“年”というデータソース（元データ）には存在しないフィールド追加されています。
ついでに、“MONTH関数”で受注日から“月”も取り出してみましょう。
年と同じように「集計フィールドの挿入」画面を開いて、
名前は“月”、数式に“MONTH関数”を入力します。
すると、受注日から“月”だけが取り出されました。（これも、書式を“標準”に戻します）
但し、新しく作った集計フィールドは値専用なので、“年”や“月”を行ラベルや列ラベルに設定することはできませんので悪しからず。。。
さて、あとはどんな関数が使えるかというと。。。
こんな、検査データを集計したピボットテーブルがあって、
フィールドリストはこんな感じになっています。
この検査データを“ROUND関数”で小数点第一位で四捨五入してみましょう。
「集計フィールドの挿入」画面を開いて、数式に“ROUND関数”を入力します。
“ROUND関数”では引数が2つありますが、問題ありません。
すると、こんなふうに四捨五入できました。
新しく作る集計フィールドは「Σ値」専用のフィールドになります。行ラベル、列ラベルには使用できません。
「Σ値」の集計方法は数値以外は“データの個数”になるので、結局使える関数も、求める結果が数値にならないと使えないと言うことになります。
それから、“合計”や“平均値”のような引数に範囲を指定しなければならない関数も使用できません。
ということで、せっかくのExcel関数ですが、用途と活用はかなり制限されちゃいます。。
関連ページ
新しい集計フィールドを追加する