業務改善と標準化を同時に実現:MS 365業務システム開発

オートフィル機能とセルの絶対参照/相対参照【データ分析エクセルスキル】

    
エクセルのオートフィル機能とセルの絶対参照/相対参照【データ分析エクセルスキル】
\ この記事を共有 /
オートフィル機能とセルの絶対参照/相対参照【データ分析エクセルスキル】

オートフィルはエクセルでの作業の高速化には必須な必ずマスターしたいスキルです。その為には数式内にドルマークを入れる絶対参照と相対参照を覚える必要があります。その機能を初心者でも分かり易く図解説明しています。

(動画時間:5:09)

エクセルのオートフィル機能:「ダブルクリック」で簡単コピー

こんにちは、リーンシグマ、ブラックベルトのマイク根上です。業務改善コンサルをしています。

今日のテーマはオートフィル機能と他のセルの参照方法です。この二つはエクセルでデータ分析をする上で絶対に不可欠です。先ずは簡単なオートフィルから。

例えばこのワークシートではA列に購買日が入っていてD列に全てのセルに数式が入ってます。もしこれ全部を手でタイプしなければいけないなら僕はエクセルを使うのを止めます。もちろんそんなことは無いですね。最初のセルを変えた時やそのセルに始めて式を入れた時は、それを下まで適用したいです。その最初のセルを選ぶとセルの右下の角が小さい四角になり、それにマウスを合わせるとマウスが黒の十字に変ります。

セルの右下の角

マウスの黒の十字

そのまま下にドラッグするとその数式がコピーされます。しかし行数が多いとすごく時間が掛かりますね。そこでその代わりにその十字をダブルクリックすると一番下までコピーされます。あっという間でしたね。僕も始めてやった時は感動しました。

また、全列を選択して、一番上のセルの右上にマウスを持って来るとマウスが黒の十字に変わり、それを右にドラッグすると列の幅も含めて全てをコピー出来ます。

全列のオートフィル機能

オートフィルで必須の絶対参照/相対参照

しかしここで問題です。D列の中の式をダブルクリックすると青色でA列の購買日を参照して四半期を出しています。コピーしたE列ではB列の販売チャネルに参照先がずれているのでエラーになっています。(上図参照)ここでセルの絶対参照、相対参照が必要になります。その言葉自体は重要ではないですが、その二つの概念の違いを理解して下さい。

先ほど下にコピーした式を見るとその参照先も下にずれています。各日付の四半期を横に出したいのでこれはちょうど良いのです。この参照方法は「相対的にずれてくれる」ので「相対参照」なのです。しかし隣の列にコピーした時はずれてほしくないです。この時に「絶対参照」を使うのです。使い方は簡単で半角の“$(ドル)”マークを入れるだけです。

絶対参照例

先ほどの例ですと、A列を固定する、または絶対にしたいのでAの前に“$(ドル)”マークをそれぞれ入れて(上図参照)、下にオートフィルし直し、横にコピーすると今度はエラーになりません。ここで知ってもらいたいのは横方向は絶対参照ですが、縦にコピーした時は相対参照のままであることです。これを複合参照と言います。

まとめると左右にコピーして通常は列のアルファベットが変わるところ、それを絶対にしたい時はアルファベットの前に$マークを入れます。反対に上下にコピーして通常は行番号が変わるところ、それを絶対にしたい時は行番号の前に$マークを入るのです。

これで絶対参照/相対参照が理解できます。

円ドル変換表作成

もう一つ練習しましょう。最近仕事で似たような作業をしたんですが、上図の上の表は全て円表示だとします。今日の円ドルの為替相場が1ドル112.46円です(セルN32)。その下にドル表示の表を作れと言われたらどうしますか?やってみましょう。

左上のセルからやります。700割る112.46でドル表示になります(上図セルN35参照)。それをただ下にコピーしたら参照先がずれますね。青の分子はずれてほしいですが、赤の分母は32行のままにしたいのでその32の前にドルマークを入れて(下図参照)、下にコピーします。上手くいきました。

行のみ絶対参照

今度は全ての数式を右にコピーします。だめですね。これは‘Divided by 0’エラー、つまり「0で割ったエラー」で、分母は空白で0で割ったらエラーなのです。

0で割ったエラー

そうじゃなく、右にコピーした時も分母はN列で固定したいですのでそのNの前にもドルマークを入れます。つまりドルマークを二つ入れるのです。 ちなみにここでドルマークを手入力する代わりにウィンドウズだとF4キーを、マックの場合は“command + T”を押すとドルマークの位置を簡単に変えられます。

セルの参照切替ショートカット

僕はウィンドウズを使ってますのでF4キーを押します(下図参照)。一回押すとNの前に移動し、もう一度押すと相対参照になり、もう一度押すと両方ともドルマークを入れられます。

ショートカット実行例

全てのセルをコピーし直して、セルの分母の参照先が同じになりました。まさに絶対参照ですね。そしてアメリカドル表示の表が完成しました。

円ドル変換表完成

この参照方式の概念を完全に理解するのに僕もすごく時間がかかりました。いつも言っていることですが、何度も使って使いこなせるようになって下さい。

「こちらの記事も読まれてます