You are here: Home \ Google Drive ・ Office \ スプレッドシート・エクセル \ エクセル・スプレッドシートでオートフィル(右下引っ張り)する時に式の特定の一部の引数を固定(絶対参照)する

    エクセル・スプレッドシートでオートフィル(右下引っ張り)する時に式の特定の一部の引数を固定(絶対参照)する

    オートフィルとは

    Auto-fill機能はGoogle Driveやエクセルに備わっている強力な機能の一つで
    何らかの値が入力されている単一もしくは複数のセルを選択した時に、
    右下にある小さい点をクリックしながら上下左右いずれかの方向にドラッグすると、
    システムがパターンを認識して値を自動的に補完してくれます。
    偉そうに言ってますけど名称は始めて知りました。
    普段何気なく使ってる方が多いと思うのですがオートフィルっていうんですねこの機能。

    やりたいこと

    他セルを参照している引数が複数ある式で、一部の引数の参照のみ固定したままオートフィルをしたい。

    絶対参照せずにオートフィルした場合

    商品Aの税抜価格である1000(B2)と税率(D8)を掛け、税込価格をC2に表示しました。

    20150420s1
    税込価格C2に手動で1080と入力するのではなく、
    税率を別のセルやシートに配置して計算させるメリットは以下の記事をご参考下さい。
    別シートほげほげだよ

    C2をベースにC5までオートフィルしてみましたが、商品A以外は税が反映されません。
    20150420s5

    わかりやすいようにオートフィルによって自動入力された式をD列に表示するとこうなります。
    20150420s6
    税率が入力されているセルD8は式の中で固定したいのですが、
    オートフィルは意図せずに値を増やしD9, D10といった参照が自動入力されてしまいました。
    図には入っていませんが、D9D10は何も入力していない空白のセルなので
    B4 + (B4 * D10)は
    1200 + (1200 * 0) = 1200
    となり、税が反映されません。

    商品数が4つ程度でしたら式を手動で入力し直しても良いかもしれませんが、
    数十や数百の式を手動で修正するのは効率的ではありません。

    絶対参照してオートフィルした場合

    式中で他セルを参照している引数に$マークを付けることによってそのセルを絶対参照している状態になります。
    絶対参照している引数はオートフィル時に式の中で固定され、先ほどのD8〜D11のように数値が増加することはありません。
    ショートカットキーはF4です。Windows,Mac共に計算式の中で絶対参照したい引数をクリックし、F4をクリックすればOKです。
    D8を絶対参照し、オートフィルしてみます。
    20150420s2

    結果はこちら。望んでいた結果が出力されました。
    20150420s3
    オートフィルによって自動入力された式をD列に表示するとこうなります。
    20150420s4

    先ほど失敗したC4の式は
    B4 + (B4 * D10) → B4 + (B4 * $D$8)
    となり、
    1200 + (1200 * 0.08) = 1296
    という計算が適用され1296が出力されました。
    このように絶対参照を利用することにより業務を劇的に効率化できるので是非利用してみてください。

    About author:

    コメントを残す