この記事はプロモーションを含みます
Excelの関数を学習して使えるようになったが、別のセルに同じ関数を貼り付けるときに不便な思いをしたことはありませんか？
- 数式を貼り付けたけど、セルの位置がズレてしまい、求める結果にならない
- 項目ごとの比率を出したいけど、合計のセルが1か所に固定されているので、数式を全て手入力している
複数の数式を作る場合、初めの一つ目の数式にひと手間かけることで、劇的に作業の効率がアップします。
今回はその作業効率がアップするひと手間を紹介します。
もくじ
この記事を読むことで、
- 絶対参照と相対参照の違いを理解して、数式の貼り付けを効率的に行える
- 数式のズレを上手に使って、新しい数式を使えるようになる
といった効果を期待できます。
絶対参照と相対参照について
様々なデータを並べて、一覧表を作り上手に活用するというのが、エクセルを使う一番大きな目的です。
数多くのデータを効率よく入力し、計算するための機能がエクセルに備えられています。
その中の一つが絶対参照と相対参照です。
一般に他のセルを引数にして数式を作った場合は、相対参照になります。
数式を下や右にずらして貼り付けると、引数のセルも同じだけズレます。
一方、絶対参照は、セルをズラさずに位置を固定させます。
相対参照では参照セルがズレて結果もズレる
例えば、図の表には、商品名、単価、個数の順にデータが入力されています。
ここで単価と個数を掛けて金額を算出してみます。
E3セルに「=C3*D3」と入力すると、金額が計算されました。
E3のセルをコピーし、E４からE９まで貼り付けます。
ここでは書式を崩さないように、数式での貼付けを行いました。
貼り付けられた数式からは、正しい結果が出力されています。
各セルの数式を確認してみます。
セルに数式が表示されました。
貼り付けられたセルは、分子、分母ともズレています。
これは、セルの位置ズレに対して同じだけ参照セルもズレているからです。
金額のセルの左隣に、単価と個数のセルが並んでいるので、参照セルをズラす必要があります。
数式の貼り付けは相対参照が標準なので、今回のように参照セルがズレた方が都合の良い時は、相対参照を使うべきです。
相対参照で正しく結果が反映されないパターン
合計の右隣に、構成を表示する列があります。
ここでは、合計金額に対して各商品が占める割合を表示させます。
金額の一番下に合計金額を入れます。ここでは、オートSUM関数を使いました。
構成を計算してみます。
金額/合計金額で計算します。表示形式は、パーセントを使いました。
二行目以降に一行目で作った式を貼り付けてみます。
二行目以降が全てエラーになってしまいました。
数式の表示で確認してみます。
二行目以降は参照セルがズレてしまい、合計金額ではなくて、空白セルを参照してしまっています。
このように、相対参照で正しく式を貼り付けられない場合は、絶対参照を使う必要があります。
絶対参照でセルの位置を固定
一行目の式を見直します。
合計金額のセルは、F10ですが、＄F＄10とします。これで参照セルが固定されます。
二行目以降に貼り付けてみます。
正しく式が貼り付けられました。
数式タブの「数式の表示」をクリックしてください。
計算結果でなくて数式をセルに表示することができます。
合計金額の＄F＄10が固定されていることが分かります。
このように、絶対参照を使うことで、特定の参照セルを固定して、数式の貼り付けを楽に行うことができるようになります。
F4キーを使って絶対参照を設定する
上では、＄を直接手入力して絶対参照を設定する方法を紹介しましたが、ファンクションキーで絶対参照を設定する方法について紹介します。
先程使った構成の一行目を相対参照に戻します。
合計金額にカーソルを当てて、F4キーを押してみます。
「＄F＄10」になりました。
またF4キーを押します。
「＄F10」になりました。
この場合は、列を固定せずに行だけを固定します。
またF4キーを押します。「F＄10」になりました。
行を固定せずに列を固定します。
F4を押し続けると4つの参照パターンを繰り返し表示します。
実際に使える絶対参照の例
絶対参照は、数式を貼り付けるいろいろな場面で使えます。
その中でも、主に使えそうな２つの例を紹介します。
１つめは、構成比の累計を計算する時に使う方法です。
金額を積算し、合計金額で割って計算します。
累計範囲の初めと合計金額の行のみを固定します。
式は、「=SUM(E$3:E3)/E$10」となります。
2行目以降に貼り付けると、累計を計算することができました。
全てのセルの数式を表示してみます。累計範囲の終わりのみがずれています。
棒と折れ線の複合グラフを作って、ABC分析を行う時に使えます。
２つめは、VLOOKUP関数で使う方法です。
VLOOKUP関数を使って、各商品の単価を横に並べています。
数式を表示してみます。
「=VLOOKUP(J$2,$B$2:$C$9,2,0)」となっており、検索値は行を固定、検索範囲は行列ともに固定しました。
初めのセルでの数式を作るのに時間がかかりますが、残りは貼り付けできるので、時短につながります。
まとめ
絶対参照と相対参照を知って正しく使い分けることが出来ると、表を作成する速度が劇的に向上します。
相対参照のみの場合では、数式を貼付けしてその後の手直しが必要になります。
貼り付けた数式の数が多いほど、手直しにかける時間は膨らみます。
絶対参照を正しく理解して、初めの数式でしっかりとセルの参照を設定しておけば、貼付けの作業一つで完了します。
貼り付ける範囲が大きいほど時短の効果は大きくなります。
是非マスターしておきましょう。
最後に、オススメの本の紹介をしておきます。
「たった1日で即戦力になるExcelの教科書【増強完全版】」という本です。
Excelのセミナーで有名な吉田拳先生が執筆されています。
この本は、他の本によく見られるようなExcelの解説のみの本だけではなく、仕事の中でどのようにExcelと付き合っていくべきかという理念も丁寧に書かれています。
Excelの操作に多くの時間を取られている方、Excelの操作に悩んでいる方にとっては、是非活用していただきたい本です。
ある程度Excelを使いこなす自信がある方にとっても、今まで知らなかった使い方を発見出来るので、無駄にはならないと思います。
私もこの本を読むことで、Excelに対する考え方が変わりました。
これまでは無駄なくエクセルを使いこなせているつもりだったのですが、まだまだ簡単な作業で改善を行える余地のあることに気づきました。
また、これからも学習し続けていこうという気持ちです。
本日もありがとうございました。
