1. Home » 
2. エクセル関数一覧表 » 
3. Excel関数の目次 » 
4. TRIMRANGE関数とトリム参照を使って空白の行または列を除外する

TRIMRANGE関数とトリム参照を使って空白の行または列を除外する:Excel関数

更新:2025/7/10;作成:2025/6/20

範囲または配列の端からスキャンし、空白の行または列を除外します
　トリム レンジ
=TRIMRANGE(range,[row_trim_mode],[col_trim_mode])

• TRIMRANGE関数が製品版のExcel for Microsoft 365 で使用することができるようになりました。(2025/6/20に気付きました)
  現在のチャネルのリリース ノート(Microsoft 365)でどのバージョンから使えるようになったのか調べても現時点では不明でした。
  ちなみに、こちらで使用しているExcelは 2505(ビルド 18827.20150)でした。
• Microsft365 Insiderでは2024/8/28頃から使用できたようです。
• 関数ライブラリでは検索/行列グループに区分されています。

TRIMRANGE関数の引数　　topへ

• TRIMRANGE関数の引数(現時点では英語表記になっています。)
  row_trim_modeとcol_trim_modeでトリミングする方向を指定できるようになっています。
  規定値では先頭と末尾の両方向をトリミングします。
  

  引数		意味
  range 範囲	必須	トリミングする範囲(または配列)
  row_trim_mode 行	省略可	トリミングする必要がある行を決定します 0：なし 1：先頭の空白行をトリミングします 2：末尾の空白行をトリミングします 3：先頭と末尾の両方の空白行をトリミングします (既定値)
  col_trim_mode 列	省略可	トリミングする必要がある列を決定します 0：なし 1：先頭の空白列をトリミングします 2：末尾の空白列をトリミングします 3：先頭と末尾の両方の空白列をトリミングします (既定値)

• =TRIMRANGE(A1:E10) とすると、
  範囲A1:E10の先頭と末尾の両方の空白行(1,8:10行)と
  先頭と末尾の両方の空白列(A列とE列)をトリミングして、B2:D7が取り出せます。
  下図の薄い水色の部分がトリミングされています。
  
• 行番号を指定しなくても、=TRIMRANGE(A:E) でもOKのようです。
  

引数と行や列のトリミングのようす　　topへ

• =TRIMRANGE(A1:E10,1,1)
  先頭の空白行(1行)、先頭の空白列(A列)がトリミングされます。
  
  • 上図のJ列など未入力セル(空白セル)は 0 が返されています。
    例えば、A1セルが未入力のセルで、B1セルで =A1 と未入力セル(空白セル)を参照すると 0 が返されるのと同様です。
    A1セルに　="" と0文字の文字列が入力されていると、Ｂ1セルで =A1 とすると ”” が返されます。
    ちなみに、A1セルに ＝””　が入力されていると、1行目とA列はトリミングされません。
• =TRIMRANGE(A1:E10,1,2)
  先頭の空白行(1行)、末尾の空白列(E列)がトリミングされます。
  
• =TRIMRANGE(A1:E10,1,3)
  先頭の行(1行)、先頭と末尾の両方の列(A列とE列)がトリミングされます。
  
• =TRIMRANGE(A1:E10,2,1)
  末尾の空白行(1と8:10行)、先頭の空白列がトリミングされます。
  
• =TRIMRANGE(A1:E10,2,2)
  末尾の空白行、末尾の空白列がトリミングされます。
  
• =TRIMRANGE(A1:E10,2,3)
  末尾の空白行、先頭と末尾の空白列がトリミングされます。
  
• =TRIMRANGE(A1:E10,3,1)
  先頭と末尾の空白行、先頭の空白列がトリミングされます。
  
• =TRIMRANGE(A1:E10,3,2)
  先頭と末尾の行、末尾の列がトリミングされます。
  
• =TRIMRANGE(A1:E10,3,3)
  先頭と末尾の空白行、先頭と末尾の空白列がトリミングされます。
  引数の「3」は既定値なので省略可です。
  =TRIMRANGE(A1:E10)と同じになります。
  

TRIMRANGE関数を使ってデータリストの先頭行と最終行のデータを取り出す　　topへ

• データリストの先頭の行と最終の行のデータを取り出してみます。
  先頭行は　=TAKE(TRIMRANGE(A1:E10),1)
  最終行は　=TAKE(TRIMRANGE(A1:E10),-1)
  で取り出すことができます。
  
• データ範囲を広くしておけば、データを追加しても自動で最終行を取得できます。
  

トリム参照：Trim References (Trim Refs とも呼ばれる)　　topへ

トリム参照の型　　topへ

• Trim Ref 型を使用して、範囲のコロン":"の後ろや前にドット "." を入力して、トリミングする方向を指定できます。
  TRIMRANGE関数と同じ機能をより簡潔に実現できます。
  

  型	例	説明	同等の TRIMRANGE
  すべてトリミング (.:.)	A1.:.E10	先頭と末尾の空白をトリミングする	TRIMRANGE(A1:E10,3,3)
  末尾のトリミング (:.)	A1:.E10	末尾の空白をトリミングする	TRIMRANGE(A1:E10,2,2)
  先頭のトリミング (.:)	A1.:E10	先頭の空白をトリミングする	TRIMRANGE(A1:E10,1,1)

• =A1.:.E10 とすると、範囲A1:E10の先頭部分と末尾部分をトリミングして、B2:D7が取り出せます。
  コロンの前後にドットがあります。
  =TRIMRANGE(A1:E10,3,3)と同じです。
  
• =A1:.E10 とすると、範囲A1:E10の末尾部分をトリミングして、A1:D7が取り出せます。
  コロンの後ろにドットがあります。
  =TRIMRANGE(A1:E10,2,2)と同じです。
  空白のセルには 0 が入力されます。
  
• =A1.:E10 とすると、範囲A1:E10の先頭部分をトリミングして、B2:E10が取り出せます。
  コロンの前にドットがあります。
  =TRIMRANGE(A1:E10,1,1)
  空白のセルには 0 が入力されます。
  

トリム参照を使ってデータリストの先頭行と最終行のデータを取り出す　　topへ

• トリム参照を使ってデータリストの先頭行と最終行のデータを取り出してみます。
  先頭行は　=TAKE(A1.:.E100,1)
  最終行は　=TAKE(A1.:.E100,-1)
  で取り出すことができます。
  
• データ範囲を広くしておけば、データを追加しても自動で最終行を取得できます。
  
• 行番号を入力してセル範囲を指定していますが、
  A:Eと列全体を指定したらどうなるのでしょう？
  ＝A.:.E　でデータだけを取り出すことができました。
  先頭行は　=TAKE(A.:.E,1)
  最終行は　=TAKE(A.:.E,-1)
  で取り出すことができました。
  行番号の指定は不要ですね・・・。
  

取り出したセルの 0値を表示しない　　topへ

• データリストの途中に空欄のセルがあると
  =A.:.E　とすると、8:9行目に 0 が表示されます。
  この途中の「0」を表示しない方法として、LAMBDA関数を利用する方法があるようです。
  =MAP(A.:.E,LAMBDA(a,IF(a="","",a)))　としています。
  配列 A.:.E　のセル値が ”” の時は ”” として、値があるときはその値に置き換えています。
  
  • ↓のサイトのページを参考にしました。
    スピル系の関数で空欄を0にしない
    http://officetanaka.net/excel/function/tips/tips124.htm
  
• ちょっと待て、=IF(TRIMRANGE(A.:.E)="","",TRIMRANGE(A.:.E)) というのは？
  これでもOKのようです。
  

トリム参照の使用例　　　topへ

• ここでは、あらかじめD15セルまでの得点を判定できるようにトリム参照を使って数式を作成しています。
  得点を順次入力していくと、入力されて部分の判定ができます。
  従来なら　数式　=IF(D3="","",IF(D3>70,"合格","-"))　をE3:E15セルに入力するか、
  =IF(D3:D15="","",IF(D3:D15>70,"合格","-"))　といった感じの数式で処理していたと思います。
  かなり、簡略された数式になります。

1. テスト結果の判定をE列で行います。
   E3セルには　=IF(D3:.D15>70,"合格","-")　と入力しています。
   
2. 得点を順次入力すると、判定が順次行われます。
   

トリム参照とXLOOKUP関数の使用例　　　topへ

• ここではセル範囲はこの程度までしか使わないだろうということで100行目に設定しています。
  シートの最終行1048576を設定してもよいのですが、ほどほどの値にしているだけです。
  入力規則のリストの範囲に　=C4:.C100　とトリム参照で設定しています。
  G4セルは　=XLOOKUP(F4,C4:.C100,D4:.D100)　とトリム参照を使って求めています。
  トリム参照にすることでC8以下に新たなデータが追加されても数式の変更は不要です。
  テーブルに変換せずにセル範囲を伸縮することができて便利になります。
  • XLOOKUP関数の使い方は　XLOOKUP関数で範囲や配列を検索するをご覧ください。
  • こちらでも使用例があります→入力規則のリスト範囲を自動で増減する(TrimRange,Offset,テーブル)
  

VSTACK関数とDROPを使って、複数シートのデータを 1 シートにまとめる　　　topへ

• VSTACK関数を使って、2つの表を1つにまとめると、2つの表の未入力セルに0が表示されます。
  トリム参照を使ってこの0の表示をなくします。
• ちなみに、2つの表がテーブルに設定してあると、トリム参照は不要です。
  =VSTACK(Tbl_6月,Tbl_7月)　で同じことができます。
  テーブルではタイトル行を無視して、データ部分だけが選択されるからです。

1. シート6月、シート7月のデータをSheet1にまとめます。
   
   
   
2. データの増減があることを見越して、数式を作成します。
   =VSTACK(DROP('6月'!B.:.E,1),DROP('7月'!B.:.E,1))
   としました。
   B2セルの数式は、トリム参照を利用してB:E →　B.:.Eとして上下の空白行をトリムしています。
   タイトル行はDrop関数で1行目を除いています。
   これらの処理をした配列をVSTACK関数でまとめています。
   

スポンサードリンク

PageViewCounter

Since2006/2/27