Excel。ピボットテーブルをつかえば、手早く順位も求めることができます。【Ranking】

Excel。ピボットテーブルをつかえば、手早く順位も求めることができます。

＜ピボットテーブル＞

大きな表などを集計するには、ピボットテーブルをつかうことで、手早く集計することができます。

そのピボットテーブルで、順位も合わせて、手早く求めることができるようになっています。

元になるデータです。

では、表内をクリックして、挿入タブのピボットテーブルをクリックします。

テーブルまたは範囲からのピボットテーブルダイアログボックスが表示されます。

ダイアログボックスは、そのままOKボタンをクリックします。

新しいシートが追加され、右側には、ピボットテーブルのフィールド作業ウィンドウが表示されます。

行のレイアウトボックスには、「商品名」を設定します。

値のレイアウトボックスには、「売上金額」を2つ設定します。

「合計/売上金額2」のC4をクリックします。

ピボットテーブル分析タブのフィールドの設定をクリックします。

値フィールドダイアログボックスが表示されます。

名前の設定　を　「順位」　に変更します。

計算の種類タブ　に変更します。

計算の種類を「降順での順位」にします。

あとは、OKボタンをクリックします。

これで、手早く集計と順位を求めることができました。

ピボットテーブルは集計以外でも様々な計算をおこなうことができます。

Excel。ピボットテーブルで順位を表示するにはどうしたらいいの。【rank】

Excel。ピボットテーブルで順位を表示するにはどうしたらいいの。

＜ピボットテーブル＞

大量のデータから、手早く集計することができる「ピボットテーブル」。

集計するだけではなく、同時に順位も算出することができると、さらに手早く資料をつくることもできます。

次のデータを用意しました。

表内をクリックしておいて、挿入タブの「ピボットテーブル」をクリックします。

テーブルまたは範囲からのピボットテーブルダイアログボックスが表示されますので、項目を確認してOKボタンをクリックします。

新しいシートが追加されます。

ピボットテーブルのフィールド作業ウィンドウの下にある、レイアウトボックスにフィールドを設定します。

行ボックスには、「商品名」、

値ボックスには、「合計」を2つ設定します。

ピボットテーブルのレイアウトを確認します。

合計フィールドの2つめの「合計/売上高2」を順位に変更していきます。

C4をクリックします。

ピボットテーブル分析タブの「フィールドの設定」をクリックします。

値フィールドの設定ダイアログボックスが表示されます。

名前の指定を「順位」に変更します。

「計算の種類」タブにして、計算の種類を「降順での順位」に設定します。

あとはOKボタンをクリックします。

このように、集計だけでなく、手早く順位も算出することができます。

Excel。ピボットテーブルの日付フィールドの表示形式が変更できないので困っています【pivot table】

Excel。ピボットテーブルの日付フィールドの表示形式が変更できないので困っています

＜ピボットテーブル＞

集計処理で便利なピボットテーブルですが、日付に関して困ることがあります。

次のデータを用意しました。

日付は、お馴染みの「yyyy/m/d」という表示形式です。

では、ピボットテーブルをつかって、日付フィールドを行のレイアウトボックスに設定します。

売上高フィールドは、値のレイアウトボックスに設定しました。

レイアウトボックスは、このようになりました。

日付フィールドを設定したら、自動的に月フィールドが生成されました。

ピボットテーブルは、月でグループ化された状態で、表示されています。

確かに、月レベルでグループ化してくれるのはありがたいのですが、日付ごとの集計で確認したいわけですね。

そこで、レイアウトボックスから自動的に生成された月フィールドを外します。

これで、日付ごとの集計結果がわかるようになりました。

ただ、気になるのは、日付の表示形式です。

「月日」の表示形式になっていますが、元データと同じ「yyyy/m/d」に変更しようとすると、出来ないわけです。

A4をクリックして、ピボットテーブル分析タブの「フィールドの設定」をクリックします。

フィールドの設定ダイアログボックスが表示されますので、表示形式のボタンをクリックします。

セルの書式設定ダイアログボックスが表示されます。

分類を日付にして、「yyyy/m/d」を選択しても、変更できません。

この原因は、勝手に生成されたグループ化された「月」フィールドなんです。

では、解決方法です。

ピボットテーブル分析タブの「グループ解除」をクリックします。

これで、元データと同じ、表示形式にすることができました。

さて、Microsoft365のExcelには、「Insider版」という、最新機能をお試しでつかえるものがあります。

この問題を、Insider版では、解決させています。

Insider版でも、自動生成されてグループ化するのですが、「日(日付)」フィールドと「月(日付)」フィールドの2つが生成されてグループ化されます。

生成された2つのフィールドのチェックマークを外すだけで、元データと同じ表示形式にすることができます。

今後、MicrosoftのExcel365にも反映されていくものと思いますが、現状では、グループ解除をする方法がいいようです。

Excel。ピボットテーブルで累計を求めるにはどうしたらいいの。【Cumulative】

Excel。ピボットテーブルで累計を求めるにはどうしたらいいの。

＜ピボットテーブル＞

集計に長けている「ピボットテーブル」。

オートフィルター機能もついているので、集計値など、抽出に連動して把握できるなど、とても便利な機能の一つです。

そこで、集計だけでなく、「累計」を算出したい場合には、どのようにしたらいいのでしょうか。

次の表は、ピボットテーブルで、次のようなレイアウトをつくっています。

C3の見出しのままだと、わかりにくいので、C列に累計値を算出したいので、C3を「累計」と変更しておきます。

C列の集計計方法を累計に変更していきます。

見出しではなく、C4などのデータをクリックします。

ピボットテーブル分析タブの「フィールドの設定」をクリックします。

値フィールドの設定ダイアログボックスが表示されますので、タブを「計算の種類」にして、計算の種類の一覧から「累計」を選択したら、OKボタンをクリックします。

これで、累計を算出することができました。

計算の種類には様々なものが用意されていますので、つかってみると、作業効率が改善できるかもしれませんね。

Excel。ピボットテーブルで構成比を算出するにはどうしたらいいの【composition ratio】

Excel。ピボットテーブルで構成比を算出するにはどうしたらいいの

＜ピボットテーブル＞

ピボットテーブルをつかうと、手早く集計することができます。

集計するだけでなく、構成比などの比較も算出できるように、色々用意されています。

データからピボットテーブルを挿入しています。

B列・C列共に、在庫数の合算値を集計しています。

C列に構成比を表示させていきます。

また、B3の見出し名を在庫合計

C3の見出し名を構成比と変更も合わせて処理します。

ピボットテーブルは、見出し名では、作業できないものが結構ありますので、データのセルである、C4をクリックします。

ピボットテーブル分析タブの「フィールドの設定」をクリックします。

値フィールドの設定ダイアログボックスが表示されます。

集計方法タブから計算の種類タブをクリックして、計算の種類を「列集計に対する比率」を選択して、OKボタンをクリックします。

これで、在庫数の構成比を、手早く算出することができました。

Excel。ピボットテーブルをつかえば、データの件数と構成比は、手早く算出できます。【composition ratio】

Excel。ピボットテーブルをつかえば、データの件数と構成比は、手早く算出できます。

＜ピボットテーブルで構成比＞

集計機能はExcelに色々搭載されています。

集計作業も、集計したあとに、さらに数式をつくって、構成比を算出しているようなら、単純な数式を作成するだけでも、面倒になってきますし、思っているよりも時間がかかることがあります。

そこで、試しにピボットテーブルをつかったら、どうなるのかを確認してみるといいかもしれません。

例えば、次のデータから、都道府県の件数と、それぞれの構成比を算出する場合で、作業を確認してみます。

表内をアクティブにします。

A1をクリックして、挿入タブのピボットテーブルをクリックします。

テーブルまたは範囲からのピボットテーブルダイアログボックスが表示されます。

範囲を確認したらOKボタンをクリックします。

ピボットテーブル用の新しいシートが挿入されます。

ピボットテーブルのフィールド作業ウインドウで、行ボックスに、都道府県フィールドを1つ。

値ボックスに都道府県フィールドを2つ設定します。

構成比をつくっていきます。

構成比を表示したいフィールド。

今回は2つ目の都道府県フィールドの「個数/都道府県2」のデータをクリックします。

ピボットテーブル分析タブの「フィールドの設定」をクリックします。

値フィールドの設定ダイアログボックスが表示されます。

名前の指定を「構成比」にします。

この名前の指定は、すでにフィールドで使っている場合、重複することができません。

計算の種類タブから、計算の種類を「列集計に対する比率」を選択してOKボタンをクリックします。

なお、パーセント表示で小数点第2位までを表示されるように設定されていますので、小数点の表示桁数を変更したい場合は、表示形式ボタンをクリックして、設定を変更してからOKボタンをクリックします。

これで、都道府県の件数と構成比を算出することができました。

あとは、並べ替えなどすれば、さらにいいですね。

データ量が増えるとか、データが追加されることが多い場合は、集計機能と数式をつかった算出でもいいのですが、ピボットテーブルをつかったら、どうなるのかなと考えてみるのもいいかもしれませんね。

Excel。5件ごとの小計を算出するなら、ピボットテーブルをつかうのはどうでしょう【subtotal】

Excel。5件ごとの小計を算出するなら、ピボットテーブルをつかうのはどうでしょう

＜ピボットテーブル＞

5件とか10件とか、一定ごとの小計を算出したい場合、どうやって、5件を1組として判断させればいいのか、どの関数をつかったら、簡単に算出できるのかなど、アレコレ考えます。

ただ、算出したいのであれば、関数や小計機能ではなくて、ピボットテーブルをつかうと比較的簡単に算出することができます。

次のデータを元に、ピボットテーブルをつかって、5件ごとの小計を算出してきます。

ピボットテーブルを挿入しますので、表内のセルをアクティブにした状態で、挿入タブの「ピボットテーブル」をクリックします。

「テーブルまたは範囲からのピボットテーブル」ダイアログボックスが表示されますので、範囲を確認したら、OKボタンをクリックします。

ピボットテーブルを設定するための新しいシートが挿入されます。

右側に表示されてある、ピボットテーブルのフィールドリストをつかって、NOを行のボックスに、販売金額を、値のボックスに設定します。

あとは、5件ごとにまとめる作業をしていきましょう。

A4などの行ラベルのデータをクリックします。

ピボットテーブル分析タブの「グループの選択」をクリックします。

グループ化ダイアログボックスが表示されます。

5件ごとに小計を算出したいので、単位に「5」と入力をして、OKボタンをクリックします。

これで、5件ごとの小計を算出することができました。

ちょっとした小計などを手早く求めるならば、ピボットテーブルを、つかってみるのも一つの方法ですね。

Excel。ピボットテーブルで集計データが空白だったらわかりやすいようにしたい。【Cell is blank】

Excel。ピボットテーブルで集計データが空白だったらわかりやすいようにしたい。

＜ピボットテーブル＞

ピボットテーブルをつかってクロス集計などを作った時に、元のデータがなければ、当たり前ですが、空欄で表示されます。

ちょっとしたデータがあればピボットテーブルをつかうことで、比較的簡単にクロス集計のような帳票を作れますが、空欄が多かったりすると、ちょっと気になります。

空欄のままでもいいのですが、例えば空欄に「0（ゼロ）」を表示したい場合は、どのようにしたらいいのでしょうか？

ピボットテーブルではなく、通常の表ならば、範囲選択をして、空欄を「0（ゼロ）」に置換すればいいわけですが、ピボットテーブルでは、そういうわけにはいきません。

例えば、C5のサンフランシスコの2月のデータが空欄なので、「0」を入力しようとすると、次のようなメッセージが表示されてしまいます。

 

ピボットテーブルで管理させているセルには直接入力することはできません。

範囲選択して、置換しようとしても、メッセージが表示されてしまい、置換することはできません。

ピボットテーブル側で管理しているので、次の方法で対応していきます。

ピボットテーブル内のセルをクリックして、ピボットテーブルをアクティブにします。

ピボットテーブル分析タブのピボットテーブルにある「オプション」をクリックします。

ピボットテーブルオプションダイアログボックスが表示されます。

レイアウトと書式タブの書式にある、「空白セルに表示する値」に「0（ゼロ）」と設定してOKボタンをクリック。

空白セルだったところに、「0（ゼロ）」が表示されました。

このようにピボットテーブルオプションを使うことで、「0（ゼロ）」を表示することが出来るわけですが、「空白セルに表示する値」となっているので、数値だけだと思っている人もいるようでして、例えば「データなし」と設定すれば、「データなし」と表示されます。

このように、データがない空白セルに「データなし」と表示することができました。

ただ、今回のように「データなし」という文字にしてしまうと、件数が多い場合、見にくい帳票になってしまう恐れがありそうですね。

また、「0（ゼロ）」を表示することも出来ますが、データがないので「0（ゼロ）」にするというのもどうかというケースもあるかもしれませんね。

「集計結果が0」と「データがない=０」とするでは意味が異なってしまうので、こちらも注意する必要がありますね。

Excel。ピボットテーブル。独自の分類名を作りグループ化して見やすくしよう。【Grouping】

Excel。ピボットテーブル。独自の分類名を作りグループ化して見やすくしよう。

＜ピボットテーブル＞

大きなデータはそのままでは、どのような傾向があるとかわかりませんので、まずは集計してみようとすると、ピボットテーブルをつかうことが多いですね。

ただ、対象アイテムが多い場合は、それでも見にくいために、せっかくピボットテーブルで作った資料も、わかりにくい資料となってしまうので、独自の分類名を作ってグループ化すると、いいわけですね。

下記のデータを用意しました。

最初はピボットテーブルをつかって集計していきます。

表のないのセルをアクティブ（A1をクリックでOKです）にして、挿入タブのピボットテーブルをクリックします。

「テーブルまたは範囲から」をクリックします。

テーブルまたは範囲からのピボットテーブルダイアログボックスが表示されますので、範囲を確認してOKボタンをクリックします。

新しいシートが追加されます。

ピボットテーブルのフィールド作業ウィンドウをつかって、レイアウトを設定してきます。

行のボックスには、「旅行名」のフィールドを設定します。

値のボックスには、「金額」のフィールドを設定します。

これで旅行名ごとの金額合計一覧が作成できたわけです。

ただ旅行先が多すぎてわかりにくい感じもします。

そこで、例えば、サンフランシスコとニューヨークとハワイはアメリカ合衆国なので、アメリカという分類名で、まとめてみることにしましょう。

集計データの旅行名の上にマウスカーソルを移動すると、「➡」という形に変わったらクリックすると集計データ行を選択することができます。

今回は、サンフランシスコ・ニューヨーク・ハワイを選択します。

ピボットテーブル分析タブの「グループの選択」をクリックします。

グループとしてまとめることができました。

グループ1だとなんだかわからないので、「アメリカ」と入力修正します。

グループ化していないものが、それぞれで、小計を算出している状態なので、次に、スペイン・ドイツ・フランスを先程と同じように選択して、グループ化します。

ベトナムと北京をアジア。

沖縄と北海道で国内というようにグループ化する作業を繰り返します。

ご覧のように、それぞれの地域名のフィールドは用意していない表から、分類を作り分けて確認出来るようになりましたね。

アメリカとヨーロッパの4行目と8行目を選択して、再度グループ化することで、さらにまとめることができます。

このように、グループ化を上手く使うことで、わかりやすい資料を作ることもできます。