Excelでは、コピーが便利という話は以下の記事で触れました。
Excelは値だけではなく計算式をコピーすることができます。例えば商品販売一覧表があって、C2からC10のセル範囲に単価、D2からD10のセル範囲に数量が入っている場合において、E2からE10のセル範囲に単価と数量を掛け合わせた計を作成する場合、セルE2にセルC2とD2を掛け合わせた計算式「=C2*D2」を作成し、まずは2行目だけを考えて計算式を作成します。
この計算式をコピーして、E3からE10のセル範囲に貼り付けると、すべての値が求められます。
これがExcelの便利なところで、計算するセルが何万行にも渡っていても全く同じ方法で計算できます。
この計算式がコピーできる機能を使って、その都度計算するのではなく、なるべく1つの計算式を作ったらすべてに適用できるように、常にどこが同じ計算式でできるのか、考えながらシートを作成していくのがExcelの本当に上手な使い方です。
Excelを使う上でこの上手な使い方は、常に意識してください。
さて、そのコピーされた計算式ですが、どうしてちゃんとそれぞれ別々の値になって正解の値になっているのでしょうか。本来、コピーは全く同じものを作るはずです。
そこで、一番下のセルE10の計算式を見てみましょう。
はじめにセルE2に入力した計算式は「=C2*D2」だったのに、「=C10*D10」になっています。これは勝手にExcelがやってくれることです。E3からE9のセル範囲に入ってくれている計算式も同様に3行目から9行目の計算式に自動で変わっています。
Excelの計算式のコピーは、今までの「コピー」の常識ではなく、勝手にその位置に合わせて1個1個計算式を作るというコピーなのです。
Excelの計算式は同じものを作るコピーという常識には当てはまらないということをポイントとして必ず意識しておきましょう。
前置きが長くなりました、この計算式のコピーについて、わかりやすく説明をしていきます。
コピーの位置を決める手品のカラクリ
Excelは計算式をコピーすると勝手に計算式の中のセル参照を変えることがわかりました。
ではその法則はどうなっているのでしょうか？人間が望んだことをテレパシーのように受け取って決めてくれているわけではありません。表の中身を考えてこうしたらいいだろうと予測して作ってくれているわけでもありません。これには明らかな法則があります。
セルE2に入力した計算式は、このように、セルC2とD2をかけたものでした。
このときポイントになってくるのは、計算式の中身に書いてあるセル参照も重要なのですが、一番注目する必要があるのは、入力するセルの位置です。
入力するセルから見て、今回の計算式は、「2つ左のセルかける１つ左のセル」という位置関係です。
その下のセルE3からE10までのセル範囲でも、計算式の入るセルに対し、すべて「2つ左のセルかける１つ左のセル」の法則は変わらないです。
Excelの計算式のコピーは、この計算式の入るセルから見た位置関係をコピーするのです。これがExcel計算式の一番大きなポイントです。
Excelの中では、単純なコピーではなく、このように法則を読み取って、わざわざひとつひとつを間違いなく、しかも一瞬で計算式を大量に作り出すようになっているのです。
この機能を使わないのは損ですよね。
この機能は関数でも同じです。
数量と計の合計を求める場合は、まず、数量の合計を求めます。セルD11にD2からD10までの合計を計算する「=SUM(D2:D10)」の計算式を入力します。
この計算式は、セルD11に入力され、その9個上のセルから1個上のセルまでの合計となっていて、この法則はセルE11に入る計の合計も同じです。ということはコピーできるのです。
同じような計算式だけど絶対に数量を見たい
次の計算式では上記の計算書に合わせて廃棄した商品の個数がF2からF10の範囲に入力されています。廃棄はマイナスになるので本来それが販売できた場合の売上金額が知りたいところです。単価と廃棄個数を掛け合わせての廃棄金額の計が計算されます。その廃棄計はG2からG10のセル範囲に求めるのです。
この場合、E2からE10に計算している計の計算式が非常に似ているので、コピーしたいところです。
しかし、セルG2で見てみると、計算式の位置関係は「４つ左のセル×1つ左のセル」という法則になっていて、位置関係がそのままでは使えないことがわかります。
どちらも共通するのは、「1つ左のセル」を使うこと、必ず「単価」の列であるC列を使うことです。
ひとつ左のセルは、上記の方法でいいのですが、問題は、単価の列で、売上金額の計から見たら2つ左だし、廃棄の計から見たら4つ左なのです。これではコピーできません。
しかし、コピーしても必ずある列を指定する方法があります。
E2の計算式では「=C2*D2」で、「C2」のC列が売上金額の計でも廃棄の計でも見たいところで、コピーしても変わってほしくないところです。
この変わってほしくない「C」の前に「$」を付けると、横にコピーしても必ずC列を見るようになります。
E2の計算式では「=C2*D2」の計算式を「=$C2*D2」に変更し、C3からC9と、G2からG9にコピーしてみましょう。
最後のセルG10の計算式を確認してみると、「C列の同じ行のセル×１つ左のセル」になっていることがわかります。
必ず固定したい列の列番号の前に「$」を付けることによって、コピーしても必ずその行を見るようになるのです。これで、セルE2の計算式１つ作成すれば、E3からE10、G3からG10の17個のセルに一度にコピーできるのです。
先月比と達成率を1つの計算式で求めたい
今度は次のように前月比と達成率を求めたいです。どちらも割り算なので、できれば1つの計算式のみ作成してコピーしたいです。
前月比は合計÷1月実績、達成率は合計÷2月目標という計算式です。
どちらも11行の合計を使います。
セルD14に入る計算式としては、合計÷2つ上のセルです。これは、前年比、達成率を求めるセルすべてで同じといえます。
計算式では「=D11/D12」になりますが、必ず合計である11行目を見るので、11の前に「$」を付け、「=D$11/D12」とします。
ではこの計算式をセルD15とE14とE15に貼り付けてみましょう。
セルE15で確認してみると、同じ列の11行のセル÷2つ上のセルの計算式が入っています。
消費税額で求めたい
今度は、売上金額の計と廃棄の計を消費税額込みで求めたいとします。
消費税を求めるためにかける数はセルI2に入力されているものを使います。
セルE2に入る計算式は「必ず単価の列×ひとつ左のセル」でした。そこにセルI2の値をかければ消費税額は出るでしょう。
では、そのセルI2はどういう位置関係でしょうか。
消費税額は必ずセルI2を見ます。絶対にI列の2行目になりますので、「I」と「2」どちらの前にも「$」がつき、「$I$2」となります。
つまりセルE2の計算式は「=$C2*D2*$I$2」となります。
この計算式をE3からE10、G2からG10の範囲にコピーし、セルG10の計算式を見てみましょう。
C列の同じ行のセル×1つ左のセル×セルI2になっています。
まとめ
この記事では、Excelの計算式のコピーは、同じものをコピーするのではなく、位置関係を考えてExcelが自動的に計算式を一つずつ変えてくれることを説明しました。
このことによって、計算式を1つ作ったらそれをコピーするだけで多くのセル範囲に計算式を作りだすことができるのです。
一方で、位置関係によらず、必ず見たいセルという考え方もありました。計算式のセル参照で必ず見たい場所の前に「$」を付けるというのがポイントです。
ここで、3つの用語を覚えておいてください。
まず「$」がつかない「D2」のようなセル参照は、コピーしたときに相対的に場所が変わるので、「相対参照」と呼びます。これが基本です。
コピーしたときに絶対にそのセルを見たいという「$I$2」という列番号の前、行番号の前両方につく場合、絶対にそこを見るということで「絶対参照」と呼びます。
コピーしたときに列か行のどちらかだけ必ず見たいという「$C2」「C$11」のような場合、「相対」と「絶対」の間なので「複合参照」と呼びます。
「このセルは絶対参照にしなければいけない」とか「ここの参照はB列だけ必ず見るようにするから複合参照ね」というような会話があるので、用語として覚えておいてください。
この「$」がつくセル参照方法は、その計算式をコピーするときにのみ必要になります。1つしか作らない計算式では全く意識する必要はありません。
また、コピーする場合においても必ず絶対参照か複合参照になる「$」を使うのかということはありません。逆に「$」がつかない相対参照にしないと正常に動作しない場合もあります。
コピーしたときに位置関係で計算するのか、必ず見たい行や列があるのではないか、というように判断するとわかりやすいでしょう。
この計算式のコピーができるので、Excelはできるだけ手間をかけずに作るものです。よくExcelを使って効率化しなさい、という意味の本当の意味は、このExcelの計算式のコピーを駆使し、効率化しなさいという意味なのです。
計算式のコピーを常に意識して計算書を作成し効率的な作業に結び付けていきましょう。
