エクセルにおける参照「相対参照・絶対参照・複合参照」の使い方と具体的な活用例
エクセルを使う上で避けて通れない概念が「参照」です。 難しくは有りません。詳しく紹介します。
参照とは特定セルを見に行くこと
そもそも参照とは何でしょう？これは特定のセルを見ることです。参照を使うにはセルに = と入力し、その後に参照先のセルを指定します。参照先のセルをクリックすれば自動で入力されます。もちろんキーボードで入力してもOKです。 このとき、= は必ず半角で入力する必要があります。
例えばA3のセルに 「=A1」と入力すると、A3のセルにはA1の値が表示されます。 A3は参照なので、A1の値が変わるとA3の表示も変わります。
これが参照の基本です。
指定が無い場合、参照は「相対参照」として処理される
参照はコピーすると参照のセル番地も変化します。例えば以下のような状態で、B1セルにA1の参照をセットします。
B2、B3、B4と下にこの式をコピーします。エクセルを使ったこととがあれば誰でも一度は使ったことがあるであろうフィルコピーを使いましょう。 フィルコピーし、B4のセルを見てみるとB4セルの中は「=A4」となっています。=A1ではなく、=A4になります。
相対とは、今の位置からみてどこにあるかを指します。日常生活でもよく使いますね。 「この店の３件右隣のお店」といった表現は相対です。
エクセルに話を戻しましょう。B1はA1を参照しますが、これは言い換えると「今いるセルから見て左に１つ移動した場所」なのです。 相対参照はコピーすると「今いるセルから見て左に１つ移動した場所」という情報をコピーします。だからB4セルを見ても、B4セルからみて左に１つ移動した場所、つまりA4を参照することになります。
絶対参照について
我々は日常生活で住所をよく使用します。「栃木県宇都宮市大工町102」といった表記は、北海道からみても沖縄から見ても変わりません。 どこから見ても「栃木県宇都宮市大工町102」です。なので住所は絶対参照と言えます。
エクセルで絶対参照を使用するには$をつけますが、通常は手動でつけることはあまり有りません。式を入力中にF4キーを押すことで絶対参照になります。 $がAの前と1の前に付きます。絶対参照はこのように$が2つ付きます。
エクセルで絶対参照を使うと、フィルコピーをしてもずれることが有りません。なぜなら「どのセル番地からみてもA1セルはA1」だからです。
絶対参照の使い所
さてコピーしても移動しないことはわかりましたが、この絶対参照はどのようなときに使うのでしょう？具体的な例が無いとイメージがつきにくいですね。 例えば商品リストの単価があり、消費税率が別の箇所に記載されているケースでは非常に効果的です。
１つ目の商品に「商品単価×税率」の式を入力します。=B5×C2と入力すれば正しい結果が求まります。 もし絶対参照を使わないでこの式をコピーすると、次のように意図しない結果が返ってくることでしょう
なぜこうなるのか？少し考えてみてください。B列は１つづつずれるので良いですが、C列の税率はズレてしまうと困るわけです。 セルC8の式を見てみると、次のようになっています。
これでは正しい計算にならないのが明確ですね。このように税率のセルを固定したいときは、税率のセルを絶対参照にして使用します。 以下が正しい式です。
この例はダウンロードできます。 エクセルサンプルをダウンロード
関数の中でも絶対参照はよく使います
絶対参照の使い道は関数でもたくさんあります。例えばCOUNTIF関数を例に見てみましょう。 ある出勤名簿があり、そこからスタッフの出勤回数を数えたいとします。以下のようなイメージです。
名前を数えるにはCOUNTIF関数が便利です。COUNTIFは指定した単語の出現回数を数えてくれます。 式をコピーする際、数える範囲がずれると意図しない結果になるので数える範囲を絶対参照として指定します。一方で、数える対象のスタッフ名は１つづつズレてほしいので相対参照を使います。
列だけ固定・または行だけ固定することを複合参照といいます
さて、これまで「絶対参照」と「相対参照」を見てきました。実はもう１つ面白い参照として「複合参照」があります。 タイトルにもありますが、列だけ、または行だけを固定する参照です。固定したい側に$をつけます。
- 列だけ固定
- =$A1のように記述します。ローマ字の前に$がつくのでローマ字（つまり列）が固定されます
- 行だけ固定
- =A$1のように記述します。数字の前に$がつくので数字（つまり行）が固定されます
絶対参照は行と列を固定するので、$A$1のように$が２回出てくるわけです。これで$の謎が一つ解明できました。 この複合参照は使用頻度でいうと少なめですが、覚えておくと便利な場面もあります。何より$が２個つく理由が理屈で理解できるので知っているだけでも有益です。 さて複合参照を使った面白い例として、例えば掛け算九九を簡単に作れます。
慣れない間は難しく感じるかもしれません。動かしたくないのは列か、行かをはっきりイメージすることが大切です。 式としては=$A3*B$2 のようになります。あとはこれを縦横にフィルコピーすれば、あっという間に掛け算九九表を作ることができます。
複合参照を使って特定の値のある行をまるごと塗りつぶす
もう少し実用的な例を見てみましょう。例えば次のエクセル画面を御覧ください。
E列に「A号車」と入力されていれば、該当する行を１行まるごと塗りつぶす条件付き書式を設定しています。 この指定には複合参照が必須です。少しわかりにくいですが順序立てて見ていきましょう。
- 塗りつぶしたい範囲を予め選択しておく
- メニュー「条件付き書式」を選択
- 新しいルールをクリック
- 「スタイル」をクラシックにする（この辺の表現はExcelのバージョンにより異なる可能性があります）
- 「数式を使用して書式設定するセルを選択」を選ぶ
- 数式バーに「=$E2=“A号車”」と入力
注意深く見てください。数式バーに指定する式は「$E2」という複合参照を使用しています。E列は固定し、行は固定しません。 このように複合参照と条件付き書式を組み合わせることで、視覚的に特定のデータを目立たせることが可能になります。
サンプルも用意しておきましたので興味のある方はダウンロードしてご利用ください。
データの集計はわかった。では集計元のデータをどのようにして用意するか？
このページで使用したようなデータは学習用として予め用意していますが、実際の業務においてはこのようなエクセルデータをどこから持ってくるのか？というのが大きな問題です。 日報やチェック表のデータをエクセルに書き写して集計しますか？
それはあまりにも非効率です。点検者が入力し、集計者が更にExcelに転記するのは二度手間であり、人の手で行う以上、転記ミスも発生します。 こういった日報やチェック表のデータは日報アプリNipoPlusから簡単に取得ができます。NipoPlusはデータの入力に特化した日報アプリです。
スマートフォンやタブレットから手軽に入力でき、集まったデータをワンクリックで１枚のシートにまとめることができます。最後に使用した点検表データなどはNipoPlusからダウンロードしたデータをそのまま使用しています。 無料で手軽に体験できますので、興味があれば是非使ってみてください。