ボロボロ皇帝のボロ切れと塊

ボロボロ皇帝が悪態、嫌み、好み、何でも己の視点だけで自由に吐く「偏」な場所。たまにExcel、家電、音楽等についても好きに吐いてる。

ボロボロ皇帝が悪態、嫌み、好み、何でも己の視点だけで自由に吐く「偏」な場所。
Excel、家電、音楽、ゲームイラスト小説映画、性癖について等、何でも好きに吐いてる。

どうでも良いかもしれんが背景色変わっていくの見てくれ。
更にどうでも良いかもしれんがアイコンが息してるの見てくれ。


我輩は何にも制約されない身、
「耐えぬ」事を恐れないと決めたのだ!
我輩は好きなときに悪態をつく!
妬み僻み嫉みを背負い、
マイナーだろうが邪道だろうが
我輩は我輩の道を行く!
好きなものは懸命で不器用な諸君,
嫌いなものは器用で完璧を気取った奴らだ。
         

【Excel2010以前】ピボットの小計に対する構成比をとりあえず出すまで

⚠注意⚠最終的な形はピボットテーブルではなくなる。

 

 

本日、急ぎピボットの小計から構成比を出したくなった(出したくなったというか出す必要があったというか)。

 

これまでは小計不要の集計が多かった為、そういや小計に対する構成比出す方法ってあったっけ?とカチカチいじくるも探せず

結果Googleパイセン経由で検索するも…

なんだって!?無いだって!?

そう、無いのである

なんで無いのか、それは当然ながら我輩の関知する域ではない

(なあ、なぜ小計に対する構成比を出せるようにしないんだマイクロソフトよ。結構需要あるはずだぞ、早急に実装せよ)

 

ただ1つ確かなこと。

無いもんは無いそれ以上でもそれ以下でも無い。

→とか思ったら公式サイト職場のExcel2007には無い計算の種類がいくつも出てきた。対象バージョンは2013以降

もしやと思い、職場の普段使用してない方のWin10、Office2016が入っているPCへデータを送り試してみると…

f:id:wornoutemperor:20211103163232p:plain

親行集計に対する比率ってので普通に出せるじゃないかYO!

これで、小計非表示にすれば…

f:id:wornoutemperor:20211103163355p:plain

普通に出来る。実装されてる。

古いもんは使うもんじゃねえなあああ…っ!

 

というわけでOffice2013以降の諸君は標準機能として備わってるので、普通に出してくれ

 

以下は、未だにOffice2010以前のバージョンを使っている諸君へ送る

 

================

 

ちなみに2010以前でも、仮に集計項目が少なかったりすれば、

f:id:wornoutemperor:20211103021454j:plain

これとか、

 

f:id:wornoutemperor:20211103021536j:plain
これのように、

コンテキストメニューからデータ集計方法→その他のオプション→計算の種類で、行方向なり列方向なりの比率を求めればいいではある。

 

 

だが現実で扱うデータ量は、

f:id:wornoutemperor:20211103022355j:plain

往々にして少なくはない

つまり比率を求める為積み重ねないようにと、行完結、列完結させようとすると横にも縦にも広がってしまうのである。

というわけで、見やすく集計する為には積み重ねる→その状態で比率を求めたい、のだが、2010以前のバージョンでは標準でその機能が備わっていないため

無理やりにでも小計に対する比率を求めるには

というのが今回のテーマである。

なので先に注意書きしているように、最終的にはピボットテーブルの形ではなくなる

その点、問題無し、許容可能な諸君だけ先へ進んでくれ

※それが嫌なら新しいバージョンのOfficeを使え。

 


今回は見やすくするため、少ないデータを例に動かす

f:id:wornoutemperor:20211103022005j:plain
↑これ。

 


では進めていこう。

 

 

まず、デザイン→レポートのレイアウトで、

 

f:id:wornoutemperor:20211103022501j:plain

このようにアウトライン形式にする

 

次にピボットテーブル範囲をコピーし、

f:id:wornoutemperor:20211103022555j:plain

f:id:wornoutemperor:20211103022606j:plain

適当なシートを作って「値のみ」で*1

 

 

f:id:wornoutemperor:20211103022708j:plain
※なんとなく、範囲をテーブルにする。

 

 

小計比率を出す為の準備として、B列(県名)の空白セルを該当の県名で埋めたいので、

f:id:wornoutemperor:20211103022742j:plain

空白セルのみが表示されるようフィルタをかける

 

そして空白セル範囲を選択し、

f:id:wornoutemperor:20211103022859j:plain
一つ上を参照するようにB5に=B4の計算式を突っ込んだら、


Ctrl+Enterで、

 

f:id:wornoutemperor:20211103023011j:plain


選択領域*2に同じ数式を入れる

※ちなみにこれは、Office2010以降ならピボット上でレポートのレイアウトアイテムのラベルをすべて繰り返すでできることではある。

 

 

そんでもってフィルタを解除すると

 

f:id:wornoutemperor:20211103023115j:plain


求めていた通りに県名が入力されていることがわかる。

 

 

同じようにC列(処理)も埋めていくが、C列の空白行は小計値なので、

 

f:id:wornoutemperor:20211103023258j:plain


適当に小計と入力しておく、そして見やすいように太字にした(これは好みの問題)。

 

そして再度フィルタを外し、

 

f:id:wornoutemperor:20211103023343j:plain

うまい事空白行が消えたことが分かる。

 

これで準備は整った。

早速、小計に対する構成比の計算である。

 

E列(まずはE4)に、

f:id:wornoutemperor:20211103023516j:plain
=D4/(SUMIF(B:B,B4,D:D)/2)と入力。
解説:たとえばA県のLL・MC・NCそれぞれの構成比を出したい場合。
小計の構成比を出すためには小計で割る必要があるが、小計はそれぞれ位置がばらばらで指定することは出来ない。
なので指定するのではなく、SUMIFで小計を求める。すなわち県名がA県と一致する場合だけ、A県と同じ行にあたるD列の値を合計するのだ(マジ何いってるかわからん文章だが試してみると分かるはずである)。
が、SUMIF条件に小計値そのものが含まれてしまう為(小計値行にもA県と入力されている為)、そのままSUMIFだけだと2倍になってしまう。なので2で割っている(割るまでを必ず()で囲う事)。

 

んで、Ctrl+Enterし(テーブルの場合はEnterを押すだけでテーブル範囲が勝手に広がり、式も自動で入力される)、

 

f:id:wornoutemperor:20211103023734j:plain


こうなる。

 

 

このままだとなんだかよくわからんので、

f:id:wornoutemperor:20211103023803j:plain

表示形式をパーセンテージにし、

 

小計行の100%は要らんので(要る人はここは飛ばしてくれ)、

f:id:wornoutemperor:20211103023835j:plain
小計でフィルタかけて、消す

 

 

最後にフィルタを外してみてみると…

 

f:id:wornoutemperor:20211103023926j:plain

完成だ。

 

 

ちなみにテーブル範囲だと、

f:id:wornoutemperor:20211103023947j:plain

こんな感じで#この行とかいうハッシュタグみたいな計算式になったりするが、あんまり気にしないで同様に進めるといい。※キャプチャ上では見やすくするためになじみのある形式に修正している。
また、この画像のように、小計行を下に持っていっても同様の流れで比率を求める事ができる。
というか小計が頭にあるって意味わからんしな。我輩は末尾派だ。
ただし末尾小計にした場合は、=D4/(SUMIF(B:B,B4,D:D)/2)のように2で割らずに、=D4/SUMIF(B:B,B4,D:D)だけにする事。

見れば分かるだろうが、小計行の項目名A県 集計という風に、条件から外れる為だ。意味わかるだろうか?わからんければ触るといい。正直文面では自分でもよくわからん。触ったほうが手っ取り早く理解できるのだ。

 

 

以上。


急ぎ小計の構成比の出し方を探しているOffice2010以前バージョンを使用中の諸君がいれば、荒業にはなるが簡単なので、このやり方で凌いでみてはいかがだろうか。

 

 

*1:ペーストのぺ。

*2:フィルタをかけた状態で選択すると、勝手に可視セル(見えてる部分)だけが選択されたことになる。