スピルを使ってみよう
(この記事は会計系 Advent Calendar 2023の14日目の記事です。)
会計系アドベントカレンダーの14日目を担当するたきぞうです。
会計系Advent Calendarの企画として何を書いてもよいというお話でしたので、Excelの新しめの機能であるスピルについて書き散らしてみたいと思います。 完全な趣味です。ゴリゴリの実務家のため学術的な内容は一切含まれておりませんが、読まれた方の何らかのインプットになれば幸いです。
はじめに
会計界隈において、Excelの操作は基本中の基本です。データをどれだけ効率的にミスなく捌けるかは非常に重要なスキルの一つです。Excelスキルがあればいいというわけではありませんが、Excelを使えないとお話にならないのもまた事実です。
そんなExcelですが、少し前から「スピル」という機能が実装されました。これは一つのセルに数式を書くと、計算結果が隣接するセルにこぼれるという機能です。聞いただけでは大したことがないように思えますが、スピル以前と以後ではExcelの使い方が劇的に変わりました。スピルの機能を最大限活用することで、これまでにはできなかった加工や集計が、メンテナンスフリーで実現可能になってしまったのです。
このスピルについて基本的な使い方はネット上で多くの解説記事があるものの、あまりにも基本的な内容であったり、X(旧Twitter)では曲芸まがいの投稿が多く、なかなか参考になる情報が少ないように思います。そこで本記事では、スピル機能の基本的な解説と、会計界隈の実務で使えそうなスピルの活用例についての紹介をしたいと思います。
※注意事項
本記事は一般的な「Excelができます」のレベルを若干超えた内容になっております。SUMIFSが分からない方はこのページを閉じて基本的な解説サイトを読むことをお勧めします。
本記事で使用しているサンプルデータはChatGPTで雑に生成したものです。細かな点で不整合があるかもしれませんが、本筋には影響しないはずですのでツッコミ無用でお願いいたします。
スピルの基本的な解説
これまでのExcelでは、セルの参照先はセルのみでした 。しかし、スピルが実装されて以降は一つのセルで複数の範囲を参照することが可能になっています。 1
範囲の参照
D2に=B2:B10
というセル範囲を指定すると下記のようにD3
からD10
に数字がこぼれます。これがスピルです。実際に数式が入っているのはD2
だけで、D3
からD10
のセルには何も入力されていません。このスピルしている箇所(D3
~D10
)はゴーストと呼ばれるもので、D2
の数式の結果がスピルしています。
では、この数字がこぼれている場所に値を入れるとどうなるでしょうか?その結果がこちらです。本来ならば数値をスピルさせたいのに、数字が入力されてしまっているのでエラーが表示されてしまいます。スピルを使う場合、結果によってはスピル範囲が縦または横方向にかってに伸びるので、数式がスピルする範囲を想定してシートを作っていく必要があります。
範囲に100を乗じてみる
では、D2
の数式=B2:B10
に100を乗じてみましょう。参照範囲の各セルに対して100を乗じた結果が出力されます。
スピル範囲演算子の使い方
#
はスピル範囲演算子という名前の記号です。数式が入力されているセルに#
を付けることで、数式が入力されたセルとゴーストの範囲をまとめて参照することができます。またセルの数式を入力している際に、スピルしているD2
からD10
の範囲をマウスまたはキーボードで選択しようとすると、D2:D10
とはならず勝手にD2#
という表記になります。
当然ながら、この=D2#
に100を乗じると、もとの数式範囲であるB2:B10
に100を乗じた結果がスピルします。
このスピル範囲演算子は、後で非常に重要になりますのでしっかり覚えておいてください。
よく使う便利な関数の解説
会計・経理実務でスピルを使うにあたって重要な関数の説明に移ります。これらの関数は基本的にスピルすることが前提です。
- UNIQUE関数
- SORT関数、SORTBY関数
- FILTER関数
XLOOKUPが便利だなんだともてはやされていますが、XLOOKUPは既存の関数でも代用可能なレベルであるのに対して、上記の三つの関数はそもそも代用ができなかったり、非常に面倒な手順を踏むことで再現可能だったりするものです。これらの専用の新しい関数が用意されたことで、Excelの使い勝手は大きく向上しています。
UNIQUE関数
UNIQUE関数の基本
指定した範囲から重複を除いたデータを返してくれます。
=UNIQUE(G2:G23)
上記の数式を入力することで、参照先から重複を取り除いた結果をスピルしてくれます。この関数が実行されるまではデータタブで「重複の削除」という機能でも同様のことはできましたが、元のデータを削除してしまう上に一部でバグが発生するという話もありました。しかし、このUNIQUE関数は元のデータに影響を与えずに重複のない一覧を新しく出力できます。
UNIQUEは基本的に単独では使いません。他の関数と組み合わせて用いることが大半かと思います。実務上でよく使うのはやはりSUMIFSでしょうか。SUMIFSの引数としてUNIQUEの結果であるL2#
を指定することで、UNIQUEの結果に対応して伸び縮みするSUMIFSの表が完成します。
L2 = UNIQUE(G2:G23) M2 = SUMIFS(J2:J23,L2#)
さて、勘がよい方はすでにお気づきでしょうがこの数式はまだまだ完璧ではありません。きっとこんなツッコミがあることでしょう。
- 毎月データが増えるんだから行数が変わるけど、いちいち関数の範囲を変えるの?
そこで、テーブルの機能を使います。テーブルは構造化参照という仕組みを使う事ができ、スピルの機能と大変相性がよいです。まずは対象範囲をテーブルに変換します。範囲指定してCtrl+Tでテーブル化を実行しましょう。
先ほどの数式はセル範囲を指定しましたが、今回は対象がテーブルのデータですので列名を指定します。最上段の行からCtrl+Shift+↓で最下段までデータを選択すると構造化参照としてテーブルの列全体を指定できます。よって、テーブルを使う場合にはUNIQUEやSUMIFSの引数にこのような形になります。
L2 = UNIQUE(テーブル1[部門名]) M2 = SUMIFS(テーブル1[金額],テーブル1[部門名],L2#)
もとのデータに新しく経営企画部門の外注費を追加したところ、L9
とM9
に新しいデータが表示されました。テーブルは新しくデータが入力されると、その範囲をテーブルの一部として認識してくれます。そのため、スピル系の関数と組み合わせると、データの内容に応じて自動で変化してくれる表が完成します。2
UNIQUE関数の応用
ここまでは関数の解説サイトでよく出てくる内容です。ただ、現実の実務に対応しようとすると、これだけでは対応しきれませんし、わざわざ記事を書くまでもありません。せっかくなのでもう一歩踏み込んでみましょう。
実はUNIQUE関数は複数の列に対しても重複のない組み合わせを作成することができます。ただし、他の関数と組み合わせる必要があります。いくつか方法はありますが、今回はHSTACK関数を使って複数列での重複のない列を作成してみます。
HSTACK関数も最近追加された新しい関数で、複数の範囲を簡単に横につなげてスピルさせることができる関数です。テーブル次の数式をL2
に入力することでとL2
からM23
までの範囲に数値がスピルしてくれます。
L2 = HSTACK(テーブル1[部門名],テーブル1[科目名称])
次に、これをUNIQUEでくくってしまいます。L2
のセルを次のように書き換えます。
L2 = UNIQUE(HSTACK(テーブル1[部門名],テーブル1[科目名称]))
そうすると、部門と科目の組み合わせで重複のないリストが出力されます。
では、これを最初のUNIQUEの場合と同様にSUMIFSの集計の引数として使ってみたいのですが、少し面倒な事態が発生していることにお気づきでしょうか?部門はL列に、科目はM列でそれぞれスピルしていますが、もともとこれらのゴーストはL2
に入力された計算式の結果がこぼれたものです。
元データが変化しない限りは、SUMIFSの引数にはL2:L9
とM2:M9
という入力で問題ないのですが、データ追加され部門や科目が増えた場合には、現在スピルしている範囲のL2
~L9
やM2
~M9
はさらに下に伸びてしまいまいます。その結果、SUMIFSの集計範囲が適切ではなくなり集計漏れが発生してしまいます。つまり、SUMIFSの引数を指定するときには単純にL2:L9
、M2:M9
という指定ではダメなのです。
そこで、 今回のケースでは複数の列範囲から特定の列を取得するためにCHOOSECOLS関数を使います。正確な構文は下記のとおりです。
=CHOOSECOLS(array,col_num1,[col_num2],…)
今回は複雑な使い方はしませんので、単純に一つ目の引数に対象の配列、二つ目の引数に取り出したい列番号を入力します。結果はこんな感じです。
このCHOOSECOLS関数を使って、複数列の自動で伸縮するSUMIFSの表を作ると、このような形になります。
L2 = UNIQUE(HSTACK(テーブル1[部門名],テーブル1[科目名称])) N2 = SUMIFS(テーブル1[金額],テーブル1[部門名],CHOOSECOLS(L2#,1),テーブル1[科目名称],CHOOSECOLS(L2#,2))
補足 ピボットテーブルとの使い分け
以上のような工夫により、UNIQUEとSUMIFSの組み合わせで対応力の高い集計表が作成できます。ただ、この程度ならピボットテーブルで十分ではないか?という意見もあるかもしれません。個人的な意見になりますが、次の二点のメリットを生かせるならば、今回のような手法を使ってスピルによる動的配列で集計表を作った方がよいのではないかと考えています。
ポイント | 内容 |
---|---|
データ更新が不要 | 計算結果が即座に反映される。 (ピボットテーブルのデータ更新漏れミスを防げます。人間はミスをする生き物です…) |
FILTER関数との組み合わせが強力 | 集計表がスピルを用いた動的配列であれば、FILTER関数と組み合わせることで簡単に出力内容を制御できる。 |
逆に、データの内容を確認するなど探索的な作業を行う場合は、マウス操作で縦横の軸や粒度を自在に操作できるピボットテーブルを用いた方がよさそうです。
なお複数条件の集計は、GROUPBY関数という新しい専用の関数が将来的に実装されるようです。この記事を書いている2023年12月時点ではインサイダープログラムでの試行提供となっており、あと1年ほどすれば通常版に実装されるかと思います。この関数さえあれば、UNIQUE関数で出力した複数列の配列を個別にCHOOSECOLS関数で指定するなんていう面倒なことは不要になりそうです。
SORT関数、SORTBY関数
つづいてSORT関数とSORTBY関数です。これらの関数は指定した範囲について並び替えの軸となる列の昇順、または降順で並び替えた結果を出力します。これらの二つの関数は指定した配列を並び替えるという根本的な機能は同じですが、並び替えの基準とする列の指定の方法が異なります。どちらが便利かは場合によるので、状況によって使い分ける形となります。
SORT関数の基本
SORT関数の構文は次のとおりです。
=SORT(配列,[並び替えインデックス],[並び替え順序],[並び替え基準])
SORT関数は指定範囲をインデックス番号(何列目か)を使ってソートする基準を設定します。 (四つ目の引数は列と行を反転させて並び替えるために使用しますが、省略可能であり更に今回の記事では取り扱わないため以降は引数の入力と説明は割愛させていただきます。)
では、実際の関数の動きを確認してみましょう。
下記のケースでは、対象の配列(B2:D10
)について、2
列目の昇順(1
)で並び替えています。
F2 = SORT(B2:D10,2,1)
並び替えの指定が一列である場合には、シンプルにSORT関数を使うと簡単かと思います。
SORTBY関数の基本
SORTBY関数の基本の書き方は以下の通りです。
=SORTBY(配列,基準配列1,[並び替え順序1],...)
複数の並び替えの基準を設定したい場合には、基本的にはSORTBY関数を使います。SORT関数が並び替えの列をインデックス番号(何列目か)で指定したのに対して、SORTBY関数は並び替えの配列をダイレクトに指定します。
実際のデータでSORTBY関数を使ってみましょう。下記のケースの場合、B2:D10
という範囲について、まずB2:B10
を基準に昇順(1
)でソート、次にD2:D10
を基準に降順(-1
)でソートしています。
F2=SORTBY(B2:D10,B2:B10,1,D2:D10,-1)
SORT系関数の具体的な使い方
SORT系の関数も単独で用いることは少なく、他の関数との組み合わせで効果を発揮します。
何も考えずにUNIQUEだけを用いて集計表を作成した場合、こんな感じの表ができてしまうことがあります。経理や会計界隈の人間はおそらくこの表を見て強烈な違和感を覚えるのではないでしょうか ?販管費が最上段に来る表なんて、私には耐え難い代物です。
そこで順番を入れ替えるSORT系関数の出番です。絶対とはいいませんが、勘定科目のコードはPLの上から順にソートがかかるように付番されているはずかと思います。そこで最初に科目コード順にソートした配列をUNIQUEして出力し、SUMIFSしたものがこちらです。
L2 = UNIQUE(SORTBY(テーブル1[科目名称],テーブル1[部門コード]))
人間が目視で確認する表は、一定の基準や暗黙の了解で並んでいた方が視認性があがります。関数を使ってサクッと並び替えてしまいましょう。
FILTER関数
会計実務におけるスピル活用の本丸といっても過言ではない、それがFILTER関数です。私が最近作ったExcelのファイルですが、おそらくFILTER関数の出現率が90%を超えています。FILTER関数が無かった時代には絶対に戻りたくないと思えるほどに、この関数に依存しきっております。
Excelには抽出や並び替えのための機能としてオートフィルタが存在しておりますが、オートフィルタは設定できる条件が非常に限られています。もっと細かい設定で自由自在にデータを抽出したい、確認したいというときにはFILTER関数が効果を発揮します。3
FILTER関数の基本
FILTER関数の基本の構文は次のようになっています。
=FILTER(配列,条件,[空の場合])
作りとしては、対象となる「配列」に対して、「条件」で絞り込みの要件を設定する仕組みです。最後の引数は条件に合致する対象がなかった場合に表示するメッセージの指定です。あった方がより親切ですが、根本的な動きに関わるものではないので今回の記事では解説や記載は省略します。
では基本的な数式の書き方とその結果を見てみましょう。下のSSは大量のデータから科目名称が売上高であるもの抽出した表です。
L2 = FILTER(テーブル1,テーブル1[科目名称]= "売上高")
売上高で絞り込みをおこなうために、FILTER関数の二つ目の引数に、 テーブル1[科目名称]="売上高"
という入力を行っています。簡単な絞り込みを行うだけならこれだけでよいのですが、FILTER関数の挙動に対して理解を深めるために、テーブル1[科目名称]="売上高"
について深掘りをしたいと思います。
テーブル1[科目名称]="売上高"
をもとのテーブルの横に貼り付けた結果が下のSSになります。
テーブル1[科目名称]="売上高"
は、テーブル1[科目名称]のすべての行に対してそれぞれの値が"売上高"と等しいかを判定し、その結果をTRUE
とFALSE
で返す働きをしているのが分かります。これがFILTER関数の二つ目の引数の正確な内容です。…ここまで説明を聞けばもうお分かりかもしれませんが、FILTER関数の挙動は非常にシンプルです。
FILTER関数は二つ目の引数の配列がTRUE
の行は表示、FALSE
の行は非表示にする動きをします。
そのため、FILTER関数の外のセルのTRUE
とFALSE
が表示されている範囲を参照しても正しく抽出を行うことができます。
L28 = FILTER(テーブル1,L3#)
なお、TRUE
とFALSE
の行数については注意が必要です。 FILTER関数の最初に指定した配列の行数と、条件の絞り込みのためのTRUE
とFALSE
の行数が一致しない場合にはエラーになります。
下記の例の場合、フィルターを行いたいテーブルが22行なのに対して、判定用のTRUE
/FALSE
の配列の行数が21行であるため、エラーが出ています。
~また、TRUE
とFALSE
はそれぞれ1
と0
で代用することもできます。1
であれば表示、0
であれば非表示という制御が可能ですので、覚えておくと大変便利です。
投稿後にお二人から指摘を受けまして、内容に誤りがありましたので修正いたします。
FALSE
は0
、TRUE
は0
以外の数字で代用が可能です 。
一つの条件で抽出する場合には特に使うことはないでしょうが、複数条件での抽出を行う場合にはこの前提が重要になります。…試したことはありませんでしたが、FALSE
についてはマイナス値でもよいみたいですね。
FILTER関数の応用_複数条件によるフィルタリング
ここからはFILTER関数の真髄である、複数条件によるフィルタリングを見ていきたいと思います。先ほど、FILTER関数は二つ目の引数の配列がTRUE
の行を表示し、FALSE
の行は非表示になるという説明をしました。複数条件であってもこのルールは変わりません。 どのような複雑な条件であっても、フィルタリングを行いたい対象の配列(≒元の表)と同じ行数のTRUE
/FALSE
の配列、もしくは「TRUE
=0
ではない数値、FALSE
=0
」の配列を準備すればよいのです。
複雑な条件の結果を出力するための配列は、複数の配列の乗算または加算で作成します。4端的に表現すると、下記のようになります。
条件 | 記号 | 数式 | 意味 |
---|---|---|---|
AND | * |
条件式1 * 条件式2 | 条件のいずれもTRUE ならばTRUE 、そうでなければ FALSE |
OR | + |
条件式1 + 条件式2 | 条件のいずれかがTRUE ならばTRUE 、そうでなければ FALSE |
さきほど、 数値が0
のものはFALSE
、0
以外のものはTRUE
で代用できる という説明を行いました。
ここでもう一つ重要な前提条件を共有します。普段意識することはないかもしれませんが、ExcelにおいてFALSE
は0
、TRUE
は1
を意味しています。
FALSE
は0
、TRUE
は1
という前提のもとで、AND(乗算)とOR(加算)は下記のように整理することが可能です。
区分 | 条件1 | 条件2 | 計算 | 結果 | FILTER関数での挙動 |
---|---|---|---|---|---|
AND | 0 |
0 |
0 *0 |
0 |
非表示 |
AND | 0 |
1 |
0 *1 |
0 |
非表示 |
AND | 1 |
0 |
1 *0 |
0 |
非表示 |
AND | 1 |
1 |
1 *1 |
1 |
表示 |
OR | 0 |
0 |
0 +0 |
0 |
非表示 |
OR | 0 |
1 |
0 +1 |
1 |
表示 |
OR | 1 |
0 |
1 +0 |
1 |
表示 |
OR | 1 |
1 |
1 +1 |
2 |
表示 |
上記の図を見てもピンとこない方も多いかもしれませんので、実際のデータを見てFILTER関数の挙動を確認してみましょう。
まずはAND条件で科目が売上高かつ製品名がマウスのものをFILTER関数で抽出してみます。 判定の途中が分かりやすいように条件をセルで表示させていきます。
セル | 判定内容 | 数式 |
---|---|---|
L3 |
科目が売上高と等しい | テーブル1[科目名称]="売上高" |
M3 |
製品名がマウスと等しい | テーブル1[製品名]="マウス" |
N3 |
L3# とM3# のAND条件 |
L3# * M3# |
L3
からは科目が売上高と等しいか否かの判定結果、M3からは製品名がマウスと等しいかの判定結果がスピルします。これらを乗算した結果、N3
から科目が売上高かつ製品名がマウスであることを示す1
とそうではないことを示す0
の判定結果がスピルします。
では、FILTER関数で二つ目の引数にN3#
を指定して結果を確認してみます。…よさそうですね。
A39 = FILTER(テーブル1,N3#)
これをセル参照ではなくFILTER関数の数式に格納する際には、それぞれの条件を()
で括り、*
でつなげます。
A39 = FILTER(テーブル1,(テーブル1[科目名称]= "売上高")*(テーブル1[製品名]= "マウス"))
オートフィルタは条件を二つまでしか設定できませんでしたが、FILTER関数は下記のように三つ以上でも条件を設定することができます。科目が売上高かつ、製品名がマウスかつ、金額が150,000円以上と設定したい場合には次のようになります。
A39 = FILTER(テーブル1,(テーブル1[科目名称]= "売上高")*(テーブル1[製品名]= "マウス")*(テーブル1[金額]>=150000))
OR条件もまったく同じ考え方で処理できます。ANDとの違いは条件を繋げる記号を+
にすることです。
まずはTRUE
/FALSE
の判定をわかりやすくするためにOR条件の判定計算式をN3
に出して、それを参照してみます。
セル | 判定内容 | 数式 |
---|---|---|
L3 |
製品名がマウスと等しい | テーブル1[製品名]="マウス" |
M3 |
製品名がディスプレイと等しい | テーブル1[製品名]="ディスプレイ" |
N3 |
L3# とM3# のOR条件 |
L3# + M3# |
続いてFILTER関数の中にOR条件を直接書いてみます。意図通りに動いていることが確認できます。
A39 = FILTER(テーブル1,(テーブル1[製品名]= "マウス")+(テーブル1[製品名]= "ディスプレイ"))
もちろん、ANDとORを組み合わせることも可能です。製品名がマウス、またはディスプレイで、金額が100,000円以上のものを抽出してみましょう。まずは製品かマウスかディスプレイのOR条件を作り、その結果に対してAND条件で金額が100,000円以上となるように括弧の位置を付ける必要があります。
A39 = FILTER( テーブル1, (( テーブル1[製品名] = "マウス" ) + ( テーブル1[製品名] = "ディスプレイ" ) ) * ( テーブル1[金額] >= 100000 ) )
やや長くなりましたが、以上がスピル機能を用いる際によく使う関数の基本的な解説です。
実践的な活用
ここからはスピルを用いた実践的な活用例を紹介します。今回は応用幅の広いFILTER関数について下記の二点の事例を解説したいと思います。5
- メンテナンスしやすい複数条件の設定方法
- 表示と非表示の切り替え
メンテナンスしやすい複数条件の設定方法
FILTER関数は、二つ目の引数のTRUE
/FALSE
の配列の取り扱いが肝要になります。繰り返しになりますが、次の二点を守れば自在にデータを抽出できるようになります。
- 抽出前の表(配列)の行数と、条件となる
TRUE
/FALSE
の配列の行数は必ず等しい - FILTER関数の二つ目の引数となる配列は、
TRUE
かFALSE
、または1
(=TRUE
)か0
(=FALSE
)で構成する
さて、先ほどのFILTER関数の解説では、OR条件で製品名を一つずつ数式内にべた書きしました。しかし、条件が二つくらいなら別に気になりませんが、大量に指定しようとすると数式が長くなってしまい内容が分かりにくくなります。また、メンテナンスも大変面倒なことになります。そこで、絞り込みの条件を関数を使って表現してみたいと思います。
ここで使う関数はXMATCHとISNUMBERです。
関数名 | 機能 |
---|---|
XMATCH | 検索値を対象セル範囲内で検索し、項目の相対位置を返す (デフォルトで完全一致) |
ISNUMBER | 対象が数値であればTRUE 、数値でなければFALSE を返す |
サンプルケースとして、複数の勘定科目を機械的に抽出してみます。手順は下記のとおりです。
- 抽出したい項目を一覧リストにします(SSのセル
J4
~J7
) - XMATCHで科目名称が
J4
~J7
に存在するかをチェックする。存在する場合は数値、存在しない場合は#N/A
が返る - ISNUMBERを使ってXMATCHの結果が数値であれば
TRUE
、数値でないならばFALSE
を返す
これでFILTER関数の二つめの引数として使える、元の表と同じ行数のTRUE
/FALSE
の配列が完成しました。これをそのままFILTER関数の条件に使えば完成です。
上記の例は分かりやすさを重視するために補助列を使いましたが、補助列を使わずにFILTER関数内部に入力するとこんな感じです。OR条件をダラダラと書くよりも非常にシンプルですし、抽出したい科目が増えたときにもメンテナンスがとても簡単になります。
J16 = FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7)))
さて、先ほどの出力結果は補助科目名の並びがバラバラで見にくいので、ソートしちゃいましょう。方法は色々ありますが、元の数式をいじらないシンプルな方法として今回はSORT関数を用います。
J16 = SORT(FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7))),3,1)
補助科目名称はきれいにソートできましたが、どうせなら部門名の並びもきれいにしたいですね。複数の列に対してソートを行う時、普通はSORTBY関数を使うのが基本です。しかしFILTER関数で抽出した結果は配列になっており列名が消失しているため、テーブルのように基準列名を直接入力することができません。この場合はCHOOSECOLS関数でインデックス番号を使って入力するのが真っ当な方法かと思いますが、式が長くなるので面倒です。そこで、ちょっとした小技の{}
を使ってみます。(配列定数とかいうらしいです)
配列を手作業で定義する機会は滅多にないので挙動が想像つきにくい方も多いかもしれません。配列を定義する{}
の中に数字を記入するとこんな感じに出力されます。
では、この配列定数とSORT関数を組み合わせてFILTER関数の結果をうまい具合にソートします
=SORT(FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7))),{3,4,5},{1,1,-1})
SORT関数の後ろ二つの引数が非常にわかりにくいかと思いますが、{3,4,5}
と{1,1,-1}
で最初に3 列目で昇順(1
)、次に 4列目で昇順(1
)、最後に 5列目は降順(-1
)でソートすることを意味します。
上記の方法がわかりにくいということであれば、SORT関数を複数回繰り返すことでも対応が可能です。一番優先したいソートの基準軸を外側に設定して、SORT関数を繰り返し使えば小技の{}
を使わずとも同じことが実現可能です。(簡単ではありますが、ネストが深くなってしまって分かりにくいので個人的にはあまり好きではありません…)
=SORT(SORT(SORT(FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7))),5,-1),4,1),3,1)
なお、SORTBY関数で真っ当に書くとこんな感じです、多分。先ほどと結果は同じですが、式は冗長になります。
J16 =LET( _array,FILTER(テーブル2,ISNUMBER(XMATCH(テーブル2[補助科目名称],J4:J7))), SORTBY(_array,CHOOSECOLS(_array,3),1,CHOOSECOLS(_array,4),1,CHOOSECOLS(_array,5),-1) )
抽出条件の切り替え
基礎解説にて説明した複数列UNIQUEとSUMIFS、これにFILTERを組み合わせることで、任意のデータの絞り込みの表示・非表示切替機能の実装を行ってみます。
今回使うのはこのようなデータです。見切れていますが3,000件ほどあります。
販管費について、まずは科目別・各部門別での集計をおこなってサマリーを作ります。 次に科目別・各部門別での合計金額が閾値を超えるもののみを抽出するための切り替え機能を付けてみます。
まずは補助科目と部門名のユニークな一覧を作成するとともに、補助科目と部門名のユニークな一覧を軸にしてSUMIFSをおこないます。
F2=SORT(UNIQUE(HSTACK(テーブル3[部門名],テーブル3[補助科目名称])),{1,2},{1,1}) H2=SUMIFS(テーブル3[金額],テーブル3[部門名],CHOOSECOLS(F2#,1),テーブル3[補助科目名称],CHOOSECOLS(F2#,2))
次に、セルL2
に閾値を入力します。今回は適当に1,000,000とします。
そしてセルI2
に、H2#>=L2
と入力し、補助科目別・部門別で集計された金額が1,000,000以上の場合にFALSE
を返す列を作成します。
続いて、L4
に下記のような入力規則を設定します。これでセルL4
はドロップダウンから「全表示」か「閾値以上」かを選択することができます。
入力規則を作成したら、先ほどセルI2
に設定したH2#>=L4
を次のように書き換えます。
I2 =IF(L4="閾値以上", H2#>=L2, IF(SEQUENCE(ROWS(H2#)),TRUE) )
唐突に出てきたIF(SEQUENCE(ROWS(H2#)),TRUE)
は、複数の関数を組み合わせて任意の値を任意の回数スピルさせる小技です。
IF(SEQUENCE(ROWS(H2#)),TRUE)
の細かい説明
利用している関数の簡単な説明は下記のとおりです。
関数名 | 機能 |
---|---|
SEQUENCE | 連番を作る |
ROWS | 指定した範囲の行数を返す |
単純化すると下記のようになります。
まず、ROWS関数で出力したい行数(5
)を取得します。これをそのままSEQUENCE関数に渡します。そうすると=IF(SEQUENCE(5),TRUE)
という形になります。SEQUENCEは連番を返しますので、1
~5
の連番を作成します。これをIFの最初の引数にもってきた場合、結果は常に真になります。そのため、作成された1
~5
のそれぞれに対応してTRUE
が返るため、五行のTRUE
がスピルするという仕組みになっています。直感的ではないかもしれませんが、こういうことができるという小技の一つとしてご理解ください。他にも色々とやり方はあるらしいです。
2023年12月17日追記
光希桃さんからご指摘いただいたので補足いたします。FILTER関数の制御用に、常にすべての行を表示させるための配列を作成するために、もっとシンプルにしようとすると次のような書き方が可能です。
SEQUENCE(ROWS(H2#))
FILTER関数は0
以外の数値であれば表示する仕組みになっていいます。SEQUENCEは一つ目の引数のみ設定した場合に、1から引数に設定した数値分だけ1刻みの連番を作成します。よって、SEQUENCE(ROWS(H2#))
と記載した場合には、出力される配列は必ず0を超えた数字の連番になります。よって、FILTER関数で条件にこの配列を用いると、常に表示される仕組みになります。
FILTER関数の表示・非表示を制御するTRUE
/FALSE
の配列は、対象となる表(配列)と同じ行数である必要があります。そこでまずはROWS(H2#)
を使って対象となる表(配列)の行数を取得します。任意の値を任意の行数出力する小技を使って、TRUE
をH2#
と同じ行数分スピルするIF(SEQUENCE(ROWS(H2#)),TRUE)
を準備します。最後にIF関数を使って、ドロップダウンリストが閾値以上のときはH2#>=L2
、そうでないときは元表の行数の数だけTRUE
をスピルする配列をセルI2
に設定しました。
セルL4
のドロップダウンリストを切り替えると、セルI2
からスピルした値は「全表示」の時にはすべての行がTRUE
、「閾値以上」の時には閾値1,000,000円以上のものがTRUE
でそれ以外はFALSE
になることが確認できます。
これで準備は完了しましたので、最後の仕上げに移ります。
まずはN2
に下記の数式を入力します。
N2 = HSTACK(F2#,H2#)
これでF2
からスピルしたデータと、H2
からスピルしたデータを横方向に合成した配列が出力されます。
最後に、閾値チェックの数式がセットされたI2#
を、FILTER関数の条件にセットします。
N2 = FILTER(HSTACK(F2#,H2#),I2#)
これで完成です。まずはドロップダウンリストで全表示を選択してみます。
I2
に入力されたされた数式はすべてTRUE
を返すのですべてのデータが表示されます
次に、ドロップダウンリストで閾値以上を選択してみると、無事に1,000,000円以上のものが表示されました。
閾値を変更したい時はL2
の金額を書き換えるだけでOKです。閾値を変えることが想定される場合には関数の中に直接条件数値を書きこまずにセル参照にすることでメンテナンス性が向上します。
今回の解説では、UNIQUEでサマライズした集計結果を用いて解説を行いましたが、このFILTERによる自由なフィルタリングは仕訳単位のデータにも応用することができます。重要なのは、表示と非表示を制御するTRUE
とFALSE
を返す数式をいかに組み立てるかです。ここさえできれば、自由自在なデータ抽出や表示の切り替えが可能です。アイディア次第ですが様々な応用が可能です。下記の使い方などは実務でかなり重宝しています。
- FIND関数を用いて特定の文字を含むデータを抽出
- 特定の科目を含む伝票を仕訳の一覧リストから貸借まるごと一括して抽出
終わりに
DXがどうのというご時世の中、組織の方針に則してノーコード・ローコード開発ツールやRPA、BIツールなどを活用することによって仕事の仕方を変えていくことが求められているかと思います。しかし、実際問題としてこれらのツールの開発や維持、運用コストに見合わないようなマイクロな業務は無数に存在します。細かい仕訳一つ一つについてツールを使った開発を行うなどはとても現実的ではありません。
長々と解説記事を書いておいて今更ですが、このような状況下において各種ツールではカバーしきれない細かな業務を埋める方策として、スピルの機能を活用するくらいでよいかな程度に思っています。所詮は単なるExcelですからね。カッターナイフで牛を捌くわけにはいきません。最近ローコードやらPower BIメインで仕事をしているため特にそう感じるのかもしれませんが、やはり各種ツールは適切な範囲で適切な使い方をすべきです。
なお、スピル系の数式はやりすぎると何をやっているのかまったくわからない特級呪物を生み出すことが可能です。今回の解説では取り上げませんでしたが、LAMBDA系を用いると作成者自身ですら内容を把握しにくくなるほどです。
個人で使う分には問題ありませんが、組織で共有するExcelファイルでスピル系の機能を用いる際には、加工の意図や処理過程の内容をドキュメントとして残すとともに、最悪のケースとして手作業で切り戻しができるような体制を整えておくことを強く推奨します。
最後までお読みいただき、誠にありがとうございました。
- 時間の制約上、CSEの解説は除外します。不可能ではないというだけでそれほど実用的ではないですし、私も実際に使ったことはありません。↩
- テーブルを用いずに列指定することもできますが、不要行の処理が必要になります。時間の制約上、今回は説明を割愛します。単にDROPやFILTER+ISNUMBER等で不要行を落とすだけですが。↩
- 以前から存在するフィルターオプションという機能を使えば制限のない自由なデータのフィルタリングができますが、設定がやや面倒で使い勝手がよいとは言えません。気軽に使える関数はやはり正義です。↩
- 気づいた方もおられるかもしれませんが、この書き方はSUMPRODUCTで複数条件の集計を使うときに使うあれです。ExcelにSUMIFSがまだ存在しなかった頃はSUMPRODUCTを使わざるを得ず、計算速度に悪態をつきながらF9ボタンで計算を手動更新していました。↩
- SCAN関数やREDUCE関数などを使えばさらに多くのことができますが、内容が難解になるため今回取り扱うトピックには含めないこととします。時間が足りません…。↩