Excel。指定した順位までの構成比を算出したいというリクエスト
【Rank.EQ関数と構成比】
先日、指定した順位までの売上の構成比を求める表を作成しないといけなくて大変だったということを聞きまして、最終的には、一日業務で大変だったとのこと。で、聞いた感じですと、こんなのみたい。
確かに大変ですよね。テクニックとしては、順位と累計とさらに構成比を駆使することになりますからね。隠し味は、累計のところでしょうかね？
ということで、今回は、これを作っていくことにしましょう。
ここまでの表に関して補足しておきましょう。C10は合計を算出しております。
F3:F9までは数値に表示形式のユーザー定義を使って、○位までという表示にしております。
セルの書式設定ダイアログボックスでも確認をしておきましょう。
お馴染みですが、セルの書式設定ダイアログボックスはCtrl + 1で表示することが出来ますよね。
G/標準でもOKですが数値なので、0（ゼロ）に”位まで”を連結しております。
最初にD列の順位を算出しましょう。
RANK.EQ関数を使っていきます。（Excel2007まではRANK関数です）
D3をクリックして、RANK.EQ関数のダイアログボックスを表示しましょう。
数値にはC3ですね。
参照には、$C$3:$C$9
C3:C9まで範囲選択をしたあとにF4キーを押すと、絶対参照が設定差ますよね。
順序には、0（ゼロ）または省略が可能。省略ができますが、省略しないようにした方がいいですね。
このRANK.EQ関数がわかりにくいのは、数値と参照ですね。数値は、ランキングを知りたいセル番地になります。参照は、全体の範囲です。
それでは、OKボタンをクリックして、オートフィルで代々木まで順位を算出しましょう。
これで、順位は出来ました。今度は、この順を基にして、順位の累計を算出していきます。
累計の算出方法は、SUM関数で簡単に算出することが出来ますが、順位順に並んでないので、そうもいきませんね。では並び替えましょうと言いたいところですが、並び替えのできない資料というのは、現場ではよくあることですので、並び替えをしないで算出していくことにします。
では、順位毎の累計を算出するのには、SUMIF関数を使っていきます。手動という訳にはいきませんからね。
G3をクリックして、SUMIF関数のダイアログボックスを表示しましょう。
範囲には、$D$3:$D$9
この範囲は、次に入力する検索条件のデータが入っている列になりますので、今回は、D列。$D$3:$D$9。オートフィルでこの数式をコピーしますから、絶対参照の設定を忘れないようにしましょう。
検索条件には、”<=”&と入力した後にF3を入力します。
"<="&F3
これは、F3に1と入力されていますね。ですから、1以下という意味になりますから、
例えば、F5だと3なので3以下だったらという条件になります。ここがポイントで、比較演算子をこのように使うアイディアがあります。
最後に、合計範囲は、
$C$3:$C$9
絶対参照を忘れないようにしましょう。
あとは、OKボタンをクリックすれば算出されますので、オートフィルを使って数式をコピーしましょう。
G9の7位までの数値とC10の合計数値が合致していますよね。このように飛び飛びのセルを使った累計の算出方法もあるんですよ。
ここまでくれば、あとは、G列を使って、構成比を算出しましょう。
○○比ですから、割り算。除算ですね。
H3には、
=G3/$G$9
という数式が入りますよね。あとは、オートフィルハンドルを使って連続コピーして完成ですね。
このように、一つの表に様々なテクニックを入れて作っていくのは大変ですが、少しずつスキルアップして、日ごろ使っている表も改善していきましょう。
