ROW関数・COLUMN関数の使い方｜行・列変更でも壊れにくいExcel設計

ROW関数・COLUMN関数がズレる理由と設計の考え方

本記事では、ROW関数・COLUMN関数の基本編を理解していることを前提とします。
関数の説明ではなく「どのように使うと壊れにくいか」という設計の観点を整理します。

ROW関数・COLUMN関数の基本的な使い方を学習したい方は
ROW関数・COLUMN関数の基本編のページへどうぞ。

こうした経験があるなら、原因は「関数が難しい」ことではありません。
原因はシンプルで「位置」に依存して設計していることです。

練習用ファイルを使って、実際に見ていきましょう。

練習用ファイル「ROW連番」を使用します

A列に「=ROW()-1」とROW関数を入力しています。

この場合、開始位置が数値として固定されているため、ROW関数の入力されているセルより上に行を挿入したり、行を削除するとズレが発生します。

このようにROW関数より上の行に増減があった場合や、COLUMN関数より左の列に増減があった場合のズレを修正する方法として、いくつかご紹介します。

ROW関数の後に、ヘッダーなどの除外する行数を「=ROW()-1」の様に直接入力しています。
今回の様に、挿入された行が1行なら、この「-1」を「-2」に変更して「=ROW()-2」とし、オートフィルで最終行まで反映させれば正しく反映されます。

=ROW()-削除する行数を直接入力
=ROW()-1

ヘッダーなどの除外する行数を直接入力する方法より、少し安定する方法が「開始行・開始列を固定する」方法です。

ROW関数の後に、ヘッダーなどの除外する行数を「=ROW()-ROW($A$1)」の様に開始行・開始列をセル指定で固定することで、どこが番号を振る起点か明確になり修正がしやすくなります。

この例では「=ROW()-ROW($A$1)」の「($A$1)」部分を「($A$2)」に変更して「=ROW()-ROW($A$2)」とし、オートフィルで最終行まで反映させれば正しく反映されます。

ただし、この方法はROW関数より上に行が挿入された場合には有効ですが、開始行を指定したセルを含む行を削除した場合、当然ですが「#REF!」エラーが出るので注意が必要です。

=ROW()-ROW(開始行・開始列を絶対参照)
=ROW()-ROW($A$1)

開始行・開始列を固定する方法より、さらに少し安定する方法として、名前を使って固定する方法です。
セル番地ではなく「意味のある名前」で管理することで、構造変更の影響を受けにくくなります。

この方法は、予めヘッダーなどの除外する行番号（列記号）に名前を付けておくことで、行や列の増減があっても「名前の管理」から1か所修正するだけで済みます。

リボンメニューの数式タブを開いて、真ん中くらいにある「名前の定義」をクリックします。

「新しい名前」というダイアログボックスが出てくるので、名前と参照範囲を指定します。
今回は名前を「開始行」とします。

参照範囲には「=ROW(ROW連番!$A$1)」と入力します。

絶対参照を忘れないよう、注意してください。

最後に「OK」ボタンを押して完了です。ではROW関数を入れていきましょう。

開始行に名前を付けたので、ROW関数の後に、ヘッダーなどの除外する行数は「-開始行」でOKなので「=ROW()-開始行」と入力します。

開始行を変更したい時は、リボンメニューの数式タブを開いて、真ん中くらいにある「名前の管理」を押すと、ダイアログボックスが出てくるので、変更したい名前の定義を修正して閉じます。

今回は「=ROW(ROW連番!$A$1)」を「=ROW(ROW連番!$A$2)」に修正します。

名前の定義を修正したので、オートフィルで最終行まで反映する必要はありません。

修正箇所は集約できますが、構造変更の影響は完全には避けられません。
それでも「どこを直せばよいか」が常に1か所に集約されるため、運用上の事故は最小化できます。

=ROW()-開始行（事前に名前を定義）
=ROW()-開始行

ROW関数・COLUMN関数は、セルの「位置（行番号・列番号）」を返す関数です。
そのため、参照しているセルの位置が変われば、返される値も変わります。

例えば、途中に行や列を挿入・削除した場合、見た目上は同じデータを扱っているつもりでも、内部的には行番号・列番号が変わってしまいます。

行や列を挿入・削除したとき、ROW関数・COLUMN関数を使った式は自動的に再計算されるため、意図しない値に変わることがあります。

設計者が「同じ意味のデータを扱っているつもり」でも、Excelはあくまで「位置」を見て計算するため、意図と計算結果がズレやすくなるのです。

これは関数の仕様として正しい動作ですが、「構造を変えたときに値が変わる」という点が、設計上の弱点になります。

つまり、ROW関数・COLUMN関数を使った設計は【行・列が増減する運用】【データの位置が変わる運用】の環境では、注意が必要になります。

ROW関数・COLUMN関数が悪いのではなく
「位置を参照している」こと自体が制約条件になります。

ROW関数・COLUMN関数は有用ですが、位置に依存した設計はズレやすくなります。
設計時には「構造変更が起きたときどうなるか」を意識することが重要です。

位置に依存しない設計方法（FILTER関数などの動的配列関数）については、「意味」でデータを扱う設計として、別記事で解説予定です。（公開後にここへリンクを追加します）