【Excel】構成比求めるのに「絶対参照」→「コピー」は時代遅れ。
こんにちは、HARUです！
実務では、販売数量や売上金額の構成比、支店別、担当者別、費目別の寄与率などを求めるときに、それぞれの値を全体の数値で割るという作業をよくやりますよね。
最もオーソドックスな、分母の参照形式を変更して、他の範囲へ複製する手順の他にも、実は意外と知られていない便利な機能があります。
そこでこの記事では、値の構成比を素早く表示するいくつかのテクニックをご紹介します。
すべての手段の概要を理解しておけば、シーンに応じて有効に使い分けられます。ぜひ最後までご覧ください！
構成比の求め方
今回のテーマにおいては、大きく４つの方法を解説します。
サンプルは支店別の売上金額・利益金額と、それぞれの合計をまとめた表です。各値が合計に占める割合を求めていきましょう。
※構成比の範囲には、あらかじめパーセンテージの表示形式を設定しておきます。
①絶対参照・複合参照
１つ目は、先頭の値を合計で割り、下へコピーする方法です。
ただこれだとセル参照がズレてしまうので、合計だけ絶対参照で固定してからコピーするといった手順が一般的ですよね。
もし構成比を求める範囲が１つだけならこれで完了ですが、利益金額にも同じ数式を適用したい場合、絶対参照のままコピーすると、各支店の利益金額を売上金額の合計で割る構図になってしまいます。
今回は数式を下へコピーしたとしてもすべての値を合計で割り、かつ右へコピーしたときには、対象となる合計値をスライドさせたい。要は合計行を固定して、その中で列単位(横方向)にはスライドして欲しいのです。
こんなときは、行番号だけを固定する複合参照にします。
これにより、売上構成の数式をコピーするだけで、各支店の利益構成も正しく求められます。
②クイック分析オプション
セル参照形式の変更はExcel仕事において必要不可欠な概念ですが、絶対参照や複合参照を駆使する作業って結構わずらわしいですよね。
こんなときは構成比を求めたいデータを選択して、Ctrlキーで「クイック分析オプション」を開きましょう。
そして、「合計」グループから、オレンジの表にパーセンテージのマークがついたアイコンを選びます。
これで、各値の構成比が一瞬で表示されます。
実際にはそれぞれの値を選択範囲の合計で割った数式が、隣接列に挿入されます。
利益金額構成も同様に求めていきます。
③スピル
とはいえ「クイック分析オプション」も、一定の工数がかかります。
最もスピーディなのは、各支店の値を配列として選択し、これを合計値で割る方法です。
これによって数式を入力した一番上のセルを先頭に、それぞれの構成比がスピルで生成されます。
↓「スピル」のしくみが気になる方は、以下の記事と動画をチェック！↓
④集計に対する比率
値の構成比はピボットテーブル上でも表示できます。
たとえば費目別の予算使用実績を年度ごとにまとめたピボットテーブルにおいて、値フィールドに既に配置されている金額を、もう一度挿入します。
金額の合計2が反映されますので、見出しを「構成比」に変えます。
構成比の列で右クリックし、「計算の種類」を「列集計に対する比率」に変更します。
これにより、各費目の実額の横にそれぞれの寄与率が記されます。
かけ算九九表
冒頭に触れた絶対参照・複合参照に関連して、セルの複合参照を学ぶときによくサンプルにされる「かけ算九九表」を作っていきます。
素早く埋めるには、大きく3つの手順があります。
①数式入力→対象範囲へコピー
先頭のセルで「列固定の複合参照」と「行固定の複合参照」をかけ合わせます。
対象範囲を選択します。
下方向（[Ctrl]+[D]）、右方向（[Ctrl]+[R]）へコピーします。
②範囲選択→数式の一括入力
先に対象の空白範囲を選択します。
「列固定の複合参照」と「行固定の複合参照」をかけます。
[Ctrl]+[Enter]で選択範囲内に数式を一括入力します。
③スピル
縦の見出し（配列）と横の見出し（配列）をかけます。
結果がスピルで生成されます。
いずれの場合も、必要に応じて値貼り付けしておきましょう。
Microsoft365の最新バージョンであれば、[Ctrl]+[C]でコピーした後、[Ctrl]+[Shift]+[V]で値貼り付けが可能になっています。
いかがでしたか？
今回は、値の構成比や寄与率を素早く表示する方法をご紹介しました。実務で使えそうなテクニックがあれば、積極的に実践していきましょう！
↓↓記事の内容を動画で解説しています↓↓
※本記事の委細が動画収録当時のバージョン・解説内容と異なる場合があります。
↓↓Excel操作をとにかく高速化したい方へ↓↓
↓↓実務直結の関数活用術を網羅的に学びたい方へ↓↓
この記事が気に入ったらサポートをしてみませんか？