⚠注意⚠最終的な形はピボットテーブルではなくなる。
本日、急ぎピボットの小計から構成比を出したくなった(出したくなったというか出す必要があったというか)。
これまでは小計不要の集計が多かった為、そういや小計に対する構成比出す方法ってあったっけ？とカチカチいじくるも探せず。
結果Googleパイセン経由で検索するも…
なんだって！？無いだって！？
そう、無いのである。
なんで無いのか、それは当然ながら我輩の関知する域ではない。
(なあ、なぜ小計に対する構成比を出せるようにしないんだマイクロソフトよ。結構需要あるはずだぞ、早急に実装せよ)
ただ1つ確かなこと。
無いもんは無い。それ以上でもそれ以下でも無い。
→とか思ったら公式サイトで職場のExcel2007には無い計算の種類がいくつも出てきた。対象バージョンは2013以降。
もしやと思い、職場の普段使用してない方のWin10、Office2016が入っているPCへデータを送り試してみると…
親行集計に対する比率ってので普通に出せるじゃないかYO！
これで、小計非表示にすれば…
普通に出来る。実装されてる。
古いもんは使うもんじゃねえなあああ…っ！
というわけでOffice2013以降の諸君は標準機能として備わってるので、普通に出してくれ。
以下は、未だにOffice2010以前のバージョンを使っている諸君へ送る。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
ちなみに2010以前でも、仮に集計項目が少なかったりすれば、
これとか、
これのように、
コンテキストメニューからデータ集計方法→その他のオプション→計算の種類で、行方向なり列方向なりの比率を求めればいいではある。
だが現実で扱うデータ量は、
往々にして少なくはない。
つまり比率を求める為に積み重ねないようにと、行完結、列完結させようとすると、横にも縦にも広がってしまうのである。
というわけで、見やすく集計する為には積み重ねる→その状態で比率を求めたい、のだが、2010以前のバージョンでは標準でその機能が備わっていないため、
無理やりにでも小計に対する比率を求めるには
というのが今回のテーマである。
なので先に注意書きしているように、最終的にはピボットテーブルの形ではなくなる。
その点、問題無し、許容可能な諸君だけ先へ進んでくれ。
※それが嫌なら新しいバージョンのOfficeを使え。
今回は見やすくするため、少ないデータを例に動かす。
↑これ。
では進めていこう。
まず、デザイン→レポートのレイアウトで、
このようにアウトライン形式にする。
次にピボットテーブル範囲をコピーし、
→適当なシートを作って「値のみ」でぺ*1。
※なんとなく、範囲をテーブルにする。
小計比率を出す為の準備として、B列(県名)の空白セルを該当の県名で埋めたいので、
空白セルのみが表示されるようフィルタをかける。
※別にフィルタをかけんでも、範囲選択後空白セルを条件に選択セルを絞れば同じことではある。
そして空白セル範囲を選択し、
一つ上を参照するようにB5に=B4の計算式を突っ込んだら、
Ctrl+Enterで､
選択領域*2に同じ数式を入れる。
※ちなみにこれは、Office2010以降ならピボット上でレポートのレイアウト→アイテムのラベルをすべて繰り返すでできることではある。
そんでもってフィルタを解除すると、
求めていた通りに県名が入力されていることがわかる。
同じようにC列(処理)も埋めていくが、C列の空白行は小計値なので、
適当に小計と入力しておく、そして見やすいように太字にした(これは好みの問題)。
そして再度フィルタを外し、
うまい事空白行が消えたことが分かる。
これで準備は整った。
早速、小計に対する構成比の計算である。
E列(まずはE4)に、
=D4/(SUMIF(B:B,B4,D:D)/2)と入力。
解説：たとえばA県のLL・MC・NCそれぞれの構成比を出したい場合。
小計の構成比を出すためには小計で割る必要があるが、小計はそれぞれ位置がばらばらで指定することは出来ない。
なので指定するのではなく、SUMIFで小計を求める。すなわち県名がA県と一致する場合だけ、A県と同じ行にあたるD列の値を合計するのだ(マジ何いってるかわからん文章だが試してみると分かるはずである)。
が、SUMIF条件に小計値そのものが含まれてしまう為(小計値行にもA県と入力されている為)、そのままSUMIFだけだと2倍になってしまう。なので2で割っている(割るまでを必ず()で囲う事)。
んで、Ctrl+Enterし(テーブルの場合はEnterを押すだけでテーブル範囲が勝手に広がり、式も自動で入力される)、
こうなる。
このままだとなんだかよくわからんので、
表示形式をパーセンテージにし、
小計行の100％は要らんので(要る人はここは飛ばしてくれ)、
小計でフィルタかけて、消す。
最後にフィルタを外してみてみると…
完成だ。
ちなみにテーブル範囲だと、
こんな感じで#この行とかいうハッシュタグみたいな計算式になったりするが、あんまり気にしないで同様に進めるといい。※キャプチャ上では見やすくするためになじみのある形式に修正している。
また、この画像のように、小計行を下に持っていっても同様の流れで比率を求める事ができる。
というか小計が頭にあるって意味わからんしな。我輩は末尾派だ。
ただし末尾小計にした場合は、=D4/(SUMIF(B:B,B4,D:D)/2)のように2で割らずに、=D4/SUMIF(B:B,B4,D:D)だけにする事。
見れば分かるだろうが、小計行の項目名がA県 集計という風に、条件から外れる為だ。意味わかるだろうか？わからんければ触るといい。正直文面では自分でもよくわからん。触ったほうが手っ取り早く理解できるのだ。
以上。
急ぎ小計の構成比の出し方を探しているOffice2010以前バージョンを使用中の諸君がいれば、荒業にはなるが簡単なので、このやり方で凌いでみてはいかがだろうか。
