Excelで行を挿入または削除する際に、常に上にあるセルの値を取得するにはどうすればよいですか？

Author Xiaoyang Last modified 2025-08-26

Excelでは、上にあるセルの値を直接参照することは、累積合計を作成したり、月ごとの比較を行ったり、ワークシートが進化するにつれてデータの一貫性を維持したりする場合に一般的な要件です。通常、=D5のようなシンプルな数式を使用して上にあるセルを参照できますが、この解決策には制限があります。行を挿入または削除すると、数式が期待通りに新しい「上」のセルを参照しなくなる可能性があります。下記に示すように、新しい行を上に挿入することで連続性が途切れたり、参照が元のセルに固定されたりすることがあります。

A screenshot showing how the reference to the above cell breaks after inserting a row in Excel

この問題を解決し、行を挿入または削除した後でも常に直上のセルから値を取得できるようにするには、いくつかの方法があります。各方法には、ワークシートの複雑さ、自動更新か手動数式か、VBA/マクロの使用に慣れているかどうかに基づくトレードオフがあります。

目次：

行を挿入または削除する際に、常に上にあるセルの値を数式で取得する
VBAイベント駆動型マクロを使用して、上にあるセルの値を自動的に更新する（常に動的）

行を挿入または削除する際に、常に上にあるセルの値を数式で取得する

この問題をマクロや複雑な設定なしで簡単に解決するには、行がどのように変化しても常に上にあるセルを動的に参照する数式を使用します。この数式はExcelのINDIRECT関数とADDRESS関数を使用しており、挿入や削除によって行が移動しても常に「上」のセルをトラッキングします。このため、リストの上部や中央に新しいデータを頻繁に追加するワークシートに特に適しています。

次の数式を、常に上にあるセルの値を取得したいセル（例えば、B5を参照したい場合はB6など）に直接入力してください：

=INDIRECT(ADDRESS(ROW()-1,COLUMN()))

数式を入力した後、Enterキーを押します。現在のセルには、直上のセルの値が即座に表示されます。以下のように表示されます：

A screenshot showing the formula to reference the above cell in Excel using INDIRECT and ADDRESS

これで、参照数式があるセルの上に行を挿入すると、数式が再計算され、常に新しい上にあるセルの値が表示されます。このように、行の挿入や削除に関係なく、数式は常に最新の状態を保ちます。参考までに、以下のスクリーンショットをご覧ください：

A screenshot showing the correct cell reference maintained after inserting a row in Excel

パラメータの説明とヒント：

この数式は、現在の数式があるセルの直上のセルの値を取得します。つまり、B6で使用すると、行の挿入や削除があっても常にB5の値が反映されます。
この数式をデータの最初の行（例：A1）で使用すると、存在しない行からデータを取得しようとして#REF!エラーが返されることがあります。これを回避するには、最初の行では空白を表示するようにエラー処理を追加できます。例えば、=IF(ROW()=1,"",INDIRECT(ADDRESS(ROW()-1,COLUMN())))を使用します。
INDIRECTは揮発性の関数であるため、非常に大きなワークシートでは過剰に使用すると計算が遅くなる可能性があることに注意してください。
この数式は、ワークシートの構造がどのように変化しても、行の位置に関する厳密な関係を維持したい場合に非常に効果的です。

トラブルシューティングとまとめ：
行を挿入または削除した後、数式が期待通りに更新されない場合は、正しいセルに入力されていることを再確認してください。また、静的な絶対セル参照（例：$A$1）を使用していないことも確認してください。 #REF! 最初の行でエラーが発生した場合、前述のように条件付き数式を使用することを検討してください。高度な自動化が必要な場合や、単に参照ではなく値をコピーする必要がある場合は、以下のVBAイベント駆動型マクロのソリューションをご覧ください。

VBAイベント駆動型マクロを使用して、上にあるセルの値を自動的に更新する（常に動的）

行を挿入または削除しても、常に上にあるセルの値に自動的に更新されるソリューションが必要で、さらに数式をコピーせずに行いたい場合、イベント駆動型のVBAマクロの使用が有効です。この方法は、マクロの有効化に慣れているユーザーにとって理想的で、動的な更新が可能です。特定のセルや範囲を選択するたびに、または値を変更するたびに、マクロがターゲットセルを常に上にあるセルの値に一致させます。これにより、ワークシートがどのように変化してもデータ入力が一貫性を保つことができます。この機能は、新規行ごとに固定パターンやデフォルト値を設定したいテンプレートに特に便利です。

以下は、Worksheet_Changeイベントを使用してこの機能を設定する方法です：

1. この機能を設定したいワークシートタブを右クリックし、「コードの表示」を選択します。Microsoft Visual Basic for Applicationsエディターが開き、該当するワークシートモジュールが表示されます。

2. 次のVBAコードをワークシートモジュールウィンドウにコピー＆ペーストします：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim WatchRange As Range
    On Error Resume Next
    ' Set the range you want to monitor (for example, B2:B100)
    Set WatchRange = Intersect(Target, Me.Range("B2:B100"))
    
    If Not WatchRange Is Nothing Then
        Application.EnableEvents = False
        Dim cell As Range
        
        For Each cell In WatchRange
            ' Avoid the first row, or adjust as needed
            If cell.Row > 1 Then
                cell.Value = Me.Cells(cell.Row - 1, cell.Column).Value
            End If
        Next cell
        
        Application.EnableEvents = True
    End If
End Sub

パラメータの注意点：Me.Range("B2:B100")内の"B2:B100"を、この動作を適用したい実際の範囲に置き換えてください（例：列全体を指定する場合は"B:B"に設定できますが、範囲を狭めることでパフォーマンスが向上し、意図しない上書きを防げます）。

3. VBAエディターを閉じます。これで、指定した範囲内でセルが変更、挿入、またはワークシートが更新された場合、Excelはそれらのセルを自動的に更新して上にあるセルの値を反映します。例えば、5行目に新しい行を挿入すると、その時点から列Bのすべての監視対象セルが新しい位置の直上のセルの値を取得します。

注意：このコードは監視対象範囲内で手動入力された値を上書きします。数式が含まれるセルや、元の入力を保持したい場合には注意して使用してください。
将来もこのVBAマクロを引き続き使用する場合は、ファイルをマクロ有効ブック（.xlsm）として保存する必要があります。
イベントコードは、マクロが貼り付けられたワークシートモジュールでのみ動作します（すべてのシートに渡って動作させるには、各シートモジュールにコードを追加する必要があります）。
値の変更時ではなく、セルを選択したときに更新を行う場合は、Worksheet_SelectionChangeと同様のロジックを使用できます。

トラブルシューティングとまとめ：
VBAスクリプトがコピー後に動作しない場合は、ワークブックでマクロが有効になっていること、および標準モジュールではなく正しいシートモジュールにコードを貼り付けたことを確認してください。エラーが発生したりExcelがフリーズしたりする場合は、Application.EnableEventsが自動セル変更前にFalseに設定され、後にTrueにリセットされていることを確認して、再帰的なループを回避してください。その他の高度な動作やより細かい制御が必要な場合は、データ構造に基づいたカスタムスクリプトを検討してください。

最高のオフィス業務効率化ツール

🤖	Kutools AI Aide：データ分析を革新します。主な機能：Intelligent Execution｜コード生成｜カスタム数式の作成｜データの分析とグラフの生成｜Kutools Functionsの呼び出し……
	人気の機能：重複の検索・ハイライト・重複をマーキング｜空白行を削除｜データを失わずに列またはセルを統合｜丸める……
	スーパーLOOKUP：複数条件でのVLookup｜複数値でのVLookup｜複数シートの検索｜ファジーマッチ……
	高度なドロップダウンリスト：ドロップダウンリストを素早く作成｜連動ドロップダウンリスト｜複数選択ドロップダウンリスト……
	列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示/非表示の切替｜範囲＆列の比較……
	注目の機能：グリッドフォーカス｜デザインビュー｜強化された数式バー｜ワークブック＆ワークシートの管理｜オートテキストライブラリ｜日付ピッカー｜データの統合｜セルの暗号化/復号化｜リストで電子メールを送信｜スーパーフィルター｜特殊フィルタ（太字/斜体/取り消し線などをフィルター）……
	トップ15ツールセット：12 種類のテキストツール（テキストの追加、特定の文字を削除など）｜50種類以上のグラフ（ガントチャートなど）｜40種類以上の便利な数式（誕生日に基づいて年齢を計算するなど）｜19 種類の挿入ツール（QRコードの挿入、パスから画像の挿入など）｜12 種類の変換ツール（単語に変換する、通貨変換など）｜7種の統合＆分割ツール（高度な行のマージ、セルの分割など）｜… その他多数