Excel。構成比シェアを求めるには、絶対参照が必須です。
絶対参照
仕事でつかえるExcel講座や、初心者が多い講座で、最初の関門になるかなぁ～というテクニックは、
絶対参照
じゃないかと。四則演算を勉強して、Σオートsumボタンを紹介するところまでは、
初心者の方でも、仕事に着いたばかりの方でも、わりかし、
トラブルもなくスキル習得出来るのですが、
今日紹介する、絶対参照は、引っかかる方がいるところなんですね。
この絶対参照を紹介するのに適しているのが、構成比。
つまり、シェア。
この構成比を教えるテクニックとしては、
まずは、割り算であること。
この値は、全体の値の中で、どのぐらい占めているのか？
ということを知りたいということなんだ。
と目的を明快にしてあげます。
そして、算出後、オートフィルを使って、連続コピーをすると…
アレアレ、うまく行きませんね。ともっていき、
次に、計算式を一つずつ確認していくと…
という流れだと、ほとんどの方が、理解していただけるようです。
では、実際に、やってみましょう。
まず、下記のような表があります。
確認しておくのは、すでに、F列と7行目に合計が算出してありますので、
算出されていない場合は、必ず算出しておく必要があります。
それでは、今回G列の構成比は、全体の合計つまり、
F列の数字をもとに、算出していくことにしましょう。
最初に、新宿店の構成比であるG3を求めることにしましょう。
今回使う数字は、新宿店の合計のF3と、全体の合計である、F7を使っていきますが、
どっちから、どっちを割るのか？ということになりますが、
ここで、よく、悩む方がいるのですが、悩むぐらいなら、
とっとと、割っちゃって、その数字を見て、おかしい場合は、逆だと判断したほうが、
早いかと思います。
今回は、構成比なので、100％を越えてはいけないわけですね。
つまり、1以上の数字が表示されれば、逆だと考えるほうが、いいわけですね。
では、新宿店の合計を全体の合計で割ることにしましょう。
=F3/F7
そうすると、0.227272727と表示されました。
数字は1を越えておりませんので、大丈夫そうですね。
つまり、構成比は、構成比を出したい数を全体の数値で割ってあげればいいわけですね。
では、この数式を合計の品川店のG6まで、オートフィルを使用して、
連続コピーをしていきましょう。すると、どうなったでしょうか？
渋谷店～品川点が、#DIV/0というエラーが表示されました。
このエラーはゼロで除算しているよという意味です。
けど、ゼロで割った覚えなんてありませんよね。
このようなエラーが表示された場合には、
必ず、数式バーを確認する癖をつけましょう。
そうすれば、エラーの原因がわかるようになりますし、Excelの力もアップしますね。
仕事で使えるスキルもアップします。
では、渋谷店の構成比をクリックして数式バーを確認してみましょう。
=F4/F8
F4は、渋谷店の合計の数値ですね。これは使ってOK。
F8はというと、この表の場外ですよね。
では、今度は、池袋店の構成比をクリックして数式バーを確認してみましょう。
=F5/F9
F5は、池袋店の合計の数値ですね。これは使ってOK。
F9はというと、あれれ、これも、この表の場外ですよね。
F8もF9も、これでいいわけがありませんよね。
本来なら、全体の合計であるF7を使って割ってくれないといけないわけなのですが…。
なぜ、このようなことが発生してしまったのかというと、
オートフィルを使って数式をコピーした時に、
実は、その計算式で使用している、セル番地との相対的な距離間を維持したまま、
コピーされたからなのです。
つまり、最初の新宿店の数式は、=F3/F7。
F3は、G3からみて、左に1個。
F7は、G3からみて、左に1個。下に4個。
この相対的な距離を保って、コピーされるわけです。
よって、渋谷店のG4の数式は、=F4/F8。
F4は、G4からみて、左に1個。
F8は、G4からみて、左に1個。下に4個。
なわけです。ですから、F8が表の場外になったわけです。
では、オートフィルを使った、連続コピーをすることは出来ないのでしょうか？
どうにか、全体の合計であるF7を固定することは出来ないのでしょうか？
どうにか、絶対にF7を参照することは出来ないのでしょうか？
そこで、登場するのが、絶対参照というテクニックなのです。
今回は、F7を絶対に参照させたいわけですね。
このF7に絶対参照を設定してあげるわけです。
設定の方法は、【F4】キーを押します。操作はこれだけ。
では、G列の数式をいったん、削除して、一から数式を作っていきましょう。
G3をクリックして、=F3/
ここまでは、同じなのですが、=F3/F7をしたあとに、すぐに【F4】キーを押すと、数式が、
=F3/$F$7
F7のFと7の前に$マークが入ってきました。これが絶対参照のマークなのです。
算出された数字は、先ほどと変わっていない、0.227272727。
では、オートフィルを使って、品川店まで連続コピーしてみましょう。
今回は、エラーが出ずに、算出されましたね。
それでは、渋谷店の数式を確認してみましょう。
=F4/$F$7
今回は、$F$7となっていて、F7を固定されていることが確認できますね。
この絶対にその範囲を固定させて、参照する方法を、
【絶対参照】
と呼んでいます。通常は相対的な距離で算出している、【相対参照】を使っています。
これで完了ですね。
なお、【F4】キーを一回以上おしてしまうと、
F7→$E$7→F$7→$F7→F7
と戻ってきますので、慌てないようにしましょう。
ちなみに、一方だけに$がついているものを、【複合参照】と呼んでおりますが、
この【複合参照】は、【絶対参照】のスキルをつけてから、
覚えるようにした方がいいと考えます。
お仕事では、なかなか、【複合参照】を使う頻度が、【絶対参照】よりも少ないので、
まずは、しっかり、基本である、【絶対参照】を身につけましょう。
