Aさん 関数の数式を直接入力できるようになってきました！
もっと関数を実務で役立てていくために、他に覚えておいた方が良いことはありますか？？
森田 直接入力ができるようになったのは素晴らしいですね！
次のステップとして、数式を再利用し時短できるようになりましょう！
そのためには、相対参照や絶対参照といった参照の種類が大事です。
では、具体的に解説していきますね。
解説動画：【数式/関数#5】関数で実務の作業効率を上げるアプローチ2選
この記事の内容は下記の動画でも解説しています。
コメント欄の各プロセスの時間部分をクリックすると該当の解説へジャンプできますよ！
はじめに
この記事は関数の概要を把握していることが前提です。
関数の概要については以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
実務で関数をフル活用するには、ベースの数式をコピペで使い回すことが大事
関数を活用する際の大枠の流れは3ステップあり、実務の作業効率を上げるには、次の2つのアプローチが重要でした。
- いかに手早くベースの数式を挿入するか（STEP2）
- いかに同じ数式を複数セルへ使い回すか（STEP3）
本記事の対象は、この中の「②いかに同じ数式を複数セルへ使い回すか」です。
実務では、同じような数式を複数セルへセットすることがよくあります。
ただし、それら1つずつ数式を記述するのは面倒ですね。
そのため、ベースとなる数式を1つセットしたら、それをコピペし、その他のセルへ再利用していくことがセオリーです。
それにより、セルの数だけ数式のセット作業を時短できます。
なお、数式のコピペはいろいろな方法がありますが、表の書式を崩さないよう「形式を選択して貼り付け」で「数式」のみペーストすることが無難です。
コピーするセルとペースト先のセルの書式が同じであれば、通常の「貼り付け」やオートフィルでも問題なし。
数式はコピペする状況に合わせて適切な「参照の種類」を設定すること
数式をコピペする上で最重要なのは、STEP2の時点でベースとなる数式の参照の種類を状況に合わせた種類に設定することです。
一例として、九九の表で縦軸（A2~A10セル）と横軸（B1~J1セル）を掛け算した結果を計算するとします。
ベースの数式はB2セルですが、この数式の参照の種類を適切に設定したかどうかで、コピペ後の結果へ影響してしまいます。
参照の種類を適切に設定できた方は、1つの数式を他の80セルへ流用でき、数式のセット作業を大幅に時短できていますね。
逆に言うと、この参照の種類を適切に設定できないと参照が誤ってしまうため、全81セルへ1つずつ数式をセットないしは修正せざるを得ないとも言えます。
これでは、せっかく便利な関数を使っていても、時短効果がそこまでなく、非常にもったいないですね。
この参照の種類で注目すべき「$」の部分です。
「$」は、コピーした数式を別セルへペーストした際、参照セルが固定されるかどうかの目印です。
この「$」の有無により、参照の種類は次の4種類あります。
| 参照の種類 | コピペ後の挙動 | 例 |
|---|---|---|
| 相対参照 | 上下左右ともスライド | A1 |
| 絶対参照 | 上下左右とも固定 | $A$1 |
| 複合参照 （行：絶対参照、列：相対参照） |
上下：固定 左右：スライド |
A$1 |
| 複合参照 （行：相対参照、列：絶対参照） |
上下：スライド 左右：固定 |
$A1 |
セル参照の規定は相対参照。
コピペ後に参照セルをスライドさせるどうかに応じて、適切な種類の参照の種類を設定しましょう（設定方法は本記事下部を参照）。
相対参照/絶対参照/複合参照の使用ケース
参照の種類の代表的な使用ケースを3つご紹介します。
【ケース①】相対参照
相対参照はコピペ後の参照セルがスライドします。
実務では、同じレコードの他列のセルを参照した数式をコピペするケース等で使うことが一般的です。
一例として、「単価」×「数量」で「金額」を計算する場合、G2セルへ「=E2*F2」の数式を記述し、それを他セル（G3~G11セル）へコピペするといったイメージです。
上記は厳密に言えば、複合参照で列だけ固定しても良いですが、固定しなくとも問題ない場合は、相対参照のままがお手軽です。
この部分は個人のお好みで問題ありません。
【ケース②】絶対参照
絶対参照はコピペ後の参照セルが固定されます。
実務では、複数セルで共通するパラメーターとなる値が入ったセルを参照した数式をコピペするケース等で使うことが一般的です。
一例として、J2セルの「消費税率」と「金額」列の値で「消費税額」を計算する場合、H2セルへ「=G2*$J$2」の数式を記述し、それを他セル（H3~H11セル）へコピペするといったイメージです。
H2セルの数式のうち「$J$2」が絶対参照（「G2」は相対参照）。
このように、複数セルへ共通するパラメーターはJ2セルのようにセルを用意し絶対参照にすることで、パラメーターに変更が生じた際の修正先を1セルにまとめることが可能です。
今回の例であれば、消費税率が変わった場合、数式すべてを修正せずとも、J2セルの値を変更するだけでメンテナンスが終わるといったイメージですね。
なお、関数のSUM等で集計対象のセル範囲を固定するといった場合も絶対参照を活用することが多いです。
【ケース③】複合参照
複合参照はコピペ後の参照セルが行列どちらかが部分的に固定されます。
実務では、クロス集計表のように表の縦軸と横軸のセルを参照した数式をコピペするケース等で使うことが一般的です。
自セルと同じ行の縦軸、同じ列の横軸をそれぞれ参照。
一例として、前述の九九の表を計算する場合、B2セルへ「=$A2*B$1」の数式を記述し、それをB2セル含む他セル（B2~J11セル）へコピペするといったイメージです。
これで、縦軸（A2~A10セル）はA列固定で上下のみスライド、横軸（B1~J1セル）は1行目固定で左右のみスライドされるようにできました。
参照の種類の切り替え方法
参照の種類はデフォルトでは相対参照です。
これを絶対参照や複合参照へ切り替える場合、数式入力時にセルを選択した後、「F4」キーを押す回数で任意の参照の種類へ切り替えできます。
| 「F4」キーの押下回数 | 参照の種類 | 例 |
|---|---|---|
| 0 ※規定 | 相対参照 | A1 |
| 1 | 絶対参照 | $A$1 |
| 2 | 複合参照 （行：絶対参照、列：相対参照） |
A$1 |
| 3 | 複合参照 （行：相対参照、列：絶対参照） |
$A1 |
「F4」キーの押下回数が「4」以上は「0」へ戻る。
参照の種類に慣れるまでは、適切な参照の種類になったかどうか、コピペしてみて他セルの数式が問題ないかを「F2」キー等でチェックしましょう。
なお、後から参照の種類を修正する際、数式バーで該当の参照セルにカーソルを合わせて「F4」キーで任意の参照の種類になるまで押下しても良いですし、手入力で「$」の有無を調整しても良いです。
【参考】後から「置換」コマンドで複数セルの参照の種類を一括修正することも可能
後から複数セルの参照の種類を一括で修正したい場合、「置換」コマンドを活用すると便利です。
一例として、相対参照を複合参照へ切り替えたものが以下です（「E」→「$E」）。
「検索する文字列」に指定する文字列が短い場合は、関数名やその他のセル番地まで変更されてしまう場合あり。
「置換」コマンドの詳細は以下の記事をご参照ください。
表の中の複数セルをまとめて修正/削除したい場合は「置換」が有効 実務では、表の複数セルに共通する修正や削除を行いたい場合があります。 たとえば、本来の表記は「りんご」なのに、一部のデータに「林檎」という別表記があるといっ …
【応用】セル範囲の起点と終点で参照の種類を変える場合もある
関数でセル範囲を引数として設定する場合、セル範囲の起点と終点の両セルを同じ参照の種類にすることが一般的です。
セル範囲を選択後に「F4」キーで起点/終点の両セルをまとめて参照の種類変更可。
ただし、ケースによっては、セル範囲の起点セルは相対参照、終点セルは絶対参照にするといったことも可能です。
これにより、SUMで累計を計算するといった応用が可能となります。
SUMでの累計計算の詳細は以下の記事をご参照ください。
はじめに この記事は関数の概要とSUMの使い方を把握していることが前提です。 参考記事 関数の概要とSUMの使い方については以下の記事をご参照ください。 累計とは 「累計」とは、辞書を引くと、次のような意味です。 部分ご …
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります（上記リンクから登録フォームへ遷移します）。
前述の使用ケース①~③を参考に、ベースの数式挿入→コピペで各シートの所定の数式をセットしてください。
- 「相対参照」シート（G2~G11セル）
- 「絶対参照」シート（H2~H11セル）
- 「複合参照」シート（B2~J10セル）
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
関数を実務で活用していく上で、状況に合わせて適切な参照の種類を設定できるようになることはマストなスキルです。
ぜひ、ベースの数式をコピペで再利用し、最小工数で複数セルへ数式をセットできるようになりましょう！
なお、数式や関数を拙著で体系的に解説していますので、こちらも参考にしてみてください。
また、よりコアな情報を発信していくために「公式LINE」を開始しましたので、ご興味があれば以下バナーから友だち追加をお願いします！
特典として、Excelワークシート関数一覧（計22スライド）やExcelショートカットキー一覧（計25スライド）等をプレゼント！
ご参考になれば幸いですm(_ _)m
森田 本記事は数式を使う方全員に理解してほしい最重要ポイントでした。
慣れないうちは頭が混乱するかもしれませんが、コピペ後の挙動を見ながら正しい参照の種類にしていく試行錯誤を繰り返せば、次第に理解できるはずです。
実務で手を動かしながら、ぜひとも参照の種類をマスターしてください。
はじめまして。「絶対参照・相対参照の使い分け方」を読ませていただいて連絡させていただいております。勉強になります、有難う御座います。1つ困っていることがありましてお尋ねさせていただきたいのですが、「勤怠管理表」を作成しているのですが出勤時間と退社時間が異なる曜日があり、残業時間計算式も曜日によって異なっています。各曜日に残業時間計算式を固定させる方法と、カレンダーの○月を変更して始まる曜日からにも連動させたいのですがどのようにして設定したら良いのかが分からない状況です。先生の本で「曜日＝残業時間計算式」固定方法、連動させる方法を書かれたのはありませんでしょうか？有りましたら購入して勉強させていただきたいので教えていただきたく宜しくお願い致します。
東浦さん
コメントありがとうございます。
お困りの内容とピンポイントではないかもですが、勤怠管理表の作成方法については、「すごい！関数」で紹介しております。
一部上記の本の解説ではマクロと連動させている部分はありますが、お困りの内容は関数で対応できるかと思います。
ご参考になれば幸いです。