Excel で特定の行または列の末尾から2 番目の値を取得するには、どうすればよいですか？

著者Siluvia変更日2025-09-03

大規模なExcel ワークシートを操作する際には、行や列の最後の値だけでなく、特に末尾から2 番目の値を特定する必要が頻繁に生じます。たとえば、以下のスクリーンショットのようにテーブル範囲 A1:E16 が与えられている場合、6 行目または B 列の末尾から2 番目の値を取得したい場面があるでしょう。こうした要件は、最近の変更を追跡したり、時系列データを監視したり、データセット内で最新ではない直近のエントリを分析する際によく発生します。単に最後の値を見つけるよりも、末尾から2 番目の値を特定する方が、データに空白セルが含まれていたり、定期的に更新されたりする場合にはやや複雑になります。本記事では、この作業をより効率的に進め、一般的な落とし穴を回避できるよう、明確でステップバイステップの解決策をご紹介します。

行と列にデータが表示されたExcelのテーブルのスクリーンショット

数式を使用して、特定の行または列の末尾から2 番目の値を検索して返す
VBA コード – マクロを使用して指定された行または列の末尾から2 番目の値を検索する
その他のExcel の組み込み機能 – 空白をすべてフィルター処理し、末尾から2 番目の値を手動で特定する

数式を使用して、特定の行または列の末尾から2 番目の値を検索して返す

上記のスクリーンショットに示すように、範囲 A1:E16 内で6 行目または B 列の末尾から2 番目の値を検索して返す必要がある場合、Excel の数式が動的で効率的な解決策を提供します。この数式は、データが頻繁に更新されたり、追加・削除によって末尾から2 番目の値の位置が変動するようなシナリオに最適です。手動での対応とは異なり、数式を使えば結果が常に最新のデータに自動的に反映されます。

B 列の末尾から2 番目の値を検索して返す

1。末尾から2 番目の値を表示したい空白セルを選択します。数式バーに次の配列数式を入力し、Ctrl + Shift + Enter（旧バージョンのExcel の場合）を押して、配列数式として確定してください。Excel 365 またはExcel 2021 をご利用の場合は、Enter キーを押すだけで OK！これらのバージョンでは、配列数式が自動的に処理されます。

=INDEX(B:B,LARGE(IF(B:B<>"",ROW(B:B)),2))

Excelで列の末尾から2番目の値を検索する数式のスクリーンショット

注：この数式では、B:Bは B 列全体を指します。別の列の末尾から2 番目の値を取得したい場合は、B:Bを目的の列参照に置き換えてください（例：C 列の場合は)C:Cに変更）。この方法は空白セルの有無にかかわらず機能しますが、非表示の行やフィルターで除外されたデータがあると、結果がずれる可能性があるためご注意ください。期待通りの値が表示されない場合は、必ずデータ範囲を再確認してください。

行6 の末尾から2 番目の値を検索して返す

6 行目の結果を表示する空白セルを選択し、数式バーに次の数式を入力してEnterキーを押してください。

=OFFSET($A$6,0, COUNTA(6:6)-2,1,1)

Excelで行の末尾から2番目の値を検索する数式のスクリーンショット

注：上記の数式において、$A$6は行6 の最初のセルを示し、6:6は行全体（6 行目）を表します。対象となる行が異なる場合は、これらの参照を適宜調整してください。この数式は、行6 内の非空セルの数を動的にカウントし、開始セルからオフセットして末尾から2 番目に値が入力されたセルを特定します。行に空文字列（)""）を返す数式が含まれている場合、COUNTA 関数はこれらを非空白としてカウントする可能性があるため、結果に影響を及ぼすことがあります。定数と数式が混在する範囲では、エラーを回避するため、必ず結果を再確認してください。

数式使用時のヒント：

大規模なデータセットでは、列全体を参照する（例：B:B）とパフォーマンスが低下する可能性があります。可能であれば、必要な範囲だけに絞り込んでください（例：B1:B100）。
データに非表示の行が含まれていたり、フィルターが適用されていたりする場合でも、数式は非表示およびフィルターで除外されたセルをカウントします。フィルター済みのデータに対しては、特別な集計関数や補助列の使用をご検討ください。
対象の行または列内のすべての値が空白の場合、これらの数式はエラーを返したり、予期しない結果となることがあります。データには少なくとも2 つの非空白値が含まれていることをご確認ください。

VBA コード – マクロを使用して指定された行または列の末尾から2 番目の値を検索する

さまざまな範囲、特に大規模なデータ範囲で頻繁に末尾から2 番目の値を取得する必要があるユーザーにとって、VBA マクロによる自動化は大幅な時間節約につながります。これは、動的または複雑なテーブルを扱う際に特に効果的です。マクロは、データセットのサイズが変動し、数式を都度更新したくない場合や、同じ方法で複数の異なる位置から値を抽出したい場合に非常に便利です。以下では、対象の行または列を指定するだけで自動的に末尾から2 番目の値を返す VBA コードのソリューションをご紹介します。これにより、手動でのカウントや調整が不要になります。

1。Excel のリボンで開発者向けツールをクリックし、Visual Basicをクリックして VBA エディターを開きます。エディターで挿入＞モジュールをクリックし、新しく作成されたモジュールに次の VBA コードを貼り付けます：

Sub GetSecondToLastValue()
    Dim rng As Range
    Dim arr As Variant
    Dim values As Collection
    Dim i As Long
    Dim secondLast As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.InputBox("Select the row or column range to analyze", xTitleId, Selection.Address, Type:=8)
    If rng Is Nothing Then Exit Sub
    
    arr = rng.Value
    Set values = New Collection
    
    If rng.Rows.Count = 1 Then
        For i = 1 To rng.Columns.Count
            If arr(1, i) <> "" Then
                values.Add arr(1, i)
            End If
        Next i
    ElseIf rng.Columns.Count = 1 Then
        For i = 1 To rng.Rows.Count
            If arr(i, 1) <> "" Then
                values.Add arr(i, 1)
            End If
        Next i
    Else
        MsgBox "Please select a single row or single column range.", vbExclamation
        Exit Sub
    End If
    
    If values.Count < 2 Then
        MsgBox "There are less than two non-blank values in the selected range.", vbInformation
        Exit Sub
    End If
    
    secondLast = values(values.Count - 1)
    MsgBox "The second-to-last value is: " & secondLast, vbInformation
End Sub

2。コードを入力したらExcel に戻り、実行ボタンをクリックするか、Alt + F8を押してリストからGetSecondToLastValueを選択し、マクロを実行します。するとプロンプトが表示され、列（例：B1:B16）または行（例：A6:E6）のいずれかから単一行または単一列の範囲を選択するよう求められます。選択を確定すると、マクロがダイアログボックスに末尾から2 番目の値を表示します。

注記：

このマクロは非空白セルのみをカウントし、選択した行または列内の空セルを自動的にスキップします。
選択範囲内に2 つ未満の非空白値しかない場合は、警告メッセージが表示され、値は返されません。
このマクロは、単一行または単一列の選択を前提としています。複数の行と列を同時に選択すると、選択範囲の調整を促すメッセージが表示されます。
自動化のために、メッセージボックスを表示する代わりに、結果を特定のワークシートのセルにコピーするようコードをさらにカスタマイズできます。

この VBA ソリューションは、動的テーブルや頻繁に変化するデータセットを扱うユーザーに特にメリットをもたらし、手動操作によるエラーや繰り返し作業を最小限に抑えます。

その他のExcel の組み込み機能 – 空白をすべてフィルター処理し、末尾から2 番目の値を手動で特定する

数式やマクロを使えば、末尾から2 番目の値を自動的に検索できますが、データが不規則または非連続であったり、値をたまに確認したいだけの場合は、より迅速で視覚的なアプローチが好まれることもあります。Excel の組み込みフィルター機能を使えば、空白や不要なセルを一時的に非表示にして、数式を編集・記述することなく、目的のエントリを簡単に特定できます。

フィルター方式：

列または行のデータ範囲を選択してください（列の場合はセル B1～B16、行の場合はワークシート上で A6～E6）。
Excel のリボンで、データ > フィルターをクリックして、フィルターの矢印を有効にしてください。その後、列見出しまたは選択した範囲の横にあるフィルタードロップダウンをクリックします。
(空白)のチェックを外すと、空のセルが非表示になります。これにより、データ表示には非空白の値のみがリストされます。
列の場合は、フィルター済みリストの一番下までスクロールし、下から2 番目の値を確認してください。行の場合は（フィルター後が可能な限り）、右端から数えて下から2 番目の非空白エントリを特定してください。

注記とヒント：

フィルター処理は、小～中規模のリストや、値の位置を視覚的に確認したい場合に最も素早く行える方法です。
データセットが非常に大きい場合、フィルター処理はパフォーマンスを低下させる可能性があるため、自動化や繰り返しの用途には向いていません。
フィルターが適用されている場合は、正しいコンテキストで確認していることをご確認ください（フィルター済みリストでは、非表示の行や列がスキップされる可能性があります）。
フィルターを解除するには、データタブでクリアをクリックします。

この方法は、継続的な動的分析や大規模なデータセットにはあまり適していませんが、末尾から2 番目の非空白エントリを透明性高く手動で確認できるため、迅速なトラブルシューティングや検証に最適です。

最高の Office 業務効率化ツール

🤖	KUTOOLS AI アシスタント：次に基づいてデータ分析を革新します：インテリジェント実行｜コード生成｜カスタム数式作成｜データ分析とチャート生成｜拡張機能呼び出し…
	人気の機能：検索・ハイライト、または重複をマーキング｜空白行を削除する｜データを失うことなく列の結合またはセルを｜数式を使用しない四捨五入...
	スーパー LOOKUP：複数条件 VLookup ｜複数値 VLookup ｜複数シート間 VLookup ｜ファジーマッチ....
	高度なドロップダウンリスト：ドロップダウンリストをすばやく作成｜連動型ドロップダウンリスト｜複数選択可能なドロップダウンリスト....
	列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示状態を切り替え｜範囲および列の比較...
	注目の機能：グリッドフォーカス｜デザインビュー｜強化された数式バー｜ワークブックとシートマネージャー｜リソースライブラリ（オートテキスト）｜日付ピッカー｜ワークシートの統合｜暗号化／セルの復号化｜リストからメール送信｜スーパーフィルター｜特殊フィルタ（太字のフォントを持つセルをフィルタリング／斜体／取り消し線。。。）。。。
	トップ15 ツールセット：12 テキストツール（テキストの追加、特定の文字を削除、...）｜ 50+チャートタイプ（ガントチャート、...）｜ 40+実用的関数（誕生日に基づいて年齢を計算します、...）｜ 19 挿入ツール（QR コードを挿入、パスから画像を挿入、...）｜ 12 変換ツール（単語に変換する、為替レートの変換、...）｜ 7 結合と分割ツール（高度な行のマージ、セルの分割、...）｜さらに多数

Kutools はお好みの言語でご利用いただけます。英語、スペイン語、ドイツ語、フランス語、中国語、および40+の他の言語をサポートしています！

Kutools for Excel でExcel スキルを強化し、これまでにない効率を体験しましょう。Kutools for Excel は、生産性を高め、時間を大幅に節約できる高度な機能を300 以上提供します。最も必要な機能を今すぐ入手するにはこちらをクリック。。。

Kutools for Excel の詳細はこちら。。。無料ダウンロード。。。

Office Tab は Office にタブインターフェースをもたらし、作業を大幅に簡単にします

Word、Excel、PowerPoint でタブを使った編集と閲覧を有効にします。Publisher、Access、Visio、Project でもご利用いただけます。
複数のドキュメントを、新しいウィンドウではなく、同じウィンドウ内の新しいタブで開いたり作成したりできます。
日々の生産性を50％も向上させ、毎日数百回ものマウスクリックを削減します！