関数や数式をコピーして別セルに貼り付けるとズレて困ることありますよね。
これはコピーした式の参照先が貼り付けたいセルの分だけ移動するからです。
つまり2列右に移動したら、式の参照先も2列分だけ動くというわけです。
2列右に移動しても参照先が動かないように設定するのが「絶対参照」です。
ということを知っていても「絶対参照」って、覚えにくくないですか。
でも1つのルールさえ覚えればOKなんです。
使い方も合わせてマスターしましょう。
よかったらフォローしてください。→ハヤトのTwitter
目次
Excelの「絶対参照」と「相対参照」の簡単な覚え方
絶対参照の場合、値に「$」が付きます。
ただ「$」は何の値に対して付いているのかこんがらがってしまうことはないでしょうか?
私は最初とても苦手でした。
なんだよ「$」って、と思ってました。
私が最初に絶対参照を使ったのが、「VLOOKUP関数」の範囲指定のときです。
「VLOOKUP関数」の場合、絶対参照しか利用しないのであまり考えることをせずに、とりあえず「F4」と覚えました。
Excelでファンクションキー「F4」を1回押すとセル値が絶対参照されますので、機械的に覚えたわけです。
でもちゃんと理解して覚えておいた方がいいです。
覚え方としては
「右側の値を絶対参照している」
1行目とB列を絶対参照
1行目を絶対参照
B列を絶対参照
参照方式の切り替え
ファンクションキー「F4」
「=B1」と入力した状態で「F4」を押すと、押した回数に応じて絶対参照される値が変わります。
「相対参照」「絶対参照」「複合参照」とは
相対参照
通常、数式を入力した状態は相対参照です。
数式が入力されたセルをコピーした場合、自動的に参照するセルが変更されます。
絶対参照
参照するセル番地を固定する参照方式のことです。
数式をコピーして貼り付けても参照するセル番地は変わりません。
複合参照
相対参照と絶対参照が混じった参照方式です。
行か列かのどちらかが絶対参照になる2通りがあります。
言葉だけではわかりづらいので、実際のExcelを使って説明しましょう。
説明の例として、A1からE2まで1から10までの数値が入ったExcelがあります。
これをA4からE5に参照するという方法で説明します。
相対参照
セルA4の参照先を「A1」とする式を入力します。
通常の入力ですね。
セルA4をコピーしてA4からE5を選択して貼り付けます。
A1からE2までをそのままコピーしたものになります。
全部の式を見てみましょう。
つまり、A1の式は「3行上の行を参照する」というものだったわけです。
ですので、A1の式をコピーして他のセルに貼り付けた場合、他のセルも「3行上の行を参照する」という式が入っているわけです。
絶対参照
参照するセル番地を固定する参照方式のことです。
数式をコピーして貼り付けても参照するセル番地は変わりません。
セルA4の参照先を「A1」とする式を入力し、「F4」を1回押して絶対参照にします。
セルA4をコピーしてA4からE5を選択して貼り付けます。
全てA1を参照しているものになっています。
全部の式を見てみましょう。
複合参照
相対参照と絶対参照が混じった参照方式です。
行か列かのどちらかが絶対参照になる2通りがあります。
行のみ絶対参照
セルA4の参照先を「A1」とする式を入力し、「F4」を2回押して行を絶対参照にします。
セルA4をコピーしてA4からE5を選択して貼り付けます。
1行目のみ絶対参照されていますので、4行目から1段下の5行目も1行目を参照したものになっています。
全部の式を見てみましょう。
列のみ絶対参照
セルA4の参照先を「A1」とする式を入力し、「F4」を3回押して列を絶対参照にします。
セルA4をコピーしてA4からE5を選択して貼り付けます。
A列目のみ絶対参照されていますので、A列目より右側の列も常にA列目を参照したものになっています。
全部の式を見てみましょう。
「絶対参照」「複合参照」の練習
こちらから練習用Excelを取得してください。
→複合参照と絶対参照の練習シート.xlsx
次の表を完成させてください。
列と行の数字が交わるセルに、それぞれの列と行の数字をかけた値を入力してください。
作成前
作成後
まずセルB2から入力しましょう。
「=B1*A2」つまり「=2*3」なので「6」となります。
セルB2の式をコピーして「B3」に貼り付けた場合、「=B2*A3」と1行ずつ行が下がります。
「C2」に貼り付けた場合、「=C1*B2」と1列ずつ右に移動します。
E5まで式を貼り付けた場合、このようになります。
このようにならないように行を1行目に絶対参照し、列はA列に絶対参照しておく必要があります。
行を固定するには、「B$1」となるようにします。
まず「B1」だけ選択して、「F4」を2回押します。
そうするとセルB2の式をコピーしてセルB3に貼り付けても、「B1」は参照されたままになっています。
セルB2の式をコピーしてセルB3、B4、B5と貼り付けます。
B列の掛け算の値は正常に計算されました。
次にA列を固定することを考えましょう。
セルB2の式をコピーしてセルC2に貼り付けると「=C$1*B2」となり、列が右に移動してしまっているのがわかります。
そこでA列を固定するためにAの前に「$」を入れてやります。
セルB2の式をC2、D2、E2に貼り付けてやります。
2列目の式も正常に計算されました。
全部のセルに式を貼り付けてみましょう。
式の確認方法
それぞれの式がどう入力されているかの確認をしましょう。
メニューで操作する場合:「数式」→「数式の表示」
ショートカットキーの場合:(Ctrl + Shift +`)
ショートカットキーの方が楽なのでこちらを覚えましょう。
まとめ
長々と書きましたがポイントは「右側の値を絶対参照している」です。
これさえ覚えておけば間違いないです。
合わせて利用したいExcelテクニック
・VLOOKUP関数は「Excelが使える」の指標
・誤って保存せずに終了したExcelを復元する方法
・Excel(エクセル)で集計データを作成できるピボットテーブルの解説