今日はExcelのキホン中のキホン、計算式のお話です。
Excelの計算式は「=A1*A2」のようなセルの参照で計算できます。
これがExcelの計算式のだいご味であり、ExcelがExcelであるための機能でもあります。
そして、よく言われるのは、計算式内の値は、実数ではなくセル参照でしたほうがいいということです。
それはどんなケースがあるからそう言えるのか、それを解説します。
ExcelをExcelらしく使いましょう。
Excelの数式はコピーできる
Excelの数式はコピーができます。
次のような表で各行の金額を求めるプロセスを考えます。
もしも、Excelが計算式のコピーができなかったら。
セルD2に「=1736*35」と入力するか、「=B2*C2」と入力するかになるでしょう。
この場合、結果的にはどちらでもいいのですが、この時点だけでも、セル参照したことで大きなメリットが発生しています。
それは、「数値や値の打ち間違いが起きない」ということです。
一文字一文字入力するのであれば、よくあるのは、1736を1376と読み間違えたり、打ち間違えたりすることです。
これは後から計算ミスを見直そうとしても一目でわからないので、直しにくいものです。
セル参照であれば、セルをクリックして行えるので、そのままセルの値を使うことができ、打ち間違いが発生することはありません。
さらに、もう一つのメリットは、セルの値が万が一間違っていた時に、「元の値を打ち直すだけで、計算をし直してくれる再計算をしてくれる」からです。
これも大きなメリットで、４月分として計算書を作っていたら、５月になったら４月のデータをすべて消して、５月のデータを入れるだけで５月の計算書にすることができるということもできます。非常にExcelらしい使い方です。
そして、セル参照で作られた計算式は、値ではなく位置関係を記憶しているということです。
セルD2に「=B2*C2」と入力するということは、セルD2に、「２つ左隣のセルの値と、左隣のセルの値をかけ合わせた計算式」という位置関係で作っています。
ここ、ものすごく大事、Excelとして。
セル参照で計算式を作れば、「数式の位置関係をコピーする」ことができるのです。
だからセルD2に2行目を参照した数式を作成し、それを１つ下にコピーすれば、3行目を参照し、「=B3*C3」という計算式になるのです。
これは10万行下までコピーしても、それぞれ「２つ左隣のセルの値と、左隣のセルの値をかけ合わせた計算式」の法則を持った10万個の計算式になります。
絶対参照
次のような消費税を含んだ計算書を作成する場合は、セルD2に「=B2*C2」に「100%」とセルB12の値を足したものをかける必要があるので、「=B2*C2*(100%+B12)」という計算式を入力します。
そして、下にコピーすると。
一見、ちゃんとしているように見えるのですが、セルD2の時点で、2つ左の値と左のセルの値と、100%と２つ左で10個下のセルの値を足したもののかけ算という計算式になります。
それを下１つにコピーした場合、2つ左の値と左のセルの値は移動していいのですが、セルB12にあたる、２つ左で10個下のセルも移動してしまします。これではいけません。
それならば、108%と計算式に入れれば解決するのでは？
その通り、それで解決します。セルD2の計算式を下にコピーすることができます。
しかし、消費税率は今年、10%になるじゃないですか、予定ですが。
となると、また計算式を変更するのも面倒じゃないですか。
そこで、消費税額もやっぱりセル参照したくなってくるのです。
実はExcelが普及したのは5%から8%になりたてのころなので、多くの人にとっては、Excelではじめての消費税変更の経験になるかもしれないです。
5%の時は、やっぱり105%と入力していたのですね。
なので、その時は消費税がセル参照されてれば楽だったなと思いました。
10%になってから次の消費税変更の動向がどうなるかは読めないので、何十年も10%のままなら110%と計算式に入れてもいいでしょうけど、そこから上がるかもしれないし、下がる可能性も０ではないはずです。それがいつ行われるかは世間の動向次第なので、セル参照にしておいた方がいいのかなと思います。
さて、そこで、セル参照にした結果、さきほどはセル参照が下にどんどんずれてしましました。
それに対応するには、セル参照が、B12ならば、$B$12というように$をつけます。
$を2個つけるのが完全な絶対参照で、$B12という付け方もB$2という付け方もあるのですが、その説明は別の機会として、今は$B$12の完全な絶対参照で使います。
これを行うことで、コピーしても絶対にセルB12を参照します。
つまり、「=B2*C2*(100%+B12)」は失敗なので、「=B2*C2*(100%+$B$12)」とします。このことによって、セルD2の時点で、2つ左の値と左のセルの値と、100%とセルB12の値を足したもののかけ算という計算式という日本語になります。
この法則なら、コピーしてもセルB12の参照は変わらずにそれぞれの行で計算します。
セルB12の値を10%に変えてみます。
商品すべてのセルの計算値が1セルの値を変更したことで変更されたのです。
Excelの計算式はできるだけコピーしよう
Excelの作り方のキホンなのですが、これは私の考え方なのですが、Excelの計算書を作成する時は、同じ種類のものを計算する時は、計算式は１つだけ作って、それをコピーして使うことを考えましょうということをよく言っております。
Excelの計算書はどんな作り方であれ、作成されただけで、使いまわすことによって効率化ができます。
しかし、その計算書を作る工程もできるだけ時短、効率化すべきと考えます。時間がかからない手段があるならそうしていきたいですよね。
そのために、Excelは計算式をコピーできるようになっています。
もしも、セル参照しなければ=1736*35という計算式になりますが、この計算式は、どこまでコピーしても=1736*35のままです。つまり、コピーできません。
なので、コピーするためにもセル参照で計算式を作成しましょう。
まとめ
セル参照する意味が伝わったでしょうか？
まとめると、なるべくセル参照で計算式を作成した方がいい理由は
- 数値の打ち間違いをなくす
- 元の値を書き換えるだけですべての計算を自動でしなおせる
- コピーの時、数値の位置関係でコピーできる
です。
セル参照にはExcelのスゴイところ、数式はコピーできる、値を入れなおせばすべて計算しなおす、の2つを大活躍させるテクニックが詰まっているのです。
コメント