エクセルの新たな参照方法「トリム参照」とは？ スピルの利便性を高める便利な記法

トリム参照はスピルと組み合わせた場合に効く

　今回は、新しい参照方法である「トリム参照」を紹介します。数式中のセル参照の無駄を省くための記述方式です。セル参照時に範囲を表す「:」（コロン）の前、または後ろに「.」（ピリオド）を追加して、「B2:.B20」のように記述します。

　以下は、氏名から姓だけを取り出している例です。TEXTBEFORE関数を利用していますが、セル参照に注目すると「:.」となっています。

セルF2には「=TEXTBEFORE(B2:.B20," ")」と入力してある。セルB2～B20の半角スペースより前を取り出すという意味

　現在は11行目までしかデータがありませんが、追記を想定して、セルB2～B20と指定しているわけです。ここで違和感を覚えた人は、Excel慣れしていますね？　従来のように「=TEXTBEFORE(B2:B20," ")」と記述すると以下のようになります。

セルF2に「=TEXTBEFORE(B2:B20," ")」と入力した結果。セルF12以降には「#N/A」エラーが表示されている

　そういうものと割り切っている人もいるかもしれませんが、セルF12以降の処理は実際には不要です。数式によっては、列全体を指定することもあり、その場合はシートの末尾まで不要な処理が続いてしまいます。このような問題を回避できるのが「トリム参照」です。本稿執筆時点で、Microsoft 365のExcelで利用可能です。

トリム参照の動作を確認する

　トリム参照の記述方法は「:.」「.:」「.:.」の3パターンあります。それぞれ、下と左、上と右、上下左右の空白を取り除きます。簡単な例で動作を見てみましょう。

　以下は、セルB2～F5に入力したデータを含むセル範囲を参照するように、セルH1に「=A1:G6」と入力してあります。「:.」を利用すると下と右の「0」が取り除かれます。「.:」は上と左、「.:.」は上下左右の「0」が取り除かれることを確認できます。

セルH1に「=A1:G6」と入力した状態。空白セルを参照している部分に「0」が表示されている

セルH1の数式を「=A1:.G6」と修正した。下と右の「0」が取り除かれている

セルH1の数式を「=A1.:G6」と修正した。上と左の「0」が取り除かれている

セルH1の数式を「=A1.:.G6」と修正した。上下左右の「0」が取り除かれている

　見慣れない記号なので戸惑いますが、「.」1つで余計な空白を取り除けるのは便利だと思いませんか？　とにかくすべての空白を取り除きたい人は「.:.」と決め打ちでもいいかもしれません。

結果がスピルで表示される数式に有効

　冒頭で紹介したように、トリム参照はスピルで結果が表示される数式に有効です。実務では、UNIQUE関数を使ったときの空白や、XLOOKUP関数で空白を無視したい時に便利です。関数を組み合わせる必要もなく、「.」を追加するだけです。

UNIQUE関数で表示される余計な「0」を非表示にできる

スピルでXLOOKUP関数の結果を求めたいときにも便利

　なお、取り除く空白を「上と右」「下と左」「上と下」のように細かく指定できる「TRIMRANGE関数」もありますが、まずは「:.」「.:」「.:.」の記述方法を覚えておくだけで、数式の扱いがより効率的になりますよ。