btzgrp
  • NGINXをDockerコンテナとして実行する方法 - TechRepublic
Headlines

Excelデータ範囲の最後の値を返す方法 - TechRepublic

05 mins
Excelデータ範囲の最後の値を返す方法 - TechRepublic

列の最後の値を取得するのは、データベースでは簡単な作業ですが、Excelはデータベースではありません（多くの人がデータベースとして利用しているにもかかわらず）。Excelでこの作業を実行するのは少し難しいですが、ほんの少しです。この記事では、Tableオブジェクトと通常のデータ範囲で使用できる数式を紹介します。また、VBAでの同等の機能も紹介します。

Windows 10 64ビットシステムでExcel 2016を使用しています。デモ用の.xlsxファイルとCLSファイルをダウンロードしていただけます。データセットの数式は以前のメニューバージョンでは機能せず、マクロはブラウザ版では機能しません。ブラウザ版はTableオブジェクトを認識しませんが、数式は機能します。Excelはブラウザでファイルを開いた場合、Tableオブジェクトの構造参照を使用するほど賢いです。

参照: Excel スキルを磨く 10 のパワーヒント (TechRepublic PDF)

テーブルを使用する

Excelのテーブルオブジェクトのおかげで、このような作業は以前よりも簡単になりました。数式は構造参照を使用しているため長くなりますが、参照するのはテーブルのみです。つまり、数式をほぼどこにでも配置でき、対象の列の上下にデータを入力できます。さらに、構造化参照はセル参照よりも読みやすく、この点では範囲名に似ています。

図Aは、請求書データのシンプルな表と、列Cの最終日付（請求日）を返す数式（E2）を示しています。ご覧のとおり、この数式と表には、表の合計行と隣接するデータが含まれています。

図A

この数式は列 C の最後の日付を返します。

この数式では、テーブルと請求日列への構造化参照を使用します。

=INDEX(Invoices[Invoice Date],COUNTA(Invoices[Invoice Date]))

この数式の仕組みを理解するには、INDEX() 関数と COUNTA() 関数の仕組みを知っておく必要があります。

  • INDEX() は、構文 INDEX(配列,行, [列] )を使用して、指定された範囲内の特定の行と列の交差点にあるセルの値または参照を返します。
  • COUNTA() は、構文 COUNTA( value1 , [ value2 ])を使用して、特定の範囲内の空でないセルの数をカウントします。

Excel のカウント関数の詳細については、「Excel のカウント関数の詳細」をお読みください。

COUNTA() 関数は 30 を返します。これは、Invoice Date 列にヘッダー行と合計行を除いて 30 行あるためです（これらの行は [#All] 識別子を含めることでカウントに含めることができます）。配列には列が 1 つしかないため、列引数は不要です。COUNTA() が 30 を返すため、INDEX() 関数は 2017 年 8 月 7 日の 30 行目の Invoice Data の値を返します。テーブルを移動したり、テーブルの上に行を追加したり、Invoice Data の上または下にデータを追加したりしても、この数式は引き続き機能します。

参照: Excel での作業をスピードアップする 10 の時間を節約するヒント (TechRepublic)

この数式は柔軟性に優れていますが、一つ欠点があります。それは、空白セルを処理できないことです。COUNTA() は、テキストや空の文字列「」を含むすべての値を評価しますが、空白セルはカウントされません。そのため、返される数は行の総数から空白セルの数を差し引いたものになります。レコードに値がない場合、0 または NA を入力してください。

リストAのコードを使って、VBAで同じことを実現できます。End(xlDown)プロパティは、請求日列の最終セルを参照します。先ほどと同様に、テーブル参照は最終行の位置を認識しているため、テーブルの下にテキストがあっても問題ありません。ただし、この参照には合計行が含まれており、Offsetプロパティでその行をカバーしています。

注:このウェブページからコードを直接コピーしないでください。代わりに、CLSファイルをダウンロードする か、コードを手動で入力してください。

リストA

Function ReturnLastValueTable()
   'Return last value in specified column.
    ReturnLastValueTable = Range("Invoices[Invoice Date]"). _
       End(xlDown).Offset(-1, 0).Value
    Debug.Print ReturnLastValueTable
End Function

データ範囲を使用する

同じ一般的な数式はテーブルなしでも機能しますが、柔軟性は低くなります。図Bでは、数式は同じ形式ですが、セル参照を使用しています。

=INDEX(C:C,COUNTA(C:C))

今回は、COUNTA()関数は行1のヘッダーセルをカウントするため、31を返します。したがって、この数式は列Cの行31、2017年8月7日の値を返しています。合計行がある場合は、それも考慮する必要があります。

図B

通常のデータ範囲内でも同じ数式が機能しますが、柔軟性は低くなります。

この数式は通常のデータ範囲でも機能しますが、柔軟性は低くなります。列参照が更新されないため、データセットを移動することはできません。データセットの上に行を挿入すると、COUNTA() はデータセットの上にある空白行を評価しないため、計算が狂う可能性があります。COUNTA() はデータを含むセルの数を返します。ただし、列 C のデータセットの上にあるセルにデータが含まれている場合は、数式は引き続き機能します。この詳細を覚えておく必要があるため、このような配置はお勧めしません。COUNTA() の列参照は列全体を評価するため、データセットの下にデータを入力することはできません。データセットの下にデータを入力すると、カウントが狂ってしまいます。

リストBのコードを使っても同じことを実現できます。End(xlDown)プロパティは最初の空白セルで停止します。つまり、最終行と新しいデータの間に少なくとも1つの空白セルがあれば、列Cに設定されたデータセットの下にデータを入力できます。

リストB

Function ReturnLastValueDataSet()
   'Return last value in specified column.
    ReturnLastValueDataSet = Range("C1").End(xlDown).Value
    Debug.Print ReturnLastValueDataSet
End Function

ラストコール

この簡単な説明は網羅的ではありませんが、ほとんどの状況で十分でしょう。テーブル式の方が柔軟性は高いですが、どちらの式も空白セルに対応していません。レコードに有効な値がない場合、0を入力してください。

Officeに関するご質問をお送りください

読者からの質問には可能な限りお答えしますが、必ずお答えできるとは限りません。リクエストがない限り、ファイルは送信しないでください。添付ファイル付きの初回サポートリクエストは未読のまま削除されます。ご質問を明確にするために、データのスクリーンショットを送信していただくことも可能です。お問い合わせの際は、できるだけ具体的にご記入ください。例えば、「ワークブックのトラブルシューティングを行い、問題点を修正してください」という質問では返答がないかもしれませんが、「この数式が期待どおりの結果にならない理由を教えていただけますか？」という質問であれば、回答が得られる可能性があります。ご使用のアプリとバージョンを明記してください。読者サポートにあたり、TechRepublicから時間や専門知識の報酬を受け取ることはありません。また、サポートした読者から料金を請求することもありません。お問い合わせは[email protected]までお願いいたします。

また読んでください…

  • Office 365 のヒント: オートコレクト機能を使って生産性を向上 (ZDNet)
  • 初心者向け Excel VBA エキスパートになる (TechRepublic Academy)
  • Excel で条件付き書式を使用して小計行を強調表示する方法 (TechRepublic)
  • Excelマクロを高速化する2つの方法（TechRepublic）
  • Office Q&A: 古いオプションを再利用して関連メッセージをすばやく見つける (TechRepublic)
Tagged:

Related News