私とExcelとの付き合いもかれこれ15年近くになりますが、Excelを使って作業をすることが当たり前の日々となっています。私と同様にデスクワークをしている方なら、日頃からExcelを使い続けていると思いますが、Excelの使い方、特に関数は常に同じものを使い続けていませんか？
Excelは進化を続けており、Excel関数もアップデートされています。新しく登場した関数を利用すれば、Excelシートの管理がスマートになります。また、後ほど詳しく説明するスピルという機能を利用すると、Excelファイルのサイズがコンパクトになります。Excelファイルが肥大化して、開くのに時間がかかる、突然Excelが落ちるといった問題の解消にもつながります。今回は、普段使いのExcel関数を見直して、よりスマートに利用できるようになってみましょう。
なお、今回紹介するExcel関数は最新バージョンのExcelでなければ使えない関数もありますのでご注意ください。
目次を整理するExcelを作成してみます
せっかくなので編集プロダクションらしく、索引整理用のExcelファイルを用意しました（図 1参照）。索引整理では、主要な単語ひとつひとつに対して、登場するページ番号（専門用語ではノンブルと言います）をまとめていきます。単語によっては、複数のページ番号をピックアップするべきものもあります。
まず、索引がいくつあるかを確認するための、連番を用意しています。連番の作成では、1、2と入力して連番コピーしたり、工夫されている方はROW関数を使ったりしているかと思います（図 2左参照）。しかし、SEQUENCE関数を使うと、1セル入力するだけで連番作成が済みます（図 2右参照）。
スピル機能がとても便利です
1セルの入力だけで連番が作成できる理由は、スピルと呼ばれる機能が働いているからです。スピル機能とは、1つのセルに入れた数式の結果が、自動的に複数のセルに広がって表示される仕組みのことを言います。1つのセルに入力した数式の結果が、複数のセルに自動的に広がる様子が、まるで液体が容器からこぼれて広がるようなイメージなので、英語で「spill（スピル）」と表現されています。
スピルのメリットは次のとおりです。
- 手動でコピーする手間がなくなる
- 元の数式を変更するだけで、スピル範囲全体が自動で更新される
- 数式の重複が減り、ファイルサイズが軽くなる
注意点は次のとおりです。
- スピル先にデータがあるとエラーになる
- スピルされたセルは編集できない
- Excel 365やExcel 2019より前のバージョンでは使えない
本当にスピルを利用することでファイルサイズが軽くなるのでしょうか？ 他の関数もスピルを使ったものに置き換えていき、スピルの使用有無でどれだけファイルサイズが変わるか確認してみましょう。
さて、続いてはノンブルをカンマ区切りでひとつのセルにまとめてみます。スピルを使わない場合、TEXTJOIN関数を各行に挿入していましたが（図 3上参照）、BYROW関数、LAMBDA関数、FILTER関数を利用すれば1セルの数式入力で表現できます（図 3下参照）。
BYROW関数は、各行に対して指定したLAMBDA関数を実行します。LAMBDA関数は、自作の関数が定義できる関数です。ここでは、rowという名前で定義し、各行のノンブルが配列として格納されます。FILTER関数は、与えられた配列に対してフィルタ処理を行います。ここでは、rowの配列に対して、空白でないデータだけを抽出します。
要約すると、LAMBDA 関数で定義されたノンブルをカンマ区切りで結合する処理を、BYROW関数で各行に対して処理を繰り返している、ということになります。
範囲指定を「C2:.F1000」と表しています。これはTRIMRANGE関数を簡素にしたTRIM参照と呼ばれ、末尾の空白行を除外してくれます（図 4参照）。計算が必要なところだけ処理されるようになるので、処理時間が節約され、見た目もスマートになります。
なお、先頭の空白行を除外する場合は「C2.:F1000」となり、先頭と末尾両方の空白行を除外する場合は「C2.:.F1000」となります。
単語とノンブルまとめを3点リーダで結合して索引を作ります（普段は単語とノンブルをタブ送りで結合します）。ここでも、スピル機能を活用して1セルの数式入力で完結させています。数式内の「#（ハッシュ）」はスピル範囲演算子と呼ばれ、H列のスピルをすべて選択した場合このような表記になります（図 5参照）。
最後にルビを昇順にすれば、索引が完成します（図 6参照）。
因みに、ルビの出力はPHONETIC関数を利用していますが、これはスピルに対応していません。すべてがスピルで表現できるわけではないので、今後のExcelのアップデートに期待したいところです。
Excelのファイルサイズを比較します
さて、気になるExcelファイルのサイズを比較してみましょう。結果は図 7のとおりです。
スピルありの方がファイルサイズはわずかに小さくなりましたが、ほとんど違いはありません。データ数が少なかったため、ほとんどファイルサイズが変わらなかったようです。
計算量を増やして再度比較します
このままでは検証としてはイマイチですので、別のExcelを用意しました。スピルありとなしの1000×1000の掛け算表でファイルサイズを検証してみます。
スピルなしはA1を除くすべてのセルに計算式が入力されています（図 8参照）。一方、スピルありはA2セル、B1セル、B2セルの3か所だけに数式が入っており、残りはスピルで計算されています（図 9参照）。
さて、今度はどうなるでしょうか。結果は図 10のとおりです。
今度はファイルサイズに明確な差が現れました。スピルありはファイルサイズを30%以上圧縮することができています。
因みに、1000×1000を2000×2000にする場合、スピルなしは追加計算するすべてのセルに数式をコピーする必要がありますが、スピルありはA2セルの数式をSEQUENCE(2000)に変更するだけです。このように拡張性が高いこともスピルの魅力のひとつです。
最後に
Excel関数の新しい機能を利用することでExcelをさらに上手に使うことができます。ぜひExcel関数の新しい世界に足を踏み入れてみてください。
Excel関数に興味が出てきた、Excel関数の知識をアップデートしたいという方は、9月に出版を予定しているリブロワークス著のExcel関数の書籍はいかがでしょうか！
■Excel関数 やさしい教科書 ［Office 2024／2021 Microsoft 365対応］
https://www.sbcr.jp/product/4815636463/
今回紹介したExcel関数を含め、Excel関数を網羅的にまとめています。
みなさま、ぜひお手に取ってご覧いただけるとうれしいです。
