【Excel】エクセルにて行挿入で数式の参照がずれる（行削除も）時の対策【関数：崩れないように：行を増やす：関数を反映させたい】  

この記事では「エクセルにて行挿入で数式の参照がずれる（行削除も）時の対策【関数：崩れないように：行を増やす：関数を反映させたい】」について解説していきます。

基本方針は下記３つとなるでしょう。

 

・「コピーしたセルの挿入」機能を用いる

・OFFSET関数とROW関数により参照範囲を割り出す

・参照範囲を1列全体にする

 

それでは、サンプルデータを用いて解説しましょう。

 

エクセルにて行挿入で数式の参照がずれる（行削除も）時の対策【関数：崩れないように：行を増やす：関数を反映させたい】

下記サンプルは工数を管理しているリストで、作業者と稼働時間から合計工数を求めています。

新たな作業が増えたため、追加を行おうとしている想定です。

セルの挿入を行うための手順の例は、

といったところです。

しかし、これでは単純に空のセルが挿入されるのみで、肝心の数式は別途入力しなければなりません。

そこで、「コピーしたセルの挿入」を行ってみましょう。

範囲を指定した状態で、コンテキストメニューより「コピー」をクリックします。

次の行のセルを選択し、コンテキストメニューより「コピーしたセルの挿入」をクリックします。

下記ダイアログが開くので、「下方向にシフト」を選択します。

数式が入力された状態で挿入できました。

ご覧の通り、参照しているセルもずれていません。

後は、「作業名」「作業者数」「稼働時間」を編集するだけです。

 

 

エクセルにて行挿入で数式の参照がずれる（行削除も）時の対策【関数：崩れないように：行を増やす：関数を反映させたい】  ②

全作業の合計工数を計算したいとなった場合を想定してみましょう。

下記サンプルでは、セルD8に「=SUM(D2:D7)」と入力することになるでしょう。

なお、セル参照を行っているので、”(ダブルクォーテーション)で括ってしまうと” D2:D7”という文字列として扱われ、期待している結果となりませんので、ご注意ください。

SUM関数について解説しますと、当関数は指定した範囲の合計値を求める際に使用し、

と入力します。

ここでは「D2:D7」としているので、D2+D3+…+D7を求めることになります。

ENTERを押すと、全作業の合計個数が計算されます。

ここで、「新たな作業を追加したい」となったらどうなるでしょう。

下記のように、最終行以外の行に「コピーしたセルの挿入」を行った場合は、特に気になるところはありません。

しかし、これが最終行である場合は、下記の通り、事情が異なります。

これを解決するには、計算範囲を可変にする必要があります。

「=SUM(OFFSET($D$2, 0, 0, ROW()-ROW($D$2)))」に入れ替えてみましょう。

登場した関数について解説しますと、OFFSET関数は指定したセルから○行△列のセルを取得する際に使用し、

と入力します。

各引数には

をそれぞれ指定します。

 

ROW関数は指定したセルの行番号を取得する関数で、

と入力します。

 

参照を指定した場合は参照したセルの行番号を、未指定の場合は当関数を入力したセル自身の行番号を出力します。

上記の例では、(ROW()-ROW($D$2)=6 (ROW()=8、ROW($D$2)=2となるため)となり、これをOFFSET関数の高さとして設定し、計算範囲を自動的に求める仕組みとなっています。

 

ENTERを押し、数式を確定させましょう。

その後、最終行に挿入し、正しく計算範囲を捉えられていることが確認出来たら、お題クリアです。

 

 

エクセルにて行挿入で数式の参照がずれる（行削除も）時の対策【関数：崩れないように：行を増やす：関数を反映させたい】  ③ 

下記サンプルでは、入力されたデータから、最高金額及びその日付を抽出しようとしています。

ここでは日付・金額をそれぞれセルE1、F1に書き出す想定でお話しします。

セルE1には「=XLOOKUP(F1,B:B,A:A)」と入力します。

XLOOKUP関数について説明しますと、当関数は指定した値の同一行(列)のデータを取得する処理を行い、

と入力します。

検索する値がセルF1で、一致するものをB列で検索、一致した行のA列の値を取得する、と覚えておけばイメージしやすいでしょう。

また、[見つからない場合]及び [検索モード]を設定するかどうかは自由ですが、本件においては、設定しなくてもよいでしょう。

ENTERを押すと、想定外の結果が出てきますが、セルF1への入力がまだなので気にする必要はありません。

続いてセルF1に「=MAX(B:B)」と入力します。

MAX関数は指定された範囲における最大値を抽出する際に使用し、

と入力します。

ここでは「B:B」としているのでB列全体が抽出対象となります。

ENTERを押すと、最高売上が判明し、それがいつなのかも同時に判明しました。

対象の範囲が1列全体なので、どれだけ行の追加・削除が行われても全く影響を受けません。

 

まとめ　エクセルにて行挿入で数式の参照がずれる（行削除も）時の対策【関数：崩れないように：行を増やす：関数を反映させたい】  ③ 

 この記事ではエクセルにて行挿入で数式の参照がずれる（行削除も）時の対策【関数：崩れないように：行を増やす：関数を反映させたい】  ③について解説しました。

関数がたくさん登場したので、少し難しかったかもしれませんが、エクセルには強力な機能を持った関数がいくらでもある、ということを知っていただけただけでもよいです。

少しずつ勉強し、スキルアップしていきましょう。