【Excel】TRIMRANGE関数とトリム参照で空白行を除いた範囲を指定するコツ

この記事は約5分で読めます。

今回は、ExcelのTRIMRANGE関数とトリム参照を使って、データの前後にある空白行や空白列を除いた範囲を指定するコツを紹介します。

データが増えるたびに数式の範囲を直す手間や、列全体を指定したときに空白セルまで計算対象になってしまう悩みは、多くの方が経験しているのではないでしょうか。TRIMRANGE関数とトリム参照は、こうした場面で役立つ機能です。

この記事は、Excel for Microsoft 365（Windows版）を前提にしています。Microsoftのサポート情報では、TRIMRANGE関数の対象製品はExcel for Microsoft 365とされており、Excelブログでは、Windows版の最新チャネル（Current Channel）と月次エンタープライズチャネルの利用者に提供されたと案内されています。買い切り版のExcel（Excel 2021、Excel 2024など）や、Mac版・Web版では、利用できるかどうかが異なる場合があります。手元のExcelで関数名を入力して候補に表示されるかを確認してから使い始めると安心です。

TRIMRANGE関数とは

TRIMRANGE関数は、指定した範囲や配列の外側の端から空白の行や列を除外する関数です。範囲の端から内側に向かって調べていき、空白ではないセルが見つかったところまでの空白行・空白列を取り除いた範囲を返します。

たとえば、A1:A1000を指定していても、実際にデータが入っているのがA1:A50だけであれば、末尾の空白部分を除いたA1:A50相当の範囲として扱われます。

構文と引数

構文は次のとおりです。

=TRIMRANGE(範囲,[trim_rows],[trim_cols])

  • 範囲（必須）：トリミングする範囲または配列
  • trim_rows（省略可）：行をどう除外するかを指定します
  • trim_cols（省略可）：列をどう除外するかを指定します

trim_rowsとtrim_colsには、次の数値を指定します。

  • 0：除外しない
  • 1：先頭の空白だけ除外する
  • 2：末尾の空白だけ除外する
  • 3：先頭と末尾の両方の空白を除外する（既定値）

引数を省略すると「3」として扱われるため、=TRIMRANGE(A1:E1000)と書けば、上下左右の外側の空白がまとめて除かれます。

途中の空白は除外されない点に注意

TRIMRANGE関数が除外するのは、あくまで範囲の外側の端にある空白です。データの途中にある空白行や空白列はそのまま残ります。途中の空白行も取り除きたい場合は、FILTER関数など別の方法と組み合わせて考える必要があります。

トリム参照で数式を短く書く

TRIMRANGE関数と同じ働きを、より短く書ける仕組みがトリム参照です。範囲を表すコロン「:」を、ピリオドを付けた記号に置き換えるだけで使えます。

  • A1.:.E10：先頭と末尾の空白を除外（TRIMRANGE(A1:E10,3,3)と同じ）
  • A1:.E10：末尾の空白だけを除外（TRIMRANGE(A1:E10,2,2)と同じ）
  • A1.:E10：先頭の空白だけを除外（TRIMRANGE(A1:E10,1,1)と同じ）

覚え方としては、ピリオドを付けた側の空白が除かれると考えるとわかりやすいです。コロンの後ろにピリオドがあれば末尾側、前にあれば先頭側が対象になります。

列全体・行全体の参照にも使える

トリム参照は、A:.Aのように列全体や行全体の参照にも使えます。「A:.A」と書けば、A列のうちデータが入っている最後のセルまでを範囲として扱えます。列全体を指定する書き方の手軽さを保ちつつ、末尾の空白セルを計算対象から外せるのが便利な点です。

実務で役立つ使い方のヒント

データが増えても範囲を直さずに済む

毎日行が追加される売上一覧などで、合計を求める数式を「=SUM(C2:C100)」のように書くと、101行目以降にデータが増えたときに範囲を直す必要があります。

あらかじめ余裕を持たせて「=SUM(C2:.C5000)」と書いておけば、5000行目までの範囲のうち、データがある部分までを対象にできます。SUM関数の場合は空白を含めても結果は変わりませんが、次のような関数では違いが出ます。

スピルする関数と組み合わせる

UNIQUE関数やSORT関数のように、結果が複数のセルに広がる（スピルする）関数に余裕を持たせた範囲を渡すと、空白セルが「0」や空の結果として一緒に表示されてしまうことがあります。

たとえば、A2:A1000から重複を除いた一覧を作るときに「=UNIQUE(A2:.A1000)」と書くと、末尾の空白部分が除かれるため、一覧の最後に余計な「0」が表示される状態を避けやすくなります。

同じように、「=A2:.A1000*1.1」のような計算をスピルさせる場合も、データがある行までで結果が止まるため、表の下に不要な計算結果が並びにくくなります。

テーブルとの使い分け

データの増減に強い範囲指定といえば、Excelのテーブル機能もよく使われます。テーブルは書式や構造化参照もまとめて管理できるため、本格的な一覧表にはテーブルが向いています。

一方で、既存のシートの構成を変えずに数式だけを手軽に直したい場合や、テーブルに変換しにくい表を扱う場合には、トリム参照が使いやすい選択肢になります。目的に合わせて使い分けるとよいでしょう。

使うときの注意点とコツ

  1. 対応環境を確認する：ブックを共有する相手が対応していないExcelを使っている場合、数式が正しく計算されない可能性があります。共有するブックで使う場合は、相手の環境も確認しておきましょう。
  2. 途中の空白は残る：前述のとおり、除かれるのは外側の空白だけです。途中に空白行がある表では、結果に空白が含まれることを前提に数式を組み立てます。
  3. 見出し行の扱いに気を付ける：A:.Aのように列全体を指定すると、見出しの文字列も範囲に含まれます。集計関数では問題にならないことが多いものの、UNIQUE関数などでは見出しも結果に含まれるため、A2:.A10000のように開始セルを指定するのが無難です。
  4. 結果を目で確かめる：最初に使うときは、ROWS関数で「=ROWS(A2:.A1000)」のように行数を求め、想定どおりの範囲になっているかを確認しておくと安心です。

まとめ

TRIMRANGE関数とトリム参照を使うと、データの前後にある空白行や空白列を除いた範囲を数式で扱えるようになります。

  • TRIMRANGE関数は、範囲の外側の端にある空白の行・列を除外する
  • 引数を省略すると、先頭と末尾の両方の空白が除かれる
  • トリム参照では、コロンにピリオドを付けるだけで同じ働きを短く書ける
  • A:.Aのように列全体の参照にも使え、データが増えても範囲を直す手間を減らせる
  • UNIQUE関数などスピルする関数と組み合わせると、余計な空白や0の表示を防ぎやすい
  • 対応しているのはExcel for Microsoft 365で、環境によっては使えない場合がある

まずは普段使っている集計表の数式を一つ選び、コロンをトリム参照に置き換えて、結果の違いを確かめてみてください。範囲の管理が少しずつ楽になっていくはずです。