haku1569　Excel でらくらく データ分析！

Excelのピボットテーブルでセルの書式を一括で設定してみます。
前回、“値の書式を設定する”で「（値）フィールドの設定」を使ってフィールドデータの書式設定を行いましたが、値に複数のフィールドが設定してあって、同じ書式を設定する場合は、いちいち「（値）フィールドの設定」で書式設定するのは面倒です。
今回は範囲を選択し一括で書式を設定する方法をやってみましょう。


例えば受注データがあって、商品名毎の“単価”と“金額”が集計されています。


ちなみにフィールドリストはこうなってます。
単価は平均、金額は合計に設定しています。


ここで、“単価”と“金額”の書式を通貨表示にしてみたいと思います。
ピボットテーブルの中にカーソルを置いて、「ピボットテーブルツール」「オプション」「アクション」「選択」から「ピボットテーブル全体」を選択します。
（この段階ではその他は選択できません）


すると、ピボットテーブル全体が選択範囲になりました。


一旦全体を選択範囲にして、もう一度「選択」を見ると今度は全てが選択可能になっています。
そこから「値」を選択します。


すると、値だけが選択範囲になりました。


選択範囲の中で右くりし、「セルの書式設定」を選択します。
（「表示形式」や「値フィールの設定」ではありませんよ！）


「セルの書式設定」画面が開きます。「通貨」を選択します。


すると、“単価”と“金額”の書式が通貨に変更になりました。


ラベルの書式も一括で変更してみましょう。
今度も、「ピボットテーブル全体」を選択範囲にしてから、「選択」から「ラベル」を選択します。


すると、ラベルだけが選択範囲になりました。


選択範囲の中で右くりしてテキストの色を青にしてみます。


ラベルのテキストの色が青になりました。



関連ページ
値の書式を設定する（値フィールドの設定）

Excelのピボットテーブルのレイアウトを変えてみましょう。
ピボットテーブルでは“コンパクト形式”、“アウトライン形式”、“表形式”の3種類のレイアウトから表示方法を選択できます。


例えば受注データがあって、商品名毎の仕入れ先名と金額がピボットテーブルで集計されています。

コンパクト形式
何も指定してないと“コンパクト形式”で表示されます。
“商品名”と“仕入先名”が同じ“B列”に表示され、横幅がコンパクトに表示されます。


ちなみに、フィールドリストはこうなってます。


“B列”の“行ラベル”のフィルターボタンを押してみると、“商品名”のフィルターが表示されます。


得意先名のフィルターはどうかけるかというと。。。
「フィールドの選択」の▼ボタンを押すと、“得意先名”が表示されるので選択します。


すると、“得意先名”のフィルターが表示されました。


それから、最初は“商品名”の小計は最初に表示されていますが、、
「ピボットテーブルツール」「デザイン」「レイアウト」「小計」から「すべての小計をグループの末尾に表示する」を選択します。


と、グループの最後に小計の行が追加されます。


アウトライン形式
では、次に「アウトライン形式」で表示してみましょう。
「ピボットテーブルツール」「デザイン」「レイアウト」「レポートのレイアウト」から「アウトライン形式で表示」を選択します。


すると、こんな風に、、
“商品名”と“得意先名”が別の列で表示さらました。その分、横幅が広くなっちゃいますね。


例えば、「小計を表示しない」にしてみます。


すると、小計の表示はなくなりましたが“商品名”の表示は空白行で1行つかわれています。


表形式
では、次は「表形式」
同じように「表形式で表示」を選択します。


すると、こん風になります。
各フィールドはそれぞれの列に表示されます。
ただ、「アウトライン形式」と違うのは“商品名”のアイテムの表示だけに1行使われるのではなく、“得意先名”の先頭の列に表示されています。
それから、セルの枠線も表示されるようになります。


「小計をグループの先頭に表示する」を選択してみます。


ところが、先頭行は“得意先名”の先頭行と重なっているので、小計の表示はされません。


「小計を表示しない」を選択してみます。


すると、ほとんど普通のワークシートの作表と同じような集計表になりました。


このように小計を非表示にすると、右端の金額の列で並べ替えできそうな気になるのですが、実際は並べ替えは“商品名”の小計と、“商品名”の中の“得意先名”での並べ替えしかできないので、勘違いしないようにしましょうね。
詳しくはコチラをご覧ください。。


関連ページ
値を並び替えしてみた（表形式の時に並び替えが出来ない時は？）

Excelのピボットテーブルでテキストデータのフィールドのアイテムを選択してグループ化してみます。
数値データや日付（中身はシリアル値なので数値です）のフィールドをを行ラベルや列ラベルに設定すると自動的にグループ化の対象になりますが、テキストデータのフィールドはグループ化の対象にはなりません。
そんな時に、アイテムを選択することでそれらをグループ化させることができます。


例えば受注データがあって、得意先名毎の受注金額がピボットテーブルで集計されています。


行ラベルの得意先名にカーソルを置いて。「ピボットテーブルツール」「オプション」「グループ」を見ても「グループフィールド」はアクティブになっていません（灰色のまま）


そこで、グループ化したいアイテムを選択していきます。「Ctrl」を押しながら選択すると任意のアイテムを複数選択できます。選択するとセルの背景が薄灰色になります。


そこで再び、「ピボットテーブルツール」「オプション」「グループ」から「グループの選択」を選択します。


すると選択したアイテムが“ｸﾞﾙｰﾌﾟ1”という名前でグループ化されました。
グループ化されていないアイテムは、それぞれの名前で1アイテムずつグループ化されています。


“ｸﾞﾙｰﾌﾟ1”は自由に名前を変えることができます。ここでは“�潟Aリス”系列の得意先としてグループ化してみました。


見易くするために、グループ化されたアイテムを折りたたんで非表示にします。
「ピボットテーブルツール」「オプション」「アクティブなフィールド」から「フィールド全体の折りたたみ」を選択します。


と、グループだけの表示になりました。


で、ついでに+ボタンも非表示にしちゃいましょう。
「ピボットテーブルツール」「オプション」「表示/非表示」から「+/-ボタン」を選択します。


と、スッキリ見易くなりましたね。


んで、このグループ化を解除するには、「ピボットテーブルツール」「グループ」から「グループ解除」を選択します。


すると解除されて最初の状態に戻りました。



関連ページ
日毎のデータを年月で集計してみた（グループ化）
日付でグループ化出来ない（グループフィールドボタンが押せない）時は

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


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


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


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


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


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


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


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


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


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


すると、受注日から年（4桁の整数）が取り出されて表示されました。

それから、ここでは行ラベルに“受注コード”を指定していますので、全て“1件”のデータになります。なので、“年“の値フィールドでの集計方法は“合計”になっていますが“平均”でも構いません（が、“データの個数”ではダメですよ。“1”になっちゃうので。。）

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


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


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


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

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


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


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


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


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

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

Excelのピボットテーブルで平均値、最小値、最大値の集計をやってみます。
ピボットテーブルでは、指定しないと数値は“合計”、数値以外は“データの個数”が集計されます。
「値フィールドの集計の方法」のは、その他にもいろいろな計算の種類が用意されています。
今回は、その中から「平均」、「最小値」、「最大値」を実際に集計してみましょう。


まず、こんな50個の検査データがあります。
一番下に平均値、最小値、最大値を確認のために関数で求めておきます。
ちなみに、平均値は“ABERAGE関数”、最小値は“MIN関数”、最大値は“MAX関数”。


その隣にピボットテーブルを挿入します。
とりあえず「Σ値」のリストに“検査データ”を3回ドラッグすると、検査データは数値なので、みんな“合計”が集計されています。


フィールドリストはこんな感じです。


ではまず、平均値を集計してみます。
一番上の「合計/検査データ」を左クリック（右クリじゃありません）し「値フィールドの設定」を選択します。


「値フィールドの設定」画面が開きます。
タブ「集計の方法」から「平均」を選択します。


次に、「合計/検査データ2」をクリックし同様に「値フィールドの設定」画面から「最小値」を選択します。


同じように、「合計/検査データ3」をクリックし「値フィールドの設定」画面から「最大値」を選択します。


すると、合計が表示されていたピボットテーブルは“平均”、“最小値”、“最大値”の集計に変わり、関数での計算結果と一致していることがわかります。


では、次の例です。
日毎の受注データがあって、“品名”毎に“金額”の“平均”、“最小値”、“最大値”を集計するピボットテーブルを作りました。


フィールドリストはこんなかんじです。


さて、この“平均値”ってなんでしょうか？
分子は何となく金額の合計値って思いますが、分母は？
受注日、1日当たりの金額？それとも数量、1個当たりの金額・・・？

前の検査データの例をやったので、もうおわかりかもしれませんが、、
分母は、受注件数。つまり元データ（データソース）のレコードの数（行数）です。
つまり、“データの個数”です。
元データで確認してみると、“あおのりあじさい”の登場した金額の合計を件数で割ったもです。


“平均値”と言うと、なにか統計的に意味のある代表値であるかのような先入観をもって見てしまいがちですが、そういうことではありません。
ピボットテーブルでベースになるのは“データの個数”と“値の合計”。それらから求められるものが「値フィールド」の「計算の種類」に登場してきます。
例えば、“標準偏差”とか“分散”とかも、だいたいどんなものか想像できますよね。“平均値”が分かれば求められるものです。

と、いうわけで検査データの様なデータソースの場合は“平均値”とか“標準偏差”は統計的に意味を持つ集計結果になりますが、この受注データのようなケースだと分かりずらいものになってしまいます。
つまり、元データ（データソース）を作り方によって、この集計結果の持つ意味は変わってしまうということですね。。。


関連ページ
データの個数(レコードの件数）を集計してみた

Excelのピボットテーブルでレポートフィルタでラベルに指定していないフィールドの特定のアイテムでデータの抽出をしてみます。
データの抽出にはラベルに設定したフィールドでフィルタをかける方法もありますが、ワンポイントのアイテムでデータを抽出する場合は、レポートフィルタを使うのが便利ですよ。


まず、こんな受注データの集計があります。
“得意先名”と“金額”だけのピボットテーブルになっています。


フィールドリストのレポートフィルタに“受注日”をドラッグします。


すると、集計表の上に“受注日”と表示されたレポートフィルタが追加されました。
“（すべて）”のフィルターボタンをクリックして、抽出したい受注日を選択します。ここでは“2014/8/3”を選択してみます。


すると、“2014/8/3”だけのデータ（酒蔵でんしかなかったんですね）が集計されました。


この、レポートフィルタは複数のアイテムを選択することもできます。
フィルタボタンをクリックして「複数のアイテムを選択」にチェックを入れます。
するとフィルタのアイテム全てにチェックボックスが表示されました。
ここで、8月の受注日に全てチェックを入れます。


すると、8月のデータが抽出されました。


また、レポートフィルタには複数のフィールドを設定することもできます。
“在籍支社”をレポートフィルタにドラッグします。


在籍支社のフィルタから“大阪支社”を選択してみます


すると、“8月”の“大阪支社”のデータが抽出集計されました。次は「行ラベル」のリス


関連ページ
ラベルにフィルタをかける
値にフィルタをかける
日付にフィルタをかける

Excelのピボットテーブルのクロス集計表で複数の値（金額と数量とか）の表示位置（配置）を変えてみます。
“複数の値（数量と金額とか）を縦と横に表示位置を変更する”で通常の集計表で金額と数量の値の表示位置を変えてみましたが、クロス集計表で値の表示位置（レイアウト）を実際にやってみました。


まず、こんなクロス集計があります。
行ラベルには“得意先名”、列ラベルには“商品名”が設定されています。値は“金額”と“数量”が列ラベルに設定されています。


フィールドリストはこうなっています。


では、次に“Σ値”を“商品名”の上にドラッグします。


すると、列ラベルはまず金額毎の集計がされて、その後数量毎の集計が配置されます。


次は“Σ値”を行ラベルに持ってきます。（“得意先名”の下に設定してます）
「Σ値」のリストには上に“金額”その下に“数量”を設定してます。


すると、行ラベルの“得意先名”毎に“金額”、“数量”が集計されました。


次に、「Σ値」のリストで“数量”を“金額”の上にドラッグします。


すると、先ほどの集計の“金額”と“数量”が入れ替わりました。


次は「行ラベル」のリストで“Σ値”を“得意先名”の上にドラッグします。


すると、行ラベルはまず数量毎の集計がされて、その後金額毎の集計が配置されます。


次に、「Σ値」のリストで“金額”を“数量”の上にドラッグします。


すると、先ほどの集計の“数量”と“金額”が入れ替わりました。



関連ページ
複数の値（数量と金額とか）を縦と横に表示位置を変更する
クロス集計表を簡単に作成する

Excelのピボットテーブルで値を昇順・降順で並び替えさせてみます。
行ラベル等に複数のフィールが設定されている時は、何のフィールドの値を並び替えするかで、値の並びが変わってきます。
その辺をよく理解していないと、表形式で表示されている時に「並び替えが出来ないっ！」と慌てるハメになっちゃいますよ！


例えば受注データがあって、行ラベルに“得意先名”値に“金額”を指定してピボットテーブルを作りました。


で、金額の値にカーソルを置いて、右クリして「並べ替え」から「昇順」を選択します。


すると、金額の小さい順に並べ替えができます。


次に、一旦並べ替えを元に戻したうえで、行ラベルに“在籍支社”を“得意先名”の上に設定します。
すると、在籍支社毎の得意先名と（受注）金額の集計が出来上がりました。


さて、ここからが並べ替えのキモ（肝）です。
集計表の一番上の“大阪支社”の小計の金額の上にカーソルを置いて、さっきと同じように昇順で並べ替えをしてみます。つまり、在籍支社での並べ替えが行われるわけです。


すると、“在籍支社”の小計金額が小さい順に並べ変えられました。
“北九州支社”が一番少なかったようですね。
中の“得意先名”毎の金額では並べ替えはされていません。


次に、一旦並べ替えを元に戻して、今度は“大阪支社”の中の“得意先名”の金額にカーソルを置いて並べ替えをします。


すると、“大阪支社”の中の“得意先”の金額が小さい順に並べ替えられました。


“大阪支社”の下の“東京本社”も見てみると、同じように“得意先”の金額順に並べ替えがされています。


このように、行ラベルに複数のフィールドを設定すると、行ラベルのボックスに並べたフィールドが下にあるもの（“得意先名”）はその上のフィールド（“在籍支社”）の中での並べ替えになるわけです。

ところが、それの仕組みが一見分かりずらくなってしまう、集計表の体裁があります。
「ピボットテーブルツール」「デザイン」「小計」から「小計を表示しない」を選択します。


次に、同様に「レポートのレイアウト」から「表形式で表示」を選択します。


するとこんな風な、見慣れた普通のワークシートのようなピボットテーブルになります。


ここで、金額の上で並べ替えを行っても、、


“在籍支社”の中で“得意先名”の金額順に並べ替えが行われるだけです。


“在籍支社”の小計は非表示にしているため、もはや“在籍支社”毎の並べ替えはできません。

だけど、まだこれは序の口で、次に紹介する例こそ「並べ替えができないーー！」と思わず叫んでしまいそうになるピボットテーブルです。
行ラベルに“得意先コード”が“得意先名”の上に設定してあります。
“得意先コード”と言うのは、“得意先名”につけられた識別コードで、“得意先コード”と“得意先名”は1対1の関係になっています。なので、１つの“得意先コード”の中には１つの“得意先名”しか含まれません。“在籍支社”のようにたくさんの“得意先名”が含まれるわけではないのです。


で、ここで並べ替えをしようとするとどうなるか、、、


ピクリともしません！！


何故だかわもう、わかりますよね。
でも、いきなりこの状態にでくわすと、叫びたくなるわけです。

で、このようなピボットテーブルの時にどうやって並べ替えするかと言うと、
まず、“得意先コード”の小計を表示させて、、


で、その小計で並べ替えをします。


んで、また小計を非表示にしてあげれば。。。

はい。できあがり！


関連ページ

Excelのピボットテーブルでグループ化を使って前年同月比較を行って、去年注文があったのに今年なくなった得意先名を調べてみます。


受注データから、“得意先名”を行ラベルに、“受注日”を列ラベルにしてピボットテーブルを作ります。値は“金額”の合計値にします。


すると、こんなふうなピボットテーブルが出来上がります。


受注日を“年”、“月”で抽出できるようにグループ化します。
「ピボットテーブルツール」「オプション」から「グループフィールド」を選択します。


「グループ化」画面が開きます。
ここで、「月」と「年」を選択します。


「OK」すると、このように“年”と“月”でグループ化され集計されました。
フィールドリストの列ラベルを見ると新たに“年”というグループ化によって作られたフィールドが“受注日”上に追加されているのがわかります。


さて、比較する“年”、“月”を抽出します。
ここでは、2013年と2014年の5月の受注状況を比較してみます。
列ラベル“年”の右側のフィルターボタンをクリックして、「2013年」と「2014年」にチェックをいれます。


次に、“受注日”のフィルターボタンから「5月」にチェックを入れます。


「OK」すると、このように得意先毎の2013年と2014年の5月の受注金額の集計表ができあがりました。


ここで、2014年の5月の金額にカーソルを置いて、右クリして「並べ替え」から「昇順」を選択します。


すると、このように、今年受注の無かった得意先が一目瞭然になりました。


さて、このデータからどのように要因分析していくか、自社の状況、得意先の状況、競合先の状況、一般消費者の状況などなど。。。
それらに関するデータの収集と分析も必要になるのかもしれません。。。ね！


関連ページ
日付でグループ化出来ない（グループフィールドボタンが押せない）時は
「○○年以前」をひとくくりにして集計する

Excelでピボットテーブルを作ってみます。

ピボットテーブルはデータの集計表から簡単にいろいろな集計を自由自在に行うことができるとっても便利な機能です。そして、一旦ピボットテーブルで集計表を作ってしまえば、元データが追加されたり、変更されても、「更新」を行えば、元データの追加、変更された内容をすぐに反映させることができます。
いちいち集計表を作り直したりする必要がありません。つまり、毎月行っている集計作業や、検査のたびに行っているデータ集計などの定型業務に適した集計の手段だと思います。


ではさっそく、ピボットテーブルを作ってみましょう。（とっても簡単ですよ）
まず例えば、こんな食品の問屋さんの受注データがあります。
受注日毎の、受注先の得意先名、商品名、数量、金額等が入力されています。
（ホントはもっと色々な入力項目があるのですが表をコンパクトにするために非表示にしてあります）
あっ、ピボットテーブルを作る時に使うこの元データですが、作る時にコツがあります。こちらをご覧ください。元データの作り方によってピボットテーブルが上手く作れないときがあります。。。


表の中にカーソルを置いて、「挿入」から「ピボットテーブルツール」を選択します。


「ピボットテーブルの作成」画面が開きます。
テーブル/範囲：元データのデータが入力されている範囲が自動的に選択されるのでそのままでOKです。
ピボットテーブルレポートを配置する場所：元データの表が小さければ同じワークシート「既存のワークシート」でも良いのですが、そうでない場合は「新規のワークシート」の方のボタンを選択しておきます。
そうすると、元データのワークシートの手前に新しいシートができてそこにピボットテーブルが作成されます。


「OK」すると、新しいシートにピボットテーブルが作成（挿入）されました。


これで、ピボットテーブルができました。おしまいです！
後は、このピボットテーブルを使って集計を行うことができます。

ためしに、受注日毎の金額の合計を集計してみましょう。
右側の「ピボットテーブルのフィールドリスト」の上のチェックボックスが付いた一覧（これは、元データのフィールド（列の項目）が全て表示されます）から、「受注日」を「行ラベル」のボックスにマウスでドラッグ&ドロップします。


次に、「金額」を「Σ　値」のボックスにドラッグ&ドロップします。


するとアッと言う間に、受注日毎の金額の合計の集計ができちゃいました！


今度は、「行ラベル」のボックスの「受注日」をフィールドの一覧のボックスにドラッグ&ドロップで戻して、代わりに「得意先名」「行ラベル」にドラッグドロップします。
すると、得意先名毎の金額の合計が集計できました。

ねっ、こういうことがマウス操作だけで簡単にできちゃくでしょ？スゴイでしょ！？

では、ピボットテーブルの「更新」をやってみましょう。
元データの“2012年6月14日”の“アリス亭”の“ベター生ハム”の数量を“10”個から“100”個に変更してみます。


んで、ピボットテーブルの中にカーソルを置いて、「ピボットテーブルツール」「オプション」から「（データ）更新」を押すか、


ピボットテーブルの中で右クリして表示されるメニューから「更新」でもOKです。


すると、“アリス亭”の金額が今まで“376000”だったのが“916000”に更新されました。スゴっ！



さて、このページで登場した基本的な用語の説明をしておきましょう。


まず元データに関して
ピボットテーブルの集計元になるワークシートのデータ（ここでは受注データ）のことを“データソース”と言います。
んで、そのデータソースの列の“受注日”、“得意先名”、“商品名”、“数量”、“金額”等の項目名のことを“フィールド”
んで、このページには登場しませんでしたが、この“フィールド”に入力されている“アリス亭”だとか“小料理ひろ”とか、商品名の“だしこんぶ”だとかのデータを“アイテム”と言います。

次にピボットテーブルの方ですが、
出来上がったピボットテーブルの行方向に表示されている“フィールド”を“行ラベル”、列方向に表示されている“フィールド”を“列ラベル”と言います。
ちなみに、“行ラベル”と“列ラベル”に“フィールド”を設定した集計表を“クロス集計”と言います。

次にピボットテーブル集計対象の“Σ値”ですが、これは普通“データソース”（元データ）の数値データ（ここでは、“数量”や“金額”）の合計値や平均値などが“Σ値”になります。
また、データソースの数値データ以外でも例えばデータの個数も“Σ値”になります。
なので、各得意先毎の商品の件数（データの個数）などデータの件数も集計できます。

データソースの“数量”や“金額”などの数値データを“Σ値”に設定すると“合計値”として集計され、数値データ以外のフィールド“商品名”などを“Σ値”に設定すると“データの個数”として集計されます。（これは、後から変更が可能です）

と、、、言うことで、ピボットテーブルでいろいろなデータ集計を実践していってください。

次回は、データソースのデータが増えていった時にピボットテーブルを更新する為にデータソースの範囲を変更する方法を説明します。

関連ページ
元データ（データソース）の作り方