- Home »
- エクセル関数一覧表 »
- Excel関数の目次 »
- TRIMRANGE関数とトリム参照を使って空白の行または列を除外する
スポンサードリンク
(追記) (追記ここまで)
(追記) (追記ここまで)
更新: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関数の引数(現時点では英語表記になっています。)
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関数の説明図01]
- 行番号を指定しなくても、=TRIMRANGE(A:E) でもOKのようです。
[画像:TRIMRANGE関数の説明図02]
- =TRIMRANGE(A1:E10,1,1)
先頭の空白行(1行)、先頭の空白列(A列)がトリミングされます。
[画像:TRIMRANGE関数の説明図03]
- 上図のJ列など未入力セル(空白セル)は 0 が返されています。
例えば、A1セルが未入力のセルで、B1セルで =A1 と未入力セル(空白セル)を参照すると 0 が返されるのと同様です。
A1セルに ="" と0文字の文字列が入力されていると、B1セルで =A1 とすると "" が返されます。
ちなみに、A1セルに ="" が入力されていると、1行目とA列はトリミングされません。
- =TRIMRANGE(A1:E10,1,2)
先頭の空白行(1行)、末尾の空白列(E列)がトリミングされます。
[画像:TRIMRANGE関数の説明図04]
- =TRIMRANGE(A1:E10,1,3)
先頭の行(1行)、先頭と末尾の両方の列(A列とE列)がトリミングされます。
[画像:TRIMRANGE関数の説明図05]
- =TRIMRANGE(A1:E10,2,1)
末尾の空白行(1と8:10行)、先頭の空白列がトリミングされます。
[画像:TRIMRANGE関数の説明図06]
- =TRIMRANGE(A1:E10,2,2)
末尾の空白行、末尾の空白列がトリミングされます。
[画像:TRIMRANGE関数の説明図07]
- =TRIMRANGE(A1:E10,2,3)
末尾の空白行、先頭と末尾の空白列がトリミングされます。
[画像:TRIMRANGE関数の説明図08]
- =TRIMRANGE(A1:E10,3,1)
先頭と末尾の空白行、先頭の空白列がトリミングされます。
[画像:TRIMRANGE関数の説明図09]
- =TRIMRANGE(A1:E10,3,2)
先頭と末尾の行、末尾の列がトリミングされます。
[画像:TRIMRANGE関数の説明図10]
- =TRIMRANGE(A1:E10,3,3)
先頭と末尾の空白行、先頭と末尾の空白列がトリミングされます。
引数の「3」は既定値なので省略可です。
=TRIMRANGE(A1:E10)と同じになります。
[画像:TRIMRANGE関数の説明図11]
トリム参照 :Trim References (Trim Refs とも呼ばれる) 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)と同じです。
[画像:トリム参照の説明図01]
- =A1:.E10 とすると、範囲A1:E10の末尾部分をトリミングして、A1:D7が取り出せます。
コロンの後ろにドットがあります。
=TRIMRANGE(A1:E10,2,2)と同じです。
空白のセルには 0 が入力されます。
[画像:トリム参照の説明図02]
- =A1.:E10 とすると、範囲A1:E10の先頭部分をトリミングして、B2:E10が取り出せます。
コロンの前にドットがあります。
=TRIMRANGE(A1:E10,1,1)
空白のセルには 0 が入力されます。
[画像:トリム参照の説明図03]
- トリム参照を使ってデータリストの先頭行と最終行のデータを取り出してみます。
先頭行は =TAKE(A1.:.E100,1)
最終行は =TAKE(A1.:.E100,-1)
で取り出すことができます。
[画像:トリム参照の説明図04]
- データ範囲を広くしておけば、データを追加しても自動で最終行を取得できます。
[画像:トリム参照の説明図05]
- 行番号を入力してセル範囲を指定していますが、
A:Eと列全体を指定したらどうなるのでしょう?
=A.:.E でデータだけを取り出すことができました。
先頭行は =TAKE(A.:.E,1)
最終行は =TAKE(A.:.E,-1)
で取り出すことができました。
行番号の指定は不要ですね・・・。
[画像:トリム参照の説明図06]
- データリストの途中に空欄のセルがあると
=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
[画像:トリム参照の説明図07]
- ちょっと待て、=IF(TRIMRANGE(A.:.E)="","",TRIMRANGE(A.:.E))
というのは?
これでもOKのようです。
[画像:トリム参照の説明図08]
- ここでは、あらかじめD15セルまでの得点を判定できるようにトリム参照を使って数式を作成しています。
得点を順次入力していくと、入力されて部分の判定ができます。
従来なら 数式 =IF(D3="","",IF(D3>70,"合格","-")) をE3:E15セルに入力するか、
=IF(D3:D15="","",IF(D3:D15>70,"合格","-")) といった感じの数式で処理していたと思います。
かなり、簡略された数式になります。
- テスト結果の判定をE列で行います。
E3セルには =IF(D3:.D15>70,"合格","-") と入力しています。
[画像:トリム参照の説明図09]
- 得点を順次入力すると、判定が順次行われます。
[画像:トリム参照の説明図10]
トリム参照とXLOOKUP関数の使用例 topへ
- ここではセル範囲はこの程度までしか使わないだろうということで100行目に設定しています。
シートの最終行1048576を設定してもよいのですが、ほどほどの値にしているだけです。
入力規則のリストの範囲に =C4:.C100 とトリム参照で設定しています。
G4セルは =XLOOKUP(F4,C4:.C100,D4:.D100) とトリム参照を使って求めています。
トリム参照にすることでC8以下に新たなデータが追加されても数式の変更は不要です。
テーブルに変換せずにセル範囲を伸縮することができて便利になります。
VSTACK関数とDROPを使って、複数シートのデータを 1 シートにまとめる topへ
- VSTACK関数を使って、2つの表を1つにまとめると、2つの表の未入力セルに0が表示されます。
トリム参照を使ってこの0の表示をなくします。
- ちなみに、2つの表がテーブルに設定してあると、トリム参照は不要です。
=VSTACK(Tbl_6月,Tbl_7月) で同じことができます。
テーブルではタイトル行を無視して、データ部分だけが選択されるからです。
- シート6月、シート7月のデータをSheet1にまとめます。
- データの増減があることを見越して、数式を作成します。
=VSTACK(DROP('6月'!B.:.E,1),DROP('7月'!B.:.E,1))
としました。
B2セルの数式は、トリム参照を利用してB:E → B.:.Eとして上下の空白行をトリムしています。
タイトル行はDrop関数で1行目を除いています。
これらの処理をした配列をVSTACK関数でまとめています。
スポンサードリンク
(追記) (追記ここまで)
(追記) (追記ここまで)
Home »
エクセル関数一覧表 » TRIMRANGE関数とトリム参照を使って空白の行または列を除外する
PageViewCounter
Counter
Since2006/2/27
