行または列で最初／最後の空白ではないセルを返すにはどうすればよいですか？

日常的なExcel作業では、一部のセルにのみ値が入力され、他のセルは空白になっているデータセットに遭遇することはよくあります。多くの場合、特定の行または列で最初または最後に入力されたセルを素早く特定する必要があります。数件のエントリであれば簡単ですが、長い行や列ではこれらの値を手動で探すのは非効率的で時間がかかり、間違いも生じやすくなります。

行または列の最初または最後の空白ではないセルの値を効率的に取得するための実用的な方法がいくつかあり、タスクを自動化し貴重な時間を節約できます。以下では、数式ベースのものやその他の実践的なソリューションをご紹介します。これらはダイナミックなデータ範囲や大規模なデータセットを扱うリアルライフシナリオに対応しています。

数式を使って行または列で最初の空白ではないセルを返す

数式を使って行または列で最後の空白ではないセルを返す

VBAマクロを使用して最初または最後の空白ではないセルの値を返す

Excelのフィルター機能を使用して最初または最後の空白ではないセルを見つける

数式を使って行または列で最初の空白ではないセルを返す

行または列から最初にデータを含むセルを抽出するために、INDEX関数とMATCH関数を組み合わせて使用することができます。このアプローチは、基礎となるデータが変更された際にもワークシートを動的に保ちつつ、値をプログラム的に取得したい場合に適用されます。具体的な手順は以下の通りです：

1. データ範囲の隣にある空白セルに次の数式を入力してください：

=INDEX(A1:A13,MATCH(TRUE,INDEX((A1:A13<>0),0),0))

例えば、データがA1からA13のセルにある場合、この数式は最初の空白ではないゼロ以外のセルを返します。下記の例をご覧ください：

2. Enterキーを押してください。範囲内で空白ではない（またはゼロではない）最初のセルの値が次のように表示されます：

注意点とヒント：

• 上記の数式では、A1:A13を任意の列または行（例えば1:1で行1、B2:M2で行の部分範囲）に参照するように調整できます。
• この方法は単一行または単一列に対して確実に動作します。表形式や複数選択範囲については、各行または列ごとに個別に数式を適用することを検討してください。
• 数式がエラー（#N/A）を返す場合、範囲内に少なくとも1つの空白ではなくゼロ以外のセルが含まれていることを再度確認してください。
• 真の空白（""）の場合、ゼロではなく空のセルを無視したい場合は、<>0を<>""に置き換えてください。

Kutools AIでExcelの魔法を解き放つ

• スマート実行: セル操作、データ分析、グラフ作成を簡単なコマンドで行います。
• カスタム数式: ワークフローを合理化するための独自の数式を生成します。
• VBAコーディング: 簡単にVBAコードを作成し実装します。
• 数式の解釈: 複雑な数式を簡単に理解できます。
• テキスト翻訳: スプレッドシート内の言語障壁を取り除きます。

AI搭載ツールでExcelの機能を強化しましょう。今すぐダウンロードして、かつてないほどの効率を体験してください！

数式を使って行または列で最後の空白ではないセルを返す

指定された範囲内の最後の空白ではないセルから値を取得するには、配列ベースのLOOKUP数式がダイナミックまたは変更されるデータに対して効率的でシンプルな解決策を提供します。特にリストやサマリーテーブルでの最新のデータ入力を自動的に特定するのに役立ちます。

1. 対象範囲の隣にある空白セルに次の数式を入力してください：

=LOOKUP(2,1/(A1:A13<>""),A1:A13)

この数式は指定された範囲をスキャンし、最後の空白ではないセルの値を返します。例えば、A1:A13を範囲として使用すると：

2その後 Enterを押すと、Excelは最後の空白ではないセルの値を計算し表示します：

注意点と提案：

• この数式は任意の単一列または単一行（B1:B20、F8:F30、2:2など）で使用できます。必要に応じて範囲参照を更新してください。
• データにゼロが含まれておりそれを無視したい場合、A1:A13<>""をA1:A13<>0に置き換えますが、真の空白とゼロが意図に一致しているか確認してください。
• このアプローチはシンプルなデータ範囲に最適です。「""」（空のテキスト）を返す数式が含まれる範囲では、この数式はそのようなセルを空白と見なします。
• すべてのセルが空白の場合、数式は#N/Aエラーを返します。

VBAマクロを使用して最初または最後の空白ではないセルの値を返す

大量のデータセットを扱ったり、繰り返しタスクを自動化する必要があるユーザーにとって、シンプルなVBAマクロが特に範囲が多様だったり多くの行や列が関わる場合に、プロセスを大幅に簡素化します。数式とは異なり、VBAはオンデマンドで最初または最後の空白ではないセルを見つけるアクションを実行できるため、複数の範囲にわたる操作に理想的です。

1. 開発 > Visual Basic に移動してVBAエディタを開きます。表示されるVBAウィンドウで、挿入 > モジュールをクリックし、次のいずれかの手順をモジュールウィンドウに貼り付けます：

選択した範囲で最初の空白ではないセルを見つけるためのマクロ：

Sub FindFirstNonBlankCell()
    Dim rng As Range
    Dim cell As Range
    Dim firstValue As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    firstValue = ""
    
    For Each cell In rng
        If cell.Value <> "" Then
            firstValue = cell.Value
            Exit For
        End If
    Next cell
    
    If firstValue <> "" Then
        MsgBox "The first non blank cell value is: " & firstValue, vbInformation, xTitleId
    Else
        MsgBox "No non blank cells found.", vbExclamation, xTitleId
    End If
End Sub

同様に、ここに最後の空白ではないセルを見つけるコードがあります：

Sub FindLastNonBlankCell()
    Dim rng As Range
    Dim cell As Range
    Dim lastValue As Variant
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    lastValue = ""
    
    For Each cell In rng
        If cell.Value <> "" Then
            lastValue = cell.Value
        End If
    Next cell
    
    If lastValue <> "" Then
        MsgBox "The last non blank cell value is: " & lastValue, vbInformation, xTitleId
    Else
        MsgBox "No non blank cells found.", vbExclamation, xTitleId
    End If
End Sub

2コードを実行するには、 実行 ボタン をVBAエディタでクリックします。ターゲット範囲を選択するよう求められるので、選択して確認すると、実行したマクロに応じて最初または最後の空白ではないセルの値がダイアログボックスに表示されます。

• これらのマクロは柔軟性があり、データサイズに関係なく行および列の両方に適しています。
• VBAは自動化と繰り返し処理を可能にするため、頻繁に行われる大規模なタスクに最適です。
• マクロを実行する際は、常にワークブックを保存し必要な場合はマクロを有効にしてください。重要なデータに適用する前にサンプルデータで正確性をテストしてください。

Excelのフィルター機能を使用して最初または最後の空白ではないセルを見つける

非常に大きな列のデータを持つユーザーで、空白ではない値を視覚的に識別する必要がある場合、Excelの組み込みフィルター機能を利用して、一目で空白ではないエントリを強調表示できます。この方法では別のセルに値が自動的に返されることはありませんが、データ分析中のレビューまたはナビゲーションに非常に効果的です。

フィルタリングによって最初または最後の空白ではないセルを目で探す方法は次の通りです：

1. データを含む列または行を選択します。より簡単なフィルタリングのために列全体（例：列の文字をクリック）を選択しても構いません。
2. データタブをクリックし、フィルターを選択します。
3. 範囲またはテーブルのヘッダーにある小さなフィルター矢印をクリックします。
4. （空白）オプションのチェックを外して、入力されているセルのみを表示するようにします。
5. フィルタリング後、列の一番上に見える最初の値が最初の空白ではないセルであり、一番下までスクロールすると最後の空白ではないセルが表示されます。

利点： フィルタリング方法は迅速で、数式を必要とせず、数千行ある列でもうまく機能します。
欠点： この解決策は視覚的なものであり、結果をセルに出力したり、数式やVBAのように自動化をサポートしません。それでも、手動でのチェックやレビュー、インタラクティブなデータ探索に理想的です。

トラブルシューティングと提案：
フィルターが正しく動作しない場合、データの一部だけを選択していないか確認してください。そうすると、フィルターが正しく適用されない可能性があります。フィルターを終了するときは データ > クリア をクリックしてフルデータセットビューを復元します。