1日10分!1年後に差がつくExcel教養

ちょっとした事なのに、意外と知らない便利なExcelの技を10分でお届け!

関数の行や列を固定する具体的な方法。

関数や数式を入力した場合、大抵の場合はそれをコピーして複製します。

しかし、これには大きなデメリットがあります。

それは、コピーして複製すると関数や数式が対象とするセルが勝手にずれてしまう事です。

これだと正しい結果を得ることができません。

その為、ずれないように回避するためには、「相対参照」「絶対参照」の概念を理解して、使いこなせるようになることが大切です。

とても重要な概念のですので、しっかり理解する必要があります。

 

最もわかりやすく、代表的な学習方法に「九九表」の作成がありますので、

これを基に解説していきます。

 

 

□「相対参照」と「絶対参照」について

 関数や数式が参照するセルを固定することを絶対参照といい、

参照するセルを固定しないことを相対参照といいます。

エクセルでは、指定しない限り関数や数式は相対参照になっています。

つまり、

一つのセルで数式を作り、コピーして使うと最初に設定している数式がずれてしまいます。

九九の表を使って確認してみます。

①B2のセルに数式を入れます「=A2*B1」

f:id:Djiro:20201210224221p:plain
 ↓

②J10のセルまでコピー

f:id:Djiro:20201210224241p:plain

結果、九九の表ができませんでした。

なぜこうなるのか?

他のセルをクリックして数式がどうなっているか、確認してみます。

f:id:Djiro:20201210224317p:plain

セルがズレているのがわかります。

例えば、

E5のセルを見てみると「=D5*E4」となっています。本来であれば、A5とE4を掛けるのが正解です。

こうなっていないのは、上記にも記載していますが「A列を固定したり」「1行目を固定」していないからです。

これが相対参照になっているから起きる現象です。

もちろんこの設定の方がいい場合もあります。

例えば、

各支店の各月の合計を求める際に、合計値を入れている場合などです。

f:id:Djiro:20201210225211p:plain

4月の合計を求める数式を一つ作って、横にコピーすると各月の合計を求められます。

f:id:Djiro:20201210225228p:plain

f:id:Djiro:20201210225244p:plain

f:id:Djiro:20201210225257p:plain


これを絶対参照にしておくと、掛けるセルが固定されてしまうので、各月の合計はすべて4月の合計になってしまいます。

理屈はとても簡単です。

後は、固定する方法について解説します。

 

 

□参照する列や行を固定する方法

「A列を固定する」「1行目を固定する」など、参照先のセルを固定する方法はとても簡単です。

それは、固定したい行または列の前に「$」(ドル記号)をつけるだけです。

具体的に説明します。例えば、

「$A2」とすれば、A列を固定すると言う意味になります。

これを使った数式をコピーすると、常にA列が固定されます。

「B$1」とすれば、1行目を固定すると言う意味になります。

これを使った数式をコピーすると、常に1行目が固定されます。

こんなイメージです↓

f:id:Djiro:20201210230151p:plain

これを使って、先程の九九表を作成してみます。

数式「$A2*B$1」を入力して、コピーにて作成 

f:id:Djiro:20201210230552p:plain

f:id:Djiro:20201210230433p:plain

しっかりと九九表になりました。

 

 

□上達のワンポイント

数式を入力した後に、間に「$」(ドル)を入れていくのは面倒です。

そんな時は、「F4」キーを押すと「A2」→「$A$2」のように一発で参照方法を切り替えることができます。

更に、続けて「F4」キー押すことで「A$2」→「$A2」→「A2」と言うように、固定する場所を変更することができます。

覚えておきましょう。

 

 

□まとめ

※関数には、「絶対参照」「相対参照」がある。(初期設定は相対参照)

 →絶対参照:関数が参照するセルが固定されること。

 →相対参照:関数が参照するセルが固定されていないこと。

※セルを固定する絶対参照を使用する場合は、行や列の記号の前に「$」を記入する。

「F4」キーを使うと、切り替えが容易にできる。